Getting a SQL Express Database to work on a SQL Server

Hi,

I'm getting ready to deploy an ASP.NET application to a server that does not support SQL Express, but does support MS Access and SQL Server. Is there any easy way to convert my SQL Express code to either Access or regular SQL Server code, without having to change very much code in my application. I really like the integration that SQL Express has with Visual Web Developer, and would like to be able to keep that sort of integration if at all possible.

Thanks,
Drew

Assuming by "SQL Server" you mean "SQL Server 2005", the only thing that should need to change is the connection string. The rest of your code should be able to remain the same.|||

Try this blog and if it works let me know

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

sql

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgs
Hi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||You can check the stored procedure sp_helplogins
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want t
o
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client

I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL 2005
web services using integrated authentication.
In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
same workstations as the client and connectivity works fine.
In testing, the windows app is begin deployed to a Windows 2000 Server
workstation and SQL Server 2005 is installed on a Windows 2003 Server. Both
are on the same domain. The user logged into the client station has admin
access on the server and dbo access (and in this case created the endpoints).
However, when the client attempts to access the server, 401 Access Denied is
returned.
The server specifies Integrated Authentication as follows:
CREATE ENDPOINT RMS
STATE = STARTED
AS HTTP (
SITE = 'SERVER',
PATH = '/sql/app1',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
The client adds the credentials to the cache before the call:
ws.Credentials =
System.Net.CredentialCache.DefaultCredentials;
I think I've covered everything here and from what I have read, the error
indicates that the user is not being authenticated (vs an issue with any
specific SQL object permissions).
Any ideas on what might be happening here and how to fix?
Thanks
-Luther
Hi Luther,
welcome to MSDN newsgroup.
As for the SQL2005's endpoint SOAP webservice, after you created the
endpoint, have you tried using IE browser to view the WSDL document of that
endpoint service? e.g:
http://servername/sql/test_endpoint?WSDL
If this also not work, the problem is likely due to the serverside
setting... If that works, we may need to check the client side code...
Also, you can print out the thread's current secuirty principal to see
whether it is the correct domain account of the server machine ...
In addition , you can also try adding the "Authorizing" setting to grant
certain user/role the permission as below:
AUTHORIZATION sa -- with optional authorization for owner
=======================
CREATE ENDPOINT zipcodes -- create the Http Endpoint
AUTHORIZATION sa -- with optional authorization for owner
STATE = STARTED -- the state of the endpoint
AS HTTP ( -- can be http, tcp, ...
path='/sql/zipcodes', -- the virtual path
AUTHENTICATION=(INTEGRATED), -- type of authentication
PORTS=(CLEAR), -- which ports (clear=all)
SITE ='*' -- site can be '*" or 'myserver'
)
FOR SOAP( -- type of protocol, in this case, SOAP for a webservice
WEBMETHOD 'http://tempuri.org/'.'DistanceBetweenzZipCodes'
-- define the webmethod(s) and fully qualified sproc
(name='zipcodes.dbo.DistanceBetweenZipcodes',
SCHEMA = STANDARD ),
======================
If there're any other findings, please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Getting 401 Access denied w/ Endpoint in SQL 2005 from C#
client
| thread-index: AcYMnwmiqttQJ9quQhelBHxCetS8Eg==
| X-WBNR-Posting-Host: 63.211.139.67
| From: "=?Utf-8?B?THV0aGVyIE1pbGxlcg==?=" <lex3001@.community.nospam>
| Subject: Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client
| Date: Thu, 29 Dec 2005 09:41:02 -0800
| Lines: 39
| Message-ID: <910F2F43-FF93-4110-A162-2C04A46F4451@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.connect:46496
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL
2005
| web services using integrated authentication.
|
| In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
| same workstations as the client and connectivity works fine.
|
| In testing, the windows app is begin deployed to a Windows 2000 Server
| workstation and SQL Server 2005 is installed on a Windows 2003 Server.
Both
| are on the same domain. The user logged into the client station has admin
| access on the server and dbo access (and in this case created the
endpoints).
|
| However, when the client attempts to access the server, 401 Access Denied
is
| returned.
|
| The server specifies Integrated Authentication as follows:
|
| CREATE ENDPOINT RMS
| STATE = STARTED
| AS HTTP (
| SITE = 'SERVER',
| PATH = '/sql/app1',
| AUTHENTICATION = (INTEGRATED),
| PORTS=(CLEAR)
| )
|
| The client adds the credentials to the cache before the call:
|
| ws.Credentials =
| System.Net.CredentialCache.DefaultCredentials;
|
| I think I've covered everything here and from what I have read, the error
| indicates that the user is not being authenticated (vs an issue with any
| specific SQL object permissions).
|
| Any ideas on what might be happening here and how to fix?
|
| Thanks
| -Luther
|
|

Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client

I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL 200
5
web services using integrated authentication.
In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
same workstations as the client and connectivity works fine.
In testing, the windows app is begin deployed to a Windows 2000 Server
workstation and SQL Server 2005 is installed on a Windows 2003 Server. Both
are on the same domain. The user logged into the client station has admin
access on the server and dbo access (and in this case created the endpoints)
.
However, when the client attempts to access the server, 401 Access Denied is
returned.
The server specifies Integrated Authentication as follows:
CREATE ENDPOINT RMS
STATE = STARTED
AS HTTP (
SITE = 'SERVER',
PATH = '/sql/app1',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
The client adds the credentials to the cache before the call:
ws.Credentials =
System.Net.CredentialCache.DefaultCredentials;
I think I've covered everything here and from what I have read, the error
indicates that the user is not being authenticated (vs an issue with any
specific SQL object permissions).
Any ideas on what might be happening here and how to fix?
Thanks
-LutherHi Luther,
welcome to MSDN newsgroup.
As for the SQL2005's endpoint SOAP webservice, after you created the
endpoint, have you tried using IE browser to view the WSDL document of that
endpoint service? e.g:
http://servername/sql/test_endpoint?WSDL
If this also not work, the problem is likely due to the serverside
setting... If that works, we may need to check the client side code...
Also, you can print out the thread's current secuirty principal to see
whether it is the correct domain account of the server machine ...
In addition , you can also try adding the "Authorizing" setting to grant
certain user/role the permission as below:
AUTHORIZATION sa -- with optional authorization for owner
=======================
CREATE ENDPOINT zipcodes -- create the Http Endpoint
AUTHORIZATION sa -- with optional authorization for owner
STATE = STARTED -- the state of the endpoint
AS HTTP ( -- can be http, tcp, ...
path='/sql/zipcodes', -- the virtual path
AUTHENTICATION=(INTEGRATED), -- type of authentication
PORTS=(CLEAR), -- which ports (clear=all)
SITE ='*' -- site can be '*" or 'myserver'
)
FOR SOAP( -- type of protocol, in this case, SOAP for a webservice
WEBMETHOD 'http://tempuri.org/'.'DistanceBetweenzZipCodes'
-- define the webmethod(s) and fully qualified sproc
(name='zipcodes.dbo.DistanceBetweenZipcodes',
SCHEMA = STANDARD ),
======================
If there're any other findings, please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Getting 401 Access denied w/ Endpoint in SQL 2005 from C#
client
| thread-index: AcYMnwmiqttQJ9quQhelBHxCetS8Eg==
| X-WBNR-Posting-Host: 63.211.139.67
| From: "examnotes" <lex3001@.community.nospam>
| Subject: Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client
| Date: Thu, 29 Dec 2005 09:41:02 -0800
| Lines: 39
| Message-ID: <910F2F43-FF93-4110-A162-2C04A46F4451@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.connect:46496
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL
2005
| web services using integrated authentication.
|
| In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
| same workstations as the client and connectivity works fine.
|
| In testing, the windows app is begin deployed to a Windows 2000 Server
| workstation and SQL Server 2005 is installed on a Windows 2003 Server.
Both
| are on the same domain. The user logged into the client station has admin
| access on the server and dbo access (and in this case created the
endpoints).
|
| However, when the client attempts to access the server, 401 Access Denied
is
| returned.
|
| The server specifies Integrated Authentication as follows:
|
| CREATE ENDPOINT RMS
| STATE = STARTED
| AS HTTP (
| SITE = 'SERVER',
| PATH = '/sql/app1',
| AUTHENTICATION = (INTEGRATED),
| PORTS=(CLEAR)
| )
|
| The client adds the credentials to the cache before the call:
|
| ws.Credentials =
| System.Net.CredentialCache.DefaultCredentials;
|
| I think I've covered everything here and from what I have read, the error
| indicates that the user is not being authenticated (vs an issue with any
| specific SQL object permissions).
|
| Any ideas on what might be happening here and how to fix?
|
| Thanks
| -Luther
|
|

Getting 20533 Error as a result of subreports.

I am using VB6 with crystal reports version 8.5 (the OCX) and Access (Jet) database.

I have created a report which works fine on my dev machine and am trying to deploy to another machine.
When the report is run on the other machine an error 20533 Unable to open database error is received.
The report has an embedded subreport, which if I remove the subreport, works fine.
I have validated the database for both the main report and the subreport.

Here is the code I am using to run the report.

With MDIMain.CrystalReport
.ReportFileName = App.Path & "\reports\Summary.rpt"

.RetrieveDataFiles
Do Until .DataFiles(xCntr) = ""
.DataFiles(xCntr) = gSystem.DB_Path
xCntr = xCntr + 1
Loop
.SelectionFormula = "{OrderHeader.OrderID} =" & rsOrder("OrderID") '{?OrderID}
.ReportTitle = gSystem.Company_Name
.Action = 1
End With

I'm at a loss... any help would be apprieciated. Thanks.Hi all... I figured out what the issue was. It was fixed with the following code that sets the database location for the subreport.
I have posted the code fix for anyone else that may run into this issue.

Thanks to all that took a look at this for me.

With MDIMain.CrystalReport
.ReportFileName = App.Path & "\reports\Summary.rpt"
.RetrieveDataFiles
Do Until .DataFiles(xCntr) = ""
.DataFiles(xCntr) = gSystem.DB_Path
xCntr = xCntr + 1
Loop
.SelectionFormula = "{OrderHeader.OrderID} =" & rsOrder("OrderID")
.ReportTitle = gSystem.Company_Name

.SubreportToChange = "Selected Options"
.DataFiles(0) = gSystem.DB_Path

.SubreportToChange = ""
.Action = 1
End With

Getting "Report Server WMI Provider error: Not Found" while configuring Reporting Sevi

I'm Getting "Report Server WMI Provider error: Not Found" when trying to Grant Database Access while configuring the Reporting Sevices Integration. Logging in fine to the DB. Tried all the WMI troublshooting and can't find any issues there. Any tips?

Many Thanks!!

I’m experiencing the same problem. Windows 2003 Server Standard, MSSQL 2005, SharePoint Services 3.0.

I hope someone is going to find a solution soon.

Thanks.

|||So sounds like a bug; I'll look into reporting it as such and maybe it'll get addressed in the next release. Guess that's what CTP's all about. Oh well, at least it seems it wasn't my screw-up for a change :)|||

Do you see the same error when connecting to the Report Server using the RS Config tool?

thanx,
Prash

|||

No, I'm able to connect and config the RS with that tool; also I can access and use report manager; here's something that I don't know whether is correct or not: when I browse to my reportserver virtual directory, I see a directory browsing type view een though directory browsing's not selected in IIS:

<page text>

wctestserv/ReportServer - /

---

Thursday, February 01, 2007 10:07 AM <dir> Users Folders

---

Microsoft SQL Server Reporting Services Version 9.00.2047.00

</page text>

Granted there are no reports on this site yet, but it's interesting (irritating) that I can't get the sharepoint report explorer web part to work with my report server either; same cause/issue?

Thanks!

|||

Looks like you have Report Server running in Native mode.

You need to switch it to SharePoint integrated mode before using "Grant Database Access" and other RS pages in the Central Admin. Refer to the following in SQL SP2 CTP BOL for more details: "How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration) "

In any case, if you simply need to run the Report Explorer web part that worked in WSS 2.0 then should not have to switch the Report Server to SharePoint Integrated mode. Refer to section 4.1.1 in the SP2 readme @.http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm
Note: you may want to use the -globalinstall switch with stsadm.exe in order to successfully connect the Report Explorer to the Report Viewer web part.

HTH
Prash

|||

I'm using SharePoint Services 3.0, so I don't have to mention Service Pack 2 I guess.

I still get the same problem: Report Server WMI Provider error: Not found while doing grand database access.

Thx.

|||Thanks, I figured out the report explorer; I was pointing at ReportServer VD when I shoulda been pointing at ReportManager VD (/reports); works well now (except getting it to connect with the reportviewer webpart, but that's for another forum) :). So somwhere I assume there's a config to switch my report server to integrated mode(?); I'll review those references you cite. Thanks for your help!!|||

Hi Prash,

I'm using WSS3.0 + MOSS 2007, so switch RS to "SharePoint Integrated Mode" would be best fit and thus can use the RS wbe part to render the reports in an easy way.

However, I can still not pass the "Grant Database Access" page, it pop-up:

The group name could not be found

Any advice?

Paul

|||I'm getting the same error message.|||

Refer to the following thread for the latest status on this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1126475&SiteID=1

thanx,
Prash

Getting "Report Server WMI Provider error: Not Found" while configuring Reporting Sevi

I'm Getting "Report Server WMI Provider error: Not Found" when trying to Grant Database Access while configuring the Reporting Sevices Integration. Logging in fine to the DB. Tried all the WMI troublshooting and can't find any issues there. Any tips?

Many Thanks!!

I’m experiencing the same problem. Windows 2003 Server Standard, MSSQL 2005, SharePoint Services 3.0.

I hope someone is going to find a solution soon.

Thanks.

|||So sounds like a bug; I'll look into reporting it as such and maybe it'll get addressed in the next release. Guess that's what CTP's all about. Oh well, at least it seems it wasn't my screw-up for a change :)|||

Do you see the same error when connecting to the Report Server using the RS Config tool?

thanx,
Prash

|||

No, I'm able to connect and config the RS with that tool; also I can access and use report manager; here's something that I don't know whether is correct or not: when I browse to my reportserver virtual directory, I see a directory browsing type view een though directory browsing's not selected in IIS:

<page text>

wctestserv/ReportServer - /

---

Thursday, February 01, 2007 10:07 AM <dir> Users Folders

---

Microsoft SQL Server Reporting Services Version 9.00.2047.00

</page text>

Granted there are no reports on this site yet, but it's interesting (irritating) that I can't get the sharepoint report explorer web part to work with my report server either; same cause/issue?

Thanks!

|||

Looks like you have Report Server running in Native mode.

You need to switch it to SharePoint integrated mode before using "Grant Database Access" and other RS pages in the Central Admin. Refer to the following in SQL SP2 CTP BOL for more details: "How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration) "

In any case, if you simply need to run the Report Explorer web part that worked in WSS 2.0 then should not have to switch the Report Server to SharePoint Integrated mode. Refer to section 4.1.1 in the SP2 readme @.http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm
Note: you may want to use the -globalinstall switch with stsadm.exe in order to successfully connect the Report Explorer to the Report Viewer web part.

HTH
Prash

|||

I'm using SharePoint Services 3.0, so I don't have to mention Service Pack 2 I guess.

I still get the same problem: Report Server WMI Provider error: Not found while doing grand database access.

Thx.

|||Thanks, I figured out the report explorer; I was pointing at ReportServer VD when I shoulda been pointing at ReportManager VD (/reports); works well now (except getting it to connect with the reportviewer webpart, but that's for another forum) :). So somwhere I assume there's a config to switch my report server to integrated mode(?); I'll review those references you cite. Thanks for your help!!|||

Hi Prash,

I'm using WSS3.0 + MOSS 2007, so switch RS to "SharePoint Integrated Mode" would be best fit and thus can use the RS wbe part to render the reports in an easy way.

However, I can still not pass the "Grant Database Access" page, it pop-up:

The group name could not be found

Any advice?

Paul

|||I'm getting the same error message.|||

Refer to the following thread for the latest status on this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1126475&SiteID=1

thanx,
Prash

Getting "Report Server WMI Provider error: Not Found" while configuring Reporting

I'm Getting "Report Server WMI Provider error: Not Found" when trying to Grant Database Access while configuring the Reporting Sevices Integration. Logging in fine to the DB. Tried all the WMI troublshooting and can't find any issues there. Any tips?

Many Thanks!!

I’m experiencing the same problem. Windows 2003 Server Standard, MSSQL 2005, SharePoint Services 3.0.

I hope someone is going to find a solution soon.

Thanks.

|||So sounds like a bug; I'll look into reporting it as such and maybe it'll get addressed in the next release. Guess that's what CTP's all about. Oh well, at least it seems it wasn't my screw-up for a change :)|||

Do you see the same error when connecting to the Report Server using the RS Config tool?

thanx,
Prash

|||

No, I'm able to connect and config the RS with that tool; also I can access and use report manager; here's something that I don't know whether is correct or not: when I browse to my reportserver virtual directory, I see a directory browsing type view een though directory browsing's not selected in IIS:

<page text>

wctestserv/ReportServer - /

---

Thursday, February 01, 2007 10:07 AM <dir> Users Folders

---

Microsoft SQL Server Reporting Services Version 9.00.2047.00

</page text>

Granted there are no reports on this site yet, but it's interesting (irritating) that I can't get the sharepoint report explorer web part to work with my report server either; same cause/issue?

Thanks!

|||

Looks like you have Report Server running in Native mode.

You need to switch it to SharePoint integrated mode before using "Grant Database Access" and other RS pages in the Central Admin. Refer to the following in SQL SP2 CTP BOL for more details: "How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration) "

In any case, if you simply need to run the Report Explorer web part that worked in WSS 2.0 then should not have to switch the Report Server to SharePoint Integrated mode. Refer to section 4.1.1 in the SP2 readme @.http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm
Note: you may want to use the -globalinstall switch with stsadm.exe in order to successfully connect the Report Explorer to the Report Viewer web part.

HTH
Prash

|||

I'm using SharePoint Services 3.0, so I don't have to mention Service Pack 2 I guess.

I still get the same problem: Report Server WMI Provider error: Not found while doing grand database access.

Thx.

|||Thanks, I figured out the report explorer; I was pointing at ReportServer VD when I shoulda been pointing at ReportManager VD (/reports); works well now (except getting it to connect with the reportviewer webpart, but that's for another forum) :). So somwhere I assume there's a config to switch my report server to integrated mode(?); I'll review those references you cite. Thanks for your help!!|||

Hi Prash,

I'm using WSS3.0 + MOSS 2007, so switch RS to "SharePoint Integrated Mode" would be best fit and thus can use the RS wbe part to render the reports in an easy way.

However, I can still not pass the "Grant Database Access" page, it pop-up:

The group name could not be found

Any advice?

Paul

|||I'm getting the same error message.|||

Refer to the following thread for the latest status on this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1126475&SiteID=1

thanx,
Prash

Getting "SQL Server does not exist or access denied" error messages

For some reason, this week we started to get "SQL Server does not exist or
access denied" error messages when attempting to connect to our SQL Server
databases. This only affects people who dial into our network. But this
seems to be a rather complicated situation, so let me explain.
We have a couple of VB6 applications we've written. People log into our
network and get authenticated on our network (username, password and domain
must be specified). We have a test server with SQL Server 2000 installed.
The test server is a Windows 2000 Server. The production server has SQL
Server 2000 installed also, but it is a Windows 2003 Server. I have found,
for example, that I can define an ODBC DSN to the test database and retrieve
data just fine, but I can NOT do the same thing to the production database
on the production server. However, our users were able to do so up until
Tuesday of this week. The only thing I can think of which has changed is I
applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
the DHCP server. But the DHCP server is not either the production or test
database servers, so I have no idea why that should make any difference at
all.
Does anyone have any idea why this problem is now showing up?
Rod
Hi Rod,
If this is was fine before, then I would suspect that it is an issue related
to the security. Usually this error happens when client does not have
permissions to connect to the SQL Server. Could be that last changes in a
fixes were related to the security issues. Do you know which patches were
installed exactly. If yes, then you could check KB articles for them to see
if installation of them does not change any security settings.
Val Mazur
Microsoft MVP
"Rod" <Rod@.NO.SPAM> wrote in message
news:u05d8jpaEHA.4092@.TK2MSFTNGP11.phx.gbl...
> For some reason, this week we started to get "SQL Server does not exist or
> access denied" error messages when attempting to connect to our SQL Server
> databases. This only affects people who dial into our network. But this
> seems to be a rather complicated situation, so let me explain.
> We have a couple of VB6 applications we've written. People log into our
> network and get authenticated on our network (username, password and
> domain
> must be specified). We have a test server with SQL Server 2000 installed.
> The test server is a Windows 2000 Server. The production server has SQL
> Server 2000 installed also, but it is a Windows 2003 Server. I have found,
> for example, that I can define an ODBC DSN to the test database and
> retrieve
> data just fine, but I can NOT do the same thing to the production database
> on the production server. However, our users were able to do so up until
> Tuesday of this week. The only thing I can think of which has changed is I
> applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
> the DHCP server. But the DHCP server is not either the production or test
> database servers, so I have no idea why that should make any difference at
> all.
> Does anyone have any idea why this problem is now showing up?
> Rod
>
>

Getting "SQL Server does not exist or access denied" error messages

For some reason, this week we started to get "SQL Server does not exist or
access denied" error messages when attempting to connect to our SQL Server
databases. This only affects people who dial into our network. But this
seems to be a rather complicated situation, so let me explain.
We have a couple of VB6 applications we've written. People log into our
network and get authenticated on our network (username, password and domain
must be specified). We have a test server with SQL Server 2000 installed.
The test server is a Windows 2000 Server. The production server has SQL
Server 2000 installed also, but it is a Windows 2003 Server. I have found,
for example, that I can define an ODBC DSN to the test database and retrieve
data just fine, but I can NOT do the same thing to the production database
on the production server. However, our users were able to do so up until
Tuesday of this week. The only thing I can think of which has changed is I
applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
the DHCP server. But the DHCP server is not either the production or test
database servers, so I have no idea why that should make any difference at
all.
Does anyone have any idea why this problem is now showing up?
RodHi Rod,
If this is was fine before, then I would suspect that it is an issue related
to the security. Usually this error happens when client does not have
permissions to connect to the SQL Server. Could be that last changes in a
fixes were related to the security issues. Do you know which patches were
installed exactly. If yes, then you could check KB articles for them to see
if installation of them does not change any security settings.
Val Mazur
Microsoft MVP
"Rod" <Rod@.NO.SPAM> wrote in message
news:u05d8jpaEHA.4092@.TK2MSFTNGP11.phx.gbl...
> For some reason, this week we started to get "SQL Server does not exist or
> access denied" error messages when attempting to connect to our SQL Server
> databases. This only affects people who dial into our network. But this
> seems to be a rather complicated situation, so let me explain.
> We have a couple of VB6 applications we've written. People log into our
> network and get authenticated on our network (username, password and
> domain
> must be specified). We have a test server with SQL Server 2000 installed.
> The test server is a Windows 2000 Server. The production server has SQL
> Server 2000 installed also, but it is a Windows 2003 Server. I have found,
> for example, that I can define an ODBC DSN to the test database and
> retrieve
> data just fine, but I can NOT do the same thing to the production database
> on the production server. However, our users were able to do so up until
> Tuesday of this week. The only thing I can think of which has changed is I
> applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
> the DHCP server. But the DHCP server is not either the production or test
> database servers, so I have no idea why that should make any difference at
> all.
> Does anyone have any idea why this problem is now showing up?
> Rod
>
>

Getting "SQL Server does not exist or access denied" error messages

For some reason, this week we started to get "SQL Server does not exist or
access denied" error messages when attempting to connect to our SQL Server
databases. This only affects people who dial into our network. But this
seems to be a rather complicated situation, so let me explain.
We have a couple of VB6 applications we've written. People log into our
network and get authenticated on our network (username, password and domain
must be specified). We have a test server with SQL Server 2000 installed.
The test server is a Windows 2000 Server. The production server has SQL
Server 2000 installed also, but it is a Windows 2003 Server. I have found,
for example, that I can define an ODBC DSN to the test database and retrieve
data just fine, but I can NOT do the same thing to the production database
on the production server. However, our users were able to do so up until
Tuesday of this week. The only thing I can think of which has changed is I
applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
the DHCP server. But the DHCP server is not either the production or test
database servers, so I have no idea why that should make any difference at
all.
Does anyone have any idea why this problem is now showing up?
Rod
Hi Rod,
If this is was fine before, then I would suspect that it is an issue related
to the security. Usually this error happens when client does not have
permissions to connect to the SQL Server. Could be that last changes in a
fixes were related to the security issues. Do you know which patches were
installed exactly. If yes, then you could check KB articles for them to see
if installation of them does not change any security settings.
Val Mazur
Microsoft MVP
"Rod" <Rod@.NO.SPAM> wrote in message
news:u05d8jpaEHA.4092@.TK2MSFTNGP11.phx.gbl...
> For some reason, this week we started to get "SQL Server does not exist or
> access denied" error messages when attempting to connect to our SQL Server
> databases. This only affects people who dial into our network. But this
> seems to be a rather complicated situation, so let me explain.
> We have a couple of VB6 applications we've written. People log into our
> network and get authenticated on our network (username, password and
> domain
> must be specified). We have a test server with SQL Server 2000 installed.
> The test server is a Windows 2000 Server. The production server has SQL
> Server 2000 installed also, but it is a Windows 2003 Server. I have found,
> for example, that I can define an ODBC DSN to the test database and
> retrieve
> data just fine, but I can NOT do the same thing to the production database
> on the production server. However, our users were able to do so up until
> Tuesday of this week. The only thing I can think of which has changed is I
> applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
> the DHCP server. But the DHCP server is not either the production or test
> database servers, so I have no idea why that should make any difference at
> all.
> Does anyone have any idea why this problem is now showing up?
> Rod
>
>

Getting "SQL Server does not exist or access denied" error messages

For some reason, this week we started to get "SQL Server does not exist or
access denied" error messages when attempting to connect to our SQL Server
databases. This only affects people who dial into our network. But this
seems to be a rather complicated situation, so let me explain.
We have a couple of VB6 applications we've written. People log into our
network and get authenticated on our network (username, password and domain
must be specified). We have a test server with SQL Server 2000 installed.
The test server is a Windows 2000 Server. The production server has SQL
Server 2000 installed also, but it is a Windows 2003 Server. I have found,
for example, that I can define an ODBC DSN to the test database and retrieve
data just fine, but I can NOT do the same thing to the production database
on the production server. However, our users were able to do so up until
Tuesday of this week. The only thing I can think of which has changed is I
applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
the DHCP server. But the DHCP server is not either the production or test
database servers, so I have no idea why that should make any difference at
all.
Does anyone have any idea why this problem is now showing up?
RodHi Rod,
If this is was fine before, then I would suspect that it is an issue related
to the security. Usually this error happens when client does not have
permissions to connect to the SQL Server. Could be that last changes in a
fixes were related to the security issues. Do you know which patches were
installed exactly. If yes, then you could check KB articles for them to see
if installation of them does not change any security settings.
--
Val Mazur
Microsoft MVP
"Rod" <Rod@.NO.SPAM> wrote in message
news:u05d8jpaEHA.4092@.TK2MSFTNGP11.phx.gbl...
> For some reason, this week we started to get "SQL Server does not exist or
> access denied" error messages when attempting to connect to our SQL Server
> databases. This only affects people who dial into our network. But this
> seems to be a rather complicated situation, so let me explain.
> We have a couple of VB6 applications we've written. People log into our
> network and get authenticated on our network (username, password and
> domain
> must be specified). We have a test server with SQL Server 2000 installed.
> The test server is a Windows 2000 Server. The production server has SQL
> Server 2000 installed also, but it is a Windows 2003 Server. I have found,
> for example, that I can define an ODBC DSN to the test database and
> retrieve
> data just fine, but I can NOT do the same thing to the production database
> on the production server. However, our users were able to do so up until
> Tuesday of this week. The only thing I can think of which has changed is I
> applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
> the DHCP server. But the DHCP server is not either the production or test
> database servers, so I have no idea why that should make any difference at
> all.
> Does anyone have any idea why this problem is now showing up?
> Rod
>
>

Getting "SQL Server does not exist or access denied" error messages

For some reason, this week we started to get "SQL Server does not exist or
access denied" error messages when attempting to connect to our SQL Server
databases. This only affects people who dial into our network. But this
seems to be a rather complicated situation, so let me explain.
We have a couple of VB6 applications we've written. People log into our
network and get authenticated on our network (username, password and domain
must be specified). We have a test server with SQL Server 2000 installed.
The test server is a Windows 2000 Server. The production server has SQL
Server 2000 installed also, but it is a Windows 2003 Server. I have found,
for example, that I can define an ODBC DSN to the test database and retrieve
data just fine, but I can NOT do the same thing to the production database
on the production server. However, our users were able to do so up until
Tuesday of this week. The only thing I can think of which has changed is I
applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
the DHCP server. But the DHCP server is not either the production or test
database servers, so I have no idea why that should make any difference at
all.
Does anyone have any idea why this problem is now showing up?
RodHi Rod,
None of the recent updates would impact SQL or client connectivity
(MDAC).
What is the OS error when the ODBC DSN fails?
Is this only happening over a VPN or can you repro this locally as well?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Rod,
If this is was fine before, then I would suspect that it is an issue related
to the security. Usually this error happens when client does not have
permissions to connect to the SQL Server. Could be that last changes in a
fixes were related to the security issues. Do you know which patches were
installed exactly. If yes, then you could check KB articles for them to see
if installation of them does not change any security settings.
Val Mazur
Microsoft MVP
"Rod" <Rod@.NO.SPAM> wrote in message
news:u05d8jpaEHA.4092@.TK2MSFTNGP11.phx.gbl...
> For some reason, this week we started to get "SQL Server does not exist or
> access denied" error messages when attempting to connect to our SQL Server
> databases. This only affects people who dial into our network. But this
> seems to be a rather complicated situation, so let me explain.
> We have a couple of VB6 applications we've written. People log into our
> network and get authenticated on our network (username, password and
> domain
> must be specified). We have a test server with SQL Server 2000 installed.
> The test server is a Windows 2000 Server. The production server has SQL
> Server 2000 installed also, but it is a Windows 2003 Server. I have found,
> for example, that I can define an ODBC DSN to the test database and
> retrieve
> data just fine, but I can NOT do the same thing to the production database
> on the production server. However, our users were able to do so up until
> Tuesday of this week. The only thing I can think of which has changed is I
> applied the 5 critical updates that Microsoft related Tuesday/Wednesday on
> the DHCP server. But the DHCP server is not either the production or test
> database servers, so I have no idea why that should make any difference at
> all.
> Does anyone have any idea why this problem is now showing up?
> Rod
>
>|||I am having a similar problem. i am not very experienced programmer and
wrote a program in vb.net which connected fine to my local copy of sql.
the db admin set up ODBC's for the remote servers and i got the same
error. i tried using odbc's on my local machine and fixing the
problem, but i can't for the life of me figure it out. When i test the
connection in the odbc setup,it connects great. i just cannot do it
via the program. I used the odbc connection string and put the
passwords and id in the string code before i try to open the
connection. very confused. any help would be greatly appreciated.
thanks
Chris
Val Mazur wrote:[vbcol=seagreen]
> *Hi Rod,
> If this is was fine before, then I would suspect that it is an issue
> related
> to the security. Usually this error happens when client does not
> have
> permissions to connect to the SQL Server. Could be that last changes
> in a
> fixes were related to the security issues. Do you know which patches
> were
> installed exactly. If yes, then you could check KB articles for them
> to see
> if installation of them does not change any security settings.
> --
> Val Mazur
> Microsoft MVP
>
> "Rod" <Rod@.NO.SPAM> wrote in message
> news:u05d8jpaEHA.4092@.TK2MSFTNGP11.phx.gbl...
> exist or
> Server
> this
> our
> and
> installed.
> SQL
> found,
> and
> database
> until
> changed is I
> Tuesday/Wednesday on
> or test
> difference at
ringo
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message862704.html|||So, if local connections work, but remote connections fail. I would
suggest making some network traces to verify that we're using the right
protocols and able to resolve the machine name.
run the following commands from a DOS prompt:
ipconfig /flushdns
ipconfig /registerdns
Then.
start Microsoft Network Monitor
Attempt a connection using your program.
stop Microsoft Network Monitor.
Review the trace.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I believe I have found the problem. The user has Norton Internet Security
2003 (or 2004, I am not sure which) installed. Once I disabled that, I was
able to reach the server.
Rod
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:AjXJoyAdEHA.3808@.cpmsftngxa10.phx.gbl...
> So, if local connections work, but remote connections fail. I would
> suggest making some network traces to verify that we're using the right
> protocols and able to resolve the machine name.
> run the following commands from a DOS prompt:
> ipconfig /flushdns
> ipconfig /registerdns
> Then.
> start Microsoft Network Monitor
> Attempt a connection using your program.
> stop Microsoft Network Monitor.
> Review the trace.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Kevin , thanks for your response.
I may not have been as clear as i should have been. After the initial
failed attempt of putting the program onto a local computer with the DB
on a separate server, i tried to re-create the problem on my own
laptop. I have made an ODBC which calls the sql on my machine. My
theory is that this would recreate the same process as if it were on a
centralized server. I still get the SQL SERVER DOES NOT EXIST OR
ACCESS NOT DENIED. I don't get it tho. when i create the ODBC, it
tests successfully. Also, when i create a new connection string, and
choose ODBC driver and the correct odbc name (which appears, so it sees
the odbc list) IT also tests successfully. However, when i try to run
the program just opening the conn, it gives the error. I thought i had
fixed it becuase one odbc name i created happened to be the same name
as my local server, so it didnt' give the error, but im thinking it
didn't use the odbc, it just called the local server.
I did the ipconfig/ things you suggested. I am not familiar wiht
Microsoft Network Monitor. Should i continue with this possible
resolution?
thanks so much,
Chris
Kevin McDonnell [MSFT] wrote:
> *So, if local connections work, but remote connections fail. I
> would
> suggest making some network traces to verify that we're using the
> right
> protocols and able to resolve the machine name.
> run the following commands from a DOS prompt:
> ipconfig /flushdns
> ipconfig /registerdns
> Then.
> start Microsoft Network Monitor
> Attempt a connection using your program.
> stop Microsoft Network Monitor.
> Review the trace.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no
> rights. *
ringo
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message862704.html|||Hi Ringo,
Unfortunately, you can't make network traces of local connections. Try
using ISQL.exe locally to see if it will generate the OS error for you.
Also check the HKLM\Software\Microsoft\MSSQLServer\Clie
nt\ConnectTo key:
the only entry should be DSQUERY set to DBNETLIB.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

GetDate() in User Defined Functions, Parameters in View

1) Am I right in believing I can't access the GetDate() function from within
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus

> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.

> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields

GetDate() in UDF

I understand that you can not use GetDate() in a UDF.
I also can not pass a parameter to the UDF (because this is an Access to SQL
Server conversion, and the program that calls this UDF does not pass any
parameter to it).
So, I am trying to create a View for GetDate() like the codes below.
The issue is, I also need to select from another table (tblA) besides
getting the GetDate() value.
Is the following codes correct and efficient on how to do that ?
Thanks.
create view get_date
as
select getdate()dt
CREATE function dbo.udftemp()
returns @.myTable TABLE(id varchar(10),datex datetime)
AS BEGIN
INSERT INTO @.myTable(id,datex)
select tblA.id,dt
FROM tblA, get_date --select from tblA and the view
WHERE tblA.colA <> 'XYZ'
return
end>I understand that you can not use GetDate() in a UDF.
? This works on my 2005 server:
CREATE FUNCTION dbo.Func1 ()
RETURNS datetime
AS
BEGIN
return getdate()
END
GO
select dbo.Func1()
William|||We are using SQL2000, and unfortunately it does not work there.
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:O97ruV0BGHA.1032@.TK2MSFTNGP11.phx.gbl...
> ? This works on my 2005 server:
> CREATE FUNCTION dbo.Func1 ()
> RETURNS datetime
> AS
> BEGIN
> return getdate()
> END
> GO
> select dbo.Func1()
> --
> William
>|||William Stacey [MVP] (william.stacey@.gmail.com) writes:
> ? This works on my 2005 server:
> CREATE FUNCTION dbo.Func1 ()
> RETURNS datetime
> AS
> BEGIN
> return getdate()
> END
> GO
> select dbo.Func1()
Yes, but it does not work on SQL 2000.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||yes - that should be fine
not much else to do in this case, though [unless you're using sql2005,
which allows non-deterministic functions in UDFs]
fniles wrote:
> I understand that you can not use GetDate() in a UDF.
> I also can not pass a parameter to the UDF (because this is an Access to S
QL
> Server conversion, and the program that calls this UDF does not pass any
> parameter to it).
> So, I am trying to create a View for GetDate() like the codes below.
> The issue is, I also need to select from another table (tblA) besides
> getting the GetDate() value.
> Is the following codes correct and efficient on how to do that ?
> Thanks.
> create view get_date
> as
> select getdate()dt
> CREATE function dbo.udftemp()
> returns @.myTable TABLE(id varchar(10),datex datetime)
> AS BEGIN
> INSERT INTO @.myTable(id,datex)
> select tblA.id,dt
> FROM tblA, get_date --select from tblA and the view
> WHERE tblA.colA <> 'XYZ'
> return
> end
>|||If it is a migration, then why not migrate to 2005 instead of 2000? Just
curious.
William Stacey [MVP]
"fniles" <fniles@.pfmail.com> wrote in message
news:e%23lFUa0BGHA.2644@.TK2MSFTNGP09.phx.gbl...
> We are using SQL2000, and unfortunately it does not work there.
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:O97ruV0BGHA.1032@.TK2MSFTNGP11.phx.gbl...
>|||He never said anything about 2000, so I gave it a shot.
William Stacey [MVP]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9734F019C8C9DYazorman@.127.0.0.1...
> William Stacey [MVP] (william.stacey@.gmail.com) writes:
> Yes, but it does not work on SQL 2000.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your reply.
Is SQL 2005 still a beta product or is it a release product ?
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OynIJo0BGHA.228@.TK2MSFTNGP12.phx.gbl...
> If it is a migration, then why not migrate to 2005 instead of 2000? Just
> curious.
> --
> William Stacey [MVP]
> "fniles" <fniles@.pfmail.com> wrote in message
> news:e%23lFUa0BGHA.2644@.TK2MSFTNGP09.phx.gbl...
>|||RTM. You can get Sql Express 2005 for free currently and buy the Std and
Enterprise versions as normal.
http://www.microsoft.com/sql/default.mspx
William Stacey [MVP]
"fniles" <fniles@.pfmail.com> wrote in message
news:uOzdgy8BGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your reply.
> Is SQL 2005 still a beta product or is it a release product ?
>
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:OynIJo0BGHA.228@.TK2MSFTNGP12.phx.gbl...
>|||was it the RTM? or a previous CTP release?
i created a function exactly like in RTM developer edition and it worked
fine.
i don't think edition should matter - but release might.
fniles wrote:
> I just install SQL 2005 Standard Edition, and try the GetDate function
> again, but it still gives me the same error.
> Here is my function:
> CREATE function dbo.udftemp()
> returns @.myTable TABLE(datex datetime)
> AS BEGIN
> INSERT INTO @.myTable(datex)
> select getdate()
> return
> end
> The error I got was: "Invalid use of 'getdate' within a function."
> Do I need SQL 2005 Enterprise Edition for the GetDate() to work ?
> Thanks.
>
> "Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
> news:uPy03j0BGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
>
>

Get UserName of Windows Account for Report Parameter

We would like to automatically pass the username as a parameter to reports. By username, I mean the username from the Windows account used to access the virtual directory containing the report (which uses Integrated Windows Authentication). Sort of like the User.Identity.Name property in ASP.NET. Is this going to be possible?

Thanks

Hi ALFKI.
Have you tried Globasl!UserID? ...or was it User!UserID?...damn, it slipped my mind; but I guess it'll help you.
Regards|||

User!UserID has the fully qualified user name, i.e. MYDOMAIN\MyUserName

HTH...

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

Get Time of Linked Server

Hello,
I have a processes which access a SQL Server 2000 in Brazil, Japan,
China.. and a few other places. I would like to return the localtime
of the linked server.
I cannot find anyway to do this, and can't find a setting in any of
the tables which shows the current timezone.
Any help would be great.
AllanSQL server does not care about Time Zones and does not store it internally.
The Windows Host gives it the time and that all what it wants and needs.
You can get the time on the remote servers by using
SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT CURRENT_TIMESTAMP')
If you need to know it's timezone, you need to access the registry on the
remote machine as Windows stores it here.
Regards
Mike
"Allan Martin" wrote:

> Hello,
> I have a processes which access a SQL Server 2000 in Brazil, Japan,
> China.. and a few other places. I would like to return the localtime
> of the linked server.
> I cannot find anyway to do this, and can't find a setting in any of
> the tables which shows the current timezone.
> Any help would be great.
> Allan
>

Get Sql Documenter

I need to see a total outline of my relationships on my sql database. Something besides database diagram. I need something like Microsoft access Documenter. I want to be able to print out a complete definition of the relationships. HEllllllllllllPPPPPPPPPP!!!!!!!!What's wrong with database diagram?|||You could try SchemaToDoc (http://www.schematodoc.com). It creates a Word document that includes info about your database - primary keys, fields (type, size, nullable, defaults), indexes, check constraints, foreign keys, triggers, views, stored procedures, and extended properties. It also includes an interface that lets you annotate your tables and fields.