ForceEncryption Parameter

Hi All,

I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.

I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.

Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.

But, I do get the following error when I set Encrypt = Yes

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

and, If I set Encrypt=No, I do not get any error again.

My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?

Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.

Could you please help me understand the results of various options like this?

Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

The Encrypt = No setting again does not give any error.

Thanks and Regards,

-Ajay Kumar Suri

Hi Ajay,

The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx

Il-Sung.

|||

Hi,

Thanks very much for your reply.

Could you please verify if my understanding now is correct for SQL Server 2005.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

I hope I have understood it correctly.

Please advise.

Best Regards,

-Ajay Kumar Suri

|||

Hi Ajay,

Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

> Correct.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

> Correct, although the login packet will always be encrypted.

Hope this clears things up.

Il-Sung.

|||

Hi Sung,

Thanks very much for your comments.

It does clear a lot of things for me.

Best Regards,

-Ajay Kumar Suri

ForceEncryption Parameter

Hi All,

I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.

I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.

Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.

But, I do get the following error when I set Encrypt = Yes

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

and, If I set Encrypt=No, I do not get any error again.

My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?

Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.

Could you please help me understand the results of various options like this?

Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

The Encrypt = No setting again does not give any error.

Thanks and Regards,

-Ajay Kumar Suri

Hi Ajay,

The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx

Il-Sung.

|||

Hi,

Thanks very much for your reply.

Could you please verify if my understanding now is correct for SQL Server 2005.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

I hope I have understood it correctly.

Please advise.

Best Regards,

-Ajay Kumar Suri

|||

Hi Ajay,

Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

> Correct.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

> Correct, although the login packet will always be encrypted.

Hope this clears things up.

Il-Sung.

|||

Hi Sung,

Thanks very much for your comments.

It does clear a lot of things for me.

Best Regards,

-Ajay Kumar Suri

ForceEncryption Parameter

Hi All,

I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.

I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.

Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.

But, I do get the following error when I set Encrypt = Yes

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

and, If I set Encrypt=No, I do not get any error again.

My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?

Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.

Could you please help me understand the results of various options like this?

Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

The Encrypt = No setting again does not give any error.

Thanks and Regards,

-Ajay Kumar Suri

Hi Ajay,

The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx

Il-Sung.

|||

Hi,

Thanks very much for your reply.

Could you please verify if my understanding now is correct for SQL Server 2005.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

I hope I have understood it correctly.

Please advise.

Best Regards,

-Ajay Kumar Suri

|||

Hi Ajay,

Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

> Correct.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

> Correct, although the login packet will always be encrypted.

Hope this clears things up.

Il-Sung.

|||

Hi Sung,

Thanks very much for your comments.

It does clear a lot of things for me.

Best Regards,

-Ajay Kumar Suri

ForceEncryption Parameter

Hi All,

I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.

I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.

Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.

But, I do get the following error when I set Encrypt = Yes

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

and, If I set Encrypt=No, I do not get any error again.

My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?

Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.

Could you please help me understand the results of various options like this?

Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

The Encrypt = No setting again does not give any error.

Thanks and Regards,

-Ajay Kumar Suri

Hi Ajay,

The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx

Il-Sung.

|||

Hi,

Thanks very much for your reply.

Could you please verify if my understanding now is correct for SQL Server 2005.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

I hope I have understood it correctly.

Please advise.

Best Regards,

-Ajay Kumar Suri

|||

Hi Ajay,

Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

> Correct.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

> Correct, although the login packet will always be encrypted.

Hope this clears things up.

Il-Sung.

|||

Hi Sung,

Thanks very much for your comments.

It does clear a lot of things for me.

Best Regards,

-Ajay Kumar Suri

Force prompt when using default parameters

I wrote an sp and assigned static values to fields for easy testing, after
that I created the parameters and assigned default values to them, now
everytime I click preview I don't get a prompt for new values, the report
automatically runs with the defaults.
I want to be able to use the default values most of the time but also want
the option to change them if needed. The only option is to stop the report
and enter different values. This is strange because I have other reports that
have all default values but give me a prompt in preview.Someone answered this a few ones back.
Add a boolean "Run Report" parameter to the report with no default.
-T
"Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
>I wrote an sp and assigned static values to fields for easy testing, after
> that I created the parameters and assigned default values to them, now
> everytime I click preview I don't get a prompt for new values, the report
> automatically runs with the defaults.
> I want to be able to use the default values most of the time but also want
> the option to change them if needed. The only option is to stop the report
> and enter different values. This is strange because I have other reports
> that
> have all default values but give me a prompt in preview.|||Cool! Thx Tim
"Tim Dot NoSpam" wrote:
> Someone answered this a few ones back.
> Add a boolean "Run Report" parameter to the report with no default.
> -T
> "Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
> news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
> >I wrote an sp and assigned static values to fields for easy testing, after
> > that I created the parameters and assigned default values to them, now
> > everytime I click preview I don't get a prompt for new values, the report
> > automatically runs with the defaults.
> > I want to be able to use the default values most of the time but also want
> > the option to change them if needed. The only option is to stop the report
> > and enter different values. This is strange because I have other reports
> > that
> > have all default values but give me a prompt in preview.
>
>

Force client components using a commmand line installation to diffrent location

Is it possible to force the SLQ 2005 Client Components to be installed into a diffrent location other than the default location?

What I'm trying to do is run the setup command though the command line to create an unattended client installation with all the client componets to a diffrent location that

Thanks, Any information or examples would be great!

There is a way to specify client component location is setup or on the command line but it can only be used for the first time SQL Server 2005 is installed on a machine. Once there is any product or instance of SQL Server 2005 on the machine this location cannot be changed. To specify the location on the command line you can use INSTALLSQLSHAREDDIR.

Note: there are still some components going under Program Files\Microsoft SQL Server\90 which location cannot be changed. But most of the client components is installed to INSTALLSQLSHAREDDIR.

For example:

setup.exe INSTALLSQLSHAREDDIR=D:\SQLTools ADDLOCAL=Client_Components,Connectivity,SQL_Tools90,SQL_WarehouseDevWorkbench /qb

ADDLOCAL lists all features you want to install. See template.ini in the same location as setup.exe for whole list of features.

For Insert/After Insert -- Default Trigger Question

Hello,
I am a little between For Insert and After Insert statements on
Triggers. I just need to know which is the Sql Server Default Trigger. Her
e
are 2 sample Triggers. Is For Insert or After Insert the default?
---
CREATE TRIGGER my_trig
ON my_table
FOR INSERT
AS
IF UPDATE(b)
PRINT 'Column b Modified'
GO
---
Create Trigger Update_Status
On ACRPLU AFTER INSERT
As
Update A Set A.a1updt = 'D'
From ACRPLU A
Where (A.a1updt = '' Or IsNull(A.a1updt, -1) < 0) And A.PK
In(Select Distinct PK From Inserted)
---
Thanks.
RichRich,
From BOL:
AFTER is the default, if FOR is the only keyword specified.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6A02BEF5-E7A3-44DF-B6B3-6C26E336A319@.microsoft.com...
> Hello,
> I am a little between For Insert and After Insert statements on
> Triggers. I just need to know which is the Sql Server Default Trigger.
> Here
> are 2 sample Triggers. Is For Insert or After Insert the default?
> ---
> CREATE TRIGGER my_trig
> ON my_table
> FOR INSERT
> AS
> IF UPDATE(b)
> PRINT 'Column b Modified'
> GO
> ---
> Create Trigger Update_Status
> On ACRPLU AFTER INSERT
> As
> Update A Set A.a1updt = 'D'
> From ACRPLU A
> Where (A.a1updt = '' Or IsNull(A.a1updt, -1) < 0) And A.PK
> In(Select Distinct PK From Inserted)
> ---
> Thanks.
> Rich
>|||AFTER is the default. In other words "AFTER INSERT" means the same as "FOR
INSERT".
David Portas
SQL Server MVP
--
"Rich" wrote:

> Hello,
> I am a little between For Insert and After Insert statements on
> Triggers. I just need to know which is the Sql Server Default Trigger. H
ere
> are 2 sample Triggers. Is For Insert or After Insert the default?
> ---
> CREATE TRIGGER my_trig
> ON my_table
> FOR INSERT
> AS
> IF UPDATE(b)
> PRINT 'Column b Modified'
> GO
> ---
> Create Trigger Update_Status
> On ACRPLU AFTER INSERT
> As
> Update A Set A.a1updt = 'D'
> From ACRPLU A
> Where (A.a1updt = '' Or IsNull(A.a1updt, -1) < 0) And A.PK
> In(Select Distinct PK From Inserted)
> ---
> Thanks.
> Rich
>|||Thanks all for your replies. If I understand the explanations correctly,
what I am interpreting is that
CREATE TRIGGER my_trig
ON my_table
FOR INSERT
...
is the same as
CREATE TRIGGER my_trig
ON my_table
After INSERT
...
Is this correct? This is where my confusion lies.
Thanks,
Rich|||Yes.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E21BD983-4695-4D7C-A9EE-854749B1D70B@.microsoft.com...
> Thanks all for your replies. If I understand the explanations correctly,
> what I am interpreting is that
> CREATE TRIGGER my_trig
> ON my_table
> FOR INSERT
> ...
> is the same as
> CREATE TRIGGER my_trig
> ON my_table
> After INSERT
> ...
> Is this correct? This is where my confusion lies.
> Thanks,
> Rich|||Thanks. But to take this one step further, since For and After seem to be
the same, is it possible to do this?
CREATE TRIGGER my_trig
ON my_table INSERT
...
where I don't include a For or After? Is this what is meant by default
trigger?
"Jerry Spivey" wrote:

> Yes.
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E21BD983-4695-4D7C-A9EE-854749B1D70B@.microsoft.com...
>
>|||No.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:11EBA02F-433D-410A-ABAD-34CAB173E453@.microsoft.com...
> Thanks. But to take this one step further, since For and After seem to be
> the same, is it possible to do this?
> CREATE TRIGGER my_trig
> ON my_table INSERT
> ...
> where I don't include a For or After? Is this what is meant by default
> trigger?
>
> "Jerry Spivey" wrote:
>|||Yes; in earlier versions of SQL Server we only had one kind of trigger, so
we didn't need to say instead of or after. We had a trigger FOR an
operation, and it always fired AFTER the operation took place.
When INSTEAD OF triggers were added, we needed a way to make sure we
differentiated the two kinds of triggers, so AFTER was introduced as a
synonym for FOR.
So really, there is no default. You have to specify when the trigger fires,
either with FOR or AFTER (which are the same) or INSTEAD OF.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E21BD983-4695-4D7C-A9EE-854749B1D70B@.microsoft.com...
> Thanks all for your replies. If I understand the explanations correctly,
> what I am interpreting is that
> CREATE TRIGGER my_trig
> ON my_table
> FOR INSERT
> ...
> is the same as
> CREATE TRIGGER my_trig
> ON my_table
> After INSERT
> ...
> Is this correct? This is where my confusion lies.
> Thanks,
> Rich
>|||Thanks everyone, again for all the explanations. So if I am not using an
Instead Of trigger, then I can write
Create Trigger...For Update...For Insert...For Delete
or
Create Trigger...After Update...After Insert...After Delete
And it is the same thing. I think I get it now.
Many thanks for all the help.
Rich
"Rich" wrote:

> Hello,
> I am a little between For Insert and After Insert statements on
> Triggers. I just need to know which is the Sql Server Default Trigger. H
ere
> are 2 sample Triggers. Is For Insert or After Insert the default?
> ---
> CREATE TRIGGER my_trig
> ON my_table
> FOR INSERT
> AS
> IF UPDATE(b)
> PRINT 'Column b Modified'
> GO
> ---
> Create Trigger Update_Status
> On ACRPLU AFTER INSERT
> As
> Update A Set A.a1updt = 'D'
> From ACRPLU A
> Where (A.a1updt = '' Or IsNull(A.a1updt, -1) < 0) And A.PK
> In(Select Distinct PK From Inserted)
> ---
> Thanks.
> Rich
>

For EXPERTS: System.NullReferenceException: Object reference not .

I have a problem with a default implementation of Reporting Services with SP2
in one box, with SQL 2000 SP4.
The tool works fine, I have 10 Reports working without problems, but when I
want to execute one of them, this error message appears :
aspnet_wp!webserver!294!07/25/2005-16:06:56:: e ERROR: Reporting Services
error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: Error
inesperado al procesar los informes. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error
inesperado al procesar los informes. --> System.NullReferenceException:
Referencia a objeto no establecida como instancia de un objeto.
In English is something like:
System.NullReferenceException: Object reference not set to an
instance of an object.
Iâ'm using a sql function inside a DataSet and I probed it in SQL Analizer
and worked ok ( it took 20 seconds, is heavy) but when I executed the Report,
appeared that message. I changed the report execution timeout from the
Report Administration but still having the error.
If anyone have an idea ... I need help !!
Thanks!!
LEO.
Heres is my log:
aspnet_wp!processing!3a4!07/28/2005-12:48:29:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error
inesperado al procesar los informes., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error
inesperado al procesar los informes. --> System.NullReferenceException:
Referencia a objeto no establecida como instancia de un objeto.
at Microsoft.ReportingServices.ReportProcessing.ReportItemInstance.b(d A_0)
at Microsoft.ReportingServices.ReportProcessing.o.a(ReportItemInstance
A_0, Boolean A_1)
at
Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
A_0, Boolean A_1)
at
Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
A_0, Boolean A_1)
at
Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
A_0, Boolean A_1)
at Microsoft.ReportingServices.ReportProcessing.o.a(ReportItemInstance
A_0, IList A_1, RenderingPagesRangesList A_2)
at Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItem A_0,
Boolean A_1, Int32 A_2)
at
Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance A_0,
ReportItemCollection A_1)
at
Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance A_0)
at Microsoft.ReportingServices.ReportProcessing.a0.b()
at Microsoft.ReportingServices.ReportProcessing.c.c(ProcessingStages A_0)
at Microsoft.ReportingServices.ReportProcessing.c.d(ProcessingStages A_0)
at Microsoft.ReportingServices.ReportProcessing.ac.a(ProcessingStages
A_0, Boolean A_1)
at Microsoft.ReportingServices.ReportProcessing.a4.a(ReportItemInstance
A_0, IList A_1, RenderingPagesRangesList A_2)
at Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItem A_0,
Boolean A_1, Int32 A_2)
at
Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance A_0,
ReportItemCollection A_1)
at
Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance A_0)
at
Microsoft.ReportingServices.ReportProcessing.bb.a(ParameterInfoCollection
A_0, Boolean A_1)
at
Microsoft.ReportingServices.ReportProcessing.bb.b(ParameterInfoCollection
A_0, Boolean A_1)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, az A_4, DateTime
A_5, CreateReportChunk A_6, ab& A_7)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0,
GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3,
CreateReportChunk A_4, Boolean& A_5)
-- Fin del seguimiento de la pila de la excepción interna --This sounds like a bug. If you need further assistance, I suggest
contacting Microsoft Product Support. If it is indeed a bug and not user
error, any fees will be refunded.
--
| Thread-Topic: For EXPERTS: System.NullReferenceException: Object
reference not .
| thread-index: AcWTjhy6HiRzY1eyQ9OOA8fay/B5TA==| X-WBNR-Posting-Host: 24.232.107.196
| From: =?Utf-8?B?TGVv?= <Leo@.discussions.microsoft.com>
| Subject: For EXPERTS: System.NullReferenceException: Object reference not|||For the one report that has this problem - can you try to republish the RDL
of that report under a new name and check if the report runs then?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:4306B254-7122-4B67-851B-39331212141C@.microsoft.com...
>I have a problem with a default implementation of Reporting Services with
>SP2
> in one box, with SQL 2000 SP4.
> The tool works fine, I have 10 Reports working without problems, but when
> I
> want to execute one of them, this error message appears :
> aspnet_wp!webserver!294!07/25/2005-16:06:56:: e ERROR: Reporting Services
> error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: Error
> inesperado al procesar los informes. -->
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> Error
> inesperado al procesar los informes. --> System.NullReferenceException:
> Referencia a objeto no establecida como instancia de un objeto.
>
> In English is something like:
> System.NullReferenceException: Object reference not set to an
> instance of an object.
>
> I'm using a sql function inside a DataSet and I probed it in SQL Analizer
> and worked ok ( it took 20 seconds, is heavy) but when I executed the
> Report,
> appeared that message. I changed the report execution timeout from the
> Report Administration but still having the error.
> If anyone have an idea ... I need help !!
> Thanks!!
> LEO.
> Heres is my log:
> aspnet_wp!processing!3a4!07/28/2005-12:48:29:: e ERROR: Throwing
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> Error
> inesperado al procesar los informes., ;
> Info:
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> Error
> inesperado al procesar los informes. --> System.NullReferenceException:
> Referencia a objeto no establecida como instancia de un objeto.
> at Microsoft.ReportingServices.ReportProcessing.ReportItemInstance.b(d
> A_0)
> at Microsoft.ReportingServices.ReportProcessing.o.a(ReportItemInstance
> A_0, Boolean A_1)
> at
> Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
> A_0, Boolean A_1)
> at
> Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
> A_0, Boolean A_1)
> at
> Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
> A_0, Boolean A_1)
> at Microsoft.ReportingServices.ReportProcessing.o.a(ReportItemInstance
> A_0, IList A_1, RenderingPagesRangesList A_2)
> at Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItem A_0,
> Boolean A_1, Int32 A_2)
> at
> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
> A_0,
> ReportItemCollection A_1)
> at
> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
> A_0)
> at Microsoft.ReportingServices.ReportProcessing.a0.b()
> at Microsoft.ReportingServices.ReportProcessing.c.c(ProcessingStages
> A_0)
> at Microsoft.ReportingServices.ReportProcessing.c.d(ProcessingStages
> A_0)
> at Microsoft.ReportingServices.ReportProcessing.ac.a(ProcessingStages
> A_0, Boolean A_1)
> at Microsoft.ReportingServices.ReportProcessing.a4.a(ReportItemInstance
> A_0, IList A_1, RenderingPagesRangesList A_2)
> at Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItem A_0,
> Boolean A_1, Int32 A_2)
> at
> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
> A_0,
> ReportItemCollection A_1)
> at
> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
> A_0)
> at
> Microsoft.ReportingServices.ReportProcessing.bb.a(ParameterInfoCollection
> A_0, Boolean A_1)
> at
> Microsoft.ReportingServices.ReportProcessing.bb.b(ParameterInfoCollection
> A_0, Boolean A_1)
> at
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
> A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, az A_4,
> DateTime
> A_5, CreateReportChunk A_6, ab& A_7)
> at
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime
> A_0,
> GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3,
> CreateReportChunk A_4, Boolean& A_5)
> -- Fin del seguimiento de la pila de la excepción interna --
>|||BTW, is this a report server that was upgraded from RS 2000 Beta 2, to RS
2000 RTM, and then to RS 2000 SP2?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e55j%23%23%23kFHA.3260@.TK2MSFTNGP10.phx.gbl...
> For the one report that has this problem - can you try to republish the
> RDL of that report under a new name and check if the report runs then?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:4306B254-7122-4B67-851B-39331212141C@.microsoft.com...
>>I have a problem with a default implementation of Reporting Services with
>>SP2
>> in one box, with SQL 2000 SP4.
>> The tool works fine, I have 10 Reports working without problems, but when
>> I
>> want to execute one of them, this error message appears :
>> aspnet_wp!webserver!294!07/25/2005-16:06:56:: e ERROR: Reporting Services
>> error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
>> Error
>> inesperado al procesar los informes. -->
>> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
>> Error
>> inesperado al procesar los informes. --> System.NullReferenceException:
>> Referencia a objeto no establecida como instancia de un objeto.
>>
>> In English is something like:
>> System.NullReferenceException: Object reference not set to an
>> instance of an object.
>>
>> I'm using a sql function inside a DataSet and I probed it in SQL Analizer
>> and worked ok ( it took 20 seconds, is heavy) but when I executed the
>> Report,
>> appeared that message. I changed the report execution timeout from the
>> Report Administration but still having the error.
>> If anyone have an idea ... I need help !!
>> Thanks!!
>> LEO.
>> Heres is my log:
>> aspnet_wp!processing!3a4!07/28/2005-12:48:29:: e ERROR: Throwing
>> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
>> Error
>> inesperado al procesar los informes., ;
>> Info:
>> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
>> Error
>> inesperado al procesar los informes. --> System.NullReferenceException:
>> Referencia a objeto no establecida como instancia de un objeto.
>> at Microsoft.ReportingServices.ReportProcessing.ReportItemInstance.b(d
>> A_0)
>> at Microsoft.ReportingServices.ReportProcessing.o.a(ReportItemInstance
>> A_0, Boolean A_1)
>> at
>> Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
>> A_0, Boolean A_1)
>> at
>> Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
>> A_0, Boolean A_1)
>> at
>> Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
>> A_0, Boolean A_1)
>> at Microsoft.ReportingServices.ReportProcessing.o.a(ReportItemInstance
>> A_0, IList A_1, RenderingPagesRangesList A_2)
>> at Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItem A_0,
>> Boolean A_1, Int32 A_2)
>> at
>> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
>> A_0,
>> ReportItemCollection A_1)
>> at
>> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
>> A_0)
>> at Microsoft.ReportingServices.ReportProcessing.a0.b()
>> at Microsoft.ReportingServices.ReportProcessing.c.c(ProcessingStages
>> A_0)
>> at Microsoft.ReportingServices.ReportProcessing.c.d(ProcessingStages
>> A_0)
>> at Microsoft.ReportingServices.ReportProcessing.ac.a(ProcessingStages
>> A_0, Boolean A_1)
>> at Microsoft.ReportingServices.ReportProcessing.a4.a(ReportItemInstance
>> A_0, IList A_1, RenderingPagesRangesList A_2)
>> at Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItem A_0,
>> Boolean A_1, Int32 A_2)
>> at
>> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
>> A_0,
>> ReportItemCollection A_1)
>> at
>> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
>> A_0)
>> at
>> Microsoft.ReportingServices.ReportProcessing.bb.a(ParameterInfoCollection
>> A_0, Boolean A_1)
>> at
>> Microsoft.ReportingServices.ReportProcessing.bb.b(ParameterInfoCollection
>> A_0, Boolean A_1)
>> at
>> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
>> A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, az A_4,
>> DateTime
>> A_5, CreateReportChunk A_6, ab& A_7)
>> at
>> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime
>> A_0,
>> GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3,
>> CreateReportChunk A_4, Boolean& A_5)
>> -- Fin del seguimiento de la pila de la excepción interna --
>>
>|||Hi Robert. Thanks for your response.
I just republish the same report with a different name but still showing the
error.
RS was upgraded from RS2000 (8.00.743) directly to RS2000 SP2 (8.00.1042)
Its difficult to analize, because the sql function executes normally in SQL
Analizer (takes 18 seconds aprox), but RS shows that error.
Otherwise, the report executes ok with some parameters, but don't work if
those parameters mean to process lot of SQL registers (f.e: <All the tickets>
to process)
Is there a bug that don't support certain number of registers to process? I
don't think so ... but .....
Thanks again.
Leo.
"Robert Bruckner [MSFT]" wrote:
> BTW, is this a report server that was upgraded from RS 2000 Beta 2, to RS
> 2000 RTM, and then to RS 2000 SP2?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:e55j%23%23%23kFHA.3260@.TK2MSFTNGP10.phx.gbl...
> > For the one report that has this problem - can you try to republish the
> > RDL of that report under a new name and check if the report runs then?
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "Leo" <Leo@.discussions.microsoft.com> wrote in message
> > news:4306B254-7122-4B67-851B-39331212141C@.microsoft.com...
> >>I have a problem with a default implementation of Reporting Services with
> >>SP2
> >> in one box, with SQL 2000 SP4.
> >>
> >> The tool works fine, I have 10 Reports working without problems, but when
> >> I
> >> want to execute one of them, this error message appears :
> >>
> >> aspnet_wp!webserver!294!07/25/2005-16:06:56:: e ERROR: Reporting Services
> >> error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
> >> Error
> >> inesperado al procesar los informes. -->
> >> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> >> Error
> >> inesperado al procesar los informes. --> System.NullReferenceException:
> >> Referencia a objeto no establecida como instancia de un objeto.
> >>
> >>
> >> In English is something like:
> >>
> >> System.NullReferenceException: Object reference not set to an
> >> instance of an object.
> >>
> >>
> >> I'm using a sql function inside a DataSet and I probed it in SQL Analizer
> >> and worked ok ( it took 20 seconds, is heavy) but when I executed the
> >> Report,
> >> appeared that message. I changed the report execution timeout from the
> >> Report Administration but still having the error.
> >>
> >> If anyone have an idea ... I need help !!
> >> Thanks!!
> >> LEO.
> >>
> >> Heres is my log:
> >>
> >> aspnet_wp!processing!3a4!07/28/2005-12:48:29:: e ERROR: Throwing
> >> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> >> Error
> >> inesperado al procesar los informes., ;
> >> Info:
> >> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> >> Error
> >> inesperado al procesar los informes. --> System.NullReferenceException:
> >> Referencia a objeto no establecida como instancia de un objeto.
> >> at Microsoft.ReportingServices.ReportProcessing.ReportItemInstance.b(d
> >> A_0)
> >> at Microsoft.ReportingServices.ReportProcessing.o.a(ReportItemInstance
> >> A_0, Boolean A_1)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
> >> A_0, Boolean A_1)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
> >> A_0, Boolean A_1)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.o.a(MatrixHeadingInstanceList
> >> A_0, Boolean A_1)
> >> at Microsoft.ReportingServices.ReportProcessing.o.a(ReportItemInstance
> >> A_0, IList A_1, RenderingPagesRangesList A_2)
> >> at Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItem A_0,
> >> Boolean A_1, Int32 A_2)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
> >> A_0,
> >> ReportItemCollection A_1)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
> >> A_0)
> >> at Microsoft.ReportingServices.ReportProcessing.a0.b()
> >> at Microsoft.ReportingServices.ReportProcessing.c.c(ProcessingStages
> >> A_0)
> >> at Microsoft.ReportingServices.ReportProcessing.c.d(ProcessingStages
> >> A_0)
> >> at Microsoft.ReportingServices.ReportProcessing.ac.a(ProcessingStages
> >> A_0, Boolean A_1)
> >> at Microsoft.ReportingServices.ReportProcessing.a4.a(ReportItemInstance
> >> A_0, IList A_1, RenderingPagesRangesList A_2)
> >> at Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItem A_0,
> >> Boolean A_1, Int32 A_2)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
> >> A_0,
> >> ReportItemCollection A_1)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.a9.a(ReportItemColInstance
> >> A_0)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.bb.a(ParameterInfoCollection
> >> A_0, Boolean A_1)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.bb.b(ParameterInfoCollection
> >> A_0, Boolean A_1)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
> >> A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, az A_4,
> >> DateTime
> >> A_5, CreateReportChunk A_6, ab& A_7)
> >> at
> >> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime
> >> A_0,
> >> GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3,
> >> CreateReportChunk A_4, Boolean& A_5)
> >> -- Fin del seguimiento de la pila de la excepción interna --
> >>
> >>
> >
> >
>
>