dts import agent: syntax

Showing posts with label syntax. Show all posts

Sunday, March 25, 2012

DTS Error checking

Hello,
What would be good syntax to add to a DTS package to make sure that all of
the records from a live system made it into a warehouse table? Would it be
as simple as a COUNT(*) - and if so, how would I use that to notify or fail
if all of the records do not make it into the warehouse?
Thanks!Something like count would suffice but is not necessary. The dts either take
s
all the rows or it does not. How you log what fails is more importante. See
step2.
If I was working with a datawarehouse i would seriously entertain the idea
of a rock solid logging system. Here are some guidelines that could work for
y0ou.
Step1: For each transformation Step in the DTS, have an 'On Success' & 'On
Fail' workflow. The Success could goto a SQL Task and do a count and log
through a sProc For eg:
EXEC sp_lo_insert_DTSlog ?, 'DTSEXECUTESTEPS', 'STATUS', 'DTS Package :
DTSName Completed Successfully'
If successgul the DTS will execute the next transformation step in the
sequence and the failure would execute an ActiveX Script Task.(see step2)
Step2: ActiveX Script Named Fail Step1
Function Main()
Dim oPackage
Set oPackage = DTSGlobalVariables.Parent
DTSGlobalVariables("gvPkgStatusDesc").Value = "ERROR : DTS Package " &
DTSGlobalVariables("gvPackageName").Value & _
" - Failed to load STAGING table TableName"
oPackage.Steps("DTSStep_DTSExecuteSQLTask_1").DisableStep = 0
Set oPackage = Nothing
Main = DTSTaskExecResult_Success
End Function
All you need is on http://www.sqldts.com/

Friday, March 9, 2012

DTS / Stored Proceedure

How can you run a DST package from a stored proceedure.
I am using sql server 2000
i cant find the syntax anywhere
it is a DTS that takes a file and imports it into a table in the dbi was looking around and someone said to try to run this at the sql prompt

EXEC xp_cmdshell 'dtsrun /S /U /P /N
exportEmail'

where exportEmail is the dts
when i do this though it says xp_cmdshell is a stored procedure that cant be found
is this something i have to make or is it something that should be there

http://www.dbforums.com/t688385.html|||i tried this method

http://p2p.wrox.com/topic.asp?TOPIC_ID=9741

and then it came back with "you dont have permission for xp_cmdShell" in short
where is this function located and where can i change permissions|||xp_cmdshell is found in the Master database. Seexp_cmdshell for more information.

Terri|||i set the permissions and i used the command

CREATE PROCEDURE MassImport AS
exec master..xp_cmdshell 'dtsrun /S /N /E'
GO

and i got the error

".Net SqlClient Data ProviderA severe error occurred on the current command. The results, if any, should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on link 472"

Is my stored proceedure correct
did I miss something does it not like the permissions
i am going to try to dig deeper into the link you gave me. But if someone knows a quick fix please tell me|||The problem I see with the stored procedure is that you did not supply the package name (exportEmail) with the /N switch, and you used the /S switch to indicate which server you were running against but did not supply the server name.

Note that you can run DTSRUN /? from the command prompt to see an explanation of each of the switches.

Terri|||Looking at the help in the command prompt this should be correct
my package name is "Mass Import" and this is a local sql server
Does this require no spaces in the package name or do i need quotes somewhere in the statement

here is my new stored procedure
and i get the same error message

exec master..xp_cmdshell 'dtsrun /S(local) /N(Mass Import) /E'

when i looked at the help /s and /n are both optional and /e is needed
/e seems to be the only one required.

i also tried it at a command prompt and this is the error i got.


H:\>dtsrun /S(local) /N(Mass Import) /E
DTSRun: Loading...Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
 Error string: The specified DTS Package ('Name = '(Mass Import) '; ID.Versio
nID = {[not specified]}.{[not specified]}') does not exist.
 Error source: Microsoft OLE DB Provider for SQL Server
 Help file:
 Help context: 0

|||Did you try enclosing your package name in double quotes?


H:\>dtsrun /S "(local)" /N "Mass Import" /E

Terri|||gives me the same error


H:\>dtsrun /S "(local)" /N "Mass Import" /E
DTSRun: Loading...Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
 Error string: The specified DTS Package ('Name = '"Mass Import" '; ID.Versio
nID = {[not specified]}.{[not specified]}') does not exist.
 Error source: Microsoft OLE DB Provider for SQL Server
 Help file:
 Help context: 0

|||I've tried that exact syntax and it works for me. So I would guess that either the Server is wrong or the Package Name is wrong.

You might try the dtsrunui.exe tool. This will enable you to visually set up your dtsrun statement.

From the command prompt, run this tool, select your Server from the drop-down (or type it in if it does not appear), choose the Authentication method, then choose the Package Name from the dropdown. When you have all of this set, click on the "Advanced..." button, and on this popup, click the "Generate..." button towards the bottom. This will drop in the generated dtsrun command in the box to the left. You should be able to use this generated command to run your package (by copying and pasting it into your stored procedure).

Terri|||that tool is cool thank you :)

here is the new command it made me run


DTSRun /S "(local)" /N "Mass Import" /G "{73D07FB3-E2D4-4A92-B69F-20C746F00DC1}" /W "0" /E

|||alright now my stored proceedure runs correctly in the command prompt and in the sql query analyzer
but for some reason it isnt working in my code

here is my code


 Dim cmdMassImport As New SqlCommand("MassImport", C.ndConnection)
 cmdMassImport.CommandType = CommandType.StoredProcedure C.ndConnection.Open()
 cmdMassImport.ExecuteNonQuery()
 C.ndConnection.Close()

Here is the error

A severe error occurred on the current command. The results, if any, should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line 472

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: A severe error occurred on the current command. The results, if any, should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line 472

Source Error:

Line 5760:
Line 5761: C.ndConnection.Open()
Line 5762: cmdMassImport.ExecuteNonQuery() <-- Says error is occuring here
Line 5763: C.ndConnection.Close()
Line 5764: End Sub

Source File: e:\Inetpub\wwwroot\Objectmanipulation\panelMaker.aspx.vb Line: 5762

Stack Trace:

[SqlException: A severe error occurred on the current command. The results, if any, should be discarded.
xpsql.cpp: Error 997 from GetProxyAccount on line 472
]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Objectmanipulation.panelMaker.btn_Click(Object sender, EventArgs e) in e:\Inetpub\wwwroot\Objectmanipulation\panelMaker.aspx.vb:5762
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()

Am I missing something stupid that I need to initialize to make the stored proceedure work?
or is there something deep that needs to be fixed|||Please??
does this seem to be a code thing
i cant seem to see that i missed anything with the stored proceedure call|||Ack, sorry, I didn't mean to leave you hanging. :-(

I have to try this out myself and get back to you -- I'll try not to make it later than tonight.

And if anyone has a suggestion -- please chime in!

Terri|||After playing around with this for a while and looking at Google, it's a permissions problem with xp_cmdshell. I am not going to be able to talk you through it because I don't thoroughly understand it -- the DBA at my place of employment set this up for me when I needed it.

Terri

Sunday, February 19, 2012

DTC issue or SQL Syntax issue

Hi All,
The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
server is SQL 2000 Enterprise SP4.
I am executing this sql in query analyzer:
create table #StoreInfoTraits (
[record type] varchar(3),
[site id] int,
[current effective date] smalldatetime,
[trait id] varchar(50),
[trait value] int
)
DECLARE @.cmd as varchar(200)
SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
insert into #StoreInfoTraits exec(@.cmd)
DROP TABLE #StoreInfoTraits
And getting this error:
OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
"The transaction manager has disabled its support for remote/network
transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "co-dbdev-01" was unable to begin a distributed transaction.
Iâ've checked the DTC services per the following link and both DTC services
are running under a domain account and configured properly.
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
This executes and returns the recordset:
Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
Since the remote stored proc returns the records when executing the stored
proc, is it a DTC issue or is this syntax correct ?
insert into #StoreInfoTraits exec(@.cmd)Hi
I think Bill's post covered most things in
http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
work OK then dtc itself can be ruled out.
Your syntax should work (well my test does on mine) providing dtc is running
correctly!
If you don't want to start a distributed transaction you could try
INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
storeinfo.dbo.pr_traitassignments' )
DECLARE @.cmd varchar(400)
SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
effective date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
storeinfo.dbo.pr_traitassignments'' ) '
EXEC ( @.cmd )
John
"brymer28303" wrote:
> Hi All,
> The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> server is SQL 2000 Enterprise SP4.
> I am executing this sql in query analyzer:
> create table #StoreInfoTraits (
> [record type] varchar(3),
> [site id] int,
> [current effective date] smalldatetime,
> [trait id] varchar(50),
> [trait value] int
> )
> DECLARE @.cmd as varchar(200)
> SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> insert into #StoreInfoTraits exec(@.cmd)
> DROP TABLE #StoreInfoTraits
> And getting this error:
> OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> "The transaction manager has disabled its support for remote/network
> transactions.".
> Msg 7391, Level 16, State 2, Line 1
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "co-dbdev-01" was unable to begin a distributed transaction.
> Iâ've checked the DTC services per the following link and both DTC services
> are running under a domain account and configured properly.
> http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> This executes and returns the recordset:
> Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> Since the remote stored proc returns the records when executing the stored
> proc, is it a DTC issue or is this syntax correct ?
> insert into #StoreInfoTraits exec(@.cmd)
>
>|||Thanks John. I tried this
INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ([CO-DBDEV-01], 'EXEC
storeinfo.dbo.pr_TraitAssignments' )
and got this:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC storeinfo.dbo.pr_TraitAssignments". The OLE
DB provider "SQLNCLI" for linked server "CO-DBDEV-01" indicates that either
the object has no columns or the current user does not have permissions on
that object.
I can execute this Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments.
and it returns:
TVA 50 08-30-2006 ACQUISITION 0
TVA 51 08-30-2006 ACQUISITION 0
TVA 52 08-30-2006 ACQUISITION 0
TVA 53 08-30-2006 ACQUISITION 0
TVA 54 08-30-2006 ACQUISITION 0
And, we apparently have a dtc issue I found when I ran the dtctester.exe --
unable to connect.
"John Bell" wrote:
> Hi
> I think Bill's post covered most things in
> http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
> work OK then dtc itself can be ruled out.
> Your syntax should work (well my test does on mine) providing dtc is running
> correctly!
> If you don't want to start a distributed transaction you could try
> INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
> storeinfo.dbo.pr_traitassignments' )
> DECLARE @.cmd varchar(400)
> SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
> effective date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
> storeinfo.dbo.pr_traitassignments'' ) '
> EXEC ( @.cmd )
> John
> "brymer28303" wrote:
> > Hi All,
> >
> > The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> > server is SQL 2000 Enterprise SP4.
> >
> > I am executing this sql in query analyzer:
> >
> > create table #StoreInfoTraits (
> > [record type] varchar(3),
> > [site id] int,
> > [current effective date] smalldatetime,
> > [trait id] varchar(50),
> > [trait value] int
> > )
> > DECLARE @.cmd as varchar(200)
> > SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> > insert into #StoreInfoTraits exec(@.cmd)
> > DROP TABLE #StoreInfoTraits
> >
> > And getting this error:
> >
> > OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> > "The transaction manager has disabled its support for remote/network
> > transactions.".
> > Msg 7391, Level 16, State 2, Line 1
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "co-dbdev-01" was unable to begin a distributed transaction.
> >
> > Iâ've checked the DTC services per the following link and both DTC services
> > are running under a domain account and configured properly.
> > http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> >
> > This executes and returns the recordset:
> > Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> >
> > Since the remote stored proc returns the records when executing the stored
> > proc, is it a DTC issue or is this syntax correct ?
> > insert into #StoreInfoTraits exec(@.cmd)
> >
> >
> >|||Hi
The user for the remote server will be determined by how the linked server
was created.
The dtctester error looks like you are not even making it to the remote
server! If you go through Bill's post and check out the networking issues it
may show something. You could also try DTCPing.
John
"brymer28303" wrote:
> Thanks John. I tried this
> INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ([CO-DBDEV-01], 'EXEC
> storeinfo.dbo.pr_TraitAssignments' )
> and got this:
> Msg 7357, Level 16, State 2, Line 1
> Cannot process the object "EXEC storeinfo.dbo.pr_TraitAssignments". The OLE
> DB provider "SQLNCLI" for linked server "CO-DBDEV-01" indicates that either
> the object has no columns or the current user does not have permissions on
> that object.
> I can execute this Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments.
> and it returns:
> TVA 50 08-30-2006 ACQUISITION 0
> TVA 51 08-30-2006 ACQUISITION 0
> TVA 52 08-30-2006 ACQUISITION 0
> TVA 53 08-30-2006 ACQUISITION 0
> TVA 54 08-30-2006 ACQUISITION 0
> And, we apparently have a dtc issue I found when I ran the dtctester.exe --
> unable to connect.
> "John Bell" wrote:
> > Hi
> >
> > I think Bill's post covered most things in
> > http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
> > work OK then dtc itself can be ruled out.
> >
> > Your syntax should work (well my test does on mine) providing dtc is running
> > correctly!
> >
> > If you don't want to start a distributed transaction you could try
> >
> > INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> > date], [trait id], [trait value] )
> > SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
> > storeinfo.dbo.pr_traitassignments' )
> >
> > DECLARE @.cmd varchar(400)
> > SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
> > effective date], [trait id], [trait value] )
> > SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
> > storeinfo.dbo.pr_traitassignments'' ) '
> >
> > EXEC ( @.cmd )
> >
> > John
> >
> > "brymer28303" wrote:
> >
> > > Hi All,
> > >
> > > The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> > > server is SQL 2000 Enterprise SP4.
> > >
> > > I am executing this sql in query analyzer:
> > >
> > > create table #StoreInfoTraits (
> > > [record type] varchar(3),
> > > [site id] int,
> > > [current effective date] smalldatetime,
> > > [trait id] varchar(50),
> > > [trait value] int
> > > )
> > > DECLARE @.cmd as varchar(200)
> > > SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> > > insert into #StoreInfoTraits exec(@.cmd)
> > > DROP TABLE #StoreInfoTraits
> > >
> > > And getting this error:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> > > "The transaction manager has disabled its support for remote/network
> > > transactions.".
> > > Msg 7391, Level 16, State 2, Line 1
> > > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > > linked server "co-dbdev-01" was unable to begin a distributed transaction.
> > >
> > > Iâ've checked the DTC services per the following link and both DTC services
> > > are running under a domain account and configured properly.
> > > http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> > >
> > > This executes and returns the recordset:
> > > Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> > >
> > > Since the remote stored proc returns the records when executing the stored
> > > proc, is it a DTC issue or is this syntax correct ?
> > > insert into #StoreInfoTraits exec(@.cmd)
> > >
> > >
> > >

Sunday, March 25, 2012

DTS Error checking

Friday, March 9, 2012

DTS / Stored Proceedure

Sunday, February 19, 2012

DTC issue or SQL Syntax issue

DTC issue or SQL Syntax issue

dts import agent

Blog Archive

About Me