@@fetch_status in trigger

in the trigger for update and insert i have the following cursor

declare crs cursor static local for
SELECT [id] FROM inserted

open crs
fetch next from crs into @.v1

print @.@.fetch_status

while @.@.fetch_status = 0
begin
print @.v1

fetch next from crs into @.v1
end

close crs
deallocate crs

the problem is that the @.@.fetch_status is now always -1 and I'm pretty sure that it worked some time ago and i can't figure what is changed.

any ideas ?
thnx.Why would you want to do this? What's the real trigger look like?

Can you post that code?

A cursor in a trigger would more than likely perform poorly...|||I use cursor in trigger cause i can have inserts/updates from multiple sources and i don't want to break the logic.

code in attach|||fixed.

cursor threshold was 0; changed to -1

@ local variable makes query slower?

Hi all,

I have a query that scans huge table consists of 8 or more millions
records. The funny thing is that if I use the query with local
variable, the query takes more than 1 minutes, whereas if I hard code
the value into the query, it takes about 1 second. Here are the
queries:

WITH VARIABLE:
------

DECLARE @.i_StartDate DATETIME
DECLARE @.i_EndDate DATETIME
SET @.i_StartDate = '2004-04-26'
SET @.i_EndDate = '2004-04-28'

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)

...And some other conditions

-------

WITHOUT VARIABLE:

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')

...And some other conditions

-------

The later one runs significantly faster than the first one. I've
isolated the problem at the local variable @.i_StartDate and
@.i_EndDate. Can somebody help me out, Please...

Thank you,
Michelle."Michelle" <michelletran@.harmonyremote.com> wrote in message
news:56c5b7ab.0404260656.281cfc40@.posting.google.c om...
> Hi all,
> I have a query that scans huge table consists of 8 or more millions
> records. The funny thing is that if I use the query with local
> variable, the query takes more than 1 minutes, whereas if I hard code
> the value into the query, it takes about 1 second. Here are the
> queries:
> WITH VARIABLE:
> ------
> DECLARE @.i_StartDate DATETIME
> DECLARE @.i_EndDate DATETIME
> SET @.i_StartDate = '2004-04-26'
> SET @.i_EndDate = '2004-04-28'
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)
> ...And some other conditions
> -------
> WITHOUT VARIABLE:
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')
> ...And some other conditions
> -------
> The later one runs significantly faster than the first one. I've
> isolated the problem at the local variable @.i_StartDate and
> @.i_EndDate. Can somebody help me out, Please...
> Thank you,
> Michelle.

This may be an example of parameter sniffing - see this post, for example,
which describes an almost identical case:

http://groups.google.com/groups?hl=...ftngp13.phx.gbl

Simon|||Thanks Simon.
Michelle.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

.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

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 sa
y
> 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...
>
>|||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...[vbcol=seagreen]
> 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:
>

.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,
CGW
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
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:

.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
>
>

.Net App Deployment Scenario: Privileges for Non-Admin Users

My VB.net application manipulates data in a local SQL Express database. When the app is installed, the database does not exist, but it cannot be created at run-time by anyone other than a user with administrator privileges. In addition, the application shares the data stored in SQL with a critical 3rd party component that can only reach the database via named DSN (also not existing prior to installation).

I see my primary SQL security options as being:

(1) Use SQL or Mixed Mode authentication with an admin-level username/password combination, or

(2) Create a db user/group with admin-level privileges and grant membership to all NT authenticated users

Secondary problem: Creating the DSN.

Does it make sense to create a Custom Action (.dll) that is called at the end of the installation process in order to create the database, the user security context and the named DSN?

Am I overlooking some built-in functionality provided by Visual Studio 2005 that will accompish some or all of this for me? I am aware that customizing the 'silent' installation of SQL Express to use a different authentication mode requires manifest tweaking -- I just don't know anything about setting up the appropriate security for this situation. Would you put the db and role creation stuff in a SQL script and execute it post-install?

Thanks in advance for any insight you can provide.

-T

UPDATE

I plan to modify the SQL Express installation to use mixed-mode authentication, then execute code at the tail end of my app install to (a) create the database, (b) create a dbo-level user (and pwd) for the database, and (c) create my DSN (via SQLConfigDataSource API call).

Feel free to respond if you think/know that there is a better solution.

-T

.Net App Deployment Scenario: Privileges for Non-Admin Users

My VB.net application manipulates data in a local SQL Express database. When the app is installed, the database does not exist, but it cannot be created at run-time by anyone other than a user with administrator privileges. In addition, the application shares the data stored in SQL with a critical 3rd party component that can only reach the database via named DSN (also not existing prior to installation).

I see my primary SQL security options as being:

(1) Use SQL or Mixed Mode authentication with an admin-level username/password combination, or

(2) Create a db user/group with admin-level privileges and grant membership to all NT authenticated users

Secondary problem: Creating the DSN.

Does it make sense to create a Custom Action (.dll) that is called at the end of the installation process in order to create the database, the user security context and the named DSN?

Am I overlooking some built-in functionality provided by Visual Studio 2005 that will accompish some or all of this for me? I am aware that customizing the 'silent' installation of SQL Express to use a different authentication mode requires manifest tweaking -- I just don't know anything about setting up the appropriate security for this situation. Would you put the db and role creation stuff in a SQL script and execute it post-install?

Thanks in advance for any insight you can provide.

-T

UPDATE

I plan to modify the SQL Express installation to use mixed-mode authentication, then execute code at the tail end of my app install to (a) create the database, (b) create a dbo-level user (and pwd) for the database, and (c) create my DSN (via SQLConfigDataSource API call).

Feel free to respond if you think/know that there is a better solution.

-T

.mdf/.ldf on a NAS

I'm wondering if it's a good idea to put datafiles on a NAS and to run the
DB Servers without local disks.
Seems risky to me, but I know the topic has come up and (I hope) Microsoft
has an article on the subject.
Thanks,
JayJay,
http://support.microsoft.com/kb/304261
RLF
"Jay" <nospan@.nospam.org> wrote in message
news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
> I'm wondering if it's a good idea to put datafiles on a NAS and to run the
> DB Servers without local disks.
> Seems risky to me, but I know the topic has come up and (I hope) Microsoft
> has an article on the subject.
> Thanks,
> Jay
>|||I'd recommend against placing the database files on NAS.
NAS is based on CIFS and CIFS is not a good protocol for block I/Os. In
addition, a host with a mapped network drive is a CIFS client, and if you
read the CIFS specs carefully, data can be cached on the CIFS client side
(i.e. SQL Server host if you map a network drive there). I'm not comfortable
with what that CIFS caching may do to your SQL database integrity.
Linchi
"Jay" wrote:
> I'm wondering if it's a good idea to put datafiles on a NAS and to run the
> DB Servers without local disks.
> Seems risky to me, but I know the topic has come up and (I hope) Microsoft
> has an article on the subject.
> Thanks,
> Jay
>
>|||I misspoke, not a NAS, a SAN (if it makes a difference)
"Jay" <nospan@.nospam.org> wrote in message
news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
> I'm wondering if it's a good idea to put datafiles on a NAS and to run the
> DB Servers without local disks.
> Seems risky to me, but I know the topic has come up and (I hope) Microsoft
> has an article on the subject.
> Thanks,
> Jay
>|||NAS = Network Attached Storage
SAN = Storage Area Network
They are different stuff.
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Jay" <nospan@.nospam.org> wrote in message
news:eZduXt$GIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I misspoke, not a NAS, a SAN (if it makes a difference)
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
>> I'm wondering if it's a good idea to put datafiles on a NAS and to run
>> the DB Servers without local disks.
>> Seems risky to me, but I know the topic has come up and (I hope)
>> Microsoft has an article on the subject.
>> Thanks,
>> Jay
>|||Yes, SAN is OK, since it has dedicated HW and network with protocols designed for this. Consider
what you have at the other end, though, and how the SAN is setup. It isn't uncommon to end up with
your database files "in the pool" with no control for you regarding RAID levels and whatever else
get shared with your data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:eZduXt$GIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I misspoke, not a NAS, a SAN (if it makes a difference)
> "Jay" <nospan@.nospam.org> wrote in message news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
>> I'm wondering if it's a good idea to put datafiles on a NAS and to run the DB Servers without
>> local disks.
>> Seems risky to me, but I know the topic has come up and (I hope) Microsoft has an article on the
>> subject.
>> Thanks,
>> Jay
>|||I'd venture to say that today most databases in large enterprises run on some
kind of SANs, mostly fibre-channel SANs.
Linchi
"Jay" wrote:
> I misspoke, not a NAS, a SAN (if it makes a difference)
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
> > I'm wondering if it's a good idea to put datafiles on a NAS and to run the
> > DB Servers without local disks.
> >
> > Seems risky to me, but I know the topic has come up and (I hope) Microsoft
> > has an article on the subject.
> >
> > Thanks,
> > Jay
> >
>
>|||SAN will be fine, it's what we do.
SAN and NAS have almost nothing in common, unless you're dyslexic :-)
"Jay" <nospan@.nospam.org> wrote in message
news:eZduXt$GIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I misspoke, not a NAS, a SAN (if it makes a difference)
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
>> I'm wondering if it's a good idea to put datafiles on a NAS and to run
>> the DB Servers without local disks.
>> Seems risky to me, but I know the topic has come up and (I hope)
>> Microsoft has an article on the subject.
>> Thanks,
>> Jay
>

*.SQL File - Seemingly Easy

The Set-Up:
Remote Shared SQL Server
Local Software - SQL Server Enterprise Manager
.SQL File
The Problem:
I am trying to set up a forum on my website which tells me to use the
supplied .SQL file to set up the tables. How do I go about doing that?
Thanks in advance for any feedback."Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:D3D230D4-6344-409A-9926-54BC64CBE973@.microsoft.com...
> The Set-Up:
> Remote Shared SQL Server
> Local Software - SQL Server Enterprise Manager
> .SQL File
> The Problem:
> I am trying to set up a forum on my website which tells me to use the
> supplied .SQL file to set up the tables. How do I go about doing that?
> Thanks in advance for any feedback.
Open up Query Analyzer (from EM you can highlight the correct database and
select Tools > Query Analyzer from the main menu). Then in QA, click File >
Open ... and load your .SQL file. Then select Query > Execute (or press
F5).|||Heck ya, thanks lots!

(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.

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.