Showing posts with label instance. Show all posts

Tuesday, March 27, 2012

@@ERROR per connection?

Hi
Hoping someone may know this. I am connecting to sql server via
OleDbConnection from C#.
Is the instance of @.@.ERROR that is set in db per connection? What I
was going to do was, after connecting and opening a data reader on one
connection, I wanted to open a 2nd connection and read @.@.ERROR.
But every time I read @.@.ERROR, it is 0, regardlessof the previous
read.
I suspect this is because conenction 2 is not reading the @.@.ERROR set
by connection1.
Is this the case?
Thanks
Jeff"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:6o3ap198n6vs7kb14h80maab9rgd9mfelg@.
4ax.com...
> Hi
> Hoping someone may know this. I am connecting to sql server via
> OleDbConnection from C#.
> Is the instance of @.@.ERROR that is set in db per connection? What I
> was going to do was, after connecting and opening a data reader on one
> connection, I wanted to open a 2nd connection and read @.@.ERROR.
> But every time I read @.@.ERROR, it is 0, regardlessof the previous
> read.
> I suspect this is because conenction 2 is not reading the @.@.ERROR set
> by connection1.
> Is this the case?
Correct. Any time you get an error it propagates back to the client. For
you it will become an OleDbException. BTW why OleDbConnection and not
SqlConnection?
David|||Thanks David
I used OleDb because I read that it was more verstile and my code will
eventually be ported to other databases. Is this incorrect?
Thanks
Jeff
On Mon, 5 Dec 2005 22:34:07 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:

>"Jeff User" <jeff31162@.hotmail.com> wrote in message
> news:6o3ap198n6vs7kb14h80maab9rgd9mfelg@.
4ax.com...
>Correct. Any time you get an error it propagates back to the client. For
>you it will become an OleDbException. BTW why OleDbConnection and not
>SqlConnection?
>David
>|||Hmm, do I even need to try to read the SQL server @.@.error or will I
always get the same information in my oleDb error that is raised when
there is a failure?
Thanks again
Jeff
On Mon, 5 Dec 2005 22:34:07 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:

>"Jeff User" <jeff31162@.hotmail.com> wrote in message
> news:6o3ap198n6vs7kb14h80maab9rgd9mfelg@.
4ax.com...
>Correct. Any time you get an error it propagates back to the client. For
>you it will become an OleDbException. BTW why OleDbConnection and not
>SqlConnection?
>David
>|||"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:486ap1ppcjptrckbde41oekh84e2srj163@.
4ax.com...
> Hmm, do I even need to try to read the SQL server @.@.error or will I
> always get the same information in my oleDb error that is raised when
> there is a failure?
That's an OleDB implementation detail I don't know. With the SqlClient, the
Error number and message are available.
David|||"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:746ap1dmf2hgclqarvfe2gd219dbajcfvk@.
4ax.com...
> Thanks David
> I used OleDb because I read that it was more verstile and my code will
> eventually be ported to other databases. Is this incorrect?
That's one way. But probably not the preferred way. It isolates the
differences between databases out to a layer you have very little access to,
and forces you to use COM interop in your code. All the .NET data providers
implement a set of common interfaces, so you can support multiple databases
and still use the best client library for each one.
See:
Generic Coding with the ADO.NET 2.0 Base Classes and Factories
http://msdn.microsoft.com/library/d...r />
erics.asp
Data Access Application Block for ADO.NET 1.1
http://msdn.microsoft.com/practices...2/html/daab.asp
David|||OK, I can do some research/testing on this one.
Thanks for both replies and I will look into the sqlClient and check
out the articles.
jeff
On Mon, 5 Dec 2005 23:53:35 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:

>"Jeff User" <jeff31162@.hotmail.com> wrote in message
> news:486ap1ppcjptrckbde41oekh84e2srj163@.
4ax.com...
>That's an OleDB implementation detail I don't know. With the SqlClient, th
e
>Error number and message are available.
>David
>

Tuesday, March 20, 2012

/3GB SQL Server 2000

Hi,
I run SQL Server 2000 on Windows 2000 Server (4 GB memory) with 2
instances.
As I understand it, one instance can use a maximum of 2 GB of memory. If I
would use the /3GB switch in this environment, would that help me or is it
that this switch can only be used with the Advanced or Enterprise Edition
of SQL Server 2000 /w2K?
TIA
WombelYou need to run Windows 2000 Advanced Server or Data Center editions in
order to use the /3GB switch. See
http://support.microsoft.com/defaul...750&Product=sql
for more information.
Assuming you want both instances to use an equal amount of memory, you
wouldn't want to use /3GB anyway with only 4GB physical RAM. It's important
to have enough physical RAM to support the SQL Server memory requirements of
all instances running on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Wrobel" <twrobel@.waslos.de> wrote in message
news:Xns94C3739FDCBEBtwrobelwaslosde@.207
.46.248.16...
> Hi,
> I run SQL Server 2000 on Windows 2000 Server (4 GB memory) with 2
> instances.
> As I understand it, one instance can use a maximum of 2 GB of memory. If I
> would use the /3GB switch in this environment, would that help me or is it
> that this switch can only be used with the Advanced or Enterprise Edition
> of SQL Server 2000 /w2K?
> TIA
> Wombel|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in
news:OrPWjb9GEHA.684@.tk2msftngp13.phx.gbl:

> You need to run Windows 2000 Advanced Server or Data Center editions
> in order to use the /3GB switch. See
> http://support.microsoft.com/defaul...274750&Product=
> sql for more information.
> Assuming you want both instances to use an equal amount of memory, you
> wouldn't want to use /3GB anyway with only 4GB physical RAM. It's
> important to have enough physical RAM to support the SQL Server memory
> requirements of all instances running on the server.
>
Dan,
thanks for your reply. Do you know if there is any difference when I user
Windows 2003 Server Standard?
TIA
Thomas|||> thanks for your reply. Do you know if there is any difference when I user
> Windows 2003 Server Standard?
As stated in MSKB 274750, you can use the /3GB boot.ini switch with Windows
2000 Server (Standard Edition).
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Wrobel" <twrobel@.waslos.de> wrote in message
news:Xns94C39FB4C54Etwrobelwaslosde@.207.46.248.16...
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in
> news:OrPWjb9GEHA.684@.tk2msftngp13.phx.gbl:
>
> Dan,
> thanks for your reply. Do you know if there is any difference when I user
> Windows 2003 Server Standard?
> TIA
> Thomassql

Monday, March 19, 2012

/3gb memory switch

The properties of sql server (EE 2000) show that the
instance is set to dynamically allocate 0 - 3895 MB.
There are 4gb of ram on the server. However, the /3gb
memory switch is not set in the boot.ini file. It looks
like that this is what is happening -- sql server
recognizes all of the memory in the server, but the OS
prevents user processes from utilizing more than 2 GB. Is
this as expected? I'm getting ready to set the /3gb
switch and need a little reassurance. It is also my
understanding, that AWE does not need to be enabled when
dealing with a server with only 4GB of RAM. The only
change I need to make is the /3gb switch in the boot.ini
file -- can someone confirm?You are correct. /3B switch will allow the SQL process to use 3GB of user
memory instead of 2GB user memory.
AWE is for servers with greater than 4GB of physical memory.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:f92301c3f197$e266ced0$a501280a@.phx.gbl...
> The properties of sql server (EE 2000) show that the
> instance is set to dynamically allocate 0 - 3895 MB.
> There are 4gb of ram on the server. However, the /3gb
> memory switch is not set in the boot.ini file. It looks
> like that this is what is happening -- sql server
> recognizes all of the memory in the server, but the OS
> prevents user processes from utilizing more than 2 GB. Is
> this as expected? I'm getting ready to set the /3gb
> switch and need a little reassurance. It is also my
> understanding, that AWE does not need to be enabled when
> dealing with a server with only 4GB of RAM. The only
> change I need to make is the /3gb switch in the boot.ini
> file -- can someone confirm?

Sunday, March 11, 2012

.NET, SQL, and firewall

We're having a problem with a local intranet site and SQL. The web server
sits behind a firewall. There is an instance of SQL on it with one, primary
database with users, permissions, and roles types of data. The main SQL
server sits on the network domain. Each SQL instance links to the other.
The PROBLEM is that when traffic is heavy on the site, the web server SQL
will sometimes "Lock up", failing to return queries. Restarting the MSSQL
service on the web server always corrects the problem, but we shouldn't be
having it. Anyone recognize the symptoms?
--
Thanks,
CGWUsing Task Manager check what process is taking all the CPU.
I've had a problem with a client where their firewall could only handle say
2Mbits/second which is quite small considering it was an intranet
application with lots of clients, so the bottleneck looked like SQL not
handling the load, but it was actually the firewall.
If you need to go through a firewall to go from the web box through to the
SQL box check what bandwidth it can handle and then check how much the
network between them is utilised.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:6E9E95C2-765F-4772-B15C-82678F955255@.microsoft.com...
> We're having a problem with a local intranet site and SQL. The web server
> sits behind a firewall. There is an instance of SQL on it with one,
> primary
> database with users, permissions, and roles types of data. The main SQL
> server sits on the network domain. Each SQL instance links to the other.
> The PROBLEM is that when traffic is heavy on the site, the web server SQL
> will sometimes "Lock up", failing to return queries. Restarting the MSSQL
> service on the web server always corrects the problem, but we shouldn't be
> having it. Anyone recognize the symptoms?
> --
> Thanks,
> CGW|||Amazing. Our dept head guessed it could be the firewall, but I had my doubts
since the instance of SQL we seemed to be having trouble with sits on the
same machine (and same side of the firewall) as the .NET application. <Bad
form for the boss to be right, but then he often is>. Thanks for the help.
We'll check it out.
--
Thanks,
CGW
"Tony Rogerson" wrote:
> Using Task Manager check what process is taking all the CPU.
> I've had a problem with a client where their firewall could only handle say
> 2Mbits/second which is quite small considering it was an intranet
> application with lots of clients, so the bottleneck looked like SQL not
> handling the load, but it was actually the firewall.
> If you need to go through a firewall to go from the web box through to the
> SQL box check what bandwidth it can handle and then check how much the
> network between them is utilised.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:6E9E95C2-765F-4772-B15C-82678F955255@.microsoft.com...
> > We're having a problem with a local intranet site and SQL. The web server
> > sits behind a firewall. There is an instance of SQL on it with one,
> > primary
> > database with users, permissions, and roles types of data. The main SQL
> > server sits on the network domain. Each SQL instance links to the other.
> >
> > The PROBLEM is that when traffic is heavy on the site, the web server SQL
> > will sometimes "Lock up", failing to return queries. Restarting the MSSQL
> > service on the web server always corrects the problem, but we shouldn't be
> > having it. Anyone recognize the symptoms?
> > --
> > Thanks,
> >
> > CGW
>
>|||Your welcome.
I think it's one of those ticking time bombs - I bet there are a lot of
installations that have the same problem but they don't know (yet anyway).
Its quite easy to miss.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:50E86CDF-E776-4A04-B8AF-DF39B7B2A886@.microsoft.com...
> Amazing. Our dept head guessed it could be the firewall, but I had my
> doubts
> since the instance of SQL we seemed to be having trouble with sits on the
> same machine (and same side of the firewall) as the .NET application. <Bad
> form for the boss to be right, but then he often is>. Thanks for the help.
> We'll check it out.
> --
> Thanks,
> CGW
>
> "Tony Rogerson" wrote:
>> Using Task Manager check what process is taking all the CPU.
>> I've had a problem with a client where their firewall could only handle
>> say
>> 2Mbits/second which is quite small considering it was an intranet
>> application with lots of clients, so the bottleneck looked like SQL not
>> handling the load, but it was actually the firewall.
>> If you need to go through a firewall to go from the web box through to
>> the
>> SQL box check what bandwidth it can handle and then check how much the
>> network between them is utilised.
>> Tony.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlserverfaq.com - free video tutorials
>>
>> "CGW" <CGW@.discussions.microsoft.com> wrote in message
>> news:6E9E95C2-765F-4772-B15C-82678F955255@.microsoft.com...
>> > We're having a problem with a local intranet site and SQL. The web
>> > server
>> > sits behind a firewall. There is an instance of SQL on it with one,
>> > primary
>> > database with users, permissions, and roles types of data. The main SQL
>> > server sits on the network domain. Each SQL instance links to the
>> > other.
>> >
>> > The PROBLEM is that when traffic is heavy on the site, the web server
>> > SQL
>> > will sometimes "Lock up", failing to return queries. Restarting the
>> > MSSQL
>> > service on the web server always corrects the problem, but we shouldn't
>> > be
>> > having it. Anyone recognize the symptoms?
>> > --
>> > Thanks,
>> >
>> > CGW
>>

.NET, SQL, and firewall

Thursday, March 8, 2012

.NET Performance - Native vs OLE DB

Using a local instance of SQL Server 2000 and .NET I have managed to insert 4000 rows across multiple tables using the OLE DB provider in 11 seconds but when using the native provider it takes 13 seconds.
I was under the impression the native provider should enhance performance not decrease it? Why is this?
Thanks, Robby
By native provider, do you mean System.Data.SqlClient? In general SqlClient
is a good deal faster than using the combination of System.Data.OleDb and
the native oledb provider for sql server. To comment further, I'd need to
know more about your scenario.
Thanks,
Dave
"Robby White" <Robby White@.discussions.microsoft.com> wrote in message
news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> Using a local instance of SQL Server 2000 and .NET I have managed to
insert 4000 rows across multiple tables using the OLE DB provider in 11
seconds but when using the native provider it takes 13 seconds.
> I was under the impression the native provider should enhance performance
not decrease it? Why is this?
> Thanks, Robby
|||Yes I mean the System.Data.SqlClient provider.
Inserting 4000 records into SQL Server 2000
using the System.Data.SqlClient took 14 seconds
using the System.Data.Odbc took 13 seconds
using the System.Data.OleDb took 12 seconds
I have tried running these tests in different orders also and get the same results. I realise the times are close but I am concerned about scalibility.
Robby White
"David Schleifer [MSFT]" wrote:

> By native provider, do you mean System.Data.SqlClient? In general SqlClient
> is a good deal faster than using the combination of System.Data.OleDb and
> the native oledb provider for sql server. To comment further, I'd need to
> know more about your scenario.
> Thanks,
> Dave
> "Robby White" <Robby White@.discussions.microsoft.com> wrote in message
> news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> insert 4000 rows across multiple tables using the OLE DB provider in 11
> seconds but when using the native provider it takes 13 seconds.
> not decrease it? Why is this?
>
>
|||The insert operation is a fairly simple one, compared to other provider
operations such as reading data, so it's hard for any one provider to exceed
another once it's been reasonbly optimized. If you want to compare overall
provider performance, whatever benchmark you choose would have to include a
fair measure of read operations, which is one of the areas where SqlClient
really outperforms the oledb managed provider.
That said, when I tried a simple test with 16000 distinct insert operations
(i.e. seperate round trip for each), I got results for SqlClient that were
1-2% better than the OleDb provider. I think it's fair to say that the two
providers are basically equivalent for many types of insert operations, but
overall for best performance you will be better off with SqlClient.
If you are only interested in insert performance, you will probably want to
check out the Whidbey release of .NET, where SqlClient supports a bulk load
api.
"Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> Yes I mean the System.Data.SqlClient provider.
> Inserting 4000 records into SQL Server 2000
> using the System.Data.SqlClient took 14 seconds
> using the System.Data.Odbc took 13 seconds
> using the System.Data.OleDb took 12 seconds
> I have tried running these tests in different orders also and get the same
results. I realise the times are close but I am concerned about
scalibility.[vbcol=seagreen]
> Robby White
> "David Schleifer [MSFT]" wrote:
SqlClient[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
performance[vbcol=seagreen]
|||Thanks for your help...
"David Schleifer [MSFT]" wrote:

> The insert operation is a fairly simple one, compared to other provider
> operations such as reading data, so it's hard for any one provider to exceed
> another once it's been reasonbly optimized. If you want to compare overall
> provider performance, whatever benchmark you choose would have to include a
> fair measure of read operations, which is one of the areas where SqlClient
> really outperforms the oledb managed provider.
> That said, when I tried a simple test with 16000 distinct insert operations
> (i.e. seperate round trip for each), I got results for SqlClient that were
> 1-2% better than the OleDb provider. I think it's fair to say that the two
> providers are basically equivalent for many types of insert operations, but
> overall for best performance you will be better off with SqlClient.
> If you are only interested in insert performance, you will probably want to
> check out the Whidbey release of .NET, where SqlClient supports a bulk load
> api.
>
> "Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
> news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> results. I realise the times are close but I am concerned about
> scalibility.
> SqlClient
> and
> to
> performance
>
>

.NET Performance - Native vs OLE DB

Using a local instance of SQL Server 2000 and .NET I have managed to insert
4000 rows across multiple tables using the OLE DB provider in 11 seconds but
when using the native provider it takes 13 seconds.
I was under the impression the native provider should enhance performance no
t decrease it? Why is this?
Thanks, RobbyBy native provider, do you mean System.Data.SqlClient? In general SqlClient
is a good deal faster than using the combination of System.Data.OleDb and
the native oledb provider for sql server. To comment further, I'd need to
know more about your scenario.
Thanks,
Dave
"Robby White" <Robby White@.discussions.microsoft.com> wrote in message
news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> Using a local instance of SQL Server 2000 and .NET I have managed to
insert 4000 rows across multiple tables using the OLE DB provider in 11
seconds but when using the native provider it takes 13 seconds.
> I was under the impression the native provider should enhance performance
not decrease it? Why is this?
> Thanks, Robby|||Yes I mean the System.Data.SqlClient provider.
Inserting 4000 records into SQL Server 2000
using the System.Data.SqlClient took 14 seconds
using the System.Data.Odbc took 13 seconds
using the System.Data.OleDb took 12 seconds
I have tried running these tests in different orders also and get the same r
esults. I realise the times are close but I am concerned about scalibility.
Robby White
"David Schleifer [MSFT]" wrote:

> By native provider, do you mean System.Data.SqlClient? In general SqlClien
t
> is a good deal faster than using the combination of System.Data.OleDb and
> the native oledb provider for sql server. To comment further, I'd need to
> know more about your scenario.
> Thanks,
> Dave
> "Robby White" <Robby White@.discussions.microsoft.com> wrote in message
> news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> insert 4000 rows across multiple tables using the OLE DB provider in 11
> seconds but when using the native provider it takes 13 seconds.
> not decrease it? Why is this?
>
>|||The insert operation is a fairly simple one, compared to other provider
operations such as reading data, so it's hard for any one provider to exceed
another once it's been reasonbly optimized. If you want to compare overall
provider performance, whatever benchmark you choose would have to include a
fair measure of read operations, which is one of the areas where SqlClient
really outperforms the oledb managed provider.
That said, when I tried a simple test with 16000 distinct insert operations
(i.e. seperate round trip for each), I got results for SqlClient that were
1-2% better than the OleDb provider. I think it's fair to say that the two
providers are basically equivalent for many types of insert operations, but
overall for best performance you will be better off with SqlClient.
If you are only interested in insert performance, you will probably want to
check out the Whidbey release of .NET, where SqlClient supports a bulk load
api.
"Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> Yes I mean the System.Data.SqlClient provider.
> Inserting 4000 records into SQL Server 2000
> using the System.Data.SqlClient took 14 seconds
> using the System.Data.Odbc took 13 seconds
> using the System.Data.OleDb took 12 seconds
> I have tried running these tests in different orders also and get the same
results. I realise the times are close but I am concerned about
scalibility.[vbcol=seagreen]
> Robby White
> "David Schleifer [MSFT]" wrote:
>
SqlClient[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
performance[vbcol=seagreen]|||Thanks for your help...
"David Schleifer [MSFT]" wrote:

> The insert operation is a fairly simple one, compared to other provider
> operations such as reading data, so it's hard for any one provider to exce
ed
> another once it's been reasonbly optimized. If you want to compare overall
> provider performance, whatever benchmark you choose would have to include
a
> fair measure of read operations, which is one of the areas where SqlClient
> really outperforms the oledb managed provider.
> That said, when I tried a simple test with 16000 distinct insert operation
s
> (i.e. seperate round trip for each), I got results for SqlClient that were
> 1-2% better than the OleDb provider. I think it's fair to say that the two
> providers are basically equivalent for many types of insert operations, bu
t
> overall for best performance you will be better off with SqlClient.
> If you are only interested in insert performance, you will probably want t
o
> check out the Whidbey release of .NET, where SqlClient supports a bulk loa
d
> api.
>
> "Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
> news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> results. I realise the times are close but I am concerned about
> scalibility.
> SqlClient
> and
> to
> performance
>
>

Thursday, February 9, 2012

(SS2000) question about distributed queries

I need to write some distributed SQL. I'm accessing databases outside my local instance through Linked Servers.

My problem is: this SQL will run at many customer sites and the database names will be different at each.

So to refer to a table in another instance I of course have to use <LinkedServerName>.<dbname>.<owner>.<object>.

Problem: the <dbname> will be different depending on the customer site. So hardwiring the <dbname> into my scripts is a pretty major problem.

I want to avoid dynamically generating the SQL and executing through sp_executesql.

Is it possible to define a Linked Server in such a way that I can skip over the database name (refered to as "catalog" in the definition). I have a one to one mapping between databases and Linked Servers, so per Linked Server I can assume access to one and only one database.

I'm using SS2000 so synonyms are out of the picture.

In a nutshell: I need to write sciprts that can easily adapt to different database names in all the distributed queries I'm writing. Is it possible to do this in SS2000?

Please help!

Best Regards,

Mike

Use the OPENROWSET function (see BOL topic OPENROWSET (Transact-SQL)).

This will allow you to specify a connection provider (oledb maybe) and connection string, then a query to run on the target server/db.

EXAMPLE:

SELECT a.*

FROM OPENROWSET(<provider name>, <connection string>,

<query>) AS a;

So you could run the same query on different server and different DBs just by adjusting the connection string.

(SS2000) question about distributed queries

I need to write some distributed SQL. I'm accessing databases outside my local instance through Linked Servers.

My problem is: this SQL will run at many customer sites and the database names will be different at each.

So to refer to a table in another instance I of course have to use <LinkedServerName>.<dbname>.<owner>.<object>.

Problem: the <dbname> will be different depending on the customer site. So hardwiring the <dbname> into my scripts is a pretty major problem.

I want to avoid dynamically generating the SQL and executing through sp_executesql.

I'm using SS2000 so synonyms are out of the picture.

In a nutshell: I need to write sciprts that can easily adapt to different database names in all the distributed queries I'm writing. Is it possible to do this in SS2000?

Please help!

Best Regards,

Mike

Use the OPENROWSET function (see BOL topic OPENROWSET (Transact-SQL)).

This will allow you to specify a connection provider (oledb maybe) and connection string, then a query to run on the target server/db.

EXAMPLE:

SELECT a.*

FROM OPENROWSET(<provider name>, <connection string>,

<query>) AS a;

So you could run the same query on different server and different DBs just by adjusting the connection string.

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

ASP v2.0

I am getting this error when attempting to sign in to my site once uploaded to the server. Can anyone help me to resolve this error?

Server Error in '/' Application.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820
 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
 System.Data.SqlClient.SqlConnection.Open() +111
 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121
 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105
 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42
 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83
 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160
 System.Web.UI.WebControls.Login.AttemptLogin() +105
 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

First, this stack trace is really useless for diagnosing your problem. Here's some questions to get you started.

What's your connection string? Are you using integrated security?

Does the SQL Server sit on the same machine as the web server? Is it part of the same domain?

Is the sql server behind a firewall? Can the web server "see" the SQL Server?

Does this work on your machine with the same username/password?

Can you log into the Web Server via remote desktop? If so can you ping the SQL Server? Can you connect using Query Analyzer?

|||I'm using the integrated security within ASPv2.0.

The issue comes up with the login on the server.

I can sign in on the development PC and everything runs fine, but I receive this error when I attempt to log on after I upload the site to the server.

I am not sure where the connection string is for v2.0 integrated security. I have just recently moved from v1.1 and am still discovering new things as I go.

I use Godaddy.com web hosting. So the server and the development PCs are separate. I have been using the Publish Web Site tool in VS2005 and then CuteFTP Pro to transfer the files. I have configured my godaddy account to accept v2.0 framework and activated the SQL server schema.

I have found that a test non-member page is having difficulty connecting to the access database I am using. It gives me an error stating "c:\access_db\Survey.mdb is not a valid location..." <-- something of that nature. Using the following connection string for this:
strConn = New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\access_db\Survey.mdb"))

I am not sure if that is caused by the same issue or if it is because of my connection string. I assume it is a connection string issue. It works fine on my development pc even though my hard drive is e: not c:, so I don't know what's up with that really. still working on it.|||

OK. Let's back up. Are you using Access, or MS SQL Server? If you're using Access, why are you posting to a SQL Server forum?

|||I apologize I am not trying to be confusing.

My issue that I posted here for is for the SQLSever database: ASPNETDB.MDF

This is where I am getting the issue from. The Access DB is an issue I think I can handle on my own at this time. I shouldn't have mentioned it.|||

So you're trying to mount this file, which is sitting on the same computer as the web server, right? And you're using integrated security, right? I believe that integrated security requires that the user have a Windows login to the box/domain that the app's running on. I'm not sure how you're doing that with your web app.

If you're developing using VS 2005, there's a lot that your app can do (because it's running under your credentials) that it can't do on a production server.

If you have a MSDN subscription, you might want to build a test Windows 2003 server using VirtualPC, and try deploying your app there. That'll give you a better feel for what's going on with the GDaddy box.(I did that last week to diagnose problems with a web service on a server running multiple web servers. It turns out using localhost doesn't always work.)

|||The web server is a completely different PC than my development. I am uploading the files to the server using CuteFTP Pro.GoDaddy.com owns the server.

I have setup my application to use internet secruity (see jpg). It should not require a Windows login. I do not have an MSDN subscription, so I cannot use a test server using VirtualPC.

|||Does the application require the user to log in? If so, how are you passing the user id/pw to the database?|||I am using the Login control in ASP.NET v2.0

I am not sure how it passes the username/password to the database. It all happens behind the scenes.|||Well, we've pretty much hit the limits of how I can help you. If you figure it out, try to post the answer in case someone else has a similar problem.|||I appreciate your attempt. Thank you for using your time to help me.

If anyone else has discovered a solution please reply. I will do the same.

Thank you|||

I have the EXACT same problem...Godaddy hosting trying to use SQLEXPRESS database file that is included with visual web designer.

Problem is... it won't work. According to Microsoft. (If Godaddy hostsmultiple sites that do not trust each other) The note below was taken from the msdn site.I underlined what I believe is the cause of your problem.

The resolution is to use plain old connection strings to plain old SQL server database instances.

good luck

Note Note

If you are deploying your SQL Server Express Edition database to a Web server that hosts multiple sites that do not trust each other, then you cannot use file-based connections or user instances to help ensure that your data is not exposed to other applications on the server. In this case, it is recommended that you migrate the contents your SQL Server Express Edition database to another version of SQL Server 2005 that your deployed ASP.NET application can access.

http://msdn2.microsoft.com/en-us/library/ms247257.aspx|||One solution I am curious about is this:

With Godaddy, you must use your host manager in order to set up the site to use Framework v2.0 and to use SQL Server. When it sets this up it creates a few files--
(2) Folder -- "_private" and "_vti_log"
(1) HTML File -- "_vti_inf.html"
(1) Asp file -- "gdform.asp"

-- I am not sure why these files are created or what they are used for. They may have something to do with this issue.

-- When you set up the server to enable using an MS Access Db, it creates a folder called "access_db." I think what it is doing is creating virtual folders to run off of. If you create your own folder on the server usinf FTP and place an access db file inside, you are not able to access the db. But if you place the db inside the "access_db" folder that GoDaddy creates, everything works fine.

-- The solution may be in figuring out the setup here. I am going to give GoDaddy a call and see if they are familiar with this issue and have a solution.

-- If there is a way to change the connection string that v2.0 uses to connect to that user db, then it may be ab easier solution. So far I have not found a way to change the connection string for the user db within v2.0.

(Problem) Named instance as SQLEXPRESS?

What if I give my named instance as SQLEXPRESS? I read that SQLEXPRESS

would be the name of the DEFAULT instance if no named instance is given.

In such a situation how can I find out if its a named or the default one?

SQLExpress is always a named instance.

jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Ananda,

You might be a bit confused by the terms "default instance" and the fact that SQL Express is installed by default as a named instance.

Default Instance - This is the term we use to describe the copy of SQL Server that can be refered to by just the name of the computer in a connection string. There can only be one default instance of SQL Server on any computer. This is compared to a named instance, which is refered to by the <machine name>\<named instance name> in the connection string.

As Jens points out, SQL Express is installed by default as a named instance, and that named instance is SQLEXPRESS. If you specified SQLEXPRESS for the INSTANCENAME parameter in a command line install, or in the UI, you have installed a named instance. You can also install to a different named instance (up to 16 different instances for SQL Express) or you can install SQL Express as the default instance, but the default instance does not have a specific instance name.

Mike

Tuesday, March 27, 2012

@@ERROR per connection?

Tuesday, March 20, 2012

/3GB SQL Server 2000

Monday, March 19, 2012

/3gb memory switch

Sunday, March 11, 2012

.NET, SQL, and firewall

.NET, SQL, and firewall

.NET, SQL, and firewall

Thursday, March 8, 2012

.NET Performance - Native vs OLE DB

.NET Performance - Native vs OLE DB

Thursday, February 9, 2012

(SS2000) question about distributed queries

(SS2000) question about distributed queries

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Server Error in '/' Application.

(Problem) Named instance as SQLEXPRESS?

SQL SQLExpress

Blog Archive

About Me