Showing posts with label fails. Show all posts
Showing posts with label fails. Show all posts

Thursday, March 29, 2012

executing sp_changeownerdb

Hi,
I've a problem executing sp_changedbowner in my stored procedure which
is saved in the master's stored procedures.
The code fails after executing @.proc1. The errors says that i could not
find proc2.
Here's the code:
CREATE PROC usp_RestoreDB(
@.db_name varchar(10),
@.backup_location varchar(255),
@.login varchar(20)
)
AS
DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
SET @.proc1 = @.db_name + '..sp_fixusers'
SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
RESTORE DATABASE @.db_name
FROM DISK = @.backup_location
WITH REPLACE
Begin
EXEC @.proc1
EXEC @.proc2
ENDTry,
...
exec (@.proc1)
exec (@.proc2)
...
AMB
"Jason" wrote:

> Hi,
> I've a problem executing sp_changedbowner in my stored procedure which
> is saved in the master's stored procedures.
> The code fails after executing @.proc1. The errors says that i could not
> find proc2.
> Here's the code:
> CREATE PROC usp_RestoreDB(
> @.db_name varchar(10),
> @.backup_location varchar(255),
> @.login varchar(20)
> )
> AS
> DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
> SET @.proc1 = @.db_name + '..sp_fixusers'
> SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
> RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
> Begin
> EXEC @.proc1
> EXEC @.proc2
> END
>|||On Thu, 29 Sep 2005 16:21:18 +0200, Jason wrote:

>Hi,
>I've a problem executing sp_changedbowner in my stored procedure which
>is saved in the master's stored procedures.
>The code fails after executing @.proc1. The errors says that i could not
>find proc2.
>Here's the code:
>CREATE PROC usp_RestoreDB(
>@.db_name varchar(10),
>@.backup_location varchar(255),
>@.login varchar(20)
> )
>AS
>DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
>SET @.proc1 = @.db_name + '..sp_fixusers'
>SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
>RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
>Begin
>EXEC @.proc1
>EXEC @.proc2
>END
Hi Jason,
Try changing the logic for proc2 to
(...)
SET @.proc2 = @.db_name + '..sp_changedbowner'
(...)
EXEC @.proc2 @.loginame = @.login
(...)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Thursday, March 22, 2012

executing a DTS package

a scheduled DTS package on a commercial server fails to execute, to transfer
data between two databases that I own.Can I trigger the package to execute
from code within a stored procedure ?
thanks in advance EarnieEarnie
In order to execute a DTS Package from a Stored Procedure you will need to
call the DTSRUN executable using xp_cmdshell. If you already have a Job
created to execute the DTS Package you can copy the DTSRun command from the
Job Step.
ie. EXEC master.dbo.xp_cmdshell 'DTSRun /~Z0x...3'
For more details refer to http://www.sqldts.com/default.aspx?210
- Peter Ward
WARDY IT Solutions
"Earnie" wrote:

> a scheduled DTS package on a commercial server fails to execute, to transf
er
> data between two databases that I own.Can I trigger the package to execute
> from code within a stored procedure ?
> thanks in advance Earnie

Wednesday, March 21, 2012

ExecuteXMLReader fails with valid xml from sql server. Why?

(From a .net class using sqlcommand)
A stored proc that takes 2 input params is firing on the server (i've
checked using sql profiler). The sp in question produces valid xml (i've
checked by creating a sqlxml virutal dir and allowing url queries - the
result is valid in IE and therefore well formed xml)
The stored proc in question is not a single "select X for xml ..." query. It
is made up of fragments
example of results
<root>
<subnode>
select x ... for xml ...
</subnode>
<subnode>
select x ... for xml ...
</subnode>
...
</root>
Why won't ExecuteXmlReader work with the results?
XR = CMD.ExecuteXmlReader()
(where xr is an xml reader)
this line comes back with the error
"Invalid command sent to ExecuteXmlReader. The command must return an Xml
result."
The command is not invalid! I can see it executing on sql. The result is
valid xml.
I have also tried using the sqlxmlcommand but it claimed I was not passing
parameters in (which I was) so I gave up on that.
I am beginning to hate .net
The concept is great but there is too much pain
trying to connect things together...
Any ideas...?
CODE--
Dim StartDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
Dim EndDate As String =
ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
CMD = New SqlCommand
CMD.Connection = CN
Select Case SubType
Case Nothing
CMD.CommandText = "XXXXX"
CMD.CommandType = CommandType.StoredProcedure
Dim Param As New SqlParameter
CN.Open()
Try
Param.ParameterName = "@.start_date" : Param.Value =
StartDate : CMD.Parameters.Add(Param)
Param = New SqlParameter
Param.ParameterName = "@.end_date" : Param.Value =
EndDate : CMD.Parameters.Add(Param)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
Case Else
'do something
End Select
'--
Try
XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
* * * *
ResultsDoc = New XmlDocument
ResultsDoc.Load(XR)
Catch ex As System.Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
Finally
End Try
END CODE--
In your description, you create a command like this:

> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
Where did you do such construction? What type of variable or command are you
using?
One easy solution on Yukon is that you can try is store the result in a
"xml" type variable, and then select from it.
For the ExecuteXmlReader() expecting an "XML" typed data back, instead of
any string that looks like "XML".
Thanks.
Xin
"adolf garlic" wrote:

> (From a .net class using sqlcommand)
> A stored proc that takes 2 input params is firing on the server (i've
> checked using sql profiler). The sp in question produces valid xml (i've
> checked by creating a sqlxml virutal dir and allowing url queries - the
> result is valid in IE and therefore well formed xml)
> The stored proc in question is not a single "select X for xml ..." query. It
> is made up of fragments
> example of results
> <root>
> <subnode>
> select x ... for xml ...
> </subnode>
> <subnode>
> select x ... for xml ...
> </subnode>
> ...
> </root>
> Why won't ExecuteXmlReader work with the results?
> XR = CMD.ExecuteXmlReader()
> (where xr is an xml reader)
> this line comes back with the error
> "Invalid command sent to ExecuteXmlReader. The command must return an Xml
> result."
> The command is not invalid! I can see it executing on sql. The result is
> valid xml.
> I have also tried using the sqlxmlcommand but it claimed I was not passing
> parameters in (which I was) so I gave up on that.
> I am beginning to hate .net
> The concept is great but there is too much pain
> trying to connect things together...
> Any ideas...?
>
> CODE--
>
> Dim StartDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='startDate']/@.value").Value
> Dim EndDate As String =
> ArgsDoc.SelectSingleNode("//Parameter[@.name='endDate']/@.value").Value
> CMD = New SqlCommand
> CMD.Connection = CN
> Select Case SubType
> Case Nothing
> CMD.CommandText = "XXXXX"
> CMD.CommandType = CommandType.StoredProcedure
> Dim Param As New SqlParameter
> CN.Open()
> Try
> Param.ParameterName = "@.start_date" : Param.Value =
> StartDate : CMD.Parameters.Add(Param)
> Param = New SqlParameter
> Param.ParameterName = "@.end_date" : Param.Value =
> EndDate : CMD.Parameters.Add(Param)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
> Case Else
> 'do something
> End Select
> '--
> Try
> XR = CMD.ExecuteXmlReader() ' * * * * * * LINE THAT FAILS * * *
> * * * *
> ResultsDoc = New XmlDocument
> ResultsDoc.Load(XR)
> Catch ex As System.Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> Finally
> End Try
>
> END CODE--

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

AllanDoesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly Big Smile|||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

AllanDoesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly Big Smile|||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

Allan
Doesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly Big Smile|||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?
|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 +ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 ='XXX'), 0)

OR

SELECT '' +ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 ='XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68sql

ExecuteSQL Fails from Variable

I am executing the following statement to setup a database:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

If this is formed inside a script task, assigned to a variable and then executed from the variable it fails. If I past it into the Execute SQL Task as a string it succeeds. Any ideas on where the difference may be? I have set a breakpoint and verified that the variable is being filled in correctly.

I get this error:

SSIS package "BuildTemplates.dtsx" starting.

Error: 0x0 at Create Database: Incorrect syntax near the keyword 'CREATE'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0xC002F210 at Create Database, Execute SQL Task: Executing the query "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

" failed with the following error: "Incorrect syntax near 'GO'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I suspect what is going on is that the \ in the paths need to be doubled. I did that and the problem appeared to go away.

Thanks,

Monday, March 19, 2012

ExecuteNonQuery Fails catastrophically

I was trying an RDA Demo project. However, everytime I pull the data from Sql Server 2005 and then move on to add a row to the table using SqlCeCommand.ExecuteNonQuery(), the application fails catastrophically without giving any exception and simply exits!

When I have pulled the data once and this application has shut down (exited) I can open it again and then continue normally but I can't expect the application to PULL the data and then even add or update a row to that table. I can do select queries fine. Why would this happen?

I am disposing all the objects that I use in both the scenarios, or atleast I believe so.
Does any one have any idea?For anyone else having the same problem -
The solution is to refrain from deleting the database manually through code and also to refrain from creating the database through code. Somehow it interferes with the ExecuteNonQuery later on. From what I have made out from my research it happens mostly with Emulators. Shouldn't be an issue with real device.

executeBatch fails on stored proc call

I have a stored procedure that is only supposed to insert if the record
does not already exist.
ALTER PROCEDURE [CARTS].[Insert_Store_Item_Price_Data]
@.Store_Item_Price_Change_ID varchar(50),
@.Store_ID char(4),
@.Item_ID char(14),
@.Batch_Number_ID varchar(6),
@.Effective_Start_Date datetime,
@.Price_AMT decimal(8,2),
@.Promotion_Code smallint,
@.State_Name varchar(10),
@.Record_Creation_Timestamp datetime
AS BEGIN
-- insert a record if no duplicate record is found
DECLARE @.ItemCode char(14)
SELECT @.ItemCode=Item_ID
FROM [CARTS].[Store_Item_Price] WITH (NOLOCK)
WHERE Store_ID=@.Store_ID AND
Item_ID=@.Item_ID AND
Batch_Number_ID = @.Batch_Number_ID AND
Price_AMT = @.Price_AMT AND
Promotion_Code = @.Promotion_Code AND
State_NAME = @.State_NAME
IF(@.ItemCode IS NULL)
BEGIN
INSERT INTO [CARTS].[Store_Item_Price]
([Store_Item_Price_Change_ID]
,[Store_ID]
,[Item_ID]
,[Batch_Number_ID]
,[Effective_Start_Date]
,[Price_AMT]
,[Promotion_Code]
,[State_Name]
,[Record_Creation_Timestamp])
VALUES
(@.Store_Item_Price_Change_ID,
@.Store_ID,
@.Item_ID,
@.Batch_Number_ID,
@.Effective_Start_Date,
@.Price_AMT,
@.Promotion_Code,
@.State_Name,
@.Record_Creation_Timestamp);
END
END
Is there an ELSE statement I can add that will return a zero for rows
effected? That way executeBatch will work. Right now, it returns the
following exception:
java.sql.BatchUpdateException: The returned update count was -1. Either
a procedure returned a result set or not every procedure returned an
update count. The driver expects 11 update counts to be returned from
this batch.StevenMartin (stevenmartin@.us.ibm.com) writes:
> I have a stored procedure that is only supposed to insert if the record
> does not already exist.
>...

> Is there an ELSE statement I can add that will return a zero for rows
> effected? That way executeBatch will work. Right now, it returns the
> following exception:
Write the query as:
INSERT tbl (...)
SELECT ....
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE ...
Note that there is not any FROM clause in the outer SELECT.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Monday, March 12, 2012

execute stored procedure

I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!

a stored procedure is run under the security context of who ran the Sps. When the sp is run it is executed in the context of who ever run the Sps. if you deny the user on the base table. th sps will fail.

how about a view with an unbroken ownership chain.

how about using functions instead of Sp.

just a wild guess....

|||

Alternatives that you can use in SQL Server 2005:

- sign the procedure and grant permission to access the tables to the certificate used for signing

- use an EXECUTE AS clause for the procedure to make it execute under a different execution context.

For signing, I have an example at: http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

For EXECUTE AS, see documentation at:

http://msdn2.microsoft.com/en-us/library/ms187926.aspx

Thanks
Laurentiu

Execute SSIS Package Job fails

Hey Folks,

I've got here a strange Problem. If I try to execute the SQL Server Agent Job, that executes my SSIS Package, it fails. The job succeedes when I run the Job as the Proxy, that maps on the User, that has deployed the Package, or when I run the Job under an System Administrator Proxy. Now my Question - how must I set up an Service-Account, which is no Admin and not has deployed the Package?

I already know, that the User has to be in the sysadmin role, and in all msdb SQLAgent*

Thanks and greez

Karsten

I assume the service account is suitable for running SQL Agent. To be sure you may want to review this rather long BOL topic "Setting Up Windows Service Accounts" (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/309b9dac-0b3a-4617-85ef-c4519ce9d014.htm), but plenty of good info buried in there.


So if the job fails when using the base service account rather than a higher privileged proxy it says to me that the service account does not have correct permissions. If it errors, then you need to find out why. Here are some tips on doing just that, in particular look at using a CmdExec step, and the Package Store File Permissions section.


Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)

Execute SQL task with update statement

I am running an update statement in an execute sql task, it will run one time but then fails after that. Whats going on?

Here is my query I'm running.

UPDATE encounter
SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime
FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN
encounter ON encounter.encounter_number = r.Encounter

What I'm doing is pulling info from a flat file, inserting it into the encounter table, I then run this sql statement to pull additional info from another table on another server, and update the encounter table with the corresponding info. Pretty straight forward. But what is really getting me is that the package will run 1 time but if I wait ten minutes and try running it again it bombs. Any ideas?

Please provide any error messages. That usually helps us help you.|||Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "Login timeout expired".

Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

[Execute SQL Task] Error: Executing the query "UPDATE encounter SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN encounter ON encounter.encounter_number = r.Encounter " failed with the following error: "Named Pipes Provider: Could not open a connection to SQL Server [5]. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is what the SSIS package says. I'm not sure how helpful it is but I can run the update statement in management studio all day long and it works great. It just bombs through the SSIS package.

|||

Seems as though the package is having problems talking to the server, "login timeout expired"

|||

I have all the timeouts settings set to 60 seconds, which should be plenty of time, one would think. When it does work it runs real fast-less than 4 seconds.

The main frustration is that it will work 1 time when I do something to the query but then it doesn't work again.

Execute SQL Task with no rows affected

Hi,

I used with Execute SQL Task for update a table in Oracle DB.

I saw that when the command has no rows for updeting, the task fails.

Here is my command:

update tableName set fieldA=sysdate where fieldB is Null

and again, when there are some rows that fieldB is Null then the command succeed, but when the fieldB in all the rows is not null the command fails.

I tried to play with the ResultSet with no success.

Please your advice.

Thank you in advance

Noam

What error or errors are returned when the task fails? Can you please copy and paste the error output from BIDS or from a log file into a reply?

Are you attempting to store the resultset from the query in a variable?

Can you run the query successfully (where zero rows are affected) without error using SQL*Plus or another query tool?

This information will likely be invaluable in helping track down the source of the error.

|||

Thank you for your reply.

This is the error:

"Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "update TableName set FieldA=SYSDATE where FieldB is Null" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
."

No, I didn't attempt to store the resultset, I just executed the query.

and yes, I ran the query successfully using SQL*Plus with the result : "0 rows updated."

Please your help.

Thanks

Noam

|||

I found it.

My connection was odbc.

I changed the connection to Ado.Net provider for odbc and it works without fail.

Why?

It's another question.

Thank you anyway.

Noam

Wednesday, March 7, 2012

Execute sp_OAMethod Fails

Hi,

I am struggeling with the execution of DTS packages from within T-SQL scripts on one of many SQL server instances on my Network (SQL 7.0 and SQL 2000 on Win 2000). The DTS Packages will execute, but one of the Tasks will always fail for some reason. The Tasks that fails are:

Execute SQL Task
Transform Data Task

I am using the OLE stored procedures, because DTSRUN on SQL 7.0 does not support passing global variables to the DTS package.

All the global variables that are sent to the DTS Packages are validated under execution and causes no problems to my knowledge. The DTS packages executes with no problems from the Enterprise Manager.

The computer where I get this strange behaviour is named KL-PC979. The "-" character is not included on any othe machine on the nettwork and I suspects that It is the cause for all my troubles.

Here is how I load:

'LoadFromSQLServer("KL-PC979", "MyUser", "UserPWD", 0, "", , , "MY_PKG")'

have tried both "." AND "local" for the SERVERNAME, but no luck.

The error logs gives me this information:

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step 'DTSStep_DTSExecuteSQLTask_3' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:No value given for one or more required parameters.
Step Error code: 80040E10
Step Error Help File:
Step Error Help Context ID:0

I have not been able to find some useful information about this, but hopfully there is someone out there...

ThanksRE:
The computer where I get this strange behaviour is named KL-PC979. The "-" character is not included on any othe machine on the network and I suspect that it is the cause for all my troubles.


Question I
Perhaps, and perhaps not; one might consider testing to see if an execution using an alias (without a '-') succeeds (or at least, does not fail at the same point / in the same manner)?

{An alias would be expected to address the issue if indeed the '-' in the name is the source of the issue (not tested).}

RE:
one of the Tasks will always fail for some reason. The Tasks that fails are:
Execute SQL Task
Transform Data Task

The error logs give me this information:

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step 'DTSStep_DTSExecuteSQLTask_3' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:No value given for one or more required parameters.
Step Error code: 80040E10


Question II
This suggests a missing parameter; what is in the Execute SQL Task?

Question III
Does the tsql in the the Execute SQL Task run from QA, (if not, consider posting it with the applicable ddl, sample data, etc., etc.)?|||Answer to Question I
I tried this through the Client Network Utility, but it did not work. Is it possible to make an alias on the server side?

Answer to Question II
The SQL Tasks execute a single procedure with a couple of parameters (globals). I have validated the globals in earlier Tasks. The procedures either selects or inserts a smal amount of data from or to a table. Query Timeout is set to 0.

Answer to Question III
The Procedure runs without problems from QA on this machine.

Any ideas?

essal|||RE:
Answer to Question I
Q1 I tried this through the Client Network Utility, but it did not work. Is it possible to make an alias on the server side?
A1 The approach is correct the Client Network Utility is the correct tool to make an alias (generally on clients, but the Client Network Utility works on servers in a similar {client} fashion).

RE:
Answer to Question II
The SQL Tasks execute a single procedure with a couple of parameters (globals). I have validated the globals in earlier Tasks. The procedures either selects or inserts a small amount of data from or to a table. Query Timeout is set to 0.

Question IV
If a simple tsql statement is substituted e.g.(Select 'TestResult' As 'TestOfTask') for the SQL Tasks (that seem to be failing per the error message content) are the same errors still returned?

RE:
Q2 Any ideas?
A2 Yes. But unfortunately, few good ones. :(

Idea 1:
If not currently using named pipes:
a enable the named pipe protocol with a default pipe name e.g.(\\.\pipe\NoDash\query)
b Create another alias (named pipes) to that pipe
c Using the alias name attempt to reproduce the KL-PC979 error

Idea 2:
Since both "." AND "local" have been tried:
a try "(local)" and "(.)"

Idea 3:
Install a development (test) server with:
a A similar dashed '-' name e.g.(KL-PC979Test)
b Make the test server, KL-PC979Test otherwise identical to KL-PC979
c Reproduce the KL-PC979 error on the KL-PC979Test server
d Change the name of the test server to KLPC979Test
e Attempt to reproduce the KL-PC979 error on the renamed KLPC979Test server
f If e fails at least the issue is definitively identified (along with a potentially painful remedy)|||Question IV
Through this simple test I managed to determine that it was the globals that tricked me. I tried, as You mentioned, to use a simple hardcoded Query, and the Package executed without errors. The reason I was sure the globals where read correctly is because the Package write them to a file using VBscript during execution.

The SQL Tasks where I discovered the problem was calling procedures like this
sp_MyProc ?, ?, ?, ?
and I had done the Parameter mapping against the Global variables.

This did not work in my enviorment when I was calling the Package through OLE. (??)

Since the ActiveX Script Task could read and write the globals correctly I removed the question marks (?) and build up the query from such a Task. Works perfect.

Thanks|||RE:
Question IV
Through this simple test I managed to determine that it was the globals that tricked me. I tried, as You mentioned, to use a simple hardcoded Query, and the Package executed without errors. The reason I was sure the globals where read correctly is because the Package write them to a file using VBscript during execution.

The SQL Tasks where I discovered the problem was calling procedures like this
sp_MyProc ?, ?, ?, ?
and I had done the Parameter mapping against the Global variables.

This did not work in my enviorment when I was calling the Package through OLE. (??)

Since the ActiveX Script Task could read and write the globals correctly I removed the question marks (?) and build up the query from such a Task. Works perfect.

Thanks

You are quite welcome.

I am pleased that Question IV at least spared you from further fruitless efforts (especially Idea 3).

Execute Script on Mirror Failover

Hi All
Is it possible to automatically execute a script when a mirror server fails
over to the secondary server?
ThanksPerhaps having an EVENT NOTIFICATION on the DATABASE_MIRRORING_STATE_CHANGE event, and activation on
that Broker queue?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David" <David@.discussions.microsoft.com> wrote in message
news:EB1E80D6-6F64-4061-8D19-9639CBBD5A31@.microsoft.com...
> Hi All
> Is it possible to automatically execute a script when a mirror server fails
> over to the secondary server?
> Thanks

Sunday, February 19, 2012

Execute Package Task Not Working

I am trying to run two execute package tasks. I want the second task NOT to
execute if the first one fails, but I cant seem to get this working. I have
put the workflow in place and check fail package on first error in every
place I can think of, yet the second package always executes regardless if
the first one fails. HELP!!!!Do you have an error? What does the package log say?
Anith

Execute Package Task always fails.

I have a package (i.e. child package) which runs itself perfectly fine without displaying any error.

However when I embeded this package inside another package (i.e. parent package) using Execute Package Task. The task always fails. It seems strange enough.

The child package has two variables that need to be passed in from parent package.

Can any expert here please help out? Thanks.

- Steve

What are the errors? Look in the output window and turn on some logging.|||

Error 1 Error loading Scheduling_F580021.dtsx: The connection "{34589715-3053-4b26-9769-0b8ecc198d85}" is not found. This error is thrown by Connections collection when the specific connection element is not found. C:\Scheduling_data.dtsx 1 1
here is the error:

I have kept getting this annoying error.

|||

Just doulble check the connection manager that is being used by the Execute PAckage task. I would drop the connection mamanger and add it back from the Execute Package task: COnnection : <New Connection>.

Rafael Salas

|||

Here is what I did to fix it ...

i delete the troubled child package completely, and rewrote it from the sratch. And it started to work.

Just as a reminder:

Please note that the old trouble child package contains some copy-and-pasted tasks (and connections) from parent package. I often use copy-and-paste to develop packages. Seems that there are some problem with it. Not sure if SSIS has a bug or something else.

|||

The problem looks like it was with a task which had been pasted in. Most tasks actually store the connection ID, the "{34589715-3053-4b26-9769-0b8ecc198d85}" GUID, rather than the name, although you generally always see the name. If you see the GUID it normally means that the connection with that ID does not exist, and therefore it cannot translate the ID to the name for display and general use or validation. In otherwords the task refers to a connection that does not exist.

Using the ID over the name means that you can rename connections and stuff keeps working, but conversely means that you cannot just create a connecion with a matching name, and expect it to be the same.

|||

you made a good point.

It should be more cautious to copy-and-paste tasks, connection, or packages for rapid SSIS development. A package works perfectly fine using copy-and-paste task/connection if it runs itself.

This does cause a pain when developing a complex package (parent-child package) if we cannot use copy-and-paste. Is Microsoft SSIS team aware of it or do they have any plan to fix it ?

|||

Personally, I do not see that such pain you are taking about. If you are copying a DataFlow task from one package to another; you would need eventually to check or re-define your connection managers; since you may need the same combination of tasks but different connections. Changing the connection managers inside of a task is just a click and select from a dropdown list task.

In the other hand, if you need more than copying the dataFlow task, meaning you also need the references to the connection managers; then you are better creating a copy of the whole package and then adding and droping tasks/logic as needed

This is just my opinion...

Rafael Salas

|||

I not only copy-paste tasks from parent package to child packages, I copy-paste all connections as well. I used the same connections which configures on the fly based on the ConnectionString. The connectionString is a variable which is assembled and determined at the runtime.

I also copy packages too all times. I use copy-paste on everything for rapid development. Somewhere, the strange errors come up when I tried to build a complex package (like grand-parent package). And it is very hard to debug. I know and have used debugging tools and logging as well. It can be painful without knowing the code behind the scene. I run all of packages with settings (i.e. connections, queries, and tasks) to be configured on the fly.

|||

Perhaps you should look at making some packages re-usable if you do so much copy and paste. It is often easier said than done, but since you say you do it so much and you find this such an issue I'd be surprised if you could not get some reuse.

The benefit of the current behaviour is the ability to rename connections without breaking task references, something that got a lot of feedback during the beta, and for me that is very usefull. Make a suggestion though - http://connect.microsoft.com

|||

As a matter of fact, I have exploited a lot of reuse feature as much as I can.

Here is the scenario: Say, I need to develop 300 packages. I do not want to create a blank package to start with. Often times, I use Copy-and-paste some similar package and rename it. That could save me a lot of time. If I start with an existing package, it may save me a lot of time.

Steve

Friday, February 17, 2012

Execute Job from another Job based on failure

I have two jobs A and B. I want to execute Job B is job
A fails but only if the error number is 3013. How can I
make this happen?
Thanks,
Vichow about sp_start_job?
"Vic" <vduran@.specpro-inc.com> wrote in message
news:e9fa01c3f183$e79f4350$a101280a@.phx.gbl...
> I have two jobs A and B. I want to execute Job B is job
> A fails but only if the error number is 3013. How can I
> make this happen?
> Thanks,
> Vic

Execute Job from another Job based on failure

I have two jobs A and B. I want to execute Job B is job
A fails but only if the error number is 3013. How can I
make this happen?
Thanks,
Vichow about sp_start_job?
"Vic" <vduran@.specpro-inc.com> wrote in message
news:e9fa01c3f183$e79f4350$a101280a@.phx.gbl...
> I have two jobs A and B. I want to execute Job B is job
> A fails but only if the error number is 3013. How can I
> make this happen?
> Thanks,
> Vic