|
After creating a similar table structure and populating it with some data, your query runs fine on my laptop (Win XP SP2, SQL Server 2005 Developer Edition). You did not specify which version of SQL Server you are running. The latest service pack may be a good cure for your problem.
You may want to get the actual error that occurred by reading the SQL server error log. Most of the time it is possible to get the error message in the stack dump just below the Access Violation message.
Nathan H. Omukwenyi
|
|
|
|
|
hello,
i'm creating a stored procedure that will give me the structure of whichever table name i provide it with.
the stored procedure i'm using is
CREATE PROCEDURE Describe_table (@table_name varchar) AS <br />
SET NOCOUNT OFF<br />
Select * from table_name<br />
GO
the code i'm using to run this procedure is
cmd=new SqlCommand("Describe_table",conn);<br />
cmd.CommandType=CommandType.StoredProcedure;<br />
cmd.Parameters.Add(new SqlParameter("@table_name",SqlDbType.NVarChar,0, ParameterDirection.Input, false, 0,0,"table_name", DataRowVersion.Default, comboBox1.Text));<br />
cmd.UpdatedRowSource=UpdateRowSource.OutputParameters;<br />
cmd.ExecuteNonQuery();
i get the following messsage at runtime
invalid object name "table_name"
i understand that the stored procedure treats the 'table_name' in the SELECT clause as a constant name.
how can i make it a variable
i've tried modifying the procedure
CREATE PROCEDURE Describe_table (@table_name varchar) AS <br />
SET NOCOUNT OFF<br />
Select * from @table_name<br />
GO
but this gives me the error of invalid syntax near 'table_name'
plz help me quick
thanks
|
|
|
|
|
the truly dirty fix for this is:
@SQLString = 'SELECT * FROM '+@Tablename
EXEC @SQLString
this is non compiled and wide open to SQL Injection but it may work depending on your circumstances
Not sure what a good fix is, i'll have a little think
Russ
|
|
|
|
|
well Russ
i executed the dirty fix. it didn't give me any error or exception but it didn't seem to give any results either.
well the select query gives the records in the table if i'm not wrong. i don't have any records in the tables yet. all i'm trying to do is display the entire structure or schema of a table. i'm working in C#.NEt with Sql Server. can u please guide me about it
loads of thanks
Saira
|
|
|
|
|
Russ gave you an answer based on your example. If you want to display the structure of the table, try using "Describe" instead of "Select *"
Chandra
|
|
|
|
|
hi
i've tried it with 'Describe' too but SQl Server doesn't support this query so we are searching for its substitute.
Saira
|
|
|
|
|
|
ChandraRam wrote: Russ gave you an answer based on your example. If you want to display the structure of the table, try using "Describe" instead of "Select *"
Sql sever don't know the Describe
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
arachnoid wrote: @SQLString = 'SELECT * FROM '+@Tablename
EXEC @SQLString
EXEC executes the stored procedure not the variable.
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
uroojkhan wrote: EXEC executes the stored procedure not the variable.
Exec can be used to execute dynamic SQL as well as stored procedures.
|
|
|
|
|
Colin Angus Mackay wrote: Exec can be used to execute dynamic SQL as well as stored procedures.
Thanks alot Colin Angus Mackay for the "Dynamic SQL"
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Saira Tanwir wrote: i understand that the stored procedure treats the 'table_name' in the SELECT clause as a constant name.
how can i make it a variable
you cann't. Infact with FROM clause variables can not be used, usually it identifies the table or subquery.
Saira Tanwir wrote: i'm creating a stored procedure that will give me the structure of whichever table name i provide it with.
CREATE PROCEDURE Describe_table (@table_name varchar) AS
SET NOCOUNT OFF
Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table_name
GO
Sql server provides a view, named "INFORMATION_SCHEMA.COLUMNS", which contains information about each column of the current databse. It is based on various "system tables". System tables are special tables, used by sql server to store its components/objects information. Infact there are various INFORMATION_SCHEMA views, which provide very useful information about database components. Hope this will help in solving your problem.
Regards
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
hi uroojkhan
u were a big help. it just solved my problem.
thanks
and regards
Saira
|
|
|
|
|
check this[^]
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Hi Every1
I have problem in Trigger.I am new for that.Pls help me.
I have one table as Try.When I insert values in Try,at a time I want to fire Trigger for inserting and insert that value also in another table as Try1.
I got help but not understand why create temporary table in Trigger.Can we use direct table name in Trigger?
Pls help me if u have solution for triggers.Becoz I am completely new in this concept.
Thanks
monika
|
|
|
|
|
hi,
Can u tell exactly what problem ur facing?
giving code snippet ll be better.
Nitin...
|
|
|
|
|
You don't have to create a temporary table in the trigger to insert values into another table. All the inserted values are available to you in a SQL Server defined table called 'inserted'. All you have to do is to read the values from this table and insert them into Try1.
For example, for single row insert operations:
CREATE TRIGGER Try_Insert
ON Try
FOR INSERT
AS
INSERT INTO Try1 (columnsList)
SELECT ColumnsList FROM Inserted
Nathan H. Omukwenyi
|
|
|
|
|
Hi
I got yr solution but i have one confusion what is exactly inserted table it's already in sql server.how to define inserted table?I am not getting concept of inserted table.Pls help me and define all the details.
Thanks for yr nice help.
Thanks
monika
|
|
|
|
|
SQL SERVER TRIGGERS[^]
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
The inserted table is a SQL Server in-memory structure only available within triggers fired during INSERT and UPDATE operations. The table has the same structure (columns) as the table that the trigger is defined on and contains as many rows as have been inserted or updated. The inserted table contains the values that are being inserted into the base table.
For example, you have Table1 with 2 columns; [col1] and [col2] and you have defined an insert trigger on Table1. When you try to insert a row into Table1 containing the values "A" for col1 and "B" for col2, the trigger fires. Within the context of the trigger, SQL Server provides you with a temporary in-memory structure called 'inserted' which contains 2 columns; col1 and col2 and this table has 1 row containing the values "A" and "B" respectively. You can query this table just like any other table using available T-SQL constructs e.g SELECT , JOIN , UNION etc.
So to put the new data into another table, just query the 'inserted' table and insert the results wherever you want.
Just for the sake of completion, SQL Server also offers you another table called 'deleted' available in triggers fired for DELETE and UPDATE operations. It can be used in the same manner as the 'inserted' table only that it contains the values that are being deleted/modified from the base table.
Nathan H. Omukwenyi
|
|
|
|
|
iam getting error when setting the value to the variable.
|
|
|
|
|
can you post what you have written along with the error message ?
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
ok i got the solution.i forgot to keep it in single quotes.
|
|
|
|
|
Ravi Shankar43 wrote: iam getting error when setting the value to the variable
Hi,
Suppose you have a varchar type variable, named @abc . With T-SQL you can assign value to @abc in two ways.
1- With the SET keyword
SET @abc='Its varchar typed'
2- With the SELECT keyword
SELECT @abc='Its new value'
However, with the SELECT keyword you can assingn values to multiple variables according to the following syntax.
SELECT varialbe1 = value[, variable2 = value, ...]
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
I want to display 10th record to 20th record from a table. I have no any field to store the recordno. I am using sql Server2000.
Bharat Bhusanam
|
|
|
|