Hide parameter toolbar and send par via querystring?

Hide parameter toolbar and send par via querystring?
I have some stored proceures that receive some parameters and output some
querys. I dont want the user to select parameters because I need to gice
those parameters via query string from My Applications
How can I achieve tha?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 3.
MIEMBRO ACTIVO DE ALIANZADEV
http://spaces.msn.com/members/extremed/Luis,
Check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_959e.asp
Adrian M.
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:%23n13jSW$EHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hide parameter toolbar and send par via querystring?
>
> I have some stored proceures that receive some parameters and output some
> querys. I dont want the user to select parameters because I need to gice
> those parameters via query string from My Applications
>
> How can I achieve tha?
>
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>

Hide Column Header in the Results

I have a stored procedure and I want to save the results in a file. I don't
want the column header to be in the file. I need to generate the file daily.
It has just one column. Example the results is showing:
Msg
{1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111XXX@.MYE@.-}
{2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111X@.MYE@.-}
I want the following results without the Header and extra line in the file.
How can I achive this. I have tried isql and isql/w but no success yet.
{1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111XXX@.MYE@.-}
{2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111X@.MYE@.-}
Use osql instead, with option -h -1.
Example:
C:\TEMP>osql -Spivotalr5 -E -Q"select top 5 orderid, orderdate from
northwind..o
rders" -h-1
AMB
"Fraz" wrote:

> I have a stored procedure and I want to save the results in a file. I don't
> want the column header to be in the file. I need to generate the file daily.
> It has just one column. Example the results is showing:
> Msg
> --
> {1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111XXX@.MYE@.-}
> {2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111X@.MYE@.-}
> I want the following results without the Header and extra line in the file.
> How can I achive this. I have tried isql and isql/w but no success yet.
> {1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111XXX@.MYE@.-}
> {2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111X@.MYE@.-}
>

Hide Column Header in the Results

I have a stored procedure and I want to save the results in a file. I don't
want the column header to be in the file. I need to generate the file daily.
It has just one column. Example the results is showing:
Msg
--
& #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
& #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111X@.MYE@.-}
I want the following results without the Header and extra line in the file.
How can I achive this. I have tried isql and isql/w but no success yet.
& #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
& #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111X@.MYE@.-}Use osql instead, with option -h -1.
Example:
C:\TEMP>osql -Spivotalr5 -E -Q"select top 5 orderid, orderdate from
northwind..o
rders" -h-1
AMB
"Fraz" wrote:

> I have a stored procedure and I want to save the results in a file. I don'
t
> want the column header to be in the file. I need to generate the file dail
y.
> It has just one column. Example the results is showing:
> Msg
> --
> & #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111X@.MYE@.-}
> I want the following results without the Header and extra line in the file
.
> How can I achive this. I have tried isql and isql/w but no success yet.
> & #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111X@.MYE@.-}
>

Hide Column Header in the Results

I have a stored procedure and I want to save the results in a file. I don't
want the column header to be in the file. I need to generate the file daily.
It has just one column. Example the results is showing:
Msg
--
{1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111XXX@.MYE@.-}
{2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111X@.MYE@.-}
I want the following results without the Header and extra line in the file.
How can I achive this. I have tried isql and isql/w but no success yet.
{1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111XXX@.MYE@.-}
{2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111X@.MYE@.-}Use osql instead, with option -h -1.
Example:
C:\TEMP>osql -Spivotalr5 -E -Q"select top 5 orderid, orderdate from
northwind..o
rders" -h-1
AMB
"Fraz" wrote:
> I have a stored procedure and I want to save the results in a file. I don't
> want the column header to be in the file. I need to generate the file daily.
> It has just one column. Example the results is showing:
> Msg
> --
> {1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111XXX@.MYE@.-}
> {2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111X@.MYE@.-}
> I want the following results without the Header and extra line in the file.
> How can I achive this. I have tried isql and isql/w but no success yet.
> {1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111XXX@.MYE@.-}
> {2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111X@.MYE@.-}
>sql

Hide aspnet_* objects

Hello, I would like to not have to see the aspnet_ tables and stored procedures that are created when using the Membership, roles, and personalization. Currently I have to suffer seeing the handful of tables and 40+ stored procedures in both the Visual Studio and the SQL Management tool. I have found that on a 2000 SQL Server I can execute a command that forces objects to be created as system objects. If I execute this before creating the objects they become system objects and I don't have to see them any longer.

However, this trick doesnot work in SQL Server 2005. So, I would like to know either 1) is there an easy way to hide these objects or 2) is there a way to change the objects to system objects in SQL Server 2005?

CodeGuy

Right click on the tables folder in Management Studio and choose Filter -> Filter settings.

Now create a filter using name NOT CONTAINING aspnet_

Hope it helps

|||

Klaus, thank you for mentioning that. I actually found reference to that as well. Two downsides to that approach, first you have to set the filter upEVERY TIME, because it's not saved. Second, that only helps me a small amount because it only works in the Management Studio.

CodeGuy

|||

Another option would be to use a separate database for the aspnet_* tables... but that has drawbacks aswell. Hope you find a good solution and keep us posted.

|||

Klaus, thank you for the ideas. You suggestion definitely would work, however, I'd prefer to have these objects in the same database for portability reasons.

Anyone else have any good ideas?

hi this is dileep

my query is
when i enter a value in the textbox like sports | cricket | footbal
please give me
insert query :: the values will be stored into the database in 3 different rows
no matter the no of rows

in .net

Quote:

Originally Posted by dileepk

my query is
when i enter a value in the textbox like sports | cricket | footbal
please give me
insert query :: the values will be stored into the database in 3 different rows
no matter the no of rows

in .net

Hi there,

Answer is not that easy my fren. Pls show us some of your work beforehand, will try to validate your SQL statement. Good luck & Take care.

hi i want to know how to write stored procedure ..then i have to include (IF condition ) w

hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP ..

let me this post ..............anybody ????

CREATEPROCEDURE name_of_procedure
-- Add the parameters for the stored procedure here
@.passedparam1 int,
@.passedparam2 smalldatetime

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNTON;

-- Insert statements for procedure here

SELECT [COLOFINTS], [OTHERCOL] FROM MYDBNAME WHERE ([COLOFINTS] = @.passedparam1 AND [DATEENTERED] > @.passedparam2

END

|||

CREATEPROCEDURE name_of_procedure
-- Add the parameters for the stored procedure here
@.passedparam1 int,
@.passedparam2 smalldatetime

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNTON;

-- Insert statements for procedure here

IF @.passedparam2 < GETDATE()

SELECT [COLOFINTS], [OTHERCOL]FROM MYDBNAMEWHERE ([COLOFINTS] = @.passedparam1 AND [DATEENTERED] > @.passedparam2)

ELSE

SELECT [COLOFINTS], [OTHERCOL]FROM MYDBNAMEWHERE [COLOFINTS] = @.passedparam1

END

|||

Just to clarify something that clevesteve wrote, if the IF has > 1 statement, you must wrap it in BEGIN END, for example,

IF @.passedparam2 < GETDATE()
BEGIN
SELECT [COLOFINTS], [OTHERCOL] FROM MYDBNAME WHERE ([COLOFINTS] = @.passedparam1 AND [DATEENTERED] > @.passedparam2)
...some other statement
END
ELSE
BEGIN
SELECT [COLOFINTS], [OTHERCOL] FROM MYDBNAME WHERE [COLOFINTS] = @.passedparam1
..some other statemetn
END

|||

hi thaka for reply

please send me a any tutorial of these stored procudre ...bcz am a beginner of these

|||

You can find many such on the web but here's a start on using T-SQL http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

For more, just google "sql server how to write stored procedures"

Sql server 2005 also supports writing procs in .net but I have no experience myself with this

Hi friend

I have a stored procedure which accepts 3 arguments that are used in the WHERE clause of a SELECT Statement

CREATE PROCEDURE [dbo].[MyProcedure] @.argYear INT, @.argMonth INT, @.argDay INT
AS

SELECT *

FROM MyData

WHERE
MyData.Year = @.argYear AND MyData.Month = @.argMonth AND MyData.Day = @.argDay

The problem that I am having is @.argDay is an "optional" argument. If @.argDay is NULL then I want to basically ignore the "AND MyData.Day = @.argDay" condition.

Is there an easier way to do this than:

IF @.argDay is NULL

SELECT *

FROM MyData

WHERE

MyData.Year = @.argYear AND MyData.Month = @.argMonth

ELSE

SELECT *

FROM MyData

WHERE

MyData.Year = @.argYear AND MyData.Month = @.argMonth AND MyData.Day = @.argDay

END IF

Thanks

You can OR in a not null condition to handle it; perhaps something like:

Code Snippet

SELECT *
FROM MyData
WHERE MyData.Year = @.argYear
AND MyData.Month = @.argMonth

AND ( MyData.Day = @.argDay or @.argDay is null )

|||

One method is something like this:

WHERE ( MyData.Year = @.argYear

AND MyData.Month = @.argMonth

AND ( MyData.Day = @.argDay

OR MyData.Day IS NULL

)

)

|||

Whatever you are using it is the best method. If you add expression on variable the Index Scan will be forced on your query..

The current query is neat & clean, will give a best performance. Stick it there itself.

|||Thanks for all your help - it works!
|||

I want to learn ASP.Net with c# from the basic stage.If you get some useful links and tutorials,please send me.

Hey Ive got a homework problem Im working on and am stumped.

It goes like the following:
Use AdventureWorks database and HumanResources.Department table.

Create a stored procedure called spDepartmentAddUpdate. This procedure
accepts two parameters: Name, and GroupName. The data types are
VarChar(50), and VarChar(50) respectively. Define logic in this
procedure to check for an existing Department record with the same Name.
If the department record exists, update the GroupName and ModifiedDate.
Otherwise, insert a new department record.

A.Execute your stored procedure to show that the insert logic works.
B.Execute your stored procedure to show that the update logic works.

Any hints from the wizards out there would be greatly appreciated!

*** Sent via Developersdex http://www.developersdex.com ***Nep Tune (neptuneca@.go.com) writes:

Quote:

Originally Posted by

It goes like the following:
Use AdventureWorks database and HumanResources.Department table.
>
Create a stored procedure called spDepartmentAddUpdate. This procedure
accepts two parameters: Name, and GroupName. The data types are
VarChar(50), and VarChar(50) respectively. Define logic in this
procedure to check for an existing Department record with the same Name.
If the department record exists, update the GroupName and ModifiedDate.
Otherwise, insert a new department record.
>
A. Execute your stored procedure to show that the insert logic works.
B. Execute your stored procedure to show that the update logic works.
>
Any hints from the wizards out there would be greatly appreciated!

If you are not able to carry out your homework assignments, you should
talk to the teacher, rather than sneak behind his back. Your teacher
knows what you are supposed to know, and what you yet have to learn.

But permit me to not that the parameters to the procedure should really
be nvarchar(50) and not varchar(50) to go along with the table definition.

--
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|||YOU WRITE:
you should talk to the teacher, rather than sneak behind his back. Your
teacher knows what you are supposed to know, and what you yet have to
learn.
I WRITE:
Well, thanks for the suggestions. You and my teacher ought to team
teach; I'd learn about the same from both of you: NOTHING!

*** Sent via Developersdex http://www.developersdex.com ***|||Nep Tune (neptuneca@.go.com) writes:

Quote:

Originally Posted by

I WRITE:
Well, thanks for the suggestions. You and my teacher ought to team
teach; I'd learn about the same from both of you: NOTHING!

Ah, but there is a difference! He is paid to teach you to nothing.

On a more serious note, newsgroups are not the best place to learn. If
someone asks a "how do I write this query", it can be fairly simple to
write that query, provided that the question is clear enough. But
explaining what is actually happening can be a lot more difficult, and
I usually don't do that in my posts. That's OK if the poster has some
experience and has run into a little more difficult problem at work.
If he is interested he will try to understand the solution and use it.

If he is not interested, well at least he got help with doing his part
in his work. After all, it could be that SQL is something he does left-
hand and his main skills are with VB, C++ or whatever. And I would expect
him to be able to write the procedure you were asked to.

Anyway, some hints: you will need to use INSERT and UPDATE. You will also
learn to master IF EXISTS. If you ever grow up to be a developer, you
will write tons of such procedures in your career! :-)

--
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|||On 12 Aug 2006 13:59:56 GMT, Nep Tune <neptuneca@.go.comwrote:

Quote:

Originally Posted by

>YOU WRITE:
>you should talk to the teacher, rather than sneak behind his back. Your
>teacher knows what you are supposed to know, and what you yet have to
>learn.
>I WRITE:
>Well, thanks for the suggestions. You and my teacher ought to team
>teach; I'd learn about the same from both of you: NOTHING!

What the hell do you expect, when your initial post amounts to "please
do my entire assignment for me"? Now if you demonstrated some prior
effort on your own part - e.g. "I tried so-and-so, but it gets
such-and-such wrong, and I don't know how to proceed from there" -
then I bet people would be a lot more willing to help out.|||> Create a stored procedure called spDepartmentAddUpdate. <<

The "sp-" prefix violates ISO-11179 rules. Even Microsoft gave up on
camelCase because it is a bitch to read.

Quote:

Originally Posted by

Quote:

Originally Posted by

>This procedure accepts two parameters: Name, and GroupName. <<

"name" is too vague to be a data element -- naem of what?

Quote:

Originally Posted by

Quote:

Originally Posted by

>The data types are VarChar(50), and VarChar(50) respectively. <<

See prior remark about camelCase. This should be NVARCHAR(n) where (n)
was actually researched . 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.

Quote:

Originally Posted by

Quote:

Originally Posted by

> Define logic in this procedure to check for an existing Department record [sic] with the same Name [was this the key in the DDL you did not post?]. If the department record [sic] exists, update the GroupName and ModifiedDate. Otherwise, insert a new department record [sic]. <<

Rows are not records; fields are not columns; tables are not files.
This is foundations.

No auditor will allow you to put "modified_date" in a table. Audit
trails have to be separate from the data by law (see SOX compliance
rules), by GAAP and by common sense.

Quote:

Originally Posted by

Quote:

Originally Posted by

>Any hints from the wizards out there would be greatly appreciated! <<

At every school I have taught or at which I have been a student,
presenting the work of other people as your own will get you kicked
out. So far, I have ended the college education of two cheaters, one
in New Zealand and one in the US by reporting them to their
departments. Is that enough of a hint?|||--CELKO-- (jcelko212@.earthlink.net) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>The data types are VarChar(50), and VarChar(50) respectively. <<

>
See prior remark about camelCase. This should be NVARCHAR(n) where (n)
was actually researched . Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc.

No, Joe. Get yourself a copy of SQL 2005 and install the AdventureWorks
database. NepTune may be cheating with his homework. But he gave all
necessary information to solve the problem.

Quote:

Originally Posted by

No auditor will allow you to put "modified_date" in a table. Audit
trails have to be separate from the data by law (see SOX compliance
rules), by GAAP and by common sense.

I don't think AdventureWorks Bicycles has any auditor...

--
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

HexToInt and HexToSmallInt

Inspired/challenged by Hans Lindgren's stored procedures of these same names
posted on SQLServerCentral, I created these. Note that they produce strange
results on non-hexadecimal strings, and may have issues with byte-ordering
in some architectures (but Itanium is little-endian like x86 and x64,
right?).

How do they work? well, the distance between one after '9' (':') and 'A' is
7 in ASCII. Also, if I subtract 48 from an upper-cased hex-digit, digit & 16
will always be equal to 16. So I can mask that bit out, shift it down 4 bits
(/16), multiply by 7, subtract from the original value, and come up with a
value from 0 to 15. This can be done on all 8 digits in parallel, as you can
see below.

I use CAST(CAST('1234ABCD' AS BINARY(8))AS BIGINT) to put the hex value
1234ABCD into a number I can manipulate, then subtract the value '00000000'
(CAST(0x3030303030303030 AS BIGINT)), then mask out the hex overflow bits,
shift right, multiply by 7, subtract to make the values 0x010203040A0B0C0D,
then I shift the bits into the proper places and add.

It's probably easier in assembly language than SQL, but oh well.

It's only about 20% faster than Hans's series of CHARINDEX calls.

CREATE FUNCTION dbo.HexToINT
(
@.Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
DECLARE @.I BIGINT
SET @.I = CAST(CAST(RIGHT( UPPER( '00000000' + @.Value ) , 8 )
AS BINARY(8)) AS BIGINT) - 3472328296227680304
SET @.I=@.I-((@.I/16)&CAST(72340172838076673 AS BIGINT))*7
RETURN (
(@.I&15)
+((@.I/16)&240)
+((@.I/256)&3840)
+((@.I/4096)&61440)
+((@.I/65536)&983040)
+((@.I/1048576)&15728640)
+((@.I/16777216)&251658240)
+((@.I/72057594037927936)*268435456) -- cause an OF if > 0x80000000
)

END
GO

CREATE FUNCTION dbo.HexToSMALLINT
(
@.Value VARCHAR(4)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @.I INT
SET @.I = CAST(CAST(RIGHT( UPPER( '0000' + @.Value ) , 4 )
AS BINARY(4)) AS INT) - 808464432
SET @.I=@.I-(@.I&269488144)*7/16
RETURN (
@.I&255
+(@.I&65280)/16
+(@.I&16711680)/256
+(@.I&2130706432)/4096
)
END
GOThe revised versions below will allow negative numbers, eg.
HexToINT('80000000')

--

Inspired/challenged by Hans Lindgren's stored procedures of these same names
posted on SQLServerCentral, I created these. Note that they produce strange
results on non-hexadecimal strings, and may have issues with byte-ordering
in some architectures (but Itanium is little-endian like x86 and x64,
right?).

How do they work? well, the distance between one after '9' (':') and 'A' is
7 in ASCII. Also, if I subtract 48 from an upper-cased hex-digit, digit & 16
will always be equal to 16. So I can mask that bit out, shift it down 4 bits
(/16), multiply by 7, subtract from the original value, and come up with a
value from 0 to 15. This can be done on all 8 digits in parallel, as you can
see below.

I use CAST(CAST('1234ABCD' AS BINARY(8)) AS BIGINT) to put the
string of hexadecimal digit characters 1234ABCD into a number I can
manipulate,
then subtract the value '00000000' (CAST(0x3030303030303030 AS BIGINT)),
then mask out the hex overflow bits, shift right 4 places (/16), multiply by
7,
subtract to make the values 0x010203040A0B0C0D,
then I shift the bits into the proper places and add. to result in
0x1234ABCD, CAST AS INT.

alter FUNCTION dbo.HexToSMALLINT
(
@.Value VARCHAR(4)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @.I INT
SET @.I = CAST(CAST(RIGHT( UPPER( '0000' + @.Value ) , 4 )
AS BINARY(4)) AS INT) - 808464432
SET @.I=@.I-(@.I&269488144)*7/16
RETURN CAST(CAST(
(@.I&15)
+((@.I/16)&240)
+((@.I/256)&3840)
+((@.I/4096)&61440)
AS BINARY(2))AS SMALLINT)
END
GO

alter FUNCTION dbo.HexToINT
(
@.Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
DECLARE @.I BIGINT
SET @.I = CAST(CAST(RIGHT( UPPER( '00000000' + @.Value ) , 8 )
AS BINARY(8)) AS BIGINT) - 3472328296227680304
SET @.I=@.I-((@.I/16)&CAST(72340172838076673 AS BIGINT))*7
RETURN CAST(CAST(
(@.I&15)
+((@.I/16)&240)
+((@.I/256)&3840)
+((@.I/4096)&61440)
+((@.I/65536)&983040)
+((@.I/1048576)&15728640)
+((@.I/16777216)&251658240)
+(CAST(@.I/72057594037927936 AS BIGINT)*268435456)
AS BINARY(4))AS INT)
END
GO

Hewlp needed creating a stored proceedure

Can someone please help me...I have created a DNN module that works onthe test site but when I upload the module zip to a new site I get anerror on creating my stored proceedure as follows:

StartJobBegin Sql executionInfoExecuting 01.00.00.SqlDataProviderStartJobStart Sql execution: 01.00.00.SqlDataProvider fileFailureSQL Execution resulted infollowing Exceptions: System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@.Str_Title'. Line 51: Incorrect syntax near '@.Str_Title'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @.PortalID int, @.SortOrder tinyint = NULL, @.Str_Title varchar(100) = '', @.Str_Text varchar(100) = '' AS IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') AND Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') OR Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESCEndJobEnd Sql execution: 01.00.00.SqlDataProvider file

The SP looks like this:

/* --------------------------
/ ListTAS_Journal
/ -------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
@.PortalID int,
@.SortOrder tinyint = NULL,
@.Str_Title varchar(100) = '',
@.Str_Text varchar(100) = ''
AS

IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = ''

SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @.PortalID AND
(Title like COALESCE('%' + @.Str_Title + '%' ,Title , '') AND
Text like COALESCE('%' + @.Str_Text + '%' ,Text, ''))

ORDER BY
(CASE
WHEN @.SortOrder = 1 THEN DateAdded
WHEN @.SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @.PortalID AND
(Title like COALESCE('%' + @.Str_Title + '%' ,Title , '') OR
Text like COALESCE('%' + @.Str_Text + '%' ,Text, ''))

ORDER BY
(CASE
WHEN @.SortOrder = 1 THEN DateAdded
WHEN @.SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This SP works on the test site.
Any help would be creatly apreciated

Mark

CREATE PROCEDURE dbo. ListTAS_Journal @.PortalID int, @.SortOrder tinyint = NULL, @.Str_Title varchar(100) = '', @.Str_Text varchar(100) = '' AS IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') AND Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') OR Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC

Notice, your pluses are missing.

|||Hi Thanks for the reply,

Yes the Pluses are in the sql file if you look at my first post but theerror is reporting they are not there so DNN must be stripping them outfor some reason!

Mark

Heterogeneous Query and ANSI_NULLS/_WARNINGS

I'm trying to create a stored proc that creates a link to a remote server and executes
other stored procedures as needed. Each of the sub-procs will be running Heterogeneous Queries.
I'm having trouble creating these sub-procs, I get the error message that states
Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
I have the remote server linked when I try to create the procs.
I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these need to be set on the linked server.
I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
but got an 'invalid options' error.
Does this need to be set as server defaults on each server or can I make these settings in a usp?
After the procs are created and I create the link at runtime can I expect some problems?
Any suggestions/links would be greatly appreciated.
I am also an Application developer who gets roped into this DBA stuff as needed. I'm the closest they have here
to a DBA and I cant find any decent answers in 'books on-line' or my books on-desk.
'Ack!'
troy
Troy,
The settings need to be set when you create your stored
procedures - not in the stored procedures but for the
connection creating the stored procedures - so that the
stored procedures are created with these settings. Something
along the lines of:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Fri, 9 Apr 2004 13:06:06 -0700, "Troy"
<anonymous@.discussions.microsoft.com> wrote:

>I'm trying to create a stored proc that creates a link to a remote server and executes
>other stored procedures as needed. Each of the sub-procs will be running Heterogeneous Queries.
>I'm having trouble creating these sub-procs, I get the error message that states
> Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
>I have the remote server linked when I try to create the procs.
>I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these need to be set on the linked server.
>I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
> EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
>but got an 'invalid options' error.
>Does this need to be set as server defaults on each server or can I make these settings in a usp?
>After the procs are created and I create the link at runtime can I expect some problems?
>Any suggestions/links would be greatly appreciated.
>I am also an Application developer who gets roped into this DBA stuff as needed. I'm the closest they have here
>to a DBA and I cant find any decent answers in 'books on-line' or my books on-desk.
>'Ack!'
>troy
|||Thanks Sue, that took care of it.
|||Troy / Sue
I'm experiencing the same issue raised in this thread. Sounds like you
guys figured out what the problem was but the solution is not in this
thread. Any chance you can share your wisdom?
imalik
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message177518.html
|||If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?
|||Hi all!
I have the same problem with a view I'm trying to use with Crystal Reports.
I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but it's still doesn't work...
How can I solve it please?

Quote:

Originally Posted by Sue Hoegemeier

If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?

|||I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote:
|||

Quote:

Originally Posted by Sue Hoegemeier

I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote:

I fix my problem by setting ANSI_NULL/WARNINGS in my ODBC source. Thx anyway

Heterogeneous Query and ANSI_NULLS/_WARNINGS

I'm trying to create a stored proc that creates a link to a remote server an
d executes
other stored procedures as needed. Each of the sub-procs will be running He
terogeneous Queries.
I'm having trouble creating these sub-procs, I get the error message that st
ates
Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
I have the remote server linked when I try to create the procs.
I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these
need to be set on the linked server.
I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
but got an 'invalid options' error.
Does this need to be set as server defaults on each server or can I make the
se settings in a usp?
After the procs are created and I create the link at runtime can I expect so
me problems?
Any suggestions/links would be greatly appreciated.
I am also an Application developer who gets roped into this DBA stuff as nee
ded. I'm the closest they have here
to a DBA and I cant find any decent answers in 'books on-line' or my books o
n-desk.
'Ack!'
troyThanks Sue, that took care of it.|||Troy / Sue
I'm experiencing the same issue raised in this thread. Sounds like you guys
figured out what the problem was but the solution is not in this thread. Any
chance you can share your wisdom?|||If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?|||Hi all!
I have the same problem with a view I'm trying to use with Crystal
Reports.
I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
it's still doesn't work...
How can I solve it please?
Sue Hoegemeier;3612624 Wrote:[vbcol=seagreen]
> If you have the same issues with a stored procedure, the
> answer is in the post in this thread. You need to create the
> procedure with the appropriate setting e.g.
> SET ANSI_NULLS ON
> GO
> SET ANSI_WARNINGS ON
> GO
> CREATE PROCEDURE YourStoredProc...etc.
>
> -Sue
> On Mon, 10 Jan 2005 04:45:31 -0600, imalik
> <imalik.1inb94@.mail.webservertalk.com> wrote:
>
> you
zen69|||I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote:

Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS

I have stored procedure:

EXEC sp_addlinkedsrvlogin @.FailedRegionServerName, 'false', NULL, 'sa', 'pass'

DECLARE @.a varchar(100)
SET @.a = @.FailedRegionServerName + '.Ithalat.dbo.Product'

DECLARE @.s varchar(100)
SET @.s = ' SELECT * FROM ' + @.a
EXEC ( @.s )

When I execute it I get the error:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

Then I tried:

DECLARE @.s varchar(300)
SET @.s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @.a
EXEC ( @.s )

I still got the error.

WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

You will have to create the stored procedures with those ANSI Settings:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

CREATE PROCEDURE Name (...)

HTH, Jens K. Suessmeyer.

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

Well, your suggestion had been tried and didn't work

All I need is to select some data from another server and insert into local server

|||

In Query Analyzer

Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;

Execute and then remove these two lines of code.

You can now write your create procedure code in Analyzer

SQL server will remember these ansi settings evertime your procedure is subsequently called

|||

hi,

did u get the solution for ur problem? i am facing the same error..

|||

Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....

-Sue

Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS

I have stored procedure:

EXEC sp_addlinkedsrvlogin @.FailedRegionServerName, 'false', NULL, 'sa', 'pass'

DECLARE @.a varchar(100)
SET @.a = @.FailedRegionServerName + '.Ithalat.dbo.Product'

DECLARE @.s varchar(100)
SET @.s = ' SELECT * FROM ' + @.a
EXEC ( @.s )

When I execute it I get the error:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

Then I tried:

DECLARE @.s varchar(300)
SET @.s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @.a
EXEC ( @.s )

I still got the error.

WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

You will have to create the stored procedures with those ANSI Settings:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

CREATE PROCEDURE Name (...)

HTH, Jens K. Suessmeyer.

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

Well, your suggestion had been tried and didn't work

All I need is to select some data from another server and insert into local server

|||

In Query Analyzer

Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;

Execute and then remove these two lines of code.

You can now write your create procedure code in Analyzer

SQL server will remember these ansi settings evertime your procedure is subsequently called

|||

hi,

did u get the solution for ur problem? i am facing the same error..

|||

Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....

-Sue

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

I have a SQL200 stored proc that gives me the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query." when I try to execute it through the query analyzer.

I was able to create the stored proc fine but when I try to execute it through the query analyzer it gives me the above error. I do have Link Server select inside the stored proc. I have to turn of warnings inside the stored proc in order for it to not crash my vb6 recordset by putting in the SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF
or else my vb6 recordset crashes.

When I created the sproc, I did what every one was telling me to do in the forums by putting in the

SET ANSI_WARNINGS ON
Go
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure usp_SprocName

AS
SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF

Can someone help me?this is a confirmed bug of sql2k. do u read this link?
http://support.microsoft.com/kb/296769/en-us|||I am not using the Enterprise Manager like the artical says. I am using the Query Analyser. I don't have a problem creating the stored proc. I only get the error when I try to execute the stored proc.|||I assume this to be the code that you are using to create the stored procedure.

SET ANSI_WARNINGS ON
Go
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure usp_SprocName

AS
SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF


OK. But why haven't you done what was mentioned in the Microsoft Support Article? That would seem like the only logical step to me.

The error occurs because, in order to execute this type of query, the stored procedure definition must contain the definitions mentioned in the article. Your stored procedure sets the relative option to 'ON' but then immediately afterwards sets it to OFF. Try reading your stored procedure definition again...

Regards,|||The reason for turning the warnings off immediately is because the stored proc. is executed in VB6 to populate a record set object. If after the creation of sproc, NSI_WARNINGS, NOCOUNT, ANSI_NULLS are on, then the recordset object crashes. The article you sent me, talks about how to fix a problem creating a stored sproc. After the stored proc is created, the warnings, nocount and ANSI_NULLS can be turned off. I have other sprcos that work this way with different linked servers. For some reason this one does not.

Heterogeneous queries

Hi Guys,

I have one stored procedure on SQL 6.5 and retrieving data from SQL
2000. I have defined a linked server on SQL 6.5. But
I am getting following error:-

"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
to be set for the connection. This ensures consistent query semantics.
Enable these options and then reissue your query."

I dropped and re-create the stored procedure after adding the SET
ANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still getting
this error. I have created this sp on ISQL/W not in Enterprise Manager.

Can anyone please let me know, how to fix this problem.

Thanks
AdnanAdnan (adnanjamil58@.yahoo.ca) writes:
> I have one stored procedure on SQL 6.5 and retrieving data from SQL
> 2000. I have defined a linked server on SQL 6.5. But
> I am getting following error:-
> "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
> to be set for the connection. This ensures consistent query semantics.
> Enable these options and then reissue your query."
> I dropped and re-create the stored procedure after adding the SET
> ANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still getting
> this error. I have created this sp on ISQL/W not in Enterprise Manager.
> Can anyone please let me know, how to fix this problem.

The setting of ANSI_NULLS is saved with the procedure, why it does
not help setting ANSI_NULLS within the procedure.

ANSI_NULLS is on by default with most interfaces - but not if you use
DB-Library, and the 6.5 tools uses DB-Library. If you insist on using
6.5 tools, be sure to always include this:

SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSACTIONS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF

then you get the same settings as in the SQL 2000 tools.

Certainly far more easier is to use Query Analyzer. (You mentioned
Enterprise Manager. If you mean the 6.5 tool, it has the same issue
as ISQL/W. It you mean EM 2000, this is a poor tool for maintaining
stored procedures.)

--
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 (esquel@.sommarskog.se) writes:
> The setting of ANSI_NULLS is saved with the procedure, why it does
> not help setting ANSI_NULLS within the procedure.

A clarification: the setting of ANSI_NULLS that applies for a stored
procedure, is the setting that was in effect when you created the procedure.
The rest below applies as before:

> ANSI_NULLS is on by default with most interfaces - but not if you use
> DB-Library, and the 6.5 tools uses DB-Library. If you insist on using
> 6.5 tools, be sure to always include this:
> SET ANSI_DEFAULTS ON
> SET IMPLICIT_TRANSACTIONS OFF
> SET CURSOR_CLOSE_ON_COMMIT OFF
> then you get the same settings as in the SQL 2000 tools.
> Certainly far more easier is to use Query Analyzer. (You mentioned
> Enterprise Manager. If you mean the 6.5 tool, it has the same issue
> as ISQL/W. It you mean EM 2000, this is a poor tool for maintaining
> stored procedures.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Hepl with a stored procedure

I want to write a stored procedure that takes two params and merges records
whose id match the two params.
For example, I have a TableX(col1 char(10), col2 char(5), col3 int) with 4
rows:
"c1"," i2", 3
"c1", "i4", 2
"d1", "i2", 2
"d1", "i3", 1
After I call the sp and pass "c1" and "d1" as arguments, the table should
contains only 3 rows:
"d1", "i2", 5
"d1", "i3", 1
"d1", "i4", 2
Note: the sp should also take into account that only records with "c1" id
are guaranteed to exist in the table but the records with "d1" is are not.
I could use two cursors to fetch records that match the given col1
arguemtns, do some comparison as I step through the cursors, write the
results into a temp table, drop the records in TableX, and finally select
records from temp table into TableX.
I think there must be an elegant & efficient way that uses only subqueries
and maybe a Table variable. Could any one help me with this?.... a complex (or simple) update statement that has the same logic as your
query.
I'd most likely use executeSQL within my sProc that allows output params fro
m an
exec string to do further validation processing
Then start a transaction, first do the update and then the delete then close
the
transaction.
I would also be concerned about record locks if users are in these same tabl
es
during this update/merge.
HTH
JeffP....
"VC" <vutha@.mailblocks.com> wrote in message
news:eAM7%238tEFHA.464@.TK2MSFTNGP15.phx.gbl...
> I want to write a stored procedure that takes two params and merges record
s
> whose id match the two params.
> For example, I have a TableX(col1 char(10), col2 char(5), col3 int) with 4
> rows:
> "c1"," i2", 3
> "c1", "i4", 2
> "d1", "i2", 2
> "d1", "i3", 1
> After I call the sp and pass "c1" and "d1" as arguments, the table should
> contains only 3 rows:
> "d1", "i2", 5
> "d1", "i3", 1
> "d1", "i4", 2
> Note: the sp should also take into account that only records with "c1" id
> are guaranteed to exist in the table but the records with "d1" is are not.
> I could use two cursors to fetch records that match the given col1
> arguemtns, do some comparison as I step through the cursors, write the
> results into a temp table, drop the records in TableX, and finally select
> records from temp table into TableX.
> I think there must be an elegant & efficient way that uses only subqueries
> and maybe a Table variable. Could any one help me with this?
>|||On Mon, 14 Feb 2005 14:49:28 -0700, VC wrote:

>I want to write a stored procedure that takes two params and merges records
>whose id match the two params.
>For example, I have a TableX(col1 char(10), col2 char(5), col3 int) with 4
>rows:
>"c1"," i2", 3
>"c1", "i4", 2
>"d1", "i2", 2
>"d1", "i3", 1
> After I call the sp and pass "c1" and "d1" as arguments, the table should
>contains only 3 rows:
>"d1", "i2", 5
>"d1", "i3", 1
>"d1", "i4", 2
>Note: the sp should also take into account that only records with "c1" id
>are guaranteed to exist in the table but the records with "d1" is are not.
Hi VC,
This can be done with three queries. Your sp should enclose them in a
procedure and add proper error handling.
-- Handle c1 without matching d1
-- (these are simply "renamed" to d1)
UPDATE c
SET col1 = 'd1'
FROM TableX AS c
LEFT JOIN TableX AS d
ON d.col1 = 'd1'
AND d.col2 = c.col2
WHERE c.col1 = 'c1'
-- Handle c1 with matching d1
-- (col3 in the d1 row gets increased; the c1 row is left unchanged)
UPDATE d
SET col3 = d.col3 + c.col3
FROM TableX AS c
INNER JOIN TableX AS d
ON d.col1 = 'd1'
AND d.col2 = c.col2
WHERE c.col1 = 'c1'
-- Remove remaining c1 rows
DELETE TableX
WHERE col1 = 'c1'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank for the suggestion of using the transaction to protect the db
intergrity in case of errors. In my case, record locks should not be a
problem because col1 contains guid variables that are supposed to be
uniquely created for a user session. So the operation would affect, if any,
just a few rows that belong to one user.
BTW, check out the solution provided by Hugo Kornelis. It is much more
efficient than the one I had in mind.
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23QhIYQuEFHA.1396@.tk2msftngp13.phx.gbl...
> ... a complex (or simple) update statement that has the same logic as
> your
> query.
> I'd most likely use executeSQL within my sProc that allows output params
> from an
> exec string to do further validation processing
> Then start a transaction, first do the update and then the delete then
> close the
> transaction.
> I would also be concerned about record locks if users are in these same
> tables
> during this update/merge.
> HTH
> JeffP....
> "VC" <vutha@.mailblocks.com> wrote in message
> news:eAM7%238tEFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||Your solution works nicely. I just make a small change to your block of code
so that only rows with unmatched col2 are renamed.
UPDATE c
SET col1 = 'd1'
FROM T1 AS c
WHERE c.col1 = 'c1'
AND c.col2 NOT IN
(SELECT d.col2 FROM T1 AS d
WHERE d.col1 = 'd1')
Thank you very much. I now only need to create a sp out of these codes :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aib2111n1uigknubv8kk4c1lmk1j0qc02m@.
4ax.com...
> On Mon, 14 Feb 2005 14:49:28 -0700, VC wrote:
>
> Hi VC,
> This can be done with three queries. Your sp should enclose them in a
> procedure and add proper error handling.
> -- Handle c1 without matching d1
> -- (these are simply "renamed" to d1)
> UPDATE c
> SET col1 = 'd1'
> FROM TableX AS c
> LEFT JOIN TableX AS d
> ON d.col1 = 'd1'
> AND d.col2 = c.col2
> WHERE c.col1 = 'c1'
> -- Handle c1 with matching d1
> -- (col3 in the d1 row gets increased; the c1 row is left unchanged)
> UPDATE d
> SET col3 = d.col3 + c.col3
> FROM TableX AS c
> INNER JOIN TableX AS d
> ON d.col1 = 'd1'
> AND d.col2 = c.col2
> WHERE c.col1 = 'c1'
> -- Remove remaining c1 rows
> DELETE TableX
> WHERE col1 = 'c1'
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 15 Feb 2005 11:54:58 -0700, VC wrote:

>Your solution works nicely. I just make a small change to your block of cod
e
>so that only rows with unmatched col2 are renamed.
>UPDATE c
>SET col1 = 'd1'
>FROM T1 AS c
>WHERE c.col1 = 'c1'
>AND c.col2 NOT IN
> (SELECT d.col2 FROM T1 AS d
> WHERE d.col1 = 'd1')
Hi VC,
This statement is actually equivalent to the statement I intened to use,
but I now see that I forgot to include one important line. This is the
statement as I meant it to write:
UPDATE c
SET col1 = 'd1'
FROM TableX AS c
LEFT JOIN TableX AS d
ON d.col1 = 'd1'
AND d.col2 = c.col2
WHERE c.col1 = 'c1'
AND d.col1 IS NULL -- This line is added
The extra line is there to test that the LEFT JOIN did not find a matching
row in TableX.
The main advantage of my version over yours is that NOT IN will produce
unexpected results if any of the rows in your table can have a NULL value
for col2. That's why I always use either the LEFT JOIN technique, or a
subquery with EXISTS.
Of course, the problem with the LEFT JOIN technique is that it goes
dramatically wrong if you forget to include the IS NULL test... <g>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help: Why excute a stored procedure need to more 30 seconds, but direct excute the query o

Hello to all,

I have a stored procedure. If i give this commandexce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.

but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time

I don't know why?

Maybe can somebody help me?

thanks in million

best Regards

Pinsha

My Procedure Codes are here:

set ANSI_NULLSON

set QUOTED_IDENTIFIERON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTERPROCEDURE [dbo].[ShortestPath](@.IDMemberint, @.IDOther varchar(1000),@.Levelint, @.Path varchar(100)=null output)

AS

BEGIN

if( @.Level= 1)

begin

select @.Path=convert(varchar(100),IDMember)

from wtcomValidRelationships

where wtcomValidRelationships.[IDMember]= @.IDMember

andPATINDEX('%'+@.IDOther+'%',(select RelationshipIDsfrom wtcomValidRelationshipswhere IDMember= @.IDMember))> 0

end

if(@.Level= 2)

begin

selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andPATINDEX('%'+@.IDOther+'%',B.RelationshipIDs)> 0

end

if(@.Level= 3)

begin

selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C

where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andPATINDEX('%'+@.IDOther+'%',C.RelationshipIDs)> 0

end

if( @.Level= 4)

begin

selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D

where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andPATINDEX('%'+@.IDOther+'%',D.RelationshipIDs)> 0

end

if(@.Level= 5)

begin

selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D, wtcomValidRelationshipsas E

where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0andPATINDEX('%'+@.IDOther+'%',E.RelationshipIDs)> 0

end

if(@.Level= 6)

begin

selecttop 1 @.Path=''from wtcomValidRelationships

end

END

Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?

If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.

You run it like this:

EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.

Where

a|||

Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?

If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.

You run it like this:

EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.

Where

|||

Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?

If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.

You run it like this:

EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.

Where

a stored|||

Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?

If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.

You run it like this:

EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.

This problem can happen , whereever a stored procedure has radically paths through it with reference to the most efficient way of processing.

|||

Hello,

I have tried this EXEC sp_recompile N'TABLENAME. But this problem can not be resolved.

Somebody tell me that thecharindex function is not good for searching. Better use in function. I tried to use in function. Another Problem comes: Member 3430 has Relationship with Member 3418, but i usedin function, Member 3430 can not be found that it has relationship with Member 3418. Here is my test code:

declare @.IDMint;

declare @.IDO varchar(100);

set @.IDM= 3418;

set @.IDO='3430'

selectconvert(varchar(100),IDMember)

from wtcomValidRelationships

where wtcomValidRelationships.[IDMember]= 3418

and(@.IDOin(select B.RelationshipIDsfrom wtcomValidRelationshipsas Bwhere B.IDMember= @.IDM))

Maybe somebody can help me? Thanks

Best Regards

Pinsha

|||

Try splittingdbo].[ShortestPath] intodbo].[ShortestPathLevel1],dbo].[ShortestPathLevel2] ect.so that each only has the logic to process one level. This will ensure that each is optimised correctly.

Help: Table Lock Confirmation

I need confirmation from you SQL Server experts out there. Please let me know if the following works. Thanks!

This stored procedure gets a value and increments by 1, but while it does this, I want to lock the table so no other processes can read the same value between the UPDATE and SELECT (of course, this may only happen in a fraction of a second, but I anticipate that we will have thousands of concurrent users). I need to manually increment this column because an identity column is not appropriate in this case.

BEGIN TRANSACTION

UPDATE forum WITH (TABLOCKX)
SET forum_last_used_msg_id = forum_last_used_msg_id + 1
WHERE forum_id = @.forum_id

SELECT @.new_id = forum_last_used_msg_id
FROM forum
WHERE forum_id = @.forum_id

COMMIT TRANSACTIONI would go for a different solution; a transaction is used to be able to rollback data in case of a failure, and may help to solve a concurrent-user issue. But not like this. I would think there could be another update between the update and the select..|||Originally posted by Kaiowas
I would go for a different solution; a transaction is used to be able to rollback data in case of a failure, and may help to solve a concurrent-user issue. But not like this. I would think there could be another update between the update and the select..

Thanks for your response, but I am using the transaction to lock the table initiated by the UPDATE forum WITH (TABLOCKX). I understand this table should stay locked until the COMMIT TRANS.|||The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Thank you very much Pat!|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Thank you very much Pat!|||Originally posted by stevenpath
Thanks for your response, but I am using the transaction to lock the table initiated by the UPDATE forum WITH (TABLOCKX). I understand this table should stay locked until the COMMIT TRANS.

You're right! Sorry I missed that, but why not stick with it?|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Pat why the extra BEGIN TRAN?

Is that a type o?

Geez what a way to hit 2000|||Originally posted by Brett Kaiser
Pat why the extra BEGIN TRAN? It is a nifty little trick that I dreamed up one night in a haze...

When using nested stored procedures, things got really, really complicated if the transaction level got puckered up, and things just went to heck in a handcart. I had to find some way that I could rollback without blowing the whole tamale out of the water. Necessity being a mother (as you so recently pointed out), I came up with a deviant solution.

The code is two transactions when life is good, with an empty one being rolled back, which has no impact on the database. When life is hard, it is only one transaction, which is also rolled back so it has no impact on the transaction count either...

The net result is that it is an odd bit of code, but it works nicely in all of the peculiar ways that we need code to function. Someday I'll have to post a little diatribe about the bad old days, when Sybase wanted considerably more dollars for each replicated database (per year) than they wanted for the license for the database! That drove us to some peculiar work arounds, this being one of them.

-PatP|||OK, I see it now...but why do it that way?

Why not handle it like the code in this thread?

http://www.dbforums.com/showthread.php?threadid=988019&perpage=15&pagenumber=1

What's the difference...you're using Goto's anyway...|||As I said in the previous post, this was a side effect of the cost of using early (like 1994) versions of SQL replication. Our work around required each procedure have one entry point, and one exit point from a code execution perspective so we could effectively "bottle" the procedure with calls to other procedures that our work around required.

It wasn't necessarily pretty, but it saved us more than a million dollars per year in licensing fees.

-PatP