return an id while doing an insert\update to a table

Hi people,

i Have a small issue. I need to be able to retrive an id number of a new row to a table using the the insert into command. I was able to do this in sql 2000 but the same sql does not work now in 2005. here is the code

"Set NoCount On; select user_id from users insert into users (username) values('" & CurrentUser & "')"

This used to work in sql2000,

I am woundering if anyone could help me or point me in the right direction for doing this with SQL 2005

Best regards

RBowden

Did you try putting a semincolumn between the statements (before the insert) ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

I tried putting the ; before the insert function it is still returns 0

any other ideas?

|||

Ah, ok now I know what you mean. You are refering to the OUTPUT clause in SQL Server 2005.

"Set NoCount On; DECLARE @.Somevar VARCHAR(10);insert into users (username) OUTPUT user_id INTO @.SomeVar values('" & CurrentUser & "')"; SELECT @.SomeVar"

Look in the BOL, there should me some examples around that. If you are using an IDENTITY Column for the userid cou can also query the SCOPE_IDENTITY() function for the new identity value.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Cheers,

thank you very much for your help, that worked a treat.

keep up the good advice

all the best

retriving gender from database in a radio button

i am storing gender in the database.
i want to retrive it in one of the radiobuttons for male and female already present on the form . how can i?

related radio button should be highlighted while clicking on the button........

Shubhada

Retriving an xml string stored in varchar(max)

I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code

dr = cmd.ExecuteReader();_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();Label1.Text = _xmlFile;

and this is what I get "12"
Maybe I missed something to get the whole XML StringWhat do you mean by getting "12"? It confused me...|||

mehdi_tn:

I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code

dr = cmd.ExecuteReader();
_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();
Label1.Text = _xmlFile;

and this is what I get "12"
Maybe I missed something to get the whole XML String

It seems to me you could do it like this:

_xmlFile = dr["XmlJoin"].ToString();

|||Thanks for answering, In fact I placed the retrived XMl in a label and the label showed "1"
When debugin I founded the whole XML in the variable. The problem was from the label try this :

Label1.Text="<;x>1</x>";// this will show 1

Bizarre this controlsql

Retrive old JobHistory

I have a job which only keeps 100 entries in the job history. When one new
entry comes, the oldest entry in the list is deleted.
I need retrieve the old job histories that have been removed from the
current job history list. How can I do it? Restore DB is not an option.
Thanks a lot,
LixinYou will need to restore msdb(as different db) and look at the table
sysjobhistory, I recommend you iether increase the number of history rows
for that job or write a job which copies the rows to an archive table.
Yovan
"Lixin Fan" <nospam@.hotmail.com> wrote in message
news:uYx1XbouDHA.2712@.tk2msftngp13.phx.gbl...
> I have a job which only keeps 100 entries in the job history. When one new
> entry comes, the oldest entry in the list is deleted.
> I need retrieve the old job histories that have been removed from the
> current job history list. How can I do it? Restore DB is not an option.
> Thanks a lot,
> Lixin
>|||Hi Lixin
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I think a trigger will be a workaround to you question.
Suppose you want to record the job history of Job_test, and the job_id is
={07C00C79-AAB0-49B0-BA0A-9E7C884440DB} ( you can get this information from
two tables in database 'msdb': 'sysjob' and 'sysjobhistory').
Suppose you have a table 'job_history' in database 'Database_test', and the
table 'job_history' has the same schema with the table 'sysjobhistory' in
msdb and you want to use this table to save the job history. You can add a
trigger to the sysjobhistory.
The thinking is: when the system add a new entry into the 'sysjobhistory',
check if this new entry is for job_test. If no, then take no action. If
yes, then the trigger will copy this entry in the job_history table in
database_test. By this way, you can record all the job history when the job
occurred.
The code for the trigger would be like this:
CREATE TRIGGER [jobtest] ON [dbo].[sysjobhistory]
FOR insert
AS
Declare @.job_date int
Declare @.job_time int
Declare @.job_idx varchar
Set @.job_idx=' ({07C00C79-AAB0-49B0-BA0A-9E7C884440DB}'
If @.job_idx<> (select top 1 job_id from [msdb].[dbo].[rundate] where
job_id=@.job_idx order by rundate,runtime asc)
--if the latest entry in the sysjobhistory is not caused by the job you
want to record
--no action
Begin
Return
End
--If the job you want to record add a new entry
Else
Begin
Insert [database_test].[dbo].[job_history].[rundate] value (select top 1 *
from [msdb].[dbo].[runtime] where job_id=@.job_idx order by rundate,runtime
asc
End
Note:
1) This code is just a thinking, not a tested one
2) This method will add the workload on your system, especially on a system
which has many concurrent jobs.
I hope this will help you to solve your problem. If you still have
questions, please feel free to post message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Great help. Thank you very much.
Lixin
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:NMNq6HxuDHA.2900@.cpmsftngxa07.phx.gbl...
> Hi Lixin
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.
> I think a trigger will be a workaround to you question.
> Suppose you want to record the job history of Job_test, and the job_id is
> ={07C00C79-AAB0-49B0-BA0A-9E7C884440DB} ( you can get this information
from
> two tables in database 'msdb': 'sysjob' and 'sysjobhistory').
> Suppose you have a table 'job_history' in database 'Database_test', and
the
> table 'job_history' has the same schema with the table 'sysjobhistory' in
> msdb and you want to use this table to save the job history. You can add a
> trigger to the sysjobhistory.
> The thinking is: when the system add a new entry into the 'sysjobhistory',
> check if this new entry is for job_test. If no, then take no action. If
> yes, then the trigger will copy this entry in the job_history table in
> database_test. By this way, you can record all the job history when the
job
> occurred.
> The code for the trigger would be like this:
> CREATE TRIGGER [jobtest] ON [dbo].[sysjobhistory]
> FOR insert
> AS
> Declare @.job_date int
> Declare @.job_time int
> Declare @.job_idx varchar
> Set @.job_idx=' ({07C00C79-AAB0-49B0-BA0A-9E7C884440DB}'
> If @.job_idx<> (select top 1 job_id from [msdb].[dbo].[rundate] where
> job_id=@.job_idx order by rundate,runtime asc)
> --if the latest entry in the sysjobhistory is not caused by the job you
> want to record
> --no action
> Begin
> Return
> End
>
> --If the job you want to record add a new entry
> Else
> Begin
> Insert [database_test].[dbo].[job_history].[rundate] value (select top 1 *
> from [msdb].[dbo].[runtime] where job_id=@.job_idx order by rundate,runtime
> asc
> End
> Note:
> 1) This code is just a thinking, not a tested one
> 2) This method will add the workload on your system, especially on a
system
> which has many concurrent jobs.
> I hope this will help you to solve your problem. If you still have
> questions, please feel free to post message here and I am ready to help!
>
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>

retrive all records within the Case statment

i want to filter a table by a view using a defind function
for example:
select * from person
where dbo.person.company like case CmpName()
when 'all' then '%'
else CmpName()
end
prblem is that '%' doesn't show the records with NULL value
how can i
thanks
samTry using IS NULL clause
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]
"Sam" <focus10@.zahav.net.il> wrote in message
news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> i want to filter a table by a view using a defind function
> for example:
> select * from person
> where dbo.person.company like case CmpName()
> when 'all' then '%'
> else CmpName()
> end
> prblem is that '%' doesn't show the records with NULL value
> how can i
> thanks
> sam
>|||"IS NULL" is not working with "=" , or "Like" operators
so i can not use it with the "CASE" statment
"Uwa Agbonile [MSFT]" <uwaag@.online.microsoft.com> wrote in message
news:ez3NxhwYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Try using IS NULL clause
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Regards,
> Uwa Agbonile[MSFT]
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
>

retrive all records within the Case statment

i want to filter a table by a view using a defind function
for example:
select * from person
where dbo.person.company like case CmpName()
when 'all' then '%'
else CmpName()
end
prblem is that '%' doesn't show the records with NULL value
how can i
thanks
sam
Try using IS NULL clause
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]
"Sam" <focus10@.zahav.net.il> wrote in message
news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> i want to filter a table by a view using a defind function
> for example:
> select * from person
> where dbo.person.company like case CmpName()
> when 'all' then '%'
> else CmpName()
> end
> prblem is that '%' doesn't show the records with NULL value
> how can i
> thanks
> sam
>
|||"IS NULL" is not working with "=" , or "Like" operators
so i can not use it with the "CASE" statment
"Uwa Agbonile [MSFT]" <uwaag@.online.microsoft.com> wrote in message
news:ez3NxhwYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Try using IS NULL clause
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Regards,
> Uwa Agbonile[MSFT]
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
>
sql

retrive a file from a binary data field

hello dear friends

I am trying to save a binary file to database with the following code:

publicstaticvoid memorystreamToDb()

{

MemoryStream mst =newMemoryStream(); UnicodeEncoding u =newUnicodeEncoding(); string Textn ="Test"; byte[] b = u.GetBytes(Textn);

mst.Write(b ,0,Textn.Length );

BinaryReader reader =newBinaryReader(mst); byte[] file = reader.ReadBytes((int)mst.Length); using (SqlConnection connection =newSqlConnection("Some Connection String"))

{

SqlCommand command =newSqlCommand("INSERT INTO temp (examplefile) Values(@.File)", connection);

command.Parameters.Add(

"@.File",SqlDbType.Binary, file.Length).Value = file;

connection.Open();

command.ExecuteNonQuery();

}

reader.Close();

mst.Close();

}

or with the other method from a real file (not memory stream)

publicstaticvoid Addfile(string path)

{

CommonMethods_class k =newCommonMethods_class(); byte[] file = GetFile(path); using (SqlConnection connection =newSqlConnection(k.Get_connection_string()))

{

SqlCommand command =newSqlCommand("INSERT INTO temp (examplefile) Values(@.File)", connection);

command.Parameters.Add(

"@.File",SqlDbType.Binary, file.Length).Value = file;

connection.Open();

command.ExecuteNonQuery();

}

}

and after running each of them seams that the file have been saved; but I can not retrive the files. I have tried some solutions from msdn but inside the created file is empty. the point that i really look for it is to just working with memory not in the disk before saving. and then retriving each field that I want.

looking forward your points

thank you in advance

Hiashk1860 ,

If you haven't readRead and Write BLOB Data by Using ADO.NET Through ASP.NET you can take a look.

|||

hi

thank you for yor response, Yes, I have read the article and some other Articles and still have problem.

retrive a binary field from database

hi

I have used the following code (mostly created by MSDN) to retrive a binary field from SQL database. it works but I have extra space between characters. for example if I save a text file with "Hello world" text, after retriving I have it like "H e l l o w o r l d". what is the problem??

I am really looking forward your answers

private void retrive()

{

publicvoid a()

{

SqlConnection connection =newSqlConnection("Some Connection string");SqlCommand command =newSqlCommand("Select * from temp", connection);// Writes the BLOB to a fileFileStream stream;// Streams the BLOB to the FileStream object.BinaryWriter writer;// Size of the BLOB buffer.int bufferSize = 50;// The BLOB byte[] buffer to be filled by GetBytes.byte[] outByte =newbyte[bufferSize];// The bytes returned from GetBytes.long retval;// The starting position in the BLOB output.long startIndex = 0;// Open the connection and read data into the DataReader.

connection.Open();

SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);while (reader.Read())

{

// Create a file to hold the output.

stream =

newFileStream("C:\\file.txt",FileMode.OpenOrCreate,FileAccess.Write);

writer =

newBinaryWriter(stream);// Reset the starting byte for the new BLOB.

startIndex = 0;

// Read bytes into outByte[] and retain the number of bytes returned.

retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);

// Continue while there are bytes beyond the size of the buffer.while (retval == bufferSize)

{

writer.Write(outByte);

writer.Flush();

// Reposition start index to end of last buffer and fill buffer.

startIndex += bufferSize;

retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);

}

// Write the remaining buffer.if (retval != 0)

writer.Write(outByte, 0, (

int)retval - 1);

writer.Flush();

// Close the output file.

writer.Close();

stream.Close();

}

// Close the reader and the connection.

reader.Close();

connection.Close();

}

}

Hi, I guess you're storing the text as NTEXT data type in your SQL Server. Since NTEXT/NVARCHAR/NCHAR data uses 2 bytes to stores a unicode character, when you retrieve the text from database using binary stream, each character is transferred as 2 bytes (with the 2nd byte empty), so that's why you found spaces between words. You?can?use?UltraEdit?to?open?the?generated?text?file?and?swith?to?hex?mode,?you'll?00s?between?normal?characters.

retrieving an instance name

In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
be the SQLServer instance where the DTS is located.
How can I retrive the instance name?
Thnx
Kyky
Hi
Not that I am aware of. DTS Packages can be run outside the scope of the SQL
Server, or be compiled into VB6 projects.
Regards
Mike
"Kyky" wrote:

> In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>
>
|||You can add a T-SQL to the DTS package and select @.@.servername to get the
instance.. .map the return value to a global variable and use it anywhere...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kyky" <kyky_az@.yahoo.it> wrote in message
news:e%230BNtZGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> In an DTS ActiveX I need to open an ADODB connection whose Data Source
> shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>

retrieving an instance name

In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
be the SQLServer instance where the DTS is located.
How can I retrive the instance name?
Thnx
KykyHi
Not that I am aware of. DTS Packages can be run outside the scope of the SQL
Server, or be compiled into VB6 projects.
Regards
Mike
"Kyky" wrote:

> In an DTS ActiveX I need to open an ADODB connection whose Data Source sho
ud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>
>|||You can add a T-SQL to the DTS package and select @.@.servername to get the
instance.. .map the return value to a global variable and use it anywhere...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kyky" <kyky_az@.yahoo.it> wrote in message
news:e%230BNtZGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> In an DTS ActiveX I need to open an ADODB connection whose Data Source
> shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>

retrieving an instance name

In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
be the SQLServer instance where the DTS is located.
How can I retrive the instance name?
Thnx
KykyHi
Not that I am aware of. DTS Packages can be run outside the scope of the SQL
Server, or be compiled into VB6 projects.
Regards
Mike
"Kyky" wrote:
> In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>
>|||You can add a T-SQL to the DTS package and select @.@.servername to get the
instance.. .map the return value to a global variable and use it anywhere...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kyky" <kyky_az@.yahoo.it> wrote in message
news:e%230BNtZGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> In an DTS ActiveX I need to open an ADODB connection whose Data Source
> shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>

Retrieve SQL servers in the network

Hi all
Is there any way to retrive all available SQL servers on the network without using the SQL DMO objects?

Thanks in advance.hello...

from SMO sample:
============================
Dim dt As DataTable
dt = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(False)
For Each dr As DataRow In dt.Rows
serverListBox1.Items.Add(dr(0).ToString())
Next
============================|||

Thanks a lot Jung but I'm looking for a solution outside the components. i.e. an API function or something because I don’t want to deploy neither the DMO nor the SMO to my clients.
I wonder how the property pages of a UDL file can retrieve the servers without installing neither of the components

|||I don't know for sure but I am guessing that the UDL dialog uses the ODBC Function BrowseConnect or NetServerEnum. You need to call these functions via PInvoke from managed code with Fx 1.1 with Fx 2.0 there is a managed API.

Http://www.sqldev.net has further explanations.

-Euan|||Make sure your SQL Browser service is up and running.