|
Im a bit new at stored procedures. How do i create a table (with a stored procedure) with the following
PageID - uniqueidentifier (not null)
pageTitle - text (not null)
DateCreated - DateTime (Null allowed)
count - integer (null allowed)
Thanks
|
|
|
|
|
Although it is not something I would recommnend, the stored procedure would look like this:
CREATE PROCEDURE CreateTable
AS
CREATE TABLE myTable
(
PageID uniqueidentifier NOT NULL,
pageTitle varchar(max) NOT NULL,
DateCreated dateTime NULL,
Count int NULL
)
GO
Of course, you can't call this procedure more than once unless you change the table to a temporary one (by using the # or ## prefixes on the table name depending on the scope) or by dropping the table first. Your application logic will determine what you actually need to do.
Nathan H. Omukwenyi
|
|
|
|
|
its simply you can use this ...
CREATE TABLE tbl_tableName
(
PageID uniqueidentifier NOT NULL,
pageTitle varchar(max) NOT NULL,
DateCreated dateTime NULL,
Count int NULL
)
Cheer
Pavan Pareta
|
|
|
|
|
is there a way to have a server automatically
send out an email on a certain date. For example, if it was someones birthday,
an email saying happy birthday would automatically be sent out.
Using : ASP.Net
Dileep.M
|
|
|
|
|
In order for this type of functionality, you will need a constant running program or at least one that executes automatically once a day. This can be done inside of SQL server using a SQL Job. This can also be implemented as a service on any system.
ASP.Net/IIS is not the technology I would use to implement this functionality. I am sure there is some way to get it done but, it would be on the bottom of my list of the tools to use.
|
|
|
|
|
I am a new learner to the Dao database operation through MFC access.For now I am trying to write a program, saving two values in two edit boxes to a column of MS Access 2000 table. There are not many samples available. Could any one write little sample codes or guide me to a tutorial. The msdn explanation seems a bit difficult and abstract for me.
|
|
|
|
|
Don't cross post and don't double post the same question.
led mike
|
|
|
|
|
alter database datafile (select file_name from sys.dba_data_files where tablespace_name='my_undo_tbsp')
AUTOEXTEND ON next 1024k;
the above query fails in oracle. Please guid me how to accomplish above.
Thanks in advance
|
|
|
|
|
There is an error when i run my script.... wat it mean??? will be becasue my command too complicated of computer hardware problem??
actually it work at first b4 i add the COST column after add it then error comes out....Those SQL i underline which is the code i added.
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
SELECT Query1.bizdate, TSuper,TSoft, THard, (TSoft+ THard+ TSuper) as TOTAL ,<br />
((TSoft+ THard+ TSuper)-(TsCost+ThCost+TsuCost))as GP <br />
FROM <br />
(SELECT bizdate, SUM(totalsales+returnamt) AS TSoft ,sum(totalcost+returncost)as TsCost FROM tot_item WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17) <br />
GROUP BY bizdate) AS Query1<br />
<br />
LEFT JOIN (SELECT bizdate, SUM(totalsales+returnamt) AS THard ,sum(totalcost+returncost)as ThCost FROM tot_item WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29) <br />
GROUP BY bizdate) AS Query2 ON Query1.bizdate = Query2.bizdate<br />
LEFT JOIN (SELECT bizdate, sum(totalsales+returnamt) AS TSuper ,sum(totalcost+returncost)as TsuCost FROM tot_item WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90) <br />
GROUP BY bizdate) AS Query3 ON Query1.bizdate = Query3.bizdate<br />
<br />
where query1.bizdate>= '2006-10-01' and query1.bizdate<= '2006-10-15'<br />
order by query1.bizdate
|
|
|
|
|
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![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
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...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
