getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?See srv_pfieldex,
http://msdn.microsoft.com/library/d.../>
07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?See srv_pfieldex,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_ref_07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?
See srv_pfieldex,
http://msdn.microsoft.com/library/de...ef_07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

Getting Report Server Running on Local Instance of SQL Express

I have installed SQL 2005 Tools which installed Visual Studio 2005 including SQL Server Reporting Services Designers. I thought this would create a local instance of SQL Server 2005 but it did not. So I installed SQL Server 2005 Express so I could designate it as my local Report Server instance. How do I configure this local instance to be used for Report Server?

RS Configuration Manager

Machine Name: "localmachinename"

Instance Name: greyed out

No report servers were found on the specified machine.

Details: Invalid Namespace

Did you install the Advanced Edition of SQL Server 2005 Express ? If not you wont have the Reporting Server installed. YOu can download the Advanced Services edition from here: http://www.microsoft.com/downloads/details.aspx?familyid=4C6BA9FD-319A-4887-BC75-3B02B5E48A40&displaylang=de

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I eventually came to the same conclusion. Thanks

Getting past SQL Network Interface error 26 - Error Locating Server/Instance Specified

First-time poster (and very new to ASP.NET, so please be patient) - I am using VS 2005. I have an ASP.Net 2.0 app that is using Forms Authentication and a Login server control with AspNetSqlMembershipProvider. I have an instance of ASPNETDB.MDF in the App_Data directory on my local machine and all works well (or at least as expected). I used the Copy Web Site function to port the app (including the ASPNETDB.MDF files) to my production platform, where I encounter the error mentioned in the subject line. I have found posts (both here and other forums) that mention enabling TCP/IP using Surface Area Configuartion Utility, SQL Server Browser are exempt from firewall, and a few other things. My production machine does not have SQL SVR 2005 installed nor SQL SVR Express.

Do I have to install SQL Server Express onto my production platform in order to resolve this, or is there another means (something in SQL Server 2005 Management Studio on my local machine) that will enable me to do this on the production machine?

Also, I have been told that it is possible to use a database on another machine as the data store for authentication. Does anyone know how to do that? This would make the first question moot.

Thanks in advance!

Then you cannot run your application because SQL Server is RDBMS (relational database management systems). When you install SQL Server then follow the steps in this thread. Hope this helps.

http://forums.asp.net/thread/1403472.aspx

|||

Thanks for the response, but I worked out my optimum solution on my own (the "Aslo" part of my original post. Using the aspnet_regsql.exe, I have my authentication database on a full SQL Server 2005 box elsewhere in my enterprise, and I worked out the web.config setting to make it work.

I understand that SQL Server is the underlying DBMS. That point was not in question. The issue was, (now moot) was it truly needed on the same box, and if not, how to make it work.

getting one instance of multiple records via query

I'm a longtime Access db developer just now really getting into MSDE and
SQL Server 2000.
I have an Access SQL query that retrieves only the first instance of any
date value from a series of records. This gives me a simple list of
available date values fro a dropdown combo box.
My query works great in an Access MDB, but apparently there is no First()
function in SQL Server and I can't find an analog. SQL Server will not
import and reconfigure my queries as it did my tables.
The Access SQL is as follows:
SELECT First(OrderSetupMaster.OrderDate) AS [Order Date Field]
FROM OrderSetupMaster
GROUP BY OrderSetupMaster.OrderDate
ORDER BY First(OrderSetupMaster.OrderDate);
Any suggestions?
Thanks,
Randall Arnold
On Fri, 14 Oct 2005 15:37:58 -0700, divineblasphemy@.hotmail.com wrote:

>I'm a longtime Access db developer just now really getting into MSDE and
>SQL Server 2000.
>I have an Access SQL query that retrieves only the first instance of any
>date value from a series of records. This gives me a simple list of
>available date values fro a dropdown combo box.
>My query works great in an Access MDB, but apparently there is no First()
>function in SQL Server and I can't find an analog. SQL Server will not
>import and reconfigure my queries as it did my tables.
>The Access SQL is as follows:
>SELECT First(OrderSetupMaster.OrderDate) AS [Order Date Field]
>FROM OrderSetupMaster
>GROUP BY OrderSetupMaster.OrderDate
>ORDER BY First(OrderSetupMaster.OrderDate);
>Any suggestions?
>Thanks,
>Randall Arnold
Hi Randall,
FIRST() is very non-relational in nature. By definition, the rows in a
relational table are UNordered, so there is no first row.
If you want the oldest OrderDate, try MIN(OrderSetupMaster.OrderDate).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Won't that just give me a single row? I need multiple rows, if there are
multiple date values. So if my recordset dates are, say, 12/10/2004
12/11/2004 12/11/2004 12/13/2004, then I expect to pull up 12/10/2004
12/11/2004 12/13/2004... not just a MIN of 12/10/2004 or MAX of
12/13/2004.
Make sense?
Randall Arnold
|||Randal,
Based on what criteria (grouping)? Perhaps you are looking for DISTINCT instead?
i.e.,
SELECT DISTINCT(ORDERDATE)AS [Order Date Field]
FROM ORDERSETUPMASTER
ORDER BY ORDERDATE
HTH
Jerry
<randall.arnold@.nokia.com> wrote in message news:u1nOsaR0FHA.3924@.TK2MSFTNGP14.phx.gbl...
Won't that just give me a single row? I need multiple rows, if there are multiple date values. So if my recordset dates are, say, 12/10/2004 12/11/2004 12/11/2004 12/13/2004, then I expect to pull up 12/10/2004 12/11/2004 12/13/2004... not just a MIN of 12/10/2004 or MAX of 12/13/2004.
Make sense?
Randall Arnold
|||Or...
with the following data in the table:
1 2004-12-10 00:00:00.000
2 2004-12-12 00:00:00.000
2 2004-12-11 00:00:00.000
2 2004-12-12 00:00:00.000
3 2004-12-12 00:00:00.000
3 2004-12-11 00:00:00.000
4 2004-12-11 00:00:00.000
and the query:
SELECT ID, MIN(ORDERDATE)
FROM ORDERSETUPMASTER
GROUP BY ID
ORDER BY MIN(ORDERDATE)
and the resultset:
1 2004-12-10 00:00:00.000
2 2004-12-11 00:00:00.000
3 2004-12-11 00:00:00.000
4 2004-12-11 00:00:00.000
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message news:OZmAUfR0FHA.2076@.TK2MSFTNGP14.phx.gbl...
Randal,
Based on what criteria (grouping)? Perhaps you are looking for DISTINCT instead?
i.e.,
SELECT DISTINCT(ORDERDATE)AS [Order Date Field]
FROM ORDERSETUPMASTER
ORDER BY ORDERDATE
HTH
Jerry
<randall.arnold@.nokia.com> wrote in message news:u1nOsaR0FHA.3924@.TK2MSFTNGP14.phx.gbl...
Won't that just give me a single row? I need multiple rows, if there are multiple date values. So if my recordset dates are, say, 12/10/2004 12/11/2004 12/11/2004 12/13/2004, then I expect to pull up 12/10/2004 12/11/2004 12/13/2004... not just a MIN of 12/10/2004 or MAX of 12/13/2004.
Make sense?
Randall Arnold
|||Distinct or Group by will be enough
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
|||Yeah, stupid me, DISTINCT did the trick. That occurred to me before I
even saw your post and I was cringing at the prospect of what responses
would look like today...lol.
Thanks,
Randall

getting one instance of multiple records via query

I'm a longtime Access db developer just now really getting into MSDE and
SQL Server 2000.
I have an Access SQL query that retrieves only the first instance of any
date value from a series of records. This gives me a simple list of
available date values fro a dropdown combo box.
My query works great in an Access MDB, but apparently there is no First()
function in SQL Server and I can't find an analog. SQL Server will not
import and reconfigure my queries as it did my tables.
The Access SQL is as follows:
SELECT First(OrderSetupMaster.OrderDate) AS [Order Date Field]
FROM OrderSetupMaster
GROUP BY OrderSetupMaster.OrderDate
ORDER BY First(OrderSetupMaster.OrderDate);
Any suggestions?
Thanks,
Randall ArnoldOn Fri, 14 Oct 2005 15:37:58 -0700, divineblasphemy@.hotmail.com wrote:

>I'm a longtime Access db developer just now really getting into MSDE and
>SQL Server 2000.
>I have an Access SQL query that retrieves only the first instance of any
>date value from a series of records. This gives me a simple list of
>available date values fro a dropdown combo box.
>My query works great in an Access MDB, but apparently there is no First()
>function in SQL Server and I can't find an analog. SQL Server will not
>import and reconfigure my queries as it did my tables.
>The Access SQL is as follows:
>SELECT First(OrderSetupMaster.OrderDate) AS [Order Date Field]
>FROM OrderSetupMaster
>GROUP BY OrderSetupMaster.OrderDate
>ORDER BY First(OrderSetupMaster.OrderDate);
>Any suggestions?
>Thanks,
>Randall Arnold
Hi Randall,
FIRST() is very non-relational in nature. By definition, the rows in a
relational table are UNordered, so there is no first row.
If you want the oldest OrderDate, try MIN(OrderSetupMaster.OrderDate).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Won't that just give me a single row? I need multiple rows, if there are
multiple date values. So if my recordset dates are, say, 12/10/2004
12/11/2004 12/11/2004 12/13/2004, then I expect to pull up 12/10/2004
12/11/2004 12/13/2004... not just a MIN of 12/10/2004 or MAX of
12/13/2004.
Make sense?
Randall Arnold|||Randal,
Based on what criteria (grouping)? Perhaps you are looking for DISTINCT ins
tead?
i.e.,
SELECT DISTINCT(ORDERDATE)AS [Order Date Field]
FROM ORDERSETUPMASTER
ORDER BY ORDERDATE
HTH
Jerry
<randall.arnold@.nokia.com> wrote in message news:u1nOsaR0FHA.3924@.TK2MSFTNGP
14.phx.gbl...
Won't that just give me a single row? I need multiple rows, if there are mu
ltiple date values. So if my recordset dates are, say, 12/10/2004 12/11/200
4 12/11/2004 12/13/2004, then I expect to pull up 12/10/2004 12/11/2004 12/1
3/2004... not just a MIN of 12/10/2004 or MAX of 12/13/2004.
Make sense?
Randall Arnold|||Or...
with the following data in the table:
1 2004-12-10 00:00:00.000
2 2004-12-12 00:00:00.000
2 2004-12-11 00:00:00.000
2 2004-12-12 00:00:00.000
3 2004-12-12 00:00:00.000
3 2004-12-11 00:00:00.000
4 2004-12-11 00:00:00.000
and the query:
SELECT ID, MIN(ORDERDATE)
FROM ORDERSETUPMASTER
GROUP BY ID
ORDER BY MIN(ORDERDATE)
and the resultset:
1 2004-12-10 00:00:00.000
2 2004-12-11 00:00:00.000
3 2004-12-11 00:00:00.000
4 2004-12-11 00:00:00.000
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message news:OZmAUfR0FHA.20
76@.TK2MSFTNGP14.phx.gbl...
Randal,
Based on what criteria (grouping)? Perhaps you are looking for DISTINCT ins
tead?
i.e.,
SELECT DISTINCT(ORDERDATE)AS [Order Date Field]
FROM ORDERSETUPMASTER
ORDER BY ORDERDATE
HTH
Jerry
<randall.arnold@.nokia.com> wrote in message news:u1nOsaR0FHA.3924@.TK2MSFTNGP
14.phx.gbl...
Won't that just give me a single row? I need multiple rows, if there are mu
ltiple date values. So if my recordset dates are, say, 12/10/2004 12/11/200
4 12/11/2004 12/13/2004, then I expect to pull up 12/10/2004 12/11/2004 12/1
3/2004... not just a MIN of 12/10/2004 or MAX of 12/13/2004.
Make sense?
Randall Arnold|||Distinct or Group by will be enough
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||Yeah, stupid me, DISTINCT did the trick. That occurred to me before I
even saw your post and I was cringing at the prospect of what responses
would look like today...lol.
Thanks,
Randall

Getting multiple drives on a sql 2005 clustered instance

We are trying to create a clustered instance on sql 2005 which is
sitting on a clustered OS having 5 SAN drives presented to it. When
setting up a flat instance, every drive is availabe to split up the
data, log and index amongst several LUNs. When creating a clustered db
instance, it seems only the install path is available. Is this simply
just the design, or is there a way I can have the other drives available
to divide i/o? From what I've seen so far, it seems only the LUN the
instance is installed on is the only one available to define index and
log path. Thanks!!
*** Sent via Developersdex http://www.codecomments.com ***
You have to make the SQL Server service dependant on the drives in cluadmin
and then restart SQL.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Michael Johnson" <michael.johnson@.sysix.com> wrote in message
news:uUUbYA5bIHA.5476@.TK2MSFTNGP02.phx.gbl...
> We are trying to create a clustered instance on sql 2005 which is
> sitting on a clustered OS having 5 SAN drives presented to it. When
> setting up a flat instance, every drive is availabe to split up the
> data, log and index amongst several LUNs. When creating a clustered db
> instance, it seems only the install path is available. Is this simply
> just the design, or is there a way I can have the other drives available
> to divide i/o? From what I've seen so far, it seems only the LUN the
> instance is installed on is the only one available to define index and
> log path. Thanks!!
> *** Sent via Developersdex http://www.codecomments.com ***
|||To expand what Jason said, you need to use Cluster Administrator (type
cluadmin at Run or command line to launch it) to: (1) add the disks as disk
resources, if not already done, (2) move these disk resources to the resource
group of the SQL Server instance that will use these disks, and (3) take the
SQL Server resource offline, add these disk resources as its dependencies,
and bring the SQL Server resource online. Now, execute the following query in
the SQL instance to see whether these disks can be used to place database
files:
select * from fn_servershareddrives()
You should not see these disks in the result list, and that means you can
now place database files of this SQL Server instance on them.
Linchi
"Michael Johnson" wrote:

> We are trying to create a clustered instance on sql 2005 which is
> sitting on a clustered OS having 5 SAN drives presented to it. When
> setting up a flat instance, every drive is availabe to split up the
> data, log and index amongst several LUNs. When creating a clustered db
> instance, it seems only the install path is available. Is this simply
> just the design, or is there a way I can have the other drives available
> to divide i/o? From what I've seen so far, it seems only the LUN the
> instance is installed on is the only one available to define index and
> log path. Thanks!!
> *** Sent via Developersdex http://www.codecomments.com ***
>

Getting multiple drives on a sql 2005 clustered instance

We are trying to create a clustered instance on sql 2005 which is
sitting on a clustered OS having 5 SAN drives presented to it. When
setting up a flat instance, every drive is availabe to split up the
data, log and index amongst several LUNs. When creating a clustered db
instance, it seems only the install path is available. Is this simply
just the design, or is there a way I can have the other drives available
to divide i/o? From what I've seen so far, it seems only the LUN the
instance is installed on is the only one available to define index and
log path. Thanks!!
*** Sent via Developersdex http://www.developersdex.com ***You have to make the SQL Server service dependant on the drives in cluadmin
and then restart SQL.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Michael Johnson" <michael.johnson@.sysix.com> wrote in message
news:uUUbYA5bIHA.5476@.TK2MSFTNGP02.phx.gbl...
> We are trying to create a clustered instance on sql 2005 which is
> sitting on a clustered OS having 5 SAN drives presented to it. When
> setting up a flat instance, every drive is availabe to split up the
> data, log and index amongst several LUNs. When creating a clustered db
> instance, it seems only the install path is available. Is this simply
> just the design, or is there a way I can have the other drives available
> to divide i/o? From what I've seen so far, it seems only the LUN the
> instance is installed on is the only one available to define index and
> log path. Thanks!!
> *** Sent via Developersdex http://www.developersdex.com ***|||To expand what Jason said, you need to use Cluster Administrator (type
cluadmin at Run or command line to launch it) to: (1) add the disks as disk
resources, if not already done, (2) move these disk resources to the resource
group of the SQL Server instance that will use these disks, and (3) take the
SQL Server resource offline, add these disk resources as its dependencies,
and bring the SQL Server resource online. Now, execute the following query in
the SQL instance to see whether these disks can be used to place database
files:
select * from fn_servershareddrives()
You should not see these disks in the result list, and that means you can
now place database files of this SQL Server instance on them.
Linchi
"Michael Johnson" wrote:
> We are trying to create a clustered instance on sql 2005 which is
> sitting on a clustered OS having 5 SAN drives presented to it. When
> setting up a flat instance, every drive is availabe to split up the
> data, log and index amongst several LUNs. When creating a clustered db
> instance, it seems only the install path is available. Is this simply
> just the design, or is there a way I can have the other drives available
> to divide i/o? From what I've seen so far, it seems only the LUN the
> instance is installed on is the only one available to define index and
> log path. Thanks!!
> *** Sent via Developersdex http://www.developersdex.com ***
>