return

whats wrong with this SP? I want @.id to contain the row identity of the newly created row as a return value.

ALTER PROCEDURE setCountry
(
@.name varchar( 50 ) = NULL,
@.alt varchar( 24 ) = NULL,
@.code varchar( 3 ) = NULL,
@.id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )
@.id = @.@.identity
RETURN

INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )select@.id = @.@.identity

couple of things :
if you'd like to return this id back to asp.net you need to return it as an OUTPUT parameter..check out BOL for OUTPUT Parameters in stored procs..

also i'd recommend using SCOPE_IDENTITY() rather than @.@.IDENTITY. check out BOL again for the differences between them.

hth|||Thanks - but what is BOL?|||RETURN @.id

??

personally I'd do it this way

SET NOCOUNT ON
-- do insert
...
SELECT @.@.Identity|||BOL = Books On Line - best reference for sql server 2000. Free Download from microsoft.

hth|||Thanks! - I got the BOL acronym too - duh - Books On Line. I will try it now and actually may use SCOPE_IDENTITY() in place of @.@.identity.|||Atrax, I think the "return" method is better as it won't incur a result set. Although I'd use a OUTPUT param rather than return, I prefer to have that indicate some form of "state of the operation".|||Okay - now I can retrieve the result using ExecuteScalar - or DataReader or both?? Because when I run it in VS I dont see the results of the procedures. I mean it adds the row, but I don't see any output in the OUTPUT window.|||if you just need to return the ID you'd be better off using executescalar().

in vb.net

dim userID as integer
...
'open connection
...
userid=sqlcommand.ExecuteScalar()
...
'close connection

and use OUTPUT parameter to return the output form the stored proc...BOL had some samples no how to do it..

hth

retriving data from a temporal table

Hi ,
I've created a stored procedure wich creates a temporal table (called
#results) , then i fill the table with data and finally at the end of the
procedure i make a "Select * FROM #Results" .
When i execute the procedure from the query analizer i can get the data
without any problem. But if i try to get the data into a visual basic ADO
recordset it always fails. I think the problem is because i'm using a
temporal table , but i need to use that solution.
If someone could give me one solution to get the data of a temporal table
into a recordset i'd been thankful.
Thanks in advance for you answers and pardon for my bad english.hi
u can use temp table when u call from VB program, but u need to do everythin
g
1. Create Table
2. Insert Data
3. Retrive data
in the same SP. else the data will be deleted / scope is lost
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Jorge Lozano" wrote:

> Hi ,
> I've created a stored procedure wich creates a temporal table (called
> #results) , then i fill the table with data and finally at the end of the
> procedure i make a "Select * FROM #Results" .
> When i execute the procedure from the query analizer i can get the data
> without any problem. But if i try to get the data into a visual basic ADO
> recordset it always fails. I think the problem is because i'm using a
> temporal table , but i need to use that solution.
> If someone could give me one solution to get the data of a temporal table
> into a recordset i'd been thankful.
> Thanks in advance for you answers and pardon for my bad english.|||My guess is that it will work if you add SET NOCOUNT ON in the beginning of
your stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
news:FED3495C-D379-457D-AE02-D9B188B156BC@.microsoft.com...
> Hi ,
> I've created a stored procedure wich creates a temporal table (called
> #results) , then i fill the table with data and finally at the end of the
> procedure i make a "Select * FROM #Results" .
> When i execute the procedure from the query analizer i can get the data
> without any problem. But if i try to get the data into a visual basic ADO
> recordset it always fails. I think the problem is because i'm using a
> temporal table , but i need to use that solution.
> If someone could give me one solution to get the data of a temporal table
> into a recordset i'd been thankful.
> Thanks in advance for you answers and pardon for my bad english.|||It worked Perfect!!!
Thanks a loot Tibor i owe you a very big beer.
"Tibor Karaszi" wrote:

> My guess is that it will work if you add SET NOCOUNT ON in the beginning o
f your stored procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
> news:FED3495C-D379-457D-AE02-D9B188B156BC@.microsoft.com...
>|||Watch out. I'm an excellent beer drinker ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
news:02F4D367-6A89-49C9-9971-BE5BA4927491@.microsoft.com...
> It worked Perfect!!!
> Thanks a loot Tibor i owe you a very big beer.
> "Tibor Karaszi" wrote:
>

Retriveing the process ID for a SQL Server Agent job being run

We have created a SQL Server Job called 'Asynchronous Batch Agent'
which is run Asynchronously on a set of databases each containing a
batch.
What I would like to do is to retrieve the Process ID of a particular
instance of this agent when its being called and write that to a table
with the ID for the individual database.
Im having little luck at finding this information. I look at sp_who2
and sysprocesses...but get a link to the job.
Thanks for your help on this
ChrisHi Chris,
Try @.@.spid, db_id() and db_name().
Hope this helps,
Ben Nevarez
"chris.asaipillai@.gmail.com" wrote:
> We have created a SQL Server Job called 'Asynchronous Batch Agent'
> which is run Asynchronously on a set of databases each containing a
> batch.
> What I would like to do is to retrieve the Process ID of a particular
> instance of this agent when its being called and write that to a table
> with the ID for the individual database.
> Im having little luck at finding this information. I look at sp_who2
> and sysprocesses...but get a link to the job.
> Thanks for your help on this
> Chris
>

Retriveing the process ID for a SQL Server Agent job being run

We have created a SQL Server Job called 'Asynchronous Batch Agent'
which is run Asynchronously on a set of databases each containing a
batch.
What I would like to do is to retrieve the Process ID of a particular
instance of this agent when its being called and write that to a table
with the ID for the individual database.
Im having little luck at finding this information. I look at sp_who2
and sysprocesses...but get a link to the job.
Thanks for your help on this
Chris
Hi Chris,
Try @.@.spid, db_id() and db_name().
Hope this helps,
Ben Nevarez
"chris.asaipillai@.gmail.com" wrote:

> We have created a SQL Server Job called 'Asynchronous Batch Agent'
> which is run Asynchronously on a set of databases each containing a
> batch.
> What I would like to do is to retrieve the Process ID of a particular
> instance of this agent when its being called and write that to a table
> with the ID for the individual database.
> Im having little luck at finding this information. I look at sp_who2
> and sysprocesses...but get a link to the job.
> Thanks for your help on this
> Chris
>

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 data using a stored procedure

I have created the following stored procedure and tried to retrieve it's output value in C#, however I am getting exceptions. Can anyone tell me what I am doing wrong? Thanks!

1 ALTER PROCEDURE [dbo].[GetCustomerById]23@.CustId NCHAR(5),4@.CustomerName NVARCHAR(50) OUTPUT56 AS7 BEGIN89SELECT @.CustomerName = ContactName10FROM Customers11WHERE CustomerId = @.CustId1213 END1415 RETURN16171819202122 SqlConnection conn = GetConnection();//retrieves a new SqlConnection23 SqlCommand cmd =new SqlCommand();24 cmd.Connection = conn;25 cmd.CommandType = CommandType.StoredProcedure;26 cmd.CommandText ="GetCustomerById";2728 SqlParameter paramCustId =new SqlParameter();29 paramCustId.ParameterName ="@.CustId";30 paramCustId.SqlDbType = SqlDbType.NChar;31 paramCustId.Direction = ParameterDirection.Input;32 paramCustId.Value ="ALFKI";3334 SqlParameter paramCustomerName =new SqlParameter();35 paramCustomerName.ParameterName ="@.CustomerName";36 paramCustomerName.SqlDbType = SqlDbType.NVarChar;37 paramCustomerName.Direction = ParameterDirection.Output;3839 cmd.Parameters.Add(paramReturn);40 cmd.Parameters.Add(paramCustId);41 cmd.Parameters.Add(paramCustomerName);4243 conn.Open();44 SqlDataReader reader = cmd.ExecuteReader();4546string custName = cmd.Parameters["@.CustomerName"].Value.ToString();

You are not specifying the size, try

cmd.Parameters.Add("@.CusomerId", SqlDbType.NVarChar, 5);
cmd.Parameters["@.CustomerName"].Value ="ALFKI";
cmd.Parameters.Add("@.CustomerName", SqlDbType.NVarChar, 50);
cmd.Parameters["@.CustomerName"].Direction = ParameterDirection.Output;

|||I tried that. Still no luck.|||Try writing
ParameterDirection.Returninstead ofParameterDirection.Output
and since the stored Procedure is returning only 1 value.... you can try using ExecuteScaler instead of ExecuteReader.......|||Please post the full text of your error message.|||Does your stored proc return the value when you run it in the query analyzer? Also try changing your @.CustID to nvarchar instead of nchar. char is fixed length so if the value you are passing is < the specified length SQL Server will padd it with spaces to make it the fixed with. So if you pass in CustID of C123 it will become @.CustID = 'C123 '|||

I should have written

http://forums.asp.net/thread/1631113.aspx

cmd.Parameters.Add("@.CusomerId", SqlDbType.NChar, 5);
cmd.Parameters["@.CustomerName"].Value ="ALFKI";
cmd.Parameters.Add("@.CustomerName", SqlDbType.NVarChar, 50);
cmd.Parameters["@.CustomerName"].Direction = ParameterDirection.Output;

retrieving data from database...

Hi all,

I am working on a project on PocketPC in which it is required to reteive data from database. I have created database on simulator as .sdf file. I want to retreive data from eVC++ code. How i can do so?

thanx

If you are trying to access a database using C++ code it sounds like the .Net Compact Framework isn't really involved. You might find better information in the Smart Devices Native C++ Development forum.

-Noah

.Net Compact Framework

Retrieveing objects

Dears,
I created a table, then dropped it by mistake. Is there any way to retrieve it back using the log file? The lat DB backup was made before creating the table.
Thanks,1. Take a log backup
2. make another database and restore the previous full backup over there as you would not want to make any changes to the original db
3. restore transaction log to a time before you dropped the table
4. copy table onto original database

tell me if you need any other help|||When applying the backup, through the Enterprise Manager, I had the "Point in time restore" option disabled. Have an idea why? Do you think making the backup through the T-SQL better?

Originally posted by Enigma
1. Take a log backup
2. make another database and restore the previous full backup over there as you would not want to make any changes to the original db
3. restore transaction log to a time before you dropped the table
4. copy table onto original database

tell me if you need any other help|||Question : What is your database recovery model

Simple , Full or Bulk Logged

Give me a list of the things you have done till now ...