T-SQL (SS2K8) :: Store Result Of Stored Procedure Into XML / Nvarchar (max) Variable
Feb 16, 2012
I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
Declare @xml nvarchar(max)
EXEC @xml = StoredProc1 @Id
This doesn't work as expected as @xml doesn't get any value assigned or rather I would say
EXEC @xml = StoredProc1 @Id
outputs the entire xml whereas it should just save the xml in a variable.
View 7 Replies
ADVERTISEMENT
Oct 16, 2007
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure.
I hope i'm being clear.
Any help will be appreciated.
Here is an example of how i am finding my number
Employee is the name of my table and Number is the name of my column.
SELECT Max(Number)
FROM Employee
View 5 Replies
View Related
Feb 29, 2008
name age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
View 7 Replies
View Related
Sep 1, 2007
Hi fellows
I have a query in a NVARCHAR variable I want to execute it , It supposed to create a "Table Variable" ,it is like this :"DECLARE @ProductTotals TABLE
([SideTab]NvarChar(250),[Jun 1 2007 12:00AM] int,[Jun 2 2007 12:00AM] int,[Jun 3 2007 12:00AM] int,[Jun 4 2007 12:00AM] int,[Jun 5 2007 12:00AM] int,[Jun 6 2007 12:00AM] int,[Jun 7 2007 12:00AM] int,[Jun 8 2007 12:00AM] int,[Jun 9 2007 12:00AM] int,[Jun 10 2007 12:00AM] int,[Jun 11 2007 12:00AM] int,[Jun 12 2007 12:00AM] int,[Jun 13 2007 12:00AM] int,[Jun 14 2007 12:00AM] int,[Jun 15 2007 12:00AM] int,[Jun 16 2007 12:00AM] int,[Jun 17 2007 12:00AM] int,[Jun 18 2007 12:00AM] int,[Jun 19 2007 12:00AM] int,[Jun 20 2007 12:00AM] int,[Jun 21 2007 12:00AM] int,[Jun 22 2007 12:00AM] int,[Jun 23 2007 12:00AM] int,[Jun 24 2007 12:00AM] int)"
And it is stored in a variable say @nvcVar.
I want to execute it
I did :
EXECUTE @nvcVar
But it gives me this error :Msg 203, Level 16, State 2, Procedure proc_Report_DailyReport_Karkard, Line 60The name 'DECLARE @ProductTotals TABLE ([SideTab]NvarChar(250),[Jun 1 2007 12:00AM] int,[Jun 2 2007 12:00AM] int,[Jun 3 2007 12:00AM] int,[Jun 4 2007 12:00AM] int,[Jun 5 2007 12:00AM] int,[Jun 6 2007 12:00AM] int,[Jun 7 2007 12:00AM] int,[Jun 8 2007 12:00AM] int,[Jun 9 2007 12:00AM] int,[Jun 10 2007 12:00AM] int,[Jun 11 2007 12:00AM] int,[Jun 12 2007 12:00AM] int,[Jun 13 2007 12:00AM] int,[Jun 14 2007 12:00AM] int,[Jun 15 2007 12:00AM] int,[Jun 16 2007 12:00AM] int,[Jun 17 2007 12:00AM] int,[Jun 18 2007 12:00AM] int,[Jun 19 2007 12:00AM] int,[Jun 20 2007 12:00AM] int,[Jun 21 2007 12:00AM] int,[Jun 22 2007 12:00AM] int,[Jun 23 2007 12:00A' is not a valid identifier.
What is the problem ?!
Thank you in advance ,
View 8 Replies
View Related
Aug 24, 2000
Hi guys. I have been struggling for days now to store the result of a stored procedure from a linkedserver. To make a long story short, here is my code...
CREATE PROCEDURE F_GET_KRONOS_HRS @wono varchar(12)
AS
BEGIN
declare @str nvarchar(2000)
declare @a varchar(10)
select @str = 'select * from openquery(kronos," SELECT decode(a.PAYCATID,1,'+ "'ST'"+",'OT'"+') paycode ,f_calc_hrs(sum(a.AMOUNT)) hrs '
select @str = @str + 'FROM TOTALEDPAYCATEDIT a ,LABORACCT b ,LABORLEVELENTRY c '
select @str = @str + 'where ( c.NAME =' + "'"+'' + @wono +"') "
select @str = @str + 'and (a.LABORACCTID = b.LABORACCTID) '
select @str = @str + 'and c.LABORLEVELENTRYID = b.LABORLEV3ID group by a.PAYCATID ' + '' + '")'
/*
exec sp_executesql @str
*/
create table #t (paycode varchar(7), hrs varchar(255))
insert into #t exec sp_executesql @str
select @a = hrs from #t where paycode='ST'
drop table #t
print @a
if i call exec sp_execute @str, it will output this
PAYCODE HRS
------- --------
ST 08: 30
OT 54: 00
(2 row(s) affected)
I want those #'s store into a temp variable OR be passed to another procedure.
HOW DO I DO IT. This is the last step holding me back from completing my DataWareHouse.
Thanks
View 2 Replies
View Related
Mar 28, 2008
All,
I'm trying to store the results of my store procedure in a temp table. when I try it, I got the error saying...
"Insert exec cannot be nested"
I suspsect this is because I have a Insert Exec statement inside my stored procedure...
Is there any way to come over this issue ?
syntax of my code...
create table #Temp1 (ID int)
insert into #Temp1
EXEC SP1
when I try to run the above code I get the error "Insert exec cannot be nested"
SP syntax :
Create Procedure SP1
as
Begin
create table #Temp2
statements.....
Insert into #temp2
exec SP<Name>
staments.. cont...
END
View 1 Replies
View Related
Mar 25, 2015
My stored procedure returns one row. I need to insert the result to a table. Can I right something like that:
=============
Insert into table1
exec myProc '1/1/15', 3/1/15'
=====
Or may be I can use Table-valued function insted of myProc?
View 4 Replies
View Related
Dec 19, 2005
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why?
create procedure Hellocw_ImportBookmark @userId varchar(80), @FolderId varchar(80), @Insertcontent nvarchar(max)
as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;
View 2 Replies
View Related
Dec 19, 2005
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why?
----------------------13-------------------------------------
create procedure Hellocw_ImportBookmark
@userId varchar(80),
@FolderId varchar(80),
@Insertcontent nvarchar(max)
as
declare @contentsql nvarchar(max);
set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+
@Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+'''';
exec sp_executesql @contentsql;
View 6 Replies
View Related
Apr 8, 2014
I want to add the result of a Select statement inside a parameter, not too sure how to pull this off, but here it is...
--Declare needed variables
Declare @NoOfApprovals Int
--Get count of approvals
Select @NoOfApprovals =
(
select NoOfApprovalsToClaim
[code]...
View 2 Replies
View Related
Mar 18, 2008
Help - I am having a moment!
I am building a simple package that looks at the results of a sql query, prior to running the next step.
Basically Outline and settings
SQL task
Result Set: Full result set
SQL Source: Direct Input
SQL Statement: Select Count (*) FROM X
Parameter Mapping
Variable Name: User::C
Direction: Return Value
Data Type: Numeric
Parameter Name: NewParameterName
Result Set = NewResultName: User::C
Precedence Constraint
Evaluation Operation: Expression and Constraint
Value: Success
Expression: @C > 0 ( I originally tried User::C and received an error message)
Execute SQL Server Agent Job
If step one results greater than 1, then execute the SQL agent job (SRS Report)
Using this setup I receive an error message stating "
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value.
Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task".
(Microsoft.DataTransformationServices.VsIntegration)
Any thought or suggestions would be greatly appreciated.
r/ Anthony
View 7 Replies
View Related
Feb 10, 2000
Hi All,
I need to store the row count from two different servers (one 6.5 and one 7.0)
to compare by doing this:(in T-SQL)
declare @kount1 int, @kount2 int
exec master..xp_cmdshell 'isql -Sserver65 -d dbA -T -Q "select count(*) from tableA",no_output
exec master..xp_cmdshell 'isql -Sserver70 -d dbA -T -Q "select count(*) from tableA",no_output
How can I save the rowcount in @kount1, @kount2 respectively for comparison?
Appreciate your feedback.
David Nguyen
View 4 Replies
View Related
Jul 20, 2005
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies
View Related
Dec 19, 2000
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP
View 4 Replies
View Related
Nov 6, 2007
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
View 1 Replies
View Related
Feb 13, 2008
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ?
What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Thanks,
-Ash
View 8 Replies
View Related
Jan 16, 2008
I have a store procedure that works fine when tested in SQL Management Studio and Visual Studio but when I actually running the page in a browser, it does not work. There is no result generated. Below is my store procedure.1 ALTER PROCEDURE [dbo].[spSearch]
2 -- Add the parameters for the stored procedure here
3 @schoolID int = NULL,
4 @scholarship varchar(250) = NULL,
5 @major varchar(250) = NULL,
6 @requirement varchar(250) = NULL
7 --@debug bit = 0
8 AS
9 BEGIN
10 -- SET NOCOUNT ON added to prevent extra result sets from
11 -- interfering with SELECT statements.
12 SET NOCOUNT ON;
13
14 -- Insert statements for procedure here
15
16 Declare @SQL as NVarchar(4000);
17 Declare @Params as NVarchar(3000);
18 Set @SQL = N'SELECT * FROM [scholarship] WHERE [sectionID] = @schoolID';
19 Set @Params = N'@schoolID int,@scholarship varchar(250),@major varchar(250),@requirement varchar(250)'
20
21 If @scholarship IS NOT NULL
22 Set @SQL = @SQL + N' AND [scholarship].[schlrName] LIKE + ''%'' + @scholarship + ''%'''
23 If @major IS NOT NULL
24 Set @SQL = @SQL + N' AND [scholarship].[Specification] LIKE + ''%'' + @major + ''%'''
25 If @requirement IS NOT NULL
26 Set @SQL = @SQL + N' AND ([scholarship].[reqr1] LIKE + ''%'' + @requirement + ''%'''
27 If @requirement IS NOT NULL
28 Set @SQL = @SQL + N' OR [scholarship].[reqr2] LIKE + ''%'' + @requirement + ''%'''
29 If @requirement IS NOT NULL
30 Set @SQL = @SQL + N' OR [scholarship].[reqr3] LIKE + ''%'' + @requirement + ''%'''
31 If @requirement IS NOT NULL
32 Set @SQL = @SQL + N' OR [scholarship].[reqr4] LIKE + ''%'' + @requirement + ''%'''
33 If @requirement IS NOT NULL
34 Set @SQL = @SQL + N' OR [scholarship].[reqr5] LIKE + ''%'' + @requirement + ''%'')'
35 --If @debug = 1
36 --PRINT @SQL
37 Exec sp_executesql @SQL, @Params, @schoolID, @scholarship, @major, @requirement
38 END
39
View 27 Replies
View Related
Feb 26, 2007
HI, I'm a simple store procedure that returns a result such as this one:
AM
AM-1
AM-2
AM-n
and in other store procedure I need to filter result from this list.
I think that some query like this is impossibile
select fields from table where id in (execute sp)
how can I make this?
Thanks a lot.
View 4 Replies
View Related
Jul 22, 2015
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
View 6 Replies
View Related
Dec 4, 2014
I am calling Store Procedure from my C# Code and inside the SP, I am calling my SSIS Package.It is working fine. On my local because I have all rights to run xp_cmdshell COMMAND. Now I have to transfer this SP to QA and Prod and I don't have rights to run xp_cmdshell COMMAND.
Here is my SP.
declare @cmd varchar(1000)
SET @ssispath = 'SSIS Package Path where my .dtsx package'
set @ExcelF = 'Passing My source file name and full path'
select @cmd = 'C:"program files (x86)""Microsoft SQL Server"100DTSBinnDTEXEC.exe /F "' + @ssispath + '"'
select @cmd = @cmd + ' /X86 /SET Package.Variables[User::ExcelF].Properties[Value];"' + @ExcelF + '" /X86 '
exec master..xp_cmdshell @cmd
My question is, is there other way to run/execute above Store Procedure/SSIS without XP_CMDSHELL Command?
View 6 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
Nov 30, 2000
Hello all,
I am having a hard time getting this to work.
I am trying to build the where statement dynamically but it is a string and needs quotes. But the problem is the quotes are the problem. Are there any escape characters? or is there a way to make this happen??? Integers are easy keys because they do not need quotes.
SET @wheretmp = 'WHERE SourceIPID = BB901625-5E89-45D4-BD20-25730365A9DA'
Select * from tbl_All_Source @wheretmp
View 1 Replies
View Related
Mar 25, 2014
Get output of SQL Procedure and assign it to a variable used in WHERE Clause
Later I want to use this variable in my code in the WHERE Clause
Declare @ProjectNo nvarchar(10)
--Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.
SELECT ID from TABLEA where Project in (@ProjectNo)
How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'
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
Sep 2, 2014
I'm trying to create a Character string so that I can execute dynamic SQL.
The date is going to change.
DECLARE @Select VARCHAR (50)
DECLARE @SQLQuery VARCHAR (500)
DECLARE @PreSelect CHAR (1)
DECLARE @CurrentDate Date
SET @SQLQuery = 'SELECT CAST(CAE_RDB_ENTRY_DATE as Date), *
FROM OPENQUERY(LS_RDB_DWH,'
SET @PreSelect = '''
SELECT @Preselect AS PreSelect
If I try this statement which what I really want. I would like to include the Quote with the Select.:
SET @Select = ''SELECT * FROM RDB_DWH_ASSOCIATE_ENTITY WHERE CAE_RDB_ENTRY_DATE >''
I get the following error:
Invalid object name 'RDB_DWH_ASSOCIATE_ENTITY'.
View 9 Replies
View Related
Dec 26, 2007
I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, , Name)
View 1 Replies
View Related
Jan 24, 2004
Hi All
I have a stored procedure, sp_GetNameDetail, which return a one row, multiple columns result set.
Yet I have another storede procedure which would call sp_GetNameDetail, and would like to access this result set. Is there a way I can do this?
Thanks,
View 1 Replies
View Related
Dec 22, 2005
Hello evry1.
i m new to SQL Server2000. plzz tell me where i m
wrong in this SP . this procedure is not giving any
error but it is not showing any result. plzz help me n
give me any idea if u know how i can solve my problem
best regards
sadaf
n here is the procedure
CREATE PROCEDURE Search12
( @signup char(50),
@user_name1 [char](50),
@gender1 [char](6),
@place1 [char](100),
@email_address1 [char](50),
@relegion1 [char](50),
@political_view1 [char](50),
@passion1 [varchar](150),
@sports1 [varchar](150),
@activities1 [varchar](150),
@books1 [varchar](150),
@music1 [varchar](150),
@tv_shows1 [varchar](150),
@movies1 [varchar](150),
@cuisines1 [varchar](150),
@intrested_in1 [varchar](150),
@education1 [char](100),
@college_university1 [char](50),
@occupation1 [char](50),
@industry1 [char](50),
@job_desc1 [char](50),
@career_intrest1 [char](100),
@latitude1 [decimal],
@longitude1 [decimal])
AS
if (@user_name1 is not null and len(@user_name1) > 0)
begin
select * from [profile] where [user_name] like
'%@user_name1%' and place like '%@place%' and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@gender1 is not null and len(@gender1) > 0)
begin
select * from [profile] where gender = @gender1 and
place like '%@place%' and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if(@email_address1 is not null and
len(@email_address1) > 0)
begin
select * from [profile] where [email-address] like
'%@email_address1%' and place like '%@place%' and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if(@relegion1 is not null and len(@relegion1) > 0)
begin
select * from [profile] where relegion = @relegion1
and place like '%@place%' and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@political_view1 is not null and
len(@political_view1) > 0)
begin
select * from [profile] where political_view =
@political_view1 and place like '%@place%' and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@passion1 is not null and len(@passion1) > 0)
begin
select * from [profile] where passion like
'%@passion1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if(@sports1 is not null and len(@sports1) > 0)
begin
select * from [profile] where sports like '%@sports1%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@activities1 is not null and len(@activities1) >
0)
begin
select * from [profile] where activities like
'%@activities%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@books1 is not null and len(@books1) > 0)
begin
select * from [profile] where books like '%books1%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@music1 is not null and len(@music1) > 0)
begin
select * from [profile] where music like '%@music%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if(@tv_shows1 is not null and len(@tv_shows1) > 0)
begin
select * from [profile] where tv_shows like
'%@tv_shows1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@movies1 is not null and len(@movies1) > 0)
begin
select * from [profile] where movies like '%@movies1%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@cuisines1 is not null and len(@cuisines1) > 0)
begin
select * from [profile] where cuisines like
'%@cuisines1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@intrested_in1 is not null and len(@intrested_in1)
> 0)
begin
select * from [profile] where intrested_in =
@intrested_in1 and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@education1 is not null and len(@education1) > 0)
begin
select * from [profile] where education
like'%@education1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@college_university1 is not null and
len(@college_university1) > 0)
begin
select * from [profile] where institution like
'%@college_university1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@occupation1 is not null and len(@occupation1) >
0)
begin
select * from [profile] where occupation like
'%@occupation1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@industry1 is not null and len(@industry1) > 0)
begin
select * from [profile] where industry like
'%@industry1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@job_desc1 is not null and len(@job_desc1) > 0)
begin
select * from [profile] where job_desc like
'%@job_desc1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@career_intrest1 is not null and
len(@career_intrest1) > 0)
begin
select * from [profile] where career_intrest like
'%@career_intrest1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
GO
View 6 Replies
View Related
Jul 20, 2005
Hi!How can I save a result set from a stored procedure into a table?Can you show me some examples?Thank you!Darko
View 2 Replies
View Related
Nov 29, 2007
i made a new stored procedure and execute it. it works great but where can i see the result ?
i want to see the number of Count(*) in a window inside the SQL Server Managment studio, can i ?
my stored procedure is as follows :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetTotalMovieCreations]
AS
select count(*)
from tbl_Animations
where statusID = 1
and ToolID = 1
and ToolID = -1
and isDeleted = 0
thanks...
View 4 Replies
View Related
Dec 26, 2007
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
View 5 Replies
View Related
Jan 24, 2008
Hi All,
I have to execute one of the stored procedure within another stored procedure.
And have to store it into some table variable...
How to do that.pls give me the syntax...
Thanks and reagards
A
View 1 Replies
View Related
Jun 12, 2008
My stored procedure displays two result sets. How can i use that result sets in my 3-tier application. I want to bind first resultset to repeater control and second to label control. I am using SqlDataReader...
View 6 Replies
View Related
