running stored proc with parameter

hi,

im getting an error when i run the stored proc with a string parameter in execute sql task object.

this is the only code i have:

exec sp_udt_keymaint 'table1'

I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.

How do i do this right?

cherrie

Cherrie,

Not sure I am understanding the details of your problem (may be if you post the error you are geeting...); but here is my shot:

Assuming the parameter inside of your procedure is table1; then the SQLStatement of the SQL task should be something like:

EXEC sp_udt_keymaint @.table1=?

The in the parameter mapping page of the SQL task you have to map the SSIS variable to the SP parameter.

Rafael Salas

|||The syntax of the SQLStatement depends upon the connection type used. You may want to refer to BOL for the syntax of the each connection type.|||

Your best bet is to use a .NET provider. Judging by your procedure name, you're running a stored procedure on SQL Server. Then, set your SQLStatement to the name of your stored proc (dbo.sp_udt_keymaint). Add the parameter (@.TableName, the @. symbol is required) in the Parameter Mappings tab and map the parameter to a user-defined variable.

HTH

running stored proc with parameter

hi,

im getting an error when i run the stored proc with a string parameter in execute sql task object.

this is the only code i have:

exec sp_udt_keymaint 'table1'

I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.

How do i do this right?

cherrie

Cherrie,

Not sure I am understanding the details of your problem (may be if you post the error you are geeting...); but here is my shot:

Assuming the parameter inside of your procedure is table1; then the SQLStatement of the SQL task should be something like:

EXEC sp_udt_keymaint @.table1=?

The in the parameter mapping page of the SQL task you have to map the SSIS variable to the SP parameter.

Rafael Salas

|||The syntax of the SQLStatement depends upon the connection type used. You may want to refer to BOL for the syntax of the each connection type.|||

Your best bet is to use a .NET provider. Judging by your procedure name, you're running a stored procedure on SQL Server. Then, set your SQLStatement to the name of your stored proc (dbo.sp_udt_keymaint). Add the parameter (@.TableName, the @. symbol is required) in the Parameter Mappings tab and map the parameter to a user-defined variable.

HTH

sql

Running SQL Server Enterprise 2005 on MS Virtual Server

I keep wondering if this is safe. I am getting errors here and there along with the inability to connect to my database via connection string in ASP.NET no matter if the user has complete permissions or not amongst other difficulties and I wonder if this is causing a lot of the problems. WE are running SQL Server Enterprise 2005 on Microsoft Virtual Server. Is this approved?

Running SQL Server in a virtual machine should work. I do this for scenario testing all the time.

The usual suspects for connectivity problems in virtual machines are whether the virtual network adapter is attached to the host's network adapter (vs. the "local adapter" that can't be seen outside the virtual server environment) and the host's firewall getting in the way of other computer's talking to the virtual machine.

The typical issues with SQL Server connectivity also apply to SQL Servers running in virtual machines. Make sure SQL Server is configured to listen to TCP/IP connections on the virtual network adapter that is mapped to the host's network adapter. Also, make sure that the firewall in your virtual machine is allowing external connections to SQL Server's port.

|||We are running some test environments on Virtual Server.
We haven't experienced any issues so far.