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.
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
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)
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.
I have a stored procedure like the following. This returns 2 result sets, however i only want it to return 2nd (SELECT SomeField FROM SomeTable). How is this done? Note - it is not possible to change 'SomeSPThatReturnsAnIntAndAResultSet '
I want to know how to handle multiple result sets return from Stored Procedure? I know one way is to insert the result sets into the table, but the limitation is the result sets must have the same data structure. If the result sets have different data structure, how can I handle it.
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.
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
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
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?
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
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]
How can I find the result columns from code? Visual J++ Seems to be able to do that. ADO doesn't want to do it for me, neither does ODBC's SQLProcedureColumns().
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
In order to pull back data from several servers to one central server I need to execute a stored procedure over each remote server. SQL 7 doesn't accept the following syntax when comparing date fields:
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.
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.
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.
I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT) AS BEGIN IF @rptType = 1 BEGIN SELECT LastName, FirstName, MiddleInitial
[Code] ....
As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example: set MyUniqueNumber = 20071101190708733 ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following: 1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2. select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.
They tend to look like this:
create procedure UberProc (@QueryId varchar(50)) as
if @QueryId = 'First Horrible Idea' begin select stuff from something end if @queryid = 'Second really bad idea' begin select otherstuff from somethingelse end
I see the following problems with this practice:
1) SQL can't cache the query plan appropriately 2) They are harder to debug 3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.
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
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
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 ?
I need to create a stored procedure that will have about 10-15 queries and take 3 parameters. the variables will be: @lastmonth, @curryear and @id @lastmonth should inherit Session variable intlastmonth @curryear should inherit Session variable intCurrYear @id should inherit Session id One example query is SELECT hours FROM table WHERE MONTH ='" + Session("intLastmonth") + "' AND YEAR ='" + Session("intCurrYear") + "' AND [NUMBER] = '" + Session("id") The rest of the queries will be similar and use all 3 variables as well. How can I go about this and how will queries be seperated.
