Generating Script

What does N indicate in the following script

SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].xxxx' )

Plz can anyone explain

EXEC dbo.sp_executesql @.statement = N'Create View xxxxx'

The 'N' in the likes of N'[dbo].xxxx' and N'Create View xxxxx' indicates an NVARCHAR datatype. This is the datatype that is used for representing UNICODE strings. The NVARCHAR datatype uses two bytes to represent each character whereas the VARCHAR datatype uses one byte to represent each character.

Also, if you are asking for an explanation of the statement:

Code Snippet

EXEC dbo.sp_executesql @.statement = N'Create View xxxxx'

The "sp_executesql" stored procedure is a stored procedure that is used for executing dynamically created SQL statements. In this case the dynamic SQL statement is the "CREATE VIEW ... " piece. And in this case this statement is used to create a view.

|||Thank you...i have also seen from the MSDN help that N denotes unicode strings which uses 2 bytes to represent each character|||That is correct. Nvarchar/Nchar will store UNICODE values.

Generating an event from an Activation Stored Proc

I am trying to raise an event using sp_trace_generateevent in my activation stored proc. (dbo.ActivationSP)

EXEC sp_trace_generateevent @.event_class = 82, @.userinfo = N'Test Event'

There is a Service broker service listening to this event.

The problem is the event is getting fired whenever the activation SP is executed (could see in profiler) but the secondtargetqueue doesnt receive any messaages.

But if manually do a "EXEC sp_trace_generateevent", the secondtargetqueue receives a messaage.

Both the queues and sevices are in the same database.

The following are the code snippets

-- Code for queue on which the activation is working

CREATE QUEUE TargetQueue WITH STATUS=ON, ACTIVATION (PROCEDURE_NAME = dbo.ActivationSP,MAX_QUEUE_READERS = 5,Execute AS 'dbo') ;

Create Service ReceiverService ON QUEUE TargetQueue (SampleContract)

-- Code for Queue listening to event

Create Queue SecondTargetQueue WITH status= ON

Create Service SecondReceiverService ON QUEUE SecondTargetQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

CREATE EVENT NOTIFICATION TestNotification

ON SERVER FOR UserConfigurable_0 TO SERVICE 'SecondReceiverService', 'current database'

Due to a product defect some profiler events are suppresed under activation. A possible workaround is to use a CLR activated procedure that connects back to the server (using ADO.Net and a real connection, not the context connection) to execute the code you need.

HTH,
~ Remus

|||

Thanks Remus

Can you help me with some example links?

Generating an event from an Activation Stored Proc

I am trying to raise an event using sp_trace_generateevent in my activation stored proc. (dbo.ActivationSP)

EXEC sp_trace_generateevent @.event_class = 82, @.userinfo = N'Test Event'

There is a Service broker service listening to this event.

The problem is the event is getting fired whenever the activation SP is executed (could see in profiler) but the secondtargetqueue doesnt receive any messaages.

But if manually do a "EXEC sp_trace_generateevent", the secondtargetqueue receives a messaage.

Both the queues and sevices are in the same database.

The following are the code snippets

-- Code for queue on which the activation is working

CREATE QUEUE TargetQueue WITH STATUS=ON, ACTIVATION (PROCEDURE_NAME = dbo.ActivationSP,MAX_QUEUE_READERS = 5,Execute AS 'dbo') ;

Create Service ReceiverService ON QUEUE TargetQueue (SampleContract)

-- Code for Queue listening to event

Create Queue SecondTargetQueue WITH status= ON

Create Service SecondReceiverService ON QUEUE SecondTargetQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

CREATE EVENT NOTIFICATION TestNotification

ON SERVER FOR UserConfigurable_0 TO SERVICE 'SecondReceiverService', 'current database'

Due to a product defect some profiler events are suppresed under activation. A possible workaround is to use a CLR activated procedure that connects back to the server (using ADO.Net and a real connection, not the context connection) to execute the code you need.

HTH,
~ Remus

|||

Thanks Remus

Can you help me with some example links?

Generate Scripts Problem.

Hi all,
I am using the generate Script feature of SQL Server 2005 Express.
The result is:
CREATE TABLE [dbo].[asmt_v2_areas](
[asmt_v2_area_id] [int] IDENTITY(1,1) NOT NULL,
[asmt_v2_mine_id] [int] NULL,
[name] [varchar](60) NOT NULL,
[parent_id] [int] NULL,
[optional_questions] [tinyint] NULL,
[send_updates] [tinyint] NULL,
CONSTRAINT [PK_asmt_v2_areas] PRIMARY KEY CLUSTERED
(
[asmt_v2_area_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] //error here
) ON [PRIMARY]
END
However, when i try an run these scripts without modification, i keep
getting the error 'Syntax error near ')' on the line above.
I am exporting to 2000 servers, could this be the problem?
Any thoughts on how to fix?
Cheers,
AdamThere are some elements in the generated script which are new to 2005. I don
't know exactly what
tool you use to generate script, but perhaps that tool has some option to ge
nerate it with 2000
compatibility.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message news:O1PtNWNPGHA.3924@.TK2MSFTNGP14.p
hx.gbl...
> Hi all,
> I am using the generate Script feature of SQL Server 2005 Express.
> The result is:
> CREATE TABLE [dbo].[asmt_v2_areas](
> [asmt_v2_area_id] [int] IDENTITY(1,1) NOT NULL,
> [asmt_v2_mine_id] [int] NULL,
> [name] [varchar](60) NOT NULL,
> [parent_id] [int] NULL,
> [optional_questions] [tinyint] NULL,
> [send_updates] [tinyint] NULL,
> CONSTRAINT [PK_asmt_v2_areas] PRIMARY KEY CLUSTERED
> (
> [asmt_v2_area_id] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] //error here
> ) ON [PRIMARY]
> END
> However, when i try an run these scripts without modification, i keep gett
ing the error 'Syntax
> error near ')' on the line above.
> I am exporting to 2000 servers, could this be the problem?
> Any thoughts on how to fix?
> Cheers,
> Adam
>
>|||Hi Tibor,
Yeah, I am using SQL Server 2005 and selecting from the relevant dialog
'export as SqlServer 2000' but the error persists.
Bit of a tricky one.
Cheers,
Adam
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Obvu2bPPGHA.3408@.TK2MSFTNGP12.phx.gbl...
> There are some elements in the generated script which are new to 2005. I
> don't know exactly what tool you use to generate script, but perhaps that
> tool has some option to generate it with 2000 compatibility.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
> news:O1PtNWNPGHA.3924@.TK2MSFTNGP14.phx.gbl...
>|||What tool are you using? SQL Server Management Studio? You mentioned Express
, and I'm a bit thrown
off since that doesn't come with any GUI tool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:%23HEEkfPPGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Yeah, I am using SQL Server 2005 and selecting from the relevant dialog 'e
xport as SqlServer 2000'
> but the error persists.
> Bit of a tricky one.
> Cheers,
> Adam
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Obvu2bPPGHA.3408@.TK2MSFTNGP12.phx.gbl...
>