Showing posts with label application. Show all posts
Showing posts with label application. Show all posts

Friday, March 23, 2012

Re-set identity field

Hi:

I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that?

Can you assist?

Thanks

http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

Tuesday, March 20, 2012

Re-raising error

In a stored procedure I usually check @.@.ERROR after every
INSERT/UPDATE/DELETE. If any error, then I exit SP with this error
code. A client application does not receive much information
with this code, so it displays a message like "Cannot insert/update record.
Error : NNN".
Is there a way to get more detailed information about an error?
RAISERROR only throws user-defined errors. Any ideas?"Tumurbaatar S." <spam_tumur@.magicnet.mn> wrote in message
news:u6%23Wmxd8FHA.3132@.TK2MSFTNGP12.phx.gbl...
> In a stored procedure I usually check @.@.ERROR after every
> INSERT/UPDATE/DELETE. If any error, then I exit SP with this error
> code. A client application does not receive much information
> with this code, so it displays a message like "Cannot insert/update
> record. Error : NNN".
> Is there a way to get more detailed information about an error?
> RAISERROR only throws user-defined errors. Any ideas?
>
Unless you use TSQL TRY/CATCH the client will recieve both the original
error and the stored procedure return code. Different client libraries
interpret this data differently, but most have a way to grab the error.
David|||Is your client application SQLServer or something else (dotnet, java etc)?
If SQL then @.@.error is fine unless you want to catch the specific error from
sysmessages. I only ever use raiserror(@.text,1,1) for triggers and i don't
use triggers so I don't really use it.
If it is an external App and you are not a GOTOless programmer then try
something like below then just call the entries from the log table in the
external App:
If @.@.ERROR <> 0 or @.@.ROWCOUNT <= 0
begin
select @.text = 'Error -50: Could not update TableAdata for w.'
select @.result = -50
GOTO ERROR_POINT
end
ERROR_POINT:
PRINT 'ERROR_POINT'
GOTO FINISH
FINISH:
select @.resultText = 'RoutineName: ' + @.text + ' from User ' + @.pWho + ' at
' + convert(varchar(15),getdate(),3) + ' ' + convert(varchar(15),getdate(),1
4)
INSERT INTO log VALUES (@.resultText, 'RoutineName', getdate(), 'Y')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO|||No pun intended, but please explain how this one works.
There must be something missing from the sample you posted.
All statements will be executed - even if there is no error and the rowcount
is above 0, and in such a case a null value (or an unexpected one) will be
inserted into the log.
Do you actually use this in your production code?
ML|||Thank you!
But you are both talking about SQL2005? If I'm not mistaken,
SQL2000 does not support TRY/CATCH exception handling. And that
is only reason why I asked for how to re-raise (read "rethrow") an error.
If there was try/catch handling in SQL2000, I had no problem.|||"Tumurbaatar S." <spam_tumur@.magicnet.mn> wrote in message
news:%235tAGhh8FHA.3984@.TK2MSFTNGP11.phx.gbl...
> Thank you!
> But you are both talking about SQL2005? If I'm not mistaken,
> SQL2000 does not support TRY/CATCH exception handling. And that
> is only reason why I asked for how to re-raise (read "rethrow") an error.
> If there was try/catch handling in SQL2000, I had no problem.
>
IN SQL 2000 there is no way to prevent the error message from propagating to
the client. If the client is using, for instance, .NET the calling code
will get a SqlException. Only in SQL 2005 is there a way to stop the error
from going to the client (CATCH), and so only there is there any need to
"rethrow" the error in SQL Server.
David|||> IN SQL 2000 there is no way to prevent the error message from propagating
> to the client. If the client is using, for instance, .NET the calling
> code will get a SqlException.
But how does a client receive an error? For example, a SP executes
INSERT that fails due to some constraint violation:
INSERT ...
IF @.@.ERROR <> 0
...
What happens in this case? SP execution stops before IF @.@.ERROR,
exits with an error notification and the client engine receives a standard
SQL error. Or SQL server remembers this error, the SP continues processing
and when SP exits normally (i.e. RETURN @.some_value) the client engine
receives
this return code (@.some_value), but also it receives the previously saved
error too?|||"Tumurbaatar S." <spam_tumur@.magicnet.mn> wrote in message
news:e2m$$tq8FHA.472@.TK2MSFTNGP15.phx.gbl...
> But how does a client receive an error? For example, a SP executes
> INSERT that fails due to some constraint violation:
> INSERT ...
> IF @.@.ERROR <> 0
> ...
> What happens in this case? SP execution stops before IF @.@.ERROR,
> exits with an error notification and the client engine receives a standard
> SQL error. Or SQL server remembers this error, the SP continues processing
> and when SP exits normally (i.e. RETURN @.some_value) the client engine
> receives
> this return code (@.some_value), but also it receives the previously saved
> error too?
Sort of, yes. The client gets the return code and any errors which occured.
Most client libraries don't expose both through the API, however. If an
error occurs, you will likely not get a return code from the procedure.
David|||Many thanks!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e$Zbolw8FHA.740@.TK2MSFTNGP11.phx.gbl...
> "Tumurbaatar S." <spam_tumur@.magicnet.mn> wrote in message
> news:e2m$$tq8FHA.472@.TK2MSFTNGP15.phx.gbl...
> Sort of, yes. The client gets the return code and any errors which
> occured. Most client libraries don't expose both through the API, however.
> If an error occurs, you will likely not get a return code from the
> procedure.
> David
>
>

Requires experts help

Hello,
In my application I need to call a HTTP address from the SQL server.Can I call the HTTP address within a stored procedure?
Are there any ways to call HTTP address using SQL server?
Awaiting for the reply.
RegardsOriginally posted by SPathak
Hello,

In my application I need to call a HTTP address from the SQL server.Can I call the HTTP address within a stored procedure?
Are there any ways to call HTTP address using SQL server?

Awaiting for the reply.

Regards

what do u want to do exactly after calling the http address?
do u want to access any data on the page or something?
please clarify a bit more|||Refer to books online for Executing SQL Statements Using HTTP topic.|||I need to call the ISAPI dll in the IIS virtual directory from stored procedure.Can it be possible?|||Originally posted by SPathak
I need to call the ISAPI dll in the IIS virtual directory from stored procedure.Can it be possible?

it is quite possible ,use sp_oacreate,
for details refer to BOL.|||As I am new to this Can you explain me in detail how to create the http object using this procedure.|||As its been suggested twice already..

READ Books On Line for more information.|||Well, for this reason I always suggest to refer to books online for any information. Hope few times you may not get much insight about the issue but it helps you to asses.

Requirements for Sql Server Mobile and Sql Server 2003 interaction

Folks,

I am trying to use .NET 2005 Beta 2 to write an application targeted to a PPC2003 device. I want to use RDA to Push and Pull data from a Sql Server 2003 database.

This communication fails because I do not have sscesa30.dll in the IIS virtual directory from which the data are pulled.

I would like to know exactly what I need to install on the server so that everything is wired up correctly.

I do not really want to install Sql Server 2005 (CPT) on my server. Is there an SDK that I can install there that makes the sscesa30.dll available? Can it be run without any Sql Server 2005 (CPT) requirements?

Thanks in advance.
-Joel Finkel
finkel@.sd-il.comHi,

I am really not aware of SQL Server 2003. SQL Mobile 3.0 works with both SQL Server 2000 and SQL Server 2005.

I believe that you are working with SQL Server 2000? If so read below!

Given that SQL Mobile 3.0 works with SQL Server 2000. That is you can have SQL Server old version but have the SQL Mobile/CE clients with latest version.

This is what is expected infrastructure:

1) Server Box - SQL Server 2000 SP3a or above
2) IIS Box - Install I1, I2 in order.
3) Device - SQL Mobile 3.0 (Can get from Visual Studio 2005)

I1 - http://www.microsoft.com/downloads/details.aspx?FamilyId=09545A2A-3D2A-4811-AFD5-41AF0F6DF2B3&displaylang=en
I2 - http://www.microsoft.com/downloads/details.aspx?familyid=0A6174A4-C009-4768-8284-698C32EC84E3&displaylang=en

Also, please note that IIS & SQL Server can be on one box. In which case only install I2 on SQL Server Box. You do not need to install I1.

Let me know if you need any more information.

Thanks,
Laxmi NRO, MSFT, SQL Mobile

|||

Thanks for your help, but I have run into issues.

Here is my setup:

IIS 6
Sql Server 2000 (SP4)
Windows 2003 Server

Referring to your note, you write:

"Also, please note that IIS & SQL Server can be on one box.
In which case only install I2 on SQL Server Box. You do not
need to install I1."

Howevever:

When I attempt to install I2, it fails because I have not yet installed I1.

When I attempt to install I1, it fails because I am not running
Sql Server 2000 SP3 or SP3a. I am runnning SP4.

This has now become a critical issue. Any help is greatly appreciated.

|||I2 in the above thread is a beta one by which time SP4 was not shipped.
I2 latest version that works with SP4 is shipped with VS 2005.

The web downloads will be updated with the latest version soon.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Requirements for Sql Server Mobile and Sql Server 2003 interaction

Folks,

I am trying to use .NET 2005 Beta 2 to write an application targeted to a PPC2003 device. I want to use RDA to Push and Pull data from a Sql Server 2003 database.

This communication fails because I do not have sscesa30.dll in the IIS virtual directory from which the data are pulled.

I would like to know exactly what I need to install on the server so that everything is wired up correctly.

I do not really want to install Sql Server 2005 (CPT) on my server. Is there an SDK that I can install there that makes the sscesa30.dll available? Can it be run without any Sql Server 2005 (CPT) requirements?

Thanks in advance.
-Joel Finkel
finkel@.sd-il.comHi,

I am really not aware of SQL Server 2003. SQL Mobile 3.0 works with both SQL Server 2000 and SQL Server 2005.

I believe that you are working with SQL Server 2000? If so read below!

Given that SQL Mobile 3.0 works with SQL Server 2000. That is you can have SQL Server old version but have the SQL Mobile/CE clients with latest version.

This is what is expected infrastructure:

1) Server Box - SQL Server 2000 SP3a or above
2) IIS Box - Install I1, I2 in order.
3) Device - SQL Mobile 3.0 (Can get from Visual Studio 2005)

I1 - http://www.microsoft.com/downloads/details.aspx?FamilyId=09545A2A-3D2A-4811-AFD5-41AF0F6DF2B3&displaylang=en
I2 - http://www.microsoft.com/downloads/details.aspx?familyid=0A6174A4-C009-4768-8284-698C32EC84E3&displaylang=en

Also, please note that IIS & SQL Server can be on one box. In which case only install I2 on SQL Server Box. You do not need to install I1.

Let me know if you need any more information.

Thanks,
Laxmi NRO, MSFT, SQL Mobile|||

Thanks for your help, but I have run into issues.

Here is my setup:

IIS 6
Sql Server 2000 (SP4)
Windows 2003 Server

Referring to your note, you write:

"Also, please note that IIS & SQL Server can be on one box.
In which case only install I2 on SQL Server Box. You do not
need to install I1."

Howevever:

When I attempt to install I2, it fails because I have not yet installed I1.

When I attempt to install I1, it fails because I am not running
Sql Server 2000 SP3 or SP3a. I am runnning SP4.

This has now become a critical issue. Any help is greatly appreciated.

|||I2 in the above thread is a beta one by which time SP4 was not shipped.
I2 latest version that works with SP4 is shipped with VS 2005.

The web downloads will be updated with the latest version soon.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Requirements for Sql Server Mobile and Sql Server 2003 interaction

Folks,

I am trying to use .NET 2005 Beta 2 to write an application targeted to a PPC2003 device. I want to use RDA to Push and Pull data from a Sql Server 2003 database.

This communication fails because I do not have sscesa30.dll in the IIS virtual directory from which the data are pulled.

I would like to know exactly what I need to install on the server so that everything is wired up correctly.

I do not really want to install Sql Server 2005 (CPT) on my server. Is there an SDK that I can install there that makes the sscesa30.dll available? Can it be run without any Sql Server 2005 (CPT) requirements?

Thanks in advance.
-Joel Finkel
finkel@.sd-il.comHi,

I am really not aware of SQL Server 2003. SQL Mobile 3.0 works with both SQL Server 2000 and SQL Server 2005.

I believe that you are working with SQL Server 2000? If so read below!

Given that SQL Mobile 3.0 works with SQL Server 2000. That is you can have SQL Server old version but have the SQL Mobile/CE clients with latest version.

This is what is expected infrastructure:

1) Server Box - SQL Server 2000 SP3a or above
2) IIS Box - Install I1, I2 in order.
3) Device - SQL Mobile 3.0 (Can get from Visual Studio 2005)

I1 - http://www.microsoft.com/downloads/details.aspx?FamilyId=09545A2A-3D2A-4811-AFD5-41AF0F6DF2B3&displaylang=en
I2 - http://www.microsoft.com/downloads/details.aspx?familyid=0A6174A4-C009-4768-8284-698C32EC84E3&displaylang=en

Also, please note that IIS & SQL Server can be on one box. In which case only install I2 on SQL Server Box. You do not need to install I1.

Let me know if you need any more information.

Thanks,
Laxmi NRO, MSFT, SQL Mobile|||

Thanks for your help, but I have run into issues.

Here is my setup:

IIS 6
Sql Server 2000 (SP4)
Windows 2003 Server

Referring to your note, you write:

"Also, please note that IIS & SQL Server can be on one box.
In which case only install I2 on SQL Server Box. You do not
need to install I1."

Howevever:

When I attempt to install I2, it fails because I have not yet installed I1.

When I attempt to install I1, it fails because I am not running
Sql Server 2000 SP3 or SP3a. I am runnning SP4.

This has now become a critical issue. Any help is greatly appreciated.

|||I2 in the above thread is a beta one by which time SP4 was not shipped.
I2 latest version that works with SP4 is shipped with VS 2005.

The web downloads will be updated with the latest version soon.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

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

Required Microsoft Sql Server 2005 Express Server Roles for JDBC Connection

Hi!

I have developed a database in MS SQL Server 2005 Express, to which I would require only bulkadmin server role from an external java application, because I only need to update rows, insert values or use select queries in the database.

The problem is that, using either the Microsoft JDBC Driver 1.1 or the Java JDBC ODBC Driver and the Windows XP Data Base (ODBC) configurations, I need a user with sysadmin server role inside Sql Server, otherwise JDBC won't connect to the database using the selected user. Even if I leave the sql login with setupadmin or any server role lower than sysadmin, the connection is refused.

Is there no way to connect using JDBC to MS Sql Server 2005 other than granting the connected user sysadmin rights? My code looks as follows:

Code Snippet

String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://FIREBLADE\\SQLEXPRESS";
String user = "username";
String password = "password$$";
Connection conn;

Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
if (conn != null)
System.out.println("\nSQL Server Connection established ...\n");

I have heard that Java JDBC connections to Microsoft require high-level access.

Any informed answer is more than welcome. Thanks for reading my post!

Your connection string is a bit different than what's documented in the following article - you'd want to start there:

http://blogs.msdn.com/angelsb/archive/2005/08/01/446452.aspx

Connecting through JDBC does not require high level access or sysadmin rights.

-Sue

Required DLLs for SQL7 DTS

I've written a VB6 application that executes a DTS package on SQL7.

The application works perfectly in both Develop and Runtime environments from my system. But when deployed (with default DLLs)to the intended client, the application raises an exception when creating an instance of the DTS object, "Set oPkg = New DTS.Package". The exception says "it can't create and instance of the object" or something to that effect.

While trying to figure out the problem, I installed the application ( without the default DLLs ) on the SQL Server machine. Again, it ran without a problem.

Unfortunately, the SQL machine is not the intended client. All systems here are running W2K(SP2).

Can someone offer some advice?

Thanks,

PaulThere are a couple of rll's you need like dtspkg.rll - the following page should answer your questions:

page (http://www.dbforums.com/archive/43/2002/04/3/356646)|||Originally posted by rnealejr
There are a couple of rll's you need like dtspkg.rll - the following page should answer your questions:

page (http://www.dbforums.com/archive/43/2002/04/3/356646)

Thanks for your response. However I am using a DSN-less connection. So the page above really does not apply.

Any other ideas. My only other option is to incorporate the function of the DTS package into the VB application. I would really like to aviod this option if possible.

Thanks again,

Paul|||Have you included the reference that you are using within your vb project when you install on another machine ? There are probably x number of files that have been left out and/or have not been registered on the deployed machine. I wanted you to see the top half of the article I posted (not the bottom half - about the dsn connection). The top half focused on required files for deployment.

Let me know.

Friday, March 9, 2012

Republishing a subscribed database

In a merge replication application with 100 subcribers the merge process is
generating large numbers of locks on the published db and causing
performance problems.
One suggestion was that we subscribe all external (VPN on ADSL) merge
subscribers to a subscribed copy of the database created explicitly for this
purpose, thereby reducing load on the primary db. Any comments? or has
anyone implemented a similar scheme and found it beneficial?
This is a good approach. You should try to identify where the locking is
coming from. For instance if you try to modify/manage your publications
using EM this can cause a large amount of locking. So use the procs if
possible.
Also use pull if you can. Then stagger your agents to run non-continuously
at different times.
"Tony Toker" <xyzzy@.identic.co.uk> wrote in message
news:cqbln8$dp4$1$8302bc10@.news.demon.co.uk...
> In a merge replication application with 100 subcribers the merge process
is
> generating large numbers of locks on the published db and causing
> performance problems.
> One suggestion was that we subscribe all external (VPN on ADSL) merge
> subscribers to a subscribed copy of the database created explicitly for
this
> purpose, thereby reducing load on the primary db. Any comments? or has
> anyone implemented a similar scheme and found it beneficial?
>
>

Wednesday, March 7, 2012

Reprting Services 2000 Printing

Hi,
I am using Reporting services 2000 in asp.net application.Everything is working fine ,but when I am trying to get a print using Internet Explorer Printing options ,the toolbar of the report is also coming into the print preview.I want to get rid of it.I want only the report no toolbar.
Can anybody help me?
Thanks!!I am not sure if IE Printing options will allow you to do that. You might need to write some code to do that. I'll see if I can cook up something for you if I get the time.

Representing currency?

We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments?
Thanks!
Consider using the money datatype in SQL Server. It keeps 4 decimal places.
I'd never use real or float for money. It can handle very large numbers,
somewhat akin to my weekly salary. ;-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments?
Thanks!
|||Have you considered using the money data type?
J
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
> We have a pretty large financial application using SQL Server 2000 and an
> Access front-end. We're having trouble with rounding of currency values.
> In
> the database, we're using a Real datatype for money amounts. The problem
> is
> that in the VBA program we calculate a invoice header value for Invoice
> Total, and store it in a Real field. But when we do a SQL Sum() on the
> invoice line items, the value returned is "sometimes" off by a penny or
> so.
> Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
> In the VBA program, we store the database values in a variable of type
> Single.
> Any comments?
> Thanks!
>
|||I'll differ from the other replies by saying don't use MONEY or SMALLMONEY
for monetary amounts. Use NUMERIC or DECIMAL instead. The money types have
no particular advantage (trivially you might save 1 byte in storage over the
equivalent DECIMAL) but they do have some significant problems with rounding
that can cause you to lose precision in calculations.
In any case REAL is an inexact numeric and so is probably totally unsuitable
for currency amounts.
David Portas
SQL Server MVP

Representing currency?

We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!Consider using the money datatype in SQL Server. It keeps 4 decimal places.
I'd never use real or float for money. It can handle very large numbers,
somewhat akin to my weekly salary. ;-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!|||Have you considered using the money data type?
J
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
> We have a pretty large financial application using SQL Server 2000 and an
> Access front-end. We're having trouble with rounding of currency values.
> In
> the database, we're using a Real datatype for money amounts. The problem
> is
> that in the VBA program we calculate a invoice header value for Invoice
> Total, and store it in a Real field. But when we do a SQL Sum() on the
> invoice line items, the value returned is "sometimes" off by a penny or
> so.
> Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
> In the VBA program, we store the database values in a variable of type
> Single.
> Any comments'
> Thanks!
>|||I'll differ from the other replies by saying don't use MONEY or SMALLMONEY
for monetary amounts. Use NUMERIC or DECIMAL instead. The money types have
no particular advantage (trivially you might save 1 byte in storage over the
equivalent DECIMAL) but they do have some significant problems with rounding
that can cause you to lose precision in calculations.
In any case REAL is an inexact numeric and so is probably totally unsuitable
for currency amounts.
--
David Portas
SQL Server MVP
--

Representing currency?

We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!Consider using the money datatype in SQL Server. It keeps 4 decimal places.
I'd never use real or float for money. It can handle very large numbers,
somewhat akin to my weekly salary. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments'
Thanks!|||Have you considered using the money data type?
J
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
> We have a pretty large financial application using SQL Server 2000 and an
> Access front-end. We're having trouble with rounding of currency values.
> In
> the database, we're using a Real datatype for money amounts. The problem
> is
> that in the VBA program we calculate a invoice header value for Invoice
> Total, and store it in a Real field. But when we do a SQL Sum() on the
> invoice line items, the value returned is "sometimes" off by a penny or
> so.
> Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
> In the VBA program, we store the database values in a variable of type
> Single.
> Any comments'
> Thanks!
>|||I'll differ from the other replies by saying don't use MONEY or SMALLMONEY
for monetary amounts. Use NUMERIC or DECIMAL instead. The money types have
no particular advantage (trivially you might save 1 byte in storage over the
equivalent DECIMAL) but they do have some significant problems with rounding
that can cause you to lose precision in calculations.
In any case REAL is an inexact numeric and so is probably totally unsuitable
for currency amounts.
David Portas
SQL Server MVP
--

Saturday, February 25, 2012

REPOST: optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimIf you don't get the SQL Server log entry that is mentioned in the KB
you posted, it may not related at all. By just knowing the warning in
the application event, it is not sufficient to say more.
To find out why the job failed, go to the individual job in EM, right
click and select 'show job history' and check on 'Show Details' box.
It should give you more ideas what went wrong. It could be disk space,
permission, resources conflict issues etc.
Mel|||Hi Mel,
It shows the same message as is in the KB article:
"The job failed. The Job was invoked by User
<computer_system_administrator>. The last step to run was step 1 (Step 1)."
There is only one step:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
It's not clear to me how to troubleshoot further.
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145009319.702671.27150@.v46g2000cwv.googlegroups.com...
> If you don't get the SQL Server log entry that is mentioned in the KB
> you posted, it may not related at all. By just knowing the warning in
> the application event, it is not sufficient to say more.
> To find out why the job failed, go to the individual job in EM, right
> click and select 'show job history' and check on 'Show Details' box.
> It should give you more ideas what went wrong. It could be disk space,
> permission, resources conflict issues etc.
> Mel
>|||The error message isn't very helpful, is it :)
Okay last attempt, change the job owner to 'sa', to see if it is
because of that.
If still not joys, back to the old classic rule - re-create the plan
(delete the existing one and create a new one). Did you create the job
manually? If so, try to use the DB Maint Wizard to create the job and
compare the two.
Mel|||Thanks for the tip, Mel!
I've changed job owner to "sa". This job is part of a maintenance that runs
once a month, on the first of the month. We'll see how it goes in a two and
one-half weeks!
The plan was recently recreated, but it could be re-recreated to see if that
helps.
Good day,
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145046790.093217.94260@.g10g2000cwb.googlegroups.com...
> The error message isn't very helpful, is it :)
> Okay last attempt, change the job owner to 'sa', to see if it is
> because of that.
> If still not joys, back to the old classic rule - re-create the plan
> (delete the existing one and create a new one). Did you create the job
> manually? If so, try to use the DB Maint Wizard to create the job and
> compare the two.
> Mel
>

REPOST: optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimIf you don't get the SQL Server log entry that is mentioned in the KB
you posted, it may not related at all. By just knowing the warning in
the application event, it is not sufficient to say more.
To find out why the job failed, go to the individual job in EM, right
click and select 'show job history' and check on 'Show Details' box.
It should give you more ideas what went wrong. It could be disk space,
permission, resources conflict issues etc.
Mel|||Hi Mel,
It shows the same message as is in the KB article:
"The job failed. The Job was invoked by User
<computer_system_administrator>. The last step to run was step 1 (Step 1)."
There is only one step:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
It's not clear to me how to troubleshoot further.
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145009319.702671.27150@.v46g2000cwv.googlegroups.com...
> If you don't get the SQL Server log entry that is mentioned in the KB
> you posted, it may not related at all. By just knowing the warning in
> the application event, it is not sufficient to say more.
> To find out why the job failed, go to the individual job in EM, right
> click and select 'show job history' and check on 'Show Details' box.
> It should give you more ideas what went wrong. It could be disk space,
> permission, resources conflict issues etc.
> Mel
>|||The error message isn't very helpful, is it
Okay last attempt, change the job owner to 'sa', to see if it is
because of that.
If still not joys, back to the old classic rule - re-create the plan
(delete the existing one and create a new one). Did you create the job
manually? If so, try to use the DB Maint Wizard to create the job and
compare the two.
Mel|||Thanks for the tip, Mel!
I've changed job owner to "sa". This job is part of a maintenance that runs
once a month, on the first of the month. We'll see how it goes in a two and
one-half weeks!
The plan was recently recreated, but it could be re-recreated to see if that
helps.
Good day,
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145046790.093217.94260@.g10g2000cwb.googlegroups.com...
> The error message isn't very helpful, is it
> Okay last attempt, change the job owner to 'sa', to see if it is
> because of that.
> If still not joys, back to the old classic rule - re-create the plan
> (delete the existing one and create a new one). Did you create the job
> manually? If so, try to use the DB Maint Wizard to create the job and
> compare the two.
> Mel
>

Tuesday, February 21, 2012

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