set @x1 = (select DRfieldname from tblJournalType where journaltypeid = 2
set @SQLstring = 'select ' + @x1 + ' from tblAssettype where assettypeid = 10'
set @x2 = EXEC (@SQLstring)
The last line above is where I am getting the error. Is this possible to do this?
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Hello all, I'm creating a stored procedure in SQL Server 2000. I'm trying to count how many rows match a certain WHERE clause, and assigning this integer to a variable:
---------------- DECLARE @RowCount int,
SELECT COUNT(*) FROM Customers WHERE FirstName='Joe' ----------------
How can I assign the count that returns from the SELECT statement to the @RowCount?
Hello I have and tested in 2 way using variable and constant. Query 1: This query I am using vaiable "@last" for the date value. this takes to run for 2 minute .
Query 2: Here I am using value '11/05/2002' instead of varialbe and it is running within a second.
Why these much difference to run this same query?. Can anybody give correct reason or solution for this. Thanks, Ravi
query 1: DECLARE @last smalldatetime SET @last ='11/05/2002'
select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num and o.added_date > @last UNION select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num INNER JOIN Leap.dbo.ENTERPRISE_STATE es WITH(NOLOCK) ON (a.enterprise_num = es.ENTERPRISE_NUM AND o.last_updated_date > @last)
Query 2:
select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num and o.added_date > '11/05/2002' UNION select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num INNER JOIN Leap.dbo.ENTERPRISE_STATE es WITH(NOLOCK) ON (a.enterprise_num = es.ENTERPRISE_NUM AND o.last_updated_date > '11/05/2002' )
Hi All, I am looking to do the following: 1) Upload a text file via a website to a server with sql server 2000. 2) Fire a DTS package on that file so it appends the contents to a table in the database. 3) Choose a variable via a dropdown list on the website 4) Run a different DTS Package that uses an update sql statement that uses that Variable. Any help or suggestions would be greatly appreciated. Thanls, Doug
Hi guys, I have the following store procedure:PROCEDURE dbo.AddSearchColumn (@A1, @A2. @A3, @A4, @A5) ASDeclare @cElements cursor,@DocNum varchar(100)BEGIN Set @cElements = cursor for select FirstNaname rom dbo.DocTable1open @cElements fetch NEXT from @cElements into @DocNumwhile (@@FETCH_STATUS = 0) begin if (@DocNum==@A1 //Here is what I need to do: I want use @A1 at the first loop step, @A2 at the second, and so on....... // @A1, @A2 are the parameters in inputendENDclose @cElementsDEALLOCATE @cElements I hope my explanation is clear.Please, give some hints.Thanks for your time,Christian Malatesti
Ok, I have a table with several column all labeled, column1, column2, etc. I need to loop through them so I have this statement which will loop through and get the first value, then go to colun2, etc
declare @score int declare @stm nchar(500) set @score=0 While @score<=50
Begin Set @score=@score+1 set @stm='select column' + rtrim(convert(char(2),@score))+' from tablename' exec sp_executesql @stm if @score>50 Break else COntinue end
My question is, how can I stuff this value from the dynamic sql into a variable such as:
I have a trigger which retrieves database names from a table. I need to use this retrieved database name in another sql insert statement as a variable e.g. set @mydbname = Select .... from.. (to get the database name) then.. insert into @mydbname.dbo.emplTable
At the moment it reads @mydbname as the string "@mydbname" not the value the variable holds
I need the database name as a variable because i have to write to the correct database (there are 15) Any help appreciated
how to use a Dynamic variable on a function, to explan my self more here is a sample, we use this on SP but the function not allow executing.
DECLARE @SQL nvarchar(1000);set @sql=''
DECLARE @RESULT nVARCHAR(1000);SET @RESULT='' DECLARE @mpq int;SET @mpq=0 DECLARE @FILENAME VARCHAR(40);SET @FILENAME='parm' SELECT @RESULT =SCHEMA_NAME((SELECT SCHEMA_ID FROM SYS.TABLES WHERE NAME=@FILENAME))+'.'
SET @SQL=N'SELECT @mpq = CASE WHEN MPQ=1 THEN 10 WHEN MPQ=2 THEN 100 WHEN MPQ=3 THEN 1000 END FROM '+@RESULT+'PARM' EXEC SP_EXECUTESQL @SQL,N'@mpq INT OUTPUT',@mpq OUTPUT
I know this has been dealt with a lot, but I would still reallyappreciate help. Thanks.I am trying to transform this tableYY--ID-Code-R1-R2-R3-R4...R402004-1-101--1--2-3-42004-2-101--2--3-4-2....2005-99-103-4-3-2-1Into a table where the new columns are the count for 4-3-2-1 for everydistinct code in the first table based on year. I will get the yearfrom the user-end(Access). I will then create my report based on theinfo in the new table. Here's what I've tried so far (only for 1stcolumn):CREATE PROCEDURE comptabilisationDYN@colonne varchar(3) '*receives R1, then R2, loop is in vba access*ASDECLARE @SQLStatement varchar(8000)DECLARE @TotalNum4 intDECLARE @TotalNum3 intDECLARE @TotalNum2 intDECLARE @TotalNum1 intSELECT SQLStatement = 'SELECT COUNT(*) FROMdbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4OUTPUT INSERT INTO Comptabilisation(Total4) VALUES (@TotalNum4)GO
Here's the WRONG way to do what I want. I need a way to populate a variable from the output of a dynamic query.
declare @TableName sysname set @TableName = 'Customers'
delcare @Output bigint
declare @SQL varchar(max)
set @SQL = 'select top 1 RowID from ' + @TableName
select @Output =
EXEC (@SQL)
create function udf_GetDatabaseFingerPrint(
@DBID bigint ) begin returns bigint as declare @dbname sysname
, @iBig bigint , @tSQL varchar(2000) select @dbName = Name from Master.Dbo.Sysdatabases where DBID = @DBID set @tSQL = 'select sum(Rows) from ' + @dbName + '.dbo.sysindexes' set @iBig = exec(@tSQL) return @iBig end
I've a roblem with table variables in dynamic sql context.
If i use it with normal sql there is of course no problem. Simplified it looks like this:
-- Standard SQL ---------------------------------------------------------------------------------------------------------------- DECLARE @TestTable as TABLE(MyBigInt bigint NOT NULL, Myint int NOT NULL);
INSERT @TestTable SELECT 4711,10
SELECT* FROM @TestTable -----------------------------------------------------------------------------------------------------------------------------------------
If i do the same thing in dynamic sql, i get errors.
SELECT* FROM @DynamicTestTable; -----------------------------------------------------------------------------------------------------------------------------------------
What is wrong with tis code? Or are table variables not supported inside dynamic SQL?
I have a table with 52 columns named 'Week1', 'Week2' etc. with values 1, 0 etc. I want to check values in each column. I have following lines in my procedure.
Declare @l_str varchar(50), @l_count int
Select @l_count = 1 Select @l_str = 'Week' + Convert(varchar, @l_count) Now how do I compare the value stored in the @l_str which should be wither 0 or 1 and not 'Week1'?
Is there any better method to compare read these 52 table variables?
I am trying to assign @sql variable to @total, where @sql is a sqlstatement stored on the database, however what I am getting is itsstring value and not its calcuation. Could anybody help?DECLARE my_cursor CURSOR FORSELECT sqlstatement from Sn_SalesReportdeclare @sql varchar(255), @total varchar(20)OPEN my_cursorFETCH NEXT FROM my_cursor INTO @sql-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGINSET @total = (@sql)print @total-- This is executed as long as the previous fetch succeeds.FETCH NEXT FROM my_cursorINTO @sqlENDCLOSE my_cursorDEALLOCATE my_cursorI tried this SET @total = EXEC (@sql), but no successRod
Hi allIn the SP below im (trying to) do some dynamic sql. As you can see the tableto use is set as a variable and the 'exec' method used to run thesqlstatements.My problem is that the 'if exists' method is not doing what i was hoping itcould do.The @presql command returns somewhere between 0 or 50 rows (give and take) -i just want the 'if exists' part to determine if the select statementreturns something or not since i then will have to update a current row - orinsert a new one.Even if there is no rows returned, the 'if exists' command will return true:-/Any suggestions to a different way of approach...?Thanks in advance :-)######## Stored procedure start ########[various @ variables]....declare @presql varchar(200)select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as varchar(6))+''IF exists((@presql))BEGINdeclare @UpdateSQL varchar(400)set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate asvarchar(12))+''''exec(@UpdateSQL)ENDELSEBEGINdeclare @InsertSQL varchar(2000)select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '(btDate,btTime)VALUES('''+ cast(@FileDate as varchar(12)) + ''','+ cast(@ImportTime as varchar(6)) + ')'EXEC(@InsertSQL)END######## Stored procedure end ########
I am trying to use this statement in a variable, including another variable:
"SELECT * FROM my_table WHERE CAST([timestamp] AS INT) > " + @[User::LastTimestamp]
But the variable value insists on giving me this error:
The expression for variable "VariableName" failed evaluation. There was an error in the expression.
I cast the columntype "timestamp" to int, and the variable "LastTimestamp is stored as int32, and has a default value of 0. I simply can't grasp what it is I am missing.
Is it because the expression is part string and part integer? If so, how is that avoided?
Error Output: -------------------------------------------------------------------------- my new db test Server: Msg 911, Level 16, State 1, Line 1 Could not locate entry in sysdatabases for database 'my'. No entry found with that name. Make sure that the name is entered correctly. -----------------------------------------------------------------------
Any idea how to get the entire database name to be used with spaces in the database name. It prints the @DatabaseName just fine, but in the dynamic sql, it truncates after the first space.
I already tried N'my new db test' in the set statement. That didn't work. I tried using NVARCHAR when declaring the variable.
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
Not wishing to derail the other recent thread on loading a local variable, I've posted this query (hee,hee,hee...I kill me) on a separate thread...though I think I am trying to do something similar...that is to build a dynamic select statement, but return a count of the rows it finds/doesn't find to a local variable...using the (amazingly timely) responses above, I tried this:
Note that the local variables @TargetDate and @TLevel are necessary because they are being passed into the procedure as variables....
DECLARE @SQLCmd varchar(256) DECLARE @TargetDate smalldatetime DECLARE @TLevel int DECLARE @n int SET @TargetDate = '2004-05-24' SET @TLevel = 1
SET @SQLCmd = 'SELECT @n = count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' + CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' + CONVERT(varchar(3), @TLevel) + '))' exec (@SQLCmd) if @n > 0 print 'yep' else print 'nope'
and, it's TRYING to work...but apparently the local variable @n is not recognized in the execution of the dynamic statement, as this is the output: Server: Msg 137, Level 15, State 1, Line 1 Must declare the variable '@n'. nope
HiI'm grateful for any light you can shed on this!!I've to admit, it's an unusual design but I've multiple contact tables namede.g. i2b_ash_contact or i2b_ted_contact.'i2b_' and '_contact' are static but the middle part is dynamic.Storing all contacts in one table with an identifier of e.g. 'ash' or 'ted'for each record is not possible.Returning the value from the dynamic Query is no problem but I don't knowhow to assign it to a variable.When I try doing this it either runs into problems with evaluating thevariables or doesn't retuen anything at all e.g. if I say at the end 'Print@AddressID'. The variable remains empty.How can I do something like:DECLARE@AddressID int,@ProgClient (varchar(10),@Table varchar(10)Note: @Prog is a string e.g. 'ash' or 'ted'SET @Table = 'i2b_ + @ProgClient + '_contactSET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)
Is there a way to write such a query where we can declare the variable dynamically ? Currently I am using the query as shown below :
declare @pYear_Internal as NVarchar(100) set @pYear_Internal = [D FISCALPERIOD].[FP CODE].[FP CODE] WITH MEMBER MEASURES.[REVENUE] AS [Measures].[TOTAL REVENUE] SET LAST5YEARS AS STRTOMEMBER(@pYear_Internal).Lag(4) : STRTOMEMBER(@pYear_Internal)
[code]....
While executing the above query, getting the error - Query (1, 9) Parser: The syntax for '@pYear_Internal' is incorrect. Â It looks like it doesn't recognize DECLARE keyword as it does with SQL queries. Â I just want a query that runs directly against the server.Â
I am trying to use the idea as mentioned by Jamie at: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx which is to build dynamic SQL using a variable evaluated as an expression.
Set Expression="SELECT * FROM MyTable WHERE MyColumn = " + @[VariableContainingFilterValue]
Everything works fine. The entire package works. My next step is to log the variable so that I know, after package execution, exactly what SQL statement the package is executing. I tried to do it by a couple of ways in a Script task:
which provides the format as myFileName_yyyymmddhhmmss.xls.
This value is then assigned to a variable, user::myFilenameDateTime.
This variable is referred to in various steps which need the full pathname or filename.
I found, though, that in subsequent steps, the value for user::myFilenameDateTime is re-calculated whenever the variable is invoked.
So in one Task i created the physical output XLS file and named it "correctly", eg, myFileName_20070428090204.xls; in the next Task, i call a Stored Procedure in SQL Server 2005 to email the file with a corresponding message (that pulls in more data from the database).
The single Parameter to the Stored Procedure (that does the emailing) is the supposed/expected full Pathname of the outgoing file just produced but the parameter no longer represents the original filename -- and has changed slightly (a few seconds have been added) and the filename is now, myFileName_20070428090210.xls; consequently, my Email Distribution program cannot find the file with that specific name, although File = myFilename_20070428090204.xls does certainly exist.
so it appears that these variables are calculated realtime whenever encountered.
1) Am I misunderstanding something or misusing the variable assignment?
2) How can i keep << myFileName_yyyymmddhhmmss.xls >>, "static" throughout the duration of the overal SSIS Process.
3) I would think that even if I assign the derived myFileName value initially to ANOTHER "static" variable, this won't achieve anything because the new variable will be re-calculated again, as well, when it is invoked.
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
Is there a way to assign a dynamic query result to a local variable?
declre @sqlString nvarchar(4000), @minEventDate datetime, @databaseName varchar(25) selct @databaseName = 'customer_12345' (actually, a cursor loop will assign the database name dynamically, here just to simplify the situation)
Though the select min(eventDate) from customer_12345.dbo.tblABC returns a date, ex. '02/01/2004 12:35 pm', however, the printed @minEventDate is always with Null value. It mean, the value was never correctly assigned to the local variable.
As an alternate way, I am using temp table to insert it with the query result and then assign to the local variable. Since I have many local variables to try to get the min, max, count for around 10 fields, perfer a way to direct assign to the local variable instead of 10 temp tables.
