return big string

Hello,
How can I return more then 8000 characters from a store proc.?
When I do this
CREATE PROCEDURE SP_GetClassCode
AS
SELECT 'very long string'
GO
It work's
But when I do this
CREATE PROCEDURE SP_GetClassCode
@.Replace AS VARCHAR(50)
AS
SELECT 'very long ' + @.Replace
GO
This time it only return the 8000 first characters
What do I have to do to return more then 8000 characters from a Store Proc?
Thank you
Marc R.Marc Robitaille wrote:
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
You could return the values as two or more columns and concatenate on
the client. The data type you are working with (char/varchar) is limited
to 8000 bytes.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Is there an other datatype that I could use to return what I need?
"David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
news: %235Wco9uhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Marc Robitaille wrote:
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
> You could return the values as two or more columns and concatenate on the
> client. The data type you are working with (char/varchar) is limited to
> 8000 bytes.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||How are you determining the length returned? Query Analyzer has a limit of
8000 bytes per column. And you should avoid using sp_ as a prefix to stored
procedures.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
>|||I try to build a VB.NET class with a Store proc. I specify the name of the
table to my SP then it return's a string. If I Copy/Paste the string in a
VB file, I have a fully fonctional class that represent my table. So, I have
writen a template that I use in my SP. My template has 38000 characters. In
some place, in my template, there are speacials words that are going to be
replace when the SP is execute. When I execute the SP without parameters, my
template is return entirely but with no modification in the template. When I
execute the SP with a parameter, only the 8000 first characters are return
with the modification. I don't run the SP in Query analyser but in a VB.NET
programm that I did. How can I return a big string with parameters?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
> How are you determining the length returned? Query Analyzer has a limit
> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
> stored procedures.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>|||It looks like SQL Server is implicitly converting your string to the
datatype of the variable you are concatenating with it. You can create a
table variable with a text column and build your string in the text column.
The issue a select from the table variable at the end to get the full
output.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I try to build a VB.NET class with a Store proc. I specify the name of the
>table to my SP then it return's a string. If I Copy/Paste the string in a
>VB file, I have a fully fonctional class that represent my table. So, I
>have writen a template that I use in my SP. My template has 38000
>characters. In some place, in my template, there are speacials words that
>are going to be replace when the SP is execute. When I execute the SP
>without parameters, my template is return entirely but with no modification
>in the template. When I execute the SP with a parameter, only the 8000
>first characters are return with the modification. I don't run the SP in
>Query analyser but in a VB.NET programm that I did. How can I return a big
>string with parameters?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
> news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a limit
>> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
>> stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>|||Great idea
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: eFA9CewhFHA.1052@.TK2MSFTNGP10.phx.gbl...
> It looks like SQL Server is implicitly converting your string to the
> datatype of the variable you are concatenating with it. You can create a
> table variable with a text column and build your string in the text
> column. The issue a select from the table variable at the end to get the
> full output.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>>I try to build a VB.NET class with a Store proc. I specify the name of
>>the table to my SP then it return's a string. If I Copy/Paste the string
>>in a VB file, I have a fully fonctional class that represent my table. So,
>>I have writen a template that I use in my SP. My template has 38000
>>characters. In some place, in my template, there are speacials words that
>>are going to be replace when the SP is execute. When I execute the SP
>>without parameters, my template is return entirely but with no
>>modification in the template. When I execute the SP with a parameter, only
>>the 8000 first characters are return with the modification. I don't run
>>the SP in Query analyser but in a VB.NET programm that I did. How can I
>>return a big string with parameters?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message
>> de news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a
>> limit of 8000 bytes per column. And you should avoid using sp_ as a
>> prefix to stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>>
>

Return all months within a range of dates

I currently have a stored procedure that returns a list of dates based on a date range a user enters.

CREATE PROCEDURE sp_GetContactScheduleDates
@.MonthFrom int,
@.YearFrom int,
@.MonthTo int,
@.YearTo int,
@.DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 >= CAST(cast(@.MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@.YearFrom as nvarchar) as smalldatetime)
 And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 <= CAST(cast(@.MonthTo as nvarchar) + '/' + cast(@.DaysInMonth as nvarchar) + '/' + cast(@.YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO

However, this only brings back those dates that are in the table. I need to get ALL dates within the range.

For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?

Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.

Can some sort of function be used? What would the code look like?I would create a table variable with one field that will hold the date. The do a loop to populate it. I'd make sure @.startdate and @.enddate have the time stripped off. Not tested, but should work with minor tweaks.

set @.date = @.startdate
set @.x = datediff(d, @.startdate, @.enddate)
set @.y = 0
While @.y <= @.x
 Begin
 insert into @.table (datefield) values (dateadd(d, @.y, @.startdate))
 set @.y = @.y + 1
 End

|||ooo that's a nice loop. :)

Return a value after insert the query

Hi!

create table testReturn
(
id int identity(100,1),
name varchar(10)
)

How can I return the value of identity column after inserting the value.

Dim objConn As SqlConnection
Dim SQLCmd As SqlClient.SqlCommand
Dim ds As New DataSet
Dim strsql As String

Try

objConn = New SqlConnection
objConn.ConnectionString = _
"Network Library=DBMSSOCN;" & _
"Data Source=localhost;" & _
"Initial Catalog=mydb;" & _
"User ID=userid;" & _
"Password=pass"

objConn.Open()

strsql = "insert into testReturn values ('a')"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)
Dim rowsAffected As Integer = 0
rowsAffected = SQLCmd.ExecuteNonQuery

Dim rv As String
rv = SQLCmd.Parameters(0).Value.ToString()
Response.Write(rv)

Catch ex As Exception
Response.Write(ex.ToString)
End Try

SeeHow do I get the IDENTITY / AUTONUMBER value for the row I inserted?

|||

strsql = "insert into testReturn values ('a'); select @.@.Identity"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)

dim Identiy as Object

Identity = SQLCmd.ExecuteScalar

|||

You need to put "Select @.@.identity" statement immediately after your insert query so it would return the ID value of the record inserted, using the preceding Insert statement.

Cheers
Ritesh

|||

Using @.@.IDENTITY is an vulnerable approach, since it returns the last inserted id of any table. If you are using SQL 2005 then you can use the OUTPUT clause.

SeeHow to get an Identity value with SQL Server 2005

|||

You can either use @.@.identity or scope_identity().

@.@.identity gives you the last generated identity value.

scope_identity() gives you the last generated identity value for current scope.

Just have a quick look at BOL for further understanding.

Hope this will help.

Return a UNIQUEIDENTIFIER

Hi,

I am writing a C# application that uses a SQL server database to hold its data. I need to create a stored procedure that returns a particular row's primary key value. This is no problem if the primary key is an INT. But my primary key is a unique identifier, and the stored procedure doesn't want to let me return any values that aren't INTs. Can someone please tell me how to get around this?

Thanks in advance.

ScottYou'll have to declare it as an outparameter.
And if you want something easier to handle you can convert it to
a varchar using CONVERT(myguid,VARCHAR)

Regards
Fredr!k|||Fredrik2000,

Thank you so much. That is exactly what I needed. Also, for anyone else out there, it is actually in the format:

CONVERT(VARCHAR(36), myguid)

where of course 36 is the number of characters allocated for the datatype.

Sc0tt|||Ahh, I always get the order mixed up (didn't have a copy of books online at the computer
I'm posting from...)

Nice to hear you got it working.

Regards
Fredr!k

Return a resultset from a Stored Pro

Hello all,
I want to be able to be able to return a resultset from a Stored Procedure.

Something like :

CREATE PROCEDURE LSNOnAJob
@.MyJobNo AS INT,@.MyLsn VarChar(10) OUTPUT

AS

SELECT @.MyLsn = dbo.TSample.ISmpShortCode
FROM dbo.TJob INNER JOIN
dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN
WHERE (dbo.TJob.IJobN = @.MyJobNo)
GO

I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.

I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.would this work for you or do you need the results returned in an output variable?

CREATE PROCEDURE LSNOnAJob
@.MyJobNo AS INT
--,@.MyLsn VarChar(10) OUTPUT

AS

SELECT dbo.TSample.ISmpShortCode
FROM dbo.TJob INNER JOIN
dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN
WHERE dbo.TJob.IJobN = @.MyJobNo
GO|||I need the results returned back to an Access DB|||Just use a pass through query and EXEC the sproc...

Return a Field from a User Function ?

Hello,
I am using SQL Server 2000 and I am wondering if it possible to create a
user fonction that return a field so I can use the return of the function in
a WHERE .
My original query I someting like this:
'=======================================
===
SELECT * FROM Table1
WHERE
case @.Workgroup
WHEN 1 THEN Table1.RouteQuart1
WHEN 2 THEN Table1.RouteQuart2
WHEN 3 THEN Table1.RouteQuart3
END = @.NumRoute
'=======================================
====
I want to create a function to remplace the CASE. This function would return
a field. And My new query would be :
'=======================================
===
SELECT * FROM Table1
WHERE
MyNewUserFunction = @.NumRoute
'=======================================
====
Is there a way to do this ? I the query will be more optimized ' If not
possible how to make my original query the most efficient?
Regards,
Gilles LabelleGilles Labelle,
Write three sps and call them from the main one.
create procedure dbo.p1
@.RouteQuart1 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart1 = @.RouteQuart1
return @.@.error
go
create procedure dbo.p2
@.RouteQuart2 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart2 = @.RouteQuart2
return @.@.error
go
create procedure dbo.p3
@.RouteQuart3 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart3 = @.RouteQuart3
return @.@.error
go
create procedure dbo.p4
@.Workgroup int,
@.NumRoute int
as
set nocount on
declare @.rv int
declare @.error int
if @.Workgroup = 1
begin
exec @.rv = dbo.p1 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 2
begin
exec @.rv = dbo.p2 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 3
begin
exec @.rv = dbo.p3 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
-- handle when the value of @.Workgroup is not 1, 2,3
end
end
end
return @.error
go
AMB
"Gilles Labelle" wrote:

> Hello,
> I am using SQL Server 2000 and I am wondering if it possible to create a
> user fonction that return a field so I can use the return of the function
in
> a WHERE .
> My original query I someting like this:
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> case @.Workgroup
> WHEN 1 THEN Table1.RouteQuart1
> WHEN 2 THEN Table1.RouteQuart2
> WHEN 3 THEN Table1.RouteQuart3
> END = @.NumRoute
> '=======================================
====
> I want to create a function to remplace the CASE. This function would retu
rn
> a field. And My new query would be :
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> MyNewUserFunction = @.NumRoute
> '=======================================
====
>
> Is there a way to do this ? I the query will be more optimized ' If not
> possible how to make my original query the most efficient?
>
> Regards,
> Gilles Labelle
>
>
>

retrieving selected join records

Hi,
I have the folowing 3 (SS2005) tables:

CREATE TABLE [dbo].[tblSubscription](
[SubscriptionID] [int] IDENTITY(1000000,1) NOT NULL,
[SubscriberID] [int] NOT NULL,
[Status] [int] NOT NULL,
[JournalID] [int] NOT NULL,

CREATE TABLE [dbo].[tblTransaction](
[TransactionID] [bigint] IDENTITY(100000000,1) NOT NULL,
[TransactionTypeID] [int] NOT NULL,
[SubscriptionID] [int] NOT NULL,
[Created] [datetime] NOT NULL,

CREATE TABLE [dbo].[tblMailing](
[MialingID] [bigint] IDENTITY(1000000000,1) NOT NULL,
[SubscriptionID] [int] NOT NULL,
[MailTypeID] [int] NOT NULL,
[MailDate] [datetime] NOT NULL

So for each subscription there can be 1 or more transactions and 0 or
more mailings, and the mailings are not necassarily related to the
transactions. What I am having difficulty doing is this:

I wish to select tblMailing.MailingID, tblMailing.MailDate,
tblMailing.SubscriptionID (or tblSubscription.SubscriptionID),
tblSubscription.SubscriberID, tblSubscription.Status,
tblTransaction.TransactionID, tblTransaction.Created, but I only wish
to retrieve rows from the transaction table where
tblTransaction.Created is the latest dated transaction for that
subscription.
I.E. (maybe this makes more sense..:) I wish to select all rows from
tblMailing along with each mailing's relevent subscription details,
including details of the LATEST TRANSACTION for each of those
subscriptions.

I am currently working along the lines of MAX(tblTransaction.Created)
and possibly GROUP BY in a subquery, but cannot quite figure out the
logic.

Any help appreciated.

Thanks, KoG

King:

Are you wanting the subscription record to appear in the report even if there are as of yet no mailings? That is, do I need to use an outer join or an inner join? I am for the moment assuming that you want the inner join.

Dave

|||

set nocount on
declare @.tblSubscription table
( subscriptionID integer not null,
subscriberID integer not null,
status integer not null,
journalID integer not null,
primary key (subscriptionID)
)

declare @.tblTransaction table
( transactionID integer not null,
transactionTypeId integer not null,
subscriptionID integer not null,
created datetime not null
primary key (transactionID),
unique (subscriptionID, transactionID)
)

declare @.tblMailing table
( mailingId bigint not null,
subscriptionID integer not null,
mailTypeId integer not null,
mailDate datetime not null,
primary key (mailingID),
unique (subscriptionId, mailingId)
)

insert into @.tblSubscription values (1000001, 1000001, 1, 1)
insert into @.tblSubscription values (1000002, 1000002, 1, 1)
insert into @.tblSubscription values (1000003, 1000001, 2, 1)
--select * from @.tblSubscription

insert into @.tblTransaction values (1000001, 1, 1000001, '3/15/6' )
insert into @.tblTransaction values (1000002, 2, 1000001, '4/7/6' )
insert into @.tblTransaction values (1000003, 1, 1000002, '4/3/6' )
insert into @.tblTransaction values (1000004, 1, 1000003, '5/8/6' )
insert into @.tblTransaction values (1000005, 2, 1000003, '10/14/6')
insert into @.tblTransaction values (1000006, 4, 1000003, '9/1/6' )
--select * from @.tblTransaction

insert into @.tblMailing values (1000001, 1000001, 1, '3/15/6' )
insert into @.tblMailing values (1000002, 1000001, 2, '4/4/6' )
insert into @.tblMailing values (1000003, 1000003, 1, '5/9/6' )
insert into @.tblMailing values (1000004, 1000003, 3, '9/3/6' )
--select * from @.tblMailing

--set statistics io on
select m.mailingId,
m.MailDate,
s.subscriptionId,
s.subscriberId,
s.Status,
t.TransactionId,
t.created
from @.tblSubscription s
inner join @.tblMailing m
on s.subscriptionId = m.subscriptionId
inner join
( select q.subscriptionId,
q.transactionId,
row_number () over
( partition by q.subscriptionId
order by q.created desc, q.transactionId desc
) as Seq,
created
from @.tblTransaction q
) t
on t.subscriptionId = s.subscriptionId
and seq = 1
--set statistics io off

-- -- Sample Output: -

-- mailingId MailDate subscriptionId subscriberId Status TransactionId created
-- -- -- -- -- - --
-- 1000001 2006-03-15 00:00:00.000 1000001 1000001 1 1000002 2006-04-07 00:00:00.000
-- 1000002 2006-04-04 00:00:00.000 1000001 1000001 1 1000002 2006-04-07 00:00:00.000
-- 1000003 2006-05-09 00:00:00.000 1000003 1000001 2 1000005 2006-10-14 00:00:00.000
-- 1000004 2006-09-03 00:00:00.000 1000003 1000001 2 1000005 2006-10-14 00:00:00.000

|||Hi Dave,

I only wish to select subscription rows where there is a mailing associated with the subscription. In fact, the driver of the query should be the mailings table, so for each row in tblMailing get the relevent subscription (& latest transaction) data. That means there may be several rows where the data in the subscription-related columns (& hence transaction related ones too) are the same, as a subscription may have several mailings.

I assume that means the inner join is required..

Thanks, Nick
sql

Retrieving records within an index range, the nth record?

if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?

Possible? How can I do it?

When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?

I'm using SQL 2005 Express, not SQL 2000.if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?
Create index and schedule update statistics according to your requirement...

Use comparison operator for retrieving desired data.

i.e. Like, Between, Not Between etc.

When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?
Use clustered and non clustered index considering your needs.

Indexes are for arranging, sorting & fast retrieval of the data. Each time you don't need to create index when you insert a row, just schedule update statistics job or set auto update statistics.

Explore Books OnLine (From query analyzer -> Help Menu) for more information.|||how do we include index as a criteria when we use normal SQL query?|||how do we include index as a criteria when we use normal SQL query?
You don't need to do such thing, SQL Server will do it for you...|||after some research, i think it's easier for me to insert row_number() into the table instead of using index. What do you think?|||Read documents / books (rather get some knowledge) before making any changes...|||Data in a relational database has no inherent order.
Why do you think you need to add a rownumber column?

There are several ways to get a "page" or "range" of records from a table. Here is one:

select top 5 *
from
(select top 10 *
from [YourTable]
order by [YourColumn] asc) Subquery
order by [YourColumn] desc

You should not be relying on the concept of a "row number"|||this is for a particular case to generate rigid report using SQL 2005.

I need the output to be in the right order in my control, and because there is headers and footers involved, I got no choice but to fix them in certain special order.

the output is to an excel spreadsheet.|||So throw an ORDER BY clause into your query.

If you want the data to be ordered according to the way it was entered, then use a datetime column to record the entry date.|||Order by cannot work without row_number. I have too many identical rows.

Entry date is not accurate as the time unit used by SQL 2005 is not small enough.|||Then use an Identity column.|||unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.|||You're not listening...|||You can also use temporary table to process desired request in memory, rather to store in table permanently,
try just like this:

SELECT ROWID=IDENTITY(int,1,1) , Col1
INTO #TempTable FROM
<UrTable List and Where Clause>

and then retrieve from Temporary table, it will save ur time, disk space and locking issues on underlying table.

--Riaz

unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.|||it will save ur time, disk space and locking issues on underlying table.
Dunno about time but this will use more disk space than blindman's query (temp tables are not held in memory but written to tempdb) and will lock tempdb while it runs (this is due to the "select into" bit).

You can use the OVER clause if you are really eager to use row_number().

SELECT *
FROM--Derived TABLE - numbering rows
(SELECT *
, ROW_NUMBER() OVER (ORDERBY my_unique_column ASC) AS rn
FROM dbo.MyTable) AS der_t
WHERE rn BETWEEN 5 AND 10

Retrieving Parameter List for a Stored Procedure

Is there a way to retrieve the parameter list for a given stored procedure?

I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.

Thanks,
Allen K.sp_sproc_columns @.Procedure_Name='procedurename'

Column_Name : Type_Name : Precision : Length : Scale : Is_Nullable

Retrieving Mutiple rows

I have a table like this.

Depositors Table

Value(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Please Can anyone write a simple query for this?

Thanks

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select top 100 sum(grandtotal),

case when saledate = dateadd("d",-1,dateadd("month",0,'07/20/2007')) then 'yesterday' --cast (saledate as nvarchar(30))

when saledate < dateadd("d",-1,dateadd("month",0,'07/20/2007')) and saledate >= dateadd("d",-7,dateadd("month",-1,'07/20/2007')) then 'Last 7 days'

when saledate < dateadd("day",-1,dateadd("month",-1,'07/20/2007')) and saledate >= dateadd("day",-2,dateadd("month",-3,'07/20/2007')) then 'Last month'

when saledate < dateadd("day",-2,dateadd("month",-3,'07/20/2007')) and saledate >= dateadd("d",-1,dateadd("year",-2,'07/20/2007')) then 'Last 1 year'

else '...'

end , saledate

from sale group by saledate order by saledate desc

select sum(amount), duration from @.temptable group by duration order by max(date) desc

Bad formatting but query works..

I checked it..

in my database i have old date so i need to use old date.. but you can use today's date..

|||

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

|||

shamen wrote:

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate = dateadd("d",-1,GETDATE()) then 'yesterday'

when Startdate between dateadd("d",-1,GETDATE()) and dateadd("d",-7,GETDATE()) then 'Last 7 days'

when startdate between dateadd("d",-7,GETDATE()) and dateadd("d",-30,GETDATE()) then 'Last month'

Else 'ABC'
end as duration, startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

May it works now. For testing purpose always keep default value so atlease you can know that condition is going where

Retrieving Mutiple rows

I have a table like this.

Depositors Table

Value(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Please Can anyone write a simple query for this?

Thanks

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select top 100 sum(grandtotal),

case when saledate = dateadd("d",-1,dateadd("month",0,'07/20/2007')) then 'yesterday' --cast (saledate as nvarchar(30))

when saledate < dateadd("d",-1,dateadd("month",0,'07/20/2007')) and saledate >= dateadd("d",-7,dateadd("month",-1,'07/20/2007')) then 'Last 7 days'

when saledate < dateadd("day",-1,dateadd("month",-1,'07/20/2007')) and saledate >= dateadd("day",-2,dateadd("month",-3,'07/20/2007')) then 'Last month'

when saledate < dateadd("day",-2,dateadd("month",-3,'07/20/2007')) and saledate >= dateadd("d",-1,dateadd("year",-2,'07/20/2007')) then 'Last 1 year'

else '...'

end , saledate

from sale group by saledate order by saledate desc

select sum(amount), duration from @.temptable group by duration order by max(date) desc

Bad formatting but query works..

I checked it..

in my database i have old date so i need to use old date.. but you can use today's date..

|||

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

|||

shamen wrote:

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate = dateadd("d",-1,GETDATE()) then 'yesterday'

when Startdate between dateadd("d",-1,GETDATE()) and dateadd("d",-7,GETDATE()) then 'Last 7 days'

when startdate between dateadd("d",-7,GETDATE()) and dateadd("d",-30,GETDATE()) then 'Last month'

Else 'ABC'
end as duration, startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

May it works now. For testing purpose always keep default value so atlease you can know that condition is going where

Retrieving multiple values from one field in SQL Server for use in multiple columsn in Reports

I am trying to create a report using Reporting Services.

My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.

I can currently get one value but how to get the information I need to be able to use in my reports.

So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.

Any help would be appreciated.

Thanks.

I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.

What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report

As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.

As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem

Thanks for the suggestions that were made, I apoligize for not making the problem clearer.

Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.

--Pulls the Service Opportunity

SELECT cs.value AS "Service Opportunity"

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE ca.name = 'Service Opportunity'

--Pulls the Number of Hours

SELECT cs.value AS 'Number of Hours'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Num of Hours'

--Pulls the Person Grade Level

SELECT cs.value AS 'Grade'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Grade'

--Pulls the Person Number, First and Last Name and Grade Level

SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"

FROM student s

INNER JOIN cperson cs ON cs.personid = s.personid

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE cs.value =(SELECT cs.value AS 'Grade'

WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')

There are a number of ways to solve this. Here are a few options:

If each value is in its own row, like this:

Name Value

Hours 100

Type AAA

Grade C

the SQL Pivot statement can be used in your query to pivot the rows to columns, so the result looks like:

Hours Type Grade

100 AAA C

If the values are concatenated in a single column, like:

Column

100;AAA;C

then you can use the Split VB function in the report to split the string into its component parts, and put them into fields. To get the values, you can use the following:

=Split(Fields!Column.Value, ";")(0)

=Split(Fields!Column.Value, ";")(1)

=Split(Fields!Column.Value, ";")(2)

|||

It is quite clear from your post that you have all the data in one column in a database table. If you have access to the query or the stored procedure, try to accomplish this in the SQL query level itself because calculating it in reporting service is costlier than doing it in the query level (better if there is a stored procedure becasue it is compiled and faster). Try using this sql query (assuming that your field separator is "," and the order is hours, type and then grade):

CASE WHEN CHARINDEX(',', Column1) > 0

SUBSTRING(Column1, 1, CHARINDEX(',', Column1)-1) AS 'Hours'

END,

CASE WHEN CHARINDEX(',', Column1) > 0

CASE WHEN CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1))) > 0

SUBSTRING(Column1, CHARINDEX(',', Column1)+1, CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1)))-1) AS 'Type'

END

END,

CASE WHEN CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1))) > 0

RIGHT(Column1, CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1)))+1) AS 'Grade'

END

If your still want to use reporting services, create 3 calculated dataset fields as follows:

Hours: IIf(Split(Fields!Column1.Value, ",").UpperBound>=0, Split(Fields!Column1.Value, ",")(0), "")

Type: IIf(Split(Fields!Column1.Value, ",").UpperBound>=1, Split(Fields!Column1.Value, ",")(1), "")

Grade: IIf(Split(Fields!Column1.Value, ",").UpperBound>=2, Split(Fields!Column1.Value, ",")(2), "")

Shyam

|||

John,

Thanks for the suggestion. I guess I didn't make myself very clear on to what I was attempting. I modified my thread and enclosed a sample of what I am attempting to do.

Thanks again for the help

|||

Shyam,

Thanks for the advice. I guess I didn't make myself clear enough on what I was attempting to do. I have modifed my thread and enclosed a sample of the code I am trying to make work.

Thanks again

|||

Use the following query to get all values at one shot:

SELECT cs1.value AS 'Service Opportunity', cs2.value AS 'Number of Hours', cs3.value AS 'Grade'

FROM Cstudent cs1

INNER JOIN cattribute ca ON ca.attributeid = cs1.attributeid

AND ca.name = 'Service Opportunity'

INNER JOIN Cstudent cs2 ON ca.attributeid = cs2.attributeid

AND ca.name = 'Num of Hours'

INNER JOIN Cstudent cs3 ON ca.attributeid = cs3.attributeid

AND ca.name = 'Grade'

Thanks,

Shyam

|||

Shyam,

I've been working with what you suggested but so far with no luck. It pulls the column headings but there is no information in the columns. I'm looking to see if I'm missing something somewhere.

Thanks for the advice though, I'll keep working at it and see what I can come up with.

Thanks

Wayne

|||

Maybe not all records are available in Cstudent, so use this query:

SELECT cs1.value AS 'Service Opportunity', cs2.value AS 'Number of Hours', cs3.value AS 'Grade'

FROM cattribute ca

LEFT OUTER JOIN Cstudent cs1

ca ON ca.attributeid = cs1.attributeid

AND ca.name = 'Service Opportunity'

LEFT OUTER JOIN Cstudent cs2 ON ca.attributeid = cs2.attributeid

AND ca.name = 'Num of Hours'

LEFT OUTER JOIN Cstudent cs3 ON ca.attributeid = cs3.attributeid

AND ca.name = 'Grade'

Shyam

|||

Shyam

Thanks for the help, Just have to do some more tweaking on my end but looks like it might give me what I'm looking for.

Thanks Again for the Help

Wayne

|||

So, can you mark the post as answer?

Shyam

|||

Shyam

Thanks for all the help. This was the first post I had done one here so I apoligize for that. It's taken care of and I posted it answered.

Retrieving excle schema

HI all,
How can I retrieve the schema of set range froman excle spreadsheet. The
range has the valid data that I need inorder to create a staging table, But
once the staging table is created I do not want to recreate it everytime
with the select into, as i am setting indexes and triggers on this staging
table. Once the staging table is created it would mostliley be used from
that point on/
Thanks
RobertAfter you've created the staging table using SELECT...INTO script it out
using your favourite editing tool, then in your script replace the
SELECT...INTO statement with a CREATE TABLE and an INSERT...SELECT statemens
.
On scripting out SQL objects' DDL:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/

Retrieving @@Identity

Hi,
I have the following proc:
CREATE PROCEDURE SP_CadastraPessoaFisica
(
@.Email Varchar(60) = '',
@.Senha Varchar(10) = '',
@.Nome Varchar(50) = '',
@.Sobrenome Varchar(50) = '',
@.DataNascimento DateTime,
@.Sexo Char(1),
@.CPF Varchar(12)
)
As
Set Nocount On
Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
GO
Simple.Isn't it ? So...I need to retrieve the ID from this insert command
when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
use @.@.Identity!
--
Thanks in advance,
Daniel GrohDaniel
If you are using SQL Server 2000 you can use SCOPE_IDENITY() function
INSERT INTO Table (col) VALUES (1)
SELECT SCOPE_IDENITY()
Note : there is a difference between @.@.IDENTITY and SCOPE_IDENTITY() which
may bring unexpected result
See BOL for details
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>|||Please don't multipost ,it is answered in .programming
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>|||Hi
You can use this:
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
This will help you solve the problem
thanks and regards
Chandra
"Daniel Groh" wrote:
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>
>

retrieving >1000 records from AD into Crystal

Hello all,

I am having a couple of problems selecting records from Active Directory. What I want to do is create a report that is grouped on a user object field in AD. Our users are not just contained with the 'Users' container, but also in other areas of the directory.

I've come across the problem that AD will only return the first 1000 records when you query it (mentioned here: http://support.businessobjects.com/library/kbase/articles/c2013533.asp). I believe you can get around this by somehow specifying the 'range' property, however I'm not 100% sure how to do this. This is my query as it stands:

Select displayName, ExtensionAttribute3, ExtensionAttribute2,
sAMAccountName, objectClass FROM 'LDAP://dc=blah,dc=blah2,dc=blah3,dc=blah4;;;Range=0-1000;subtree' WHERE objectClass='user'

Whenever I click OK to this I get the error "An invalid directory pathname was passed".

I guess I actually have 2 questions:
1. How do you get the range property to work (i.e. how can I return more than 1000 rows)
2. How can I get the query to search the subtrees of the directory (I think you need to specify the 'subtree' keyword, but again, this isn't working in my query.

Any help would be appreciated!
Cheers,
DanielIf you dont solve the problem search at http://support.businessobjects.com/

Retrieve values from child table

Hello there,

I need to get the last value (status) from a child table. I try to simplify the problem with the following example.

Create Table Users
(
UserId int,
Lastname nvarchar(50)
)

Create Table UserStatus
(
UserId int,
Date datetime,
StatusId int
)

Create Table Status
(
StatusId int
Status nvarchar(50)
)

A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.
Now I want to know which users are in progress (2) but a simple select statement like:

Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2

Will not return the wanted records because all authorized Users have been in this status.

I hope you understand the problem and can help me out.

Thx in advance.

Etinuzso you want the last names of users that stopped at status 2?

Select Users.LastName From Users Inner Join (Select Max(StatusID) As MaxStatusID, UserID, UserStatus UserID) As LimitedStatus On Users.UserID = LimitedStatus.UserID WHERE MaxStatusID = 2

Or something to that effect.|||Thx KraGIE, i suppose this is the right direction. Only the values are not in a specific order (and can not be set this way). So the example was not right.
So a user can be Authorized (3) and later set to In progress (2). However in the table there is a ID.

Create Table UserStatus
(
Id int, (identifier)
UserId int,
Date datetime,
StatusId int
)

At this moment I havent figured out how to use

Select Users.LastName From Users Inner Join (Select Max(ID) As MaxID, UserID, StatusId From UserStatus Group BY USerId, StatusId) As LimitedStatus On Users.UserID = LimitedStatus.UserID WHERE StatusID = 2|||I found my solution

SELECT
u.LastName
FROM
Users u
JOIN UserStatus us
ON u.UserId = us.UserId
JOIN
(SELECT UserId, MAX([Date]) AS last_date
FROM UserStatus
GROUP BY UserId ) AS last_status
ON u.UserId = last_status.UserId
AND us.[Date] = last_status.last_date
WHERE
us.StatusId = 2

Retrieve the Identifier of the newly added row

Howdie y'all,

I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one...

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32)
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
GO

I actually would like to get value of the UserId column for the newly added record.

Can anyone of you folks help me with this?

Cheers,

Wes

UserID being a primary key and identity column? For example modifying the proc as follows

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32),
@.NewID int OUTPUT
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
SET @.NewID = SCOPE_IDENTITY()
GO

This way you can get it via output parameter after the query is executed.

|||

Thank for this fast answer! It works like a charm now!

Cheers,

Wes

|||And to tag onto Teemu's response...
You might find a function called @.@.IDENTITY that seems to return thesame kind of information. Don't be tempted to use it, however. Itreturns the last identity value added with a wider scope thanSCOPE_IDENTIY(). It's appropriate only in very rare situations, andnever in the scenario you are using.
Don
|||

Funny you mention the @.@.Identity... I first tried that. I found it on msdn but for some reason I didn't trust it so I decided to ask the question to people who know what they are doing instead of just copy paste the code.

I'll start my mcsd course soon now and will follow the path of sql-server. I think I'll start a website where people can get extensive info on how to create and enter stored procedures on sql-server to become an MVP. I've found it hard to get some good information. I see a lot of differences in approach and syntax( should I use OUTPUT or just OUT or are they the same?) and no real good tutorial on where to start and how to learn with explanation.

Thanks again you both,

Wesley

retrieve records affected count from ADO?

Hello,

If I run an action SP from MS Access using ADO:
...
cmd.execute

where the SP is something like Create...
Update tbl1 set fld1 = 'something' where...

how can I retrive the count of records affected like from Query
analyzer?

Thanks,
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Rich Protzel (rpng123@.aol.com) writes:
> If I run an action SP from MS Access using ADO:
> ..
> cmd.execute
> where the SP is something like Create...
> Update tbl1 set fld1 = 'something' where...
> how can I retrive the count of records affected like from Query
> analyzer?

The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.

>>The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.
<<

May I ask how I go about retrieving the Count of records affected back
into MS Access?

Dim CountRecsAffected As Long
...
cmd.Parameters("@.bDate").Value = sDate
cmd.Execute
CountRecsAffected = cmd.?
or
CountRecsAffected = ?
or
CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
the count of parameters being used?

Thanks again for your reply.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Rich Protzel (rpng123@.aol.com) writes:
> May I ask how I go about retrieving the Count of records affected back
> into MS Access?
> Dim CountRecsAffected As Long
> ..
> cmd.Parameters("@.bDate").Value = sDate
> cmd.Execute
> CountRecsAffected = cmd.?
> or
> CountRecsAffected = ?
> or
> CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
> the count of parameters being used?

cmd.Execute CountRecsAffected

Assuming that Access works like Visual Basic.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 3 Aug 2003 16:08:14 +0000 (UTC) in
comp.databases.ms-sqlserver, Erland Sommarskog <sommar@.algonet.se>
wrote:

>Assuming that Access works like Visual Basic.

For most things, including ADO, it does.

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)|||Thank you all for your replies. I think I get the idea now about how to
retrieve the count of records affected from an action sp.

One more question if I may:

If I set my sp to

SET NOCOUNT ON

would that improve the performance of my sp? It is not critical for me
to retrieve the count of records affected, mostly just a check. But if
the sp works consistently, and setting

SET NOCOUNT ON

significantly improve performance, then maybe I should consider that.
Most of my action sp's are affecting over 100,000 records of tables with
nearly 200 fields (no redundant fields) with over 1,000,000 records.

Thanks again,

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Retrieve ID of Last Insert (Scope_Identity)

(Newbie) Hi, I am trying to create in a session variable, the ID of the last inserted record. My reading suggests I should use Scope_Identity. I'm having trouble with the syntax/code structure. Also, is it good programming practise to directly assign the session variable e.g. "Session[var]=SqlDataSource.Select()"? The error I'm getting from my code below is "No overload for method SELECT takes 0 arguments". Thanks.

Session["snCoDeptRowID"] = SqlDataSource1.Select();

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT Scope_Identity"

</asp:SqlDataSource>

try

SelectCommand="SELECT Scope_Identity()"

|||

Thank you for the suggestion. Now I am having trouble with the following line of code which assigns the Scope_Identity() to a session variable. The error msg is: does not recognise the word "command". How can I get the value of the @.CoDeptRowID (Scope_Identity) into my session variable? Thanks

Session["snCoDeptRowID"] = Convert.ToInt32(e.command.parameters("@.CoDeptRowID").value);

|||

Check thate argument has a command object

only if its there in event argument you will be able to use it. And Check the Direction of the @.CoDeptRowId is set to Output in Command as well

|||

Hi,

There are many ways to get the SCOPE_IDENTITY() from stored procedure.

You can use SELECT SCOPE_IDENTITY() or RETURN SCOPE_IDENTITY() or SET @.PARAM = SCOPE_IDENTITY(). All is fine, but the way to get it is different.

SELECT will return the SCOPE_IDENTITY() as the first col and first row of a result set. RETURN will return is as a return parameter. While SET will set the value to a parameter and you have to specify it as an OUTPUT parameter when declaring it.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

retrieve from db then write to text file

basically i am trying to create a program wherein after saving a new transaction to the sql database, the fields saved will be retrieved and then written to a text file.

i read a thread here which is similar to what i am trying to do but it was in xml format..

hope someone anwers me...i really need help!

thanks!

I'm not sure what the difference is between the thread you mentioned and what you want to do. I would expect that the challenging thing is to retrieve the fields that were just saved. Once you have the information, changing it to the appropriate format is potentially tedious, but not difficult.

Do you have a reference to the thread?