Get size of a table/query

Is there a way to get the size of a query of a table?

I know I can use DATALENGTH on every column in my query, but I thought there might be an easier way.what exacly do you mean by size?, number of rows?|||see sp_spaceused in BOL|||sp_spaceused will get the size of the table, but I would like to get the size of results from a query. Would I need to create a temp table from my query and then use sp_spaceused to get the size of the temp table?|||I think it is a possibility
Also you can look through the sp_apceused code to see how they do it|||Yes, it does appear to be possible. I wonder though, it seems a lot more "expensive" to create a temporary table and then get the size of it than to use SUM(DATALENGTH(mycolumn)), but I could be wrong. The reason I say that is one of the tables I'm using this on contains image(blob) data which can be very large. If I have to copy all of that data into a temp table, that could be a bit sluggish. Anyone know performance wise which is better to use?|||you are right
it is definitely non performant

why do you want to measure the query ?
may be there is another way to do what you are trying to do ?|||I have a CMS. Multiple clients use the same tables in my CMS but I need to discover how much space each individual client is taking up. The only way to do this that I've found is to measure the size of the query data.|||it is not good way.
You can just have clientid in each row in the tables (and/or separate clients table)
and count rows each client has (anyway counting rows)|||I assume you mean getting the size of the entire table and calculating client size based on their number of rows times and average size/row. I don't think this is an accurate assessment of how much space a client is consuming especially when one of the columns in the table is a blob field. One client could be consuming most of the space in the table because of a huge value for the blob field in a single record. Using the SUM of DATALENGTH of the blob field works for me. I already have each record assigned a clientID so I can get an accurate count of how much space they are taking up. I just thought there might be a better way other than using SUM of DATALENGTH.

Get Size of a dedicated table

Hi,
I'd like to analyse my db. Does anybody how to get an overview about the
table sizes?
TIA
Pete
Pete Smith
Pete Smith wrote:
> Hi,
> I'd like to analyse my db. Does anybody how to get an overview about
> the table sizes?
> TIA
> Pete
Have a look at sp_spaceused in BOL.
David Gugick
Imceda Software
www.imceda.com

Get SessionID

How can I get the sessionID for a particular execution. I know the report
server automatically generates a sessionID for each execution, but I need to
grab tht as I use that in my processing of stored procedures.
ThanksIf you render the report by SOAP, the SessionID will be exposed as property
of the RS Web service proxy (rs.SessionHeaderValue.SessionId). Look at the
code example for ReportingService.Render method in BOL.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"NI" <NI@.discussions.microsoft.com> wrote in message
news:14649E15-AD50-4AA6-A635-F1C00B3A31E2@.microsoft.com...
> How can I get the sessionID for a particular execution. I know the report
> server automatically generates a sessionID for each execution, but I need
to
> grab tht as I use that in my processing of stored procedures.
> Thanks|||Grab it from where? From inside your report? The session id is passed as a
session cookie to the browser and ping-pongs between the browser and the
server with each request.
For example, you do some tracing between the browser and the Report Server
when a report is requested by URL, you will see something like:
/<folderpath>/<reportname>=vazffvza1iojxlbtqp4nwged
In my case, vazffvza1iojxlbtqp4nwged is the session id. If you need to get
from inside your report try to parse the HttpContext.Current to get to the
cookie value
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"NI" <NI@.discussions.microsoft.com> wrote in message
news:64288C18-CF50-43A9-B8FA-331557612F41@.microsoft.com...
> Teo:
> I just use the reporting services just as it comes out of the box. All my
> report generation is through the report manager(and not through web
service
> access). In this situation how can I grab a sessionID?
> Thanks
> "Teo Lachev" wrote:
> > If you render the report by SOAP, the SessionID will be exposed as
property
> > of the RS Web service proxy (rs.SessionHeaderValue.SessionId). Look at
the
> > code example for ReportingService.Render method in BOL.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "NI" <NI@.discussions.microsoft.com> wrote in message
> > news:14649E15-AD50-4AA6-A635-F1C00B3A31E2@.microsoft.com...
> > > How can I get the sessionID for a particular execution. I know the
report
> > > server automatically generates a sessionID for each execution, but I
need
> > to
> > > grab tht as I use that in my processing of stored procedures.
> > >
> > > Thanks
> >
> >
> >|||Let's back up a little. You say that you are passing the session id to your
stored procedure. What is it that you are using the session id for? If what
you want to know is who is running the report then you can pass the global
variable user!userid to the stored procedure.
It would seem to me that the sessionid will mean nothing outside of RS.
Bruce L-C
"NI" <NI@.discussions.microsoft.com> wrote in message
news:2268528A-FA6E-40E2-AF2F-4A6371E1ACBC@.microsoft.com...
> Maybe am missing something here, but how can I use HttpContext.Current
when
> all i have is a bunch of rdl files. I DO NOT have any classic ASP.NEt
pages.
> I just have a few rdl files deployed on my report manager.
> when i go to the reportmanager, and browse thru a few folders and get to
my
> report (report1.rdl)
> http://localhost/reports/(sessiondID)/<report folder>...
> Before I select my parameters and click view report, I need to get this
> sessionID and send it to a stored procedure.
> Thanks. Really appreciate the help
>
> "Teo Lachev" wrote:
> > Grab it from where? From inside your report? The session id is passed as
a
> > session cookie to the browser and ping-pongs between the browser and the
> > server with each request.
> >
> > For example, you do some tracing between the browser and the Report
Server
> > when a report is requested by URL, you will see something like:
> >
> > /<folderpath>/<reportname>=vazffvza1iojxlbtqp4nwged
> >
> > In my case, vazffvza1iojxlbtqp4nwged is the session id. If you need to
get
> > from inside your report try to parse the HttpContext.Current to get to
the
> > cookie value
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "NI" <NI@.discussions.microsoft.com> wrote in message
> > news:64288C18-CF50-43A9-B8FA-331557612F41@.microsoft.com...
> > > Teo:
> > >
> > > I just use the reporting services just as it comes out of the box. All
my
> > > report generation is through the report manager(and not through web
> > service
> > > access). In this situation how can I grab a sessionID?
> > >
> > > Thanks
> > >
> > > "Teo Lachev" wrote:
> > >
> > > > If you render the report by SOAP, the SessionID will be exposed as
> > property
> > > > of the RS Web service proxy (rs.SessionHeaderValue.SessionId). Look
at
> > the
> > > > code example for ReportingService.Render method in BOL.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ----
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > Publisher website: http://www.manning.com/lachev
> > > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > > Home page and blog: http://www.prologika.com/
> > > > ----
> > > >
> > > > "NI" <NI@.discussions.microsoft.com> wrote in message
> > > > news:14649E15-AD50-4AA6-A635-F1C00B3A31E2@.microsoft.com...
> > > > > How can I get the sessionID for a particular execution. I know the
> > report
> > > > > server automatically generates a sessionID for each execution, but
I
> > need
> > > > to
> > > > > grab tht as I use that in my processing of stored procedures.
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >
> >
> >
> >|||OK, think of the Report Server is just a web-based application. In your
report you can call custom code located inside your report or in an external
.NET assembly. There, you can get to HttpContect.Current.
Of course, this is not going to work when testing your report within the
Report Designer. So, you need to check if HttpContext.Current is null and,
if yes, default to a dummy session id.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"NI" <NI@.discussions.microsoft.com> wrote in message
news:2268528A-FA6E-40E2-AF2F-4A6371E1ACBC@.microsoft.com...
> Maybe am missing something here, but how can I use HttpContext.Current
when
> all i have is a bunch of rdl files. I DO NOT have any classic ASP.NEt
pages.
> I just have a few rdl files deployed on my report manager.
> when i go to the reportmanager, and browse thru a few folders and get to
my
> report (report1.rdl)
> http://localhost/reports/(sessiondID)/<report folder>...
> Before I select my parameters and click view report, I need to get this
> sessionID and send it to a stored procedure.
> Thanks. Really appreciate the help
>
> "Teo Lachev" wrote:
> > Grab it from where? From inside your report? The session id is passed as
a
> > session cookie to the browser and ping-pongs between the browser and the
> > server with each request.
> >
> > For example, you do some tracing between the browser and the Report
Server
> > when a report is requested by URL, you will see something like:
> >
> > /<folderpath>/<reportname>=vazffvza1iojxlbtqp4nwged
> >
> > In my case, vazffvza1iojxlbtqp4nwged is the session id. If you need to
get
> > from inside your report try to parse the HttpContext.Current to get to
the
> > cookie value
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "NI" <NI@.discussions.microsoft.com> wrote in message
> > news:64288C18-CF50-43A9-B8FA-331557612F41@.microsoft.com...
> > > Teo:
> > >
> > > I just use the reporting services just as it comes out of the box. All
my
> > > report generation is through the report manager(and not through web
> > service
> > > access). In this situation how can I grab a sessionID?
> > >
> > > Thanks
> > >
> > > "Teo Lachev" wrote:
> > >
> > > > If you render the report by SOAP, the SessionID will be exposed as
> > property
> > > > of the RS Web service proxy (rs.SessionHeaderValue.SessionId). Look
at
> > the
> > > > code example for ReportingService.Render method in BOL.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ----
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > Publisher website: http://www.manning.com/lachev
> > > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > > Home page and blog: http://www.prologika.com/
> > > > ----
> > > >
> > > > "NI" <NI@.discussions.microsoft.com> wrote in message
> > > > news:14649E15-AD50-4AA6-A635-F1C00B3A31E2@.microsoft.com...
> > > > > How can I get the sessionID for a particular execution. I know the
> > report
> > > > > server automatically generates a sessionID for each execution, but
I
> > need
> > > > to
> > > > > grab tht as I use that in my processing of stored procedures.
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >
> >
> >
> >

Get servers current date format?

sql server 2k...
I found the Set DateFormat method but I can't seem to find a get method
where it will return to me what sqlserver is set as...(mdy,dmy etc..) does
such a method/property exist?
thanks
Doug
Doug Swanson
Senior Applications Developer
Synchrono, Inc
651.228.1772
dswanson@.synchrono.comYou can use DBCC USEROPTIONS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Doug Swanson" <dcswanson@._remove_this_surfbest.net> wrote in message
news:ujHMya0FEHA.1272@.TK2MSFTNGP12.phx.gbl...
> sql server 2k...
> I found the Set DateFormat method but I can't seem to find a get method
> where it will return to me what sqlserver is set as...(mdy,dmy etc..) doe
s
> such a method/property exist?
> thanks
> Doug
> --
> Doug Swanson
> Senior Applications Developer
> Synchrono, Inc
> 651.228.1772
> dswanson@.synchrono.com
>

Get server\instance name(s) using Physical node name

All:
Short of breaking out the cluster administator tool (GUI), given a
serversnetbios or DNS name, how can I deduce the SQL server names (and
instances) that may be present on that server. Ideally, I could pass
something into a VBSCRIPT function, and get the desired information,
but at this point, even just passing something at the command line that
is parsable would be useful.
Long version of the story is I have a list of 250 servers, but the
physical server name does not always map well to the SQL name,
especailly when instances are involved.
Does this make sense?
thanks,
d.
Look up the SQM-DMO method ListAvailableSQLServers in BOL. You can take
that and filter by InstanceName and ServiceName properties.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<google@.dcbarry.com> wrote in message
news:1141177148.783918.95800@.v46g2000cwv.googlegro ups.com...
> All:
> Short of breaking out the cluster administator tool (GUI), given a
> serversnetbios or DNS name, how can I deduce the SQL server names (and
> instances) that may be present on that server. Ideally, I could pass
> something into a VBSCRIPT function, and get the desired information,
> but at this point, even just passing something at the command line that
> is parsable would be useful.
>
> Long version of the story is I have a list of 250 servers, but the
> physical server name does not always map well to the SQL name,
> especailly when instances are involved.
>
> Does this make sense?
> thanks,
> d.
>
|||You may find SQLPing.exe by Chip Andrews perfect for this. It talks to UDP
1434 or SQL Browser, but you only need to supply a NetBIOS name, a DNS name,
or even an IP address. It'll return all the SQL Server instance names along
with a few other things. This is a very simple program with C# source code
you can compile yourself.
Google SQLPing.exe, and you'll find it.
Linchi
"google@.dcbarry.com" wrote:

> All:
> Short of breaking out the cluster administator tool (GUI), given a
> serversnetbios or DNS name, how can I deduce the SQL server names (and
> instances) that may be present on that server. Ideally, I could pass
> something into a VBSCRIPT function, and get the desired information,
> but at this point, even just passing something at the command line that
> is parsable would be useful.
>
> Long version of the story is I have a list of 250 servers, but the
> physical server name does not always map well to the SQL name,
> especailly when instances are involved.
>
> Does this make sense?
> thanks,
> d.
>

Get Server\Instance Name

I can't find this anywhere in the thousands of objects in SSIS. So I thought I would ask.

I have a situtation where I want to retrieve the server\instance name of the SSIS job is running on. I have multiple servers and instances and I want to send emails like "Failed see server\instance - job name".

Any ideas?

Thanks

There is a System variable in SSIS called MachineName that, I think, solves half of what you want.

Rafael Salas

|||

Tom Phillips wrote:

I can't find this anywhere in the thousands of objects in SSIS. So I thought I would ask.

I have a situtation where I want to retrieve the server\instance name of the SSIS job is running on. I have multiple servers and instances and I want to send emails like "Failed see server\instance - job name".

Any ideas?

Thanks

A SSIS package does not run on a server/instance. Each execution is a standalone process (you can see it in Task Manager as dtexec.exe). There is no server component required in order to execute packages.

-Jamie

|||That is true. However the job which kicked off the SSIS package did start on a server. That is actually what I am trying to get.

Maybe I will have to pass it in the job to the SSIS package?|||

Tom Phillips wrote:

Maybe I will have to pass it in the job to the SSIS package?

That might be the way to go...

Rafael Salas

|||

Tom Phillips wrote:

That is true. However the job which kicked off the SSIS package did start on a server. That is actually what I am trying to get.

Maybe I will have to pass it in the job to the SSIS package?

OK, so the job is completely different to the package. There is no way for the SSIS package to know where it was executed from unless you explicitly tell it so yes, you will have to pass it in.

-Jamie

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 second dataset value

hi,

how do I get value from second dataset (in multi dataset report)

in some function it provide scope parameter like

Sum(Fields!amount.Value, "DS2")

but now I need to show the value without using function

thks,

Can you please let us know where you are using this expression and what was the exact requirement you are looking for.

Actually sometimes when we face the issues like this

we use to place

=First(Fields!amount.Value, "DS2")

Hope if you are grouping is correctly ,you should be able to write the expression with out function.

Thank you,

Raj Deep.A

|||

ok I create 2 dataset and 2 table on a report . I just want to show list out the record . on table 1 it work find but on table 2 it give error that the field name is not in scope . I already set datasource on table 2 to dataset2 . on exepression window only show me field of dataset1 .

how do I resolve this

Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanks
Hi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help!
John
"JJ Williams" wrote:

> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>
|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:

> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.
|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:

> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQL
> statement/command or otherwise. I've got dozens of .sql scripts and want a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rather
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>
>
|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If you had two files script.sql and sript1.sql you could do something like:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and then
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many are
> run at any one given time.
> John
>
> "JJ Williams" wrote:
|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.

> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,
|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote:

Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanksHi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help!
John
"JJ Williams" wrote:
> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:
> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
>> Hi,
>> SQL Server 2000. How do I get the filename of the script currently being
>> executed from within the .SQL script itself?
>> Is there a special variable containing the filename of the .SQL script,
>> or
>> another method of obtaining it?
>> thanks
>>|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:
> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
> > osql -U sa -i test.sql
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQL
> statement/command or otherwise. I've got dozens of .sql scripts and want a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rather
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
> > Hi
> >
> > I am not quite sure what you are wanting, but the filename is really only
> > known by the application and AFAIK will not get passed to the server. If
> > you
> > are running the command from a command line or batch file you could use
> > command prompt variables to substitute into a script something that is
> > sent
> > to the server.
> >
> > If you expand and post some example of what you are trying to do it may
> > help!
> >
> > John
> >
> > "JJ Williams" wrote:
> >
> >> Hi,
> >>
> >> SQL Server 2000. How do I get the filename of the script currently being
> >> executed from within the .SQL script itself?
> >>
> >> Is there a special variable containing the filename of the .SQL script,
> >> or
> >> another method of obtaining it?
> >>
> >> thanks
> >>
> >>
> >>
>
>|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
> Hi
> If you had two files script.sql and sript1.sql you could do something like:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and then
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many are
> run at any one given time.
> John
>
> "JJ Williams" wrote:
> > Hi, thanks for your reply. Here's a simple example. test.sql contains:
> >
> > print 'Script: test.sql'
> > select @.@.version
> >
> >
> > Execute it using osql:
> >
> > > osql -U sa -i test.sql
> >
> >
> > Instead of hardcoding the script filename within the script, I want to get
> > the script filename programmatically within the script itself, e.g. via SQL
> > statement/command or otherwise. I've got dozens of .sql scripts and want a
> > general method to output the filename of the script (it doesn't matter if
> > the filename printed includes the whole folder path or not) and would rather
> > not hardcode the print statement in each script.
> >
> > Hope that clarifies what I want to do.
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
> > > Hi
> > >
> > > I am not quite sure what you are wanting, but the filename is really only
> > > known by the application and AFAIK will not get passed to the server. If
> > > you
> > > are running the command from a command line or batch file you could use
> > > command prompt variables to substitute into a script something that is
> > > sent
> > > to the server.
> > >
> > > If you expand and post some example of what you are trying to do it may
> > > help!
> > >
> > > John
> > >
> > > "JJ Williams" wrote:
> > >
> > >> Hi,
> > >>
> > >> SQL Server 2000. How do I get the filename of the script currently being
> > >> executed from within the .SQL script itself?
> > >>
> > >> Is there a special variable containing the filename of the .SQL script,
> > >> or
> > >> another method of obtaining it?
> > >>
> > >> thanks
> > >>
> > >>
> > >>
> >
> >
> >|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.
> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote:
>> Hi
>> If you had two files script.sql and sript1.sql you could do something
>> like:
>> script.sql
>> PRINT 'Script Script1.sql'
>> :r Script1.sql
>> script1.sql
>> SELECT @.@.VERSION
>> Then run script.sql
>> osql -E -S (local) -d master -n -i script.sql -o script.out
>> If you want to do this for multiple files you could create the file and
>> then
>> run it something like:
>> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i'
>> >>
>> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d
>> Master -n -i
>> script.sqf > script.out
>> If you have a large number of scripts it may be useful to limit how many
>> are
>> run at any one given time.
>> John
>>
>> "JJ Williams" wrote:
>> > Hi, thanks for your reply. Here's a simple example. test.sql
>> > contains:
>> >
>> > print 'Script: test.sql'
>> > select @.@.version
>> >
>> >
>> > Execute it using osql:
>> >
>> > > osql -U sa -i test.sql
>> >
>> >
>> > Instead of hardcoding the script filename within the script, I want to
>> > get
>> > the script filename programmatically within the script itself, e.g. via
>> > SQL
>> > statement/command or otherwise. I've got dozens of .sql scripts and
>> > want a
>> > general method to output the filename of the script (it doesn't matter
>> > if
>> > the filename printed includes the whole folder path or not) and would
>> > rather
>> > not hardcode the print statement in each script.
>> >
>> > Hope that clarifies what I want to do.
>> >
>> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> > news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>> > > Hi
>> > >
>> > > I am not quite sure what you are wanting, but the filename is really
>> > > only
>> > > known by the application and AFAIK will not get passed to the server.
>> > > If
>> > > you
>> > > are running the command from a command line or batch file you could
>> > > use
>> > > command prompt variables to substitute into a script something that
>> > > is
>> > > sent
>> > > to the server.
>> > >
>> > > If you expand and post some example of what you are trying to do it
>> > > may
>> > > help!
>> > >
>> > > John
>> > >
>> > > "JJ Williams" wrote:
>> > >
>> > >> Hi,
>> > >>
>> > >> SQL Server 2000. How do I get the filename of the script currently
>> > >> being
>> > >> executed from within the .SQL script itself?
>> > >>
>> > >> Is there a special variable containing the filename of the .SQL
>> > >> script,
>> > >> or
>> > >> another method of obtaining it?
>> > >>
>> > >> thanks
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanksHi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help
!
John
"JJ Williams" wrote:

> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:

> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
>|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:

> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
>
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQ
L
> statement/command or otherwise. I've got dozens of .sql scripts and want
a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rath
er
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>
>|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If you had two files script.sql and sript1.sql you could do something like
:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and th
en
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i'
>>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n
-i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many a
re
> run at any one given time.
> John
>
> "JJ Williams" wrote:
>|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.

> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote:
>

GET SCOPE_IDENTITY ON INSERCOMMAND

Hello,

I need to get the scope_identity value after the insert, but I couldn't see how when using the InsertCommand of SqlDataSource. I think I can try using ADO commands, doing it 'mannually', but how?

If I execute the insertcommand and after select, I don't have the value, it only exists 'when inserting'. So, I need to keep that value. It's possible to do it with a session on sql or how?

I'm really newba.

Thanks

Search the forums, this has been discussed a half dozen times already.|||

Motley wrote:

Search the forums, this has been discussed a half dozen times already.

I saw, but I need something like that:

sqlComm.CommandText ="INSERT INTO S_CUSTOMER(C_NAME, C_RECDEL) VALUES ('" & name.Text &"',0); DECLARE @.C_Customer_Id VARCHAR(50); SET @.C_Customer_Id = SCOPE_IDENTITY;"

I need to get the scope_identity after the insert. If I execute a select command after, I don't get anything. I want to know if I can get the @.C_Customer_Id, declarated on the sql command.

|||

JPope

I had your problem and realized that the only way to get the value back was to do it the "old fashioned" way! I actually use @.@.Indentity which I know is more prone to problems than scope_identity but below is some code that I have used many times and found it works fine for me.

By the way, I am not the world's best programmer - so you may be able to refine some of this down to make it a bit more glamorous!

'Set up the objects and variable

Dim connAsNew SqlConnection()

Dim commAsNew SqlCommand()

Dim adpAsNew SqlDataAdapter()

Dim datasetAsNew DataSet

Dim recordidentityAs String

'Create our INSERT command - do NOT put the identity line in here -

comm.CommandText ="INSERT INTO [Table] ([Field1], [Field2], etc...) VALUES (Some values)"

'set the connection up and open it - use the connection name from web.config - in this case I have called it MyConnection, subsitute yours in its place

conn.ConnectionString = ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString

conn.Open()

'set the insert command up

adp.InsertCommand =New SqlCommand(comm.CommandText, conn)

'execute the insert command

adp.InsertCommand.ExecuteNonQuery()

'now that we have completed the insert command but NOT closed the connection we can set up the select command - in your case it will be SELECT scope_identity as indent

comm.CommandText ="SELECT @.@.Identity as Ident"

adp.SelectCommand =New SqlCommand(comm.CommandText, conn)

adp.Fill(dataset,"Ident")

'Create a datatable to get the data from the first table in the dataset - referred to as "0"

Dim IdentityTableAs DataTable = dataset.Tables(0)

'create a reader for use with the loading in of data

Dim readerAs DataTableReader = dataset.CreateDataReader

'load in data

IdentityTable.Load(reader)

'get a row that we can use - once again the first row referred to as "0"

Dim rowAs DataRow

row = IdentityTable.Rows.Item(0)

'get the inserted record id back as a string so we can use it in another command if required

RecordIdentity = row(0).ToString()

Hope this works - let me know!

Stuart

Get Scope Identity Value using ObjectDataSource and Vb.Net

Hi,

I have been trying to get the scope Identity after inserting a record using an ObjectDataSource.

I can't find what event, or how to get the value that the scope identity returns.

Here is my Sproc.

ALTER PROCEDUREdbo.[YourCompany_LanCustomer_Insert]

(

@.DNNUserIDint,

@.FirstNamenvarchar(50),

@.LastNamenvarchar(50),

@.Addressnvarchar(50),

@.Address2nvarchar(50),

@.Citynvarchar(50),

@.Statenvarchar(50),

@.Zipnvarchar(50),

@.EmailAddressnvarchar(50),

@.PhoneNumbernvarchar(50),

@.CustomerIDint OUTPUT

)

AS

INSERT INTOYourCompany_LanCustomer

(DNNUserID, FirstName, LastName, Address, Address2, City, State, Zip, EmailAddress, PhoneNumber, DateEntered)

VALUES(@.DNNUserID, @.FirstName, @.LastName, @.Address, @.Address2, @.City, @.State, @.Zip, @.EmailAddress, @.PhoneNumber,getdate())

SET@.CustomerID =Scope_Identity()

RETURN

When I try to execute the stored procedure in Sql Manager I get the CustomerID Value, how do I get this value in the VB.Net code behind?

Any help is greatly appreciated.

In the Inserted event of the ObjectDataSource and you use the OutputParameters collection to get the value

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs)Dim _customerIdAs Integer =CInt(e.OutputParameters("@.CustomerID"))End Sub

Thanks

-Mark post(s) as "Answer" that helped you

|||

You'll get the collection of the insert parameters for the data source. You can retrieve the value of any parameter using yourDataSourceID.InsertParameters("parameterName"). You haven't explained how you're executing the stored procedure here. If possible post the aspx page code so that we can know how you've setup the data source.

|||

Now I'm lost, do I get the Scope Identity at the iteminserting or the iteminserted. I tried the first sample and get a parameter is not equal error.

Here is my ascx (I'm using dotnetnuke) for the objectDataSource.

<asp:ObjectDataSourceID="ObjectDataSource_Customer"runat="server"TypeName="YourCompany.Modules.Lan.LanCustomerController"SelectMethod="LanCustomer_GetCustomers"DataObjectTypeName="YourCompany.Modules.Lan.LanCustomerInfo"DeleteMethod="LanCustomer_Delete"OldValuesParameterFormatString="original_{0}"InsertMethod="LanCustomer_Insert">

</asp:ObjectDataSource>

here is the code behind (vb.net) that I am using on insert:

ProtectedSub NewItem(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.FormViewInsertEventArgs)Handles CustomerFormView.ItemInserting

Try

e.Values.Item("CustomerId") = 0

If e.Values.Item("DNNUserID") ="-1"Then

e.Values.Item("DNNUserID") ="0"

Else

e.Values.Item("DNNUserID") = UserId

EndIf

If e.Values.Item("FirstName") =""Then

e.Values.Item("FirstName") = Null.NullString

EndIf

If e.Values.Item("LastName") =""Then

e.Values.Item("LastName") = Null.NullString

EndIf

If e.Values.Item("Address") =""Then

e.Values.Item("Address") = Null.NullString

EndIf

If e.Values.Item("Address2") =""Then

e.Values.Item("Address2") = Null.NullString

EndIf

If e.Values.Item("City") =""Then

e.Values.Item("City") = Null.NullString

EndIf

If e.Values.Item("State") =""Then

e.Values.Item("State") = Null.NullString

EndIf

If e.Values.Item("Zip") =""Then

e.Values.Item("Zip") = Null.NullString

EndIf

If e.Values.Item("EmailAddress") =""Then

e.Values.Item("EmailAddress") = Null.NullString

EndIf

If e.Values.Item("PhoneNumber") =""Then

e.Values.Item("PhoneNumber") = Null.NullString

EndIf

Catch exAs Exception

ProcessModuleLoadException(Me, ex)

EndTry

EndSub

The insert sproc worked before, I just can't get it to work now.

|||

Hi,

SET @.CustomerID = Scope_Identity()

From the code you provided, the @.CustomerID is an OUTPUT parameter you set, right?

And we assume that you are using SqlCommand to execute the stored procedure in your business object method, and then you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Thanks.

|||

Nai-Dong Jin - MSFT:

you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Why do we need to "RETURN" the "OUTPUT PARAMETER" ? Do you know that RETURN values and OUTPUT parameters are independent of each other and we could retrieve either "RETURN" value or "OUTPUT" parameter or both of them?

Note: I dont know how the thread was marked as "Answer"

Thanks

-Mark post(s) as "Answer" that helped you

|||

Hi e_screw,

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And since the original poster was using ObjectDataSource, so he must had invoked the ExecuteNonQuery() in the business object method, right? What I said "return the parameter's value" means return the value from the business method. In this stage, that's totally nothing related with the OUTPUT parameter in procs.

Now I'm lost,

Second,of course, you also can use "Return" to achieve that, but since the original poster was lost, kept asking against previous solution and no one followed up, I just provide another solution for him to refer.

So if you are able to help him further with your solution, I appreciate it. And it also can be beneficial to other community members reading the thread.

Thanks.

|||

This is your previous post:

Nai-Dong Jin - MSFT:

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Last post:

Nai-Dong Jin - MSFT:

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

In the first you said, get the value from the ReturnValue property , after assigning the value of OUTPUT parameters to it. In the second, you are just talking about OUTPUT parameters.

Have you had looked at the ObjectDataSourceStatusEventArgs, there is OutputParameters (which returns a collection of output parameters and their values) and a ReturnValue (which gets the return value returned by the business object, if any). Now read your replies again.

Note: Its not with my solution or your solution. Its all about a correct solution, which helps many other community members.

Thanks

|||

Hi,

To Dan5150,

Here's the sample code for you which describes the solution in my previous posts.

First, in your Procedure:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[ProcName]@.TOINSERTNVARCHAR(50),@.RESULTINT OUTPUT-- THE OUTPUT Parameter has been set as OUTPUTAS INSERT INTO MYTABLE(TOINSERT)VALUES (@.TOINSERT)SET@.RESULT = SCOPE_IDENTITY();

Second, here's the method in business object class:

Public Function BusinessMethod(ByVal TOINSERTAs String)As String Dim connAs String = ConfigurationManager.ConnectionStrings("SampleDbConnectionString").ConnectionStringDim myconnAs New SqlConnection(conn)Dim mycommAs New SqlCommand() mycomm.Connection = myconn mycomm.CommandText ="ProcName" mycomm.CommandType = CommandType.StoredProcedureDim sp1As New SqlParameter() sp1.ParameterName ="TOINSERT" sp1.Value = TOINSERTDim sp2As New SqlParameter() sp2.ParameterName ="RESULT"' This parameter has been set in OUTPUT direction sp2.Direction = ParameterDirection.Output sp2.Size = 4 sp2.SqlDbType = SqlDbType.Int mycomm.Parameters.Add(sp1) mycomm.Parameters.Add(sp2) myconn.Open() mycomm.ExecuteNonQuery() myconn.close()' Return the parameter in OUTPUT direction.Return sp2.Value.ToString()End Function

Third, you can get the value in Inserted event of ODS by accessing ReturnValue property.

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs) Response.Write(e.ReturnValue.ToString())' You can get the id of new inserted row here.End Sub

To e_screw,

Please read my codes, and especially the comment parts in bold. And let's back to your solution which given in the second post:

Protected Sub ObjectDataSource1_Inserted(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
Dim _customerId As Integer = CInt(e.OutputParameters("@.CustomerID"))
End Sub

You can use OutputParameters collection to retrieve the value, while output parameters would be ByRef (out in C#) parameters.

But since the original poster hadn't posted out his business method signature, how can you make sure that he was declaring parameters that are passed to the business object method by reference? If the parameters was passed by val, how could he get the value in OutputParameters collection?

Thanks.

Get Saturday's date...

I am building a report that has two parameters that will always be Sunday to
Sunday but I want a text box to show the Saturdays date for the first
parameter which would be the first sunday. Example:
Parameter 1 - 4/15/2007
Parameter 2 - 4/22/2007
I need the text box to read 4/21/2007 which is the saturday of the week that
begins on 4/15.
Any help will be greatly appreciated, Thanks.
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+Hi,
You should create a new dataset to retreive your parameter default. Put this
code in a stored procedure and the result should be the last Saturday:
set datefirst 7
declare @.date datetime
set @.date = getdate()
while datepart(dw,@.date) <> 7
begin
set @.date= dateadd(day,-1,@.date)
end
select @.date
If you are using the datefirst with the default settingd, you should not put
it in the code. Also, you should format your date with CONVERT or CAST
function, if required.
Another way is to write a custom code in SSRS, but I was lazy :)
I hope, it helps for you.
Regards,
Janos
"CipherTeKST" <CipherTeKST@.discussions.microsoft.com> wrote in message
news:3F271DBD-A5C5-4550-90FB-95B862A2C682@.microsoft.com...
>I am building a report that has two parameters that will always be Sunday
>to
> Sunday but I want a text box to show the Saturdays date for the first
> parameter which would be the first sunday. Example:
> Parameter 1 - 4/15/2007
> Parameter 2 - 4/22/2007
> I need the text box to read 4/21/2007 which is the saturday of the week
> that
> begins on 4/15.
> Any help will be greatly appreciated, Thanks.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+|||Yes, I can do this in SQL with,
SELECT
CONVERT(VARCHAR(10),DATEADD(wk, DATEDIFF(wk, 5, getdate()), 5),101) as
SATURDAY
I was looking for an expression to use in SSRS.
Thanks for your help though!
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+
"BERKE Janos" wrote:
> Hi,
> You should create a new dataset to retreive your parameter default. Put this
> code in a stored procedure and the result should be the last Saturday:
> set datefirst 7
> declare @.date datetime
> set @.date = getdate()
> while datepart(dw,@.date) <> 7
> begin
> set @.date= dateadd(day,-1,@.date)
> end
> select @.date
> If you are using the datefirst with the default settingd, you should not put
> it in the code. Also, you should format your date with CONVERT or CAST
> function, if required.
> Another way is to write a custom code in SSRS, but I was lazy :)
> I hope, it helps for you.
> Regards,
> Janos
> "CipherTeKST" <CipherTeKST@.discussions.microsoft.com> wrote in message
> news:3F271DBD-A5C5-4550-90FB-95B862A2C682@.microsoft.com...
> >I am building a report that has two parameters that will always be Sunday
> >to
> > Sunday but I want a text box to show the Saturdays date for the first
> > parameter which would be the first sunday. Example:
> > Parameter 1 - 4/15/2007
> > Parameter 2 - 4/22/2007
> >
> > I need the text box to read 4/21/2007 which is the saturday of the week
> > that
> > begins on 4/15.
> >
> > Any help will be greatly appreciated, Thanks.
> > --
> > CipherTeKST
> > MCSE: Security 2003, CCNA, Security+
>

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