In Oracle and other databases there is a way to dump all the values that hav
e
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files
.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted whe
n
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that ha
ve
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream file
s.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a sele
ct
>insert from one table into another and only 1995 rows are being inserted wh
en
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen o
r
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believ
e
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>sql
Showing posts with label variables. Show all posts
Showing posts with label variables. Show all posts
Wednesday, March 21, 2012
Dumping SQL Server Variables
In Oracle and other databases there is a way to dump all the values that have
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitations
I don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>
|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.co m> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations
|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitations
I don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>
|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.co m> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations
|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
Dumping SQL Server Variables
In Oracle and other databases there is a way to dump all the values that have
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
Friday, February 17, 2012
DTS Variables..?
Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?I tend to stay away from DTS... Some smartly written stored procs using Bulk Insert should do the trick...|||Yes,
Such as a FileName to export to and/or a parameter for the export query?I tend to stay away from DTS... Some smartly written stored procs using Bulk Insert should do the trick...|||Yes,
I've done it from a stored procedure. Not sure how to go from ado.net directly to DTS.
Here a couple links to examples of using global variables. Not exactly what you need, but a start. It's what I used to get going and figured it out from there.
http://www.swynk.com/friends/green/textfile.asp
http://www.swynk.com/friends/green/DTSHowTo3.asp
http://www.sqldts.com/
http://www.dts2000.com/|||i did it this way from VB.NET...
Dim DTSPackage As New DTS.Package2
DTSPackage.LoadFromSQLServer(Config.Server, Config.User, Config.Pwd, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", DTSPackageName)
DTSPackage.GlobalVariables().Remove("ImportFileName")
DTSPackage.GlobalVariables().AddGlobalVariable("ImportFileName", ImportFileName)
DTSPackage.GlobalVariables().Remove("ExportCatalog")
DTSPackage.GlobalVariables().AddGlobalVariable("ExportCatalog", ExportCatalog)
i had a little trouble resetting the value of the global var in the DTS package if it already existed so i just removed it and re-added it. I'm sure theres a better way but...time was scarce.
Note: DTS Package Global Vars are case sensitive|||Thank you,
Got it late, but still works good!|||I'm having one little problem with this whole thread. What class library is the DTS.Package2 contained in? I can find no reference to it anywhere!|||DTS is a COM library that gets installed when you load SQL server
its named: dtspkg.dll
its titled: Microsoft DTSPackage Object Library
it lives in SQL Servers BINN folder|||Thanks a bunch. That's exactly what I needed. I've always been more on the SQL side than the .NET side. I searched everywhere for that reference and couldn't find a single one for DTS. Since it is only executed once a month (and never on the same day) I couldn't schedule it as I normally would.
Subscribe to:
Posts (Atom)