Determine what version of RS is insalled

How do you determine what version of Reporting Services is installed int the DB and Web server ( I have the DB on a different server than the Web server)

Thanks

Try typing
http://localhost/Reportserver

Determine next order id

I'm working on a sproc that determines the next order id for a specified customer. The table has

custid int,

ordernum varchar(10)

Data is:

1000, 1000-001

1000, 1000-002

1001, 1001-001

1000, 1000-003

I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet).

I honestly don't know where to begin.

Can someone please help?

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(OderNumber,3) + 1 AS VARCHAR(6)),3)
FROm Orders
Order by OrderNumer
Where CustId = @.CustId

SELECT @.LASTOrdernNum = ISNULL(@.LASTOrdernNum,'001')

SELECT CAST(CustId as VARCHAR(4)) + '-' + LASTOrdernNum

Should b something like the above (untested).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

That was very close. Thanks very much.

Is there a way to return a string value? I won't be calling this SP with ExecuteScalar, so I'd like to RETURN the value instead of just selecting it.

Here's the working version:

Code Snippet

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

I can use RETURN on the last line instead of select, but my test code errors with:

Conversion failed when converting the varchar value '10052-002' to data type int.

|||

RETURN allows for only an integer operand, which is generally used as a status or return code.

You may want to consider creating a function rather than a procedure.

|||

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

@.retval varchar(255) output

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT @.retval = CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

sql

Design help

I have two tables Publications and TM_Pub.

CREATE TABLE [dbo].[Publications] (
[Pub_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Pub_Pres] [int] NULL ,
[Title] [nchar] (200) COLLATE Latin1_General_CI_AI NULL ,
[Authors] [char] (100) COLLATE Latin1_General_CI_AI NULL ,
[Location] [char] (50) COLLATE Latin1_General_CI_AI NULL ,
[Publication] [char] (150) COLLATE Latin1_General_CI_AI NULL ,
[Pub_date] [datetime] NULL ,
[Pres_enddate] [datetime] NULL ,
[Pres_Desc] [text] COLLATE Latin1_General_CI_AI NULL ,
[Pub_detail] [char] (20) COLLATE Latin1_General_CI_AI NULL ,
[keywords] [text] COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[TM_PUB] (
[TM_PUB_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Pub_ID] [int] NULL ,
[TM_ID] [int] NULL
) ON [PRIMARY]

Each Pub_id can have multiple TM_id's. But I want to ensure that I do not create any duplicate Pub_id; TM_id records.

The user will be looking at an indivdual Publication record (Pub_id) along with a all the available TM_id's, with the TM_id's currently assigned to the Pub_id selected. The user will be able to select or unselect TM_ids for the Pub_id.

I am having trouble figuring out how to take the users selection and insert new records in the TM_Pub table if they do not already exist as well as delete records if the do exist. I plan to send my sproc two parameters @.pub_id and @.TMidstring.

I am looking for ideas on how best to accomplish this.

Thanks.Hi

If I've understood your problem, why don't you just use Pub_ID and TM_ID as a composite key? Like that you won't be able to have any duplicates.

In the case of a junction table like this, I would delete all the old records for a particular Pub_ID or TM_ID, and insert all the new ones, so that you don't have to check each time if they already exist.

Does that help or am I way off?!

A.