Default Logging (UseParentSetting) function works for the immediate child packge ONLY

I am not sure if this is a bug or not, but we have found that only the immediatelly called child package inherits the parent logging option, and others called byond the immediate child will loose the settings. For instance, say you have three packages, pkg1 will specify logging (checked events that will be logged), pkg2(which is called from pkg1) will see (by default) the checked events from pkg1, but pkg 3(which is called from pkg2) won't. You will have to manually check the logged events or load an existing settings file. This would be fine if you were dealing with just a few called packages, but for some cases (ours included) this would prove to be quite a pain having to manually check all the sub pkgs that didn't inherit the parent.

Is this a bug? Any easier ways of incorporating logged events in all of the sub-sub pkgs without manually checking the events?

Thanks in advance...

I think my problem is more in depth then I originally illustrated. When you define logging, (the provider and log connection) does this get inherited by other child packages, or must you define a provider/connection for every package?

|||I posted a similar question about logging, but I think I didn't ask it properly.

When you set LoggingMode=enabled and define the provider and checked events in the pkg(parent), don't any and all child packages inherit these properties as long as LoggingMode=UseParentSetting?

We setup and configured one pkg for logging, then set all sub pkgs to UseParentSetting, but when we would run the pkgs, the other pkgs below the immediatelly called child wouldn't get logged. So, pkg1(enabled logging) would call pkg2(useparent), then pkg2 called pkg3(useparent), and so on -- pkg1 and pkg2 would log, but 3,4,5... wouldn't.

When we check logging settings under pkg3(so on...) the events were not checked, we manually tried to check them but it created a unique logging pkg (LoggingMode=enabled).

Are we missing something here?|||

[Threads merged]

The LoggingMode setting is only for enabling and disabling the logging. It's only that distinction which can be inheritted from the parent. I.e., your choices are:

Enable logging on this package|||That's what I thought, unfortunatelly though, it doesn't log the other child packages as illustrated in my earlier post. Maybe, SeptCTP has some bugs in logging? We will try this scenerio on the RTM and see...

Thanks,

DEFAULT keyword performance

I have a function which performs a query and returns a table. The one
parameter that can get passed in is a date which defaults to NULL.
There is an IF statement in the function that will set the paramter to
an actual date if null. If I call the function while passing in a date
the function comes back a second or 2 later. But if I pass in DEFAULT
to the function, the same query takes 8 minutes. See code below and
sample call below.

CREATE FUNCTION fCalculateProfitLossFromClearing (
@.TradeDate DATETIME = NULL
)
RETURNS @.t TABLE (
[TradeDate] DATETIME,
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[Account] VARCHAR(10),
[Value] DECIMAL(18, 6)
)
AS
BEGIN
-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Make the query
INSERT @.t
SELECT
@.TradeDate,
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@.TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @.TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @.TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

RETURN
END

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing('09/25/2003')

it returns in 2 seconds.

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing(DEFAULT)

in which GetPreviousTradeDate() will set @.TradeDate to 09/25/2003 it
returns in 8 minutes.[posted and mailed, please reply in news]

Jason (JayCallas@.hotmail.com) writes:
> I have a function which performs a query and returns a table. The one
> parameter that can get passed in is a date which defaults to NULL.
> There is an IF statement in the function that will set the paramter to
> an actual date if null. If I call the function while passing in a date
> the function comes back a second or 2 later. But if I pass in DEFAULT
> to the function, the same query takes 8 minutes. See code below and
> sample call below.

The query seems familiar. :-)

The reason for this is that when SQL Server builds the query plan,
it considers the value of the input parameter. When you provide an
explicit date, SQL Server can consult the statistics for the table
and see that the value you provided is very selective, and use the
index.

But if you provide NULL, SQL Server will build the query plan on that
assumption. Obviously a NULL value would return no rows, but SQL Server
never makes any assumptions that could yield incorrect results. Since
you WHERE condition is for a range, SQL Server appears to prefer to
scan the table, than using a non-clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> The query seems familiar. :-)
> The reason for this is that when SQL Server builds the query plan,
> it considers the value of the input parameter. When you provide an
> explicit date, SQL Server can consult the statistics for the table
> and see that the value you provided is very selective, and use the
> index.
> But if you provide NULL, SQL Server will build the query plan on that
> assumption. Obviously a NULL value would return no rows, but SQL Server
> never makes any assumptions that could yield incorrect results. Since
> you WHERE condition is for a range, SQL Server appears to prefer to
> scan the table, than using a non-clustered index.

I hate the restart this thread but I have hit a brick wall...

I am at a lose of whether to creat functions or stored procedures (or
even views).

The below questions/issues are based on a need to return information
on a particular date for one to many symbols.

For my example lets say 15 symbols. You could do a query like Symbol =
'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
Symbol IN ('a','b',...))

I would also like to give my functions and stored procedures to use a
default date if none is specified. I created a function named
GetPreviousTradeDate() which does this based on a calendar.

SO here is how I see it.

Stored procedures seem to be the fastest in terms of returning data
back. But they seem to be limited in the sense that they can return
ONE row or ALL the rows since you cannot pass in a variable list of
symbols. You also cannot use the SP as part of a query. You could just
return all the rows back to the client and do filter or searching on
that end but that does not seem efficient or professional.

A function also does not let you pass in a variable list of symbols
but at least you can use it in a query. You could do something like
SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
happens at the server side and only the needed rows gets sent back.

But functions seem to have MAJOR performance problems when default
values are passed in. When I pass in a specific date the query takes a
few seconds but when I pass in DEFAULT and set the date to the results
of the GetPreviousTradeDate() function the query takes anywhere from 8
minutes to 15 minutes. (This even happens if I do not use the
GetPreviousTradeDate() function and set my parameter to a hard-coded
value)

Any thoughts or comments would be appreciated.|||Jason (JayCallas@.hotmail.com) writes:
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.

I happen to have an article on my web site that discusses this case
in detail. You don't have to read all of it, but you can just get
the function you need at
http://www.algonet.se/~sommar/array...html#iterative.

> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)

The difference is not always that big, but apparently your query is
sensitive for this. I suggest that you split up the procedure in two:

EXEC outer_sp @.date = NULL datetime
IF @.date IS NULL
SELECT @.date = dbo.yourfunctionfordefault()
EXEC inner_sp @.date

And then inner_sp includes the actual query.

For a long treatise on this subject, search on Google news for articles
by Bart Duncan (a escalation engineer at Microsoft) and the phrase
"parameter sniffing".

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||JayCallas@.hotmail.com (Jason) wrote in message news:<f01a7c89.0310141510.28e9c846@.posting.google.com>...
> I hate the restart this thread but I have hit a brick wall...
> I am at a lose of whether to creat functions or stored procedures (or
> even views).
> The below questions/issues are based on a need to return information
> on a particular date for one to many symbols.
> For my example lets say 15 symbols. You could do a query like Symbol =
> 'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
> Symbol IN ('a','b',...))
> I would also like to give my functions and stored procedures to use a
> default date if none is specified. I created a function named
> GetPreviousTradeDate() which does this based on a calendar.
> SO here is how I see it.
> Stored procedures seem to be the fastest in terms of returning data
> back. But they seem to be limited in the sense that they can return
> ONE row or ALL the rows since you cannot pass in a variable list of
> symbols. You also cannot use the SP as part of a query. You could just
> return all the rows back to the client and do filter or searching on
> that end but that does not seem efficient or professional.
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.
> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)
> Any thoughts or comments would be appreciated.

Since the stored procedure has both the speed and the ability to use
default values without performance hit, would it be normal practice or
efficient to send separate queries for each symbol to the stored
procedure? This could result in anywhere from a few to several hundred
calls at a time.|||Jason (JayCallas@.hotmail.com) writes:
> Since the stored procedure has both the speed and the ability to use
> default values without performance hit, would it be normal practice or
> efficient to send separate queries for each symbol to the stored
> procedure? This could result in anywhere from a few to several hundred
> calls at a time.

That does not seem like a good idea. Certainly more efficient to get
data for all symbols at once. See my previous post for suggestions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> A function also does not let you pass in a variable list of symbols
but at least you can use it in a query ... Any thoughts or comments
would be appreciated. <<

Ever try putting the list of symbols into a one column table and using
an "IN (SELECT parm FROM Parmlist)" instead?

Default Constraints (call: User-Defined Function or proc)

Hi
Have any way to call User-Defined Function or proc of a Default Constraint'
INT CONSTRAINT test_df DEFAULT dbo.myFunc()
ThaksAccording to the SQL BOL, a user-defined function can be used in a DEFAULT
clause. A stored procedure - not that I am aware of.
HTH
Jerry
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>|||Hi
Here is an example of a udf:
CREATE FUNCTION SetDate ()
RETURNS Datetime
AS
BEGIN
DECLARE @.Startdate Datetime
SET @.Startdate = '20050101'
RETURN @.Startdate
END
CREATE TABLE MyTest ( id int not null identity(1,1) Primary key,
name varchar(10) NOT NULL,
StartDate datetime not null default dbo.SetDate(),
EndDate datetime not null default getdate()+1
)
INSERT INTO MyTest ( name ) VALUES ( 'John')
SELECT * FROM MyTest
/*
id name StartDate
EndDate
-- -- ----
-
--1 John 2005-01-01 00:00:00.000
2005-09-25 15:38:48.220
(1 row(s) affected)
*/
John
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>

DecryptByKeyAutoCert with cert_password bugged?

I am having trouble with the DecryptByKeyAutoCert function when I try to provide the cert_password parameter.

According to the BOL, it is the second parameter of the function:
DecryptByKeyAutoCert
( cert_ID , cert_password , { 'ciphertext' | @.ciphertext }
[ , { add_authenticator | @.add_authenticator }
[ , { authenticator | @.authenticator } ]
]
)
However, when I provide a password, I get the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of DecryptByKeyAutoCert function.

This is totally in contradiction with what the BOL description says:

cert_password

Is the password that protects the private key of the certificate. Can be NULL if the private key is protected by the database master key. varchar.

Does anyone have any experience with this? I tried Google already but didn't get too many results, unfortunately.

I do not want to use the master key because that would enable all DBAs to read the encrypted data without knowing any password to decrypt. But I do need to use the automatic function due to the design of our dated VB6 application (i.e. it is impossible to open the key prior to the select due to design of interaction with Crystal Reports).

Also, is there any way to find out what parameter type the function is actually expecting? Where are these functions stored?

PS: I tried this on win2003 SP1/SQL2005 SP1 and winXP SP2/SQL2005 SP2 - same result on both.

Thanks in advance!

Okay, I found the solution: the function apparently expects an nvarchar instead of a varchar...
What a difference one letter sometimes can make...

decrypt function in SQL Server 2000

I know that there are undocumented encryption function, such as:
pwdencrypt
pwdcompare
anyone know what is the decrypt function in SQL Server 7.0/2000. If you know
any, please provide syntax and some simple examples.
Thanks a lotHi,
THose function are undocumeted and may not be available in next version.
refer the below site:-
http://www.activecrypt.com/
Thanks
Hari
SQL Server MVP
"jzhou" <jzhou@.discussions.microsoft.com> wrote in message
news:0DC77411-9710-4A06-A502-EE1DC61DAE54@.microsoft.com...
>I know that there are undocumented encryption function, such as:
> pwdencrypt
> pwdcompare
> anyone know what is the decrypt function in SQL Server 7.0/2000. If you
> know
> any, please provide syntax and some simple examples.
> Thanks a lot|||To add to Hari's response, undocumented functionality can change between
versions or even service packs and thus break your code. Avoid using
undocumented stuff in production.
Hope this helps.
Dan Guzman
SQL Server MVP
"jzhou" <jzhou@.discussions.microsoft.com> wrote in message
news:0DC77411-9710-4A06-A502-EE1DC61DAE54@.microsoft.com...
>I know that there are undocumented encryption function, such as:
> pwdencrypt
> pwdcompare
> anyone know what is the decrypt function in SQL Server 7.0/2000. If you
> know
> any, please provide syntax and some simple examples.
> Thanks a lot|||The others have explained why you shouldn't use this undocumented feature. T
o
answer your original question, there is no corresponding decrypt SP because,
despite the name, pwdencrypt isn't in fact an encryption function. pwdencryp
t
generates a hash (not a very secure one apparently) so you can't actually
"decrypt" it.
David Portas
SQL Server MVP
--

DECODE in SQL Server?"

What is the function in SQL that works like DECODE in Oracle?"

Thanks,

NUse CASE

--
David Portas
SQL Server MVP
--|||SELECT CASE WHEN Assignment_Type = 'C' THEN 'Com' ELSE 'Donot Know'
FROM ASSIGNMENT

When I try to use CASE WHEN, it gave me error [Microsoft][ODBC SQL Server
Driver][SQL Server]Incorrect syntax near the keyword 'FROM'. What did I do
wrong?

Thanks

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:24GdncWmqJeLKfzcRVn-vg@.giganews.com...
> Use CASE
> --
> David Portas
> SQL Server MVP
> --|||> What did I do
> wrong?

You didn't read-up on the syntax first! My reply was intended as an
indication of something you should look up. You'll find Books Online is a
great resource if you refer to it occassionally :-)

SELECT CASE WHEN Assignment_Type = 'C' THEN 'Com' ELSE 'Do not know' END
FROM ASSIGNMENT

or

SELECT CASE Assignment_Type WHEN 'C' THEN 'Com' ELSE 'Do not know' END
FROM ASSIGNMENT

--
David Portas
SQL Server MVP
--|||David,

Sorry, I don't have books online. The examples I found on the site, none of
them used "END" in the statment.

Thanks a bunch! ^_____^
N

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:5MGdne_kiKNeKvzcRVn-gw@.giganews.com...
> > What did I do
> > wrong?
> You didn't read-up on the syntax first! My reply was intended as an
> indication of something you should look up. You'll find Books Online is a
> great resource if you refer to it occassionally :-)
> SELECT CASE WHEN Assignment_Type = 'C' THEN 'Com' ELSE 'Do not know' END
> FROM ASSIGNMENT
> or
> SELECT CASE Assignment_Type WHEN 'C' THEN 'Com' ELSE 'Do not know' END
> FROM ASSIGNMENT
> --
> David Portas
> SQL Server MVP
> --|||Get BOL here:
http://www.microsoft.com/sql/techin...000/default.asp

--
David Portas
SQL Server MVP
--|||N wrote:
> What is the function in SQL that works like DECODE in Oracle?"
>
> Thanks,
> N

As you know CASE is not the same as DECODE ... but SQL Server hsa no
functionality equivalent to DECODE so you will have to adapt CASE to
do the job.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||Daniel Morgan wrote:

> N wrote:
>> What is the function in SQL that works like DECODE in Oracle?"
>>
>>
>>
>> Thanks,
>>
>> N
>
> As you know CASE is not the same as DECODE ... but SQL Server hsa no
> functionality equivalent to DECODE so you will have to adapt CASE to
> do the job.
How is DECODE different than "simple CASE"?
(Other than that DECODE is a function and CASE an expression, of course...)

Cheers
Serge|||You can also find the books online online at

http://msdn.microsoft.com/library/d...lserver2000.asp

Open up the SDK Documentation tree item.

Muhd

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:5aOdncEAVOgnX_zcRVn-tA@.giganews.com...
> Get BOL here:
> http://www.microsoft.com/sql/techin...000/default.asp
> --
> David Portas
> SQL Server MVP
> --|||"Daniel Morgan" <damorgan@.x.washington.edu> wrote in message
news:1096932539.547042@.yasure...
> N wrote:
> > What is the function in SQL that works like DECODE in Oracle?"
> > Thanks,
> > N
> As you know CASE is not the same as DECODE ... but SQL Server hsa no
> functionality equivalent to DECODE so you will have to adapt CASE to
> do the job.
> --
> Daniel A. Morgan
> University of Washington
> damorgan@.x.washington.edu
> (replace 'x' with 'u' to respond)

OK, then, what does DECODE do?|||"DHatheway" <dlhatheway@.mmm.com.nospam> wrote in message news:ck18oi$m31$1@.tuvok3.mmm.com...
> "Daniel Morgan" <damorgan@.x.washington.edu> wrote in message
> news:1096932539.547042@.yasure...
> > N wrote:
> > > What is the function in SQL that works like DECODE in Oracle?"
> > > Thanks,
> > > > N
> > As you know CASE is not the same as DECODE ... but SQL Server hsa no
> > functionality equivalent to DECODE so you will have to adapt CASE to
> > do the job.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan@.x.washington.edu
> > (replace 'x' with 'u' to respond)
> OK, then, what does DECODE do?

DECODE( exp, search1, result1 [,search2, result2]... ) is semantically equivalent to:

CASE exp
when search1 then result1
when search2 then result2
... END

The difference between CASE and DECODE is that CASE also allows the form:
CASE
WHEN exp1 = search1 then result1
WHEN exp2 = search2 then result2
... END

DECODE can't do that.

--
Paul Horan[TeamSybase]

VCI Springfield, Mass
www.vcisolutions.com

Declaring USER_NAME() as SQL Variable

Hi,

I have a User-defined function "Concatenate_NoteTexts" which I use in a
query (SQL Server 2000). On my local development machine it is called like
this:

SELECT
dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
FROM tblIntroducers

I want to run the same code on a shared remote server where I am user "JON"
instead of "dbo". I don't want to hard-code the User Name into the SQL, but
when I tried to put the user name into a variable as here:

DECLARE @.USER_NAME VarChar(30)
SET @.USER_NAME = USER_NAME()

SELECT
@.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
FROM tblIntroducers

I get the following error:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'

Any advice?

TIA,

JON

PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
1"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bj4s3n$kh5$1@.online.de...
> Hi,
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
like
> this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
"JON"
> instead of "dbo". I don't want to hard-code the User Name into the SQL,
but
> when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> I get the following error:
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '.'
> Any advice?

Beg for your own database.
Development as a non-dbo is really a hastle.

You can have your own database without being a SystemAdministrator. Just
have a SystemAdministrator to run this code:

create database jon_dev
go
use jon_dev
go
sp_addalias 'jon', 'dbo'

David|||Hi David,

Thanks, nice idea, I'll have to see if the webhosts will do that.

But there must also be a way to code what I want *without* being a SysAd!

JON|||What are you trying to do with the variable? Concatenate? Or return it in
the select statement as a column? If the latter, change the period to a
comma.

DECLARE @.USER_NAME VarChar(30)
SET @.USER_NAME = USER_NAME()

SELECT
@.USER_NAME,Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
FROM tblIntroducers

"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bj4s3n$kh5$1@.online.de...
> Hi,
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
like
> this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
"JON"
> instead of "dbo". I don't want to hard-code the User Name into the SQL,
but
> when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> I get the following error:
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '.'
> Any advice?
> TIA,
> JON
>
> PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
> 1
>
>
>|||Sorry... misread your message - you need access to the function.

"Morgan" <mfears@.spamcop.net> wrote in message
news:OCNLY9icDHA.1280@.tk2msftngp13.phx.gbl...
> What are you trying to do with the variable? Concatenate? Or return it in
> the select statement as a column? If the latter, change the period to a
> comma.
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME,Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> "Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
> news:bj4s3n$kh5$1@.online.de...
> > Hi,
> > I have a User-defined function "Concatenate_NoteTexts" which I use in a
> > query (SQL Server 2000). On my local development machine it is called
> like
> > this:
> > SELECT
> > dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> > FROM tblIntroducers
> > I want to run the same code on a shared remote server where I am user
> "JON"
> > instead of "dbo". I don't want to hard-code the User Name into the SQL,
> but
> > when I tried to put the user name into a variable as here:
> > DECLARE @.USER_NAME VarChar(30)
> > SET @.USER_NAME = USER_NAME()
> > SELECT
> > @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as
NoteTexts
> > FROM tblIntroducers
> > I get the following error:
> > Server: Msg 170, Level 15, State 1, Line 4
> > Line 4: Incorrect syntax near '.'
> > Any advice?
> > TIA,
> > JON
> > PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
> > 1|||Jon Maz (jonmaz@.NOSPAM.surfeu.de) writes:
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
> like this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
> "JON" instead of "dbo". I don't want to hard-code the User Name into
> the SQL, but when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers

The question is slightly more interesting than it may look like.

Say that you instead had had a stored procedure, call it notetext_sp.
This would not have constituted any problem, because you could have
called it as:

EXEC notetext_sp

When you are logged in as JON on the remote server, SQL Server would
have found the notetext_sp owned by you. This works for any other
SQL Server object as well. Except scalar user-defined functions, because
you must refer to them with a two-part name. The reason for this is
syntactical, so that the parser can distinguish between UDF and built-in
functions.

However, there is an exception to the exception. This works:

ALTER FUNCTION nisse_fun (@.a int) returns varchar(90) as
BEGIN
RETURN (SELECT replicate('nisse', @.a))
END
go
declare @.g varchar(90)
exec @.g = nisse_fun 8
select @.g

That is you can invoke a scalar UDF with EXEC as well, and in this case
you don't need the two-part name. Whether this actually helps you, I
don't know.

However, as noted by David Browne, getting your database makes life a
lot easier.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Declaring Constants

hey

when declaring constants, could you use the LIKE function. If I am not entirely sure what the constant should contain.
so as an example:

LType varchar2(10) := 'Example';

could this be changed to LType varchar2(10) LIKE 'Example'

CheersI think it is not possible
Declare it as it is and use left or mid function for further validations

declare syntax in a UDF

Hi, I'm trying to create a function that returns a table, however I want
to use a local variable in there and enterprise manager ain't liking it!

The error I get is number 156 'incorrect syntax near the keyword
'declare'.. hopefully this is just a simple thing where I've put it in
the wrong place.

The code follows:

CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
nvarchar(20))

RETURNS TABLE AS
RETURN

declare @.set nvarchar(150)

select "Total Pledged" as info, sum(total) as tot
FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)

Cheers for any help,
Chris"Not Me" <Noone.is.home@.here.com> wrote in message
news:ckoccr$olo$1@.ucsnew1.ncl.ac.uk...
> Hi, I'm trying to create a function that returns a table, however I want
> to use a local variable in there and enterprise manager ain't liking it!
> The error I get is number 156 'incorrect syntax near the keyword
> 'declare'.. hopefully this is just a simple thing where I've put it in the
> wrong place.
> The code follows:
> CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
> smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
> nvarchar(20))
> RETURNS TABLE AS
> RETURN
> declare @.set nvarchar(150)
> select "Total Pledged" as info, sum(total) as tot
> FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
>
> Cheers for any help,
> Chris

You seem to be mixing inline and multi-statement syntax. If you just say
RETURN TABLE, then the rest of the function can only be a single SELECT
statement; if you want to use multiple statements in the function, then you
must define the structure of the table you're returning. See the examples in
Books Online under CREATE FUNCTION.

In your function, you haven't defined the structure of the returned table,
so the only thing you can have in the body of the function is a single
SELECT.

Simon|||Simon Hayes wrote:
> "Not Me" <Noone.is.home@.here.com> wrote in message
> news:ckoccr$olo$1@.ucsnew1.ncl.ac.uk...
>>The error I get is number 156 'incorrect syntax near the keyword
>>'declare'.. hopefully this is just a simple thing where I've put it in the
>>wrong place.
>>
>>The code follows:
>>
>>CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
>>smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
>>nvarchar(20))
>>RETURNS TABLE AS
>>RETURN
>>declare @.set nvarchar(150)
>>select "Total Pledged" as info, sum(total) as tot
>>FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
> You seem to be mixing inline and multi-statement syntax. If you just say
> RETURN TABLE, then the rest of the function can only be a single SELECT
> statement; if you want to use multiple statements in the function, then you
> must define the structure of the table you're returning. See the examples in
> Books Online under CREATE FUNCTION.

Aha! sounds about right, just needed a little shunt in the right
direction.. gonna have nightmares about BOL :p

cheers,
Chris

Decimal problem with expression

Hi,

I've made a new report with a matrix and subtotals. But i don't want to have subtotals but subaverages so i used the inscope function. This is my expression, as you can see very long :)

=iif(InScope("groups"), iif(InScope("items"), Iif(Fields!SCO_SCORE.Value=0,"",Fields!SCO_SCORE.Value), iif(sum(iif(Fields!SCO_SCORE.Value>0,1,0))=0,"",((sum(Fields!SCO_SCORE.Value)*Fields!IC_WEIGHT.Value)/sum(iif(Fields!SCO_SCORE.Value>0,1,0))))), iif(InScope("items"), "In Subtotal of ColumnGroup1", AVG(Fields!SCO_SCORE.Value*Fields!IC_WEIGHT.Value)))

The output of this function is a number with no decimals in the matrix. for example 2

But this is not the problem, this expression works but i wanted to make the exact same new report using this exact same function. I've made everything this report but when i run it i get a number with 2 decimals for example 2.00

I really don't know how to fix this? I did exactly the same as the first time? Anyone who knows the source of this problem? is it a bug?

Greetz

Maybe the query / underlying dataset field datatypes have changed from integer to double/decimal?

Regardless, you can always explicitly set the format property on the textbox to get a particular formatting. In your case you should set the format property to N0 to achieve numeric formatting with zero decimals.

-- Robert

|||I am using RS 2000. Can someone please help me learn to do this? I want to be able to format numbers in a text box to only display whole numbers and commas, no decimals.|||

Use the following link as a starting point into the MSDN documentation for numeric formatting: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp

It should help you finding a format code setting that will achieve what you are looking for.

-- Robert

Decimal point question

Hi
What is the function that shows only the N digits after the decimal point
?
For example: 1.4567 will result as 1.45ROUND() will round the value. This has little to do with what is
actually shown on the screen however. Display formatting is controlled
by your client application, not by SQL Server.
David Portas
SQL Server MVP
--

Decimal and Number Formatting

I have a written a function where I am defining the return value as decimal. Now I need to do the formatting
to make the negative number look like (123.34%) and postive numbers as 123.34%. When I try to do this, I am
getting values like (123.3456788). How do I get rid of these extra decimals?
** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is not working since I am using Sum for aggregation.
Thanks a lot for your help.Go to the Textbox Properties dialog in designer and specify
"#,##0.00%;(#,##0.00%);Zero" (without the double quotes) as the custom
format string.
Ravi Mumulla
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Taps" <Taps@.discussions.microsoft.com> wrote in message
news:89671ED3-88A9-4FE3-B1FA-109DCDE50065@.microsoft.com...
> I have a written a function where I am defining the return value as
decimal. Now I need to do the formatting
> to make the negative number look like (123.34%) and postive numbers as
123.34%. When I try to do this, I am
> getting values like (123.3456788). How do I get rid of these extra
decimals?
> ** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is not
working since I am using Sum for aggregation.
> Thanks a lot for your help.
>|||It works! Thanks a lot.
"Ravi Mumulla (Microsoft)" wrote:
> Go to the Textbox Properties dialog in designer and specify
> "#,##0.00%;(#,##0.00%);Zero" (without the double quotes) as the custom
> format string.
> Ravi Mumulla
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Taps" <Taps@.discussions.microsoft.com> wrote in message
> news:89671ED3-88A9-4FE3-B1FA-109DCDE50065@.microsoft.com...
> > I have a written a function where I am defining the return value as
> decimal. Now I need to do the formatting
> > to make the negative number look like (123.34%) and postive numbers as
> 123.34%. When I try to do this, I am
> > getting values like (123.3456788). How do I get rid of these extra
> decimals?
> >
> > ** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is not
> working since I am using Sum for aggregation.
> >
> > Thanks a lot for your help.
> >
> >
>
>|||Taps wrote:
> I have a written a function where I am defining the return value as
> decimal. Now I need to do the formatting to make the negative number
> look like (123.34%) and postive numbers as 123.34%. When I try to do
> this, I am getting values like (123.3456788). How do I get rid of
> these extra decimals?
> ** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is
> not working since I am using Sum for aggregation.
> Thanks a lot for your help.
Try setting format property at cell level (right click at cell or cells
then y properties set format)

debugging user defined function in query analyzer

Hi Anyone ,
how do i actually debug a User Defined Function ?
rdgs
Create a stored procedure which alls the function and debug the stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> Hi Anyone ,
> how do i actually debug a User Defined Function ?
> rdgs
|||Maxzsim
You could put in PRINT Statements at certain points in the code of the
function. Execute it from isqlw and test it. If Im not mistaken, step by step
debugging is possible from Visual Interdev.
Cheers!
SQLCatZ
"Tibor Karaszi" wrote:

> Create a stored procedure which alls the function and debug the stored procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
>
>
|||Hi,
i have problem testing the function , though i have put a breakpoint but it
never goes into the debug window , what could be the problem ?
From the Query Analyzer i can debug but all the button such as step inot ,
step over are all greyed out
i am using a LocalSystem account
rdgs
"SQLCatz" wrote:
[vbcol=seagreen]
> Maxzsim
> You could put in PRINT Statements at certain points in the code of the
> function. Execute it from isqlw and test it. If Im not mistaken, step by step
> debugging is possible from Visual Interdev.
> Cheers!
> SQLCatZ
>
> "Tibor Karaszi" wrote:
|||Did you check out the troubleshooting section for the TSQL debugger in Books Online? (Make sure you
have the latest update of Books Online...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...[vbcol=seagreen]
> Hi,
> i have problem testing the function , though i have put a breakpoint but it
> never goes into the debug window , what could be the problem ?
> From the Query Analyzer i can debug but all the button such as step inot ,
> step over are all greyed out
> i am using a LocalSystem account
> rdgs
> "SQLCatz" wrote:
|||will do tks
"Tibor Karaszi" wrote:

> Did you check out the troubleshooting section for the TSQL debugger in Books Online? (Make sure you
> have the latest update of Books Online...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...
>
>

debugging user defined function in query analyzer

Hi Anyone ,
how do i actually debug a User Defined Function ?
rdgsCreate a stored procedure which alls the function and debug the stored proce
dure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> Hi Anyone ,
> how do i actually debug a User Defined Function ?
> rdgs|||Maxzsim
You could put in PRINT Statements at certain points in the code of the
function. Execute it from isqlw and test it. If Im not mistaken, step by ste
p
debugging is possible from Visual Interdev.
Cheers!
SQLCatZ
"Tibor Karaszi" wrote:

> Create a stored procedure which alls the function and debug the stored pro
cedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
>
>|||Hi,
i have problem testing the function , though i have put a breakpoint but it
never goes into the debug window , what could be the problem ?
From the Query Analyzer i can debug but all the button such as step inot ,
step over are all greyed out
i am using a LocalSystem account
rdgs
"SQLCatz" wrote:
[vbcol=seagreen]
> Maxzsim
> You could put in PRINT Statements at certain points in the code of the
> function. Execute it from isqlw and test it. If Im not mistaken, step by s
tep
> debugging is possible from Visual Interdev.
> Cheers!
> SQLCatZ
>
> "Tibor Karaszi" wrote:
>|||Did you check out the troubleshooting section for the TSQL debugger in Books
Online? (Make sure you
have the latest update of Books Online...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...[vbcol=seagreen]
> Hi,
> i have problem testing the function , though i have put a breakpoint but i
t
> never goes into the debug window , what could be the problem ?
> From the Query Analyzer i can debug but all the button such as step inot ,
> step over are all greyed out
> i am using a LocalSystem account
> rdgs
> "SQLCatz" wrote:
>|||will do tks
"Tibor Karaszi" wrote:

> Did you check out the troubleshooting section for the TSQL debugger in Boo
ks Online? (Make sure you
> have the latest update of Books Online...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...
>
>

debugging user defined function in query analyzer

Hi Anyone ,
how do i actually debug a User Defined Function ?
rdgsCreate a stored procedure which alls the function and debug the stored procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> Hi Anyone ,
> how do i actually debug a User Defined Function ?
> rdgs|||Maxzsim
You could put in PRINT Statements at certain points in the code of the
function. Execute it from isqlw and test it. If Im not mistaken, step by step
debugging is possible from Visual Interdev.
Cheers!
SQLCatZ
"Tibor Karaszi" wrote:
> Create a stored procedure which alls the function and debug the stored procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> > Hi Anyone ,
> >
> > how do i actually debug a User Defined Function ?
> >
> > rdgs
>
>|||Hi,
i have problem testing the function , though i have put a breakpoint but it
never goes into the debug window , what could be the problem ?
From the Query Analyzer i can debug but all the button such as step inot ,
step over are all greyed out
i am using a LocalSystem account
rdgs
"SQLCatz" wrote:
> Maxzsim
> You could put in PRINT Statements at certain points in the code of the
> function. Execute it from isqlw and test it. If Im not mistaken, step by step
> debugging is possible from Visual Interdev.
> Cheers!
> SQLCatZ
>
> "Tibor Karaszi" wrote:
> > Create a stored procedure which alls the function and debug the stored procedure.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> > > Hi Anyone ,
> > >
> > > how do i actually debug a User Defined Function ?
> > >
> > > rdgs
> >
> >
> >|||Did you check out the troubleshooting section for the TSQL debugger in Books Online? (Make sure you
have the latest update of Books Online...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...
> Hi,
> i have problem testing the function , though i have put a breakpoint but it
> never goes into the debug window , what could be the problem ?
> From the Query Analyzer i can debug but all the button such as step inot ,
> step over are all greyed out
> i am using a LocalSystem account
> rdgs
> "SQLCatz" wrote:
>> Maxzsim
>> You could put in PRINT Statements at certain points in the code of the
>> function. Execute it from isqlw and test it. If Im not mistaken, step by step
>> debugging is possible from Visual Interdev.
>> Cheers!
>> SQLCatZ
>>
>> "Tibor Karaszi" wrote:
>> > Create a stored procedure which alls the function and debug the stored procedure.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> > news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
>> > > Hi Anyone ,
>> > >
>> > > how do i actually debug a User Defined Function ?
>> > >
>> > > rdgs
>> >
>> >
>> >|||will do tks
"Tibor Karaszi" wrote:
> Did you check out the troubleshooting section for the TSQL debugger in Books Online? (Make sure you
> have the latest update of Books Online...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...
> > Hi,
> >
> > i have problem testing the function , though i have put a breakpoint but it
> > never goes into the debug window , what could be the problem ?
> >
> > From the Query Analyzer i can debug but all the button such as step inot ,
> > step over are all greyed out
> >
> > i am using a LocalSystem account
> >
> > rdgs
> >
> > "SQLCatz" wrote:
> >
> >> Maxzsim
> >>
> >> You could put in PRINT Statements at certain points in the code of the
> >> function. Execute it from isqlw and test it. If Im not mistaken, step by step
> >> debugging is possible from Visual Interdev.
> >>
> >> Cheers!
> >> SQLCatZ
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> > Create a stored procedure which alls the function and debug the stored procedure.
> >> >
> >> > --
> >> > Tibor Karaszi, SQL Server MVP
> >> > http://www.karaszi.com/sqlserver/default.asp
> >> > http://www.solidqualitylearning.com/
> >> >
> >> >
> >> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> > news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> >> > > Hi Anyone ,
> >> > >
> >> > > how do i actually debug a User Defined Function ?
> >> > >
> >> > > rdgs
> >> >
> >> >
> >> >
>
>

Debugging SQL Server 2005 Stored Proc with Visual Studio 2005

Hello,

we have a SQL server 2005 with Visual studio Prof. 2005 in the

employment.

The debuggers function only in Visual studio correctly, as long as no

code on the SQL server must be implemented.

If a BREAK POINT in a Stored Procedure is set, this is not activated,

since this cannot be bound.

Does someone know, what it lies and can like one it eliminate?

Thank you for your assistance in advance.

Yours sincerely

Big_Ben_31

---

This entry was translated automatically with the translation

service babel.altavista.com from the German into English.

I will have to ask the obvious question about your setup. Have you ensured that it is complete? The new topics in BOL and MSDN describe the steps in details including troubleshooting ones. Please start with the links below:

http://msdn2.microsoft.com/en-us/library/kkyhd4yb.aspx

http://msdn2.microsoft.com/en-us/library/s4sszxst.aspx

Debugging in QA

Hi,
Could anyone please guide me how can I debug a stored proc/ function in
Query Analyzer. and Is it possible to debug a trigger same way?
Thanks
1.Make sure debugging
are enabled / installed on the sql server.
2.Trigger deb.: Use a stored procedure that fires the trigger, debug the sp,
that will let you step into the trigger.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Zero.NULL" wrote:

> Hi,
> Could anyone please guide me how can I debug a stored proc/ function in
> Query Analyzer. and Is it possible to debug a trigger same way?
> Thanks
>
|||I just want to add what I am facing when trying to debug a procedure
(created on master DB using sa login)
when I start debugging on this procedure in query analyzer, I recieve a
msg box which says:
SP debugging may not work properly if you log on as 'Local System
account'
while SQL Server is configured to run as a service.
You can open Event Viwer to see details.
Do you want to continue?
When I continue with this and execute this procedure by providing
parameter values, I get the print outputs, but procedure execution
doesn't break on break points!!
I am puzzled now how to work around with this? How can I break
execution on break points?
Thanks

Debugging in QA

Hi,
Could anyone please guide me how can I debug a stored proc/ function in
Query Analyzer. and Is it possible to debug a trigger same way?
Thanks1.Make sure debugging
are enabled / installed on the sql server.
2.Trigger deb.: Use a stored procedure that fires the trigger, debug the sp,
that will let you step into the trigger.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Zero.NULL" wrote:
> Hi,
> Could anyone please guide me how can I debug a stored proc/ function in
> Query Analyzer. and Is it possible to debug a trigger same way?
> Thanks
>|||I just want to add what I am facing when trying to debug a procedure
(created on master DB using sa login)
when I start debugging on this procedure in query analyzer, I recieve a
msg box which says:
SP debugging may not work properly if you log on as 'Local System
account'
while SQL Server is configured to run as a service.
You can open Event Viwer to see details.
Do you want to continue?
When I continue with this and execute this procedure by providing
parameter values, I get the print outputs, but procedure execution
doesn't break on break points!!
I am puzzled now how to work around with this? How can I break
execution on break points?
Thanks

Debugging in QA

Hi,
Could anyone please guide me how can I debug a stored proc/ function in
Query Analyzer. and Is it possible to debug a trigger same way?
Thanks1.Make sure debugging
are enabled / installed on the sql server.
2.Trigger deb.: Use a stored procedure that fires the trigger, debug the sp,
that will let you step into the trigger.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Zero.NULL" wrote:

> Hi,
> Could anyone please guide me how can I debug a stored proc/ function in
> Query Analyzer. and Is it possible to debug a trigger same way?
> Thanks
>|||I just want to add what I am facing when trying to debug a procedure
(created on master DB using sa login)
when I start debugging on this procedure in query analyzer, I recieve a
msg box which says:
SP debugging may not work properly if you log on as 'Local System
account'
while SQL Server is configured to run as a service.
You can open Event Viwer to see details.
Do you want to continue?
When I continue with this and execute this procedure by providing
parameter values, I get the print outputs, but procedure execution
doesn't break on break points!!
I am puzzled now how to work around with this? How can I break
execution on break points?
Thanks