Creating an INSERT UPDATE Trigger that works on certain account

Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> the proprietary database account. How do I capture the user or group name in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> What is the equivalent in MS SQL? Thanks in advance!!!|||This is a multi-part message in MIME format.
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the =currently logged in user account.
Look in Books-on-Line for more information on system_user.
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message =news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database server will =
> occassionally push data back into MS SQL via JDBC. I don't want the MS =SQL > trigger to execute its SQL code if the INSERT UPDATE Trigger was =initiated by > the proprietary database account. How do I capture the user or group =name in > MS SQL? I know in PostgreSQL, you can use the "user" variable in a =trigger. > What is the equivalent in MS SQL? Thanks in advance!!!
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try the sytem function: system_user. =
For example, SELECT system_user will provide the =Domain\Loginname of the currently logged in user account.
Look in Books-on-Line for more =information on system_user.
-- Arnie Rowland, YACE* "To be =successful, your heart must accompany your knowledge."
*Yet Another Certification =Exam
"Jaime Rios" wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...> =Hi folks,> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database =server will > occassionally push data back into MS SQL via JDBC. I don't want =the MS SQL > trigger to execute its SQL code if the INSERT UPDATE =Trigger was initiated by > the proprietary database account. How do I capture =the user or group name in > MS SQL? I know in PostgreSQL, you can use =the "user" variable in a trigger. > What is the equivalent in MS SQL? =Thanks in advance!!!

---

--=_NextPart_000_0EFB_01C6952D.3CD86780--|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>> Hi folks,
>> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL
>> 2000
>> to another proprietary database server. The other database server will
>> occassionally push data back into MS SQL via JDBC. I don't want the MS
>> SQL
>> trigger to execute its SQL code if the INSERT UPDATE Trigger was
>> initiated by
>> the proprietary database account. How do I capture the user or group name
>> in
>> MS SQL? I know in PostgreSQL, you can use the "user" variable in a
>> trigger.
>> What is the equivalent in MS SQL? Thanks in advance!!!|||I concur with Arnie.
--
Venkat
sql server admirer
"Arnie Rowland" wrote:
> Try the sytem function: system_user.
> For example, SELECT system_user will provide the Domain\Loginname of the currently logged in user account.
> Look in Books-on-Line for more information on system_user.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> > Hi folks,
> > I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> > to another proprietary database server. The other database server will
> > occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> > trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> > the proprietary database account. How do I capture the user or group name in
> > MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> > What is the equivalent in MS SQL? Thanks in advance!!!

Creating an INSERT UPDATE Trigger that works on certain account

just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>|||Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated b
y
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!|||just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>sql

Creating a trigger using a cursor

Hi all,
I need to create a trigger on all tables in a database that will insert into
an audit table username, and event on the table. I can create the trigger
individually, but I would like to put this into a cursor so I do not have to
run the trigger 500 times.
I am grabbing all user tables and trying to exec a string within the cursor
to create the triggers. I keep gettin eror by kyword insert. which I believe
is near
" INSERT INTO #inputbuffer"
Below is the code I am using:
TIA,
Joe
declare @.name varchar(100), @.str varchar(8000)
declare crscall cursor for
select name from sysobjects
where type = 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status = 0
begin
declare @.str varchar(8000),@.name varchar(50)
set @.name = 'testrights'
select @.str = 'IF EXISTS (SELECT name FROM sysobjects
WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
'FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll
deallocate crsCAllAre you sure you really want this kind of automation?
Anyway, change the script to print out the query strings instead of just
executing them. Then test them: parse them and attempt to execute them.
And when it's done - I don't want to scare you - you'll still have to test
them 500 times.
ML|||Thank you daniel,
I guess it was just an extra pair of eyes!
The first typo did the trick.
Thanks again.
Joe|||Thank you! this was helpful as well as Daniels.|||This is a one-time thing to create all the triggers so it won't get into
production code.
So good for the poster if he can automate the creation of the triggers.
But I agree with out on the last part, he's still have to test them all.
Maybe he can automate that part too. ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:8C32588A-D032-40D3-B554-D6A64EB83F15@.microsoft.com...
> Are you sure you really want this kind of automation?
> Anyway, change the script to print out the query strings instead of just
> executing them. Then test them: parse them and attempt to execute them.
> And when it's done - I don't want to scare you - you'll still have to test
> them 500 times.
>
> ML|||If he puts his mind to it, someday his entire life might get automated. :)
He'll have automated himself out of existence.
ML|||That's what I am looking for. Automation is a wonderful thing!|||Well, I wish you good luck on your journey. :)
I hope those 500 tables weren't created automatically by mistake... ;)
ML|||jaylou wrote on Thu, 28 Jul 2005 07:01:13 -0700:

> Hi all,
> I need to create a trigger on all tables in a database that will insert
> into an audit table username, and event on the table. I can create the
> trigger individually, but I would like to put this into a cursor so I do
> not have to run the trigger 500 times.
> I am grabbing all user tables and trying to exec a string within the
> cursor to create the triggers. I keep gettin eror by kyword insert. which
> I believe is near
> " INSERT INTO #inputbuffer"
> Below is the code I am using:
Did you copy and paste that code? If so, there are 2 errors I spotted
straight away, both near the word INSERT. Comments inline, look for Typo #1
and Typo #2.
Dan

> TIA,
> Joe
> declare @.name varchar(100), @.str varchar(8000)
> declare crscall cursor for
> select name from sysobjects
> where type = 'u'
> open crscall
> fetch next from crscall
> into @.name
> while @.@.Fetch_Status = 0
> begin
> declare @.str varchar(8000),@.name varchar(50)
> set @.name = 'testrights'
> select @.str = 'IF EXISTS (SELECT name FROM sysobjects
> WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
> '+''''+'TR'+''''+')
> DROP TRIGGER Audit_InsUpd'
> exec (@.str)
> select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
> ON '+@.name+
> 'FOR INSERT, UPDATE AS
Typo #1. There's no space between ' and FOR, so you'd end up with invalid
syntax here as the table name will be concatenated into FOR and then the
INSERT keyword is invalid as there is no FOR.

> BEGIN
> SET NOCOUNT ON
> DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
> CREATE TABLE #inputbuffer
> (
> EventType nvarchar(30),
> Parameters int,
> EventInfo nvarchar(255)
> )
> SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
> ' INSERT INTO #inputbuffer
Typo #2. There's a ' missing at the start of this line, so this INSERT won't
be inside the string being assigned to @.str, it's going to be run in the
trigger creating code and #inputbuffer doesn't yet exist as a table.
However, I'm pretty sure the error is due to typo #1 otherwise you'd have
received an error about table #inputbuffer not existing, the compiler might
not be getting this far.

> EXEC (@.ExecStr)
> SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> insert into Tbl_MSDBAudit
> select SUSER_SNAME(),@.qry
> END'
> select @.str
> exec (@.str)
> fetch next from crscall
> into @.name
> end
> close crsCAll
> deallocate crsCAll
>|||Hi
Run this Code
declare @.str varchar(8000),@.name varchar(50)
declare crscall cursor for
select name from sysobjects
where type =3D 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status =3D 0
begin
--set @.name =3D 'testrights'
select @.str =3D 'IF EXISTS (SELECT name FROM sysobjects
WHERE name =3D '+''''+@.name+'_Audit_InsUpd'+'=AD'''+' AND type =3D
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str =3D 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
' FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr =3D '+''''+'DBCC INPUTBUFFER('+
STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry =3D (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll=20
deallocate crsCAll=20
With warm regards
Jatinder Singhsql

creating a table

I have used this syntax to create a table
create emp(emp_id int,staff_Name varchar(10),det_Name varchar(20));
This creates a table but I cannot insert any data in the table.How can i insert data in the table.insert into emp values(100,'sridhar','programmer')

Creating a stored procedure to insert data?

Hello all,

I am having a lot of trouble with stored procedures. Could anyone help me out.

I have a table which contains a number of meetings. What I want to do is search this table, get out all the meetings for today and put them in a seperate table meetings today.

I can select the values, and I can insert the values.

But how do I store the values so that i can pass the results of the select to the insert?

Im also having a lot of trouble with storing date values.

ANy help would be greatly appreciated.

Regards,

Padraic Hickey

if exists (select * from dbo.sysobjects where id = object_id(N'[table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [table2]
SELECT *
INTO table2
FROM table1
WHERE convert(varchar,table1.meetingdate,112)=convert(varchar,@.mydate,112)
|||

Cheers Motley,

I was having a lot of trouble with that one.

If ever i can return the favour.

Padraic

Creating a Script out of Sample Data

SQL Server 2000

Is there any way to take sample data in my database and create an INSERT INTO script?

I have a commercial application that I would like to include sample data, and instead of restoring a backup like I am doing now, I would like to first run a script that creates the database, stored procedures, etc, then run a script that inserts sample data if the customer so chooses.

I know I can do this manually, but is there any way to create the script based on exisiting data?

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comTim,

You can use our free tool, QALite or ObjectScriptr to generate insert
statements. You can download it from site.

--
-oj
http://www.rac4sql.net

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:J%1Lb.761723$HS4.6022647@.attbi_s01...
SQL Server 2000

Is there any way to take sample data in my database and create an INSERT INTO
script?

I have a commercial application that I would like to include sample data, and
instead of restoring a backup like I am doing now, I would like to first run a
script that creates the database, stored procedures, etc, then run a script that
inserts sample data if the customer so chooses.

I know I can do this manually, but is there any way to create the script based
on exisiting data?

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related
website.
http://www.vehiclewebstudio.com|||"Tim Morrison" <sales@.kjmsoftware.com> wrote in message news:<J%1Lb.761723$HS4.6022647@.attbi_s01>...
> SQL Server 2000
> Is there any way to take sample data in my database and create an INSERT
> INTO script?
> I have a commercial application that I would like to include sample
> data, and instead of restoring a backup like I am doing now, I would
> like to first run a script that creates the database, stored procedures,
> etc, then run a script that inserts sample data if the customer so
> chooses.
> I know I can do this manually, but is there any way to create the script
> based on exisiting data?
> TIA
> --
> Tim Morrison
> ----------------------
> ---
> Vehicle Web Studio - The easiest way to create and maintain your vehicle
> related website.
> http://www.vehiclewebstudio.com
> --

You could use BCP/DTS to load the sample data from external files, but
if you want to use pure TSQL, then this is one possibility:

http://vyaskn.tripod.com/code.htm#inserts

Simon|||Looks like ObjectScriptr is just what im looking for.

Thanks,

Tim Morrison

"oj" <nospam_ojngo@.home.com> wrote in message
news:JN7Lb.781998$Fm2.760879@.attbi_s04...
> Tim,
> You can use our free tool, QALite or ObjectScriptr to generate insert
> statements. You can download it from site.
> --
> -oj
> http://www.rac4sql.net
>
> "Tim Morrison" <sales@.kjmsoftware.com> wrote in message
> news:J%1Lb.761723$HS4.6022647@.attbi_s01...
> SQL Server 2000
> Is there any way to take sample data in my database and create an INSERT
INTO
> script?
> I have a commercial application that I would like to include sample data,
and
> instead of restoring a backup like I am doing now, I would like to first
run a
> script that creates the database, stored procedures, etc, then run a
script that
> inserts sample data if the customer so chooses.
> I know I can do this manually, but is there any way to create the script
based
> on exisiting data?
> TIA
> --
> Tim Morrison
> -----------------------
--
> Vehicle Web Studio - The easiest way to create and maintain your vehicle
related
> website.
> http://www.vehiclewebstudio.com|||Hi !
Try http://www.sqlscripter.com

This tool is able to create INSERT, UPDATE and DELETE commands.

Nicksql

creating a insert trigger

i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.

The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated.
Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.

My Two tables DDL are like down below

create table student(
studentId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
insCheck char(1) not null,
);

create table instructor(
instructorId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
instructorQual varchar(100) not null,
);

thanks for your help in advance!
gazawaymyYou don't need a trigger for this. In the proc that populates your student, if they have the insCheck = 'y' then execute the proc to populate the instructor with the appropriate parameters.

Also, why are you populating the studentId and instructorId manually? Can you explain this process? It sounds like a design issue. Is there a formula to determine this so you can automate it?|||I didn't know I can do it that way with procedure. I still have to figure out the actual procedure code. Do you have the code in mine?

I made student, instructor table's primary key to be manual because of relationshion between contract and these tables. Since i already populate a lot of records and these are the primary keys, I am not sure I should change the table structure. What do you think?

If I should change to automatic, how I do that?

thanks
michelle|||Why are you storing Students and Instructors in separate tables? Store them in a single table ("People", for instance).|||because

student and instructor table are connected with class table.|||That's not a reason to separate the two:

Table PEOPLE (PersonID, PersonName, InstructorFlag)

Table CLASSES(ClassID, InstructorPersonID, ClassName)

Table CLASSMEMBERS(ClassID, PersonID)

Strictly speaking, the instructor flag is not necessary but makes interface coding easier.|||You're doing this crap on a Saturday?

I thought I had an addiction...ok I do...

And yes the flag is bs...the relationship identifies if the person is or isn't an instructor...

what they can't be both?

Creating a dataset

Im trying to complete my function which will allow me to insert data into a table by referencing it in the relevant pages, the following code is what i am using to take the SQL query, execute it and return the resultant dataset in destResponse. However i am getting the following error message; "Compiler Error Message:CS1026: ) expected". And it is coming from the following line;

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";

Here is my code below, please feel free to criticise it, and im sure the error above is not the only error i will be getting.

publicstaticvoid SQLExecute(string strTableName,string strData)

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

("INSERT INTO" + strTableName + strData +" VALUES " + strData);

|||

let me know if it is ok

|||

your line should be like below

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName + strData +") VALUES (" + strData +")");

|||

Missing a space after INTO.

Missing a closing parenthesis before the first semi-colon.

Missing a literal space and opening parenthesis between the strTableName and strData -- should be strTableName + ' (' + strData.

Used strData for both the column name list AND the column values.

SqlCommand command =new SqlCommand("INSERT INTO " + strTableName +" (" + strColumnList +") VALUES (" + strData +")");

|||

Hi thanks for responding, the one that worked was Motleys suggestion, however my next task is to return the results in a dataset; this is my code so far, am i on the right track?

publicstaticvoid SQLExecute(string strTableName,string strData)

{

String strConnection =null;

try

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName +" (" + strData +") VALUES (" + strData +")");command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

}

creating a back up [create and insert statement] for a table in mssql server 2005

hi all,
i have a php application connecting to mssql server 2005. i want to generate sql script for backing up a table with create statement and complete inserts. i dont want to use data publishing wizard. how can i do it.

Thanks

Quote:

Originally Posted by bhakti

hi all,
i have a php application connecting to mssql server 2005. i want to generate sql script for backing up a table with create statement and complete inserts. i dont want to use data publishing wizard. how can i do it.

Thanks

or you can do a SELECT ... INTO NewTableName from MyTable...