Getting SCHEMA name from sys views

Hello,
I am trying to write a query that will generate the script to drop a
constraint. The problem is that I need to get the SCHEMA name from one of
the system views to join with the query below.
This query produces the column schema_id
SELECT * FROM sys.tables
Any help would be appreciated.
sck10
SELECT
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
WHERE (Tsyso.type = 'D')Thanks, but I think I figured it out:
SELECT
Tschema.name AS 'Schema Name',
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
INNER JOIN sys.schemas Tschema
ON Tsyst.schema_id = Tschema.schema_id
WHERE (Tsyso.type = 'D')
"sck10" <sck10@.online.nospam> wrote in message
news:eWQDGA$QHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am trying to write a query that will generate the script to drop a
> constraint. The problem is that I need to get the SCHEMA name from one of
> the system views to join with the query below.
> This query produces the column schema_id
> SELECT * FROM sys.tables
> Any help would be appreciated.
> sck10
> SELECT
> Tsyst.schema_id,
> Tsyst.name AS 'TableName',
> Tsyst.object_id AS 'ObjectID',
> Tsyso.name AS 'DefaultName',
> Tsyso.type_desc AS 'TypeDscr',
> 'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
> FROM
> sys.tables Tsyst
> INNER JOIN sys.objects Tsyso
> ON Tsyst.object_id = Tsyso.parent_object_id
> WHERE (Tsyso.type = 'D')
>

Getting SCHEMA name from sys views

Hello,
I am trying to write a query that will generate the script to drop a
constraint. The problem is that I need to get the SCHEMA name from one of
the system views to join with the query below.
This query produces the column schema_id
SELECT * FROM sys.tables
Any help would be appreciated.
sck10
SELECT
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
WHERE (Tsyso.type = 'D')Thanks, but I think I figured it out:
SELECT
Tschema.name AS 'Schema Name',
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
INNER JOIN sys.schemas Tschema
ON Tsyst.schema_id = Tschema.schema_id
WHERE (Tsyso.type = 'D')
"sck10" <sck10@.online.nospam> wrote in message
news:eWQDGA$QHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am trying to write a query that will generate the script to drop a
> constraint. The problem is that I need to get the SCHEMA name from one of
> the system views to join with the query below.
> This query produces the column schema_id
> SELECT * FROM sys.tables
> Any help would be appreciated.
> sck10
> SELECT
> Tsyst.schema_id,
> Tsyst.name AS 'TableName',
> Tsyst.object_id AS 'ObjectID',
> Tsyso.name AS 'DefaultName',
> Tsyso.type_desc AS 'TypeDscr',
> 'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
> FROM
> sys.tables Tsyst
> INNER JOIN sys.objects Tsyso
> ON Tsyst.object_id = Tsyso.parent_object_id
> WHERE (Tsyso.type = 'D')
>

Getting SCHEMA name from sys views

Hello,
I am trying to write a query that will generate the script to drop a
constraint. The problem is that I need to get the SCHEMA name from one of
the system views to join with the query below.
This query produces the column schema_id
SELECT * FROM sys.tables
Any help would be appreciated.
sck10
SELECT
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
WHERE (Tsyso.type = 'D')
Thanks, but I think I figured it out:
SELECT
Tschema.name AS 'Schema Name',
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
INNER JOIN sys.schemas Tschema
ON Tsyst.schema_id = Tschema.schema_id
WHERE (Tsyso.type = 'D')
"sck10" <sck10@.online.nospam> wrote in message
news:eWQDGA$QHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am trying to write a query that will generate the script to drop a
> constraint. The problem is that I need to get the SCHEMA name from one of
> the system views to join with the query below.
> This query produces the column schema_id
> SELECT * FROM sys.tables
> Any help would be appreciated.
> sck10
> SELECT
> Tsyst.schema_id,
> Tsyst.name AS 'TableName',
> Tsyst.object_id AS 'ObjectID',
> Tsyso.name AS 'DefaultName',
> Tsyso.type_desc AS 'TypeDscr',
> 'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
> FROM
> sys.tables Tsyst
> INNER JOIN sys.objects Tsyso
> ON Tsyst.object_id = Tsyso.parent_object_id
> WHERE (Tsyso.type = 'D')
>

Getting rid of deleted subscriptions in EM

We have a T-SQL script that enables replication on a server, creates a publication on that server, and pushes a subscription. We also have a script that deletes the subscription and publication and disables replication. The problem is that, in EM, when
the deletion script is run, there is still information in Replication > Subscriptions. How can we get rid of this subscription information so that it doesn't display?
Stephanie,
if you delete the subscription before deleting the publication usually these
entries are removed - however, this is not always the case. Where you want
to keep other replication settings (eg the subscriber is also a publisher)
you need to locate the subscription information and remove it manually, but
in your case you are removing replication settings generally so you can
remove the remaining metadata tables using sp_removedbreplication.
HTH,
Paul Ibison
|||Thanks for the info. sp_removedbreplication did clean up the subscription information in EM. If I needed to keep other replication settings, where can I locate this subscription information to delete it manually?
|||Stephanie,
you can have a look at sp_MSenumsubscriptions to see what is happening when
you click on the subscriptions folder. The key tables in each subscribing
databases are sysmergesubscriptions and MSreplication_subscriptions.
Regards,
Paul Ibison

getting previous versions of a stored procedure

Hello All,
We made a stupid mistake.We had overwritten some of our stored procedures
accidentallyfrom an older script(dropped and recreated).Since this was a new
database there are no back ups.
This sounds extremely unlikely but is there a way to get the older
procedures back.
If so please let me know of a way to do so.
Thank You
Only if you had them in source safe or something similar.
Andrew J. Kelly SQL MVP
"Pradeep Sundaram" <PradeepSundaram@.discussions.microsoft.com> wrote in
message news:47F990BB-4AA4-49F3-A23C-A143E6DA90B5@.microsoft.com...
> Hello All,
> We made a stupid mistake.We had overwritten some of our stored procedures
> accidentallyfrom an older script(dropped and recreated).Since this was a
> new
> database there are no back ups.
> This sounds extremely unlikely but is there a way to get the older
> procedures back.
> If so please let me know of a way to do so.
> Thank You

getting previous versions of a stored procedure

Hello All,
We made a stupid mistake.We had overwritten some of our stored procedures
accidentallyfrom an older script(dropped and recreated).Since this was a new
database there are no back ups.
This sounds extremely unlikely but is there a way to get the older
procedures back.
If so please let me know of a way to do so.
Thank YouOnly if you had them in source safe or something similar.
Andrew J. Kelly SQL MVP
"Pradeep Sundaram" <PradeepSundaram@.discussions.microsoft.com> wrote in
message news:47F990BB-4AA4-49F3-A23C-A143E6DA90B5@.microsoft.com...
> Hello All,
> We made a stupid mistake.We had overwritten some of our stored procedures
> accidentallyfrom an older script(dropped and recreated).Since this was a
> new
> database there are no back ups.
> This sounds extremely unlikely but is there a way to get the older
> procedures back.
> If so please let me know of a way to do so.
> Thank You

getting previous versions of a stored procedure

Hello All,
We made a stupid mistake.We had overwritten some of our stored procedures
accidentallyfrom an older script(dropped and recreated).Since this was a new
database there are no back ups.
This sounds extremely unlikely but is there a way to get the older
procedures back.
If so please let me know of a way to do so.
Thank YouOnly if you had them in source safe or something similar.
--
Andrew J. Kelly SQL MVP
"Pradeep Sundaram" <PradeepSundaram@.discussions.microsoft.com> wrote in
message news:47F990BB-4AA4-49F3-A23C-A143E6DA90B5@.microsoft.com...
> Hello All,
> We made a stupid mistake.We had overwritten some of our stored procedures
> accidentallyfrom an older script(dropped and recreated).Since this was a
> new
> database there are no back ups.
> This sounds extremely unlikely but is there a way to get the older
> procedures back.
> If so please let me know of a way to do so.
> Thank You

Getting Previous Date SQL Script

Hello,

I have a table that also has a timestamp field I want to get any data that was inserted into that table on the previous day even if the month changes and i have no clue how to go about that

Tdar

Is your field actually a "timestamp" or a "datetime" field?|||

If your "timestamp" is actually a "datetime" field you can do:

select *
from yourTable
where dateTimeColumn < cast ( floor(cast(getdate() as float)) as datetime)
and dateTimeColumn >= cast ( floor(cast(getdate() as float)) - 1 as datetime)

or also

select *
from yourTable
where dateTimeColumn < convert(datetime, convert (varchar(10), getdate(), 101))
and dateTimeColumn >= convert(datetime, convert (varchar(10), getdate(), 101)) - 1

|||

SELECT * FROM yourDatesTable WHERE DATEDIFF(day, yourDateColumn, getdate())=1

|||Waldrop's is the better option here, because it doesn't need to apply a function to yourDateColumn before doing the comparison. This means that it can use indexes effectively.

Rob|||

limno wrote:

SELECT * FROM yourDatesTable WHERE DATEDIFF(day, yourDateColumn, getdate())=1

Another approach:
SELECT * FROM yourDatesTable WHERE yourDateColumn>DATEADD(day,-2,getdate()) AND yourDateColumn<=DATEADD(day,-1,getdate())

Getting osql to take a script in codepage 1252

I have a table that stores ISO-8859-1 data. The default collation for my
database is SQL_Latin1_General_CP1_CI_AS. I have a script that I pass the
"osql" that populates the table with default data. The script works for
normal ASCII character data, but not for the full ISO-8859-1 character set.
If I could specify that the script was in codepage 1252, all would be well.
The script is as follows:
-- drop any previous version of the table
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Salutations' AND type =
'U')
DROP TABLE Salutations
GO
-- create the new table
CREATE TABLE Salutations (
Salutation VARCHAR (10) PRIMARY KEY NOT NULL,
Pronunciation VARCHAR (20) NOT NULL,
Pattern VARCHAR (100) NOT NULL,
UIRepresentation VARCHAR (20) NULL,
UISortOrder INTEGER NULL
)
GO
-- populate the table
-- Salutation Pronunciation Pattern
UIRepresentation UISortOrder
INSERT INTO Salutations VALUES ('mr', 'mister', '^mr\.? +',
'Mr.', 1)
INSERT INTO Salutations VALUES ('mister', 'mister', '^mister +',
NULL, NULL)
INSERT INTO Salutations VALUES ('mrs', 'missus', '^mrs\.? +',
'Mrs.', 2)
INSERT INTO Salutations VALUES ('ms', 'miz', '^ms\.? +',
'Ms.', 3)
INSERT INTO Salutations VALUES ('miss', 'miss', '^miss +',
'Miss', 4)
INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
'Dr.', 5)
INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.? +',
'Prof.', 6)
INSERT INTO Salutations VALUES ('professor', 'professor', '^professor
+', NULL, NULL)
-- Spanish salutations
INSERT INTO Salutations VALUES ('sr', 'seor', '^sr\.? +',
'Sr.', 7)
INSERT INTO Salutations VALUES ('seor', 'seor', '^seor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('senor', 'seor', '^senor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('sra', 'seora', '^sra\.? +',
'Sra.', 8)
INSERT INTO Salutations VALUES ('seora', 'seora', '^seora +',
NULL, NULL)
INSERT INTO Salutations VALUES ('senora', 'seora', '^senora +',
NULL, NULL)
INSERT INTO Salutations VALUES ('srta', 'seorita', '^srta\.? +',
'Srta.', 9)
INSERT INTO Salutations VALUES ('seorita', 'seorita', '^seorita +',
NULL, NULL)
INSERT INTO Salutations VALUES ('senorita', 'seorita', '^senorita +',
NULL, NULL)
INSERT INTO Salutations VALUES ('profesor', 'profesor', '^profesor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('profa', 'profesora', '^profa\.? +',
'Profa.', 10)
INSERT INTO Salutations VALUES ('profesora', 'profesora', '^profesora
+', NULL, NULL)
INSERT INTO Salutations VALUES ('dra', 'doctora', '^dra\.? +',
'Dra.', 11)
INSERT INTO Salutations VALUES ('doctora', 'doctora', '^doctora +',
NULL, NULL)
-- the same as in English
--INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
'Dr.', 5)
--INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
NULL, NULL)
--INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.?
+', 'Prof.', 6)
GO
When I run the script, all of the entries that use the character (ANSI
character 0xE1) end up being stored using the character (ANSI character
0xB1). Note that if I copy and paste the script into Query Analyzer and run
it from there, it runs as expected.
I've tried creating an ODBC datasource with the "Perform translation for
character data" option unchecked and using that data source via the -D
command line switch to osql, but it made no difference. I am running against
SQL Server 2000 SP3a.
What else can I try?
Rick
rgenter "at" silverlink.com
save the file as OEM in Query Analyzer.
regards,
Mark Baekdal
www.dbghost.com
Living and breathing database change management for SQL Server
"Rick Genter" wrote:

> I have a table that stores ISO-8859-1 data. The default collation for my
> database is SQL_Latin1_General_CP1_CI_AS. I have a script that I pass the
> "osql" that populates the table with default data. The script works for
> normal ASCII character data, but not for the full ISO-8859-1 character set.
> If I could specify that the script was in codepage 1252, all would be well.
> The script is as follows:
> -- drop any previous version of the table
> IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Salutations' AND type =
> 'U')
> DROP TABLE Salutations
> GO
>
> -- create the new table
> CREATE TABLE Salutations (
> Salutation VARCHAR (10) PRIMARY KEY NOT NULL,
> Pronunciation VARCHAR (20) NOT NULL,
> Pattern VARCHAR (100) NOT NULL,
> UIRepresentation VARCHAR (20) NULL,
> UISortOrder INTEGER NULL
> )
> GO
>
> -- populate the table
> -- Salutation Pronunciation Pattern
> UIRepresentation UISortOrder
> INSERT INTO Salutations VALUES ('mr', 'mister', '^mr\.? +',
> 'Mr.', 1)
> INSERT INTO Salutations VALUES ('mister', 'mister', '^mister +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('mrs', 'missus', '^mrs\.? +',
> 'Mrs.', 2)
> INSERT INTO Salutations VALUES ('ms', 'miz', '^ms\.? +',
> 'Ms.', 3)
> INSERT INTO Salutations VALUES ('miss', 'miss', '^miss +',
> 'Miss', 4)
> INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
> 'Dr.', 5)
> INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.? +',
> 'Prof.', 6)
> INSERT INTO Salutations VALUES ('professor', 'professor', '^professor
> +', NULL, NULL)
> -- Spanish salutations
> INSERT INTO Salutations VALUES ('sr', 'se?or', '^sr\.? +',
> 'Sr.', 7)
> INSERT INTO Salutations VALUES ('se?or', 'se?or', '^se?or +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('senor', 'se?or', '^senor +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('sra', 'se?ora', '^sra\.? +',
> 'Sra.', 8)
> INSERT INTO Salutations VALUES ('se?ora', 'se?ora', '^se?ora +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('senora', 'se?ora', '^senora +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('srta', 'se?orita', '^srta\.? +',
> 'Srta.', 9)
> INSERT INTO Salutations VALUES ('se?orita', 'se?orita', '^se?orita +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('senorita', 'se?orita', '^senorita +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('profesor', 'profesor', '^profesor +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('profa', 'profesora', '^profa\.? +',
> 'Profa.', 10)
> INSERT INTO Salutations VALUES ('profesora', 'profesora', '^profesora
> +', NULL, NULL)
> INSERT INTO Salutations VALUES ('dra', 'doctora', '^dra\.? +',
> 'Dra.', 11)
> INSERT INTO Salutations VALUES ('doctora', 'doctora', '^doctora +',
> NULL, NULL)
> -- the same as in English
> --INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
> 'Dr.', 5)
> --INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
> NULL, NULL)
> --INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.?
> +', 'Prof.', 6)
> GO
>
> When I run the script, all of the entries that use the ? character (ANSI
> character 0xE1) end up being stored using the character ± (ANSI character
> 0xB1). Note that if I copy and paste the script into Query Analyzer and run
> it from there, it runs as expected.
> I've tried creating an ODBC datasource with the "Perform translation for
> character data" option unchecked and using that data source via the -D
> command line switch to osql, but it made no difference. I am running against
> SQL Server 2000 SP3a.
> What else can I try?
> Rick
> --
> rgenter "at" silverlink.com
>
>
|||Mark,
Thanks. That did the trick.
Rick
rgenter "at" silverlink.com
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:3CEC828C-E8EB-41C8-99DE-705C52B62ED1@.microsoft.com...[vbcol=seagreen]
> save the file as OEM in Query Analyzer.
> regards,
> Mark Baekdal
> www.dbghost.com
> Living and breathing database change management for SQL Server
> "Rick Genter" wrote:
my[vbcol=seagreen]
the[vbcol=seagreen]
set.[vbcol=seagreen]
well.[vbcol=seagreen]
type =[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
'^professor[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
'^profesora[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
'^prof\.?[vbcol=seagreen]
character[vbcol=seagreen]
run[vbcol=seagreen]
against[vbcol=seagreen]

Getting OLE DB connection properties in script

I have SSIS packages that send success/failure email upon completion, and I'd like to add a note that identifies the server and database used. I can certainly add variables to the packages and use them when constructing the email, but I'd prefer to get the information directly from the OLE DB connection itself. Is there a way to access the connection string from within a control-flow VB script task? Furthermore, can I get the data source and initial catalog from that connection string, or do I need to parse it myself?

Thanks!

Phil

Yeah you can do that. See here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

If you don't want to parse it yourself you could try using the Properties collection using something like the following:

Dts.Connections("Errors").Properties("Format").GetValue(o)

Unfortunately that doesn't actually work cos I don't know what you're supposed to supply to the GetValue() method and the docs are a bit thin. But you can havea go with it if you like.

|||

Thanks, Jamie. You're right - I couldn't make it work with GetValue. However I was able to skin this particular cat thusly:

Dim _dbCxcn As String
Dim _inx As Integer
Dim _dbServer As String
Dim _dbCatalog As String

_dbCxcn = Dts.Connections("My_OLEDB_Connection").ConnectionString
_inx = InStr(_dbCxcn, "Data Source=")
_dbServer = Mid(_dbCxcn, _inx + 12, InStr(_inx, _dbCxcn, ";") - (_inx + 12))
_inx = InStr(_dbCxcn, "Initial Catalog=")
_dbCatalog = Mid(_dbCxcn, _inx + 16, InStr(_inx, _dbCxcn, ";") - (_inx + 16))

Then I constructed the email message using the _dbServer and _dbCatalog variables.

Thanks again!

Phil

Getting HelloWorld_CLR sample to work

Hello,

I have been trying to get the app installed and running; I've updated the install script to point to the right server and it worked. I've run the

ALTER DATABASE ssb_HelloWorld SET ENABLE_BROKER; and the

EXECsp_configure'clr enabled', 1;

and the CLR seemed to have problems while getting replies back from the server. However after repeated retries it worked well. Sporadically I would see an error. After uninstalling the sample and installing it again - to document the steps previously taken - the CLR application is no longer receiving messages from the server. Any idea about what setting might be needed?

Thanks,

Running the app - after a while - just for kicks, it worked. I do not understand why? Any help greatly appreciated.|||

Could you please clarify what you mean by "works" and "not works". For example, when you say "does not work" do you mean:

a) target queue is not getting the messages (i.e. select * from TargetQueue has no messages)

b) activation procedure is not getting activated at all (i.e. you don't see any tasks activated in select * from sys.dm_broker_activated_tasks but select * from TargetQueue is non-empty)

c) activation procedure gets launched but is throwing unhandled exceptions (which are reported to eventlog).

Thanks,
Rushi

|||

Hi Rushi,

After successfully sending a message from HelloWorldClient to HelloWorldService, the client app attempts to get a conversation from the service. It is here that no conversation is returned.

Also, as I am learning about this SQL Server Service Broker, and reading the online documentation, would there be:

1. A good book that would comprehensively cover Service Broker and CLR

2. Additional HelloWorld_CLR sample documentation. This sample touches - for instance message looping - that I could not find explicit related documentation.

Thanks in advance

|||

Rushi,

here is more info, from the event application log. The problem seems to have come back:

An error occurred in the service broker message dispatcher, Error: 15404 State: 19.

|||

See this previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1

Typically, this problem occurs because the dbo account is mapped to an invalid Windows login. Random failures indicate that the login is randomly valid/invalid, like for instance when you work on a laptop and sometimes you're connected to the domain sometimes you're not. Changing dbo to a valid login (like [sa]) resolves the issue:

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA];

HTH,
~ Remus

Getting HelloWorld_CLR sample to work

Hello,

I have been trying to get the app installed and running; I've updated the install script to point to the right server and it worked. I've run the

ALTER DATABASE ssb_HelloWorld SET ENABLE_BROKER; and the

EXEC sp_configure 'clr enabled', 1;

and the CLR seemed to have problems while getting replies back from the server. However after repeated retries it worked well. Sporadically I would see an error. After uninstalling the sample and installing it again - to document the steps previously taken - the CLR application is no longer receiving messages from the server. Any idea about what setting might be needed?

Thanks,

Running the app - after a while - just for kicks, it worked. I do not understand why? Any help greatly appreciated.|||

Could you please clarify what you mean by "works" and "not works". For example, when you say "does not work" do you mean:

a) target queue is not getting the messages (i.e. select * from TargetQueue has no messages)

b) activation procedure is not getting activated at all (i.e. you don't see any tasks activated in select * from sys.dm_broker_activated_tasks but select * from TargetQueue is non-empty)

c) activation procedure gets launched but is throwing unhandled exceptions (which are reported to eventlog).

Thanks,
Rushi

|||

Hi Rushi,

After successfully sending a message from HelloWorldClient to HelloWorldService, the client app attempts to get a conversation from the service. It is here that no conversation is returned.

Also, as I am learning about this SQL Server Service Broker, and reading the online documentation, would there be:

1. A good book that would comprehensively cover Service Broker and CLR

2. Additional HelloWorld_CLR sample documentation. This sample touches - for instance message looping - that I could not find explicit related documentation.

Thanks in advance

|||

Rushi,

here is more info, from the event application log. The problem seems to have come back:

An error occurred in the service broker message dispatcher, Error: 15404 State: 19.

|||

See this previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1

Typically, this problem occurs because the dbo account is mapped to an invalid Windows login. Random failures indicate that the login is randomly valid/invalid, like for instance when you work on a laptop and sometimes you're connected to the domain sometimes you're not. Changing dbo to a valid login (like [sa]) resolves the issue:

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA];

HTH,
~ Remus