Getting Job Info

Hi,
Is there a stored procedure which will return all jobs on a server along
with their current status (I am especially interested in getting back the
Last Run Status)? If not, does anybody have a query that would do this?
I need to run this on SQL Server 7 and 2000.
Thanks,
JamesCheck out sp_help_Job in BooksOnLine.
Andrew J. Kelly SQL MVP
"JW" <nospam@.hotmail.com> wrote in message
news:uwDnmtPNEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there a stored procedure which will return all jobs on a server along
> with their current status (I am especially interested in getting back the
> Last Run Status)? If not, does anybody have a query that would do this?
> I need to run this on SQL Server 7 and 2000.
> Thanks,
> James
>

Getting Job Info

Hi,
Is there a stored procedure which will return all jobs on a server along
with their current status (I am especially interested in getting back the
Last Run Status)? If not, does anybody have a query that would do this?
I need to run this on SQL Server 7 and 2000.
Thanks,
JamesCheck out sp_help_Job in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"JW" <nospam@.hotmail.com> wrote in message
news:uwDnmtPNEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there a stored procedure which will return all jobs on a server along
> with their current status (I am especially interested in getting back the
> Last Run Status)? If not, does anybody have a query that would do this?
> I need to run this on SQL Server 7 and 2000.
> Thanks,
> James
>

Getting JDBC metadata for synonyms

Using the Latest JDBC Driver from SQLExpress I'm attempting to get use the getTable() method to get information about database objects that the user can access/alter. Works fine for for tables and views, but can't seem to get any information returned for synonyms.

Is it possible to get information for synonyms, e.g. column definitions? or am I'm missing some setting in the connection.

Any Help would be appreciated!

Dave.

This a known issue and is being tracked by the SQL Server team. Can you describe the scenario for which you are usign synonyms? Are you able to owrk-around by using the table or view name?

|||Thanks for your reply,

The particular scenario we have is that we are developing a tool to create a Web Application from the database object definitions. We therefore, have no control over how the user has constructed his database and whether synonyms are used. We could get the data from the synonyms system table, but that would go against the design approach we have used. I will say that, unlike in Oracle, there seems to be no good reason to use synonyms in SQL Server at all, except possibly to enable the user to access different tables/views without changing code, which in my opinion, is a somewhat dubious practise anyway.

Dave

Getting JDBC metadata for synonyms

Using the Latest JDBC Driver from SQLExpress I'm attempting to get use the getTable() method to get information about database objects that the user can access/alter. Works fine for for tables and views, but can't seem to get any information returned for synonyms.

Is it possible to get information for synonyms, e.g. column definitions? or am I'm missing some setting in the connection.

Any Help would be appreciated!

Dave.

This a known issue and is being tracked by the SQL Server team. Can you describe the scenario for which you are usign synonyms? Are you able to owrk-around by using the table or view name?

|||Thanks for your reply,

The particular scenario we have is that we are developing a tool to create a Web Application from the database object definitions. We therefore, have no control over how the user has constructed his database and whether synonyms are used. We could get the data from the synonyms system table, but that would go against the design approach we have used. I will say that, unlike in Oracle, there seems to be no good reason to use synonyms in SQL Server at all, except possibly to enable the user to access different tables/views without changing code, which in my opinion, is a somewhat dubious practise anyway.

Dave

Getting Items that were ordered alone

Hello Experts. You may have more luck at this than me.

I am interested in finding the quantity of items that were ordered alone. I have an orderid field and a product field. So the count of the orderid has to equal one and the have them grouped by product.

Example of how data looks like

I am looking for transactions like orderid 3 and 5.

OrderID Product 1 hotdog 1 burger 1 taco 2 burrito 2 snack 2 chips 3 burger 4 hotdog 4 burger 4 taco 5 burrito 6 snack 6 chips

When i run

SELECT product,count(orderid)

From Table

Where BusinessDateID = 20060725

groupby product

having(count(orderid)=1)

I only get back items that were only sold once.

I am looking for a result that looks like this

Product Ordered alone hotdog 2 burger 3 taco 4 burrito 32 snack 12 chips 76

This seems to provide your desired results:

Code Snippet

SET NOCOUNT ON

DECLARE @.Orders table
( RowID int IDENTITY,
OrderID int,
Product varchar(20)
)

INSERT INTO @.Orders VALUES ( 1, 'hotdog' )
INSERT INTO @.Orders VALUES ( 1, 'burger' )
INSERT INTO @.Orders VALUES ( 1, 'taco' )
INSERT INTO @.Orders VALUES ( 2, 'burrito' )
INSERT INTO @.Orders VALUES ( 2, 'snack' )
INSERT INTO @.Orders VALUES ( 2, 'chips' )
INSERT INTO @.Orders VALUES ( 3, 'burger' )
INSERT INTO @.Orders VALUES ( 7, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'hotdog' )
INSERT INTO @.Orders VALUES ( 4, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'taco' )
INSERT INTO @.Orders VALUES ( 5, 'burrito' )
INSERT INTO @.Orders VALUES ( 6, 'snack' )
INSERT INTO @.Orders VALUES ( 6, 'chips' )

SELECT
Product,
Count = sum( ProdCount )
FROM (SELECT
Product = max( Product ),
ProdCount = count( Product )
FROM @.Orders
GROUP BY OrderID
HAVING ( count( OrderID ) = 1 )
) dt
GROUP BY Product

Product Count
-- --
burger 2
burrito 1

That returns the total of OrderID's 3, 5, 7 -all singleton purchases.

|||

Thank you for the quick response.

I am having trouble executing the query above. Is there away to get around inserting all those entries?

Right now i can get all of the Orderid's that had only one product. I can't figure out how to attach the product next to the orderid. To get the orderid's with only one product i did

SELECT orderid

From dbo.vFactOrderitemDetail

Where BusinessDateID = 20060724

GROUPBY orderid

having(count(orderid)=1)

I get a list that looks like this.

6072401121160951
6072473174424676
6072435021178729
6072409154427790
6072408391169292
6072483063312181
6072471601109909
6072437871152147
6072490014417771

I want to have the product next to the orderid on this list how would i do that?

|||

Davy579124 wrote:

I am having trouble executing the query above.

What kind of trouble are you having? (I think that the code will run exactly as it is -all you have to do is 'cut and paste' into a query window.)

Davy579124 wrote:

Is there away to get around inserting all those entries?

All those 'entries' is a table of sample data -since you didn't provide any to work with.

Davy579124 wrote:

Right now i can get all of the Orderid's that had only one product. I can't figure out how to attach the product next to the orderid.

The best way is to examine the sample query, with the sample data that I posted.

Then when you understand what the query does, substitute your table name and your column names in the query.

You asked how to get a list of singleton Orders/Products, and the number of times ordered.

That is the solution I provided -which answered the question you posted.

Now like you have 'revised' the requirements.

That's ok, but we can't help you get something you don't tell us about.

So, looking at the query I posted, you will see that there is a sub-query.

The sub-query can be altered to return just the OrderID (instead of Product, and Count).

Then the 'outer' query can JOIN the @.Orders table with the sub-query (as a derived table) and return the Product for that OrderID.

Somewhat like this: (using the same sample data...)

Code Snippet

DECLARE @.Orders table
( RowID int IDENTITY,
OrderID int,
Product varchar(20)
)

INSERT INTO @.Orders VALUES ( 1, 'hotdog' )
INSERT INTO @.Orders VALUES ( 1, 'burger' )
INSERT INTO @.Orders VALUES ( 1, 'taco' )
INSERT INTO @.Orders VALUES ( 2, 'burrito' )
INSERT INTO @.Orders VALUES ( 2, 'snack' )
INSERT INTO @.Orders VALUES ( 2, 'chips' )
INSERT INTO @.Orders VALUES ( 3, 'burger' )
INSERT INTO @.Orders VALUES ( 7, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'hotdog' )
INSERT INTO @.Orders VALUES ( 4, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'taco' )
INSERT INTO @.Orders VALUES ( 5, 'burrito' )
INSERT INTO @.Orders VALUES ( 6, 'snack' )
INSERT INTO @.Orders VALUES ( 6, 'chips' )

SELECT
o.OrderID,
o.Product
FROM @.Orders o
JOIN (SELECT OrderID
FROM @.Orders
GROUP BY OrderID
HAVING ( count( OrderID ) = 1 )
) dt
ON o.OrderID = dt.OrderID
ORDER BY o.OrderID

OrderID Product
-- --
3 burger
5 burrito
7 burger

|||

Many Thanks!

So instead of

INSERT INTO @.Orders VALUES ( 1, 'hotdog' )
INSERT INTO @.Orders VALUES ( 1, 'burger' )
INSERT INTO @.Orders VALUES ( 1, 'taco' )
INSERT INTO @.Orders VALUES ( 2, 'burrito' )
INSERT INTO @.Orders VALUES ( 2, 'snack' )
INSERT INTO @.Orders VALUES ( 2, 'chips' )
INSERT INTO @.Orders VALUES ( 3, 'burger' )
INSERT INTO @.Orders VALUES ( 7, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'hotdog' )
INSERT INTO @.Orders VALUES ( 4, 'burger' )
INSERT INTO @.Orders VALUES ( 4, 'taco' )
INSERT INTO @.Orders VALUES ( 5, 'burrito' )
INSERT INTO @.Orders VALUES ( 6, 'snack' )
INSERT INTO @.Orders VALUES ( 6, 'chips' )

I can replace it with a table? I have 2 tables for orderid and product.(dbo.FactOrderItemDetail and dbo.DimProductHierarchy)

Can i use a view? "vFactOrderitemDetail"

Thanks

Davy

|||Yes, you can use your own tables, and you may be able to use a view as well.|||I got it to work! Thanks for your help

Getting it right ! - Enterprise manager

A little question to make sure I get it right.
You cannot update records using Enterprise manager!
Or can you?You can, but shouldn't. Use Query Analyzer.
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:90B9EC08-D75F-461B-B40B-3E02EC9BE352@.microsoft.com...
>A little question to make sure I get it right.
> You cannot update records using Enterprise manager!
> Or can you?|||Hi,
NO. We can update records using EM.
open the rows and then edit the records
Regards,
Herbert
"benoit" wrote:

> A little question to make sure I get it right.
> You cannot update records using Enterprise manager!
> Or can you?|||Yes you can.
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:90B9EC08-D75F-461B-B40B-3E02EC9BE352@.microsoft.com...
>A little question to make sure I get it right.
> You cannot update records using Enterprise manager!
> Or can you?

Getting Internal SQL Server error. with SP4

Hi !
We recently changed server to a SQL 2000 SP4 on Windows 2003 (Old was SP3
with Windows 2000)
Since the server change we've had Internal SQL Server error on a few Stored
procedures.
Server: Msg 8624, Level 16, State 1, Line 5
Internal SQL Server error.
We were able to create this script that can be run on any test environment.
Notes: Index View must have at least 3 index + clustered index
Header Table rerquires 2 Foreign Keys
Details also requires 2 Foreign Keys
Final Select (To get error) requires :
1 Temp table (Declare @. or Temp #)
4 joins to any tables
2 conditions in the where clause
-- Drop view Search_IVw
-- Drop Table Header
-- Drop Table Detail
-- Drop Table FK1
-- Create Table for Foreign Keys
Create Table FK1 (KeyId Int ,CONSTRAINT PK_KeyId PRIMARY KEY CLUSTERED
(KeyId))
Insert into FK1 Values (1)
Insert into FK1 Values (2)
-- Create Header Table
CREATE TABLE [Header] (
[POHId] [int] IDENTITY (1, 1) NOT NULL ,
[FK2] [int] NOT NULL ,
[FK3] [int] NULL ,
[Bit1] [bit] NOT NULL ,
[Int1] [int] NOT NULL ,
[Int2] [int] NULL ,
[Int3] [int] NULL ,
[FK1] [int] NULL ,
CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED ([POHId]) ON [PRIMARY] ,
CONSTRAINT [FK_Header_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
CONSTRAINT [FK_Header_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId]),
CONSTRAINT [FK_Header_FK3] FOREIGN KEY ([FK3]) REFERENCES [FK1] ([KeyId])
) ON [PRIMARY]
GO
-- Create Detail Table
CREATE TABLE [Detail] (
[PODId] [int] IDENTITY (1, 1) NOT NULL ,
[FK1] [int] NOT NULL ,
[FK2] [int] NOT NULL ,
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([PODId]) ON [PRIMARY] ,
CONSTRAINT [FK_Detail_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
CONSTRAINT [FK_Detail_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId])
) ON [PRIMARY]
GO
-- Create Header table with at least 136977 entries
Create table #G (Number Int)
Declare @.A Int
Set @.A =0
Insert into #G Values(1)
While @.A <=17
Begin
Insert into #G Select 1 From #G
Set @.A = @.A + 1
End
Insert into Header Select top 200000 2, 1, 0, 1, 1, 1, 1 From #G
Drop Table #G
GO
-- Create Index view with at least 3 index + clustered index
CREATE View Search_IVw WITH SCHEMABINDING As
Select D.PODId,
H.POHId
From Dbo.Header H
join Dbo.Detail D on D.FK1 = H.POHId
Go
Create unique clustered index Search_IVw_Idx on Search_IVw (PODId)
Create index Search_IVw_Idx2 on Search_IVw (POHId)
Create index Search_IVw_Idx3 on Search_IVw (POHId)
Create index Search_IVw_Idx4 on Search_IVw (POHId)
GO
Declare @.Temp Table (KeyId Int)
Insert into Dbo.Detail
Select 1,
2
From Dbo.Header H (NoLock)
join @.Temp X on X.KeyId =H.FK1
join Dbo.FK1 DI1 (NoLock) on DI1.KeyId = 2
join Dbo.FK1 DI2 (NoLock) on DI2.KeyId = 2
join Dbo.FK1 DI3 (NoLock) on DI3.KeyId = 2
Where H.FK3 =1
and H.FK2=1
Thank You
Hartco Oasys Team
We found this issue post sp3a and Microsoft support identified it as a bug
and relased a private HOTFIX for us. As per MS support, this bug exists in
SP4 as well. So you may have to open a case with MS support.
"Hartco Developers" <HartcoDevelopers@.discussions.microsoft.com> wrote in
message news:30445DFB-C7DE-4275-BA4C-05662EC420C3@.microsoft.com...
> Hi !
> We recently changed server to a SQL 2000 SP4 on Windows 2003 (Old was SP3
> with Windows 2000)
> Since the server change we've had Internal SQL Server error on a few
> Stored
> procedures.
> Server: Msg 8624, Level 16, State 1, Line 5
> Internal SQL Server error.
>
> We were able to create this script that can be run on any test
> environment.
> Notes: Index View must have at least 3 index + clustered index
> Header Table rerquires 2 Foreign Keys
> Details also requires 2 Foreign Keys
> Final Select (To get error) requires :
> 1 Temp table (Declare @. or Temp #)
> 4 joins to any tables
> 2 conditions in the where clause
>
> -- Drop view Search_IVw
> -- Drop Table Header
> -- Drop Table Detail
> -- Drop Table FK1
> -- Create Table for Foreign Keys
> Create Table FK1 (KeyId Int ,CONSTRAINT PK_KeyId PRIMARY KEY CLUSTERED
> (KeyId))
> Insert into FK1 Values (1)
> Insert into FK1 Values (2)
> -- Create Header Table
> CREATE TABLE [Header] (
> [POHId] [int] IDENTITY (1, 1) NOT NULL ,
> [FK2] [int] NOT NULL ,
> [FK3] [int] NULL ,
> [Bit1] [bit] NOT NULL ,
> [Int1] [int] NOT NULL ,
> [Int2] [int] NULL ,
> [Int3] [int] NULL ,
> [FK1] [int] NULL ,
> CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED ([POHId]) ON [PRIMARY] ,
> CONSTRAINT [FK_Header_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
> CONSTRAINT [FK_Header_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId]),
> CONSTRAINT [FK_Header_FK3] FOREIGN KEY ([FK3]) REFERENCES [FK1] ([KeyId])
> ) ON [PRIMARY]
> GO
> -- Create Detail Table
> CREATE TABLE [Detail] (
> [PODId] [int] IDENTITY (1, 1) NOT NULL ,
> [FK1] [int] NOT NULL ,
> [FK2] [int] NOT NULL ,
> CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([PODId]) ON [PRIMARY] ,
> CONSTRAINT [FK_Detail_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
> CONSTRAINT [FK_Detail_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId])
> ) ON [PRIMARY]
> GO
> -- Create Header table with at least 136977 entries
> Create table #G (Number Int)
> Declare @.A Int
> Set @.A =0
> Insert into #G Values(1)
> While @.A <=17
> Begin
> Insert into #G Select 1 From #G
> Set @.A = @.A + 1
> End
> Insert into Header Select top 200000 2, 1, 0, 1, 1, 1, 1 From #G
> Drop Table #G
> GO
> -- Create Index view with at least 3 index + clustered index
> CREATE View Search_IVw WITH SCHEMABINDING As
> Select D.PODId,
> H.POHId
> From Dbo.Header H
> join Dbo.Detail D on D.FK1 = H.POHId
> Go
> Create unique clustered index Search_IVw_Idx on Search_IVw (PODId)
> Create index Search_IVw_Idx2 on Search_IVw (POHId)
> Create index Search_IVw_Idx3 on Search_IVw (POHId)
> Create index Search_IVw_Idx4 on Search_IVw (POHId)
> GO
> Declare @.Temp Table (KeyId Int)
> Insert into Dbo.Detail
> Select 1,
> 2
> From Dbo.Header H (NoLock)
> join @.Temp X on X.KeyId =H.FK1
> join Dbo.FK1 DI1 (NoLock) on DI1.KeyId = 2
> join Dbo.FK1 DI2 (NoLock) on DI2.KeyId = 2
> join Dbo.FK1 DI3 (NoLock) on DI3.KeyId = 2
> Where H.FK3 =1
> and H.FK2=1
>
> --
> Thank You
> Hartco Oasys Team

Getting Internal SQL Server error. with SP4

Hi !
We recently changed server to a SQL 2000 SP4 on Windows 2003 (Old was SP3
with Windows 2000)
Since the server change we've had Internal SQL Server error on a few Stored
procedures.
Server: Msg 8624, Level 16, State 1, Line 5
Internal SQL Server error.
We were able to create this script that can be run on any test environment.
Notes: Index View must have at least 3 index + clustered index
Header Table rerquires 2 Foreign Keys
Details also requires 2 Foreign Keys
Final Select (To get error) requires :
1 Temp table (Declare @. or Temp #)
4 joins to any tables
2 conditions in the where clause
-- Drop view Search_IVw
-- Drop Table Header
-- Drop Table Detail
-- Drop Table FK1
-- Create Table for Foreign Keys
Create Table FK1 (KeyId Int ,CONSTRAINT PK_KeyId PRIMARY KEY CLUSTERED
(KeyId))
Insert into FK1 Values (1)
Insert into FK1 Values (2)
-- Create Header Table
CREATE TABLE [Header] (
[POHId] [int] IDENTITY (1, 1) NOT NULL ,
[FK2] [int] NOT NULL ,
[FK3] [int] NULL ,
[Bit1] [bit] NOT NULL ,
[Int1] [int] NOT NULL ,
[Int2] [int] NULL ,
[Int3] [int] NULL ,
[FK1] [int] NULL ,
CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED ([POHId]) ON [PRIMARY] ,
CONSTRAINT [FK_Header_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
CONSTRAINT [FK_Header_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId]),
CONSTRAINT [FK_Header_FK3] FOREIGN KEY ([FK3]) REFERENCES [FK1] ([KeyId])
) ON [PRIMARY]
GO
-- Create Detail Table
CREATE TABLE [Detail] (
[PODId] [int] IDENTITY (1, 1) NOT NULL ,
[FK1] [int] NOT NULL ,
[FK2] [int] NOT NULL ,
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([PODId]) ON [PRIMARY] ,
CONSTRAINT [FK_Detail_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
CONSTRAINT [FK_Detail_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId])
) ON [PRIMARY]
GO
-- Create Header table with at least 136977 entries
Create table #G (Number Int)
Declare @.A Int
Set @.A =0
Insert into #G Values(1)
While @.A <=17
Begin
Insert into #G Select 1 From #G
Set @.A = @.A + 1
End
Insert into Header Select top 200000 2, 1, 0, 1, 1, 1, 1 From #G
Drop Table #G
GO
-- Create Index view with at least 3 index + clustered index
CREATE View Search_IVw WITH SCHEMABINDING As
Select D.PODId,
H.POHId
From Dbo.Header H
join Dbo.Detail D on D.FK1 = H.POHId
Go
Create unique clustered index Search_IVw_Idx on Search_IVw (PODId)
Create index Search_IVw_Idx2 on Search_IVw (POHId)
Create index Search_IVw_Idx3 on Search_IVw (POHId)
Create index Search_IVw_Idx4 on Search_IVw (POHId)
GO
Declare @.Temp Table (KeyId Int)
Insert into Dbo.Detail
Select 1,
2
From Dbo.Header H (NoLock)
join @.Temp X on X.KeyId =H.FK1
join Dbo.FK1 DI1 (NoLock) on DI1.KeyId = 2
join Dbo.FK1 DI2 (NoLock) on DI2.KeyId = 2
join Dbo.FK1 DI3 (NoLock) on DI3.KeyId = 2
Where H.FK3 =1
and H.FK2=1
--
Thank You
Hartco Oasys TeamWe found this issue post sp3a and Microsoft support identified it as a bug
and relased a private HOTFIX for us. As per MS support, this bug exists in
SP4 as well. So you may have to open a case with MS support.
"Hartco Developers" <HartcoDevelopers@.discussions.microsoft.com> wrote in
message news:30445DFB-C7DE-4275-BA4C-05662EC420C3@.microsoft.com...
> Hi !
> We recently changed server to a SQL 2000 SP4 on Windows 2003 (Old was SP3
> with Windows 2000)
> Since the server change we've had Internal SQL Server error on a few
> Stored
> procedures.
> Server: Msg 8624, Level 16, State 1, Line 5
> Internal SQL Server error.
>
> We were able to create this script that can be run on any test
> environment.
> Notes: Index View must have at least 3 index + clustered index
> Header Table rerquires 2 Foreign Keys
> Details also requires 2 Foreign Keys
> Final Select (To get error) requires :
> 1 Temp table (Declare @. or Temp #)
> 4 joins to any tables
> 2 conditions in the where clause
>
> -- Drop view Search_IVw
> -- Drop Table Header
> -- Drop Table Detail
> -- Drop Table FK1
> -- Create Table for Foreign Keys
> Create Table FK1 (KeyId Int ,CONSTRAINT PK_KeyId PRIMARY KEY CLUSTERED
> (KeyId))
> Insert into FK1 Values (1)
> Insert into FK1 Values (2)
> -- Create Header Table
> CREATE TABLE [Header] (
> [POHId] [int] IDENTITY (1, 1) NOT NULL ,
> [FK2] [int] NOT NULL ,
> [FK3] [int] NULL ,
> [Bit1] [bit] NOT NULL ,
> [Int1] [int] NOT NULL ,
> [Int2] [int] NULL ,
> [Int3] [int] NULL ,
> [FK1] [int] NULL ,
> CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED ([POHId]) ON [PRIMARY] ,
> CONSTRAINT [FK_Header_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
> CONSTRAINT [FK_Header_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId]),
> CONSTRAINT [FK_Header_FK3] FOREIGN KEY ([FK3]) REFERENCES [FK1] ([KeyId])
> ) ON [PRIMARY]
> GO
> -- Create Detail Table
> CREATE TABLE [Detail] (
> [PODId] [int] IDENTITY (1, 1) NOT NULL ,
> [FK1] [int] NOT NULL ,
> [FK2] [int] NOT NULL ,
> CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([PODId]) ON [PRIMARY] ,
> CONSTRAINT [FK_Detail_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
> CONSTRAINT [FK_Detail_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId])
> ) ON [PRIMARY]
> GO
> -- Create Header table with at least 136977 entries
> Create table #G (Number Int)
> Declare @.A Int
> Set @.A =0
> Insert into #G Values(1)
> While @.A <=17
> Begin
> Insert into #G Select 1 From #G
> Set @.A = @.A + 1
> End
> Insert into Header Select top 200000 2, 1, 0, 1, 1, 1, 1 From #G
> Drop Table #G
> GO
> -- Create Index view with at least 3 index + clustered index
> CREATE View Search_IVw WITH SCHEMABINDING As
> Select D.PODId,
> H.POHId
> From Dbo.Header H
> join Dbo.Detail D on D.FK1 = H.POHId
> Go
> Create unique clustered index Search_IVw_Idx on Search_IVw (PODId)
> Create index Search_IVw_Idx2 on Search_IVw (POHId)
> Create index Search_IVw_Idx3 on Search_IVw (POHId)
> Create index Search_IVw_Idx4 on Search_IVw (POHId)
> GO
> Declare @.Temp Table (KeyId Int)
> Insert into Dbo.Detail
> Select 1,
> 2
> From Dbo.Header H (NoLock)
> join @.Temp X on X.KeyId =H.FK1
> join Dbo.FK1 DI1 (NoLock) on DI1.KeyId = 2
> join Dbo.FK1 DI2 (NoLock) on DI2.KeyId = 2
> join Dbo.FK1 DI3 (NoLock) on DI3.KeyId = 2
> Where H.FK3 =1
> and H.FK2=1
>
> --
> Thank You
> Hartco Oasys Team

Getting Internal SQL Server error. with SP4

Hi !
We recently changed server to a SQL 2000 SP4 on Windows 2003 (Old was SP3
with Windows 2000)
Since the server change we've had Internal SQL Server error on a few Stored
procedures.
Server: Msg 8624, Level 16, State 1, Line 5
Internal SQL Server error.
We were able to create this script that can be run on any test environment.
Notes: Index View must have at least 3 index + clustered index
Header Table rerquires 2 Foreign Keys
Details also requires 2 Foreign Keys
Final Select (To get error) requires :
1 Temp table (Declare @. or Temp #)
4 joins to any tables
2 conditions in the where clause
-- Drop view Search_IVw
-- Drop Table Header
-- Drop Table Detail
-- Drop Table FK1
-- Create Table for Foreign Keys
Create Table FK1 (KeyId Int ,CONSTRAINT PK_KeyId PRIMARY KEY CLUSTERED
(KeyId))
Insert into FK1 Values (1)
Insert into FK1 Values (2)
-- Create Header Table
CREATE TABLE [Header] (
[POHId] [int] IDENTITY (1, 1) NOT NULL ,
[FK2] [int] NOT NULL ,
[FK3] [int] NULL ,
[Bit1] [bit] NOT NULL ,
[Int1] [int] NOT NULL ,
[Int2] [int] NULL ,
[Int3] [int] NULL ,
[FK1] [int] NULL ,
CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED ([POHId]) ON [PRIMA
RY] ,
CONSTRAINT [FK_Header_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1]
([KeyId]),
CONSTRAINT [FK_Header_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1]
([KeyId]),
CONSTRAINT [FK_Header_FK3] FOREIGN KEY ([FK3]) REFERENCES [FK1]
([KeyId])
) ON [PRIMARY]
GO
-- Create Detail Table
CREATE TABLE [Detail] (
[PODId] [int] IDENTITY (1, 1) NOT NULL ,
[FK1] [int] NOT NULL ,
[FK2] [int] NOT NULL ,
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([PODId]) ON [PRIMA
RY] ,
CONSTRAINT [FK_Detail_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1]
([KeyId]),
CONSTRAINT [FK_Detail_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1]
([KeyId])
) ON [PRIMARY]
GO
-- Create Header table with at least 136977 entries
Create table #G (Number Int)
Declare @.A Int
Set @.A =0
Insert into #G Values(1)
While @.A <=17
Begin
Insert into #G Select 1 From #G
Set @.A = @.A + 1
End
Insert into Header Select top 200000 2, 1, 0, 1, 1, 1, 1 From #G
Drop Table #G
GO
-- Create Index view with at least 3 index + clustered index
CREATE View Search_IVw WITH SCHEMABINDING As
Select D.PODId,
H.POHId
From Dbo.Header H
join Dbo.Detail D on D.FK1 = H.POHId
Go
Create unique clustered index Search_IVw_Idx on Search_IVw (PODId)
Create index Search_IVw_Idx2 on Search_IVw (POHId)
Create index Search_IVw_Idx3 on Search_IVw (POHId)
Create index Search_IVw_Idx4 on Search_IVw (POHId)
GO
Declare @.Temp Table (KeyId Int)
Insert into Dbo.Detail
Select 1,
2
From Dbo.Header H (NoLock)
join @.Temp X on X.KeyId =H.FK1
join Dbo.FK1 DI1 (NoLock) on DI1.KeyId = 2
join Dbo.FK1 DI2 (NoLock) on DI2.KeyId = 2
join Dbo.FK1 DI3 (NoLock) on DI3.KeyId = 2
Where H.FK3 =1
and H.FK2=1
Thank You
Hartco Oasys TeamWe found this issue post sp3a and Microsoft support identified it as a bug
and relased a private HOTFIX for us. As per MS support, this bug exists in
SP4 as well. So you may have to open a case with MS support.
"Hartco Developers" <HartcoDevelopers@.discussions.microsoft.com> wrote in
message news:30445DFB-C7DE-4275-BA4C-05662EC420C3@.microsoft.com...
> Hi !
> We recently changed server to a SQL 2000 SP4 on Windows 2003 (Old was SP3
> with Windows 2000)
> Since the server change we've had Internal SQL Server error on a few
> Stored
> procedures.
> Server: Msg 8624, Level 16, State 1, Line 5
> Internal SQL Server error.
>
> We were able to create this script that can be run on any test
> environment.
> Notes: Index View must have at least 3 index + clustered index
> Header Table rerquires 2 Foreign Keys
> Details also requires 2 Foreign Keys
> Final Select (To get error) requires :
> 1 Temp table (Declare @. or Temp #)
> 4 joins to any tables
> 2 conditions in the where clause
>
> -- Drop view Search_IVw
> -- Drop Table Header
> -- Drop Table Detail
> -- Drop Table FK1
> -- Create Table for Foreign Keys
> Create Table FK1 (KeyId Int ,CONSTRAINT PK_KeyId PRIMARY KEY CLUSTERED
> (KeyId))
> Insert into FK1 Values (1)
> Insert into FK1 Values (2)
> -- Create Header Table
> CREATE TABLE [Header] (
> [POHId] [int] IDENTITY (1, 1) NOT NULL ,
> [FK2] [int] NOT NULL ,
> [FK3] [int] NULL ,
> [Bit1] [bit] NOT NULL ,
> [Int1] [int] NOT NULL ,
> [Int2] [int] NULL ,
> [Int3] [int] NULL ,
> [FK1] [int] NULL ,
> CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED ([POHId]) ON [PRI
MARY] ,
> CONSTRAINT [FK_Header_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1
] ([KeyId]),
> CONSTRAINT [FK_Header_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1
] ([KeyId]),
> CONSTRAINT [FK_Header_FK3] FOREIGN KEY ([FK3]) REFERENCES [FK1
] ([KeyId])
> ) ON [PRIMARY]
> GO
> -- Create Detail Table
> CREATE TABLE [Detail] (
> [PODId] [int] IDENTITY (1, 1) NOT NULL ,
> [FK1] [int] NOT NULL ,
> [FK2] [int] NOT NULL ,
> CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([PODId]) ON [PRI
MARY] ,
> CONSTRAINT [FK_Detail_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1
] ([KeyId]),
> CONSTRAINT [FK_Detail_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1
] ([KeyId])
> ) ON [PRIMARY]
> GO
> -- Create Header table with at least 136977 entries
> Create table #G (Number Int)
> Declare @.A Int
> Set @.A =0
> Insert into #G Values(1)
> While @.A <=17
> Begin
> Insert into #G Select 1 From #G
> Set @.A = @.A + 1
> End
> Insert into Header Select top 200000 2, 1, 0, 1, 1, 1, 1 From #G
> Drop Table #G
> GO
> -- Create Index view with at least 3 index + clustered index
> CREATE View Search_IVw WITH SCHEMABINDING As
> Select D.PODId,
> H.POHId
> From Dbo.Header H
> join Dbo.Detail D on D.FK1 = H.POHId
> Go
> Create unique clustered index Search_IVw_Idx on Search_IVw (PODId)
> Create index Search_IVw_Idx2 on Search_IVw (POHId)
> Create index Search_IVw_Idx3 on Search_IVw (POHId)
> Create index Search_IVw_Idx4 on Search_IVw (POHId)
> GO
> Declare @.Temp Table (KeyId Int)
> Insert into Dbo.Detail
> Select 1,
> 2
> From Dbo.Header H (NoLock)
> join @.Temp X on X.KeyId =H.FK1
> join Dbo.FK1 DI1 (NoLock) on DI1.KeyId = 2
> join Dbo.FK1 DI2 (NoLock) on DI2.KeyId = 2
> join Dbo.FK1 DI3 (NoLock) on DI3.KeyId = 2
> Where H.FK3 =1
> and H.FK2=1
>
> --
> Thank You
> Hartco Oasys Team

Getting intermittent error with SQLDMO objects

In an application that uses SQLDMO object to BCP data into a empty table in a database I get the following error only occassionally on one or two machines out of hundreds. I would like to understand why and resolve the issue.

From the application log file.

Bulk Copying data
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Error Description
-2147220299 Error Error
IDispatch error #693 Error Message
An error occurred while processing [RMS_EDM_TUTORIAL] Batch Copy:[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.RmsDbManager::Process

The Code involved.

The error is thrown at the the following line in the code below.

dmoTable = dmoTables->Item((LPCTSTR)sTableName); // Error thrown I think by ODBC driver but not sure. The error indicates locked locked resources but which resources (no query involved that I see) and on the machine where the error occurrs it happens every time. Help would be greatly appreciated.

bool

MyClassSqlServer::BatchCopy( const char *sDBName, const char *sDir, const char *sFileSpec, MyClassFile& logfile)
{
MyClass_ASSERT_VALID;
MyClassBool bRet = MyClass_FALSE;
ASSERT(sDBName);
ASSERT(sDir);
ASSERT(sFileSpec);
ASSERT(m_dmoServer.GetInterfacePtr());
CString sPath(sDir), sDataFile, sTableName;

try
{
sPath += PATH_SEP;
sPath += CString(sFileSpec);

CFileFind fileFind;
BOOL bContinue = fileFind.FindFile(sPath);

SQLDMO::DatabasesPtr dmoDbs;
SQLDMO::_DatabasePtr dmoDb;
SQLDMO::TablesPtr dmoTables;
SQLDMO::_TablePtr dmoTable;
dmoDbs = m_dmoServer->GetDatabases();
dmoDb = dmoDbs->Item(sDBName);
dmoTables = dmoDb->GetTables();

while ( bContinue )
{

bContinue = fileFind.FindNextFile();
sDataFile = fileFind.GetFilePath();
sTableName = fileFind.GetFileTitle();
sTableName = "[" + sTableName + "]";
CString msg;

if (!fileFind.IsDirectory())
{
dmoTable = dmoTables->Item((LPCTSTR)sTableName);
msg.Format("BCP file is: %s\n", sTableName);
logfile << msg;
BulkCopy ( dmoTable, sDataFile);
}
}
fileFind.Close();
bRet = MyClass_TRUE;
}
catch(_com_error &err)
{
TRACE0("An error occurred in MyClassSqlServer::BatchCopy()");
logfile << err.Description() << " Error Description \n";
logfile << err.Error() << " Error Error\n";
logfile << err.ErrorMessage() << " Error Message\n";
throw Except(Except::se_BatchCopy, err.Description(), err.ErrorMessage());
}

I moved this thread to data access as this issue does not seem to be rooted in DMO. I hope that someone in this group can help out.

Getting inserted data in trigger

I am using SQL Server 2000.

I want to create an after insert trigger on one of my tables, but I have forgotten how I reference the inserted data to do some business logic on it. Can someone please help.

Thanks

Jag

Hi!,

u can refer the iinserted row as "inserted":

select * from inserted OR inserted.field1 for a specific field.

Hope this will help.

Getting information out of a transaction that rollsback

I have a client who is logging diagnostic information in the midst of lengthy, nested transactions, and finds that the log informaiton disappears when the outer transaction is rolled back. What he wants to do is essentially defeat the transaction, and get records added to a table from within a transaction that don't participate in the transaction.

I've thought about using an Extended Stored Proc, or an RPC with SET_REMOTE_PROC_TRANSACTIONS set to "OFF", or trying to create a linked server and defeat the distributed transaction system, but before I set out building and testing I'd like to ask the experts how to purposefully get data out of a transaction.

Yes, there are work-arounds possible within the T_SQL Procs, we've talked about loading the debug information into a table var which is passed around until all the transactions are cleared and then is logged, but he doesn't hant to go back and revise hundreds of procs. We're hoping to adjust the single logging function to make a "transaction-proof" INSERT.

Any ideas?

GA Dean

You should be able to write a .NET CLR procedure to do this.

When you create the SqlConnection object in the procedure, do not specify Context Connection=true. Instead, create a "real" connection to the database. Such a connection will not be a part of the ambient transaction.

I do not believe that there is any straight T-SQL way to do this, but since you interested in extended SP's, this technique should make sense to you as well, given MS's recommendation to move away frop extended procs in favor of SQLCLR.

Look up "Regular vs. Context Connections" in Books Online for more information.

|||Thanks, I'll give it a try. Reading the docs it was unclear if we would be able to create a regular connection from a CLR called from within an existing transaction.|||I'm not sure that will work. A context connection runs within the same transaction space as the regular connection. It sounds like you're doing two-phase commits if you have what you called an inner and an outer transaction. An alternative would be to start a different transaction, using a different regular connection, for the logging phase although data from the "production" transaction would have to be explicitly passed to the "logging" transaction. Of course, I'm not sure if that will work either.
|||

GA Dean,

I believe that the easy way to capture logging information into a table variable (you mentioned alreay) while in the transaction.

Thanks.

Naras.

|||A transaction by definition is all or nothing. If it gets rolled back, it never happened. There is no evidence of it, except in the transaction log itself. You cannot do "just part of a transaction". EVERYTHING within the transaction gets rolled back on failure. If you log to another table, that other table will be rolled back also, because it is within the transaction scope.

There is no way to get what you want, except to write a text file with the diagnostic info. You could write out to a file, and then bcp the log file into a table after the transaction is done.

Getting information out of a transaction that rollsback

I have a client who is logging diagnostic information in the midst of lengthy, nested transactions, and finds that the log informaiton disappears when the outer transaction is rolled back. What he wants to do is essentially defeat the transaction, and get records added to a table from within a transaction that don't participate in the transaction.

I've thought about using an Extended Stored Proc, or an RPC with SET_REMOTE_PROC_TRANSACTIONS set to "OFF", or trying to create a linked server and defeat the distributed transaction system, but before I set out building and testing I'd like to ask the experts how to purposefully get data out of a transaction.

Yes, there are work-arounds possible within the T_SQL Procs, we've talked about loading the debug information into a table var which is passed around until all the transactions are cleared and then is logged, but he doesn't hant to go back and revise hundreds of procs. We're hoping to adjust the single logging function to make a "transaction-proof" INSERT.

Any ideas?

GA Dean

You should be able to write a .NET CLR procedure to do this.

When you create the SqlConnection object in the procedure, do not specify Context Connection=true. Instead, create a "real" connection to the database. Such a connection will not be a part of the ambient transaction.

I do not believe that there is any straight T-SQL way to do this, but since you interested in extended SP's, this technique should make sense to you as well, given MS's recommendation to move away frop extended procs in favor of SQLCLR.

Look up "Regular vs. Context Connections" in Books Online for more information.

|||Thanks, I'll give it a try. Reading the docs it was unclear if we would be able to create a regular connection from a CLR called from within an existing transaction.|||I'm not sure that will work. A context connection runs within the same transaction space as the regular connection. It sounds like you're doing two-phase commits if you have what you called an inner and an outer transaction. An alternative would be to start a different transaction, using a different regular connection, for the logging phase although data from the "production" transaction would have to be explicitly passed to the "logging" transaction. Of course, I'm not sure if that will work either.
|||

GA Dean,

I believe that the easy way to capture logging information into a table variable (you mentioned alreay) while in the transaction.

Thanks.

Naras.

|||A transaction by definition is all or nothing. If it gets rolled back, it never happened. There is no evidence of it, except in the transaction log itself. You cannot do "just part of a transaction". EVERYTHING within the transaction gets rolled back on failure. If you log to another table, that other table will be rolled back also, because it is within the transaction scope.

There is no way to get what you want, except to write a text file with the diagnostic info. You could write out to a file, and then bcp the log file into a table after the transaction is done.

Getting information dependant upon primary & foreign key

Hi All,

It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!

I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.

The Problem?

Tables:

1.tblRisk: Ref(pk), subject, status(fk), staff(fk),Dept(fk)

2.tblDept:Ref(pk), Department

The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.

Many Thanks,

Kunal

CREATE PROCEDURE dbo.ShowMe @.yourInputValue INT
AS
SELECT tblDept.Department
FROM tblDept JOIN tblRisk
ON tblDept.Ref = tblRisk.Dept
WHEREtblDept.Ref = @.yourInputValue
RETURN 0
GO

You stored procedure is ok

You can use SqlCommand to call stored procedures in your ASP .NET application, just set the CommandType property of the SqlCommand to CommandType.StoredProcedure and pass proper parameter(s) to the stored procedure.

Getting Info from Merge Agent Programmatically

Hi - I was wondering how one goes about getting infomation on agent
activity (merge and snapshot agent in my case). I would like to know when
the last synchronization took place, if it failed or not, any error
messages, etc.
How can I get this information without EM?
Thank you,
Maer
Thank you, Paul. This is exactly what I was looking for.
Do we have any control over what the name of the agent is?
Thank you - Maer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0ed301c4b1c8$0f7b9770$a601280a@.phx.gbl...
> Maer,
> this script will get you details of the last history for
> the merge agent called 'DH1791628-Northwind-
> NorthwindStaff-DH1791628-3'.
> It will have basic error info if an error occurs.
> If you want the detailed error info, you'll need to join
> to the MSrepl_errors table: MSmerge_history.error_id =
> MSrepl_errors.id. This table typically has several rows
> so you might want to use a cursor to add the text
> together.
> HTH,
> Paul Ibison (SQL Server MVP)
>
> declare @.agent_id int
> select @.agent_id = id from msmerge_agents where name
> = 'DH1791628-Northwind-NorthwindStaff-DH1791628-3'
> select top 1 case
> when rh.runstatus = 1 then 'Start'
> when rh.runstatus = 2 then 'Succeed'
> when rh.runstatus = 3 then 'In progress'
> when rh.runstatus = 4 then 'Idle'
> when rh.runstatus = 5 then 'Retry'
> when rh.runstatus = 6 then 'Fail'
> end as RunStatus,
> 'start_time' = convert(nvarchar(12), start_time, 112) +
> substring(convert(nvarchar(24), start_time, 121), 11, 13),
> comments
> from MSmerge_history rh
> order by timestamp desc

Getting Images to render in HTML using Web Service (SOAP)

I have created a web app that uses the reporting services Web Service to
pull reports from the SQL Server.
The web server is on one machine, SQL Server on another and I use the web
service to get the reports. I do not use URLs in any way in order to keep as
much information hidden as possible.
I have been able to list all reports, select a report, get back the
parameters required, set the parameter values and render reports using
ReportingService.Render
HOWEVER...
The reports that work do not include charts (graphics). When I render a
report using my app, if it has no chart I can view it in HTML, PDF, TIF,
Excel and MHTML. If it has a chart then for PDF, Excel and TIF all is fine.
If I try to use HTML or MHTML I get a big empty space where my charts should
be.
In reading the BOL under ReportingService.Render Method
I found this: "StreamIds
[out] The stream identifiers. These IDs are passed to the RenderStream
method. You can use them to render the external resources (images, etc.)
that are associated with a given report. "
I have found this topic:
ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.htm
ReportingService.RenderStream Method
I read the code, and I have tried to implement it as in the BOL. I am able
to write the images onto the web-server hard-drive, and the image place
holder properties no longer point to my SQL server (which they did at first)
but I still don't get an image.
For example, my image is actually at:
http://sandy.murdocks.on.ca/1/reports/temp/C_79_S
It is there and it works. Now my report opens in a new window since I do the
final call to open it like this:
Response.AddHeader("Content-Disposition", "attachment; filename=" &
fileName)
Response.BinaryWrite(data)
When I right click on the image place holder I get this:
file:///C:/Documents%20and%20Settings/Administrator/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/QPIH2VE9/1/Reports/Temp/C_79_S
The images don't seem to get from being written on the drive, to being
picked up for display.
My code for writing the images looks like this:
If format = "HTML4.0" Then
For Each streamid In streamIDs
image = rs.RenderStream(strpath, "HTML4.0", streamid,
Nothing, Nothing, parameters, Nothing, Nothing)
Dim stream As System.IO.FileStream = _
System.IO.File.OpenWrite(Application("TempReports") &
streamid)
stream.Write(image, 0, CInt(image.Length))
stream.Close()
Next
End If
Sandy Murdock MCPlook at my previuos post:
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=rome&lang=en&cr=US&guid=&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a830cc14-9b06-496b-b1f4-e55ab3611f0b
Antonio
"Roger Twomey" wrote:
> I have created a web app that uses the reporting services Web Service to
> pull reports from the SQL Server.
> The web server is on one machine, SQL Server on another and I use the web
> service to get the reports. I do not use URLs in any way in order to keep as
> much information hidden as possible.
> I have been able to list all reports, select a report, get back the
> parameters required, set the parameter values and render reports using
> ReportingService.Render
> HOWEVER...
> The reports that work do not include charts (graphics). When I render a
> report using my app, if it has no chart I can view it in HTML, PDF, TIF,
> Excel and MHTML. If it has a chart then for PDF, Excel and TIF all is fine.
> If I try to use HTML or MHTML I get a big empty space where my charts should
> be.
> In reading the BOL under ReportingService.Render Method
> I found this: "StreamIds
> [out] The stream identifiers. These IDs are passed to the RenderStream
> method. You can use them to render the external resources (images, etc.)
> that are associated with a given report. "
> I have found this topic:
> ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.htm
> ReportingService.RenderStream Method
> I read the code, and I have tried to implement it as in the BOL. I am able
> to write the images onto the web-server hard-drive, and the image place
> holder properties no longer point to my SQL server (which they did at first)
> but I still don't get an image.
> For example, my image is actually at:
> http://sandy.murdocks.on.ca/1/reports/temp/C_79_S
> It is there and it works. Now my report opens in a new window since I do the
> final call to open it like this:
> Response.AddHeader("Content-Disposition", "attachment; filename=" &
> fileName)
> Response.BinaryWrite(data)
> When I right click on the image place holder I get this:
> file:///C:/Documents%20and%20Settings/Administrator/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/QPIH2VE9/1/Reports/Temp/C_79_S
> The images don't seem to get from being written on the drive, to being
> picked up for display.
> My code for writing the images looks like this:
> If format = "HTML4.0" Then
> For Each streamid In streamIDs
> image = rs.RenderStream(strpath, "HTML4.0", streamid,
> Nothing, Nothing, parameters, Nothing, Nothing)
> Dim stream As System.IO.FileStream = _
> System.IO.File.OpenWrite(Application("TempReports") &
> streamid)
> stream.Write(image, 0, CInt(image.Length))
> stream.Close()
> Next
> End If
>
> Sandy Murdock MCP
>
>|||Thank you!
I was able to take your solution and from it build one that works for me. I
was stuck.
Thanks.
"Antonio Rome" <AntonioRome@.discussions.microsoft.com> wrote in message
news:61BF34C8-98DB-4388-8E06-F5F8C1F22BBE@.microsoft.com...
> look at my previuos post:
>
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=rome&lang=en&cr=US&guid=&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a830cc14-9b06-496b-b1f4-e55ab3611f0b
> Antonio
>
> "Roger Twomey" wrote:
> > I have created a web app that uses the reporting services Web Service to
> > pull reports from the SQL Server.
> >
> > The web server is on one machine, SQL Server on another and I use the
web
> > service to get the reports. I do not use URLs in any way in order to
keep as
> > much information hidden as possible.
> >
> > I have been able to list all reports, select a report, get back the
> > parameters required, set the parameter values and render reports using
> > ReportingService.Render
> >
> > HOWEVER...
> >
> > The reports that work do not include charts (graphics). When I render a
> > report using my app, if it has no chart I can view it in HTML, PDF, TIF,
> > Excel and MHTML. If it has a chart then for PDF, Excel and TIF all is
fine.
> > If I try to use HTML or MHTML I get a big empty space where my charts
should
> > be.
> >
> > In reading the BOL under ReportingService.Render Method
> > I found this: "StreamIds
> > [out] The stream identifiers. These IDs are passed to the RenderStream
> > method. You can use them to render the external resources (images, etc.)
> > that are associated with a given report. "
> >
> > I have found this topic:
> > ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.htm
> > ReportingService.RenderStream Method
> >
> > I read the code, and I have tried to implement it as in the BOL. I am
able
> > to write the images onto the web-server hard-drive, and the image place
> > holder properties no longer point to my SQL server (which they did at
first)
> > but I still don't get an image.
> >
> > For example, my image is actually at:
> > http://sandy.murdocks.on.ca/1/reports/temp/C_79_S
> >
> > It is there and it works. Now my report opens in a new window since I do
the
> > final call to open it like this:
> > Response.AddHeader("Content-Disposition", "attachment; filename=" &
> > fileName)
> > Response.BinaryWrite(data)
> >
> > When I right click on the image place holder I get this:
> >
file:///C:/Documents%20and%20Settings/Administrator/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/QPIH2VE9/1/Reports/Temp/C_79_S
> >
> > The images don't seem to get from being written on the drive, to being
> > picked up for display.
> >
> > My code for writing the images looks like this:
> >
> > If format = "HTML4.0" Then
> > For Each streamid In streamIDs
> > image = rs.RenderStream(strpath, "HTML4.0", streamid,
> > Nothing, Nothing, parameters, Nothing, Nothing)
> >
> > Dim stream As System.IO.FileStream = _
> > System.IO.File.OpenWrite(Application("TempReports") &
> > streamid)
> >
> > stream.Write(image, 0, CInt(image.Length))
> > stream.Close()
> > Next
> > End If
> >
> >
> > Sandy Murdock MCP
> >
> >
> >|||I've been rendering chart images successfully for a report using the
ReportingService web services for a few weeks now. Today I had to add a
parameter to the report and now after I call my render method and loop
through the returned streamId's to get the images, I get the exception:
"rsStreamNotFound The stream cannot be found. The stream identifier that is
provided to an operation cannot be located in the report server database."
Why would this be happening?! I'm looping through the very streamId's that
the render method just returned to me!
Thanks in advance, Brian Parker

Getting image into database

Hey all, I have a table that i would like to be able to store images in but dont know how i can ge them in there. How can i put the image in the database? (sql express 2005, VS 2005 Pro) vb

Thanks!

Image/text data in SQL is also called BLOB data, which is stored as binary object. What you need to do is transfer binary stream between files and database tables. You can take a look at this post:

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

Getting image from DB error?

Unsure why I am getting such an error when the image is there and thesearch feature for the site is not working so that doesn't help so I'mhoping some out there can offer why I maybe getting this and help mewith getting the image from the DB.

I use the code from the starter app for retrieving an image from the DB and I get the error message:
Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'

Here is the code and I am getting the error on the red line (Return New MemoryStream(CType(result, Byte()))):

Public Overloads Function GetPhoto(ByVal UserName As String) As Stream
command.CommandText = "sp_Themes_GetUserThemeImage"
command.Parameters.Add("@.UserName", SqlDbType.VarChar, 50)
command.Parameters(0).Value = UserName

Dim result As Object = command.ExecuteScalar
Try
If result Is Nothing Then
Dim path As String =HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings("siteImageDirectory"))
path = (path + "noimageav.gif")

Return New FileStream(path, FileMode.Open, FileAccess.Read,FileShare.Read)
Else
Return New MemoryStream(CType(result, Byte()))
End If
Catch e As ArgumentNullException
Return Nothing
End Try
End Function

--this is the code from the imagehandler.ashx page--
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest

Dim userName As String
Dim stream As IO.Stream = Nothing
If ((Not (context.Request.QueryString("UserName")) Is Nothing) _
AndAlso (context.Request.QueryString("UserName") <> "")) Then
userName = [Convert].ToString(context.Request.QueryString("UserName"))
stream = (New PhotoManager).GetPhoto(userName)

'Get the photo from the database, if nothing is returned, get thedefault "placeholder" photo
'If (stream Is Nothing) Then
' stream = (New PhotoManager).GetDefaultPhoto()
'End If
' Write image stream to the response stream
Dim buffersize As Integer = (1024 * 16)
Dim buffer() As Byte = New Byte((buffersize) - 1) {}
Dim count As Integer = stream.Read(buffer, 0, buffersize)

Do While (count > 0)
context.Response.OutputStream.Write(buffer, 0, count)
count = stream.Read(buffer, 0, buffersize)
Loop
End If
End Sub

--this is how I call it from the image.aspx page--

<img src='imageHandler.ashx?username=<%# Eval("UserName") %>'style="border:2px solid white;height:40px;" alt='Thumbnail.' /
Thanks for all your help.

The image data is a null, I think. Try doing:

IF result=system.DBNULL.value then
Return nothing
ELSE
Return New MemoryStream(CType(result, Byte()))
ENDIF

|||Well, I changed things which works but still the image will not show up.

I was using the photoManager.vb file which was part of one of the starter apps but I eliminated a step and came up with this:

If ((Not (context.Request.QueryString("UserName")) Is Nothing) _
AndAlso (context.Request.QueryString("UserName") <> "")) Then
userName = [Convert].ToString(context.Request.QueryString("UserName"))
Dim connection As SqlConnection
Dim command As SqlCommand
Dim reader As SqlDataReader

connection = NewSqlConnection(ConfigurationManager.ConnectionStrings("owcConnectionString").ConnectionString)
command = New SqlCommand
command.Connection = connection
command.CommandType = CommandType.StoredProcedure
connection.Open()

'get image from db
command.CommandText = "sp_Themes_GetUserThemeImage"
command.Parameters.Add("@.UserName", SqlDbType.VarChar, 50)
command.Parameters(0).Value = userName

Try
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Do While (reader.Read)
If IsDBNull(reader.GetValue(0)) Then
Dim path As String =HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings("siteImageDirectory"))
path += "noimageav.gif"
stream = New FileStream(path, FileMode.Open, FileAccess.Read,FileShare.Read)
Dim buffersize As Integer = (1024 * 16)
Dim buffer() As Byte = New Byte((buffersize) - 1) {}
Dim count As Integer = stream.Read(buffer, 0, buffersize)
context.Response.OutputStream.Write(buffer, 0, count)
Exit Do
Else
context.Response.ContentType = reader.Item(1).ToString
context.Response.BinaryWrite(reader.Item(0))
End If
Loop
Catch e As Exception
context.Response.Write(e)
Finally
command.Dispose()
connection.Dispose()
End Try
End If

Which works just fine except that I am still not getting the image fromthe DB to show up. I don't get any error messages or anything andwhen I debug and walk through, it walks through just fine but no imagefrom the DB.

Can anyone offer some help on this?

thanks|||

You don't need to/shouldn't be looping (Do While reader.read), since you can't output multiple images that way -- Change it to "If reader.read then ...". You aren't supplying a contenttype if your image is null. If your noimageav.gif is larger than 16k, you'll truncate it. I would also issue a response.clear before you set the contenttype, and response.end after you finish writing.

Personally, I would probably just issue a response.redirect instead of opening the file, reading it and dumping it in the case that you don't have an image, but that's me.

|||Point well taken but I'm only outputting a single image and onlyretrieving a single image so no concerns about multiple images. Also, if the image in the DB is null, it then retrieves thenoimageav.gif which never changes in size and it also isn't in the DBso it is not the problem. The problem is the is the image that isstored in the DB is not showing up.

When it hits these 2 lines of code, I would expect it to return the image but it doesn't. Just shows blank.

context.Response.ContentType = reader.Item(1).ToString
context.Response.BinaryWrite(reader.Item(0))

Even though there is data. Verified this when debugged.

Any other ideas?

thanks
|||

Grab fiddler (www.fiddler.com), and with that you can see exactly what the server is sending back to you.

Like I said, i would reponse.clear before, and response.end around those 2 lines of code just to make sure the headers are getting cleared out, and not getting clobbered after.

Make sure that you close the browser you are trying to pull the image with regularly because IE seems to cache the contenttype of a page, and it's rather hard to get it to change to the new type sometimes.

|||That link doesn't have anything on it related to what you mentioned.

I did do a response.clear and response.end in my latest code but still the same thing.

So far, I've tried about 3 different ways for displaying an image froma DB. 1st from a book, second from the starter app and 3rd froman online source and none of them worked. At 1st I was thinkingthat the data but that doesn't seem to be the case.

Right now, I've come to a complete and total stand still in trying to solve this.

Any ideas out there about what it may be?

thanks

Getting Identity/Serial of Row Just Inserted?

This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent).

At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results.

Hints? Mahalos in advance ... :) KevInKauai

You can use the SCOPE_IDENTITY() function to retrieve the ID of the row just inserted. Check out books on line to read up some info on the function.

|||

@.@.IDENTITY

Returns the last-inserted identity value.

Syntax

@.@.IDENTITY

Return Types

numeric

Remarks

After an INSERT, SELECT INTO, or bulk copy statement completes, @.@.IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @.@.IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @.@.IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @.@.IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @.@.IDENTITYvalue does not revert to a previous setting if the INSERT or SELECT INTO statementor bulk copy fails, or if the transaction is rolled back.

@.@.IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@.@.IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @.@.IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, seeIDENT_CURRENT.

Examples

This example inserts a row into a table with an identity column and uses @.@.IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.@.IDENTITY AS 'Identity'

|||

Perhaps I wasn't clear. I'm doing this in an ASP.NET application (..aspx) -- NOT in Transact-SQL -- where neither IDENTITY_CURRENT nor any of those other TRANSACT-SQL constants seems to be available.

KevInKauai

|||

KevInKauai:

NOT in Transact-SQL

Then perhaps you should convert it to a stored proc and youre life will be easier

|||

KevInKauai:

Perhaps I wasn't clear. I'm doing this in an ASP.NET application (..aspx) -- NOT in Transact-SQL -- where neither IDENTITY_CURRENT nor any of those other TRANSACT-SQL constants seems to be available.

KevInKauai

But it is still what you need to do. How exactly you implement it depends on how you're doing the code, but SCOPE_IDENTITY is what you need. (Mehedi: @.@.IDENTITY is generally not recommended, since concurrent operations can return the wrong value. SCOPE_IDENTITY() virtually always returns what you actually need.)

Are you running dynamic SQL in your ASP.NET application? Then you can tack on a call to SCOPE_IDENTITY() to the end of the query. Something like this:

string sql = "INSERT <row into primary table>; SELECT SCOPE_IDENTITY()"

Then execute the code and read the return value from the statement.

As David commented, this is all much easier and cleaner if you use a stored procedure, because then the new identity can either be returned as a single row, single column record set, or as the return value from the procedure.

In essence, you need to cause SQL Server to send you the value, which requires SCOPE_IDENTITY. But there are various ways to get it to do that.

Make sense?

Don

|||

Hi, Don - -

I prefer not to deal with Stored Procedures in general as they tend (to me) to be cumbersome and require extra steps rather than the "on-the-fly" development that I am presently dealing with.

That said, apprenting the "SCOPE_IDENTITY" to the INSERT seemed to not get an error, but where does the result come back?

1 SQL =String.Format("INSERT INTO [Parent] ([parentData]) " & _2 "VALUES ('{0}'); SELECT SCOPE_IDENTITY() ", _3 txtRowData.Text)45 SqlDataSource1.InsertCommand = SQL67Try8 SqlDataSource1.Insert()

The row got inserted (verified that), but now how do I get that identity? (Sorry to be such a blank here. This serial stuff was always obscure and I guess we can blame Chris Date for not including it more formally in the SQL definitiong.)

tia ... :) KevInKauai

Getting Identity when inserting a record from Stored Procedure....

I have a stored procedure that inserts a record into a table.
I need to get the Identity seed or Key that was just created by this
insert. Can I do this inside the stored procedure, returning the just
created key?
Here is the stored procedure...
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1
AS
INSERT INTO a_TixDetail
(SvcTixKey, Date, [Performed by], [Detail Summary],
External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
RETURN
TixKey is a field in the table that is the Primary key. It will be
genereated when the Insert is executed. How can I get this returned from
this procedure?
Thanks,
RogIn SQL Server 2000 use SCOPE_IDENTITY() to return the inserted IDENTITY
value. In SQL Server 7.0 use @.@.IDENTITY.
David Portas
SQL Server MVP
--|||Roger,
Create an output parameter and get the value from the function
SCOPE_IDENTITY().
Example:
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1,
@.new_id int output
AS
set nocount on
INSERT INTO a_TixDetail (SvcTixKey, Date, [Performed by], [Detail
Summary],External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
set @.new_id = scope_identity()
RETURN
go
declare @.i int
declare @.rv int
exec @.rv = R_InsertTicketDetail ..., @.i output
print @.rv
print @.i
go
AMB
"Roger" wrote:

> I have a stored procedure that inserts a record into a table.
> I need to get the Identity seed or Key that was just created by this
> insert. Can I do this inside the stored procedure, returning the just
> created key?
> Here is the stored procedure...
> ALTER PROCEDURE R_InsertTicketDetail
> @.SvcTixKey int,
> @.OpenDate datetime,
> @.PerformedBy nvarchar(50),
> @.Detail ntext,
> @.External bit = -1
> AS
> INSERT INTO a_TixDetail
> (SvcTixKey, Date, [Performed by], [Detail Summary],
> External)
> VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
> RETURN
>
> TixKey is a field in the table that is the Primary key. It will be
> genereated when the Insert is executed. How can I get this returned from
> this procedure?
> Thanks,
> Rog
>
>

Getting identity value

I am using ADO to connect to SQL 2k db. We have converted it to merge
replication and now I am not getting identity field value after I use the
Update method. For example:
rsPayInfo.AddNew
rsPayInfo("CheckAmt") = 100.00
rsPayInfo.Update
lngCheckID = rsPayInfo("CheckID")
The variable lngCheckID is returning a zero, which is not the actual value
on the new table entry. What do I need to do to retrieve the new CheckID?
Thanks.
Davidcheck the update command - it should be UPDATE ...; SELECT ...
to select the updated values
same for INSERT
David wrote:
> I am using ADO to connect to SQL 2k db. We have converted it to merge
> replication and now I am not getting identity field value after I use the
> Update method. For example:
> rsPayInfo.AddNew
> rsPayInfo("CheckAmt") = 100.00
> rsPayInfo.Update
> lngCheckID = rsPayInfo("CheckID")
> The variable lngCheckID is returning a zero, which is not the actual value
> on the new table entry. What do I need to do to retrieve the new CheckID?
> Thanks.
> David
>
>

Getting Identity of Inserted Record using inserted event??

is there any way of getting the identity without using the "@.@.idemtity" in sql? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me?use scope_identity() is the best way. do not try to get last identity from VB, C#.

Hope this help

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try
(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>
|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
--|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
--|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>