insert or replace: stored

Showing posts with label stored. Show all posts

Friday, March 30, 2012

Inserting a Decimal - HELP

Im trying to insert a decimal into SQL 2000 using a stored procedure. I can successfully insert it using a direct T-SQL statement inside the code, but I cant insert it correctly when calling for a stored procedure.

For instance 12.5 will insert as 12.

Here is my SQL Parameter code:
[code]
MyCommand.Parameters.Add(New SqlParameter("@.Num", SqlDbType.Decimal))
MyCommand.Parameters("@.Num").Precision = 5
MyCommand.Parameters("@.Num").Scale = 2
MyCommand.Parameters("@.Num").Value = Convert.ToDecimal(TextBox1.Text)
[/code]

I also declared @.Num to be a decimal in my Stored Procedure.

As you can see I give the Parameter more than enough detail about the data type as well as convert the textbox value to a decimal, but it still rounds the value to an integer like data.

Again using a direct T-SQL statement inside the code works OK, but the stored procedure will not.
ANyone got any ideas why this is happening.Try running Profiler and tracing the transaction. Then you can determine if the precision is being lost when .NET sends the command to SQL Server, or when SQL Server calls the stored procedure. Have you tried not being so specific? Can you post the source code for your stored procedure?


MyCommand.Parameters.Add("@.Num", Convert.ToDecimal(TextBox1.Text))

Inserting 1:M relationship data via One Stored Procedure

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

The best reference for this kind of thing when you truly have a 1:M relationship is Erland's web page: http://www.sommarskog.se/arrays-in-sql.html

But if you have a max of 3, then just write the proc with 3 parameters (something like):

create procedure contact$insert
(
@.LastName,
...
@.MobileNo,
@.PhoneNo1,
@.PhoneNo2,
@.PhoneNo3
)
--add your own error handling of course or add SET XACT_ABORT ON that
--will stop the tran on any error

begin tran

insert into contact (lastName, ..., MobileNo) --note, assuming contactId is an identity
values (@.lastName, ..., @.MobileNo)

declare @.newContactId int
set @.newContactId = scope_identity()

insert into contactTelephone
select @.newContactId, @.phoneNo1
where @.phoneNo1 is not null
union all
select @.newContactId, @.phoneNo2
where @.phoneNo2 is not null
union all
select @.newContactId, @.phoneNo3
where @.phoneNo3 is not null

commit tran

|||

Hi Louis,

Thanks for the Response, this cleared my mind and the problem. Thank you again!

sql

Wednesday, March 28, 2012

Inserted and Deleted tables

Hi:

Can any of the experts please confirm the fact that Inserted and deleted tables in SQL Server 2005 are stored in tempdb?. If so, how can I query them in tempdb ( A code snippet would be useful).

Thanks

Hi Ankith,

Inserted and deleted table are created in Trigger execution time and can't possible query them, only in trigger execution time.

Regards,

|||

Thanks for the reply. I still would like to know if they are stored in tempdb though in SQL Server 2005 Vs getting stored in memory in SQL Server 2000.

Any pointers?

Thanks

|||inserted/deleted are memory-resident tables. You cannot access them outside of the execution context.|||Thanks OJ. So what I might have read is probably talking of row versioning that uses tempdb. Thanks again for the clarification.|||You shouldn't be allowed to read internal worktable even if it resides in tempdb. If you could, this would be a major security hole. ;-)|||

Hi OJ:

Right I agree with you. However what does the following paragraph mean?

URL is :http://www.sqlmag.com/Article/ArticleID/93465/sql_server_93465.html

The first impression i get when i read the paragraph is the tables are stored in tempdb in 2005. This is where I am confused. Can you please elaborate further?.

Thanks

Triggers have long been a part of SQL Server and were the only feature prior to SQL Server 2005 that provided any type of historical (or versioned) data. Triggers can access two pseudo-tables called deleted and inserted. Inside the trigger, you can access these two tables as if they were real tables, but accessing them while not in a trigger results in an unknown object error. If the trigger is a DELETE trigger, the deleted table contains copies of all the rows deleted by the operation that caused the trigger to fire. If the trigger is an INSERT trigger, the inserted table contains copies of all the rows inserted by the operation that caused the trigger to fire. And if the trigger is an UPDATE trigger, the deleted table contains copies of the old versions of the rows, and the inserted table contains all the new versions. Before SQL Server 2005, SQL Server would determine which rows were included in these pseudo-tables by scanning the transaction log for all the log records belonging to the current transaction. Any log records containing data inserted in or deleted from the table to which the trigger was tied were included in the inserted or deleted tables.

In SQL Server 2005, these pseudo-tables are created by using RLV technology. When data-modification operations are performed on a table that has a relevant trigger defined, SQL Server creates versions of the old and new data in the version store in tempdb.This occurs whether or not either of the snapshot-based isolation levels has been enabled.When a SQL Server 2005 trigger accesses the deleted table, it retrieves the data from the version store.When a trigger needs to determine which rows in the table are new rows and accesses the inserted table, SQL Server again gets the inserted table rows from the version store.

|||The article describes how sqlserver physically create/maintain the inserted/deleted table. For a very long time now, tempdb has always been used as the workspace for sqlserver. It uses tempdb to hold the paged data that can't fit in the allowable memory - @.table variable is the best example of this. So, in the new sql2k5, instead of scanning the log to materialize the inserted/deleted table, it goes ahead and store a copy of updated data in tempdb. This will make the materialization faster because it does not have to scan the entire log.

Long story short, inserted and deleted table are very special table. Regardless of how they're materialized, they can only be accessed within the execution (trigger) context.|||

I am curious why you would want to do this in the first place. Are you simply trying to access the data before and after the record is created. In a trigger you can access the date using inserted and deleted as a table name.

select * from inserted

Also, it is interesting to point out that an update consists of both an insert and a delete.

|||Thanks OJ for your explanation.sql

inserted and deleted table

for after trigger the records stored in followig table

inserted and deleted table.

but i want to know where this tables physically stored ...i mean in which database master or some other database?

and 2nd thing tigger fired for each row or for only insert,delete,update statement?

thanx

Where stored?

Obviously in temp tables at tempdb.

Is it executed for each row?

No. If single query affects more than one row, the inserted or deleted table may have more than one row. When you write a trigger you have to keep consider this & you have to handle your trigger query which will support both single row & multiple rows.

|||

The table is not physically stored it is virtual only, it only exists within the trigger context. Triggers are fired per statement not per row, you will need to handle mutlirow existance in your trigger and in addition the occurence of no affected rows,a s the trigger is also fired if no rows is affected like

Code Snippet

UPDATE SomeTable SET SomeColumn = 'SomeValue' WHERE 1=2

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Is it executed for each row?

mani

i mean trigger fired for each row or only for update statement..here i m not talking about inserted and deleted table

|||

On high level it is called virtual, but SQL Server always use the TempDB as workspace to store the data, so the data may be presented or stored in tempdb but you can't access these data from outside of your trigger scope & these are absolutely read-only.

There is interesting thread on same question on DB Engine forum

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=908238&SiteID=1

|||The answer is for one statement not for each rows.

|||thanx mani and jens.

Insert/Updated SP from multiple tables

How do I insert unrelated statistical data from three tables into another
table that already exist with data using an insert or update stored procedure?
OR...
How do I write an insert/Update stored procedure that has multiple select
and a where something = something statements?

This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this...

CREATE PROCEDURE AddDrawStats
AS
INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)

SELECT
WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),

TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),

RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )

FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID

WHERE D.DrawingID = DS.CurrentDrawing
GO"INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing"
and
"WHERE W.DrawingID = DS.CurrentDrawing"
are redundant. They both accomplish the same thing; associating records in the two tables. Among SQL Server DBAs, the INNER JOIN syntax is preferred, so drop the links in your WHERE clauses.

As to your other issues, I'm sorry but the SQL statement you posted is too disjointed to figure out what your intentions are. You will need to describe your tables and your objective if you want more help, but embedding subqueries into the SELECT clause is rarely a good idea. I highly suspect that what your SQL statement describes is not really what you are trying to do.|||yes my attention is that I have Four related/non-related table and I would like to get some statistical data such as the count of how many student are in the student table, how many student are playing the current drawing, how many tickets are in the current drawing, and how many students won the current drawing. Setting up a common inner join would not allow me to get the exact data I need. Plus, I need to insert this data in the drawing table record that already have data but these fields are null. My stored procedure works somewhat, but it creates a new record; I want the stored procedure to insert this information in the record that already exist where drawing = the CurrentDrawing. So should I do an insert/update stored procedure, and how? All I need to see is an example of a stored procedure that insert or update data in some table where some criteria are met which comes from multiple select statements using different table within those select statement.|||This is what your SQL Statement describes, but again, I doubt that it is exactly what you want:

CREATE PROCEDURE AddDrawStats
AS

Declare @.Players int
Declare @.RegisterPlayers int
Declare @.TicketsPlayed int

set @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
set @.RegisterPlayers = (SELECT Count(*) FROM Student)
set @.TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Update Drawing
set WinnersWon = WinnersSubquery.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
from Drawing
inner join
(SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
on Drawing.DrawingID = WinnersSubquery.CurrentDrawing

INSERT INTO Drawing (DrawingID, WinnersWon, TicketsPlayed, Players, RegisterPlayers)
select WinnersSubquery.CurrentDrawing,
WinnersSubquery.WinnersWon,
@.TicketsPlayed,
@.Players,
@.RegisterPlayers
from (SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
left outer join Drawing on WinnersSubquery.CurrentDrawing = Drawing.DrawingID
where Drawing.DrawingID is null|||Thanks for all the help! This works but I have two questions?
Could I have written this Stored procedure better? and...
Why this statement yeilds the wrong results? *i.e.*each player can have up to five tickets in the ticket table, but this statement count each ticket as a player.How do I write this statement to get only unigue AccountID within the ticket table?
**SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)

CREATE PROCEDURE AddDrawStats2
AS

DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT

SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )

UPDATE Drawing
SET WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers

WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO|||SET @.Players = (SELECT Count(Distinct T.AccountID) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)|||Thanks so much for all the help. This stored procedure does the job, but you see any drawbacks?

CREATE PROCEDURE AddDrawStats
AS
DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT

SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players =(SELECT Count(Distinct T.AccountID) FROM Ticket T WHERE T.Active = 1)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )

UPDATE Drawing
SET
WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO

Insert/Update statements or Stored Procs

When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.
thanksStored procs...but who's going to write them?|||Use ADO from VB for insert and update|||Originally posted by Brett Kaiser
Stored procs...but who's going to write them?

wouldn't i just code the Insert/Update statement within the stored proc, then pass the value's to the stored proc. That sounds like alot of parameters to be dealing with for larger tables.|||Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.|||Originally posted by Brett Kaiser
Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.

Thanks Brett, one more quick question. Within the stored proc, i need to check if the record already exists before inserting or updating. Can you paste a small code sample to give me an idea of how i would ideally do that.

thanks alot|||I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP|||USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D'
GO

CREATE PROC mySproc99
@.Action Char(1)
, @.Col1 int
, @.Col2 char(1) = Null
AS
-- File: {\\tsstrv03\ESolutions}:
-- Date: May 1st, 2002
-- Author: Brett Kaiser
-- Server:
-- Database: TaxReconDB
-- Login: sa
-- Description: myTable99 Maint sproc
--
--
-- The stream will do the following:
--
-- 1.
--
-- Tables Used: myTable99
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- ------- ---- ------ ------ ------ ------
-- Ledger_Detail 76779 17160 KB 17040 KB 64 KB 56 KB
-- ATS_SignOff_Entity 3316 512 KB 504 KB 16 KB -8 KB
-- tblAcct_LedgerBalance 11691 3848 KB 3792 KB 8 KB 48 KB
--
--Change Log
--
-- UserId Date Description
-- ---- ----- ---------------------------
-- x002548 05/23/2002 1. Initial release
--
--
--

Declare @.error_out int, @.Result_Count int, @.Error_Message varchar(255), @.Error_Type int, @.Error_Loc int, @.RC int

SET NOCOUNT ON

SELECT @.rc = 0

BEGIN TRAN

IF @.Action NOT IN ('S','I','U','D')
BEGIN
SELECT @.Error_Loc = 1
SELECT @.Error_Message = 'Incorrect Request. Must be S,I,U or D. Paramter was: "' + @.Action + '"'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END

IF @.Action = 'S'
BEGIN
SELECT Col1, Col2 FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 2
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 2
SELECT @.Error_Message = 'myTable99 Returned zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'D'
BEGIN
DELETE FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 3
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 3
SELECT @.Error_Message = 'An Attempted DELETE from myTable99 affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'I'
BEGIN
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 4
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 4
SELECT @.Error_Message = 'An Attempted INSERT to myTable99 did not insert anything'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'U'
BEGIN
UPDATE myTable99 SET Col2=@.Col2 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 5
SELECT @.Error_Message = 'An Attempted UPDATE of myTable99 Affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

COMMIT TRAN

mySproc99_Exit:

SET NOCOUNT OFF

RETURN @.rc

mySproc99_Error:

ROLLBACK TRAN

IF @.Error_Type = 50001
BEGIN
Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error_out)
END
IF @.Error_Type = 50002

BEGIN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END

SELECT @.rc = -1

RAISERROR @.Error_Type @.Error_Message

GOTO mySproc99_Exit
GO

DECLARE @.RC int

EXEC @.RC = mySproc99 'X',1,'A'

SELECT @.RC

EXEC @.RC = mySproc99 'S',4

SELECT @.RC

EXEC @.RC = mySproc99 'I',5,'E'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'U',5,'F'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'D',5

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'I',4,'F'

SELECT @.RC
GO

DROP PROC mySproc99
GO
DROP TABLE myTable99
GO|||Originally posted by Pat Phelan
I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP
Actually, any communication with the server should be done through stored procedure, including SELECT.|||Originally posted by rdjabarov
Actually, any communication with the server should be done through stored procedure, including SELECT. I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||here's the man of so many virtues|||Originally posted by ms_sql_dba
here's the man of so many virtues

You sure s/he's a man?

Pat, you lost me...

we're talking about an app right? Not ad-hoc/dba maint issues? right?|||Originally posted by ms_sql_dba
here's the man of so many virtues Are you accusing me of having virtues ? I may wear many hats, but that is due to having a huge head. It has nothing to do with virtues of any kind!

-PatP|||Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)

suave is the only word I can think of...

You must be a ladies man....

:D

Does anyone use anything like the template posted..or is it 1 sproc per operation?|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?) Heck, I thought that confusion was a consequence of working with databases!

Nah, I don't really have any of those titles, but they sounded cool next to my actual titles (International super-spy, Bon-Vivant, and Ultra-cool geek about town). I'll try to behave better from now on!

-PatP|||Originally posted by Brett Kaiser
suave is the only word I can think of... Nope, Suave is one of our DataWarehousing consultants. He lives somewhere in Jersey and flies out to come play when we need him.
Originally posted by Brett Kaiser
You must be a ladies man.... Just one lady, although I do flirt outrageously. I used to send people around the bend when I'd dial our last TAM and ask "So how are you, other than obviously devastatingly gorgeous?" To which she'd often reply "Gee, you've just GOT to call more often."
Originally posted by Brett Kaiser
Does anyone use anything like the template posted..or is it 1 sproc per operation? Nope, to me that smacks of bad design. At least in my book, cross-tabs should be done on the client side or in the data warehouse, not from an OLTP system.

-PatP|||That's all VERY funny...

but what do you mean cross tabs?

And I definetly have to use that line...

Well, at least on the wife anyway...|||Originally posted by Brett Kaiser
but what do you mean cross tabs?
Whoops! Brain fart on my part, wrong thread!

-PatP|||International super-spy? You took my title!!! I demand it back...or a cig!|||You're really having trouble with the cig thing, but it is worth the fight. There aren't enough "bright boys" around, and we can't afford to loose any!

Anywho, the title isn't exclusive. When they awarded me the title, none of the previous users lost their permission to use it!

-PatP|||>For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand".

Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools. You better get your facts right...pb8 > 9704 would "compile" and give SQL error codes whenever embedded sql is given in code and it adds to ease of use for developers...[no doubt agrees for SP approch being the better one].

moreover, PB supports all 4 levels of dynamic SQL superbly and I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...

my 2 cents

WS [wizardofnet-at-yahoo]

Originally posted by Pat Phelan
I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||Originally posted by mell
I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...

[as he types falling out of chair]
Really?
[/as he types falling out of chair]|||Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN|||Originally posted by MCrowley
Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN

Ya lost me on that one...you mean make 4 out of the one I posted?

It's all a matter of methodolgy...

Pick 1 and stick eith it...no thinking involved...same thing for naming comventions...

make it so you don't have to look anything up...

But I like the part about not knowing the names of columns or tables...

must make for some very interesting code...no?|||Originally posted by mell
Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools.[wizardofnet-at-yahoo] 'splain dis one again for me... In the scenario I described, you don't have SELECT permissions, so you can't see any tables. You can't open the DataWindow painter. You can't generate any dynamic SQL...

What exactly can you do again?

-PatP|||Hey Lucy.....what did you did you do with the permissions this time?|||Yep. As near as I understand, when a procedure runs for the first time with it's first parameters, a query plan is born. SQL Server tries to use that query plan for each successive run of the stored procedure. Writing an all in one procedure is good if the procedure is not run very often, but for a website where a stored procedure can be run many many times, you don't want to wait around for the query optimizer to try to figure out it needs to recompile all of a sudden. Clear as mud?|||woooooosh...

and huh?

wouldn't the plan just stay in cache?

Got to get that internals book...|||Here is a classic example. Get on a server that has been around and been backing up databases regularly. Then make this stored procedure:

create procedure testproc (@.start int, @.end int)
as

select *
from msdb..backupset
where backup_set_id > @.start
and backup_set_id < @.end
go

Then run this:
testproc 1, 2

Get the execution plan, then run this:
testproc 1, 40000

and check out that execution plan

It should use the index for both, even though a table scan would be better for the second query.

EDIT: Hmm. Having trouble with the reverse of the logic in this example. I can not get the stored proc to do anything but use the index. Anyway, complex queries can get hit pretty hard by this fact. Something to keep in mind.|||Originally posted by Sammy_S
When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.

thanks

It's definitely better to use sprocs. In this way you separate the different layers in your application (something, which you may have missed to consider during the development). Later if you need changes you will need just to change the sprocs, without any modifications in the VB code. Also consider that the sprocs syntax is being validated during creation and they're compiled. So in all cases it's better to use them instead of raw hard-coded statements.

Martin Markov

INSERT...EXEC prevents parallelism?

Hello all. I have a, shall we say, "legacy" stored proc whose result
set I must manipulate in another stored proc. The legacy stored proc
is extremely complex, and the final select for the result set is about
2 pages of UNION statements. When this legacy SP runs in a standalone
fashion, it executes in less than 20 seconds, with the query plan
showing a high degree of parallelism.
The driver calls the legacy proc using INSERT ...EXEC, placing the data
into a pre-defined temp table and doing some simple manipulations to
it. Not complex. But it takes several minutes to run, and the query
plan shows basically the same execution plan for the legacy SP, but
with no parallelism.
What can be done to make the driver perform without the
single-processor bottleneck? I'm happy to provide the SP code, query
plans, etc if desired, but thought there might be a "Doh!" answer. :-)
TIA
CCinnabar (heynele@.cotse.net) writes:
> Hello all. I have a, shall we say, "legacy" stored proc whose result
> set I must manipulate in another stored proc. The legacy stored proc
> is extremely complex, and the final select for the result set is about
> 2 pages of UNION statements. When this legacy SP runs in a standalone
> fashion, it executes in less than 20 seconds, with the query plan
> showing a high degree of parallelism.
> The driver calls the legacy proc using INSERT ...EXEC, placing the data
> into a pre-defined temp table and doing some simple manipulations to
> it. Not complex. But it takes several minutes to run, and the query
> plan shows basically the same execution plan for the legacy SP, but
> with no parallelism.
> What can be done to make the driver perform without the
> single-processor bottleneck? I'm happy to provide the SP code, query
> plans, etc if desired, but thought there might be a "Doh!" answer. :-)
Hm, I trying to recall that whether I have heard of this issue before,
but I am not sure. In any case, when you are insertning data into a table
that can affect the query plan and the choice of parallelism.
I have an article on web site, that discusses INSERT-EXEC and a number
of alternatives to it. Maybe you should try any of the alternatives?
http://www.sommarskog.se/share_data.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||INSERT...EXEC causes the stored procedure to run within the context of a
transaction. It may be that parallelism isn't the problem, but rather
running it in a transaction.
Try executing the procedure from within a transaction, i.e.,
BEGIN TRAN
EXEC procName...
COMMIT
and see if things slow down. If they do, then don't use INSERT...EXEC,
instead rewrite the procedure or a copy of the procedure so that the insert
happens within the proc. If you create the temp table before executing the
procedure, it can be populated by the proc and will exist after the proc
returns.
"Cinnabar" <heynele@.cotse.net> wrote in message
news:1136847107.546724.48670@.o13g2000cwo.googlegroups.com...
> Hello all. I have a, shall we say, "legacy" stored proc whose result
> set I must manipulate in another stored proc. The legacy stored proc
> is extremely complex, and the final select for the result set is about
> 2 pages of UNION statements. When this legacy SP runs in a standalone
> fashion, it executes in less than 20 seconds, with the query plan
> showing a high degree of parallelism.
> The driver calls the legacy proc using INSERT ...EXEC, placing the data
> into a pre-defined temp table and doing some simple manipulations to
> it. Not complex. But it takes several minutes to run, and the query
> plan shows basically the same execution plan for the legacy SP, but
> with no parallelism.
> What can be done to make the driver perform without the
> single-processor bottleneck? I'm happy to provide the SP code, query
> plans, etc if desired, but thought there might be a "Doh!" answer. :-)
> TIA
> C
>|||Thanks Erland. Unfortunately, none of the alternatives is really a
possibility.
I knew that insert...exec would cause the called SP to execute within a
transaction (as Brian points out) but that in and of itself shouldn't
prevent parallelism, should it? I will plan to try executing the
legacy SP within a transaction tomorrow, to get proof that is the
issue.
This is a real drag, as my only recourse is to copy the guts of the
legacy SP into mine...which obviates the whole idea of reuse. This
legacy SP contains complex data business rules that are essentially
undocumented (and uncommented in the SP), and are maintained by another
group of developers.
thanks for your and Brian's replies.
C
Erland Sommarskog wrote:
> Cinnabar (heynele@.cotse.net) writes:
> Hm, I trying to recall that whether I have heard of this issue before,
> but I am not sure. In any case, when you are insertning data into a table
> that can affect the query plan and the choice of parallelism.
> I have an article on web site, that discusses INSERT-EXEC and a number
> of alternatives to it. Maybe you should try any of the alternatives?
> http://www.sommarskog.se/share_data.html.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Brian, I did the test you suggested and that's exactly it.
Do you know why the explicit transaction could prevent parallelism?
And a related question: I might also have to use this legacy SP as the
source on a data pump DTS task that inserts the result set into a
staging table on another server. Am I likely to run into the same
issue, i.e. poor performance due to lack of parallelism, which in turn
is due to an implicit or explicit transaction?
Thanks!
C
Brian Selzer wrote:
> INSERT...EXEC causes the stored procedure to run within the context of a
> transaction. It may be that parallelism isn't the problem, but rather
> running it in a transaction.
> Try executing the procedure from within a transaction, i.e.,
> BEGIN TRAN
> EXEC procName...
> COMMIT
> and see if things slow down. If they do, then don't use INSERT...EXEC,
> instead rewrite the procedure or a copy of the procedure so that the inser
t
> happens within the proc. If you create the temp table before executing th
e
> procedure, it can be populated by the proc and will exist after the proc
> returns.
> "Cinnabar" <heynele@.cotse.net> wrote in message
> news:1136847107.546724.48670@.o13g2000cwo.googlegroups.com...|||Cinnabar (heynele@.cotse.net) writes:
> Brian, I did the test you suggested and that's exactly it.
> Do you know why the explicit transaction could prevent parallelism?
Very interesting.
But there is something which smells fishy to me. I find it bit difficult
to believe that a transaction scope in general would prevent parallelism.
I mean, in such case I should have heard of it before. Or at least I think
so.
It could of course be something particular particular with this procedure,
although I have no idea of what that could be.
Out of the blue, you are not running with a transactiun isolation
level of REPEATABLE READ or SERIALIZABLE?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I didn't think that the explicit transaction would prevent parallelism. I
suggested on the other hand that the problem may not be parallelism at all,
but the increased lock duration and overhead caused by executing the entire
procedure within the context of a transaction.
It sounds like the problem is the procedure itself. Consider that it may
cost less in time, resources, and performance to fix it. If this is a
procedure that must remain in production, then that should carry more weight
in your decision process. I'm usually hold the opinion: if it ain't broke,
don't fix it! But if the performance is unmanagable, then in this case it's
clearly broken.
"Cinnabar" <heynele@.cotse.net> wrote in message
news:1136924932.520579.29290@.f14g2000cwb.googlegroups.com...
> Brian, I did the test you suggested and that's exactly it.
> Do you know why the explicit transaction could prevent parallelism?
> And a related question: I might also have to use this legacy SP as the
> source on a data pump DTS task that inserts the result set into a
> staging table on another server. Am I likely to run into the same
> issue, i.e. poor performance due to lack of parallelism, which in turn
> is due to an implicit or explicit transaction?
> Thanks!
> C
> Brian Selzer wrote:
>|||Brian, I wish this SP were in my purview, I would certainly rewrite it.
But in a sense it isn't "broken", since it is working fine in its
standalone execution. So those who maintain it will not be eager to
rewrite it to help the performance of my driver proc.
(Erland, neither the driver nor the legacy SP is running with the
isolation levels you asked about.)
Some background I did not mention earlier: my driver SP that filters
the legacy SP's result set is part of a DTS package that moves data
between two servers. (However, this problem is repeatable outside the
context of the DTS package.) One of the alternatives I'm considering
is to modify the package to dump the legacy SP's result set directly
into a staging table on the destination server. Then I'd do the
filtering and transformations at the destination. But I'm not sure
that this design won't result in the same problem; seems like it would
not, since the destination table is on a different server and I can
control the transaction context. I'm banging out a prototype to
determine if this will work.
again, thanks for all your comments!
C

insert, update issue - stored procedure workaround

any stored procedure guru's around ?

I'm going nuts around here.
ok basically I've create a multilangual website using global en localresources for the static parts and a DB for the dynamic part.
I'm using the PROFILE option in asp.net 2.0 to store the language preference of visitors. It's working perfectly.

but Now I have some problems trying to get the right inserts.

basically I have designed my db based on this article:
http://www.codeproject.com/aspnet/LocalizedSamplePart2.asp?print=true

more specifically:
http://www.codeproject.com/aspnet/LocalizedSamplePart2/normalizedSchema.gif

ok now let's take the example of Categorie, Categorie_Local, and Culture

I basically want to create an insert that will let me insert categories into my database with the 2 language:

eg.
in categorie I have ID's 1 & 2
in culture I have:
ID: 1
culture: en-US
ID 2
culture: fr-Be

now the insert should create into Categorie_Local:

cat_id culture_id name
1 1 a category
1 2 une categorie

and so on...

I think this thing is only do-able with a stored procedure because:

1. when creating a new categorie, a new ID has to be entered into Categorie table
2. into the Categorie_local I need 2 rows inserted with the 2 values for 2 different cultures...

any idea on how to do this right ?
I'm a newbie with ms sql and stored procedures :s

help would be very very appreciated!
thanks a lotOK I got it ;)

it's not the best procedure out there I guess since I'm statically assigning my culture_id's but in this case 2 language are more than enough ;)

1 CREATEPROCEDURE [dbo].[InsCategories]2-- Add the parameters for the stored procedure here3@.cat_naam_envarchar(200),4@.cat_naam_nlvarchar(200),5@.cat_dateDateTime6AS7SET NOCOUNT ON;8BEGIN TRAN AddCategory9DECLARE @.cat_idint10Insert into Categorie11(Cat_date)12VALUES (@.cat_date)1314SELECT15@.cat_id=@.@.Identity1617--static: culture_id=1 for dutch18--static: culture_id=2 for english19Insert Into Categorie_Local20(cat_id, culture_id, catnaam)21VALUES (@.cat_id,1,@.cat_naam_nl)2223Insert Into Categorie_Local24(cat_id, culture_id, catnaam)25VALUES (@.cat_id,2,@.cat_naam_en)2627COMMIT Tran AddCategory28

sql

Monday, March 26, 2012

insert, delete, update stored procedures in Snapshot

Just a general question ....
When the initial snapshot for a replication runs, I see it creates a
bunch of sp_MSins, sp_MSdel, and sp_MSupd, 3 stored procedures for
each of the articles in my publication.
I see these sp's at the subscriber db, but what I do not understand is
why I see 2 stored procedures that are identical, one named with
bracquets, the other without bracquets, e.g. [sp_MSupd_Entity Address]
and sp_MSupd_Entity Address ?
My current replication fails because it tries to run sp_MSupd_Entity
Address that apparently never gets created. But [sp_MSupd_Entity
Address] is created.
how can I tell the replication to use [sp_MSupd_Entity Address] not
sp_MSupd_Entity Address ??
Having SQL Server create two sets of replication stored procedures is an error.
SQL Server will by default autogenerate stored procedures in the unbracketed form, ie sp_MSupd_Entity Address.
It looks like the space is in the table name is causing this problem, but I am unable to reproduce your problem on SQL 2000 sp3 8.000.818. What version are you running.
Do a sp_browsereplcmds in your distribution database to see what procs SQL Server is using.
To answer your question, if you need to change the proc name that SQL Server is using right click on your publication, click on the articles tab, click on the browse button to the right of your table name, click on commands tab, and make the changes there

INSERT works in SQL server 2003 but NOT in SQL server 2000

I am new to Infopath 2003, SQL server 2000 and SQL server 2003. I am calling up a stored procedure with 2 variables from jscript in infopath 2003 to run a stored procedure in SQL server 2003 to copy a record (@.RecipetoCopy) and insert it with a new name(@.RecipeNew). It works fine with SQL 2003 but it will not work in SQL 2000. Below is my stored procedure for both.

Code for 2005 work fine:
__________________________________________________ ________
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[CopyInsert]@.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

INSERT INTO [Epmar].[dbo].[Formulas]
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
__________________________________________________ __________

Code for SQL 2000 does not work
__________________________________________________ __________
CREATE PROCEDURE CopyInsert @.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

INSERT Formulas
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
GO
__________________________________________________ _______

Quote:

Originally Posted by MMCI