Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure
Feb 8, 2008
Hi
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................
FROM @Temp T
LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )
Unfortunately this is not working.
Giving the errror
Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance
View 4 Replies
ADVERTISEMENT
Oct 2, 2007
I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?
View 7 Replies
View Related
May 14, 2008
Hello,
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
Insert Into @NewTableNameOut
Select * From tableToSelectFrom
)
------------------------------------------------------------------------------------
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)
------------------------------------------------------------------------------------
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
Is this possible at all?
If you need more info please let me know.
View 1 Replies
View Related
Jul 23, 2005
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies
View Related
Apr 26, 2008
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies
View Related
May 6, 2015
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
View 4 Replies
View Related
Sep 19, 2000
How do I use a @variable to hold on value return from an exec ('string command') statement.
Example for:
declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)
set @db = 'DBNAME'
set @OldOwner = 'USERNAME'
select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
exec (@cmd)
How can I use @OldID to hold on UID return from this statement.
When I try use:
select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
then I'm getting a error message: @OldID not declare.
Thanks.
View 2 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
Mar 27, 2006
Hello to all!
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
Any idea how to solve this problem?
Thanx,
Ziga
View 3 Replies
View Related
Jul 19, 2012
I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
Simple example:
   declare @tTable(col1 int)
   insert into @tTable(col1) values (1)
   select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
View 11 Replies
View Related
Jul 20, 2005
Hello Forum !I want to have the tablename "dbo.Enbxxxx" as an additional parameterfor a procedure like this:ALTER Procedure prcSucheUNR(@UNR int)Asset nocount onSELECT ABRUFNR,UNR,STICHTAG,Datum,InhaltINTO #temp FROM dbo.Enb WHERE UNR = @UNRFor some reason:@tablename varchar(11),and: INTO #temp FROM @tablename WHERE .... does not work. :-(I get the following syntax Error:Zeile 33: Falsche Syntax in der Nähe von '@tablename'.I'am sure it is possible but i don't know how.Greetings
View 3 Replies
View Related
Aug 24, 2007
I need to use a variable for a table name, pass it to a stored procedure that extracts data, and then store the result of the sp in another variable.
For example, earlier in the program I construct the table name using this statement:
@tablename = 'OC_TEMPLATE_' + @FORM. The resulting table name may by something like: OC_TEMPLATE_101
Now I need to use this table name in a statement like this:
SELECT ITEM FROM @TABLENAME WHERE DEX = @REC
If I use code like this, I can get a result, but I can't store it in a variable:
EXECUTE ('SELECT ITEM FROM ' + @TABLENAME + ' WHERE DEX = ' + @REC)
Running this give me a value of 309 in the results pane in Management Studio. 309 is the value I want, but I want to now store that in a variable called @ITEM.
I'm trying to run this as a stored procedure where I pass @tablename and @rec into the proc and I want to return @item as an output parameter with the value of 309 (in this case) in @item. I can't figure out how to configure the proc to give me the output parameter.
Any ideas?
T
View 6 Replies
View Related
Oct 31, 2007
I am working on an mailing list program and I am trying to create a preview option for the sender (to preview their message prior to sending)
I am new to .net and sql but I thought it might be best to do this with table variable inside a stored procedure.. I just do not know how to insert the data into the table variable and select that data at the same time, to return it back to a preview.aspx page.
Please forgive my ignorance..
here is my stored procedure..
ALTER PROCEDURE [dbo].[proc_MessagePreview]
@Subject varchar(150),
@EmailMessage varchar(max)
AS
BEGIN
DECLARE @MessagePreview Table(Subject varchar(150), EmailMessage varchar(max))
INSERT INTO @MessagePreview (Subject, EmailMessage)
VALUES (@Subject, @EmailMessage)
SELECT Subject, EmailMessage FROM @MessagePreview
END
any suggestion or help to point me in the right direction is appreciated, thanks!
View 3 Replies
View Related
Jul 10, 2007
I want to pass the results of a stored proc into a table variable in SQL SEVER 2000. Something like this
declare @a table
(
employeeid int
)exec @a=dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2
How can i do this? This is throwing an error.
Thanks.
View 8 Replies
View Related
May 5, 2000
I need to execute a stored procedure which selects all columns from the passed table. The table used is a variable.
Select * from @Passedtablename. This won't work. Any insights.
View 1 Replies
View Related
Jul 29, 2015
What is the purpose of creating table variable and inserting data into it and selecting from table variable inside of stored proc?
Why can't the stored proc just have select statement instead of creating table variable?
View 3 Replies
View Related
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Nov 5, 2007
Hello
Which is faster :
to write a a big stored procedure with if conditions, or to separate them and call them using exec??
i.e:
if @id=1 insert into ....else if @id=2 update...-----------------------orif @id=1 exec InsertProcedureelse if @id=2 exec UpdateProcedurePlease help
View 8 Replies
View Related
Jan 10, 2006
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
View 6 Replies
View Related
Aug 20, 2007
Hi,
I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!
I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!
The ole db command calls a stored procedure with two input variables:
exec stp_updedgrsholds status, temp_cr_num
from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.
but nothing gets updated in the database.
If I call the stored procedure the following way
exec stp_updedgrsholds 'C', 87
It updates fine! I have tried a number of different way with @ symbols and assignment p_status = @status
but nothing seems to work.
Any ideas are much appreciated.
Ninder Bassi
View 4 Replies
View Related
May 23, 2007
hi,
how do I exec stored procedure that accept parameter and return a single value?
here is example of report
stu_id = ******
stu_name = ****
subject | marks
aa****** | call sp_mark and return student mark for that particular student id and subject
bb****** | call sp_mark and return student mark for that particular student id and subject
cc****** | call sp_mark and return student mark for that particular student id and subject
thks,
View 3 Replies
View Related
Apr 29, 2004
Hi there
i have a stored procedure like this:
CREATE PROCEDURE SP_Main
AS
SET NOCOUNT ON
BEGIN TRANSACTION
exec SP_Sub_One output
exec SP_Sub_Two output
IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran
return
GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
Thanks in advance
Mahmoud Manasrah
View 1 Replies
View Related
Jan 7, 2014
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
The structure of the periods table is as follows:
[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL
The stored procedure is currently:
USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
View 1 Replies
View Related
May 2, 2008
I am trying to create a stored procedure that will take a text value passed from an application and update a table using the corresponding integer value using a CASE statement. I get the error: Incorrect syntax near the keyword 'SET' when I execute the creation of the SP. What am I missing here? This looks to me like it should work. Here is my code.
CREATE PROCEDURE OfficeMove
-- Add the parameters for the stored procedure here
@UserName nvarchar(10),
@NewLocation nchar(5),
@NewCity nvarchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Execute as user = '***'
DELETE FROM [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
WHERE User_Name = @UserName
INSERT INTO [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
SET User_Name = @UserName,
Room_ID = @NewLocation
UPDATE [SQLSZD].[SZDDB].dbo.Employee_Locations
SET Office_ID =
CASE
WHEN @NewCity = 'Columbus' THEN 1
WHEN @NewCity = 'Cleveland' THEN 2
WHEN @NewCity = 'Cincinnati' THEN 4
WHEN @NewCity = 'Raleigh' THEN 5
WHEN @NewCity = 'Carrollwood' THEN 6
WHEN @NewCity = 'Orlando' THEN 7
END
WHERE User_Name = @UserName
END
GO
View 4 Replies
View Related
Mar 12, 2008
I Have a problem When I execute a stored procedure from query analyzer
(Exec storedname @parameter1='', @Parameter2='') it take 7 min. and I stop running
If I copy stored procedure , past it in Query analyzer and declare parameters
it take 3 sec.
View 3 Replies
View Related
Feb 12, 2008
Hello,
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?
Thanks
Arun
View 4 Replies
View Related
Sep 20, 2005
Is there a way to execute a stored procedure and have it move on without waiting for a response from the stored procedure. I am trying to create a button on a webpage that will execute a stored procedure but the procedure takes to long to run and my page times out. Instead I would like the button to start the procedure and the webpage look at a table of data. When the table of data is empty then I will know the stored procedure is complete. Is this possible?
View 1 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Feb 21, 2004
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.
View 9 Replies
View Related
Oct 1, 2007
Hi All,
I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me. within stored procedure.
Note: If run the statement using EXEC() command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.
Any idea how to capture as I would like capture the result and stored it in a table.
Thank you.
--Israr
View 4 Replies
View Related
Feb 13, 2008
Hi,
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.
Debbie
View 4 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Apr 15, 2008
Create table tbl(title nvarchar(40))
Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
Set @sstr = N'Alter Table tbl Add Constraint df_title Default '+ @de + ' For title'
Exec sp_executesql @sstr, @de
End
Execute df_bppr @de = 'NoTitle'
******************
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'NoTitle'.
Msg 128, Level 15, State 1, Line 1
The name "NoTitle" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
--------------------
I created the procedure without any error. But when i execute the procedure it shows the above error
I want to create a proc which dynamically change the default value for more than one column with same default value.
Vijai
View 6 Replies
View Related
