Hi everybody, i have a problem and i hope anyone can help me.
I write a sp thayt include some variables. One of it is nvarchar(max) (because i think needed value is too big). Unfortunately, that variable is really too big (about 1000000 chars). It is out of bound of nvarchar(max). How can i do??
Please help me because i am working in it and it is too late now.
Thanks.
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.
Hello to all!
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
Any idea how to solve this problem?
Thanx,
Ziga
hi how can I set my variable value with a query??? I am about to make a procedure to get a comma seperatet array and insert into the table. if I omit ID (primary key) and set it as Identity every thing is ok but I don't want it to be Identity. so I have to find the max of ID and then add it to the first of my comma seperated values. the problem is I have a table name as nvarchar in the parameters. so I have to first make a query as string and then execute it. anyway I need to get the value of the query string and assingn @ID variable to it's return value. how can I do it? so just suppose that I have @Values, @TableName (values are in the right order) then I want to find max(ID)+1 in @TableName (something like "select max(id)+1 from @TableName" I know that it will not work. so I make a string and execute it ) and add it to the first of my @Values then my problem will solve thank you in advance regards
View 3 Replies View RelatedI am building a stored procedure to get a list of rows, and I want to limit the rows returned to a variable. However, i keep getting a syntax error.
SQL:
SELECT TOP @TopArticles ArticleTable.*, ArticleSubSectionTable.SubSectionID AS SSID, ArticleTable.ArticleDate AS EXPR1
FROM ArticleTable
ERROR
Error 170: Line 28: Incorrect Syntax near '@TopArticles'
Any help would be appreciated,
PK
Hello experts!
I have a problem. I am trying to get a value from stored procedure A into a variable in storded procedure B, kind of like this:
Note: don't be fooled by the simplicity of these samples. There is no other way for me to solve this problem, but to get the value from sp_A into a variable in sp_B, since sp_A is in a database out of my control and i have no clue what happens therein...
CREATE PROCEDURE sp_A
AS
SELECT 'Hello world!'
RETURN
GO
CREATE PROCEDURE sp_B
AS
DECLARE @Value nvarchar(50)
SET @Value = --** Here i want to get the value from sp_A
SELECT @Value
GO
Thanks!
/Murti
HelloI am a newbie to this, so I would appreciate any help, I am strugglingto get this to workCREATE PROCEDURE [dbo].[sp_test]@strfinalint as varchar(1000),@startdate as datetime@enddate as datetimeasdeclare @insertcmd as varchar(2000)declare @startdate as datetimedeclare @enddate as datetimeset @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:MyFolder' + @strfinalint + ';'',''SELECT * FROM [Sheet1$]'') Select * from tbltest WHERE S_DateBetween' + @startdate + 'AND' + @enddateEXEC (@insertcmd)GOIt was working with the commandset @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:MyFolder' + @strfinalint + ';'',''SELECT * FROM [Sheet1$]'') Select * from tbltest'But I am struggling to include the WHERE part of it, I seem to haveproblems making the variables work in this with appropriate quotationmarks !!ThanksSunny
View 3 Replies View Relatedhi
how can I set my variable value with a query???
I am about to make a procedure to get a comma seperatet array and insert into the table. if I omit ID (primary key) and set it as Identity every thing is ok but I don't want it to be Identity. so I have to find the max of ID and then add it to the first of my comma seperated values. the problem is I have a table name as nvarchar in the parameters. so I have to first make a query as string and then execute it.
anyway I need to get the value of the query string and assingn @ID variable to it's return value. how can I do it?
so just suppose that I have @Values, @TableName (values are in the right order) then I want to find max(ID)+1 in @TableName (something like "select max(id)+1 from @TableName" I know that it will not work. so I make a string and execute it ) and add it to the first of my @Values then my problem will solve
thank you in advance
regards
Hi,
i need to insert a record 1 or more times, depending of a variable in code-behind:dim amount as integeramount= value (e.g. 3)
My problem is: how to pass that variable to the stored procedure?I tried with this but nothing happens:
comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount_of_details
Maybe is my stored procedure wrong?
Thanks
T.
Here is it:----------
ALTER PROCEDURE dbo.insert_table (@field1 nvarchar(10),...)ASDeclare @iLoopNumber intDeclare @amount intBEGIN TRAN
SET @iLoopNumber = 1
SET @amountr
While (@iLoopNumber <= @amount)
BEGIN
INSERT INTO table(field1,...)
VALUES (....))
SET @iLoopNumber = @iLoopNumber +1
End
COMMIT TRAN
Hi,I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.Help!!!I want to add @OrderBy at the end of :-----------------SELECT *FROM AnnoncesWHERE CategoryID = @CategoryID AND DateOnCreate >= @DateFinale-----------------My declaration :Declare @OrderBy nvarchar(50)If @Sort = 'date_de' set @OrderBy = ' ORDER BY DateOnCreate DESC'Else set @OrderBy = ' ORDER BY DateOnCreate ASC'
View 8 Replies View RelatedI am trying to create a stored procedure that is sent a column name and orders by the column name. conn = New SqlConnection(SQLserver)SQL = New SqlCommand("SearchECN", conn)
SQL.CommandType = CommandType.StoredProcedure
SQL.Parameters.Add("@Search", SqlDbType.Variant)SQL.Parameters.Add("@Sort", SqlDbType.Variant)
SQL.Parameters(0).Value = Search.Text
SQL.Parameters(1).Value = "ECN.ECN"
SQL.Connection.Open()
GVECN.DataSource = SQL.ExecuteReader()
GVECN.DataBind()
SQL.Connection.Close()
SQL.Connection.Dispose()
Stored Procedure
@Search NVARCHAR(MAX),
@Sort NVARCHAR(MAX)
SELECT ECN.ECN, ECN.A, ECN.B FROM ECN WHERE ECN.ECN LIKE @Search OR ECN.A LIKE @Search ORDER BY @Sort
I get the following error
Msg 1008, Level 16, State 1, Line 10
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Any Ideas
hi everybody,
I have two stored procedures lets say A and B and a variable inside stored procedure A lets say @BReturn, now, what is the syntax to execute B inside A and to store its returned value in @BReturn? any help is greatly appriciated.
devmetz.
Hi all.
1. I have a stored procedure, the procedure need create new category for forums, each category have their own place (what going first , what going second etc...), it's importent for me that all categories will be in property for them place, so i must read next place from table, and if not then i set it = 0 . But it's seems not working, i getting error.
Can somebody tell me please what wrong ?
ALTER PROCEDURE dbo.CreateCategory
(
@category_name varchar(100)
)
AS
declare @place int
/* setting place to zero */
SET @place = 0
/* trying to get last place and set value into @place */
SET @place = (Select max(category_place) FROM fo_categories)
/* if got nothing from table then setting value of first category */
IF (@place = 0)
set @place = 1
INSERT fo_categories (category_name, category_place)
VALUES(@category_name, @place)
RETURN
Running dbo."CreateCategory" ( @category_name = Public forums ).
Cannot insert the value NULL into column 'category_place', table 'mg_forum.dbo.fo_categories'; column does not allow nulls. INSERT fails.
2. I also would ask what SET NOCOUNT ON/OFF mean and for what i can use it ?
Hi I am trying to achieve something like:
ALTER PROCEDURE dbo.GetShares
@SortValue varChar(30)= SHARE_DESCRIPTION
as
SELECT SHARES.SHARE_DESCRIPTION, SHARES.SHARE_SYMBOL, SECTORS.SECTOR_NAME
FROM SHARES INNER JOIN
SECTORS ON SHARES.SECTOR_ID = SECTORS.SECTOR_ID
ORDER BY @SortValue
but it does not seem to be possible to use variable after order by
is there any way to achieve something with sorting by variable?
I am trying to Execute a Stored Procedure using Call OSQL from a .bat file, and passing a DOS variable to the Stored Procedure.
The DOS variable is established in the DOS command...
set SERVERJOB=JOBNAME
I have tried...
EXEC sp_procedure %SERVERJOB%
With this, I get an error...
sg 170, Level 15, State 1, Server ABCDEFGH, Line 20
Line 20: Incorrect syntax near '%'.
If I put the variable name in quotes on the EXEC statement above, the value used is the variable name, %SERVERJOB% itself rather than the value the variable was set to, which would be JOBNAME in the above example.
Any ideas??? Thanks!
when passing a value to a stored procedure
How can you use that value within a Select Top <@Variable>
?
Below is the basic idea ..
CREATE Procedure SelectTop
(
@Number int
)
AS
SELECT TOP @Number *
FROM TableName
GO
Hello;
I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.
I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
So how do I refer to a procedures name using a variable?
Here's my code;
Function LieuBen()
MyCalendar = CurrTSCalendar
Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
Call PopulateTmpFile("sp_PopTmpCalcLieuBen")
End Function
Function PopulateTmpFile(MySProc As Variant)
Dim sp_PopulateTempOTTable As String
Const DS = "SOS-1"
Const db = "TIMS"
Const DP = "SQLOLEDB"
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command
ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & db & _
";Integrated Security=SSPI;"
' Connect to the data source.
objConn.Open ConnectionString
' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn
objConn.MySProc MyCalendar, objRs
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing
End Function
Hello again folks, I've come crying for some more help :(
I have an sql query that runs fairly well and I'd like to make it a stored procedure.
The only hickup here is that I need to be able to send a field name in the select statement as a variable.
SQL Server does not allow this and I'm at a loss for the best way to handle it.
Thx for any help :)
CREATE PROCEDURE spReturnandScoring (@varKeyField as varchar(100),@varRegionID as varchar(10))
AS
Select
@varKeyField,count(*) 'SurveysSent',Sum(SD.return_status) 'SurveysReturned',avg(alScoring.Score) 'SurveyScore'
From
tblSurveyData SD
left join (Select Return_Key,cast(sum(response) as numeric)/cast(count(*) as numeric) as 'Score' from tblResponses RE group by return_key) alScoring on SD.objid = alScoring.Return_Key
Where
Region_ID=@varRegionID
Group By
@varKeyField
Order By
@varKeyField
GO
In the code below, the statement 'Print @Sites' prints nothing, eventhough the cursor contains 4 records, and 'Print @Site' prints theappropriate values. Can anyone see my mistake? I am attempting toprint a delimited string of the values referred to by @Sites.Thanks.Dan FishermanDECLARE SiteCursor CURSORGLOBALSCROLLSTATICFOR SELECT OfficeName FROM ClientOffices WHERE ClientID=12 ORDER BYOfficeNameOPEN SiteCursorDECLARE @Sites varchar(1000)DECLARE @Site varchar(100)FETCH NEXT FROM SiteCursor INTO @SiteWHILE @@FETCH_STATUS=0BEGINprint @SiteSET @Sites = @Sites + ', ' + @SiteFETCH NEXT FROM SiteCursor INTO @SiteENDPRINT @SitesCLOSE SiteCursorDEALLOCATE SiteCursorGO
View 4 Replies View RelatedCan someone post a working stored procedure with output variable thatworks on northwind db or explain to me what I am missing. I've triedever tying but it always returns @outvariable parameter not supplied.I've read everything but must be missing something. Here is an sampleSP that I can't get to work._____________________________________CREATE PROCEDURE SampleProcedure@out smallint OUTPUTASSelect @out = count(*) from titlesGO_____________________________________This errors and returns-Server: Msg 201, Level 16, State 4, Procedure SampleProcedure, Line 0Procedure 'SampleProcedure' expects parameter '@out', which was notsupplied.What am I missing with this?Frustrated,Jeff
View 2 Replies View RelatedHello Forum !I want to have the tablename "dbo.Enbxxxx" as an additional parameterfor a procedure like this:ALTER Procedure prcSucheUNR(@UNR int)Asset nocount onSELECT ABRUFNR,UNR,STICHTAG,Datum,InhaltINTO #temp FROM dbo.Enb WHERE UNR = @UNRFor some reason:@tablename varchar(11),and: INTO #temp FROM @tablename WHERE .... does not work. :-(I get the following syntax Error:Zeile 33: Falsche Syntax in der Nähe von '@tablename'.I'am sure it is possible but i don't know how.Greetings
View 3 Replies View RelatedI need to use a variable for a table name, pass it to a stored procedure that extracts data, and then store the result of the sp in another variable.
For example, earlier in the program I construct the table name using this statement:
@tablename = 'OC_TEMPLATE_' + @FORM. The resulting table name may by something like: OC_TEMPLATE_101
Now I need to use this table name in a statement like this:
SELECT ITEM FROM @TABLENAME WHERE DEX = @REC
If I use code like this, I can get a result, but I can't store it in a variable:
EXECUTE ('SELECT ITEM FROM ' + @TABLENAME + ' WHERE DEX = ' + @REC)
Running this give me a value of 309 in the results pane in Management Studio. 309 is the value I want, but I want to now store that in a variable called @ITEM.
I'm trying to run this as a stored procedure where I pass @tablename and @rec into the proc and I want to return @item as an output parameter with the value of 309 (in this case) in @item. I can't figure out how to configure the proc to give me the output parameter.
Any ideas?
T
hi can anyone please help me
i have a stored procedure ,can i put the value of this into a variable......????
I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ...
example if my stored procedure is
[dbo].[GetPortfolioName](@NT_Account varchar(100) )
and ans of this are
NL
UK
IN
GN
JK
then how can i put this into a variable like
Declare @PortfolioName as varchar(250)
set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) )
.......
and my ans is
@PortfolioName = 'NL','UK','IN','GN','JK'
I am working on an mailing list program and I am trying to create a preview option for the sender (to preview their message prior to sending)
I am new to .net and sql but I thought it might be best to do this with table variable inside a stored procedure.. I just do not know how to insert the data into the table variable and select that data at the same time, to return it back to a preview.aspx page.
Please forgive my ignorance..
here is my stored procedure..
ALTER PROCEDURE [dbo].[proc_MessagePreview]
@Subject varchar(150),
@EmailMessage varchar(max)
AS
BEGIN
DECLARE @MessagePreview Table(Subject varchar(150), EmailMessage varchar(max))
INSERT INTO @MessagePreview (Subject, EmailMessage)
VALUES (@Subject, @EmailMessage)
SELECT Subject, EmailMessage FROM @MessagePreview
END
any suggestion or help to point me in the right direction is appreciated, thanks!
Hi all,
I wonder if there is anybody that can help with this one.
As you know, you can pass variables into a stored procedure and then use these variables for table names, columns etc.
Now, you also know that you can access another database on the same server just by typing the name of it into your query.
The hard part:
Instead of hardcoding the database name into the stored procedure i want to use a variable and then pass this to reference the database name, in the same way you would reference anything else with a variable. I need to do this as i have to search for various results across multiple databases.
We currently use SQL server 2000 standard, though are looking into SQL 2005 - especially is this problem is easy to resolve in the latter.
Look forward to your help
Regards
Darren
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 ,
I've got stored procedure:
ALTER PROCEDURE [dbo].[dropmyValue](@dropVal Char OUTPUT)ASEXECUTE('ALTER TABLE [dbo].[tbl1] DROP CONSTRAINT ' + @dropVal) That gets it's value from a GridView.SelectedValue:
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) If GridView1.SelectedValue.ToString <> "" Then Dim cs As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString Using con As New SqlConnection(cs) con.Open() Dim cmd As New SqlCommand cmd.Connection = con cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "dropmyValue" cmd.Parameters.Add("dropVal", SqlDbType.Char) cmd.Parameters("dropVal").Direction = ParameterDirection.InputOutput cmd.Parameters("dropVal").Value = "DF_" + GridView1.SelectedValue Label2.Text = cmd.Parameters("dropVal").Value cmd.ExecuteNonQuery() End Using GridView1.DataBind() End If End Sub
Label2.text shows that @dropVal is "DF_xxxxxxxx" and is the name of the Constaint to be dropped (when I comment out "cmd.ExecuteNonQuery" and run it), but the error I get is that
" 'D' is not a Constraint ". I don't know if this is a sqldbtype problem, but I've tried different ones and evidently only the first character "D" is getting read, or passed to the stored procedure.
Any help would be appreciated.
Steve
Hello,
I want to execute a sproc where the query statement goes something along these lines:
SELECT * FROM myTable WHERE @aVarCharVariable
@aVarCharVariable contains column names and their possible values
How do I achieve this?
Cheers!
/Eskil
Hello :) I need to do something like this:
CREATE PROCEDURE SelectCostumers @name varchar(100)
Declare @SQL = "SELECT Id, Name FROM Costumers"
AS
IF (@name IS NULL)
@SQL
ELSE
@SQL += "WHERE Name LIKE @name"
See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.
Thank you
I have the following lines embedded in a stored procedure
---
----
----
select @querystr = "select @lkinpos = pos_lkin_pos from "+@database+" where pos_clnt_id = "+str(@clntid)+" and pos_isin ="+"'"+ @isinno+"'"
print @querystr
exec(@querystr)
---
---
---
When i execute the above stored procedure, it returns an error as follows:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@lkinpos'.
When i see the print @querystr statement, it returns the query str as follows:
select @lkinpos = pos_lkin_pos from nsdldpm..pos_mstr where pos_clnt_id = 10000045 and pos_isin ='ine227a01011'
This when executed independently, gives me the required output.
Can anybody solve this for me?
Thanks in advance
I have a web page which passes back parameters to a stored procedure.
From the web page the user selects different clauses for the 'where' criteria.
Based upon the number of clause items in the parameters sent back, a select statement is built and executed.
In the stored procedure I have many if statements to chose the correct sql statement.
As the no of clauses in the where statement can vary, it can become messy script.
Has anyone dealt with this scenario. What is the best strategy ?
A simple illustation of this is as follows
A statement with two clauses :-
Select * from Sales where
user = 'John' and country = 'England'
A statement with three clauses :-
Select * from Sales where
user = 'John' and country = 'England' and County = 'Staffordshire'
The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back
Ok, so I have this stored procedure that Inserts a record and I need to return the ID inserted to pass to another procedure. I ended up having to use sp_executesql because I need to dynamically add the schema to the table. The issue that I am having is that the variable that I try to pass to the other procedure always ends up null, but it shows the correct output. Just to clarify on this, i am an Applications developer and not a DBA so I may be going about this all wrong. Any help on this would be greatly appreciated.
Here is the SP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_AddAnalyte]
@Analyt NVarChar(100),
@LName NVarChar(100),
@AtomicNum NVarChar(100),
@AtomicMass NVarChar(100),
@Weight NVarChar(100),
@HalfLife NVarChar(100),
@Units NVarChar(100),
@HF_Secs NVarChar(100),
@Gas NVarChar(100),
@Nat NVarChar(100)
AS
DECLARE @ID INT,@i INT
DECLARE @schema VarChar(50)
DECLARE @sql NVARCHAR(512)
BEGIN
SET @Schema = (SELECT schema_name())
SET @sql = N'INSERT INTO [' + @schema + '].[Analytes] ([Analyte],[LongName],[AnalyteType]) VALUES (''' + @Analyt + ''',''' + @LName + ''',1)';
EXEC sp_executesql @query = @sql;
SET @sql = N'SELECT MAX(AnalytePK) FROM [' + @schema + '].[Analytes]';
EXEC sp_executesql
@query = @sql,
@params = N'@ID INT OUTPUT',
@i = @ID OUTPUT
EXEC sp_AddParameterValue 6, @i, 'AtomicNum', @AtomicNum, '';
EXEC sp_AddParameterValue 6, @i, 'AtomicMass', @AtomicMass, '';
EXEC sp_AddParameterValue 6, @i, 'Weight', @Weight, '';
EXEC sp_AddParameterValue 6, @i, 'HalfLife', @HalfLife, '';
EXEC sp_AddParameterValue 6, @i, 'Units', @Units, '';
EXEC sp_AddParameterValue 6, @i, 'HF_Secs', @HF_Secs, '';
EXEC sp_AddParameterValue 6, @i, 'Gas', @Gas, '';
EXEC sp_AddParameterValue 6, @i, 'Natural', @Nat, '';
END
Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.
What I have is:
CREATE PROCEDURE busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
if @reporteddate is null
select @reporteddate = CURRENT_TIMESTAMP
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SET DATEFORMAT dmy
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
The error I keep getting is:
Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.
Any ideas appreciated.
(Edit:)
I've also tried it this way:
CREATE PROCEDURE UK_busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @reporteddate=CONVERT(datetime, @reporteddate)
IF @@ERROR <> 0 BEGIN
/* Do some error processing */
PRINT 'Error Occured' END
ELSE
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
Which gives me the same error!
.logic.