Getting a stored procedures return value -- URGENT !

We've got some code that has been using a SqlCommand with the commandtype
set to Text. It is running a stored procedure by building a StringBuilder
object to string together the parameters and then execute. The problem I am
running into is that if I add a parameter to the commands paramter
collection and designate it as the return value in the "direction"
parameter, I never get the value returned.
I'm assuming it is because when executing a stored proc in this manner
(instead of using commandtype of StoredProcedure) that the stored procedure
is actually considered to be nested within the "procedural" code I'm
executing as text. Does this make sense? I hope that explanation is clear
enough. I really need to be able to access these return codes without
rewriting the world. As it is now they have all their stored procs doing a
"select ##" to send a return code back to their C# code. This is ludicrous
and I cannot reuse any of these storedprocs from another stored proc. I
don't see anyway to get the select results of a nested stored proc...
I'm on a tight deadline here haven't much time to solve this before writing
it over would be faster.
Any help is greatly appreciated!Hi
Did you check out:
http://msdn.microsoft.com/library/d...r />
outas.asp
The return values are only available once all result sets have been
processed.
John
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>|||> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
If you must stick with CommandType.Text for now, you might try passing the
return code value as an output parameter value. At least that will lessen
the immediate code changes needed.
As you probably know, it's generally a bad technique to build literal
strings instead of using parameterized procs and queries. When you get
around to converting to CommandType.StoredProcedure, ditch the StringBuilder
and use input parameters instead as well as the proper return value
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>

Getting a 404/401 on a long running report

Greetings.
I have a report that I am running through ASP.Net with the rs.render method.
When I have a longer date range, the execution time of the query gets longer
and I start to get funky errors. Namely, at around 3 minutes, I get
prompted for network credentials by the standard IE prompt. It doesn't
accept what they are, and after a few times, it takes me to a 404 saying that
the page can't be found. If I try to cancel the prompt, I get a 401 that I
don't have access. I'm not sure what else to look at here. This report has
worked fine in the past, and there are no problems if I just scale back the
date range.
I have the render make a pdf file in a virtual directory on my web server
and then my postback page has a client script inserted to open a window
showing that pdf file.
Anyone have any ideas?
Dale.I fixed it.
It turns out that the HTTP connection timeout settings needed to be
increased a little bit to allow the report to complete. The default of 120
seconds had worked fine in IIS 5 on Windows 2000 Server, but the same value
in IIS 6 on Windows 2003 Server didn't fare too well.
Also, I wound up re-writing the stored proc for the report. The original
author of it had cursors and correlated subqueries doing things they were
never intended to do. Execution time is now 33s instead of 10m15s.
:-)
"Dale M." wrote:
> Greetings.
> I have a report that I am running through ASP.Net with the rs.render method.
> When I have a longer date range, the execution time of the query gets longer
> and I start to get funky errors. Namely, at around 3 minutes, I get
> prompted for network credentials by the standard IE prompt. It doesn't
> accept what they are, and after a few times, it takes me to a 404 saying that
> the page can't be found. If I try to cancel the prompt, I get a 401 that I
> don't have access. I'm not sure what else to look at here. This report has
> worked fine in the past, and there are no problems if I just scale back the
> date range.
> I have the render make a pdf file in a virtual directory on my web server
> and then my postback page has a client script inserted to open a window
> showing that pdf file.
> Anyone have any ideas?
> Dale.

Getting "infinity" when running this expression

When I run this expression, percentage, I'm still getting infinity as my
return when I have a zero in one of the columns
=IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))=0, 0,
SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))) /
IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1,
Fields!APR_CNT.Value,0)) = 0, 1, SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value)
= DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0)))
Any ideas where I'm screwing up... Thanks in advanceThe IIF is a VB function. All function arguments are evaluated immediately
before the function is called. So this will fail: IIF(1 = 1, 1/1, 1/0) even
though it seems like 1/0 should not be evaluated because the condition (1=1)
is true.
This is what most likely happening in your expression.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:C3944D44-31B9-449C-ABC3-DFF6036BC46D@.microsoft.com...
> When I run this expression, percentage, I'm still getting infinity as my
> return when I have a zero in one of the columns
> =IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) =DATEPART("yyyy",NOW()),
> Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) => DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))=0, 0,
> SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
> Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) => DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))) /
> IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) =DATEPART("yyyy",NOW())-1,
> Fields!APR_CNT.Value,0)) = 0, 1,
SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value)
> = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0)))
>
> Any ideas where I'm screwing up... Thanks in advance

GetReportParameters -> 400 - Bad Request

Hello,
I want to iterate throught the parameter collection of a report on a report
server running in sharepoint integration mode. To do that I use this code:
ReportingService2006 rs = new ReportingService2006();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
foreach (ReportParameter parameter in
rs.GetReportParameters(ConfigurationManager.AppSettings["RS_ReportParameters"],
null, null, null))
{
Response.Write(parameter.Name);
}
Where RS_ReportParameters is like this in the web.config:
<add key="RS_ReportParameters"
value="http://intranet/sitedirectory/rapportage/rapporten/testvoorparameters.rdl"/>
This gives me a 400 - Bad Request error.
Does anyone know how to solve this' Running the actual report goes well...
Best regards,
PeterSome more information:
when I use this URL for the webservice:
http://vpc-ssrs:8080/ReportServer/ReportService2006.asmx
I get: The request failed with HTTP status 401: Unauthorized
When I use this:
http://vpc-ssrs/_vti_bin/ReportServer/ReportService2006.asmx
I get: Bad request and also other methods don't work.
So how do solve this 401 error?
Best regards,
Peter
-- Original Message --
From: "Peter" <peter@.giessen.remthisfornospam.nl>
Newsgroups: microsoft.public.sqlserver.reportingsvcs
Sent: Tuesday, November 06, 2007 10:46 AM
Subject: GetReportParameters -> 400 - Bad Request
> Hello,
> I want to iterate throught the parameter collection of a report on a
> report server running in sharepoint integration mode. To do that I use
> this code:
>
> ReportingService2006 rs = new ReportingService2006();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> foreach (ReportParameter parameter in
> rs.GetReportParameters(ConfigurationManager.AppSettings["RS_ReportParameters"],
> null, null, null))
> {
> Response.Write(parameter.Name);
> }
> Where RS_ReportParameters is like this in the web.config:
> <add key="RS_ReportParameters"
> value="http://intranet/sitedirectory/rapportage/rapporten/testvoorparameters.rdl"/>
> This gives me a 400 - Bad Request error.
> Does anyone know how to solve this' Running the actual report goes
> well...
> Best regards,
> Peter|||Nevermind, solved it... thanks for reading anyway ;)
"Peter" <peter@.giessen.remthisfornospam.nl> wrote in message
news:6D4F92F8-9BFC-4C1B-9C29-BEC328685CB4@.microsoft.com...
> Some more information:
>
> when I use this URL for the webservice:
> http://vpc-ssrs:8080/ReportServer/ReportService2006.asmx
> I get: The request failed with HTTP status 401: Unauthorized
> When I use this:
> http://vpc-ssrs/_vti_bin/ReportServer/ReportService2006.asmx
> I get: Bad request and also other methods don't work.
> So how do solve this 401 error?
> Best regards,
> Peter
> -- Original Message --
> From: "Peter" <peter@.giessen.remthisfornospam.nl>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Sent: Tuesday, November 06, 2007 10:46 AM
> Subject: GetReportParameters -> 400 - Bad Request
>
>> Hello,
>> I want to iterate throught the parameter collection of a report on a
>> report server running in sharepoint integration mode. To do that I use
>> this code:
>>
>> ReportingService2006 rs = new ReportingService2006();
>> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
>> foreach (ReportParameter parameter in
>> rs.GetReportParameters(ConfigurationManager.AppSettings["RS_ReportParameters"],
>> null, null, null))
>> {
>> Response.Write(parameter.Name);
>> }
>> Where RS_ReportParameters is like this in the web.config:
>> <add key="RS_ReportParameters"
>> value="http://intranet/sitedirectory/rapportage/rapporten/testvoorparameters.rdl"/>
>> This gives me a 400 - Bad Request error.
>> Does anyone know how to solve this' Running the actual report goes
>> well...
>> Best regards,
>> Peter
>

GetIsClustered() returns FALSE for Veritas Cluster Server

With both SQL Server 2000 and SQL Server 2005 when running in a Veritas Cluster, the GetIsClustered() API returns FALSE.

Does anyone know if this is working as designed?

Does GetIsClustered() really only mean MSCS clustered or should Veritas be setting that server property?

Thanks

Del Hoobler

Posting the last email thread that we had on the topic.

From: Michiel Wories
Sent: Monday, January 09, 2006 10:34 PM
To: 'Del Hoobler'
Cc: Mark Wistrom
Subject: RE: SQL Server 2005 - "IsClustered" - Is it just for MSCS?

Hi Del, as it seems Veritas clusters do not express their ‘cluster-ness’ through this serverproperty, so you would need to check with them how this is done. This is specific to their implementation so I am afraid I cannot help you with that.

Thanks,

Michiel

From: Del Hoobler
Sent: Monday, January 09, 2006 6:52 PM
To: Michiel Wories
Cc: Mark Wistrom
Subject: RE: SQL Server 2005 - "IsClustered" - Is it just for MSCS?

Hi Michiel,
Yes... this is what gets called for GetIsClustered(): (Here is the profile snippet):
14:29.9 select convert(int, serverproperty(N'isclustered'))
14:29.9 select convert(int, serverproperty(N'isclustered'))
14:29.9 select convert(int, serverproperty(N'isclustered'))
But... it sounds like what Mark is saying is that this field only pertains to MSCS... and we need
another way to determine if the server is clustered. Do you agree?

Thanks,
Del

getdate() problem: where is the time taken from ?

Hi,

I have a funny situation.
Within: MSSQL 2000 SP3, everything below described is running on same
PC.

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

Now, two other applications, are getting this information at the same
moment.
FIRST of this applications, updates local time of the computer with
the time recieved.
SECOND application, writes a protocol to file, with timestamp read at
moment of writing from operating system.
Until now, all times are equal (the differences are not biger that
ms).

Now, the SECOND application, after writing a log into file (with
proper timestamp), calls SP in database.
It passes as input prm. the time recieved from very first program,
which is the same time as the current system time, which is the same
time the SECOND application writes to the log file.

This SP (besides other things) at the very beginning writes a log into
table, where two times are logged:
- getdate() to first column,
- timestamp recieved as input parameter.

Now the funny thing.
I would expect, the times are equal.
getdate() = '2007.04.25 10:00:00.000'
prm_recieved = '2007.04.25 10:00:00.000'

I would expect, that the time from getdate() will be shifted with
miliseconds (because of call etc).
getdate() = '2007.04.25 10:00:00.123'
prm_recieved = '2007.04.25 10:00:00.000'

I would even expect, that the time is shifted 5 seconds ahead:
getdate() = '2007.04.25 10:00:05.000'
prm_recieved = '2007.04.25 10:00:00.000'

or, 5 seconds and some miliseconds:
getdate() = '2007.04.25 10:00:05.123'
prm_recieved = '2007.04.25 10:00:00.000'

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

It seams to me, the getdate is getting somehow the PERVIOUS local
system time, which was acctualy already upgraded ! Becasue all other
app's are having the proper value.

All other apps are writen in C++ and are very simple.
I was trying to set the SQLServer running property higher - with no
result.
I need to mention, there is SQLServer Agent running, and one procedure
with endless loop, with waitfor delay equal 2 seconds.
But non of them (changing the waitfor delay to other value, disabling
SQLAgent) fixes the problem.

Can somebody then tell me, where from is the time taken, or what is
the root problem of this issue?
Or what can it be?

Best regards,

MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

So you have an application that modifies the computer clock every
second, and now you are asking why:

Quote:

Originally Posted by

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :
>
getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

getdate() does not always reflect you recently updated system time.

I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.

One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

--
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|||First, thanks Erland for the answer!

Quote:

Originally Posted by

So you have an application that modifies the computer clock every
second, and now you are asking why:

:) No ... of course, I'm not confused about the time changes :)
I'm expecting them ...
Confused for me, was this:

Quote:

Originally Posted by

Quote:

Originally Posted by

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

>
getdate() does not always reflect you recently updated system time.
>
I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.
>
One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

Ok.
So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.
That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

Thank's Erland again for help.

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.

I like to stress that is my own speculation of how the internals work.

Quote:

Originally Posted by

That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

If my theory is correct, yes, this appears to be a correct conclusion.

Quote:

Originally Posted by

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

I would expect that CURRENT_TIMESTAMP to be just a synonym fot getdate(). It
would be funny if two equivalent functions are implemented in different
ways.

I also like to point out that this kind of behaviour that could be different
in different versions of SQL Server, or even in different service packs.

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

Quote:

Originally Posted by

Quote:

Originally Posted by

The getdate() is not taking the current system time, only the buffered
SQLServer system time.

>
I like to stress that is my own speculation of how the internals work.
>

Quote:

Originally Posted by

That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

>
If my theory is correct, yes, this appears to be a correct conclusion.

I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Since all this is about behaviour that is strictly internal to SQL Server,
the likelyhood that there is a interface, documented or undocumented,
to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

If you are on SQL 2005, you could write a CLR function which retrieves
the system time from Windows, with the regular 100 ns precision.

If you are on SQL 2000, you would have to write an extended stored
procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

But getdate() seems dead in the water when you are living in the fast lane
like you do.

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

Quote:

Originally Posted by

to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

I wont :) Have other things to do as well :)

Quote:

Originally Posted by

If you are on SQL 2005, you could write a CLR function which retrieves

I'm on 2000 ... right now ...

Quote:

Originally Posted by

If you are on SQL 2000, you would have to write an extended stored

This is what I was thinking of...

Quote:

Originally Posted by

procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

This is what I was afraid of :(

Quote:

Originally Posted by

But getdate() seems dead in the water when you are living in the fast lane
like you do.

I just need to change aproach probably, and try to solve it in
different way.
Probably the solution is what I did right now ...
Just, a the beginning, I'm sending procedure to sleep (waitfor delay).
It is not nice, and slows everything down, but maybe ... will be fast
enougth...
Otherwise ... try to do smth. else.

Thanks Erland again for your help and patience.

Best regards

Matik

GETDATE() Function Issue

Hi Everyone,
I'm running into an issue trying to update a column on a remote database.
The server is linked from the server I'm calling this update statement. The
syntax is as follows:
UPDATE Server1.Test.dbo.Testdata
SET dtUpdated = GETDATE()
WHERE intID = 111
The problem is that the update takes a very long time to complete. Now if I
declare a variable with a datetime and set that variable to GETDATE(), the
update takes seconds. Is there some known issue? Can anyone help with this
?
I have noticed that I can update small size tables, but it takes much longer
to update large size tables. The columns in the "Where" clause have indexes
on them and that can't be the issue since my test using the variable worked
quicker. I'm just wondering why I can't use the GETDATE() function directly
.
DarrenDarren
No, it works fine with GETDATE() function too. Please take a look at
execution plan of the query (with GETDATE() and without) and see what is
going on
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
> Hi Everyone,
> I'm running into an issue trying to update a column on a remote database.
> The server is linked from the server I'm calling this update statement.
> The
> syntax is as follows:
> UPDATE Server1.Test.dbo.Testdata
> SET dtUpdated = GETDATE()
> WHERE intID = 111
> The problem is that the update takes a very long time to complete. Now if
> I
> declare a variable with a datetime and set that variable to GETDATE(), the
> update takes seconds. Is there some known issue? Can anyone help with
> this?
> I have noticed that I can update small size tables, but it takes much
> longer
> to update large size tables. The columns in the "Where" clause have
> indexes
> on them and that can't be the issue since my test using the variable
> worked
> quicker. I'm just wondering why I can't use the GETDATE() function
> directly.
> Darren|||Run it again and see if there is any issues
Madhivanan|||I looked at the execution plan and it is doing what I thought it would. The
execution plan shows that it does a remote scan to look at the whole table
then it will filter by the where clause. This is the source of my problem.
For a large table that holds over 4 million records, it has to scan that
whole table before filtering on the primary key I'm already passing it. I
don't understand why on the remote server it must scan the whole table
instead of already filtering by the where clause. When I use a variable to
update the date column it shows a remote query for the update. Why is this
happening? Did some setting happen that I don't know about? How can I trac
k
down this issue?
"Uri Dimant" wrote:

> Darren
> No, it works fine with GETDATE() function too. Please take a look at
> execution plan of the query (with GETDATE() and without) and see what is
> going on
>
>
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
>
>

Get user syntax

I am building a view for a report and one of the requirements is to grab the
user who is running the report. What would this syntax look like? I thought
it was getuser(), but no luck there.
Thanks,
Ryan
Ryan,
Depending on whether you want a User or a Login, you can try one of the
following: CURRENT_USER, SYSTEM_USER, USER_NAME, SUSER_SNAME. Check them
out in the Books Online, or just run them and choose what you prefer to use.
RLF
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:266EBEEE-3FB5-448D-A58B-0EF6D8F006F1@.microsoft.com...
>I am building a view for a report and one of the requirements is to grab
>the
> user who is running the report. What would this syntax look like? I
> thought
> it was getuser(), but no luck there.
> Thanks,
> Ryan

Get Sql Err Message 15422 when activating application role.

Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX).

Developing a desktop application which calls a local instance of ".\sqlexpress".

This app needs to set data base options and add/del various table columns.

When activating the application role, I get the following message:

HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level.

Anyone know what this message means?

I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious.

Thanks

Gary

You cannot call sp_setapprole or sp_unsetapprole within another stored procedure. These procedures must be called directly (that's what the message means by ad hoc level).

Here's the sp_setapprole topic:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_sa-sz_6tt1.asp

It mentions in the remarks section that: "The sp_setapprole stored procedure can be executed only by direct Transact-SQL statements; it cannot be executed within another stored procedure or from within a user-defined transaction."

Thanks
Laurentiu

|||Many thanks Laurentiu!!!

Obviously, I will not continue going down a path which is not intended to work. You have save me time and agravitation.

Again, Thanks!!!

Gary|||Hi,

I have being trying to setup application roles in my application. I have been reading a couple of threads (the one with Ian), and am still a bit lost.

When you say that application roles must be called directly, how do you get the 3rd party application to to call this SP via ODBC and if you have to manualy type in the SP with the password, doesn't that allow the user (inputer) access to the database.

Please help.|||

You must make the calls directly from your application code, not by embedding them in a stored procedure and calling the stored procedure from the application code. Your application should issue the setapprole request directly, not call a procedure that calls sp_setapprole.

Thanks
Laurentiu

|||This is a snippet of C/C++ ODBC code I'm using. I have a valid statement handle allocated and is freed upon function return.

sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[0], 0, app, parmlen[0], &cb[0]);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[1], 0, pw, parmlen[1], &cb[1]);

strcpy(buff, "{call sp_setapprole (?,?)}");
sqlrc = SQLExecDirect(hstmt, buff, SQL_NTS);

I have examined the stored proc code and have noticed that it signals this error when the nest level is greater than 1. Is there some code in ODBC that creates a SP on the fly and then calls the SQLExecDirect ?

Many thanks.|||

I am not sure what you are asking for. For ODBC questions, you may want to post on the SQL Server Data Access forum.

If you want to find out how to set an application role withing your application, here is a simple C# example that does that. It assumes you have created the application role in a database named test with the following commands:

create database test

use test

create application role approle with password = 'Password)^'

In the following function, you should replace the YOURSERVERNAME string with the name of your SQL Server instance:

static void sql_test()
{
SqlConnection sqlConn = null;
String strConn = "Persist Security Info=false;";

strConn += "Server=\'" + "YOURSERVERNAME"
+ "\';Integrated Security=true;";

try
{
sqlConn = new SqlConnection(strConn);
sqlConn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlConn.ChangeDatabase("test");

SqlCommand sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("sp_setapprole 'approle', 'Password)^'", sqlConn);
try
{
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}
}

This should print your original context and then the context of approle, showing that approle was set.

Thanks
Laurentiu

Get Sql Err Message 15422 when activating application role.

Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX).

Developing a desktop application which calls a local instance of ".\sqlexpress".

This app needs to set data base options and add/del various table columns.

When activating the application role, I get the following message:

HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level.

Anyone know what this message means?

I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious.

Thanks

Gary

You cannot call sp_setapprole or sp_unsetapprole within another stored procedure. These procedures must be called directly (that's what the message means by ad hoc level).

Here's the sp_setapprole topic:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_sa-sz_6tt1.asp

It mentions in the remarks section that: "The sp_setapprole stored procedure can be executed only by direct Transact-SQL statements; it cannot be executed within another stored procedure or from within a user-defined transaction."

Thanks
Laurentiu

|||Many thanks Laurentiu!!!

Obviously, I will not continue going down a path which is not intended to work. You have save me time and agravitation.

Again, Thanks!!!

Gary|||Hi,

I have being trying to setup application roles in my application. I have been reading a couple of threads (the one with Ian), and am still a bit lost.

When you say that application roles must be called directly, how do you get the 3rd party application to to call this SP via ODBC and if you have to manualy type in the SP with the password, doesn't that allow the user (inputer) access to the database.

Please help.|||

You must make the calls directly from your application code, not by embedding them in a stored procedure and calling the stored procedure from the application code. Your application should issue the setapprole request directly, not call a procedure that calls sp_setapprole.

Thanks
Laurentiu

|||This is a snippet of C/C++ ODBC code I'm using. I have a valid statement handle allocated and is freed upon function return.

sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[0], 0, app, parmlen[0], &cb[0]);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[1], 0, pw, parmlen[1], &cb[1]);

strcpy(buff, "{call sp_setapprole (?,?)}");
sqlrc = SQLExecDirect(hstmt, buff, SQL_NTS);

I have examined the stored proc code and have noticed that it signals this error when the nest level is greater than 1. Is there some code in ODBC that creates a SP on the fly and then calls the SQLExecDirect ?

Many thanks.|||

I am not sure what you are asking for. For ODBC questions, you may want to post on the SQL Server Data Access forum.

If you want to find out how to set an application role withing your application, here is a simple C# example that does that. It assumes you have created the application role in a database named test with the following commands:

create database test

use test

create application role approle with password = 'Password)^'

In the following function, you should replace the YOURSERVERNAME string with the name of your SQL Server instance:

static void sql_test()
{
SqlConnection sqlConn = null;
String strConn = "Persist Security Info=false;";

strConn += "Server=\'" + "YOURSERVERNAME"
+ "\';Integrated Security=true;";

try
{
sqlConn = new SqlConnection(strConn);
sqlConn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlConn.ChangeDatabase("test");

SqlCommand sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("sp_setapprole 'approle', 'Password)^'", sqlConn);
try
{
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}
}

This should print your original context and then the context of approle, showing that approle was set.

Thanks
Laurentiu

Get server name

Is there any way to get the SQL Server's name without running SELECT
@.@.SERVERNAME? I want a DTS package that I am working on to be able to
migrate from the dev server to the live server without having any connection
s
to the dev server. To run the query to find the server name, you need a SQL
server to run the query on. However, the dev server is not guaranteed to be
accessible to run the query on and I don't know the name of the servers that
this will be migrated to.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsHi
Unless you are using a default connection your installation process should
really ask for the Sqlserver name/instance. You could look at deciphering th
e
output of NET START. If you only want the computer name the command prompt
command HOSTNAME will give you it.
You may want to look at:
http://www.sqldts.com/default.aspx?242
John
"Chris Lieb" wrote:

> Is there any way to get the SQL Server's name without running SELECT
> @.@.SERVERNAME? I want a DTS package that I am working on to be able to
> migrate from the dev server to the live server without having any connecti
ons
> to the dev server. To run the query to find the server name, you need a S
QL
> server to run the query on. However, the dev server is not guaranteed to
be
> accessible to run the query on and I don't know the name of the servers th
at
> this will be migrated to.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps

Get sa password

Hey,
I admin an company with MS SQL-Server. They forget the sa-password. Is there
any way to get the password from an running sql-server? I need it for
SQL-Backup!
Thanks for answere
Frank
Are you using mixed mode that has an NT account with sysadmin rights? if so,
log that account then change the sa password.
What version are you running?
Thomas
"Frank Moskopp" wrote:

> Hey,
> I admin an company with MS SQL-Server. They forget the sa-password. Is there
> any way to get the password from an running sql-server? I need it for
> SQL-Backup!
> Thanks for answere
> Frank
>
>
|||I have no access with my NT accounts. The company run SQL-Server 2000.
Thanks for more information
Frank
"Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...[vbcol=seagreen]
> Are you using mixed mode that has an NT account with sysadmin rights? if
> so,
> log that account then change the sa password.
> What version are you running?
>
> --
> Thomas
>
> "Frank Moskopp" wrote:
|||What service account is SQL running under ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
news:djrccf$qtb$1@.newsreader3.netcologne.de...
>I have no access with my NT accounts. The company run SQL-Server 2000.
> Thanks for more information
> Frank
>
> "Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...
>
|||If you have NT authentication enabled you should be able to logon as a local
administrator and then change the sa password (this assumes that builtin
admins has not been removed from SQL)
http://vyaskn.tripod.com/administration_faq.htm#q9
Thomas
"Frank Moskopp" wrote:

> I have no access with my NT accounts. The company run SQL-Server 2000.
> Thanks for more information
> Frank
>
> "Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...
>
>
|||Hi,
Talk to you system admin about this. Using his windows admin login login to
server and connect to SQL Server using Windows authentication.
After that change your SA password using (SP_Password null,newpassword,sa).
After this you could login using new password and do necessary Admin tasks.
Thanks
Hari
SQL Server MVP
"Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
news:djr6ja$f3q$1@.newsreader3.netcologne.de...
> Hey,
> I admin an company with MS SQL-Server. They forget the sa-password. Is
> there any way to get the password from an running sql-server? I need it
> for SQL-Backup!
> Thanks for answere
> Frank
>
|||I have 3 mssql-services. It's run on an SBS 2003 Standard-Server. The
application 'Act!' install the SQL-Server. I coundn't set an sa password.
The application manage the service of MSSQL$ACT7. The service of
'Sharepoint' run with the NT admin account.
I need the sa password for my Veritas SQL-Backup and maybe for maintenance.
Services:
MSSQL$ACT7
MSSQL$Sharepoint
MSSQL$xxxx
Frank
"Jasper Smith" <jasper_smith9@.hotmail.com> schrieb im Newsbeitrag
news:u31L0az2FHA.1980@.TK2MSFTNGP15.phx.gbl...
> What service account is SQL running under ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
> news:djrccf$qtb$1@.newsreader3.netcologne.de...
>
|||Hey Thomas,
The progamm 'act' manage the MSSQL-service. I need the actually password and
not any new password.
Frank
"Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A17984F0-5540-4BC1-9A2E-C69AB3647256@.microsoft.com...[vbcol=seagreen]
> If you have NT authentication enabled you should be able to logon as a
> local
> administrator and then change the sa password (this assumes that builtin
> admins has not been removed from SQL)
> http://vyaskn.tripod.com/administration_faq.htm#q9
>
> --
> Thomas
>
> "Frank Moskopp" wrote:
|||Hey Hari,
The progamm 'act' manage the MSSQL-service. I need the actually password and
not any new password.
Thanks
Frank
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:e%23o60642FHA.3276@.TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Talk to you system admin about this. Using his windows admin login login
> to server and connect to SQL Server using Windows authentication.
> After that change your SA password using (SP_Password
> null,newpassword,sa).
> After this you could login using new password and do necessary Admin
> tasks.
> Thanks
> Hari
> SQL Server MVP
>
> "Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
> news:djr6ja$f3q$1@.newsreader3.netcologne.de...
>
|||Frank Moskopp wrote:
> Hey Hari,
> The progamm 'act' manage the MSSQL-service. I need the actually password and
> not any new password.
> Thanks
> Frank
> "Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> news:e%23o60642FHA.3276@.TK2MSFTNGP10.phx.gbl...
>
You can't get the actual password for sa- you'll have to enter a new
one. You might be able to contact the application vendor and ask them
for the sa password since it sounds like the program is assigning a sa
password during installation. Maybe it's a standard password (God forbid
it...) and then they can tell you what it is.
Regards
Steen

Get sa password

Hey,
I admin an company with MS SQL-Server. They forget the sa-password. Is there
any way to get the password from an running sql-server? I need it for
SQL-Backup!
Thanks for answere
FrankAre you using mixed mode that has an NT account with sysadmin rights? if so,
log that account then change the sa password.
What version are you running?
Thomas
"Frank Moskopp" wrote:
> Hey,
> I admin an company with MS SQL-Server. They forget the sa-password. Is there
> any way to get the password from an running sql-server? I need it for
> SQL-Backup!
> Thanks for answere
> Frank
>
>|||I have no access with my NT accounts. The company run SQL-Server 2000.
Thanks for more information
Frank
"Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...
> Are you using mixed mode that has an NT account with sysadmin rights? if
> so,
> log that account then change the sa password.
> What version are you running?
>
> --
> Thomas
>
> "Frank Moskopp" wrote:
>> Hey,
>> I admin an company with MS SQL-Server. They forget the sa-password. Is
>> there
>> any way to get the password from an running sql-server? I need it for
>> SQL-Backup!
>> Thanks for answere
>> Frank
>>|||What service account is SQL running under ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
news:djrccf$qtb$1@.newsreader3.netcologne.de...
>I have no access with my NT accounts. The company run SQL-Server 2000.
> Thanks for more information
> Frank
>
> "Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...
>> Are you using mixed mode that has an NT account with sysadmin rights? if
>> so,
>> log that account then change the sa password.
>> What version are you running?
>>
>> --
>> Thomas
>>
>> "Frank Moskopp" wrote:
>> Hey,
>> I admin an company with MS SQL-Server. They forget the sa-password. Is
>> there
>> any way to get the password from an running sql-server? I need it for
>> SQL-Backup!
>> Thanks for answere
>> Frank
>>
>|||If you have NT authentication enabled you should be able to logon as a local
administrator and then change the sa password (this assumes that builtin
admins has not been removed from SQL)
http://vyaskn.tripod.com/administration_faq.htm#q9
Thomas
"Frank Moskopp" wrote:
> I have no access with my NT accounts. The company run SQL-Server 2000.
> Thanks for more information
> Frank
>
> "Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...
> > Are you using mixed mode that has an NT account with sysadmin rights? if
> > so,
> > log that account then change the sa password.
> >
> > What version are you running?
> >
> >
> > --
> > Thomas
> >
> >
> > "Frank Moskopp" wrote:
> >
> >> Hey,
> >>
> >> I admin an company with MS SQL-Server. They forget the sa-password. Is
> >> there
> >> any way to get the password from an running sql-server? I need it for
> >> SQL-Backup!
> >>
> >> Thanks for answere
> >>
> >> Frank
> >>
> >>
> >>
>
>|||Hi,
Talk to you system admin about this. Using his windows admin login login to
server and connect to SQL Server using Windows authentication.
After that change your SA password using (SP_Password null,newpassword,sa).
After this you could login using new password and do necessary Admin tasks.
Thanks
Hari
SQL Server MVP
"Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
news:djr6ja$f3q$1@.newsreader3.netcologne.de...
> Hey,
> I admin an company with MS SQL-Server. They forget the sa-password. Is
> there any way to get the password from an running sql-server? I need it
> for SQL-Backup!
> Thanks for answere
> Frank
>|||I have 3 mssql-services. It's run on an SBS 2003 Standard-Server. The
application 'Act!' install the SQL-Server. I coundn't set an sa password.
The application manage the service of MSSQL$ACT7. The service of
'Sharepoint' run with the NT admin account.
I need the sa password for my Veritas SQL-Backup and maybe for maintenance.
Services:
MSSQL$ACT7
MSSQL$Sharepoint
MSSQL$xxxx
Frank
"Jasper Smith" <jasper_smith9@.hotmail.com> schrieb im Newsbeitrag
news:u31L0az2FHA.1980@.TK2MSFTNGP15.phx.gbl...
> What service account is SQL running under ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
> news:djrccf$qtb$1@.newsreader3.netcologne.de...
>>I have no access with my NT accounts. The company run SQL-Server 2000.
>> Thanks for more information
>> Frank
>>
>> "Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
>> news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...
>> Are you using mixed mode that has an NT account with sysadmin rights? if
>> so,
>> log that account then change the sa password.
>> What version are you running?
>>
>> --
>> Thomas
>>
>> "Frank Moskopp" wrote:
>> Hey,
>> I admin an company with MS SQL-Server. They forget the sa-password. Is
>> there
>> any way to get the password from an running sql-server? I need it for
>> SQL-Backup!
>> Thanks for answere
>> Frank
>>
>>
>|||Hey Thomas,
The progamm 'act' manage the MSSQL-service. I need the actually password and
not any new password.
Frank
"Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A17984F0-5540-4BC1-9A2E-C69AB3647256@.microsoft.com...
> If you have NT authentication enabled you should be able to logon as a
> local
> administrator and then change the sa password (this assumes that builtin
> admins has not been removed from SQL)
> http://vyaskn.tripod.com/administration_faq.htm#q9
>
> --
> Thomas
>
> "Frank Moskopp" wrote:
>> I have no access with my NT accounts. The company run SQL-Server 2000.
>> Thanks for more information
>> Frank
>>
>> "Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
>> news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...
>> > Are you using mixed mode that has an NT account with sysadmin rights?
>> > if
>> > so,
>> > log that account then change the sa password.
>> >
>> > What version are you running?
>> >
>> >
>> > --
>> > Thomas
>> >
>> >
>> > "Frank Moskopp" wrote:
>> >
>> >> Hey,
>> >>
>> >> I admin an company with MS SQL-Server. They forget the sa-password. Is
>> >> there
>> >> any way to get the password from an running sql-server? I need it for
>> >> SQL-Backup!
>> >>
>> >> Thanks for answere
>> >>
>> >> Frank
>> >>
>> >>
>> >>
>>|||Hey Hari,
The progamm 'act' manage the MSSQL-service. I need the actually password and
not any new password.
Thanks
Frank
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:e%23o60642FHA.3276@.TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Talk to you system admin about this. Using his windows admin login login
> to server and connect to SQL Server using Windows authentication.
> After that change your SA password using (SP_Password
> null,newpassword,sa).
> After this you could login using new password and do necessary Admin
> tasks.
> Thanks
> Hari
> SQL Server MVP
>
> "Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
> news:djr6ja$f3q$1@.newsreader3.netcologne.de...
>> Hey,
>> I admin an company with MS SQL-Server. They forget the sa-password. Is
>> there any way to get the password from an running sql-server? I need it
>> for SQL-Backup!
>> Thanks for answere
>> Frank
>|||Frank Moskopp wrote:
> Hey Hari,
> The progamm 'act' manage the MSSQL-service. I need the actually password and
> not any new password.
> Thanks
> Frank
> "Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> news:e%23o60642FHA.3276@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>>
>> Talk to you system admin about this. Using his windows admin login login
>> to server and connect to SQL Server using Windows authentication.
>> After that change your SA password using (SP_Password
>> null,newpassword,sa).
>> After this you could login using new password and do necessary Admin
>> tasks.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
>> news:djr6ja$f3q$1@.newsreader3.netcologne.de...
>> Hey,
>> I admin an company with MS SQL-Server. They forget the sa-password. Is
>> there any way to get the password from an running sql-server? I need it
>> for SQL-Backup!
>> Thanks for answere
>> Frank
>>
>
You can't get the actual password for sa- you'll have to enter a new
one. You might be able to contact the application vendor and ask them
for the sa password since it sounds like the program is assigning a sa
password during installation. Maybe it's a standard password (God forbid
it...) and then they can tell you what it is.
Regards
Steen|||Hey Steen
I contakt the app. venndor and they told me the install-routine create an
random password and its possible to get the password (for money). By
ordering the act-program we get the SQL-server. But these is not an
Standard-SQL-Server. If I have the sa-password I could use this trimmed
Server with managed tools full. I doesn't want open the SQL-Server. Only for
Backup
Frank
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> schrieb im Newsbeitrag
news:Om59K752FHA.3732@.TK2MSFTNGP15.phx.gbl...
> Frank Moskopp wrote:
>> Hey Hari,
>> The progamm 'act' manage the MSSQL-service. I need the actually password
>> and
>> not any new password.
>> Thanks
>> Frank
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
>> news:e%23o60642FHA.3276@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>>
>> Talk to you system admin about this. Using his windows admin login login
>> to server and connect to SQL Server using Windows authentication.
>> After that change your SA password using (SP_Password
>> null,newpassword,sa).
>> After this you could login using new password and do necessary Admin
>> tasks.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
>> news:djr6ja$f3q$1@.newsreader3.netcologne.de...
>> Hey,
>> I admin an company with MS SQL-Server. They forget the sa-password. Is
>> there any way to get the password from an running sql-server? I need it
>> for SQL-Backup!
>> Thanks for answere
>> Frank
>>
>>
> You can't get the actual password for sa- you'll have to enter a new one.
> You might be able to contact the application vendor and ask them for the
> sa password since it sounds like the program is assigning a sa password
> during installation. Maybe it's a standard password (God forbid it...) and
> then they can tell you what it is.
> Regards
> Steen|||Frank Moskopp wrote:
> Hey Steen
> I contakt the app. venndor and they told me the install-routine create an
> random password and its possible to get the password (for money). By
> ordering the act-program we get the SQL-server. But these is not an
> Standard-SQL-Server. If I have the sa-password I could use this trimmed
> Server with managed tools full. I doesn't want open the SQL-Server. Only for
> Backup
>
> Frank
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> schrieb im Newsbeitrag
> news:Om59K752FHA.3732@.TK2MSFTNGP15.phx.gbl...
>> Frank Moskopp wrote:
>> Hey Hari,
>> The progamm 'act' manage the MSSQL-service. I need the actually password
>> and
>> not any new password.
>> Thanks
>> Frank
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
>> news:e%23o60642FHA.3276@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>>
>> Talk to you system admin about this. Using his windows admin login login
>> to server and connect to SQL Server using Windows authentication.
>> After that change your SA password using (SP_Password
>> null,newpassword,sa).
>> After this you could login using new password and do necessary Admin
>> tasks.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
>> news:djr6ja$f3q$1@.newsreader3.netcologne.de...
>> Hey,
>> I admin an company with MS SQL-Server. They forget the sa-password. Is
>> there any way to get the password from an running sql-server? I need it
>> for SQL-Backup!
>> Thanks for answere
>> Frank
>>
>> You can't get the actual password for sa- you'll have to enter a new one.
>> You might be able to contact the application vendor and ask them for the
>> sa password since it sounds like the program is assigning a sa password
>> during installation. Maybe it's a standard password (God forbid it...) and
>> then they can tell you what it is.
>> Regards
>> Steen
>
Hi Frank
I'm sorry, but I don't quite understand you last post. If you really
need the SA password and the vendor can supply you with this password
for a fee, I think this is the route you have to go. Otherwise you'll
have to follow the other peoples advices to log on to the server with an
Admin account and then either reset SA password or create an account for
you, that can do what you need to do.
None of the administration you need to do have to be done with the SA
account. Instead I'd suggest that you gets an admin account created that
can perform the tasks you need and where you can control password
yourself. Best of all this should be a windows account, but that require
SQL server to be running in Mixed Mode.
Regards
Steen

Get sa password

Hey,
I admin an company with MS SQL-Server. They forget the sa-password. Is there
any way to get the password from an running sql-server? I need it for
SQL-Backup!
Thanks for answere
FrankAre you using mixed mode that has an NT account with sysadmin rights? if so,
log that account then change the sa password.
What version are you running?
Thomas
"Frank Moskopp" wrote:

> Hey,
> I admin an company with MS SQL-Server. They forget the sa-password. Is the
re
> any way to get the password from an running sql-server? I need it for
> SQL-Backup!
> Thanks for answere
> Frank
>
>|||I have no access with my NT accounts. The company run SQL-Server 2000.
Thanks for more information
Frank
"Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...[vbcol=seagreen]
> Are you using mixed mode that has an NT account with sysadmin rights? if
> so,
> log that account then change the sa password.
> What version are you running?
>
> --
> Thomas
>
> "Frank Moskopp" wrote:
>|||What service account is SQL running under ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
news:djrccf$qtb$1@.newsreader3.netcologne.de...
>I have no access with my NT accounts. The company run SQL-Server 2000.
> Thanks for more information
> Frank
>
> "Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...
>|||If you have NT authentication enabled you should be able to logon as a local
administrator and then change the sa password (this assumes that builtin
admins has not been removed from SQL)
http://vyaskn.tripod.com/administration_faq.htm#q9
Thomas
"Frank Moskopp" wrote:

> I have no access with my NT accounts. The company run SQL-Server 2000.
> Thanks for more information
> Frank
>
> "Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:6A73707E-E854-4FAA-BF35-3DE2178766A1@.microsoft.com...
>
>|||Hi,
Talk to you system admin about this. Using his windows admin login login to
server and connect to SQL Server using Windows authentication.
After that change your SA password using (SP_Password null,newpassword,sa).
After this you could login using new password and do necessary Admin tasks.
Thanks
Hari
SQL Server MVP
"Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
news:djr6ja$f3q$1@.newsreader3.netcologne.de...
> Hey,
> I admin an company with MS SQL-Server. They forget the sa-password. Is
> there any way to get the password from an running sql-server? I need it
> for SQL-Backup!
> Thanks for answere
> Frank
>|||I have 3 mssql-services. It's run on an SBS 2003 Standard-Server. The
application 'Act!' install the SQL-Server. I coundn't set an sa password.
The application manage the service of MSSQL$ACT7. The service of
'Sharepoint' run with the NT admin account.
I need the sa password for my Veritas SQL-Backup and maybe for maintenance.
Services:
MSSQL$ACT7
MSSQL$Sharepoint
MSSQL$xxxx
Frank
"Jasper Smith" <jasper_smith9@.hotmail.com> schrieb im Newsbeitrag
news:u31L0az2FHA.1980@.TK2MSFTNGP15.phx.gbl...
> What service account is SQL running under ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
> news:djrccf$qtb$1@.newsreader3.netcologne.de...
>|||Hey Thomas,
The progamm 'act' manage the MSSQL-service. I need the actually password and
not any new password.
Frank
"Thomas" <Thomas@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A17984F0-5540-4BC1-9A2E-C69AB3647256@.microsoft.com...[vbcol=seagreen]
> If you have NT authentication enabled you should be able to logon as a
> local
> administrator and then change the sa password (this assumes that builtin
> admins has not been removed from SQL)
> http://vyaskn.tripod.com/administration_faq.htm#q9
>
> --
> Thomas
>
> "Frank Moskopp" wrote:
>|||Hey Hari,
The progamm 'act' manage the MSSQL-service. I need the actually password and
not any new password.
Thanks
Frank
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:e%23o60642FHA.3276@.TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Talk to you system admin about this. Using his windows admin login login
> to server and connect to SQL Server using Windows authentication.
> After that change your SA password using (SP_Password
> null,newpassword,sa).
> After this you could login using new password and do necessary Admin
> tasks.
> Thanks
> Hari
> SQL Server MVP
>
> "Frank Moskopp" <FMoskopp@.Moskopp.org> wrote in message
> news:djr6ja$f3q$1@.newsreader3.netcologne.de...
>|||Frank Moskopp wrote:
> Hey Hari,
> The progamm 'act' manage the MSSQL-service. I need the actually password a
nd
> not any new password.
> Thanks
> Frank
> "Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> news:e%23o60642FHA.3276@.TK2MSFTNGP10.phx.gbl...
>
You can't get the actual password for sa- you'll have to enter a new
one. You might be able to contact the application vendor and ask them
for the sa password since it sounds like the program is assigning a sa
password during installation. Maybe it's a standard password (God forbid
it...) and then they can tell you what it is.
Regards
Steen

Get recordset result in variables

Hello folks.
I am running a series of queries in a stored procedure.
For example, my first query might return a recordset like this
Apples
Oranges
Pears
Turnips
I want to put those reults in variables
So I might have
Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
char(10
Select top 4 fruits from tblFruits
How do I get the recordset into the variable?Sorry to answer you with another question but could you explain just
*why* you would want to assign the results to variables? Your reason
may have some bearing on the answer.
Your requirement is a bit unusual. SQL Server doesn't have arrays. The
main data structure is a table and it is hard work to manipulate lists
of variables just because that's not really what the declarative SQL
language was designed to do.
David Portas
SQL Server MVP
--|||Try one by one.
declare @.Fruit1 char(10)
declare @.Fruit2 char(10)
declare @.Fruit3 char(10)
declare @.Fruit4 char(10)
select top 1 @.Fruit1 = fruits from tblFruits
select top 1 @.Fruit2 = fruits from tblFruits
where fruits != @.Fruit1
select top 1 @.Fruit3 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2
select top 1 @.Fruit4 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2 and fruits != @.Fruit3
go
AMB
"Bob" wrote:

> Hello folks.
> I am running a series of queries in a stored procedure.
> For example, my first query might return a recordset like this
> Apples
> Oranges
> Pears
> Turnips
> I want to put those reults in variables
> So I might have
> Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
> char(10
> Select top 4 fruits from tblFruits
> How do I get the recordset into the variable?
>

Get Query Status

I need a way to determine if a query is still running on the database server. Is there a way to do this?

If the query is still running after 2 hours I need to send a message. How can I query the database for the status of a query?

This will give you an idea of what queries are currently running on your server, and what the query is:

select percent_complete,* from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle)
where session_id > 50

This will tell you the longest open tran in the db:

DBCC OPENTRAN|||

If you know the session ID in question, then you can use this statement posted today by Louis Davidson.

declare @.spid int

set @.spid = ?

select

der.session_id,

der.wait_type,

der.wait_time,

der.status as requestStatus,

des.login_name,

cast(db_name(der.database_id) as sysname) as databaseName,

des.program_name,

der.command as commandType,

execText.text as objectText,

case when der.statement_end_offset = -1 then '--see objectText--'

else SUBSTRING(execText.text, der.statement_start_offset/2,

(der.statement_end_offset - der.statement_start_offset)/2)

end AS currentExecutingCommand,

der.open_transaction_count

from

sys.dm_exec_sessions des

inner join

sys.dm_exec_requests as der

on der.session_id = des.session_id

cross apply

sys.dm_exec_sql_text(der.sql_handle) as execText

where

des.session_id = @.spid;

Check BOL for more info about these DMVs.

AMB