Install Path

I need to be able to look up the install path for sql from within a DTS
package. The registry reading stored procs have been deleted for 'security'
but I need to make sure that SQL Agent has full access to the directory for a
temp file. It would be ideal to get the install path and use that. This DTS
will be installed an mulitple instances so I would like to automate this.
Thom,
Maybe REGEDT32 and look for SQLPath?
HTH
Jerry
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:7A48EA99-65D1-4ECF-B996-84191EF2DA64@.microsoft.com...
>I need to be able to look up the install path for sql from within a DTS
> package. The registry reading stored procs have been deleted for
> 'security'
> but I need to make sure that SQL Agent has full access to the directory
> for a
> temp file. It would be ideal to get the install path and use that. This
> DTS
> will be installed an mulitple instances so I would like to automate this.

Install Path

I need to be able to look up the install path for sql from within a DTS
package. The registry reading stored procs have been deleted for 'security'
but I need to make sure that SQL Agent has full access to the directory for a
temp file. It would be ideal to get the install path and use that. This DTS
will be installed an mulitple instances so I would like to automate this.Thom,
Maybe REGEDT32 and look for SQLPath?
HTH
Jerry
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:7A48EA99-65D1-4ECF-B996-84191EF2DA64@.microsoft.com...
>I need to be able to look up the install path for sql from within a DTS
> package. The registry reading stored procs have been deleted for
> 'security'
> but I need to make sure that SQL Agent has full access to the directory
> for a
> temp file. It would be ideal to get the install path and use that. This
> DTS
> will be installed an mulitple instances so I would like to automate this.

Install Path

I need to be able to look up the install path for sql from within a DTS
package. The registry reading stored procs have been deleted for 'security'
but I need to make sure that SQL Agent has full access to the directory for
a
temp file. It would be ideal to get the install path and use that. This DT
S
will be installed an mulitple instances so I would like to automate this.Thom,
Maybe REGEDT32 and look for SQLPath?
HTH
Jerry
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:7A48EA99-65D1-4ECF-B996-84191EF2DA64@.microsoft.com...
>I need to be able to look up the install path for sql from within a DTS
> package. The registry reading stored procs have been deleted for
> 'security'
> but I need to make sure that SQL Agent has full access to the directory
> for a
> temp file. It would be ideal to get the install path and use that. This
> DTS
> will be installed an mulitple instances so I would like to automate this.sql

Insidious Bug?

This looks like a bug to me, can someone verify that this is faulty behavior or tell me why it is proper?

Two stored procedures are defined, each creates a temporary table of the same name, but with different definitions. Both procedures compile successfully. If procedure Two is executed by itself, errors are produced. Once procedure One has been run, then both procedures succeed.

It seems to be an issue of improperly resolving the definition of the temporary table.

Thanks

drop procedure one

go

create procedure one

as

begin

create table #salt(

a int not null,

b int not null,

c varchar(100)

)

insert #salt values( 1, 1, 'Fred' )

insert #salt values( 1, 2, 'Barney' )

insert #salt values( 1, 3, 'Wilma' )

insert #salt values( 2, 1, 'Betty' )

insert #salt values( 2, 2, 'Pebbles' )

insert #salt values( 2, 3, 'BamBam' )

insert #salt values( 3, 1, 'Dino' )

Selecta,

b

From#salt

end

go

drop procedure two

go

create procedure two

as

begin

create table #salt(

a int not null,

b int not null

)

insert #salt

Execone

Select*

From#salt

end

go

--Exec one

go

Exec two

go

This is by design and has to do with how we compile modules/batches. When you run SP two for the first time without running one before, we will try to compile the definition of SP two. And when we hit the call to SP one we will try to compile it. At this point the temporary table #salt exists and the compilation will use that as definition. This needs to be done to allow scenarios where you can create a temporary table in one SP and reference it any SPs that you call without creating it. The error message happens now because the table definition is different and/or you already have the object created. If you run SP one first then there is no temporary table at the time of compilation and the definition inside the SP will be used. This plan is cached and it will be reused later when you call SP two so it will work fine. Part of this has to do with deferred name resolution and compilation which will kick-in here because of the use of temporary table.

The moral is that you should avoid creating temporary tables with same name and different definitions in multiple SPs that will call one another. The results can often be unpredictable if you don't know the exact call order. And if you have statements or constructs that will be deferred to run-time for resolution/compilation it becomes even more problematic.

|||

Thanks for the information. It certainly explains the problem.

However, it really seems like a bad design or at least a lazy one. There is a different meaning to two procedures creating temporary tables and two procedures sharing temp tables. Furthermore, the meaning of each case is clear to the compiler as one contains a new Create Table at the inner level. And this design is inconsistent with the rest of the implementation. Once the second Create Table is executed there are two temp tables. In other words, the compiler is forcing the table to share definitions even though they do not share the same instances.

As a practical matter, this behavior is very difficult to deal with. The very fact that inconsistent behavior is produced based on the order of execution points that out. The error message received can vary wildly based on the actual statements involved. I encountered this by calling a procedure written by someone sharing my office and it was more a wild stab that I got it fixed. Imagine calling code delivered with a purchased package. It does not seem reasonable that a developer should have to be aware of the implementation of every stored procedure they might call.

Thanks again for the explanation.

Insertion data via Stored Procedure [URGENT]!

Hi all;

Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??

Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??

Example:
----
I have two tables "Customer" and "CustomerDetails"..

SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).

SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.

FYI:
--
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)

Thanks in advanced!There are a couple of ways to get the last inserted IDENT, but I prefer to use IDENT_CURRENT('table_name') to get the ID from the last inserted record of thespecified table.

INSERT INTO Customer (val1,val2,etc) VALUES (@.Val1,@.val2,etc)
INSERT INTO CustomerDetails (ID,Val1,Val2, etc) VALUES ((IDENT_CURRENT('Customer'),@.val1,@.val2,etc)

|||Ok, what about if I want to do these process in two SPs??

I want to take the IDDENTITY value from the fitrst "INSERT INTO" statment, because I need this value in my source code as well as other Stored Procedure(s).

Thanks in advanced!|||Insert Blah...;
Return SCOPE_IDENTITY()|||Thanks gays.

I think my problem is how to get the returned value (IDDENTITY value) from the VB.NET code (in other words, how to extract it from VB.NET/ADO.NET code)?

Note:
--
VB.NET/ADO.NET
or
C#.NET/ADO.NET

Are are ok, if you would like to demonstrate your replay. ( I want the answer!).

Thanks again.|||YES!!

The problem was from my ADO.NET part.

Thanks for your help gays.

inserting video from sql into embedded media

Hi i have a video file path stored in my sql database and i have an embedded media player in my web page, how can i load up a different video file into the embedded media player based on movie id or movie name, this is the code i have written so far but im unsure if im in the right direction, thanks

sorry this is the code

<

formid="form1"runat="server"><palign="center"><OBJECTID="Player"

CLASSID

="CLSID:6BF52A52-394A-11d3-B153-00C04F79FAA6"

VIEWASTEXT

><PARAMname="autoStart"value="True"><PARAMname="URL"value="<%# Eval("MovieFile") %>"><PARAMname="rate"value="1"><PARAMname="balance"value="0"><PARAMname="enabled"value="true"><PARAMname="enabledContextMenu"value="true"><PARAMname="fullScreen"value="false"><PARAMname="playCount"value="1"><PARAMname="volume"value="100">

</

OBJECT></p>

|||

Hi Peter,

Naturally, you can pass the value from database to the url attribute of the object. And about the usage of ActiveX controls from Internet Explorer, you may refer the following kb article:

http://support.microsoft.com/kb/555687

If this does not help you,pls feel free to mark the post as Not Answered and reply.

Thanks.

Inserting values Using Stored Procedure

Hi !
Iam working in asp.net in Visual Studio 2005. I tried to insert some datas from the webform's interfaces into the SQL Server 05 database using a stored procedure. Since iam new to asp.net,im confused wheather to use any command or anything else ? Kindly Help me !You can either directly execute the text command or can execute the same through a procedure.For that u need to write stored procedure in database. If you know that then go for it. But for that u need some extra coding in the front end. But performance wise using a stored procedure is better.|||

Quote:

Originally Posted by debasisdas

You can either directly execute the text command or can execute the same through a procedure.For that u need to write stored procedure in database. If you know that then go for it. But for that u need some extra coding in the front end. But performance wise using a stored procedure is better.

Can you give me some sample coding !|||

Quote:

Originally Posted by hellboss

Can you give me some sample coding !

The stored procedures is as follows

CREATE PROCEDURE TEST
@.field1 nvarchar(50),
@.field2 numeric(10)
AS
BEGIN
INSERT INTO TABLE1 (field1,field2) VALUES (@.field1,@.field2)
END

Hope this helps.

Thanks,

Inserting values and get the last ID recorded to use in another INSERT

I need to insert some values into a table and after that catch the ID inserted.

I set some input parameters in stored procedure and only one to get the @.id defined as output

SqlParameter paramIdPedido = new SqlParameter("@.APP_IDPEDIDO", SqlDbType.Int, 4);
paramIdPedido.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramIdPedido);

Do I have to run cmd.ExecuteNonQuery(); to record first what I need and after
run ExecuteReader: something like SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()... to get the ID)

From stored procedure:

INSERT table(fields) VALUES(vars)
SELECT TOP 1 @.id = id FROM table ORDER BY id DESC

I must do all these steps or maybe is there anything less complex to do?

Thanks!

Hello Roberot,

Have a look at the following example:http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

Jeroen Molenaar.

INSERTING TEXT

Hello,
Can someone tell me how to insert text into a table containing a TEXT field?
How to update?
I would like to create a stored procedure that take a text parameter and
inserts it into a new column in my table. Then do the same stored procedure
but this time to update the TEXT field.
HELP!
YamaInsert is simple.
INSERT table_name(int_column, text_column) VALUES(1, ' ... really long text
... ')
Update is a little trickier. In the apps I write, I pull out the whole
value, let the user edit it as a whole, and write the whole value back. As
you might know, you can't append to a TEXT column (so you can't do UPDATE
table_name SET text_column += @.varcharParam), and you can't even manipulate
it locally because you can't have a local variable of type TEXT.
If you are adding things to the end of a text column, you might consider
having a separate related table with comments. That way you can track them
separately, and you don't have to worry about concatenation.
The only time I would ever update is if I have to a batch search and replace
on all rows, e.g. see http://www.aspfaq.com/2445
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Yama" <ykamyar@.grandpacificresorts.com> wrote in message
news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can someone tell me how to insert text into a table containing a TEXT
field?
> How to update?
> I would like to create a stored procedure that take a text parameter and
> inserts it into a new column in my table. Then do the same stored
procedure
> but this time to update the TEXT field.
> HELP!
> Yama
>|||Hi Aaron,
Are you sure the INSERT will work even with more than 8000 characters?
I thought you had to use the WRITETEXT command?
Yama
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23MPf2fy2DHA.2680@.tk2msftngp13.phx.gbl...
> Insert is simple.
> INSERT table_name(int_column, text_column) VALUES(1, ' ... really long
text
> ... ')
> Update is a little trickier. In the apps I write, I pull out the whole
> value, let the user edit it as a whole, and write the whole value back.
As
> you might know, you can't append to a TEXT column (so you can't do UPDATE
> table_name SET text_column += @.varcharParam), and you can't even
manipulate
> it locally because you can't have a local variable of type TEXT.
> If you are adding things to the end of a text column, you might consider
> having a separate related table with comments. That way you can track
them
> separately, and you don't have to worry about concatenation.
> The only time I would ever update is if I have to a batch search and
replace
> on all rows, e.g. see http://www.aspfaq.com/2445
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Yama" <ykamyar@.grandpacificresorts.com> wrote in message
> news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > Can someone tell me how to insert text into a table containing a TEXT
> field?
> > How to update?
> >
> > I would like to create a stored procedure that take a text parameter and
> > inserts it into a new column in my table. Then do the same stored
> procedure
> > but this time to update the TEXT field.
> >
> > HELP!
> >
> > Yama
> >
> >
>|||> Are you sure the INSERT will work even with more than 8000 characters?
Yes, though it depends from where it came. Some providers / clients will
truncate because they don't know how to deal with >255 or >8000.
> I thought you had to use the WRITETEXT command?
I've never used the WRITETEXT command in production systems. www.aspfaq.com
stores articles in a TEXT column and I have no problems using INSERT /
UPDATE through a web interface. All I can suggest is that you keep your
drivers up to date (e.g. MDAC 2.8) and test your environment before taking
my word for it.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||My Dear Friend,
Here is how I settled doing it.
In my ASPX web page I have four fields. A dropdown with a list of all the
letters, a checkbox for activating a letter or setting it to innactive, a
text box for the description of a letter, and another text box for the
letter with multiple line enabled. Hope you like this... :-)
--The table:
CREATE TABLE [dbo].[tblLetter] (
[LetterID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[Letter] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--The stored procedure:
CREATE PROCEDURE SaveLetter
@.Description VARCHAR(100) = '',
@.Active BIT = 1,
@.BlobLetter TEXT = '',
@.blnInsert BIT = 0,
@.LetterNumber INT = NULL,
@.blnDelete BIT = 0
AS
DECLARE @.s BINARY(16)
IF @.blnInsert = 1 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
DECLARE @.ID INT
INSERT INTO tblLetter
(Description, Active, Letter) VALUES (@.Description, @.Active, @.BlobLetter)
SET @.ID = @.@.IDENTITY
SELECT @.s = TEXTPTR( Letter )
FROM tblLetter
WHERE LetterID = @.@.IDENTITY
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnInsert = 0 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
UPDATE tblLetter
SET Description = @.Description , Active = @.Active
WHERE LetterID = @.LetterNumber
SELECT @.s = TEXTPTR(Letter)
FROM tblLetter
WHERE LetterID = @.LetterNumber
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnDelete = 1
BEGIN
DELETE FROM tblLetter
WHERE LetterID = @.LetterNumber
END
GO
Yama Kamyar
Senior Microsoft .NET Consultant
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23SAJJZ62DHA.1740@.TK2MSFTNGP09.phx.gbl...
> > Are you sure the INSERT will work even with more than 8000 characters?
> Yes, though it depends from where it came. Some providers / clients will
> truncate because they don't know how to deal with >255 or >8000.
> > I thought you had to use the WRITETEXT command?
> I've never used the WRITETEXT command in production systems.
www.aspfaq.com
> stores articles in a TEXT column and I have no problems using INSERT /
> UPDATE through a web interface. All I can suggest is that you keep your
> drivers up to date (e.g. MDAC 2.8) and test your environment before taking
> my word for it.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

INSERTING TEXT

Hello,
Can someone tell me how to insert text into a table containing a TEXT field?
How to update?
I would like to create a stored procedure that take a text parameter and
inserts it into a new column in my table. Then do the same stored procedure
but this time to update the TEXT field.
HELP!
YamaInsert is simple.
INSERT table_name(int_column, text_column) VALUES(1, ' ... really long text
... ')
Update is a little trickier. In the apps I write, I pull out the whole
value, let the user edit it as a whole, and write the whole value back. As
you might know, you can't append to a TEXT column (so you can't do UPDATE
table_name SET text_column += @.varcharParam), and you can't even manipulate
it locally because you can't have a local variable of type TEXT.
If you are adding things to the end of a text column, you might consider
having a separate related table with comments. That way you can track them
separately, and you don't have to worry about concatenation.
The only time I would ever update is if I have to a batch search and replace
on all rows, e.g. see http://www.aspfaq.com/2445
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Yama" <ykamyar@.grandpacificresorts.com> wrote in message
news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hello,
> Can someone tell me how to insert text into a table containing a TEXT

---

field?

quote:

---

> How to update?
> I would like to create a stored procedure that take a text parameter and
> inserts it into a new column in my table. Then do the same stored

---

procedure

quote:

---

> but this time to update the TEXT field.
> HELP!
> Yama
>

---

|||Hi Aaron,
Are you sure the INSERT will work even with more than 8000 characters?
I thought you had to use the WRITETEXT command?
Yama
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23MPf2fy2DHA.2680@.tk2msftngp13.phx.gbl...

quote:

---

> Insert is simple.
> INSERT table_name(int_column, text_column) VALUES(1, ' ... really long

---

text

quote:

---

> ... ')
> Update is a little trickier. In the apps I write, I pull out the whole
> value, let the user edit it as a whole, and write the whole value back.

---

As

quote:

---

> you might know, you can't append to a TEXT column (so you can't do UPDATE
> table_name SET text_column += @.varcharParam), and you can't even

---

manipulate

quote:

---

> it locally because you can't have a local variable of type TEXT.
> If you are adding things to the end of a text column, you might consider
> having a separate related table with comments. That way you can track

---

them

quote:

---

> separately, and you don't have to worry about concatenation.
> The only time I would ever update is if I have to a batch search and

---

replace

quote:

---

> on all rows, e.g. see http://www.aspfaq.com/2445
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Yama" <ykamyar@.grandpacificresorts.com> wrote in message
> news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> field?
> procedure
>

---

|||> Are you sure the INSERT will work even with more than 8000 characters?
Yes, though it depends from where it came. Some providers / clients will
truncate because they don't know how to deal with >255 or >8000.

quote:

---

> I thought you had to use the WRITETEXT command?

---

I've never used the WRITETEXT command in production systems. www.aspfaq.com
stores articles in a TEXT column and I have no problems using INSERT /
UPDATE through a web interface. All I can suggest is that you keep your
drivers up to date (e.g. MDAC 2.8) and test your environment before taking
my word for it.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||My Dear Friend,
Here is how I settled doing it.
In my ASPX web page I have four fields. A dropdown with a list of all the
letters, a checkbox for activating a letter or setting it to innactive, a
text box for the description of a letter, and another text box for the
letter with multiple line enabled. Hope you like this... :-)
--The table:
CREATE TABLE [dbo].[tblLetter] (
[LetterID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[Letter] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--The stored procedure:
CREATE PROCEDURE SaveLetter
@.Description VARCHAR(100) = '',
@.Active BIT = 1,
@.BlobLetter TEXT = '',
@.blnInsert BIT = 0,
@.LetterNumber INT = NULL,
@.blnDelete BIT = 0
AS
DECLARE @.s BINARY(16)
IF @.blnInsert = 1 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
DECLARE @.ID INT
INSERT INTO tblLetter
(Description, Active, Letter) VALUES (@.Description, @.Active, @.BlobLetter)
SET @.ID = @.@.IDENTITY
SELECT @.s = TEXTPTR( Letter )
FROM tblLetter
WHERE LetterID = @.@.IDENTITY
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnInsert = 0 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
UPDATE tblLetter
SET Description = @.Description , Active = @.Active
WHERE LetterID = @.LetterNumber
SELECT @.s = TEXTPTR(Letter)
FROM tblLetter
WHERE LetterID = @.LetterNumber
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnDelete = 1
BEGIN
DELETE FROM tblLetter
WHERE LetterID = @.LetterNumber
END
GO
Yama Kamyar
Senior Microsoft .NET Consultant
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23SAJJZ62DHA.1740@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Yes, though it depends from where it came. Some providers / clients will
> truncate because they don't know how to deal with >255 or >8000.
>
> I've never used the WRITETEXT command in production systems.

---

www.aspfaq.com

quote:

---

> stores articles in a TEXT column and I have no problems using INSERT /
> UPDATE through a web interface. All I can suggest is that you keep your
> drivers up to date (e.g. MDAC 2.8) and test your environment before taking
> my word for it.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

---

Inserting SOAP formatted message data into a SQL table -- OPENXML

I have a SOAP formatted message. I am passing this message as a string from
a
C# client to a SQL Stored Procedure.
I would like to read this SOAP formatted message and store the data in the
SOAP message in appropriate columns in a SQL table. How can I achieve this?
Can OPENXML be used for this purpose?
--
ggCould you just store in a nvarchar(max) ?
William Stacey [MVP]
"gudia" <gudia@.discussions.microsoft.com> wrote in message
news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
> Is configuring IIS to be used in conjunction with SQLXML a requirement for
> the question I am asking?
> My eventual goal is to insert the data present in the SOAP message into a
> SQL table.
> Please let me know how I can achieve this goal.
> Thanks|||There are a couple of approaches. You could pass the entire SOAP XML doc to
a stored procedure and use OPENXML to shred it into tables, or you could
create an annotated XSD schema that maps the elements/attributes in your
SOAP message to the tables/column in the database and use the SQLXML Bulk
Load component.
Neither of these approaches requires a SQLXML IIS site.
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"gudia" <gudia@.discussions.microsoft.com> wrote in message
news:B85E4E76-A4FB-49A7-881A-A1795CE80728@.microsoft.com...
Is configuring IIS to be used in conjunction with SQLXML a requirement for
the question I am asking?
My eventual goal is to insert the data present in the SOAP message into a
SQL table.
Please let me know how I can achieve this goal.
Thanks

Inserting Simultaneous tables using Stored Procedure

I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says"Incorrect syntax near '@.WIP'. Must declare the scalar variable "@.ECReason" and"@.WIP"." I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.

I made up the two tables just for testing, they are:

tbltest

ECID – int (PK)

ECReason – varchar(50)

tblWIP

WIPID – int (PK)

ECID – int

WIP - varchar(50)

Operation - varchar(50)

CREATE PROCEDUREtestInsert2(

@.ECReasonas varchar(50)
@.WIPas varchar(50)
@.Operationas varchar(50)
)
AS
BEGIN

DECLARE@.ECIDINT
INSERT INTOtbltest(ECReason)VALUES(@.ECReason)

SELECT@.ECID=@.@.IDENTITY

INSERT INTOtblWIP(ECID, WIP, Operation)
VALUES(@.ECID,@.WIP,@.Operation)

END

Thanks again for the help!

I think you forgot the commas between the proc's arguments. Try the following:

CREATE PROCEDURE testInsert2( @.ECReasonas varchar(50), @.WIPas varchar(50), @.Operationas varchar(50) )ASBEGIN DECLARE @.ECIDINT INSERT INTO tbltest(ECReason)VALUES(@.ECReason)SELECT @.ECID=@.@.IDENTITYINSERT INTO tblWIP(ECID, WIP, Operation)VALUES(@.ECID,@.WIP,@.Operation)END

By the way, I don't know about the "as" in the argument declarations. I never use it. I would normally do the following:

CREATE PROCEDURE testInsert2( @.ECReasonvarchar(50), @.WIPvarchar(50), @.Operationvarchar(50) )AS

HTH.

|||

I took the commas out and then it worked. I know I had them in there before, but I must have changed it along the way. I left the rest of it alone and it worked fine. Thanks for the help!

Inserting Records via Stored Procedure

I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...

here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"

===

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click

Dim connAs SqlConnection =Nothing

Dim transAs SqlTransaction =Nothing

Dim cmdAs SqlCommand

conn =New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)

conn.Open()

trans = conn.BeginTransaction

cmd =New SqlCommand()

cmd.Connection = conn

cmd.Transaction = trans

cmd.CommandText ="usp_InserOrder"

cmd.CommandType = Data.CommandType.StoredProcedure

cmd.Parameters.Add("@.MaterialID", Data.SqlDbType.Int)

cmd.Parameters.Add("@.OpenItem", Data.SqlDbType.Bit)

cmd.Parameters("@.MaterialID").Value = 3

cmd.ExecuteNonQuery()

trans.Commit()

=====

I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.

Please help, I am a newb and lost...as you can tell from my code...

Are you absolutely sure that your stored procedure is called usp_InserOrder. It would make more sense if it was called usp_InsertOrder.|||It is called that. That is what I was thinking at firs, but I checked the database and that is what I named it.|||

I ran it inside of SQL 2005 here is the output...

(0 row(s) returned)

@.RETURN_VALUE = 0

Finished running [dbo].[InserOrder].

|||You code should read -cmd.CommandText ="InserOrder" as that is what it is called in the report you posted above.

Inserting Records from Table type to Temp table

Hi,
I want to insert records from table type to temp table without using cursors
in SQL Server 2000 stored procedure.
Regards,
ShanmugamYou mean like below?
DECLARE @.t TABLE (c1 int)
INSERT INTO @.t (c1) VALUES(1)
CREATE TABLE #t (c1 int)
INSERT INTO #t
SELECT c1 FROM @.t
SELECT * FROM @.t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uma" <uma@.cspl.com> wrote in message news:OZAWvwpHGHA.3936@.TK2MSFTNGP12.phx.gbl...arkred">
> Hi,
> I want to insert records from table type to temp table without using curso
rs
> in SQL Server 2000 stored procedure.
> Regards,
> Shanmugam
>
>

Inserting Records from Table type to Temp table

Hi,
I want to insert records from table type to temp table without using cursors
in SQL Server 2000 stored procedure.
Regards,
ShanmugamInsert into #temp(columns)
Select columns from @.tab
Madhivananl|||Insert into #temp(columns)
Select columns from @.tab
Madhivanan|||Insert into #temp(columns)
Select columns from @.tab
Madhivanan|||Thanks.
Is it possible to give like below.
DECLARE @.a VARCHAR(50)
SET @.a = '#temp'
Insert into @.a(columns)
Select columns from @.tab
any other way?
Regards,
Shanmugam
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1138177782.869581.254030@.z14g2000cwz.googlegroups.com...
> Insert into #temp(columns)
> Select columns from @.tab
> Madhivanan
>|||You'd have to use dynamic SQL for that. Why don't you know the names of your
objects at design time?
ML
http://milambda.blogspot.com/

Inserting records and running a stored procedure at the same time?

Hi there..
The target table got these two 'must be set columns', one called DATASET, an
d
one called LXBENUMMER.
Now, when I want to insert data into this target table I need to fill those
two
columns with the new records I'm inserting. DATASET is easy as it'll always
be a
fixed value, however, LXBENUMMER must be set with a number that comes from a
stored procedure.
If I do an exec sp_xal_seqno 1, 'DAT' I get a number back, which must be put
in
LXBENUMMER with the new record.
An example-
insert tbl1
(dataset,lxbenummer,col1,col2,col3,etc)
select 'DAT',**value from stored procedure**, col1, col2, col3, etc
from tbl2
How do I go about this?
I doubt, therefore I might be.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Also, you do not know that a column is not anything like a field and
that a row is not a record. After 20+ years of doing SQL and charging
a lot of money for consulting work in correctly schemas, this is a
signal that you are really screwed up.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129947794.955269.92220@.g43g2000cwa.googlegroups.com

> Also, you do not know that a column is not anything like a field and
> that a row is not a record. After 20+ years of doing SQL and charging
Well everywhere I look, row and record is the same and is used interchangeab
le.
Why should I think different? I don't see where I wrote field and compared i
t
with a column; but well you do know better.

> a lot of money for consulting work in correctly schemas, this is a
> signal that you are really screwed up.
Dude, you need to take a break from time to time, instead of throwing dirt a
t
those to aren't "worthy" compared to you. I'd see your point better if you h
ad
kept it nicer.
I'm screwed up if I write nutty SQL? Man, if you think that, you /seriously/
need to take a break from anything relating to computers.
Anyway, I shall post again when I have a DDL ready.
--
I doubt, therefore I might be.|||Kim Noer (kn@.nospam.dk) writes:
> The target table got these two 'must be set columns', one called
> DATASET, and one called LXBENUMMER.
> Now, when I want to insert data into this target table I need to fill
> those two columns with the new records I'm inserting. DATASET is easy as
> it'll always be a fixed value, however, LXBENUMMER must be set with a
> number that comes from a stored procedure.
> If I do an exec sp_xal_seqno 1, 'DAT' I get a number back, which must be
> put in LXBENUMMER with the new record.
General comment: don't use sp_ as the leading charcters in the name of
your objects. This prefix is reserved for SQL Server, and SQL Server will
first look in master for these objects.

> An example-
> insert tbl1
> (dataset,lxbenummer,col1,col2,col3,etc)
> select 'DAT',**value from stored procedure**, col1, col2, col3, etc
> from tbl2
> How do I go about this?
There are number of options, of which some requires you to change
the procedure. I happen to have an article about this on my web site:
http://www.sommarskog.se/share_data.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||From your attitude i'd be suprised if anybody outside of education has hired
you in the past couple of years!
You need to bring your skills up-to-date with what business wants now, not
15 years ago.
Times have significantly changed and you seem to have been left behind.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129947794.955269.92220@.g43g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Also, you do not know that a column is not anything like a field and
> that a row is not a record. After 20+ years of doing SQL and charging
> a lot of money for consulting work in correctly schemas, this is a
> signal that you are really screwed up.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129947794.955269.92220@.g43g2000cwa.googlegroups.com
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
CREATE TABLE [tbl1] (
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[LXBENUMMER] [int] NOT NULL ,
[col1] [varchar] (30) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [XALSEQ] (
[SEQID] [int] NOT NULL ,
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[SEQNO] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO XALSEQ
VALUES (0,'DAT',1000)
GO
INSERT INTO tbl1
VALUES ('DAT',1000,'somerandomtext')
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_xal_seqno @.increment INT, @.dataset CHAR(3) AS
BEGIN TRAN
UPDATE XALSEQ SET SEQNO = SEQNO + @.increment
WHERE DATASET = @.dataset AND SEQID = 0
SELECT SEQNO - @.increment FROM XALSEQ
WHERE DATASET = @.dataset AND SEQID = 0
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I doubt, therefore I might be.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F7D40BB5DE7Yazorman@.127.0.0.1

> General comment: don't use sp_ as the leading charcters in the name of
> your objects. This prefix is reserved for SQL Server, and SQL Server
> will first look in master for these objects.
Aye, I name all those I create differently, but alas, I did not get to chose
the
name of the stored procedure, this one comes from MBS themself.

> There are number of options, of which some requires you to change
> the procedure. I happen to have an article about this on my web site:
> http://www.sommarskog.se/share_data.html.
In my second post to Celko, I've included the structure of the tables, the S
P
and tiny amount of sample data, if that helps point me in the correct direct
ion
(I'm going to read up on your article anyway though).
Basically, the whole purpose is to make sure that every single row (or is it
record?) get their very own unique number. It would be exceedingly lovely if
any
solution to this particular problem could be made generic if possible.
Thanks in advance.
--
I doubt, therefore I might be.|||Kim Noer (kn@.nospam.dk) writes:
> Aye, I name all those I create differently, but alas, I did not get to
> chose the name of the stored procedure, this one comes from MBS
> themself.
MBS? That's some third-party software?

> Basically, the whole purpose is to make sure that every single row (or
> is it record?) get their very own unique number. It would be exceedingly
> lovely if any solution to this particular problem could be made generic
> if possible.
It would certainly be more convenient if it was an OUTPUT parameter
rather than a result ser. If you can't change the procedure, you will
have to use INSERT EXEC.
Besides this looks funny:
CREATE TABLE [XALSEQ] (
[SEQID] [int] NOT NULL ,
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[SEQNO] [int] NOT NULL
) ON [PRIMARY]
GO
It doesn't have a primary key?
If there are no indexes on the table, this means that SQL Server will
have to take a out a table lock to give you a sequence number, and thus
no other will be able to get a sequence number simultaneously.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> MBS? That's some third-party software?
Microsoft Business Solution, they bought a company named Navision, which
makes applications that primarily talks flat fileish. In my case the
application uses cursors, a /lot/ of cursors. They do plan to change this,
but that's 2+ years in the future atleast.
I'm trying to insert data from the "outside" of this application, which
means that I can use all kinds of RDBMS tricks.

> It would certainly be more convenient if it was an OUTPUT parameter
> rather than a result ser. If you can't change the procedure, you will
> have to use INSERT EXEC.
I'm sure can write a new, as long as I retain the functionality. Which one
of your methods would you use then?

> It doesn't have a primary key?
> If there are no indexes on the table, this means that SQL Server will
> have to take a out a table lock to give you a sequence number, and
> thus no other will be able to get a sequence number simultaneously.
Maybe they forgot, or the application might not be able to handle such a
situation.
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783|||Kim Noer (kn@.nospam.dk) writes:
> I'm sure can write a new, as long as I retain the functionality. Which one
> of your methods would you use then?
OUTPUT parameter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

inserting record into second database from a stored procedure in first database

is it possible to insert record into second database from a stored procedure which is in first database?
If the store proc include an INSERT INTO statement. Run a search for INSERT INTO in SQL Server BOL(books online). Hope this helps.

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem