|
here it is:
<br />
declare @season as varchar(4)<br />
set @season = '2004'<br />
declare @sql1 as varchar(255)<br />
declare @sql2 as varchar(255)<br />
declare @sql3 as varchar(255)<br />
select @sql1 = 'create table driver_'+@season+'(id int, f_name varchar(50),l_name varchar(50))'<br />
exec (@sql1)<br />
<br />
select @sql2 = 'declare @f_name varchar(50)<br />
declare @l_name varchar(50) declare @id as int set @id = 123 <br />
set @f_name = ''lewis'' <br />
set @l_name = ''hamilton'' insert into driver_'+@season+' values(@id,@f_name,@l_name)'<br />
exec (@sql2)<br />
select @sql3 = 'select * from driver_'+@season
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
thanks i will check it now![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
I was thinking on doing that has in the before code, didn´t tryed yet
Don´t you know other way?
No need to anser
nelsonpaixao@yahoo.com.br
|
|
|
|
|
I have tested and it has worked for me. 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Listen Blue_Boy thanks for the post it woorks fine.
It seems i didn´t need that yet, because i thought on creating new tables and drop them after "my search", so i needed to create tables with diferents names, (because they triggered the same sp code)
But i ended on doing a diferent loop: create 1 table, search it and drop it, created 1 table,search it and drop it etc...
(so i didn´t need diferent table names)
ALL OK, but if i needed to do your way?
I couldn´t manage to do your dynamic sql when i have to enter a sp parameter, like @season.
declare @my_sql varchar(max)
select @my_sql = 'bellow code'
use my_Database
go
create procedure season_standings
@season as char(4)
as
begin
...
end
go
thanks no need to anser 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi guys,
We have a column "LastModifiedDate" columns in almost all our tables. We are currently using procedures to update lastupdateddate column. but every now and then developers forget to add this to their query.
Is their a way to make this automatic...should we use triggers for this??...
But again I m not sure if we should add triggers to all tables...
what's the best practice for this?
Any suggestions??
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
Use triggers. Much better solution than to use separate procedure.
|
|
|
|
|
we are not using separate procs but every procs that updates any table...it updates this column also...
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
So is every update and insert done via stored procedure and the developers are writing the procedures?
If that is true I would still go to triggers. In this way the database takes care of critical information (I understood that the date must be modified every time) and the developers don't have to remember to add this column to every DML operation. Also DML executed directly against the table is setting the date correctly if triggers are used.
Just wondering how you use the LastModifiedDate. If it simply shows the date when the record is last modified, it's ok. However if you use it for optimistic locking, the resolution isn't adequate.
Mika
|
|
|
|
|
Thanks Mika,
We are using it to update date for any change in the table...i guess i will be using triggers only..
thanks again for help
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
You're welcome
|
|
|
|
|
Hi,
i don´t know if i can help you there but use sql getdatetime() function to get date automatically. You will get time when the procedure is triggered, don´t even need to ask nothing to the user.
I had a application on which i manage the login/logout process automatically, the user doesn´t even know that it exists
algorithm:
create table log_table(id int,user varchar(50),login smalldatetime, logout smalldatetime)
--in login procedure
insert into log_table(235,john,getdatetime(), null)
--in logout procedure
insert into log_table(235,john,null,getdatetime())
Good Luck 
nelsonpaixao@yahoo.com.br
|
|
|
|
|
yeah...thats the way we are doing right now...
but if your table is accessible at many places/procs in application it can be difficult to manage or make sure that every developer is adding this attribute.
if anyone missed it..our business logic will fail...so i feel its better to add a trigger once and forget about it..
but i was wondering if there are any disadvantages of using triggers???
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
Hi
I am developing a project which involves query strings.
I want when user selects a city from a drop down list it has to be passed to the next page and show the values from the database in the gridview based on the value selected in drop downlist.
My code is :
protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
{
string url = "Cityschedules.aspx?City="+ ddlCity.SelectedItem.Text;
Response.Redirect(url);
}
When the user Enters say "New Delhi"
It shud show the all the values where city is "New Delhi"
In Cityschedules.aspx
protected void Page_Load(object sender, EventArgs e)
{
try
{
string strSQLconnection = "Data Source=SHASHI-56B0C28E\\SQLEXPRESS;Initial Catalog=Project_Airways;Persist Security Info=True;User ID=sa;Password=sql2005";
SqlConnection sqlConnection = new SqlConnection(strSQLconnection);
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM [ARS_Schedules] WHERE ([Origin_Place] = '@Origin_Place1')", sqlConnection);
sqlConnection.Open();
string Origin_Place1 = Request.QueryString["City"];
SqlDataReader reader = sqlCommand.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
}
catch (SqlException se)
{
Console.WriteLine(se.Message);
}
finally
{
sqlConnection.close();
}
}
I am getting exceprion
"{"The DataSourceID of 'GridView1' must be the ID of a control of type IDataSource. A control with ID 'SqlDataSource1' could not be found."}"
Also when this exception is solved i am getting another exception saying
"Must declare scalar variable @origin_Place1"
Thanks in advance
regards
shashi
|
|
|
|
|
shashi_code wrote: "{"The DataSourceID of 'GridView1' must be the ID of a control of type IDataSource. A control with ID 'SqlDataSource1' could not be found."}"
Check GridView1 property Datasource on Design if it has set SqlDataSource1 as datasourceid, if it has then remove it.
<br />
SqlCommand sqlCommand = new SqlCommand(" declare @Origin_Place1 as varchar (500) set @Origin_Place1='"+Request.QueryString["City"]+"' SELECT * FROM [ARS_Schedules] WHERE ([Origin_Place] = @Origin_Place1)", sqlConnection);
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I was getting the same error. Ur soultion worked. Thanks a lot.
Meenal Ronghe.
|
|
|
|
|
hi,
try c# forum, you will get more posts
By the way, can´t you do the sql in sql server and trigger it in c#?
Good Luck 
nelsonpaixao@yahoo.com.br
|
|
|
|
|
well u can use a session variable instead of request.city ...
make a session variable and store the value of ur drop down in it
on page load retrieve the value n then query the Database...
Example
make a session variable in global.asax
Session["City"]="";
then store the value of dropdown in session
i.e
session["City=dropdownlist1.selectedvalue"];
then redirect to other url
on page load
string query="SELECT * FROM [ARS_Schedules] WHERE ([Origin_Place] = "+session["City"].ToString()+"";
hope this will work for u...
take care
remember me in ur prayers
FEEAMANALLAH
AHSAN SARFRAZ
UNIVERSITY OF ENGINEERING AND TECHNOLOGY TAXILA PAKISTAN
COMPUTER SYSTEM ENGINEERING DEPARTEMENT
|
|
|
|
|
i wnat to inset the following data into a table. The data is an xml file as folows
-------------
<root>
<employee>
<id>20</id>
<fname>tom</fname>
<lname>smith</lname>
</employee>
</root>
---------------------
i have the follwoing stored procedure that takes the xml text as a parameter, insert the data and return the table data back to me - but im getting this error = "Incorrect syntax near the keyword 'FROM'" and "Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon."
Help Please
--------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROC [dbo].[sp_add_employee1]
@empdata text
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
INSERT into Employee (id, fname, lname)
SELECT FROM OPENXML (@hDoc, '/root/Employee')
WITH Employee (id varchar(50),fname varchar(50), lname varchar(50))
EXEC sp_xml_removedocument @hDoc
SELECT *
from Employee
FOR XML AUTO
-------------------------------------------
|
|
|
|
|
ONeil Tomlinson wrote: SELECT FROM OPENXML (@hDoc, '/root/Employee')
SELECT * FROM OPENXML (@hDoc, '/root/Employee')
|
|
|
|
|
Hi All
We have just upgaded our old sql server from 2000 to a 2005 cluster server
I am now having problems running some bcp commands
The command i am running is
exec master..xp_cmdshell 'bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -o c:\invoice\template\bcpOutput.txt'
the error i am getting is
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
i have tried to add in -U and -P but then get the below
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
i have also tried adding in
exec master..xp_cmdshell 'bcp "select fileoutput from [new_db]..[Invoice_Table] order by roworder" queryout c:\invoice\template\customer.html -c -o -STCSQL1\sqlcluster c:\invoice\template\bcpOutput.txt'
but i get the same error saying it could not open a connection to SQL server
Can anyone help at all ?
|
|
|
|
|
Didn't figure out if you have used all -P, -U and -S at the same time.
If not, provide username, password and instancename for command arguments.
Also check that the server is listening named pipes since it seems to be configured as the protocol for the client component.
Hope this helps,
MIka
|
|
|
|
|
Hi,
I'm trying to create a trigger for a table which has a column of type TEXT. While executing Trigger Statement, It shows a message stating that " can't use text field in inserted/deleted table".
Can anyone give me the solution for this problem?
Thanks in advance
sri
|
|
|
|
|
Hi,
If possible use VARCHAR(MAX) instead of TEXT data type. TEXT should not be used anymore.
If not possible, use INSTEAD OF trigger. Normal AFTER trigger does not support TEXT datatype.
Hope this helps,
Mika
|
|
|
|
|
SELECT * FROM CITY_POPULATION;
CITY_ID CITY_POPULATION
------- ---------------
1 100
2 200
3 300
SELECT * FROM CITY_INFO;
CITY_ID CITY_NAME
------- ---------
1 FIRST CITY
2 SECOND CITY
3 THIRD CITY
How can I have some sort of union query that would have the city id, name and population in one output where the id columns from the two tables match, I mean, the city names from the second table should be picked up to match their id number from the other table showing how much population is in that country id with it's name.. something like:
ID, Country Name, Population
Many thanks guys!
modified on Wednesday, September 3, 2008 3:30 PM
|
|
|
|
|
You would use a join for this.
Select *
From City_Info i
Inner Join City_Population p on
p.City_Id = i.City_Id
The output should be similar to:
City_Id City_Name City_Id City_Population
------ ------ ------- ------------
1 First City 1 100
2 Second City 2 200
3 Third City 3 300
You probably wouldn't want City_Id two times, so you would need to specify the columns that you want in the select list. Be sure to qualify the column names so you don't get an ambiguous column error (ex: i.City_id, i.City_Name, p.City_Population).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
