Excel Import

Thursday, March 29, 2012

Exec Stored Procedure

Hi,

Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.

Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.

Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).

Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?

Thanks

Rich

To do exactly what you need - use OLE DB Command Transformation - part of the data flow. Use ? for column parameters.

IMHO: There are two schools that define how to do this: ETL and ELT. What you are trying to do is ETL - the disadvantage of this is if you have a lot of rows - this method will execute the SP as many times - this is performance impact.

In ELT approach you load the data with Bulk Insert and then apply a SQL statement or another set of trasnformations to load the target.

|||

This is helpful Dima, particularly your comments on ETL & ELT, but it doesn't quite hit the spot in terms of what I want to achieve.

Maybe because I'm being thick. My fundamental problem is that my OLE DB connector will not return any column names. When I add the OLE DB transform it just whinges that I don't have any column names.

As I see it your solution will allow me to connect two tables through OLE DB connectors, drag them into OLEDB transform, do my changes, and then output columns - are you saying that there is no way for SSIS to derive column names from a stored procedure without iterating through the recordset one record at a time? This sounds totally useless, but completely feasible.....

As I have already done the work I really just want to get the stored procedure results into SSIS via my OLE DB source, if you are saying I have to start again then I will, but in truth if using this technology means I have to rewrite all my existing SP code as data flows then it is about as much use to me as a chocolate teapot, I have years of work wrapped up in stored procedures and also I would prefer to distribute the load, I want as few transformations as possible in SSIS, the primary use for the server is reporting, not data transforms, we have plenty of spare DB capacity across the planet and bandwidth is at a premium so thinking about what you've said I think we are better off restricting the recordset at source.

|||

I feel your pain :)

What is your OLE DB source? a DB or file? If you have weird source - you may want to see if there is newer ODBC or OLE DB driver out there.

For the existing SP's:

If you already have SP's that accept fields and insert them into a table, row by row - you should be able to reuse them. You can map source fields to the OLE DB command parameters.

|||

Hi Dima,

Hmm... Just to let you know I worked round the problem after reading your answers - my stored procedures now delete and create tables, which I then query, not very efficient but hey, it works, and I can schedule them monthly just before my reports need doing. Anyway, it is only MS SQL I have a problem with, all my Oracle stuff is based on views anyway and it works fine (?)

I tried using the ADO.NET source instead but this is even more messed up.

Path of least resistance until Microsoft sort this mess out, I think.

Thanks for your help

Rich

|||I guess you are doing something wrong... You can call a SP in an OLEDB datasource (described here: http://msdn2.microsoft.com/en-us/library/ms141696.aspx). I tried that with Adventureworks using the SP they suggested for testing with parameters (just entered a one as value for each ?). It returned the metadata correctly...|||I've had similar problems where SSIS doesn't correctly identify columns if you have anything more than a vanilla SELECT statement in the s/p. The solution is to do a SET NOCOUNT ON at the start of the s/p. In addition, there seems to be a problem if the s/p uses temporary tables (i.e. #TableName). It works fine if using table variables though.

Greg.

Exec Stored Procedure

Hi,

Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.

Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.

Thanks

Rich

To do exactly what you need - use OLE DB Command Transformation - part of the data flow. Use ? for column parameters.

In ELT approach you load the data with Bulk Insert and then apply a SQL statement or another set of trasnformations to load the target.

|||

This is helpful Dima, particularly your comments on ETL & ELT, but it doesn't quite hit the spot in terms of what I want to achieve.

Maybe because I'm being thick. My fundamental problem is that my OLE DB connector will not return any column names. When I add the OLE DB transform it just whinges that I don't have any column names.

|||

I feel your pain :)

What is your OLE DB source? a DB or file? If you have weird source - you may want to see if there is newer ODBC or OLE DB driver out there.

For the existing SP's:

If you already have SP's that accept fields and insert them into a table, row by row - you should be able to reuse them. You can map source fields to the OLE DB command parameters.

|||

Hi Dima,

I tried using the ADO.NET source instead but this is even more messed up.

Path of least resistance until Microsoft sort this mess out, I think.

Thanks for your help

Rich

Greg.

EXEC stored procedure

I have a stored procedure that creates temp tables. It has two parameters,
@.tablename and @.type. I execute it with the following command.
EXEC _sp_CreateTempTable 'TableA', 'T'
With these parameters, this will create a temp table named ##TMP_TableA
based on the original table TableA.
The problem is when I run this on one of our servers it works and when I run
it on another I get the following error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Help!
DaveCan you post the proc?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>|||Unfortunately not. It is proprietary information.
Do you think the error is coming from within the executed proc?
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
> Can you post the proc?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>>I have a stored procedure that creates temp tables. It has two
>>parameters, @.tablename and @.type. I execute it with the following command.
>> EXEC _sp_CreateTempTable 'TableA', 'T'
>> With these parameters, this will create a temp table named ##TMP_TableA
>> based on the original table TableA.
>> The problem is when I run this on one of our servers it works and when I
>> run it on another I get the following error.
>> Msg 102, Level 15, State 1, Line 1
>> Incorrect syntax near ','.
>> Help!
>> Dave
>|||> Do you think the error is coming from within the executed proc?
Yes. I suspect the proc is executing a dynamic SQL statement (since you are
passing the table name parameter) and that's where the error is.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:6Ungh.9006$495.7049@.trnddc06...
> Unfortunately not. It is proprietary information.
> Do you think the error is coming from within the executed proc?
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Can you post the proc?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "DaveF" <dave@.aol.com> wrote in message
>> news:5Ingh.8974$495.126@.trnddc06...
>>I have a stored procedure that creates temp tables. It has two
>>parameters, @.tablename and @.type. I execute it with the following
>>command.
>> EXEC _sp_CreateTempTable 'TableA', 'T'
>> With these parameters, this will create a temp table named ##TMP_TableA
>> based on the original table TableA.
>> The problem is when I run this on one of our servers it works and when I
>> run it on another I get the following error.
>> Msg 102, Level 15, State 1, Line 1
>> Incorrect syntax near ','.
>> Help!
>> Dave
>>
>|||I have a solution for you but unfortunately can't post it because it's
proprietary.
Just kidding. Try this and see if it works any better:
EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
Make sure the SP is the same version (accepts the same # of parameters) on
both servers.
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>|||Thanks for the idea but that didn't work.
I decided that since it worked on one machine and not the other that I would
compare Management Studio options and sure enough I found a difference. I
made the change and the error went away. Don't ask me why this particular
change fixed it because I don't know why.
Options-->Query Execution-->SQL Server-->Advanced
check SET CONCAT_NULL_YIELDS_NULL
Dave
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23loaRX$HHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I have a solution for you but unfortunately can't post it because it's
>proprietary.
> Just kidding. Try this and see if it works any better:
> EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
> Make sure the SP is the same version (accepts the same # of parameters) on
> both servers.
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>>I have a stored procedure that creates temp tables. It has two
>>parameters, @.tablename and @.type. I execute it with the following command.
>> EXEC _sp_CreateTempTable 'TableA', 'T'
>> With these parameters, this will create a temp table named ##TMP_TableA
>> based on the original table TableA.
>> The problem is when I run this on one of our servers it works and when I
>> run it on another I get the following error.
>> Msg 102, Level 15, State 1, Line 1
>> Incorrect syntax near ','.
>> Help!
>> Dave
>|||"DaveF" <dave@.aol.com> wrote in message news:i2Hgh.51$hy6.13@.trnddc05...
> Thanks for the idea but that didn't work.
> I decided that since it worked on one machine and not the other that I
> would compare Management Studio options and sure enough I found a
> difference. I made the change and the error went away. Don't ask me why
> this particular change fixed it because I don't know why.
> Options-->Query Execution-->SQL Server-->Advanced
> check SET CONCAT_NULL_YIELDS_NULL
Sounds like there's a NULL value being concatenated into a string somewhere
in your SP, most likely in some dynamic SQL you're trying to execute. That
setting controls null concatenation in strings. When it's set to ON,
concatenating NULL to a string returns NULL. When it's set to OFF, when a
NULL is concatenated to a string it's treated like '' - an empty string.
Here's an example that shows the difference:
DECLARE @.table VARCHAR(255)
SELECT @.table = 'syscomments'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: syscomments', @.sql
SET CONCAT_NULL_YIELDS_NULL ON
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
GO
SET CONCAT_NULL_YIELDS_NULL ON
When CONCAT_NULL_YIELDS_NULL is ON, appending NULL to a string returns NULL.
SQL Server will EXEC a NULL dynamic SQL string without complaint. When
CONCAT_NULL_YIELDS_NULL is OFF, the table name in this SELECT query is
appended as an empty string which will cause an error when SQL Server tries
to execute it.
Based on what you said, it sounds like one of the dynamic SQL statements
you're trying to execute has a NULL value appended to it somewhere, and
that's causing your error when that setting is OFF. It also means that that
particular dynamic SQL statement you think is being executed is not being
executed. Might want to check all your dynamic SQL statements that should
be executing (SELECT or PRINT them out right before the EXEC statement).

exec stored proc and use udf or select in it

Hi,
I have a stored procedure which has a cursor that populates a table
datatype variabel with few fields on eof which is of type [nText].
Now for each row in the above table type variabel I have to call
another stored proc to which I have to pass the [nText] field value.
Can anyone suggest how to do it?
Thanks,
Vikas.Could you call the stored procedure passing the ntext value before inserting
it into the table variable? You could call it after inserting as well, but
before fetching the next value from the cursor.
"vikassah@.gmail.com" wrote:

> Hi,
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
> Thanks,
> Vikas.
>|||(vikassah@.gmail.com) writes:
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
I don't really understand what you are trying to do. Maybe you could
be more specific?
Note, though that you cannot assign to variables of the type ntext,
so you may have to rework your strategy.
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

Exec Stored Proc (C#) - the Size property has an invalid size of 0

Hi All
I am trying to execute a stored procedure that does a very simple
lookup and returns a text field. However, when I try to execute it, I
am getting a rather strange error that I can't seem to fix!
There is defiantely information coming back as I have tested this in
Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
String[1]: the Size property has an invalid size of 0.
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.InvalidOperationException: String[1]: the
Size property has an invalid size of 0.
Many thanks in advance for your help
Darren
STORED PROC CODE
=================
ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
-- Add the parameters for the stored procedure here
@.iID int,
@.tXML text = null output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
pRINT @.tXML
END
C# CODE
=======
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = m_sConnectionString;
oConn.Open();
SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
oConn);
oCmd.Connection = oConn;
oCmd.CommandType = CommandType.StoredProcedure;
SqlParameter spID = oCmd.Parameters.Add("@.iID",
SqlDbType.Int);
spID.Direction = ParameterDirection.Input;
spID.Value = iSQLCacheID;
SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
SqlDbType.Text);
spXML.Direction = ParameterDirection.Output;
oCmd.ExecuteNonQuery();
oConn.Close();
XmlDocument xdDBCache = new XmlDocument();
xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
return xdDBCache;
}Just a guess, but since the sp is going to return data, I don't think you
should use ExecuteNonQuery. ExecuteNonQuery is used for executing statements
that don't return a result set (like UPDATE or DELETE).
"daz_oldham" wrote:

> Hi All
> I am trying to execute a stored procedure that does a very simple
> lookup and returns a text field. However, when I try to execute it, I
> am getting a rather strange error that I can't seem to fix!
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> 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.InvalidOperationException: String[1]: the
> Size property has an invalid size of 0.
> Many thanks in advance for your help
> Darren
> STORED PROC CODE
> =================
> ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
> -- Add the parameters for the stored procedure here
> @.iID int,
> @.tXML text = null output
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Insert statements for procedure here
> SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
> pRINT @.tXML
> END
>
> C# CODE
> =======
> SqlConnection oConn = new SqlConnection();
> oConn.ConnectionString = m_sConnectionString;
> oConn.Open();
> SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
> oConn);
> oCmd.Connection = oConn;
> oCmd.CommandType = CommandType.StoredProcedure;
> SqlParameter spID = oCmd.Parameters.Add("@.iID",
> SqlDbType.Int);
> spID.Direction = ParameterDirection.Input;
> spID.Value = iSQLCacheID;
> SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
> SqlDbType.Text);
> spXML.Direction = ParameterDirection.Output;
> oCmd.ExecuteNonQuery();
> oConn.Close();
> XmlDocument xdDBCache = new XmlDocument();
> xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
> return xdDBCache;
> }
>|||daz_oldham (Darren.Ratcliffe@.gmail.com) writes:
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> 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.
The error message is unknown to me, and I can't say where it's coming
from. However, I do spot an error:

> @.tXML text = null output
This won't fly. text for output parameters is bound to fail. You
cannot assign to variables of the type text.
If you are on SQL 2005, use varchar(MAX) instead. Or even better the
xml data type.
If you are on SQL 2000, return the XML column as a result set instead.
(In which case you must use something different than ExecuteNonQuery
to retrieve the data.)
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|||Mark Williams (MarkWilliams@.discussions.microsoft.com) writes:
> Just a guess, but since the sp is going to return data, I don't think
> you should use ExecuteNonQuery. ExecuteNonQuery is used for executing
> statements that don't return a result set (like UPDATE or DELETE).
But Daz's procedure does not return any result set, but returns data in
an OUTPUT parameter (or would have returned, had he chosen a data type
that is eligible for output parameters). The procedure also includes a
PRINT statement. Both of these are fine with ExecuteNonQuery. (To get
the data from the PRINT statement you need an InfoMessage event handler.)
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|||Hi Erland
I have changed this to get the value out via a data reader, and it is
spot on.
I have never been aware in the past about having a text value as an
output parameter, but I know now!
I am currently using SQL 2000 so can't take advantage of the added XML
benefits in 2005 which is a shame really.
Many thanks for your help - and everyone else too.
Regards
Darren

Exec Stored Proc

I need some help with the following store proc, something is wrong but I just dont see it.
Thanks
btw I am no expert at sp so something might be complety wrong.

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnNamenvarchar (50),
@.dtDate1 Date,
@.dtDate2 Date
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ' + ''' + @.dtDate1 + ''' + ' AND ' + ''' + @.dtDate2 + '''')i havent tried it but check if this works:


EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName +
 ' BETWEEN ' + '''' + @.dtDate1 + '''' + ' AND ' + '''' + @.dtDate2 + ''''

hth|||Try this:

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ''' + @.dtDate1 + ''' AND ''' + @.dtDate2 + '''')|||How many possible values for @.strColumnName could there be? For performance and code reliability reasons I would write it like below. That way the SQL is precompiled (allowing the db engine to skip a step during execution and also allowing syntax errors to be spotted at the time of sp creation instead of execution.


ALTER PROCEDURE dbo.GetSearchByDateRange(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as
 if (@.strColumnName = 'Birthdate')
 begin
 SELECT * FROM Customers
 WHERE Birthdate BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 if (@.strColumnName = 'LastPayment')
 begin
 SELECT * FROM Customers
 WHERE 'LastPayment'BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 ETC...

|||Thank you all for the infos.
Corbi I think you had a good point there and I will definatly take it in consideration.

Oh last thing how do I control the injection of a date from my code to the store proc if my db is using small dates? I see that it can cause problem if I don't send the right format to the sp.
Thanks again|||If possible, i would change the stored procedure parameters to "smalldatetime", process the different date formats in your application and convert your userinput to "smalldatetime" there.

Hth,

Moon|||the only things that need to be considered in the Execute(Whatever Valid SQL Query in string format) is to have double single qoutes for each date value in the query, and that those date values are converted to a valid string (this is because you need to concatenate a string, so try this

Declare @.SQLString As VarChar(8000)

Set @.SQLString = 'SELECT * FROM Employees WHERE ' + @.strColumnName + ' BETWEEN ''' + Convert(Char(8), @.dtDate1, 112) + ''' AND ''' + Convert(Char(8), @.dtDate2, 112) + ''''

Exec (@.SQLString)

This will Work fine

Delfino III Salinas Sepúlveda
DSS Hi-Tech, México
diiisalinas@.prodigy.net.mx|||Also use DateTime or smalldatetime types.

Thursday, March 29, 2012

Exec Stored Procedure

Exec Stored Procedure

EXEC stored procedure

EXEC stored procedure

exec stored proc and use udf or select in it

Exec Stored Proc (C#) - the Size property has an invalid size of 0

Exec Stored Proc

Excel Import

Blog Archive

About Me