Error when using SP in data flow

I want to execute a stored procedure in an OLE DB source in a Data Flow Task.

The stored procedure has a parameter.

When I put in an SQL command as :

EXEC sp_readcustomers 1

(thus passing 1 as the parameter value I can use the Preview button and I get a list of columns being returned.

The data flow task should run inside a ForEach Loop where I assign the value of an ADO resultset to a variable and it is this variable I want to pass to the SP :

EXEC sp_readcustomers ?

In the Parameter mapping I then name the parameter @.par_company_id (which is the exact same name as in the SP) and map it to the variable var_company_id.

@.par_company_id has been declared as int, var_company_id as Int16

When I now try to Parse or Preview the query from the OLE DB Source Edit window I get the following errors :

Parse : Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Preview : No value given for one or more required parameters (Microsoft SQL Native Client)

I have already installed SQL 2005 SP2 and VS2005 SP1.

I have tried everything I know, please help ?!?!

Have you tried putting the SQL Statment in a variable? You can parametrized queries using expressions in variables; then the source component will get the sql statemnt from the variable. There are a lot of example on this forum.|||

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

|||

Ronald Dirkx wrote:

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

To use the variable in an OLE DB Source, select the data access mode of "SQL command from variable." Then your variable will show up in the drop down.

Error when using more then one comment

Helo,
I=B4ve two comments and between them i put some code, but=20
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes=20
without any error but if i try to execute evrything it=20
shows me the error
When was created the second comment i've done it like in=20
the first part of code, this is not a problem but i dont=20
know why SQL shows this message to me!!!
Best regards
Sounds like you have a 'go' in the middle of a multi line comment... Query
Analyzer behaves poorly when this occurs... Use a single line comment -- to
comment out the go statement.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c03b01c42143$7e978080$a101280a@.phx.gbl...
Helo,
Ive two comments and between them i put some code, but
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes
without any error but if i try to execute evrything it
shows me the error
When was created the second comment i've done it like in
the first part of code, this is not a problem but i dont
know why SQL shows this message to me!!!
Best regards
|||Thanks Wayne, you're right
Best regards

>--Original Message--
>Sounds like you have a 'go' in the middle of a multi line=20
comment... Query
>Analyzer behaves poorly when this occurs... Use a single=20
line comment -- to
>comment out the go statement.
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:1c03b01c42143$7e978080$a101280a@.phx.gbl...
>Helo,
>I=B4ve two comments and between them i put some code, but
>when i execute Query Analyser show the following error
>"Server: Msg 113, Level 15, State 1, Line 40
>Missing end comment mark '*/'.
>Server: Msg 170, Level 15, State 1, Line 34
>Line 34: Incorrect syntax near '/'."
>If i execute the code with the first comment it executes
>without any error but if i try to execute evrything it
>shows me the error
>When was created the second comment i've done it like in
>the first part of code, this is not a problem but i dont
>know why SQL shows this message to me!!!
>Best regards
>
>.
>
sql

Error when using more then one comment

Helo,
I=B4ve two comments and between them i put some code, but=20
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes=20
without any error but if i try to execute evrything it=20
shows me the error
When was created the second comment i've done it like in=20
the first part of code, this is not a problem but i dont=20
know why SQL shows this message to me!!!
Best regardsSounds like you have a 'go' in the middle of a multi line comment... Query
Analyzer behaves poorly when this occurs... Use a single line comment -- to
comment out the go statement.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c03b01c42143$7e978080$a101280a@.phx
.gbl...
Helo,
Ive two comments and between them i put some code, but
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes
without any error but if i try to execute evrything it
shows me the error
When was created the second comment i've done it like in
the first part of code, this is not a problem but i dont
know why SQL shows this message to me!!!
Best regards|||Thanks Wayne, you're right
Best regards

>--Original Message--
>Sounds like you have a 'go' in the middle of a multi line=20
comment... Query
>Analyzer behaves poorly when this occurs... Use a single=20
line comment -- to
>comment out the go statement.
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message
> news:1c03b01c42143$7e978080$a101280a@.phx
.gbl...
>Helo,
>I=B4ve two comments and between them i put some code, but
>when i execute Query Analyser show the following error
>"Server: Msg 113, Level 15, State 1, Line 40
>Missing end comment mark '*/'.
>Server: Msg 170, Level 15, State 1, Line 34
>Line 34: Incorrect syntax near '/'."
>If i execute the code with the first comment it executes
>without any error but if i try to execute evrything it
>shows me the error
>When was created the second comment i've done it like in
>the first part of code, this is not a problem but i dont
>know why SQL shows this message to me!!!
>Best regards
>
>.
>

Error when using more then one comment

Helo,
I=B4ve two comments and between them i put some code, but when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes without any error but if i try to execute evrything it shows me the error
When was created the second comment i've done it like in the first part of code, this is not a problem but i dont know why SQL shows this message to me!!!
Best regardsSounds like you have a 'go' in the middle of a multi line comment... Query
Analyzer behaves poorly when this occurs... Use a single line comment -- to
comment out the go statement.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c03b01c42143$7e978080$a101280a@.phx.gbl...
Helo,
I´ve two comments and between them i put some code, but
when i execute Query Analyser show the following error
"Server: Msg 113, Level 15, State 1, Line 40
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '/'."
If i execute the code with the first comment it executes
without any error but if i try to execute evrything it
shows me the error
When was created the second comment i've done it like in
the first part of code, this is not a problem but i dont
know why SQL shows this message to me!!!
Best regards|||Thanks Wayne, you're right
Best regards
>--Original Message--
>Sounds like you have a 'go' in the middle of a multi line comment... Query
>Analyzer behaves poorly when this occurs... Use a single line comment -- to
>comment out the go statement.
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
>news:1c03b01c42143$7e978080$a101280a@.phx.gbl...
>Helo,
>I=B4ve two comments and between them i put some code, but
>when i execute Query Analyser show the following error
>"Server: Msg 113, Level 15, State 1, Line 40
>Missing end comment mark '*/'.
>Server: Msg 170, Level 15, State 1, Line 34
>Line 34: Incorrect syntax near '/'."
>If i execute the code with the first comment it executes
>without any error but if i try to execute evrything it
>shows me the error
>When was created the second comment i've done it like in
>the first part of code, this is not a problem but i dont
>know why SQL shows this message to me!!!
>Best regards
>
>.
>

Error when updating

I get the following error when I execute the statement below. 'Invalid
length parameter passed to the substring function'
Update Tbl_Txn_UT
Set Fxd_Units =
CASE
WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%' and TxnUnitsDur like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%')
THEN TxnUnitsBase
WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
TxnUnitsBase
Else TxnUnits
END
Where TxnUnits is not Null
It took a while to get this code correct with help and now evidently one of
the answers from the math causes an error. Any help is appreciated.Hi
Posting DDL an example data would help to see what you are trying to achive
and help re-create the problem. The statement is assuming that TxnUnitsDur i
s
at least one character which could is probably causing the problem.
John
"Job" wrote:

> I get the following error when I execute the statement below. 'Invalid
> length parameter passed to the substring function'
> Update Tbl_Txn_UT
> Set Fxd_Units =
> CASE
> WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%' and TxnUnitsDur like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
> WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%')
> THEN TxnUnitsBase
> WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
> TxnUnitsBase
> Else TxnUnits
> END
> Where TxnUnits is not Null
>
> It took a while to get this code correct with help and now evidently one o
f
> the answers from the math causes an error. Any help is appreciated.
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Ju
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Hi
You seem to have some problem posting?
John
"Job" <Job@.nomail.com> wrote in message
news:uUH$jpCoFHA.708@.TK2MSFTNGP09.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>|||John, your correct. I included the case statement if len(TxnUnitsDur )
= 0 then TxnUnitsBase and it worked perfectly.
Cheers!
Job wrote:
> Ju
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>
>
>

Error when trying to execute a job from windows service.

Hi,
I'm trying to execute a DTS package from a .NET service. The service is
running under a domain account that owns the job which it executes using
'sp_start_job'. The call to the sp succeeds however checking the Job history
gives: -
"The job failed. Unable to determine if the owner (DOMAIN\ServiceUser) of
job <JobName> has server access (reason: Could not obtain information about
Windows NT group/user 'DOMAIN\ServiceUser'. [SQLSTATE 42000] (Error 8198
))."
SQL Server 2000 SP4
Both SQLServer + SQLAgent run under a domain account (different to the
service)
ServiceUser has login to sql server not an admin, however I've tried using
the SQL Agent Proxy account still no success.
Any ideas?
thanks Paul> SQL Server 2000 SP4
> Both SQLServer + SQLAgent run under a domain account (different to the
> service)
> ServiceUser has login to sql server not an admin, however I've tried using
> the SQL Agent Proxy account still no success.
The SQL 2000 Books Online <instsql.chm::/in_overview_6k1f.htm> states that
the SQL Agent service account must be a member of the local Administrators
group in order to "Create CmdExec and ActiveScript jobs belonging to someone
other than a SQL Server administrator".
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Coulson" <nospam@.nospam.net> wrote in message
news:OZUDQ3WOGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm trying to execute a DTS package from a .NET service. The service is
> running under a domain account that owns the job which it executes using
> 'sp_start_job'. The call to the sp succeeds however checking the Job
> history gives: -
> "The job failed. Unable to determine if the owner (DOMAIN\ServiceUser) of
> job <JobName> has server access (reason: Could not obtain information
> about Windows NT group/user 'DOMAIN\ServiceUser'. [SQLSTATE 42000] (Er
ror
> 8198))."
> SQL Server 2000 SP4
> Both SQLServer + SQLAgent run under a domain account (different to the
> service)
> ServiceUser has login to sql server not an admin, however I've tried using
> the SQL Agent Proxy account still no success.
> Any ideas?
> thanks Paul
>|||Hi Dan,
Thanks that's sorted it. Also got it working using the proxy account
again this needed local admin rights.
Cheers Paul
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ueU6PriOGHA.1192@.TK2MSFTNGP11.phx.gbl...
> The SQL 2000 Books Online <instsql.chm::/in_overview_6k1f.htm> states that
> the SQL Agent service account must be a member of the local Administrators
> group in order to "Create CmdExec and ActiveScript jobs belonging to
> someone other than a SQL Server administrator".
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Coulson" <nospam@.nospam.net> wrote in message
> news:OZUDQ3WOGHA.3144@.TK2MSFTNGP11.phx.gbl...
>|||I'm glad you were able to get it working. However, I'd be leery about
granting admin rights the proxy account since that allows non-sysadmins to
run processes with admin rights. Now that you got the service accounts
sorted out, I would expect your DTS package to run fine as long as the proxy
account has permissions on data and files accessed by the package.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Coulson" <nospam@.nospam.net> wrote in message
news:eCdaOR4OGHA.812@.TK2MSFTNGP10.phx.gbl...
> Hi Dan,
> Thanks that's sorted it. Also got it working using the proxy account
> again this needed local admin rights.
> Cheers Paul
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:ueU6PriOGHA.1192@.TK2MSFTNGP11.phx.gbl...
>

Error when trying to create new SQL Server login via ado.Net

I get an error when I try to execute the following code using ADO.Net
in VB.Net:
conn.open
sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'"
command=new SqlCommand(sqlString)
command.connection = conn
command.ExecuteNonQuery()
The error occurs when executing the ExecuteNonQuery line and is:
"Line1: Incorrect syntax near 'LOGIN' "
The database connection is connected to the master database of SQL 2005
Express using the sa account. This line executes just fine in Sql
Server Studio Manger when connected as sa. Am I not allowed to create
database logins via ado.net?!?! I need to for my application.
Thx,
MarcusHi
It seems to be something wrong with quoatations. Print the sqlString and see
if you can execute it in QA
"holysmokes99" <holysmokes99@.hotmail.com> wrote in message
news:1143588567.138045.218660@.j33g2000cwa.googlegroups.com...
>I get an error when I try to execute the following code using ADO.Net
> in VB.Net:
> conn.open
> sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'"
> command=new SqlCommand(sqlString)
> command.connection = conn
> command.ExecuteNonQuery()
> The error occurs when executing the ExecuteNonQuery line and is:
> "Line1: Incorrect syntax near 'LOGIN' "
> The database connection is connected to the master database of SQL 2005
> Express using the sa account. This line executes just fine in Sql
> Server Studio Manger when connected as sa. Am I not allowed to create
> database logins via ado.net?!?! I need to for my application.
> Thx,
> Marcus
>|||I suggest you double check your connection string to make sure you are
connected to the SQL Express instance since this message indicates you are
connected to a pre-SQL 2000 instance. You can insert code like the example
below to query the instance version. My SQL Express instance returns
"9.00.1399.06".
command=new SqlCommand("SELECT SERVERPROPERTY('ProductVersion')")
Dim version as String = command.ExecuteScalar().ToString()
MessageBox.Show(version)
Hope this helps.
Dan Guzman
SQL Server MVP
"holysmokes99" <holysmokes99@.hotmail.com> wrote in message
news:1143588567.138045.218660@.j33g2000cwa.googlegroups.com...
>I get an error when I try to execute the following code using ADO.Net
> in VB.Net:
> conn.open
> sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'"
> command=new SqlCommand(sqlString)
> command.connection = conn
> command.ExecuteNonQuery()
> The error occurs when executing the ExecuteNonQuery line and is:
> "Line1: Incorrect syntax near 'LOGIN' "
> The database connection is connected to the master database of SQL 2005
> Express using the sa account. This line executes just fine in Sql
> Server Studio Manger when connected as sa. Am I not allowed to create
> database logins via ado.net?!?! I need to for my application.
> Thx,
> Marcus
>|||> It seems to be something wrong with quoatations. Print the sqlString and
> see if you can execute it in QA
No need to print the SQL statement here since it is in clear text in Marcus'
code snippet:
CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eVeLQavUGHA.4436@.TK2MSFTNGP10.phx.gbl...
> Hi
> It seems to be something wrong with quoatations. Print the sqlString and
> see if you can execute it in QA
>
> "holysmokes99" <holysmokes99@.hotmail.com> wrote in message
> news:1143588567.138045.218660@.j33g2000cwa.googlegroups.com...
>|||Hi,Dan
Yep, I got it. thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uahPs5yUGHA.5900@.tk2msftngp13.phx.gbl...
> No need to print the SQL statement here since it is in clear text in
> Marcus' code snippet:
> CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVeLQavUGHA.4436@.TK2MSFTNGP10.phx.gbl...
>|||Oh, Jeesh, My bad!!! Dan you were spot on. I was connecting to an
instance of SQL Server 2000, and not Express. I have both on my machine
and forgot to reference it as "Marcus\SQLEXPRESS", instead using
"Marcus" only. CREATE LOGIN is new for SQL Server 2005 and thus I got a
syntax error when connecting to SQL 2000. Thanks for your insight.
On a sort-of-related note, is there a way for me to iterate through all
the SQL Server Express instances on the network? Using SQL-DMO, the
code below gives me the names of the servers, but some of the SQL
Express servers are named "[server_name]\SQLEXPRESS", while others are
just "[server_name]" (even though they do not have anything other that
SQL Express on them). If they were all suffixed with "SQLEXPRESS" then
that would be easy, but it doesn't seem to work that way.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Dim oServer As SQLDMO.SQLServer2
oSQLApp = New SQLDMO.Application
oNames = oSQLApp.ListAvailableSQLServers
For i As Integer = 1 To oNames.Count
TextBox1.AppendText(oNames.Item(i) + Environment.NewLine)
Next
End sub
Thanks!
Marcus|||Check the following:
1) SQL Brower service is enabled and running
2) Port 1434 isn't blocked (e.g. Windows firewall)
3) The SQL Express instance is configured for remote connections
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1143659183.191249.143360@.e56g2000cwe.googlegroups.com...
> Oh, Jeesh, My bad!!! Dan you were spot on. I was connecting to an
> instance of SQL Server 2000, and not Express. I have both on my machine
> and forgot to reference it as "Marcus\SQLEXPRESS", instead using
> "Marcus" only. CREATE LOGIN is new for SQL Server 2005 and thus I got a
> syntax error when connecting to SQL 2000. Thanks for your insight.
> On a sort-of-related note, is there a way for me to iterate through all
> the SQL Server Express instances on the network? Using SQL-DMO, the
> code below gives me the names of the servers, but some of the SQL
> Express servers are named "[server_name]\SQLEXPRESS", while others are
> just "[server_name]" (even though they do not have anything other that
> SQL Express on them). If they were all suffixed with "SQLEXPRESS" then
> that would be easy, but it doesn't seem to work that way.
> Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button2.Click
> Dim oNames As SQLDMO.NameList
> Dim oSQLApp As SQLDMO.Application
> Dim oServer As SQLDMO.SQLServer2
> oSQLApp = New SQLDMO.Application
> oNames = oSQLApp.ListAvailableSQLServers
> For i As Integer = 1 To oNames.Count
> TextBox1.AppendText(oNames.Item(i) + Environment.NewLine)
> Next
> End sub
>
> Thanks!
> Marcus
>|||Thanks, Dan. You were right again. On the machine with SQL Express
only, Windows Firewall was on. When I disabled it, the server name and
sql server instancen name came up when I ran the code above, i.e.
"training\sqlexpress", not just the server name as before when firewall
was active. Why did the server name come up at all then when the
firewall was enabled, when using the SQL-DMO code above?
Cheers,
Marcus|||> Why did the server name come up at all then when the
> firewall was enabled, when using the SQL-DMO code above?
IIRC, before SQL Server 2000, network broadcast packets sent to facilitate
server enumeration. According to Perter's blog
(http://blogs.msdn.com/sql_protocols.../24/473502.aspx), legacy
behavior can show default instances even with UDP 1434 blocked.
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1143736505.471840.78380@.i39g2000cwa.googlegroups.com...
> Thanks, Dan. You were right again. On the machine with SQL Express
> only, Windows Firewall was on. When I disabled it, the server name and
> sql server instancen name came up when I ran the code above, i.e.
> "training\sqlexpress", not just the server name as before when firewall
> was active. Why did the server name come up at all then when the
> firewall was enabled, when using the SQL-DMO code above?
> Cheers,
> Marcus
>sql

Error when schedule a job in SQL 2005

Hi,

I have a SSIS package that contains an Execute DTS 2000 process. This DTS 2000 is calling a legacy package, which resides on the same server. The legacy package is just to import a flat file into the database. When i right click and execute this package, it runs perfectly without any errors. However, if i try to schedule this package as a job, it keeps failing.

I checked the flat file source for the permission, it does have the account of the executor of the package. In the view history, i didn't see any more information about why it is failing. It keeps saying "The job failed. The Job was invoked by Schedule 10...."

Does anyone have any ideas?

Did you try to turn on logging so you could get better error details?|||

Thanks, I turned on the log and it wrote to the application log events saying. Is there another log event in SQL 2005? I only saw it said "View History" in the scheduled job.

Status: Failed - Invoked on: 2006-11-06 13:56:00 - Message: The job failed. The Job was invoked by Schedule 24 (Test Schedule). The last step to run was step 1 (RUN ImportData)

|||See this KB about turning on logging and general troubleshooting of SSIS packages under Agent:
http://support.microsoft.com/kb/918760

Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when issuing OPENQUERY

I'm running on a Windows 2003 server SQL Server 2000. I'm getting the
following error when trying to execute an OPENQUERY statement. I have
about 5 linked servers, and they all worked in the past. Now one of
them is giving me trouble when issuing the OPENQUERY statement with
the following error:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I've tried all the Microsoft Workaround solutions:
http://support.microsoft.com/kb/873160 (even if it doesn't apply 100%)
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
http://support.microsoft.com/default.aspx?scid=kb;en-us;816701
http://support.microsoft.com/?kbid=816701
The linked server has not changed according to the administrator.
There is no firewall under either side of the severs (internal private
network). After enabling DTC under the remote server, I've noticed
that the queries take longer, but they continue returning the same
error as indicated above.
The servers have been configured with 'No authentication on DTC' so I
can't imagine what the problem could be, I've also checked the routing
(IP routes) between the two servers and I can't see any flaw, all
traffic is internal. I say this because the servers host dual cards
(internal and external networks) - but the external network is not
being enabled. Bidirectional traceroutes show the same path.
I've Googled the error messages and they all point to the same thing,
but it appears not to work. I have other SQL servers under the same
internal networks and I can run the OPENQUERY on them.
Can anyone help me out here?
Hi
The last two links you posted are the same!
Have you tried DTCping? http://support.microsoft.com/kb/306843
John
"InvestorTrade" wrote:

> I'm running on a Windows 2003 server SQL Server 2000. I'm getting the
> following error when trying to execute an OPENQUERY statement. I have
> about 5 linked servers, and they all worked in the past. Now one of
> them is giving me trouble when issuing the OPENQUERY statement with
> the following error:
> Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I've tried all the Microsoft Workaround solutions:
> http://support.microsoft.com/kb/873160 (even if it doesn't apply 100%)
> http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
> http://support.microsoft.com/default.aspx?scid=kb;en-us;816701
> http://support.microsoft.com/?kbid=816701
> The linked server has not changed according to the administrator.
> There is no firewall under either side of the severs (internal private
> network). After enabling DTC under the remote server, I've noticed
> that the queries take longer, but they continue returning the same
> error as indicated above.
> The servers have been configured with 'No authentication on DTC' so I
> can't imagine what the problem could be, I've also checked the routing
> (IP routes) between the two servers and I can't see any flaw, all
> traffic is internal. I say this because the servers host dual cards
> (internal and external networks) - but the external network is not
> being enabled. Bidirectional traceroutes show the same path.
> I've Googled the error messages and they all point to the same thing,
> but it appears not to work. I have other SQL servers under the same
> internal networks and I can run the OPENQUERY on them.
> Can anyone help me out here?
>
>
|||On Jan 7, 12:33Xpm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> The last two links you posted are the same!
> Have you tried DTCping?http://support.microsoft.com/kb/306843
> John
>
> "InvestorTrade" wrote:
>
>
>
>
> - Show quoted text -
Thank you!!! DTCPing helped to spot the problem - Bottom line - the
RPC Locator was shutdown under the remote server - the moment I
enabled it, it worked like a charm.

Error when issuing OPENQUERY

I'm running on a Windows 2003 server SQL Server 2000. I'm getting the
following error when trying to execute an OPENQUERY statement. I have
about 5 linked servers, and they all worked in the past. Now one of
them is giving me trouble when issuing the OPENQUERY statement with
the following error:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I've tried all the Microsoft Workaround solutions:
http://support.microsoft.com/kb/873160 (even if it doesn't apply 100%)
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
http://support.microsoft.com/default.aspx?scid=kb;en-us;816701
http://support.microsoft.com/?kbid=816701
The linked server has not changed according to the administrator.
There is no firewall under either side of the severs (internal private
network). After enabling DTC under the remote server, I've noticed
that the queries take longer, but they continue returning the same
error as indicated above.
The servers have been configured with 'No authentication on DTC' so I
can't imagine what the problem could be, I've also checked the routing
(IP routes) between the two servers and I can't see any flaw, all
traffic is internal. I say this because the servers host dual cards
(internal and external networks) - but the external network is not
being enabled. Bidirectional traceroutes show the same path.
I've Googled the error messages and they all point to the same thing,
but it appears not to work. I have other SQL servers under the same
internal networks and I can run the OPENQUERY on them.
Can anyone help me out here?Hi
The last two links you posted are the same!
Have you tried DTCping? http://support.microsoft.com/kb/306843
John
"InvestorTrade" wrote:
> I'm running on a Windows 2003 server SQL Server 2000. I'm getting the
> following error when trying to execute an OPENQUERY statement. I have
> about 5 linked servers, and they all worked in the past. Now one of
> them is giving me trouble when issuing the OPENQUERY statement with
> the following error:
> Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I've tried all the Microsoft Workaround solutions:
> http://support.microsoft.com/kb/873160 (even if it doesn't apply 100%)
> http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
> http://support.microsoft.com/default.aspx?scid=kb;en-us;816701
> http://support.microsoft.com/?kbid=816701
> The linked server has not changed according to the administrator.
> There is no firewall under either side of the severs (internal private
> network). After enabling DTC under the remote server, I've noticed
> that the queries take longer, but they continue returning the same
> error as indicated above.
> The servers have been configured with 'No authentication on DTC' so I
> can't imagine what the problem could be, I've also checked the routing
> (IP routes) between the two servers and I can't see any flaw, all
> traffic is internal. I say this because the servers host dual cards
> (internal and external networks) - but the external network is not
> being enabled. Bidirectional traceroutes show the same path.
> I've Googled the error messages and they all point to the same thing,
> but it appears not to work. I have other SQL servers under the same
> internal networks and I can run the OPENQUERY on them.
> Can anyone help me out here?
>
>|||On Jan 7, 12:33=A0pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> The last two links you posted are the same!
> Have you tried DTCping?http://support.microsoft.com/kb/306843
> John
>
> "InvestorTrade" wrote:
> > I'm running on a Windows 2003 server SQL Server 2000. =A0I'm getting the=
> > following error when trying to execute an OPENQUERY statement. =A0I have=
> > about 5 linked servers, and they all worked in the past. =A0Now one of
> > them is giving me trouble when issuing the OPENQUERY statement with
> > the following error:
> > Server: Msg 7391, Level 16, State 1, Line 1
> > The operation could not be performed because the OLE DB provider
> > 'SQLOLEDB' was unable to begin a distributed transaction.
> > [OLE/DB provider returned message: New transaction cannot enlist in
> > the specified transaction coordinator. ]
> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> > ITransactionJoin::JoinTransaction returned 0x8004d00a].
> > I've tried all the Microsoft Workaround solutions:
> >http://support.microsoft.com/kb/873160(even if it doesn't apply 100%)
> >http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;329332
> >http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;816701
> >http://support.microsoft.com/?kbid=3D816701
> > The linked server has not changed according to the administrator.
> > There is no firewall under either side of the severs (internal private
> > network). =A0After enabling DTC under the remote server, I've noticed
> > that the queries take longer, but they continue returning the same
> > error as indicated above.
> > The servers have been configured with 'No authentication on DTC' so I
> > can't imagine what the problem could be, I've also checked the routing
> > (IP routes) between the two servers and I can't see any flaw, all
> > traffic is internal. =A0I say this because the servers host dual cards
> > (internal and external networks) - but the external network is not
> > being enabled. =A0Bidirectional traceroutes show the same path.
> > I've Googled the error messages and they all point to the same thing,
> > but it appears not to work. =A0I have other SQL servers under the same
> > internal networks and I can run the OPENQUERY on them.
> > Can anyone help me out here... Hide quoted text -
> - Show quoted text -
Thank you!!! DTCPing helped to spot the problem - Bottom line - the
RPC Locator was shutdown under the remote server - the moment I
enabled it, it worked like a charm.

Error when issuing BACKUP LOG mydb with truncate_only

Hi,

Following error appears in the SQL Server error log when I execute BACKUP Log db with truncate_only using a stored procedure. The stored proc I am using is as follows:

CREATE procedure spm_tranlog as

declare @.DBName as Varchar(120)

select @.DBName = DB_name()

dump transaction @.DBName with truncate_only

GO

There are data import processes running in the night, before starting the processes we are executing spm_tranlog procedure to clear the transaction logs. The following error appears in log after executing the spm_tranlog:

“BACKUP failed to complete the command exec spm_tranlog”

This always happens after weekly server maintenance tasks. The scheduled maintenance tasks I am running are:Index rebuild, truncate log and shrinking database. There were no other processes running during that time when the spm_tranlog prodecure fail.

The Database size is 40GB, Log size is 80MB.

The database is in the simple recovery model.The database is in the simple recovery model.

I am running SQL Server 2000 (SP4) on windows server 2003.

Please help me to resolve this issue.

Thanks,

Roshan.

Hello Roshan,

Try this instead:

create procedure spm_tranlog

as

declare @.DBName asvarchar(120), @.sql asvarchar(200)

select @.DBName =DB_name()

select @.sql ='backup log '+ @.DBName +' with truncate_only'

execute(@.sql)
go

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply. I have changed the DUMP TRANSACTION to BACKUP LOG in my stored proc, but still shows same error message.

Usualy the dump transaction (or Backup Log) fails only when server is backing up databases or shrinking.

My problem is Backup Log satement fails even there are no database backups running. This is really strange.

Thanks,

Roshan.

|||

Can you run the backup log statement directly without getting an error?

backup log DB_Name with truncate_only

Jarret

|||

No. its not allowing me to run the backup log statement directly.

Roshan.

|||

Can you post the error message you are getting and the exact statement you are running?

Are you sure you have permissions to do this? You need to be a sysadmin on the server or in the db_owner role of the database.

Jarret

|||

Hi Jarret,

I am receiving following error in the SQL server log: "BACKUP failed to complete the command exec spm_tranlog"

and I am running following stored proc:

CREATE procedure spm_tranlog as

/* 29/03/2006 RXP: Modified to get the database name using system function
* DB_name(), to avoid hardcoding database name.
*
*/

declare @.DBName as Varchar(120)

select @.DBName = DB_name()
dump transaction @.DBName with truncate_only
GO

This was running perfecctly for a long time, suddenly start creating problems. The user running this proc has sysadmin rights.

Thanks,

Roshan.

|||

How are you running the stored procedure, just 'exec spm_tranlog' from a new query window?

Bypassing the stored procedure, can you try to run this:

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
dump transaction @.DBName with truncate_only

And also...

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
backup log @.DBName with truncate_only

If these still don't work, post the error message(s) you get from each.

Jarret

|||You cant truncate the Log with SIMPLE PLAN in a DB. Because it is doing by itself.

Error when issuing BACKUP LOG mydb with truncate_only

Hi,

Following error appears in the SQL Server error log when I execute BACKUP Log db with truncate_only using a stored procedure. The stored proc I am using is as follows:

CREATE procedure spm_tranlog as

declare @.DBName as Varchar(120)

select @.DBName = DB_name()

dump transaction @.DBName with truncate_only

GO

There are data import processes running in the night, before starting the processes we are executing spm_tranlog procedure to clear the transaction logs. The following error appears in log after executing the spm_tranlog:

“BACKUP failed to complete the command exec spm_tranlog”

This always happens after weekly server maintenance tasks. The scheduled maintenance tasks I am running are:Index rebuild, truncate log and shrinking database. There were no other processes running during that time when the spm_tranlog prodecure fail.

The Database size is 40GB, Log size is 80MB.

The database is in the simple recovery model.The database is in the simple recovery model.

I am running SQL Server 2000 (SP4) on windows server 2003.

Please help me to resolve this issue.

Thanks,

Roshan.

Hello Roshan,

Try this instead:

create procedure spm_tranlog

as

declare @.DBName asvarchar(120), @.sql asvarchar(200)

select @.DBName =DB_name()

select @.sql ='backup log '+ @.DBName +' with truncate_only'

execute(@.sql)
go

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply. I have changed the DUMP TRANSACTION to BACKUP LOG in my stored proc, but still shows same error message.

Usualy the dump transaction (or Backup Log) fails only when server is backing up databases or shrinking.

My problem is Backup Log satement fails even there are no database backups running. This is really strange.

Thanks,

Roshan.

|||

Can you run the backup log statement directly without getting an error?

backup log DB_Name with truncate_only

Jarret

|||

No. its not allowing me to run the backup log statement directly.

Roshan.

|||

Can you post the error message you are getting and the exact statement you are running?

Are you sure you have permissions to do this? You need to be a sysadmin on the server or in the db_owner role of the database.

Jarret

|||

Hi Jarret,

I am receiving following error in the SQL server log: "BACKUP failed to complete the command exec spm_tranlog"

and I am running following stored proc:

CREATE procedure spm_tranlog as

/* 29/03/2006 RXP: Modified to get the database name using system function
* DB_name(), to avoid hardcoding database name.
*
*/

declare @.DBName as Varchar(120)

select @.DBName = DB_name()
dump transaction @.DBName with truncate_only
GO

This was running perfecctly for a long time, suddenly start creating problems. The user running this proc has sysadmin rights.

Thanks,

Roshan.

|||

How are you running the stored procedure, just 'exec spm_tranlog' from a new query window?

Bypassing the stored procedure, can you try to run this:

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
dump transaction @.DBName with truncate_only

And also...

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
backup log @.DBName with truncate_only

If these still don't work, post the error message(s) you get from each.

Jarret

|||You cant truncate the Log with SIMPLE PLAN in a DB. Because it is doing by itself.

Error when issuing BACKUP LOG mydb with truncate_only

Hi,

Following error appears in the SQL Server error log when I execute BACKUP Log db with truncate_only using a stored procedure. The stored proc I am using is as follows:

CREATE procedure spm_tranlog as

declare @.DBName as Varchar(120)

select @.DBName = DB_name()

dump transaction @.DBName with truncate_only

GO

There are data import processes running in the night, before starting the processes we are executing spm_tranlog procedure to clear the transaction logs. The following error appears in log after executing the spm_tranlog:

“BACKUP failed to complete the command exec spm_tranlog”

This always happens after weekly server maintenance tasks. The scheduled maintenance tasks I am running are:Index rebuild, truncate log and shrinking database. There were no other processes running during that time when the spm_tranlog prodecure fail.

The Database size is 40GB, Log size is 80MB.

The database is in the simple recovery model.The database is in the simple recovery model.

I am running SQL Server 2000 (SP4) on windows server 2003.

Please help me to resolve this issue.

Thanks,

Roshan.

Hello Roshan,

Try this instead:

create procedure spm_tranlog

as

declare @.DBName asvarchar(120), @.sql asvarchar(200)

select @.DBName =DB_name()

select @.sql ='backup log '+ @.DBName +' with truncate_only'

execute(@.sql)
go

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply. I have changed the DUMP TRANSACTION to BACKUP LOG in my stored proc, but still shows same error message.

Usualy the dump transaction (or Backup Log) fails only when server is backing up databases or shrinking.

My problem is Backup Log satement fails even there are no database backups running. This is really strange.

Thanks,

Roshan.

|||

Can you run the backup log statement directly without getting an error?

backup log DB_Name with truncate_only

Jarret

|||

No. its not allowing me to run the backup log statement directly.

Roshan.

|||

Can you post the error message you are getting and the exact statement you are running?

Are you sure you have permissions to do this? You need to be a sysadmin on the server or in the db_owner role of the database.

Jarret

|||

Hi Jarret,

I am receiving following error in the SQL server log: "BACKUP failed to complete the command exec spm_tranlog"

and I am running following stored proc:

CREATE procedure spm_tranlog as

/* 29/03/2006 RXP: Modified to get the database name using system function
* DB_name(), to avoid hardcoding database name.
*
*/

declare @.DBName as Varchar(120)

select @.DBName = DB_name()
dump transaction @.DBName with truncate_only
GO

This was running perfecctly for a long time, suddenly start creating problems. The user running this proc has sysadmin rights.

Thanks,

Roshan.

|||

How are you running the stored procedure, just 'exec spm_tranlog' from a new query window?

Bypassing the stored procedure, can you try to run this:

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
dump transaction @.DBName with truncate_only

And also...

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
backup log @.DBName with truncate_only

If these still don't work, post the error message(s) you get from each.

Jarret

|||You cant truncate the Log with SIMPLE PLAN in a DB. Because it is doing by itself.sql

Error when issuing BACKUP LOG mydb with truncate_only

Hi,

Following error appears in the SQL Server error log when I execute BACKUP Log db with truncate_only using a stored procedure. The stored proc I am using is as follows:

CREATE procedure spm_tranlog as

declare @.DBName as Varchar(120)

select @.DBName = DB_name()

dump transaction @.DBName with truncate_only

GO

There are data import processes running in the night, before starting the processes we are executing spm_tranlog procedure to clear the transaction logs. The following error appears in log after executing the spm_tranlog:

“BACKUP failed to complete the command exec spm_tranlog”

This always happens after weekly server maintenance tasks. The scheduled maintenance tasks I am running are: Index rebuild, truncate log and shrinking database. There were no other processes running during that time when the spm_tranlog prodecure fail.

The Database size is 40GB, Log size is 80MB.

The database is in the simple recovery model.The database is in the simple recovery model.

I am running SQL Server 2000 (SP4) on windows server 2003.

Please help me to resolve this issue.

Thanks,

Roshan.

Hello Roshan,

Try this instead:

create procedure spm_tranlog

as

declare @.DBName as varchar(120), @.sql as varchar(200)

select @.DBName = DB_name()

select @.sql = 'backup log ' + @.DBName + ' with truncate_only'

execute (@.sql)
go

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply. I have changed the DUMP TRANSACTION to BACKUP LOG in my stored proc, but still shows same error message.

Usualy the dump transaction (or Backup Log) fails only when server is backing up databases or shrinking.

My problem is Backup Log satement fails even there are no database backups running. This is really strange.

Thanks,

Roshan.

|||

Can you run the backup log statement directly without getting an error?

backup log DB_Name with truncate_only

Jarret

|||

No. its not allowing me to run the backup log statement directly.

Roshan.

|||

Can you post the error message you are getting and the exact statement you are running?

Are you sure you have permissions to do this? You need to be a sysadmin on the server or in the db_owner role of the database.

Jarret

|||

Hi Jarret,

I am receiving following error in the SQL server log: "BACKUP failed to complete the command exec spm_tranlog"

and I am running following stored proc:

CREATE procedure spm_tranlog as

/* 29/03/2006 RXP: Modified to get the database name using system function
* DB_name(), to avoid hardcoding database name.
*
*/

declare @.DBName as Varchar(120)

select @.DBName = DB_name()
dump transaction @.DBName with truncate_only
GO

This was running perfecctly for a long time, suddenly start creating problems. The user running this proc has sysadmin rights.

Thanks,

Roshan.

|||

How are you running the stored procedure, just 'exec spm_tranlog' from a new query window?

Bypassing the stored procedure, can you try to run this:

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
dump transaction @.DBName with truncate_only

And also...

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
backup log @.DBName with truncate_only

If these still don't work, post the error message(s) you get from each.

Jarret

|||You cant truncate the Log with SIMPLE PLAN in a DB. Because it is doing by itself.

Error when executing DTS from sp_OA

What does this error mean? I get this error when i execute a DTS
package using SP_OA from within T-SQL....
I'm stumped.
0x80070008 Microsoft Data Transformation Services (DTS) Package
Not enough storage
is available to process this command.This may indicate the memory size is not big enough to hold distributed
query operations. You need to restart sql server to refresh memory.
"Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...

quote:

---

> What does this error mean? I get this error when i execute a DTS
> package using SP_OA from within T-SQL....
> I'm stumped.
>
> 0x80070008 Microsoft Data Transformation Services (DTS) Package
>
> Not enough storage
> is available to process this command.
>

---

|||That's what I'm thinking. I just don't want to have to restart all the
time to refresh memory. Is this a bug?
Richard Ding wrote:

quote:

---

> This may indicate the memory size is not big enough to hold distributed
> query operations. You need to restart sql server to refresh memory.
> "Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
> news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
>
>
>

---

Error when executing DTS from sp_OA

What does this error mean? I get this error when i execute a DTS
package using SP_OA from within T-SQL....
I'm stumped.
0x80070008 Microsoft Data Transformation Services (DTS) Package
Not enough storage
is available to process this command.This may indicate the memory size is not big enough to hold distributed
query operations. You need to restart sql server to refresh memory.
"Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
> What does this error mean? I get this error when i execute a DTS
> package using SP_OA from within T-SQL....
> I'm stumped.
>
> 0x80070008 Microsoft Data Transformation Services (DTS) Package
>
> Not enough storage
> is available to process this command.
>|||That's what I'm thinking. I just don't want to have to restart all the
time to refresh memory. Is this a bug?
Richard Ding wrote:
> This may indicate the memory size is not big enough to hold distributed
> query operations. You need to restart sql server to refresh memory.
> "Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
> news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
>>What does this error mean? I get this error when i execute a DTS
>>package using SP_OA from within T-SQL....
>>I'm stumped.
>>
>>0x80070008 Microsoft Data Transformation Services (DTS) Package
>>
>> Not enough storage
>>is available to process this command.
>
>

Error when execute the packages

All,

Could someone please tell me what to do with it and what might be causing it?

The packages were fine yesterday.

Here is the error message:

[Script Component 1 1 [3941]] Error: System.Runtime.InteropServices.COMException (0x80040154):

Retrieving the COM class factory for component with CLSID {A138CF39-2CAE-42C2-ADB3-022658D79F2F}

failed due to the following error:

80040154.at Microsoft.VisualBasic.Vsa.VsaEngine.CreateEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.CheckEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.set_RootMoniker(String value)

at Microsoft.SqlServer.VSAHosting.Runtime.CreateVsaEngine()

at Microsoft.SqlServer.Dts.Pipeline.ScriptRuntime..ctor(String projectName,

String moniker, String language, Boolean showErrorUI)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Thanks in Advance

Check this KB:

http://support.microsoft.com/kb/931846

Error when calling stored procedure

I am trying to execute a store procedure from ASP/VB but it fails with the message:

Incorrect syntax near 'InitProject'.

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: Incorrect syntax near 'InitProject'.

Source Error:

Line 24: cmd.Parameters("@.ProjectId").Value = 3Line 25: cn.Open()Line 26: cmd.ExecuteNonQuery()Line 27: cn.Close()Line 28: End Sub

Here is my code:

'Execute the InitProject stored procedure
'Create the connection from the string in the web.config file
Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SMARTConnectionString").ConnectionString)
'I want to execute InitProject stored procedure
Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)
'With the parameter @.ProjectId = 3
cmd.Parameters.Add(New SqlParameter("@.ProjectId", Data.SqlDbType.Int))
cmd.Parameters("@.ProjectId").Direction = Data.ParameterDirection.Input
cmd.Parameters("@.ProjectId").Value = 3
cn.Open()
'But this fails
cmd.ExecuteNonQuery()
cn.Close()

And my stored procedure is defined as:

[dbo].[InitProject] @.ProjectId int
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into MATERIAL ( PROJECT_ID, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID )
select @.ProjectId, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID
from MATERIAL_TEMPLATE
END

The store procedure works fine when I do

exec InitProject 3

in sql query.

I have been away from .NET but try putting commandtype = storedproc. I dont remember the syntax but you could figure it out. It would go before adding the parameters.|||

Like this:

Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)

cmd.CommandType = CommandType.StoredProcedure

.....

|||

Thank you very much for your help. It works like this:

'I want to execute InitProject stored procedure
Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)
cmd.CommandType = Data.CommandType.StoredProcedure
'With the parameter @.ProjectId = 3
cmd.Parameters.Add(New SqlParameter("@.ProjectId", Data.SqlDbType.Int))
cmd.Parameters("@.ProjectId").Direction = Data.ParameterDirection.Input
cmd.Parameters("@.ProjectId").Value = 3
cn.Open()
'Now it works
cmd.ExecuteNonQuery()
cn.Close()

I don't understand why I need to prefix withData. when others don't need.

|||

Hi

Hierarchy : System.Data.CommandType.StoredProcedure

You just import namespace System so you need to prefix with Data

If you import namespace System.Data you don't need that.

Hope it helps.