Resolving function USER

If I log in to Query Analyzer using Windows Auth and run "PRINT USER", it returns "dbo". Is there any way to have the USER function actually return <Domain>\<login> for myself?Look up suser_name and suser_sname.

RESOLVED - Users Cannot Connect to SQL Server 2000 SP4 After Applying Hotfix (build 8.00.2148)

Users are reporting that they cannot connect to SQL Server using ODBC and Windows Authentication after I applied a SQL Server 2000 SP4 hotfix. The client-side error is:

Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied

I installed hotfix for SQL Server 2000 SP4 (sets version to SQL Server 2000 build 8.00.2148 (SP4)); see KB894905. This hotfix was intended to resolve the following issues occurring on the server:

902955 (http://support.microsoft.com/kb/902955/) FIX: You receive a "Getting registry information" message when you run the Sqldiag.exe utility after you install SQL Server 2000 Service Pack 4

895123 (http://support.microsoft.com/kb/895123/) FIX: You may receive error message 701, error message 802, and error message 17803 when many hashed buffers are available in SQL Server 2000

Has anyone else experienced this, found a solution, etc.?

Thanks,

David

The server is SQL Server 2000 SP4 on Windows 2003 SP1. The client PC has MDAC 2.8 SP1 on Windows XP SP 2.

|||

double check that windows firewall isn't blocking on both client and server.

on clients run "C:\windows\System32\CliConfg.exe" and make sure that tcp/ip is a configured protocol

on Server, run "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe" and make sure that tcp/ip is configured

|||

Thanks for your reply.

Neither client or server is running Windows Firewall. Both are enabled for TCP/IP. I also ran the MDAC Component Checker on both and neither reported mismatches.

|||

sanity check. . . is the service running?

what happens when you do this from a client -

telnet servername 1433

does the screen go blank, as if waiting for or input? or does it say - "Connecting to servername" and then time out? or something different?

what happens when you do this on the server -

telnet localhost 1433

|||

Yes, the MSSQLSERVER and SQLSERVERAGENT services are both running on the server and I can connect fine using EM from my PC. In fact, I can connect using ODBC from my PC, but a user cannot connect from his PC using the same ODBC setup. An app. server running Windows 2000 SP1 also cannot connect. I've been validating the Windows and MDAC versions and making sure no firewalls are running, etc.

When I run telnet servername 1433 from my PC the window opens and reports "Connecting to servername" and then it disappears. When I run telnet localhost 1433 on the server it does the same thing.

|||

wait a second. . .

sitting at the server, from the start menu, click run . . .

you do:

telnet localhost 1433

a command window opens up and you get a message "connecting to localhost"

and then the command window closes?
If so the connection is being refused from the machine/service.

the telnet should go to a blank/black command window as the sql telnet session awaits more commands.

Is it a named instance of sql server? or default instance?

|||This is a default instance and yes, the telnet window disappears.|||

when you are sitting at the server and trying the telnet, you are logged in as a machine administator and the telent fails?

using enterprise manager, check the server from your machine and confirm that 'servername\Administrators' have server admin permissions.

double check all your logins and db users

are you running NT Authentication or Mixed mode authentication?

|||Yes, I am logged in as an administrator on the server when I run telnet. 'BUILTIN\Administrators' has been removed from the SQL Server logins, but that should not affect users trying to connect with valid registrations. The instance uses mixed-mode authentication (Windows and SQL Server).|||

well, double check your logins and users. . . . no one is denied.

|||

This issue has been resolved. It was related to permissions for the SQL Server Service Account. That account needed permissions adjustments, as described in KB283811.

The permissions changes resolved the ODBC TCP/IP connection failures, but the root cause is still a little perplexing. Connections worked fine for many months, then the failure. The MS Support Engineer that I worked with was convinced that the 2 SP4 hotfixes did not cause the issue. He thinks it may have been the result of a change in Group Policy, which I will have to do more research on.

reset sa password msde (sql server 7)

Hi Guys,
We've lost the password for the sa. No other users belong to the admin
group. I've tried logging in using osql -E (windows authentication)
without success. Is there anyway to reset the sa password? The database
is MSDE and the version seems to be 7 (sql server 7 folder on pc). It
is running on Windows 2000 professional.Any help would be greatly
appreciated.
Regards,
EddieAm 24 Sep 2006 14:10:15 -0700 schrieb eddie:

Quote:

Originally Posted by

Hi Guys,
We've lost the password for the sa. No other users belong to the admin
group. I've tried logging in using osql -E (windows authentication)
without success. Is there anyway to reset the sa password? The database
is MSDE and the version seems to be 7 (sql server 7 folder on pc). It
is running on Windows 2000 professional.Any help would be greatly
appreciated.
Regards,
Eddie

AFAIK since SQL 7 you have authentication and it is not possible to disable
login per windows authentication. So if you are logged in as windows admin
you should always be able to connect to a database like "master" (if the
server is running :-)) ). Then you can use sp_password for setting a new
password for sa.
Or you stop the server, save your database-/logfiles, make a new install of
SQL Server and then attach your database files again.

bye, Helmut|||Is it possible to upgrade the database without the sa login (and noone
else belonging to admin group)?
Helmut Woess wrote:

Quote:

Originally Posted by

Am 24 Sep 2006 14:10:15 -0700 schrieb eddie:
>

Quote:

Originally Posted by

Hi Guys,
We've lost the password for the sa. No other users belong to the admin
group. I've tried logging in using osql -E (windows authentication)
without success. Is there anyway to reset the sa password? The database
is MSDE and the version seems to be 7 (sql server 7 folder on pc). It
is running on Windows 2000 professional.Any help would be greatly
appreciated.
Regards,
Eddie

>
AFAIK since SQL 7 you have authentication and it is not possible to disable
login per windows authentication. So if you are logged in as windows admin
you should always be able to connect to a database like "master" (if the
server is running :-)) ). Then you can use sp_password for setting a new
password for sa.
Or you stop the server, save your database-/logfiles, make a new install of
SQL Server and then attach your database files again.
>
bye, Helmut

Required Security for Report Viewer on Windows 2003 SP1 Web Server

When trying to run a local report using the RS Report Viewer for VS
2005 we get the below exception when the User Account for the
Application Pool we are using is not in the Administrators Group of the
web server.
Does anybody have any suggestions as to what specific security is
needed to use the Report Viewer?
Could this be because we built the application as a "Web Project"
instead of a "Web Site"?
Microsoft.Reporting.WebForms.LocalProcessingException: An error
occurred during local report processing. -->
Microsoft.Reporting.DefinitionInvalidException: The definition of the
report 'Main Report' is invalid. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred while compiling expressions. Native
compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
could not be found'. -- End of inner exception stack trace -- at
AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
Settings\god\My Documents\Visual Studio
2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
C:\Documents and Settings\God\My Documents\Visual Studio
2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
InnerException: Microsoft.Reporting.DefinitionInvalidException: The
definition of the report 'Main Report' is invalid. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred while compiling expressions. Native
compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
could not be found'.This error is also logged in the system event viewer, however granting
access NETMAN in the DCOM manager only keeps the errors from showing
and does not fix the problem. The CLSID refers to tthe Network
Connection Manager Class (under the NETMAN application).
The application-specific permission settings do not grant Local
Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user AMI\srvAcctAppAccessDEV SID
(S-1-5-21-2975352776-793034493-3225607600-10632). This security
permission can be modified using the Component Services administrative
tool.
tfelber@.gmail.com wrote:
> When trying to run a local report using the RS Report Viewer for VS
> 2005 we get the below exception when the User Account for the
> Application Pool we are using is not in the Administrators Group of the
> web server.
> Does anybody have any suggestions as to what specific security is
> needed to use the Report Viewer?
> Could this be because we built the application as a "Web Project"
> instead of a "Web Site"?
> Microsoft.Reporting.WebForms.LocalProcessingException: An error
> occurred during local report processing. -->
> Microsoft.Reporting.DefinitionInvalidException: The definition of the
> report 'Main Report' is invalid. -->
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> An unexpected error occurred while compiling expressions. Native
> compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> could not be found'. -- End of inner exception stack trace -- at
> AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
> Settings\god\My Documents\Visual Studio
> 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
> AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
> C:\Documents and Settings\God\My Documents\Visual Studio
> 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
> InnerException: Microsoft.Reporting.DefinitionInvalidException: The
> definition of the report 'Main Report' is invalid. -->
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> An unexpected error occurred while compiling expressions. Native
> compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> could not be found'.|||Finally figured it out.
The configurable identity that our application pool was using under
IIS 6.0 needed read/write access to the c:\Windows\Temp directory.
(Explains why we could get this to work when the account was part of
the Administrators or Power Users Group). I suppose this is to give it
temp space when rendering large reports.
Hopefully this helps somebody else out one day.
tfelber@.gmail.com wrote:
> This error is also logged in the system event viewer, however granting
> access NETMAN in the DCOM manager only keeps the errors from showing
> and does not fix the problem. The CLSID refers to tthe Network
> Connection Manager Class (under the NETMAN application).
> The application-specific permission settings do not grant Local
> Activation permission for the COM Server application with CLSID
> {BA126AD1-2166-11D1-B1D0-00805FC1270E}
> to the user AMI\srvAcctAppAccessDEV SID
> (S-1-5-21-2975352776-793034493-3225607600-10632). This security
> permission can be modified using the Component Services administrative
> tool.
>
> tfelber@.gmail.com wrote:
> > When trying to run a local report using the RS Report Viewer for VS
> > 2005 we get the below exception when the User Account for the
> > Application Pool we are using is not in the Administrators Group of the
> > web server.
> >
> > Does anybody have any suggestions as to what specific security is
> > needed to use the Report Viewer?
> >
> > Could this be because we built the application as a "Web Project"
> > instead of a "Web Site"?
> >
> > Microsoft.Reporting.WebForms.LocalProcessingException: An error
> > occurred during local report processing. -->
> > Microsoft.Reporting.DefinitionInvalidException: The definition of the
> > report 'Main Report' is invalid. -->
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > An unexpected error occurred while compiling expressions. Native
> > compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> > could not be found'. -- End of inner exception stack trace -- at
> > AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
> > Settings\god\My Documents\Visual Studio
> > 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
> > AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
> > C:\Documents and Settings\God\My Documents\Visual Studio
> > 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
> > InnerException: Microsoft.Reporting.DefinitionInvalidException: The
> > definition of the report 'Main Report' is invalid. -->
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > An unexpected error occurred while compiling expressions. Native
> > compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> > could not be found'.

Request for current credentials

I'm not sure how this issue arose, but suddenly I keep getting an alert that
:
'Windows needs your current credentials to ensure network connectivity' and
requests to log off and log back on. I successfully establish a VPN
connection and this error occurs when I open Enterprise Manager to connect t
o
a remote server through the VPN. Any suggestions? Thanks.Take a look at the following:
http://www.ms-mvps.com/credentialserrors.htm
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"DLS" <DLS@.discussions.microsoft.com> wrote in message
news:A16E04AD-83EC-4336-A65A-AD38D2767F26@.microsoft.com...
> I'm not sure how this issue arose, but suddenly I keep getting an alert
> that:
> 'Windows needs your current credentials to ensure network connectivity'
> and
> requests to log off and log back on. I successfully establish a VPN
> connection and this error occurs when I open Enterprise Manager to connect
> to
> a remote server through the VPN. Any suggestions? Thanks.

Request Encryption certificate from Windows 2003 CA

I have instructions on how to request the certificate from a Windows 2000 CA
,
but not from the 2003 CA server.
http://msdn2.microsoft.com/en-us/library/aa302414.aspx
The 2000 CA instructions involve requesting a server authentication
certificate. This is not an option on a 2003 CA server. Does anyone know the
trick to getting this done?Depends on the OS version - if it's Enterprise, you can find
some differences which are outlined in the Create a Server
Certificate section of this article - configuring for a
different product but the same issues:
http://www.cisco.com/en/US/products...0804721c3.shtml
-Sue
On Tue, 6 Mar 2007 10:13:18 -0800, Jason Carter
<JasonCarter@.discussions.microsoft.com> wrote:

>I have instructions on how to request the certificate from a Windows 2000 C
A,
>but not from the 2003 CA server.
>http://msdn2.microsoft.com/en-us/library/aa302414.aspx
>The 2000 CA instructions involve requesting a server authentication
>certificate. This is not an option on a 2003 CA server. Does anyone know th
e
>trick to getting this done?

request advice on judging memory usage/allocation

Hello,
I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
with the following specs:
Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
license
2.8 GHz Xeon, 2 GB RAM, about 2 years old
Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
size < 5 GB? I don't know exactly.
Other apps are the OS, some Access MDBs accessed by Access front ends
via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
migrated to SQL Server, the Abra client is on Citrix servers.
We have a monitoring system through our support consultant. This system
tells us now and then that SQL has used up all its physical memory, but
these alerts occur at odd times like midnight on a Sunday (to be fair,
this is around the time when SQL does its maintenance, backup, etc), or
540 pm when the main GP users have all gone home and are not doing
anything in GP. I don't know yet if SQL is paging a lot, but no
specific end user complaints have appeared yet, so we have time to be
pro-active.
I've recently found some information on which PerfMon items to check,
and will try these.
My questions:
1. If we install 4 GB of RAM do I need to learn about doing anything
special like set PAE or AWE parameters or boot.ini parameters or
anything' Most of the MS links I saw on the list are for Windows 2000
Server, not Windows 2003 Server.
2. If we put in 4 GB of RAM, it seems to me that setting SQL to 2.5 GB
max and leaving the remainder for the OS and Abra Suite would work
until Abra Suite's DB is upgraded to SQL 2005, in which case I would
change it to 3 GB for SQL and the remaining 1 GB for the OS and IIS,
which is our most likely upgrade path. Does this make sense'
Right now SQL is set to dynamic memory allocation and I would like to
leave it this way if possible but I want to learn about preventing
future issues down the road related to virtual or physical memory,
especially to prevent paging, this server has to last a while!!
3. Any other forum threads or websites or advice would be most welcome.
Thank you for reading this,
Tom"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1151518715.642430.142110@.x69g2000cwx.googlegroups.com...
> Hello,
> I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
> with the following specs:
> Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
> license
> 2.8 GHz Xeon, 2 GB RAM, about 2 years old
> Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
> size < 5 GB? I don't know exactly.
> Other apps are the OS, some Access MDBs accessed by Access front ends
> via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
> migrated to SQL Server, the Abra client is on Citrix servers.
> We have a monitoring system through our support consultant. This system
> tells us now and then that SQL has used up all its physical memory, but
> these alerts occur at odd times like midnight on a Sunday (to be fair,
> this is around the time when SQL does its maintenance, backup, etc), or
> 540 pm when the main GP users have all gone home and are not doing
> anything in GP. I don't know yet if SQL is paging a lot, but no
> specific end user complaints have appeared yet, so we have time to be
> pro-active.
> I've recently found some information on which PerfMon items to check,
> and will try these.
> My questions:
> 1. If we install 4 GB of RAM do I need to learn about doing anything
> special like set PAE or AWE parameters or boot.ini parameters or
> anything' Most of the MS links I saw on the list are for Windows 2000
> Server, not Windows 2003 Server.
No. In a small setup like this SQL Server will use < 2GB of ram (that's SQL
2000 Standard Ed.'s limit).
If you install more ram, the additional ram will be available for the other
applications, and they won't have to fight with SQL Server for memory.
SQL Server is designed to use up all the available memory for data cache,
which is appropriate only for a dedicated SQL Server. If you don't install
more ram, you should change SQL Server's max memory setting to leave enough
memory for the other applications to run..SQL Server will run just fine with
1G or 1.5G of ram. It will just use a smaller data cache and have to go to
disk more often.
David|||Thank you for responding...I put in two more short questions below.
David Browne wrote:
> No. In a small setup like this SQL Server will use < 2GB of ram (that's SQL
> 2000 Standard Ed.'s limit).
> If you install more ram, the additional ram will be available for the other
> applications, and they won't have to fight with SQL Server for memory.
Adding more RAM will not make any difference' Sigh. Thank you.
Does SQL Server 2005 Standard have the same limitation' One book I
read states that SQL Server 2005 Standard has no maximum RAM limit,
please correct me if I am wrong, so that seems like a good reason to
upgrade when we can do so, at which point it will only have SQL Server,
IIS, and the OS.
> SQL Server is designed to use up all the available memory for data cache,
> which is appropriate only for a dedicated SQL Server. If you don't install
> more ram, you should change SQL Server's max memory setting to leave enough
> memory for the other applications to run..SQL Server will run just fine with
> 1G or 1.5G of ram. It will just use a smaller data cache and have to go to
> disk more often.
Are there any *negative* consequences to SQL Server 2000 paging to disk
more often other than things possibly being "slower" and whatever wear
and tear could happen to the disk drives'
So far our server may not be paging very much at all, the alerts we
receive state that the server is using ~2% of virtual memory (paging).
Is any information available about judging how much RAM the OS (Windows
2003) should have available'
The pagefile BTW on this server I believe is 4 GB.
Thank you for your help so far.
Thank you, Tom

Repost: 00000 display on upgrade to Windows XP

I posted this under 'microsoft.public.sqlserver.client' but got no reply.
Any help with this problem would be greatly appreciated
-------
I developed a database under SQL Server 2000, with Access 2000 on Windows
2000 as client. This had been running fine for several years. The client is
now upgrading to Windows XP, and has come across a display problem on the
reports. The figures are correct but are now displayed with lots of trailing
zeros e.g

365.00000

I have built a test system but cannot duplicate the problem :-( . Also the
problem varies between computers, and even between different users on the
same computer. One solution that seems to work is to explicitly cast all
output from the server e.g.

CAST ( TonsLoaded AS INT) TonsLoaded

but this is a pita since there are 141 stored procedures.

Has anyone seen this problem? I haven't found anything in the newsgroups or
any Knowlege Base article. It looks like it is to do with Windows XP as the
client ...

David"David Greenwood" <david at greenwood dot lu> wrote in message
news:40274c80$1_1@.news.vo.lu...
> I posted this under 'microsoft.public.sqlserver.client' but got no reply.
> Any help with this problem would be greatly appreciated
> -------
> I developed a database under SQL Server 2000, with Access 2000 on Windows
> 2000 as client. This had been running fine for several years. The client
is
> now upgrading to Windows XP, and has come across a display problem on the
> reports. The figures are correct but are now displayed with lots of
trailing
> zeros e.g
> 365.00000
> I have built a test system but cannot duplicate the problem :-( . Also
the
> problem varies between computers, and even between different users on the
> same computer. One solution that seems to work is to explicitly cast all
> output from the server e.g.
> CAST ( TonsLoaded AS INT) TonsLoaded
> but this is a pita since there are 141 stored procedures.
> Has anyone seen this problem? I haven't found anything in the newsgroups
or
> any Knowlege Base article. It looks like it is to do with Windows XP as
the
> client ...
> David

If the behaviour varies between users on the same PC, then the issue may be
somehow related to the language or regional settings in their profiles,
although 4 decimal places seems like a strange format. Or possibly the
updated MDAC version in Windows XP is behaving slightly differently. What
data type is the TonsLoaded column in the result set - decimal?

In any case, I guess you should be able to modify your Access client to
display these figures as integers - it's usually easier to handle display
issues in the front end. And you might consider posting this in an Access
newsgroup (if you haven't already), in case it's a known Access/XP issue.

Simon|||If the worse comes to the worse then I shall just have to fix the problem on
the client end, somehow. But this is a big file (10 mega) full of forms and
reports so that will be a major job.

I checked on the client's system and either there are 3 trailing zeros or 6
trailing zeros, though I can't see why. Also, it appears that all the
machines have exactly the same problem. The only difference I can see with
my test system is that they have Active Directory ... (?)

I shall try posting to the Access newsgroup as you suggested.

David

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4027d81a$1_2@.news.bluewin.ch...
> "David Greenwood" <david at greenwood dot lu> wrote in message
> news:40274c80$1_1@.news.vo.lu...
> > I posted this under 'microsoft.public.sqlserver.client' but got no
reply.
> > Any help with this problem would be greatly appreciated
> > -------
> > I developed a database under SQL Server 2000, with Access 2000 on
Windows
> > 2000 as client. This had been running fine for several years. The client
> is
> > now upgrading to Windows XP, and has come across a display problem on
the
> > reports. The figures are correct but are now displayed with lots of
> trailing
> > zeros e.g
> > 365.00000
> > I have built a test system but cannot duplicate the problem :-( . Also
> the
> > problem varies between computers, and even between different users on
the
> > same computer. One solution that seems to work is to explicitly cast all
> > output from the server e.g.
> > CAST ( TonsLoaded AS INT) TonsLoaded
> > but this is a pita since there are 141 stored procedures.
> > Has anyone seen this problem? I haven't found anything in the newsgroups
> or
> > any Knowlege Base article. It looks like it is to do with Windows XP as
> the
> > client ...
> > David
> If the behaviour varies between users on the same PC, then the issue may
be
> somehow related to the language or regional settings in their profiles,
> although 4 decimal places seems like a strange format. Or possibly the
> updated MDAC version in Windows XP is behaving slightly differently. What
> data type is the TonsLoaded column in the result set - decimal?
> In any case, I guess you should be able to modify your Access client to
> display these figures as integers - it's usually easier to handle display
> issues in the front end. And you might consider posting this in an Access
> newsgroup (if you haven't already), in case it's a known Access/XP issue.
> Simon|||David Greenwood (david at greenwood dot lu) writes:
> I posted this under 'microsoft.public.sqlserver.client' but got no reply.
> Any help with this problem would be greatly appreciated
> -------
> I developed a database under SQL Server 2000, with Access 2000 on
> Windows 2000 as client. This had been running fine for several years.
> The client is now upgrading to Windows XP, and has come across a display
> problem on the reports. The figures are correct but are now displayed
> with lots of trailing zeros e.g
> 365.00000
> I have built a test system but cannot duplicate the problem :-( . Also
> the problem varies between computers, and even between different users
> on the same computer. One solution that seems to work is to explicitly
> cast all output from the server e.g.

I have to admit that I saw your post in .clients, but I let it pass, since
1) I don't know Access, 2) There seems to be information missing from
your posting.

What data type are the columns in SQL Server? How does the code look
like in Access to bring the data from SQL Server to the output? Do you
just associate a record set with a window control, or is there some
processing on the way?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I rebuilt my test Windows XP system, managed to duplicate the error, and
have at last got to the bottom of the problem. It would appear that Windows
XP does not correctly interprets the data for fields based on the SQL Server
"decimal" type, even though this has always worked fine under Windows 2000.

We use decimal fields with fixed three decimal places to store tonnes and
kilos. Once these have been totalled, averaged etc they automatically track
6 decimal places of precision. After ROUNDing to 0 places the data is
returned to the client. However, running tests under Query Analyser shows
that the 6 zeros are still part of the decimal answer. Windows 2000 has
never had a problem with this, no matter what the language settings. The
data has displayed okay. In contrast, Windows XP chokes on the data. I
guess not many people use the decimal data type, so this has not been an
issue.

The work round is to CAST the data to either INT or FLOAT as required on the
server.

Thanks for the feedback,

David

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948AF2EB79AB2Yazorman@.127.0.0.1...
> David Greenwood (david at greenwood dot lu) writes:
> > I posted this under 'microsoft.public.sqlserver.client' but got no
reply.
> > Any help with this problem would be greatly appreciated
> > -------
> > I developed a database under SQL Server 2000, with Access 2000 on
> > Windows 2000 as client. This had been running fine for several years.
> > The client is now upgrading to Windows XP, and has come across a display
> > problem on the reports. The figures are correct but are now displayed
> > with lots of trailing zeros e.g
> > 365.00000
> > I have built a test system but cannot duplicate the problem :-( . Also
> > the problem varies between computers, and even between different users
> > on the same computer. One solution that seems to work is to explicitly
> > cast all output from the server e.g.
> I have to admit that I saw your post in .clients, but I let it pass, since
> 1) I don't know Access, 2) There seems to be information missing from
> your posting.
> What data type are the columns in SQL Server? How does the code look
> like in Access to bring the data from SQL Server to the output? Do you
> just associate a record set with a window control, or is there some
> processing on the way?
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||David Greenwood (david at greenwood dot lu) writes:
> We use decimal fields with fixed three decimal places to store tonnes
> and kilos. Once these have been totalled, averaged etc they
> automatically track 6 decimal places of precision. After ROUNDing to 0
> places the data is returned to the client. However, running tests under
> Query Analyser shows that the 6 zeros are still part of the decimal
> answer. Windows 2000 has never had a problem with this, no matter what
> the language settings. The data has displayed okay. In contrast,
> Windows XP chokes on the data. I guess not many people use the decimal
> data type, so this has not been an issue.

You still don't tell us what you are doing, so I have to guess that you
are doing something like:

declare @.d decimal(18, 6)
select @.d = 21
select round(@.d, 0)

This displays as 21.000000 everywhere I tried, and this appears correct to
me. The return value of round() is the same as the input value.

I don't think Windows XP vs. 2000 has anything to do it. I tried it on
two Windows 2000 machines, and I got 21.00000 back on both. Rather I
would guess that it depends on the MDAC version. Windows XP comes with
MDAC 2.7, whereas Windows 2000 comes with MDAC 2.5 or even earlier, but
to get full use of SQL 2000, it's good to have MDAC 2.6 at least.

Interesting enough, I ran the above from ISQL, which uses DB-Library,
which is not part of the MDAC, and basically unchanged since 1997. It,
too, displays 21.000000.

So it seems that your code took a shortcut, depending of what actually is a
bug in an earlier versions on the MDAC.

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

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

REPOST: New SQL Server Registration failure

Hi,

I have 3 SQL Servers running here. Here are their configurations:

SERVER 1:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 2:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 3:
OS: Windows XP Professionnal
SQL: SQL Server 2000 SP 3

Now, as you can see, SERVER 1 and SERVER 2 have identical configurations. Plus, both will accept Windows Authentication and SQL Authentication.

If I try to add a new Registration from SERVER 3 to SERVER 1, it works fine but from SERVER 3 to SERVER 2 it doesn't. I always get an error (SQL Server does not exist or access denied). But, using the exact same user name and password, a connection can be established from SERVER 1 to SERVER 2 and vice-versa. Only when trying to connect froms SERVER 3 to SERVER 2 fails.

Any ideas?

Thanks,

Skip.Hi Skippy,

I'm assuming you're running MSDE or SQL Server PE on the XP Pro machine. Is it possible that you have used the access licenses or are you disconnecting from the 1st server before you try to attach wtih the second?

Hope this helps.

John

Originally posted by Skippy_sc
Hi,

I have 3 SQL Servers running here. Here are their configurations:

SERVER 1:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 2:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 3:
OS: Windows XP Professionnal
SQL: SQL Server 2000 SP 3

Now, as you can see, SERVER 1 and SERVER 2 have identical configurations. Plus, both will accept Windows Authentication and SQL Authentication.

If I try to add a new Registration from SERVER 3 to SERVER 1, it works fine but from SERVER 3 to SERVER 2 it doesn't. I always get an error (SQL Server does not exist or access denied). But, using the exact same user name and password, a connection can be established from SERVER 1 to SERVER 2 and vice-versa. Only when trying to connect froms SERVER 3 to SERVER 2 fails.

Any ideas?

Thanks,

Skip.

reportviewer: code the windows credentials

I have bought a hosting service for reporting services. The server definitions are set to windows authentication.

I'm using a small application and each time I access a report, a windows prompt asks for the username and password. I would like to avoid this message.

How should I pass the credentials through this application and the reportviewer control (which I use) to the server in order to avoid having to insert the user details in the prompt. This application is to be used by several users and they shouldn't need to insert the account's information.

thank you for a quick reply.

There is no way to add credentials to the report viewer.

Does your local login map to a login on the server at all? If so, Internet Explorer only sends credentials automatically to servers in the Intranet zone, but you can have it automatically send credentials to your host.

You could mark your host as a trusted site (Tools -> Internet Options -> Security), and then tell IE to send credentials automatically to sites in the trusted zone. With Trusted Zones highlighted in the tab, click "Custom Level". Scroll down to the bottom and select "automatic login with current username and password".

|||This can be done. First create a class called ReportServerCredentials that implements the IReportServerCredentials interface. You will need to import System.Net and Microsoft.Reporting.WebForms to this class. Overload the New and GetFormsCredentials methods and the NetworkCredentials and ImpersonationUser properties. Here's a class that I use:

Imports Microsoft.VisualBasic
Imports Microsoft.Reporting.WebForms
Imports System.Net

Public Class ReportServerCredentials
Implements IReportServerCredentials

Private _userName As String
Private _password As String
Private _domain As String

Public Sub New(ByVal userName As String, ByVal password As String, ByVal domain As String)
_userName = userName
_password = password
_domain = domain
End Sub

Public ReadOnly Property ImpersonationUser() As System.Security.Principal.WindowsIdentity Implements Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUser
Get
Return Nothing
End Get
End Property

Public ReadOnly Property NetworkCredentials() As ICredentials Implements Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentials
Get
Return New NetworkCredential(_userName, _password, _domain)
End Get
End Property

Public Function GetFormsCredentials(ByRef authCookie As System.Net.Cookie, ByRef userName As String, ByRef password As String, ByRef authority As String) As Boolean Implements Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials
userName = _userName
password = _password
authority = _domain
Return Nothing
End Function
End Class

Next, in the OnLoad of your page with the ReportViewer control, create a new instance of your ReportServerCredentials (I use values stored in AppSettings). Then assign your object to the ReportViewer control's ServerReport.ReportServerCredentials property.

Dim cred As New ReportServerCredentials(ConfigurationManager.AppSettings("MyStoredUser"),_
ConfigurationManager.AppSettings("MyStoredPassword"), _
ConfigurationManager.AppSettings("MyStoredDomain"))

MyReportViewerControl.ServerReport.ReportServerCredentials = cred

A few things can break though-- referenced images on your reports won't render, and you can't use integrated security in your SSRS data sources, unless the account you are impersonating has access to the data as well as SSRS.
|||

Code Salad,

My immeasurable thanks. After three days of searching, your solution was the one that finally got my reports running

|||

It seems CodeSalad's code worked. Can someone explain how it works? Where do I add the code? I am having the same problem. I am using Framework 2.0. Thanks.

DanYeung

|||

I think these links will be helpful -

http://blogs.msdn.com/bimusings/archive/2005/11/18/494436.aspx

http://msdn2.microsoft.com/en-us/library/aa983458(VS.80).aspx

|||

Danyeng,

U have to add a new class file in the app_Code directory

|||

The links and the sample code are very helpful. I still need more help on this. How do I get the ReportViewer control? I am opening the report without using ReportView control. I added the attribute to the button to open the report with Javascript. If I use the ReportViewer control, do I have to open the report differently and how? Thanks.

DanYeung

|||

this is how u doing it.

create a new aspx page and add the report viewer control to ur page by going View Menu -> Toolbox and in the toolbox look under Data.. and then in the properties window, Select the processingmode i.e Remote or local and also specify the report path...

and then in the aspx.vb page in Page_Load sub give this code.

ReportViewer1.ProcessingMode = ProcessingMode.Remote ReportViewer1.ShowCredentialPrompts =True'ReportViewer1.ShowExportControls = False ReportViewer1.ServerReport.ReportServerCredentials =New ReportServerCredentials() ReportViewer1.ServerReport.ReportServerUrl =New Uri("http://IPAddress/ReportServer") ReportViewer1.ServerReport.ReportPath ="/FolderName/Mainrdlfilename" ReportViewer1.ServerReport.Refresh()

ReportViewer1.ServerReport.ReportServerCredentials =New ReportServerCredentials() -- ReportServerCredentials is the base class name of the class that u put in the app_code folder

Hope this helps.

Regards,

Karen

|||

Thanks Karen. I am getting closer. I received the following error:

The path of the item '/Pages/ReportViewer.aspx?nsPortalReports.rptIssuesByRole&rs:Command=Render&intProjectID=119&RunBy=DanYeung' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)

The report is located in the different project with the same solution. nsPortalReports is the project name and rptIssuesByRole is the report name. What is wrong in the path? Thanks.

DanYeung

|||

is ur report stored in the report server? and if ur reports are stored in the report server is there any kinda directory structure in it...

if u answer this questions may be i can give u a better answer.

Regards

Karen

|||

Yes, I deployed the reports to the report server. Thanks.

DanYeung

|||

are u able to run the reports using the report viewer...

Since ur reports are in the report server just give the path as i have given and it should work... if u need to pass any parameters to the report also let me know i will help u out..

Regards

Karen

|||

How do I run the report using ReportViewer? Yes, I have to pass parameters. So I am not supposed pass parameters in the ReportPath? Is my path correct?

ReportViewer1.ServerReport.ReportPath = "/Pages/ReportViewer.aspx?nsPortalReports.rptIssuesByRole&rs:Command=Render&intProjectID=" & cboProject.SelectedValue & "&RunBy=" & Session("strEmployeeName")

Thanks.

DanYeung

|||

your report Path doesnt Look right to me.

Instead of specifying the report Path... Specify a the ReportServerURL so that u can access ur report server directly. and the url will behttp://IpAddress/ReportServer.

take a look at this code as to how to specify the parameters and the report Path

ReportViewer1.ProcessingMode = ProcessingMode.Remote ReportViewer1.ShowCredentialPrompts =True ReportViewer1.ShowExportControls =False ReportViewer1.ServerReport.ReportServerCredentials =New ReportServerCredentials() ReportViewer1.ServerReport.ReportServerUrl =New Uri("http://IPAddress/ReportServer") s =CType(PreviousPage.FindControl("ddlReportType"), DropDownList).SelectedItem.ValueIf (s ="/Statements/StatementMain")Then ReportViewer1.ServerReport.ReportPath ="/Statements/StatementMain"Dim ClientIDAs String = Session("ClientId")Dim param(0)As Microsoft.Reporting.WebForms.ReportParameter'param(0) = New Microsoft.Reporting.WebForms.ReportParameter("PeriodId", "") param(0) =New Microsoft.Reporting.WebForms.ReportParameter("ClientId", ClientID,False) ReportViewer1.ServerReport.SetParameters(param)ReportViewer1.ServerReport.Refresh()End If

u can omit the parameters so that u can directly set them while u run the reports.Hope this helps.

Regards,

Karen

ReportViewer, VS2005 and SSRS 2005

I have developed a web app that uses forms authentication. I wrote it in asp.net 2.0. My server is running windows 2003 server standard and IIS. I have also installed on the same machine SQL Server 2005 with Reporting Services.

From my webapplication, I have a button that the user can click and then I have the Report Viewer control generate a report. However, I do not know how to pass the proper credentials to the report server. I would like to pass one username and password that has permissions to run that report. My code is follows:

Private Sub saveRptAs(ByVal s_rptType As String)
Dim warnings As Microsoft.Reporting.WebForms.Warning()
Dim streamids As String()
Dim mimeType As String
Dim encoding As String
Dim extension As String

ReportViewer1.ServerReport.ReportServerUrl = New Uri("http://myserver/reportserver")
ReportViewer1.ServerReport.ReportPath = "/reports/Individual Report"

Dim test As New NetworkCredential("localuser", "password", "domain")

ReportViewer1.ServerReport.ReportServerCredentials = test

Dim rprameter As ReportParameter = New ReportParameter("user_no", Profile.DTGVariables.SelectedYMNo)
Me.ReportViewer1.ServerReport.SetParameters(New ReportParameter() {rprameter})

Dim bytes As Byte() = ReportViewer1.ServerReport.Render(s_rptType, Nothing, mimeType, encoding, extension, streamids, warnings)
Response.Buffer = True
Response.Clear()
Response.ContentType = mimeType
Response.AddHeader("content-disposition", "attachment; filename=sample." + extension)
Response.BinaryWrite(bytes)
Response.Flush()
Response.End()
End Sub

When I click on the button I get the following message:

Unable to cast object of type 'System.Net.NetworkCredential' to type 'Microsoft.Reporting.WebForms.IReportServerCredentials'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: Unable to cast object of type 'System.Net.NetworkCredential' to type 'Microsoft.Reporting.WebForms.IReportServerCredentials'.

Can someone help me with the credentials issue? Do I need to change Reporting Services to use Forms Authentication?

Your help and insight is greatly appreciated.

Thanks

Nate Dogg

put your credentials in web.config, that's how i worked around it

<identityimpersonate="true"userName="Domain\user"password="password"/>

|||try this - works for me

... ServerReport report = ReportViewer1.ServerReport; report.ReportServerCredentials = new MyReportServerCredentials(); ... class MyReportServerCredentials : IReportServerCredentials{ public MyReportServerCredentials() { } public System.Security.Principal.WindowsIdentity ImpersonationUser { get { return null; // Use default identity. } } public System.Net.ICredentials NetworkCredentials { get { return new System.Net.NetworkCredential("user", "pass", "domain"); } } public bool GetFormsCredentials(out System.Net.Cookie authCookie, out string user, out string password, out string authority) { authCookie = null; user = password = authority = null; return false; // Not use forms credentials to authenticate. } }

|||

Hey guys this is killing me also. I am using webhosting4life.com as my hosting company and have reporting services. I am trying the same thing the original poster is doing and getting the same error message. When I try the impersonate user I get the following error message. All I truly want is for the report viewer to pull open the report and display to the end user...but I need to pass my user name and password over....PLEASE HELP!! I have read several books and none of them touch on this.

Configuration Error

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:Could not create Windows user token from the credentials specified in the config file. Error from the operating system 'Unspecified error
'

|||

It appears the error is in the web.config. Implementing theIReportServerCredentials as shown in the previous article works and is a good solution to only having the SRS work under that user context.