Executing an string on MS SQL from .Net

Hi there,

I have this:

Dim strSQL As String = "SELECT * FROM GrupoRegistro WHERE Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 5) OR g.Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 3)"

How do I execute on .Net? I am using VWD2005 Express Edition and MS SQL 2000 server on a WinXp pro with MS Framework 2.0

I am using o trying and some variants :
SelectCommand="@.strsqlvar">
<SelectParameters>
<asp:QueryStringParameter Name="strsqlvar" DefaultValue="strsql.string" />
</SelectParameters>

And it doesn′t work... any help will e well apreciated. thank u in advance.

Lesson number "8" here might help:

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

Executing an string on MS SQL from .Net

Hi there,

I have this:

Dim strSQL As String = "SELECT * FROM GrupoRegistro WHERE Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 5) OR g.Fecha BETWEEN '" & fini & "' AND '" & ffin & "' AND (g.IDTipoCliente = 3)"

How do I execute on .Net? I am using VWD2005 Express Edition and MS SQL 2000 server on a WinXp pro with MS Framework 2.0

I am using o trying and some variants :
SelectCommand="@.strsqlvar">
<SelectParameters>
<asp:QueryStringParameter Name="strsqlvar" DefaultValue="strsql.string" />
</SelectParameters>

And it doesn′t work... any help will e well apreciated. thank u in advance.

Lesson number "8" here might help:

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

sql

Execute SSIS package from ".vbs" file

I need to execute a SSIS package from a ".vbs" file on a computer that don't have SSIS installed on it.

Thank You

The only way to remotely execute a SSIS package is to set up a SQL Server Agent job to run the package and run that remotely. If you can find a way of doing that in a .vbs file then you're laughing.

-Jamie

|||You can use ADO (from VBS) to call sp_start_job for an existing SQL Server Agent job, or even create a new job and then start. SSIS is not client/server as per SQL Server, it executes the package on the host machine. You need to use another technology to communicate wth the "server", hence the suggestion of using SQL Agent to host the process.

Execute SQL Task w/ XML Output

I have a stored procedure that returns XML and have defined my own "Root" element w/ "ROOT('urlset')" in the stored procedure. When I put this into my Control Flow, I have a subsequent task that takes this XML stream and writes it to a file. The issue I am running into is that SSIS is adding it's own "Root" element before my output. Is there any way of avoiding this?
Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.|||

Matt Masson - MSFT wrote:

Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.

Hi Matt,

Why? What's the rationale for this behaviour?

-Jamie

|||

Good question.

The task doesn't really parse the input SQL statement (or the results, for that matter), so it appears the tags were added as a simple way to ensure we were always returning a well-formed XML document.

One could argue that this should be optional behaviour, and that we should provide a property which allows you to tell the task to leave the results alone. I've opened a tracking item to consider the change for Katmai.

Feel free to open an item on Connect if you have ideas on how it should work.

~Matt

|||

No need. You beat me to it

Execute SQL Task Error: no result rowset associated...

I am getting the following error when I execute my sql task:

An error occurred while assigning a value to variable "NullVar": " NO result rowset is associated with the execution of this query. "

I am executing a SP that has one input & one output parameter. The output parameter is returning a single row for debugging if the sp failes.
I tried using Jamie's method:(http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx) to get it to work but keep getting the above error. I have the following variables:

sqlSource (string) := Exec RBCprcsInsertWmsInvTransactionRecords '" + (DT_WSTR,10 ) @.[User::SnapShotDate] + "', NULL"
NullVar (string)

In the execute sql task, I set the ResultSet to single row. I set SQLSourceType = variable & sourcevariable = user::SqlSource. In the result tab, I added a result set, NewResultName with the variable user::NullVar. I tried different configurations with the parameter mappings but nothing seemed to work. I didn't know if i still had to use this if I am using the sqlSource variable to drive the task.

So I am not sure what I am missing here. Anyone have any suggestions?

Thanks!
John

I would try to catch the SP output via parameter mapping in the execute sql task.

For that, you have to change ResultSet=None; delete the result set from result set tab; and add 2 entries in the parameter mapping page; these 2 entries have to be in the same order you are using the parameters in the Exec t-sql command. If you are using OLE DB connection; the parameter name in the parameter mapping page should be 0 and 1.

The SQLSourceType should be DirectInput and the SQLStatment: EXEC RBCprcsInsertWmsInvTransactionRecords @.yourInputParameterName = ?, @.YourOutputParameterName = ? OUTPUT

|||

Rafael,

I was missing how to correctly specify the parameters. Thanks for giving me the correct syntax! I had tried something similar to this before using the variable to pass the sql statement but didn't have it quite right.

Can i add one more question onto this? Do I have to turn on logging inorder to capture this output variable to a log file?

Thanks again!
John

|||

laker_42 wrote:

Can i add one more question onto this? Do I have to turn on logging inorder to capture this output variable to a log file?

Package logging would give you logging information about package execution; I don’t think is the way to go for getting the value of a SSIS variable written into a file. I cannot think on a quick way to do so; but perhaps you could use a script task.

|||Ok, i will do some more digging on that. Thanks for your help!

John