Hi!
I am kind of new to the sequel server and I have to write a stored procedure to create raw of my table. I wrote the following stored procedures, but I am not sure weather I can use variables in select statements as in the following procedure. Could you please explain what is the problem, why I am getting an error, when I am trying to create it? <The error says that there is an incorrect syntax near '@tbleName' in lines 21, 25 and 30>
Thank you very much!
Elly.
/* Inserts raw into the talble tbleName. Called from other procs.
tbleName: The name of the table to insert
other variables: values to insert
checkParentId,..: items to be checked upon-parentid, position, parent primary key
Returns:
0 : Successfully inserted a raw into the table
-1 : Position to insert is taken
-16: The specified parent does not exist
-17: Insert was unsuccessful
*/
create procedure sp_acml_createRaw (@tbleName VARCHAR(50), @nameN VARCHAR(50),
@hrefN VARCHAR(50), @posN VARCHAR(50), @parentIdN VARCHAR(50),
@nameV VARCHAR(150),@hrefV VARCHAR(255),
@posV SMALLINT, @parentIdV SMALLINT,
@checkParentId VARCHAR(50), @checkPosition VARCHAR(50),
@checkParentKey VARCHAR(50))
as
BEGIN
/* Check weather the parent exist */
IF exists (select @parentIdN
from @tbleName
where @tbleName.@checkParentKey = @parentIdV) /* if parent exist */
BEGIN
/* Check weather the isertion position is correct */
IF exists (select @checkParentId from @tbleName
where @tbleName.@checkParentId = @parentIdV AND
@tbleName.@checkPosition = @posV)
return -15 /*Insertion position is incorrect - it is taken already*/
/* Insertion postion is correct */
insert into @tbleName(@tbleName.@nameN, @tbleName.@hrefN, @tbleName.@posN, @tbleName.@parentIdN)
values (@nameV, @hrefV, @posV, @parentIdV)
IF (@@ERROR != 0)
return -17 /* Insert was unsuccessful */
return 0
END
return -16 /* The parent specified was not found */
END
/* End of sp_acml_createRaw */
I am writing a stored procedure that basically gets a whole lot of info. Simple enough... that part works. Now I want to add functionality to pass in sortBy and direction variables so the results can be sorted from a web page. The code I have is:
Code:
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER procedure GetRecruiterApplicants @useridvarchar(50), @trackerGroup varchar(50), @sortBy varchar(20), @dir varchar(5)
as begin
set nocount on
SELECT C.USERID INTO #VU FROM VUSERS V WITH (NOLOCK), COMPANIES C WITH (NOLOCK) WHERE CHARINDEX(','+C.USERID+',', ','+VUSERS+',')>0 AND V.USERID=@userid UNION SELECT @userid
SELECT distinct l.AccessCode, l.ApplicantGivenName, l.ApplicantFamilyName, l.DateCreated, l.DateApplicantAccessed, p.ApplicationTitle, l.disabled FROM chamslinks l, chamsProjectIDs p, chamsGroups g WHERE l.TrackerID IN (SELECT userid FROM #VU) AND g.trackerGroup = @trackerGroup AND p.groupTblID = g.groupTblID AND p.ProjectID = l.ProjectID ORDER BY l.@sortBy @dir
DROP Table #VU
set nocount off end
The error I am getting is: "Server: Msg 170, Level 15, State 1, Procedure GetRecruiterApplicants, Line 24 Line 24: Incorrect syntax near '@dir'."
DELETE FROM delflubase WHERE (acad_period = @p_acad_period)
SET @total_students = 0 SET @total_enrolments = 0 SET @acad_year_spurs = 0.00
SET @base = CURSOR LOCAL FAST_FORWARD FOR
SELECT stmaos.student_id, stmaos.acad_period, stmaos.aos_code, stmaos.aos_period, stcstatd.full_desc, stcsessd.full_desc AS SessionDescription, stcsessd.dept_code, stcsessd.aos_start_dt, stcsessd.exp_length, stcsessd.unit_length, ISNULL(stmaos.student_year, 0) AS student_year, stcsessd.hrs_per_week / 100 AS hrs_per_week, stcsessd.aos_end_dt, stcsessd.no_of_weeks, stcsessd.hrs_total / 100 AS hrs_total, stmfesqa.student_type, stcsessd.moa_code, stcfesdt.qual_aim, stcfesdt.subject_code, delsubj.subj_area, ISNULL(delsubj.subj_course_group, 0) AS subj_course_group, stcfesdt.nvq_lvl_ind, stcsessd.geolocn_code, stmbiogr.surname, stmbiogr.forename, stmbiogr.birth_dt, stmadres.post_code, stmaos.attend_mode, stmfesqa.fee_override, stmaos.start_date, RTRIM(stmfesqa.course_status) AS course_status, stmfesqa.course_status_dt as end_date, stmfesqa.spec_learn_disab, FLOOR(DATEDIFF(day, stmbiogr.birth_dt, stsacper.age_date))/365 AS Age, delESOL.subj_code AS ESOL, delspurs.threshold1, /* Log No.132694 - SQL changed*/ delspurs.threshold2, delspurs.threshold3, stmfesqa.outcome, delbasicIT.qual_aim AS basicIT, ISNULL(delsubjarea.subj_area_weight, 0.00) AS subj_area_weight, ISNULL(deloutwgt.weighting, 0.10) AS weighting, ISNULL(stmaos.additionality_yn, 0) AS additionality_yn, nicisreports.dbo.ndaqquals.qual_ref FROM stsacper INNER JOIN stmaos INNER JOIN stcsessd ON stmaos.aos_code = stcsessd.aos_code AND stmaos.acad_period = stcsessd.acad_period AND stmaos.aos_period = stcsessd.aos_period INNER JOIN stcfesdt ON stmaos.acad_period = stcfesdt.acad_period AND stmaos.aos_code = stcfesdt.aos_code AND stmaos.aos_period = stcfesdt.aos_period INNER JOIN stmfesqa ON stmaos.student_id = stmfesqa.student_id AND stmaos.aos_code = stmfesqa.aos_code AND stmaos.acad_period = stmfesqa.acad_period AND stmaos.aos_period = stmfesqa.aos_period INNER JOIN stmbiogr ON stmaos.student_id = stmbiogr.student_id ON stsacper.acad_period = stmaos.acad_period AND stsacper.start_date <= stcsessd.aos_start_dt AND stsacper.end_date >= stcsessd.aos_end_dt INNER JOIN delspurs ON stmaos.acad_period = delspurs.acad_period INNER JOIN stcstatd ON stmaos.aos_code = stcstatd.aos_code LEFT OUTER JOIN deloutwgt ON stcfesdt.nvq_lvl_ind = deloutwgt.nvq_lvl_ind LEFT OUTER JOIN delbasicIT ON stcfesdt.qual_aim = delbasicIT.qual_aim AND stcfesdt.subject_code = delbasicIT.subj_code LEFT OUTER JOIN delESOL ON stcfesdt.subject_code = delESOL.subj_code LEFT OUTER JOIN stmadres ON stmbiogr.student_id = stmadres.student_id AND stmbiogr.perm_add_id = stmadres.add_id LEFT OUTER JOIN delsubjarea RIGHT OUTER JOIN delsubj ON delsubjarea.subj_area = delsubj.subj_area ON stcfesdt.subject_code = delsubj.subj_code left outer join stcstdet on stmaos.aos_code = stcstdet.aos_code inner join nicisreports.dbo.ndaqquals on replace(stcstdet.text_field1,'/','') = nicisreports.dbo.ndaqquals.qual_ref where stmaos.acad_period = @p_acad_period and (stmaos.return_ind = 'F' OR stmaos.return_ind = 'B') AND (stmaos.stage_ind = 'E') AND (stcfesdt.qual_aim IS not null) AND (stcfesdt.qual_aim IS not null) and (stmfesqa.fund_source <> '09' OR stmfesqa.fund_source IS NULL) and stmaos.start_date is not null and stmaos.start_date <= getdate()
Server: Msg 16924, Level 16, State 1, Procedure testflu, Line 210 Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
i am unsure cos from what i can see i ahve included all, am i missing something
I am taking my first steps into stored procedures and I am working on a solution for efficiently paging large resultsets with SQL Server 2000 based on the example on 4Guys: http://www.4guysfromrolla.com/webtech/042606-1.shtml The problem with my stored procedure is, is that it doesn't seem to recognize a variable (@First_Id) in my dynamic Sql. With this particular sproc I get the error message: "Must declare the scalar variable '@First_Id'"It seems to be a problem with 'scope', though I still can't yet figure out. Can anyone give me some hints on how to correctly implement the @First_Id in my stored procedure? Thanks in advance! Here's the sproc: ALTER PROCEDURE dbo.spSearchNieuws(@SearchQuery NVARCHAR(100) = NULL,@CategorieId INT = NULL,@StartRowIndex INT, @MaximumRows INT,@Debug BIT = 0)ASSET NOCOUNT ONDECLARE @Sql_sri NVARCHAR(4000),@Sql_mr NVARCHAR(4000),@Paramlist NVARCHAR(4000),@First_Id INT, @StartRow INTSET ROWCOUNT @StartRowIndexSELECT @Sql_sri = 'SELECT @First_Id = dbo.tblNieuws.NieuwsId FROM dbo.tblNieuwsWHERE 1 = 1'IF @SearchQuery IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'SELECT @Sql_sri = @Sql_sri + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'SET ROWCOUNT @MaximumRows SELECT @Sql_mr = 'SELECT dbo.tblNieuws.NieuwsId, dbo.tblNieuws.NieuwsKop, dbo.tblNieuws.NieuwsLink, dbo.tblNieuws.NieuwsOmschrijving, dbo.tblNieuws.NieuwsDatum, dbo.tblNieuws.NieuwsTijd, dbo.tblNieuws.BronId, dbo.tblNieuws.CategorieId, dbo.tblBronnen.BronNaam, dbo.tblBronnen.BronLink, dbo.tblBronnen.BiBu, dbo.tblBronnen.Video, dbo.tblCategorieen.CategorieFROM dbo.tblNieuws INNER JOIN dbo.tblBronnen ON dbo.tblNieuws.BronId = dbo.tblBronnen.BronId INNER JOIN dbo.tblCategorieen ON dbo.tblNieuws.CategorieId = dbo.tblCategorieen.CategorieId AND dbo.tblBronnen.CategorieId = dbo.tblCategorieen.CategorieId WHERE dbo.tblNieuws.NieuwsId <= @First_Id AND 1 = 1' IF @SearchQuery IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.CategorieId = @xCategorieId' SELECT @Sql_mr = @Sql_mr + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'IF @Debug = 1PRINT @Sql_mr SELECT @Paramlist = '@xSearchQuery NVARCHAR(100), @xCategorieId INT'EXEC sp_executesql @Sql_sri, @Paramlist, @SearchQuery, @CategorieIdEXEC sp_executesql @Sql_mr, @Paramlist, @SearchQuery, @CategorieId
SELECT * FROM Cards WHERE CASE @Type = 1111 THEN CardType = 1111 ELSE CardType = 2222 END
GO
I know that the part after WHERE is wrong. But what I would like to achieve is this:
if the @type variable equals 1111 then get alla the rows with that value in the CardType-column. The same if @type = 2222, and if @type is any other value, then choose all rows regardles of the CardType value.
I want to convert a SQL query as shown below into a stored procedure:
select name from namelist where town in ('A','B','D')
If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.
I am using a table variable inside a stored procedure that I am trying to execute from an OLE Datasource task in IS. I know there was a problem doing this in DTS, which would result in an Invalid Pointer error. I am not getting that error, but I am getting an error that says "[OLE DB Source [55]] Error: A rowset based on the SQL command was not returned by the OLE DB provider." The stored procedure runs fine on it's own.
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails @Id string as Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it. How can i solve this problem?
I have a foreach loop in my SSIS script. I am able to successfully enumerate through an input query. I have a script task inside of my container. I would like to use this task to formulate a Stored Procedure and save this procedrue in a variable so I can use in a future Execute SQL task.
Here is a copy of the code (Which Does Not Work) I am using in the script task to set the variables.
Hi, I need to include two input variables in my Order By Clause in a stored procedure like ORDER BY @column @Dirction. But MS SQL does not allow me to do so and gives an Error 1008. How can i solve this problem?
HiI've heard 2 things recently, can I confirm if their true/false?(1) If you have a stored procedure and you want to optimise it you cancall exec proc1,you could also use define/set for each of the variables and copy thecode into query analyser,this then makes it easier to tune. However the optimiser worksdifferently for these variables than it does for variables passed intothe query via exec and will produce a less optimalplan(2) There is a different optimiser used in query analyser than thatused otherwise? A colleaguehad a problem where a stored procedure called from dotnet code wasrunning slowly butone run from query analyser via exec, with exactly the same arguments,was running quicklyta
How do I pass system variables to a stored procedure? Is it possible to have an OLE DB transformation with the following sql command: exec InsertIntoLog @MachineName, @TaskName...? Do I have to use a Derived Transformation first to 'convert' variables into columns and then use exec InsertIntoLog ?, ? ...
Hi , I am trying to write a stored procedure (i have given it below).i am basically trying to join 4 strings into one based on some if conditions.But the result gives only the intially assaigned string and rest are not getting concatenated.i have provided teh stored procedure below along with the inputs and result i got.Can anyone Please help me to acheive this set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[TestSearch] @distributorId int, @locationId int, @orderTypeId int, @fromDate Datetime = NULL, @toDate Datetime = NULL, @OrderStatus varchar(500) = NULL, @TaxAuthority varchar(500) = NULL, @TaxStampType varchar(500) = NULLASBEGINDeclare @SQL varchar(8000)Set @SQL ='select * from Orders AS a INNER JOINOrderLines AS b ON a.Id = b.FkOrder INNER JOINTaxStampTypes AS c ON b.FkTaxStampType = c.Id where ''' +CONVERT(VARCHAR(8),@fromDate ,1) + ''' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and ''' +CONVERT(VARCHAR(8),@toDate ,1) + '''>= CONVERT(VARCHAR(8), a.OrderDate,1)and a.fkordertype = '+ convert(varchar(1),@orderTypeId) +'anda.FkDistributor = ('+ convert(varchar(50), @distributorId)+',a.FkDistributor)anda.FkLocation in ('+convert(varchar(10),@locationId)+',a.FkLocation)and'IF(@OrderStatus != null)Beginset @SQL= @SQL + 'a.FkOrderState in ('+ @OrderStatus +') and' EndIF(@TaxAuthority!= null)Beginset @SQL = @SQL +'a.FkTaxAuthority in ('+@TaxAuthority+') and'EndIF(@TaxStampType!= null)Beginset @SQL = @SQL + 'c.id in ('+ @TaxStampType+ ')and'End--Execute (@SQL1)select (@SQL);ENDHere is the Input Given to stored Procedure for executing:DECLARE @return_value intEXEC @return_value = [dbo].[TestSearch] @distributorId = 1002, @locationId = 3, @orderTypeId = 1, @fromDate = N'06/10/07', @toDate = N'10/10/07', @OrderStatus = N'2', @TaxAuthority = N'1000', @TaxStampType = N'1000'SELECT 'Return Value' = @return_valueHere Is The Output i get when I execute the stored procedure: select * from Orders AS a INNER JOIN OrderLines AS b ON a.Id = b.FkOrder INNER JOIN TaxStampTypes AS c ON b.FkTaxStampType = c.Id where '06/10/07' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and '10/10/07'>= CONVERT(VARCHAR(8), a.OrderDate,1) and a.fkordertype = 1 and a.FkDistributor = (1002,a.FkDistributor) and a.FkLocation in (3,a.FkLocation) and--Ajay
I have a stored procedure where I gather some data and then insert the data into a table variable. I then attempt to go through each row of the table variable, asign the values to local variables to be inserted into other tables. However, the local variables show as NULL.BEGIN DECLARE @tblcontact table ( SOKey int, Cntctkey varchar(60), Cntctownerkey int, LASTNAME varchar(32), FIRSTNAME varchar(32), WORKPHONE varchar(32), EMAIL varchar(128), processed int DEFAULT 0 )
I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours @DayName varchar,@DayIDName varchar AS BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID END RETURN ...is this possible?? if so how? Thanks
(I have searched this forum extensively, but still can't find the solution to this problem)
Here it is:
I have step in my ETL process that gets facts from another database. Here is how I set it up:
1) I have to package variables called User::startDate and User::endDate of data type datetime
2) Two separate Execute SQL Tasks populate those variables with appropriate dates (this works fine)
3) Then I have a Data Flow Task with OLE DB source that uses a call to a sproc of the form "exec ETL_GetMyData @startDate = ?, @endDate = ?" with parameters mapped accordingly (0 -> User::startDate, 1 -> User::endDate)
When I run this I get an error 0xC0207014: "The SQL command requires a parameter named "@startDate", which is not found in the parameter mapping."
It is true that the sproc in fact requires @startDate and @endDate parameters, so next thing I tried to do is call the sproc the following way: "exec ETL_GetMyData @startDate = ?, @endDate = ?"
To no avail. It gives me the same error. Incidentally, when I hard code both dates like "exec ETL_GetMyData '2006-04-01', '2006-04-02'" everything works well.
Also, I want to mention that in the first two cases, I get an error right in the editor. When I try to parse the statement it gives me "Invalid parameter number" message.
This has been such a pain in my neck. I've waisted the whole day trying to monkey with the various parts of package/statements to get this to work and it still doesn't. I dont' want to say anything about Integration Services design right now, but you probably know what I'm thinking...
I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line (@Tester1 integer,@Tester2 integer,@Tester3 integer,@Tester4 integer,@Tester5 integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen: @Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.
I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:
C++ Extended Stored Procedure:
(Basically all this code is doing is retrieving the parameters and printing them back out)
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.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"
Can someone help me on how to assign a value to a local variable from a dynamic query.
I have a PROC that I want to modify via a variable I will pass in from ASP.Net.
Currently the PROC is as follows:
ALTER Procedure [dbo].[spu_FindProperties] @Search varChar(30) AS Set @Search = '%'+ @Search + '%'
SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1, dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP, dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO FROM dbo.EDCclient INNER JOIN dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID WHERE (dbo.EDCclient.CLIENT like @Search) OR (dbo.EDCproperty.PROPERTY like @Search)
I now want to modify it so that the entire Where clause is created in ASp and passed in. So I think my new PROC should look as follows:
ALTER Procedure [dbo].[spu_FindProperties] @Search varChar(200) AS
SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1, dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP, dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO FROM dbo.EDCclient INNER JOIN dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID
@Search)
But I can't get SQL to accept the @Search in place of the Where... Any ideas?
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.I am looking for a "How to" to put this limit to 8000.Thank you!
