I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example
eg
DECLARE @column
DECLARE @value
SET @column = 'col1'
SET @value = 'bloggs'
Select * FROM table1 WHERE @column = @value
It keeps returning no results even though i've tried
Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results
I realise its the column which is not being selected, but there must be a way by using a variable?
Hello, I'm quite new to T-SQL, but since I'm trying to create a statistics page on database contents (Counting savesets in Enterprise Vault saveset Databases) I prefer to do the coding in the databases. I create temp tables for the distinct partitions in the saveset table. Then I pass 2 variables to the EXEC function, but it seems unable to pass the ['+@idpartition+']-variable as a value: Declare @EVBase varchar(20)Declare @IdPartition INTSet @EVBase=(SELECT EVMbxName from Servers) Set @IdPartition=(SELECT TOP 1 Dist_Partitions FROM TEMP_EV1)EXEC('SELECT COUNT (IdPartition)FROM ['+@evbase+']..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] = ['+@idpartition+'] AND StoreIdentifier IS NULL') Server: Msg 207, Level 16, State 3, Line 2Invalid column name '0'. If I change the last line to: WHERE [IdPartition] = 2 AND StoreIdentifier IS NULL')The script runs fine - but I need the value from the table. Any help will be appreciated. Best regards, Tim
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 want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user)) BEGIN DROP PROCEDURE InsertTimeCard END go /********************************************************************************************************* ** PROC NAME : InsertTimeCardHoursWorked ** ** AUTHOR : Demetrius Powers ** ** TODO/ISSUES ** ------------------------------------------------------------------------------------ ** ** ** MODIFICATIONS ** ------------------------------------------------------------------------------------ ** Name Date Comment ** ------------------------------------------------------------------------------------ ** Powers 12/11/2007 -Initial Creation *********************************************************************************************************/ CREATE PROCEDURE InsertTimeCard @DateCreated DateTime, @EmployeeID int, @DateEntered DateTime, @SerializedXML text, @Result int output as declare @NewTimeCardID int select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards -- proc settings SET NOCOUNT ON
-- local variables DECLARE @intDoc int DECLARE @bolOpen bit SET @bolOpen = 0 --Prepare the XML document to be loaded EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML -- check for error IF @@ERROR <> 0 GOTO ErrorHandler --The document was prepared so set the boolean indicator so we know to close it if an error occurs. SET @bolOpen = 1
--Create temp variable to store values inthe XML document DECLARE @tempXMLTimeCardExpense TABLE ( TimeCardExpenseID int not null identity(1,1), TimeCardID int, ExpenseRptID int, ExpenseDate datetime, ProjectID int, ExpenseDescription nvarchar(510), ExpenseAmount money, ExpenseCodeID int, AttachedRct bit, SubmittoExpRep bit ) DECLARE @tempXMLTimeCardWorked TABLE ( TimeCardDetailID int not null identity(1,1), TimeCardID int, DateWorked DateTime, ProjectID int, WorkDescription nvarchar(510), BillableHours float, BillingRate money, WorkCodeID int, Location nvarchar(50) ) -- begin trans BEGIN TRANSACTION insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid) values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0) -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep) SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2) WITH ( ExpenseRptID int 'ExpenseRptID', ExpenseDate datetime 'ExpenseDate', ProjectID int 'ProjectID', ExpenseDescription nvarchar(510) 'ExpenseDescription', ExpenseAmount money 'ExpenseAmount', ExpenseCodeID int 'ExpenseCodeID', AttachedRct bit 'AttachedRct', SubmittoExpRep bit 'SubmittoExpRep') -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
-- remove XML doc from memory EXEC sp_xml_removedocument @intDoc SET @bolOpen = 0
-- commit transaction, and exit COMMIT TRANSACTION set @Result = @NewTimeCardID RETURN 0
-- Error Handler ErrorHandler: -- see if transaction is open IF @@TRANCOUNT > 0 BEGIN -- rollback tran ROLLBACK TRANSACTION END -- set failure values SET @Result = -1 RETURN -1
I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say Select OrderId from Order. But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.
The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.
I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
Does anyone know a select statement that would return the column namesand keys and indexes of a table?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
I have a cursor which populates a variable with the name of each user table within my DB. I'm trying to copy the tables one at a time by using a command like this: SELECT * INTO @NewTable FROM @OrigTable Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'. This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this? Are there any alternative ways of copying the table structure (preferable without the data)?
I am writing an sproc for creating a table, but I don't know how I can use parameter values as object names (for example the name of the table needs to be [@sFile1+'_'+@sLinkName+'_'+@sFile2']. It seems that I could concatenate my whole CREATE TABLE string into a single variable and use EXEC to run it, but I'd prefer to be able to do it in the context of the sProc (I read that EXEC always has the current user's permissions). What is a good technique?
CREATE TABLE [@sFile1+'_'+@sLinkName+'_'+@sFile2] ( [GID_ID]uniqueidentifierROWGUIDCOLNOT NULL, ['GID_'+@sFile1]uniqueidentifierNOT NULL, ['GID_'+@sFile2]uniqueidentifierNOT NULL, CONSTRAINT ['LNK_'+@sFile1+'_'+@sLinkname+'_'+@sFile2] FOREIGN KEY(['GID_'+@sFile2]) REFERENCES [@sFile2](GID_ID) ON UPDATE NO ACTION ON DELETE NO ACTION NOT FOR REPLICATION, CONSTRAINT ['LNK_'+@sFile2+'_'+@sLinkname+'_'+@sFile1] FOREIGN KEY(['GID_'+@sFile1]) REFERENCES [@sFile1](GID_ID) ON UPDATE NO ACTION ON DELETE NO ACTION NOT FOR REPLICATION )
Hi, I am new to using SQL for anything more in depth than querying and reporting.
I am trying to create a series of SQL scripts which will be used across several customer sites so need to be easily customisable. What I want to do is have all of the table names, field names and customisable items handled by variables which will be declared and set at the beginning of the script, making them easy to find and change. The problem I am having at the moment is with creating a new table using variables for table name and field names, can anyone help?
set @a_fieldid1 = 'newFieldid' set @a_tmptbl = 'newTable'
create table @a_tmptbl ( @a_fieldid1 varchar(15), value float, counter INT);
insert into @a_tmptbl values ( "foobar", 21.76, 1);
select * from @a_tmptbl;
The error I am getting is:
quote:Server: Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near '@a_tmptbl'. Server: Msg 137, Level 15, State 1, Line 10 Must declare the variable '@a_tmptbl'. Server: Msg 137, Level 15, State 1, Line 12 Must declare the variable '@a_tmptbl'.
i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:
Create Procedure VerifySubjectNum (@forum_ID VARCHAR(10), @subject_num INT) As If Exists (SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num) Return 1 Else Return 0
when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?
I am a SSIS novice, and need help to do the following:
Every day a Table is generated by the system of format XR+present Year + present month + present day. My task is pretty simple. I am to devise a SSIS package that will import this table on to a different server.
I have defined the variable as user defined variable. I am also able to specify the variable in OLE db source editor -> Data Access Mode : Table name or view name variable. When I am click the 'Columns' feature, I get the following error
TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "xr+ convert(char(8),getdate(), 112)" failed. Check that the object exists in the database. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------
In short can any one tell me a correct way of using a table name as variable in Data Access Mode : Table name or view name variable ?
One other question: can I make use of a dynamic SQL in Execute SQL Task? If yes, how do I write the query
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed. After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename) drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does. I duplicate a table that is going to be modified by using 'select into temptable' I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA' then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02) SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02', FROM TableB where ColumnB = 003860 Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02) Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02', From #temptableabcd Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.
For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".
And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.
So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.
here a quick rundown of what i want to do: database database1
database hold all my good information. database 2 is the copy for breaking. Im too a point that i need to get my 2nd DB to by like the first. I want to truncate all the tables in database2, and then export, or select from insert into. I just need the data, not triggers or anything like that.
So: How can i find the name of my tables (not views) in that database
For each (select table_name from system where database="database") copy database.table_name database1.table_name;
i hope that makes sense. I just need to restore my devel DB back to the original so i can mess it up again, Thanks
Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that.
The problem is that I do not know how to extract the name of a column and put it in to a textbox ?
Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
At my work, we are upgrading a number of MS Access 2000 databases to SQL Server 2000. Many of the columns have names that do not follow the rules for Identifiers. For example there are columns with a numeric names 1,2,3,4,5,6,7,8,9. There are also columns with a hyphen in the name and columns that begin with a number and not an underscore or an alpha character. Plus there are columns with names like first, last, position, etc. (There was also columns with reserved words used as the names. I have changed those column names in the databases that I have already converted) Will leaving these names alone create a problem? The database serves as the backend to ASP pages.
I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated. Randy
Pretty simple question and I see it's been asked a few times with no answers so I thought I'd try again. :) Is there a way to put a variable into an SQL connection string? Like: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BGHelpdeskConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM MyTable WHERE name=" & response.write(MyName) .... etc
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
Hi all, By using below query i can get no of tables having the give column name in a particular database, SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno') but i want to know the table names too? any one please suggest me how to find table names too...... In other words i know the particular column name and right now i want to know the table names in which this column name exists.
Hi, I have a table in which out of 20 columns, there will be data only in a few columns. So, I need to find those column names which have data in them. I was able to do it using the information_schema and using a cursor to loop through all the column names and find out which columns have data in them. But i need to know if there is a more efficient way of doing this without using the cursor. Can somebody please let me know how this can be done without using a cursor? Thank you.
