I am writing a stored procedure in which I have a query that selects the Headings of Columns from another table...
I want to then create a loop that will contain a variable with the value of the column heading and then set the column to a value of NULL...
Is there any way to accomplish this???
I thought about placing these values into a temp table...
Create Table #UpdateRejDoc
(
Abbreviation varchar(20)
)
Insert into #UpdateRejDoc
Select Abbreviation
From tbl_Titles
left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID
Where tbl_TitleRouting.Application_ID = @Application_ID
While Exists (Select Abbreviation from #UpdateRejDoc)
Begin
Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' +
'@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20))
End
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 am attempting to export data on a daily basis via DTS to an XLspreadsheet. I would either like to: a) have a separate worksheet inthe spreadsheet for each export or b) a completely differentspreadsheet for each export. Whenever I attempt to use a variable asthe name of my table in the DTS package, it will not let me.Constructing a string and attempting to execute it also fails,although both of these work in query analyzer. Any advice?Peter
Greetings!I am now doing one type of analysis every month, and wanted to creattable names in a more efficient way.Here is what happens now, everytime I do the analysis, I will create atable called something like customer_20050930, and then update thetable by using several update steps. Then next month I will create atable called customer_20051031. Does anyone know if there is a betterway to do it? like using a macro variable for the month-end date? Noweverytime I have to change the table name in every single update step,which would cause errors if I forget to change one of them. By using amacro, I would only need to change it once.Thanks!
is it possible to use twice declared. Variable names- declared. Variable and after KILL and use the same declared. Variable like
DECLARE
@StartDate datetime
KILL @StartDate datetime (remove from memory) use after with the same name
i have 2 big stored PROCEDURE i need to put one after one and psss only 1 Variable name to the second stored PROCEDURE like this i don't get this error
The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 134, Level 15, State 1, Line 146
The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure. i use like KILL @endDate ?? KILL @StartDate ??
Ok I am faced with working with XML on a regular basis, which is fine.
DECLARE @ViewSN INT IF NOT EXISTS (select null from tblviews where viewcode = 'loadAtTerm') --<workflowEventType>loadAtTerminal</workflowEventType> insert into tblviews (ViewName,Description,OutBoundForm,StoredProcSN,TriggersReply,ViewCode,DispXactLayer,DispXactViewType,DispXfcTag,Comments) select 'QC:WF-LoadAtTerminal','This View Corresponds to the XML for loadAtTerminal in Omnitracs Workflow','0',NULL,'0', 'loadAtTerm','MCOM','MCOM',NULL,NULL
[code]...
What would be really useful is to be able to present any xml file and automatically parse the NODE names into a memory variable table and then the fields of each node in another.
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.
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.
Can anyone help me with a SQL statement that will list all the column names in a table please ?
I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:
Hi is possible to create dynamic column name example Declare @StartDate as dateTime Select @StartDate = '2004-06-05'
select SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)], SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)] from table1
Is there a way that you could get the column names for each table in a database using 1 query? something like: tbl colname t1 catID t1 catName t2 prodID t2 prodDesc t3 cartID ... ...
I know it would be long, but I would just be searching through the saved output for specific names.
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Morning guys, I have a project where Iam suppose to script all columns in every database/table/columns on the network. I figured the best way to do this would be using sql-dmo (Sql Server 2000)
Does anyone have any suggestions on getting started with this project?
Hi All, Heres my problem... If I create a table T1 with a couple of columns; MyPKCol, ColA I then create a view V1 as "Select * From T1" At this stage, if I run "Select * From V1" the result will, as expected, include following columns: MyPKCol,ColA
If I now add another column, ColB, to T1 and then again run "Select * From V1", I still get the result with only 2 columns; MyPKCol,ColA
In order for V1 to return MyPKCol,ColA,ColB I have to drop V1 and then recreate it again.
Can someone explain why this is and how I can clear this type of cache? I've tried the following commands, but it didn't work: DBCC FREEPROCCACHE; DBCC FREEsystemCACHE( 'ALL' ); DBCC DROPCLEANBUFFERS;
There has to be an easier way than having to recreate SPs, Function and Views just because you make a change to a table.
I hope I made sence Thank you in advance for any help.
I have a SQL table with 5000 rows in it. Some of the other SQL columns have different values but if I wanted to swap say 500 of the rows would it be something like
UPDATE Coupins SET Name='test1,test2,test3' WHERE Name='test4,test5,test6';
I need basically to swap the Name value of these without affecting any other values.
I'm trying to perform an insert on a table from another table, but the fieldnames are different. The data is similar, but there are less fields as well. Does anyone know how to accomplish this? All the material I've read on the Internet so far pertaining to Insert statements, has all the column names matching up.
