Showing posts with label instance. Show all posts
Showing posts with label instance. Show all posts

Monday, March 19, 2012

Execute Stored Procedure menu not appearing

Hi,

I have created an SQL Server instance in SQL Server Management Studio. I have a few databases, and stored procedures in them. When I right click on the stored procedure, I have the menu for "New Stored Procedure, Modify, Script Procedure as, and so on". But, I could not see the "Execute Stored Procedure" item.

Could any one help to find out what could be the problem and solve it?

Thanks,
Luke.Stored procedures in 2000 must be executed from a utility such as Query Analyzer.
I think the 2005 management interface allows you to execute them directly and submit parameters, but I'd have to check to be sure.
Regardless, it is not good to be doing these types of activities in the GUI. Use QA instead.

Monday, March 12, 2012

Execute stored proc on a named instance

I think I'm being a bit thick, but I just cannot figure out the proper syntax to call a stored proc on a SQL named instance I have. I've tried many variations, but here is the basic format of what I'm trying:

EXEC Server\Instance.DB.dbo.usp_fm_proc 1

It seems it doesn't like the \ as I get an error "Incorrect syntax near 'Instance'.

What am I missing here?

Have you created linekd server, try the following:

EXEC [Server\Instance].DB.dbo.usp_fm_proc 1

|||Dooh! That's it, I was overlooking the []. Thanks!

Friday, March 9, 2012

Execute SQL Task speed

I've created a SSIS package, in a sql 2005 instance, that uses an Execute SQL Task" to call a stored proc as its last step. When run from BIDS, the last step takes about 2 to 3 minutes, consistently. When I run the exact same query from Management Studio (either via exec <spname> or by copying the sp's t-sql code into a query window) it consistently takes about 1 minute. I've run sevral test and these number are quite reproducible.

Any ideas to account for the "slowness" of the Execute SQL Task?

TIA,

Barkingdog

Hi, are you running your package in debug mode?

Try run the package without Visual Studio.

John Bocachica - Colombia

www.iquos-bi.com

|||

The dropdown box at the top of BIDs says "Development"

Here is what I have found. My package runs three control tasks. When I run all three, the Exec SQL tasks takes about 2 minutes to run but when I execute ONLY the Exec SQL task that task runs in about 1 minute!

I saved the package (in BIDS) to a .dtsx file and ran it. The whole process took about 2.5 miniutes which tells me that the Exec SQL task still took about 2 minutes.

barkingdog

|||

What are the other tasks doing? Do they use the same database connection? Are there transactions involved?

|||

The first task truncates a table called Contact. The second task imports a CSV file into a table (uses a SQL Server Destination. Does a straight copy of the data; no transformations. The file imported is on the sql 2005 server and database I'm importing into). The third task (Exec SQL Task) applies various UPDATE statements to the table populated in step 2. Steps 2 and 3 use the same sql connection.

II don't know how to tell if all the tasks belong to the same transaction. I set up three control flows in the same pane but they are not contained in any container object, if that helps at all.)

TIA,

Barkingdog

EXECUTE SQL TASK --> Object Reference Not Set to An Instance of an Object

Hi all,

Does anyone see the error below before?

I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.

It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY

“Object Reference Not Set to An Instance of an Object” when I click on PARSE QUERY.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

OLEDB Sample also give me syntax error

exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''

I tried to change to OLEDB and call the stored procedure like this but got syntax error?

Not sure what is the error here.

I believe there is a known issue when trying to do a Parse Query with ADO.NET, when variables or parameters are being used. Does the task succeed if you execute it?|||

yes, it executes fine.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

This is just an example. But to call ANY STORED PROCEDURE will give the same problem.

I think it is a bug with the ADO.NET query parser.

Note that I saw this exact same error LAST YEAR with SP1. I think it never get fixed.

work around? customers don't want to use OLEDB because the syntax ?,? etc is not straightforward and easy to maintain.

They elect to just ignore it.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

EXECUTE SQL TASK --> Object Reference Not Set to An Instance of an Object

Hi all,

Does anyone see the error below before?

I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.

It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY

“Object Reference Not Set to An Instance of an Object” when I click on PARSE QUERY.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

OLEDB Sample also give me syntax error

exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''

I tried to change to OLEDB and call the stored procedure like this but got syntax error?

Not sure what is the error here.

I believe there is a known issue when trying to do a Parse Query with ADO.NET, when variables or parameters are being used. Does the task succeed if you execute it?|||

yes, it executes fine.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

This is just an example. But to call ANY STORED PROCEDURE will give the same problem.

I think it is a bug with the ADO.NET query parser.

Note that I saw this exact same error LAST YEAR with SP1. I think it never get fixed.

work around? customers don't want to use OLEDB because the syntax ?,? etc is not straightforward and easy to maintain.

They elect to just ignore it.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

Friday, February 17, 2012

execute ie instance

Which is the best way to execute an internet explorer Instance on oracle forms ?Originally posted by fhunth
Which is the best way to execute an internet explorer Instance on oracle forms ?
You can use the HOST command to run any windows program from Forms.