Create Table syntax SQL in SQL Server 7

Hi guys,I need to pass some SQL to someone else who will run it on their database. I have got the SQL for SQL Server 2000 but they are running SQL Server 7. Apparently the below MSSQL 2000 script doesn't work;SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tableName]( [id] [int] IDENTITY(1,1) NOT NULL, [ArticleID] [int] NULL, [Heading] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [BodyContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [WrittenDate] [datetime] NULL,CONSTRAINT [tableName] PRIMARY KEY CLUSTERED ( [id] ASC ))What is the equivalent of the above in for SQL Server 7? I don't have access to it via SQL Server Manager so have to run the script.

Looks like the forum didn't like Safari. I'll try that againwith Firefox and see if that comes out a bit better. SO to summarise,how do I do the following in SQL Server 7, as this is for SQL Server2000 and I don't know what the syntax difference is:

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tableName]

( [id] [int] IDENTITY(1,1) NOT NULL,

[ArticleID] [int] NULL,

[Heading] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BodyContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[WrittenDate] [datetime] NULL,

CONSTRAINT [tableName] PRIMARY KEY CLUSTERED ( [id] ASC ))

|||

I've written this in what should be OK in SQL Server 7. Idon't have anyway of testing this, and would prefer that it is correctbefore sending it. So, is the below equivalent to the SQL Server 2000code above:

CREATE TABLE tableName
(
id int IDENTITY NOT NULL,
ArticleID text,
Heading text,
BodyContent text,
WrittenDate datetime
)

This doesn't seem to specify any primary key, how do I do that if indeed I need to?

Slowly realising how much I rely on SQL Server Managment studio...!

CREATE TABLE scripts

I have a bunch of CREATE TABLE scripts I need to run.

In Visual Studio 2003 you could right-click on a .SQL file and choose Run from the shortcut menu... and it would run the script. Visual Studio 2005 there is not a Run on the right-click short cut menu.

What gives?

hi,

why not just downloading the free official management tool, SQL Server Management Studio Express, from http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en ..

after installing it, the .Sql extension will be associated to this tool... very easy to use..

regards

|||

You should consider asking this question of the VS folks, they hang out over in the VS forums.

Mike

Create Table Script Incorrect syntax near 'Collate'

HI.

I am using SQL Server 2000. I generate a script of some table from EmpDB database when I run script in query analyzer it return error "Incorrect syntax near 'COLLATE'."

Scripts is

************************************************** ****
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Emp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Emp]
GO

CREATE TABLE [dbo].[Emp] (
[EmpID] [int] NOT NULL ,
[EmpName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

************************************************** ****
When I remove " COLLATE SQL_Latin1_General_CP1_CI_AS" from script manually then it run successfully.

Please guide why it is happening and how to escape from error.

Thanx in advance.I have tried your code and it works on my PC.

Try creating the table without the Collation and then use entreprice manager to assign the spicfic collation to the field. Does the specific calation exist?sql

create table permission denied

Hi,

i run an asp.net application which uses sql server express.
i defined a login 'aspnet' (IIS 5.0) and for the specific database, an user
'aspnet' with following roles:
db_datareader and db_datawriter.

Now, any user who uses that application must also be able to create
programmatically tables in that database. My question is: which role do i
have to give to user 'aspnet'?

I use Studio Management express.

Thanks
Tartuffe

The answer depends on which version of SQL Server you are using, since SQL Server 2005 dramatically enhanced permissions in the database. Which version?

Also, must the user be able to create ANY table in the database, or only a specific table? I think the former, but please confirm.

Don

|||

Hi, thanks for replying.

I use sql server 2005 express with Studio Management express.

Each windows-account in the domain in our organization who starts the application creates automatically (in code-behnd) a table with his personal (unique) number in the organization (e.g. L0564).

This happens only the first time he starts the application. So each member has his own table.

We use IIS 5.1, so the account which runs under asp.net is ASPNET. For another application, i defined a login in Studio Management and then for the database of that application, i defined a user 'aspnet' with following roles: db_reader and db_writer (i didn't use schema's because it's not very clear to me ..). This works, but there was no need to cerate a table.

I did the same for this new application +I db_owner and then it works. But i think it's probably too many privileges ... So: my question is: which privileges to give to 'aspnet' and how to do that in Studio Management?

Thanks

|||

You need the dbowner... only then you will be able to do the specific operations like creating tables and other things. DBWrite and Read will allow you to do simple updates insert and deletes.

|||

Thanks. I'll try.

If you don't mind, .. what if the ASPNET account must also be able to create databases? Is it suffisant with db_owner only?

|||

Yes i think so .. it doesn't matter which account it is .. till the time you map correct roles

|||

i tried with db_owner and Aspnet can create tables programmatically.

But Aspnet cannot create a new database with only db_owner.

|||

you need to be sysadmin for that

|||

Thanks

sql

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJosql

CREATE SCHEMA fails Inside an If Block

Hello All,

The below "CREATE SCHEMA" sql statement fails if it is inside an IF block. It runs fine if i run it without the IF block...

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')

BEGIN

CREATE SCHEMA Customer AUTHORIZATION [sys]

END

-

Did anyone encountered this issue before....

Thanks..

Make this as dynamic SQL.|||

Thanks, Bushan.

As the DDL scripts can grow bigger, I feel that it is hard to maintain dynamic sql. But, I was just trying to figure out why this is not possible in this "create schema" scenario alone. It even works for "drop schema".

|||

Like the error message you get back says, CREATE SCHEMA must be the first command in the batch. So to do a CREATE SCHEMA in a script like this, that one statement has to be done dynamically.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')
BEGIN
exec ('CREATE SCHEMA Customer AUTHORIZATION [sys]')
END
Annoying? Yes? But it is not that much more work than doing it in the way that you (and I) originally expected :)

|||This is such an OBVIOUS shortcoming of TransactSQL. Why hasn't Microsoft corrected this flaw. As far as I know (with the exception of entering the exec ('sneak the CREATE in as a text string') there is no way to conditionally create a SCHEMA or a FUNCTION for that matter.

If it's disallowed for security reasons then why can we sneak it in with an EXEC?

The problem this causes for me (over and over again) is I write a sample function for the user and include it in my upgrade script. If they've already run the script for an earlier version, they already have my example and may have customized it to their specific application. If they have, I don't really want to replace it with my example again. So I'm stuck sneaking it in through the string route. This has been a flaw in T-SQL for a long time....

Sorry for the rant... Sure wish the T-SQL gods were listening |||
Thanks for the clarification..

create new database in analysis service not use BI

i have AAAA project create by BI, now i want to change my computer, so i get XMLA from AAAA project in BI and run it in analysis service XMLA query but it return follow error:
Executing the query ...
Either the user, PCHOME\max, does not have access to the Analysis Services Project1 database, or the database does not exist.

Execution complete

i try another way: i click right mouse on my database from analysis service, so i choice "Script database as "--> CREATE to... and i get a new XMLA file so igo to another computer, and run it, SQL return follow error:
Executing the query ...
Either the user, PCHOME\max, does not have access to the AAAA database, or the database does not exist.

Execution complete
can i solve this problem.
thanks

Another way to do this is to use BI dev studio to import the database and then redeploy to another server

For example

Launch BI Dev Studio

New Project/Business Intelligence Projects/Import Analysis Services 9.0 Database

After loaded, go to project properties and set the target server.

Also you can use the Deployment Wizard under the start menu/SQL Server 2005/Analysis Services/Deployment Wizard

You do need to be an administrator on the target server in order to create a database there.

HTH

-Jamie

|||sorry, i want to create on new computer not connect to my computer, how to export analysis services database to import to another computer

As you said, i have to use

Launch BI Dev Studio

New Project/Business Intelligence Projects/Import Analysis Services 9.0 Database

on my computer and select database on my computer, after that change target server. on properties change server name,

then i go to another computer and open my project on target server?

i can't understand about your idea,

can you explain to me

|||I'm confused now. What is the true goal of what you are trying to accomplish? Are you simply trying to move an Analysis Services database from one machine to another machine? If so, the correct way to do so is to use backup and restore. If you want to move the definition of a database between machines, these other options are the way to go.|||

This link gives more information about the deployment wizard: http://msdn2.microsoft.com/en-us/library/ms176121.aspx which can create a database on Computer B from a project on Computer A

This link gives information about the synchronization wizard which may also be useful: http://msdn2.microsoft.com/en-us/library/ms174488.aspx - it can copy a database from Computer A to Computer B

There also other methods, 5 in total, described here: http://msdn2.microsoft.com/en-us/library/ms175446.aspx

Which method you use will depend on exactly what you would like to acheive. But I think you will find one of these methods suitable.

create new database in analysis service not use BI

i have AAAA project create by BI, now i want to change my computer, so i get XMLA from AAAA project in BI and run it in analysis service XMLA query but it return follow error:
Executing the query ...
Either the user, PCHOME\max, does not have access to the Analysis Services Project1 database, or the database does not exist.

Execution complete

i try another way: i click right mouse on my database from analysis service, so i choice "Script database as "--> CREATE to... and i get a new XMLA file so igo to another computer, and run it, SQL return follow error:
Executing the query ...
Either the user, PCHOME\max, does not have access to the AAAA database, or the database does not exist.

Execution complete
can i solve this problem.
thanks

Another way to do this is to use BI dev studio to import the database and then redeploy to another server

For example

Launch BI Dev Studio

New Project/Business Intelligence Projects/Import Analysis Services 9.0 Database

After loaded, go to project properties and set the target server.

Also you can use the Deployment Wizard under the start menu/SQL Server 2005/Analysis Services/Deployment Wizard

You do need to be an administrator on the target server in order to create a database there.

HTH

-Jamie

|||sorry, i want to create on new computer not connect to my computer, how to export analysis services database to import to another computer

As you said, i have to use

Launch BI Dev Studio

New Project/Business Intelligence Projects/Import Analysis Services 9.0 Database

on my computer and select database on my computer, after that change target server. on properties change server name,

then i go to another computer and open my project on target server?

i can't understand about your idea,

can you explain to me

|||I'm confused now. What is the true goal of what you are trying to accomplish? Are you simply trying to move an Analysis Services database from one machine to another machine? If so, the correct way to do so is to use backup and restore. If you want to move the definition of a database between machines, these other options are the way to go.|||

This link gives more information about the deployment wizard: http://msdn2.microsoft.com/en-us/library/ms176121.aspx which can create a database on Computer B from a project on Computer A

This link gives information about the synchronization wizard which may also be useful: http://msdn2.microsoft.com/en-us/library/ms174488.aspx - it can copy a database from Computer A to Computer B

There also other methods, 5 in total, described here: http://msdn2.microsoft.com/en-us/library/ms175446.aspx

Which method you use will depend on exactly what you would like to acheive. But I think you will find one of these methods suitable.

create multiple views

Hi,
I want to create multiple views in one run in the query analyzer, but it
won't work. I've the following sqlcode in the analyzer:
create view test1 as select.......
create view test2 as select......
etc.
What is wrong with this code?
On Thu, 5 Aug 2004 08:09:02 -0700, Ezekil wrote:

>Hi,
>I want to create multiple views in one run in the query analyzer, but it
>won't work. I've the following sqlcode in the analyzer:
>create view test1 as select.......
>create view test2 as select......
>etc.
>What is wrong with this code?
Hi Ezekil,
The CREATE VIEW statement must be the first in a batch. So your code will
run if you add batch seperators:
create view test1 as select.......
GO
create view test2 as select......
GO
etc.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

create multiple views

Hi,
I want to create multiple views in one run in the query analyzer, but it
won't work. I've the following sqlcode in the analyzer:
create view test1 as select.......
create view test2 as select......
etc.
What is wrong with this code?On Thu, 5 Aug 2004 08:09:02 -0700, Ezekil wrote:

>Hi,
>I want to create multiple views in one run in the query analyzer, but it
>won't work. I've the following sqlcode in the analyzer:
>create view test1 as select.......
>create view test2 as select......
>etc.
>What is wrong with this code?
Hi Ezekil,
The CREATE VIEW statement must be the first in a batch. So your code will
run if you add batch seperators:
create view test1 as select.......
GO
create view test2 as select......
GO
etc.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Create Maintenance Plans on SQL2K and SQL2005 w/SQL Mgmt. Studio?

I don't see any way to run the Maintenance Plan wizard against my existing SQL2K or SQL2005 DBs using the MS SQL Maintenance Studio. I read in another thread about having to install SSIS, and I think I have that on the SQL2005, but how is it done against the SQL2K DBs? I have dug around in the Mgmt Studio to no avail. If this thing is not fully backward compatible, then in addition to being slow as an old dog on a 2.5Ghz computer, then I really think MS has not demonstrated how cool this .NET stuff can be (or have they). What a sink for CPU cycles.

Do you have SQL 2000 server tools installed as a seperate instance, if so why not schedule with this server instance.

True that you have to use SSIS in order to run the maintenance plan and schedule that a seperate job, refer to the books online for more information in this regard.

|||

Yes, I do have the SQL 2000 Enterprise Manager installed, but I was trying to transition to a single set of tools to manage all my servers. One would think that MS would support fully the next most recent version.

What would be the way to verify if the SSIS is installed on a specific server?

create index with drop existing

Create index with drop existing after succesfull creation,
when dbcc checktable is run, at times it does not finish
at all, when killed and again submitted runs okay.
Vinodh,
Are you saying that the dbcc does not finish or the create index? How long
did you wait? How big is the table, and how many indexes on there?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Vinodh" <anonymous@.discussions.microsoft.com> wrote in message
news:b85901c437d6$ef3b81c0$a401280a@.phx.gbl...
> Create index with drop existing after succesfull creation,
> when dbcc checktable is run, at times it does not finish
> at all, when killed and again submitted runs okay.
>

create index with drop existing

Create index with drop existing after succesfull creation,
when dbcc checktable is run, at times it does not finish
at all, when killed and again submitted runs okay.Vinodh,
Are you saying that the dbcc does not finish or the create index? How long
did you wait? How big is the table, and how many indexes on there?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Vinodh" <anonymous@.discussions.microsoft.com> wrote in message
news:b85901c437d6$ef3b81c0$a401280a@.phx.gbl...
> Create index with drop existing after succesfull creation,
> when dbcc checktable is run, at times it does not finish
> at all, when killed and again submitted runs okay.
>

create index with drop existing

Create index with drop existing after succesfull creation,
when dbcc checktable is run, at times it does not finish
at all, when killed and again submitted runs okay.Vinodh,
Are you saying that the dbcc does not finish or the create index? How long
did you wait? How big is the table, and how many indexes on there?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Vinodh" <anonymous@.discussions.microsoft.com> wrote in message
news:b85901c437d6$ef3b81c0$a401280a@.phx.gbl...
> Create index with drop existing after succesfull creation,
> when dbcc checktable is run, at times it does not finish
> at all, when killed and again submitted runs okay.
>

'CREATE INDEX' Statement...Faster?

I am indexing a 32 millon row table with a unqine clustered index and
the run time for the create index statement is over 4 hours long. This
runs is too slow and may cost me a job If i cant find a faster way to
create index on large table.
Can anyone help.
Hi
What is the version of SQL Server?
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
|||i don't think it would be faster. Clustered Index is the way data is stored
physically in the hard disk.
bye!
"GreenHillCourt@.gmail.com" wrote:

> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
>
|||Setting the database to simple recovery should improve compared to full recovery. At least it will
cut down on the logging. But the data shuffling still has to occur of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:15C6F565-D67E-4912-8424-BFBDF0EC88A2@.microsoft.com...[vbcol=seagreen]
>i don't think it would be faster. Clustered Index is the way data is stored
> physically in the hard disk.
> bye!
> "GreenHillCourt@.gmail.com" wrote:
|||<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
As others have said, if you're creating the clustered index, not much you
can do other than perhaps look at a faster disk subsystem (say raid 10 vs
RAID 5, etc.)
If you mean you're creating a non-clustered index on a table that has an
existing clustered index, one thing that can certainly help is putting the
non-clustered index on its own set of disks.

> Can anyone help.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Have you tried creating the index without clustering? If I'm not mistaken,
it should then not have to physically re-order the data.
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
|||"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
- drop all unnecessary nonclustered indexes
- if possible, connect in single user mode
- add extra memory to the server, don't "pin" tables
- make sure you have enough I/O and bandwidth, both for your table, the
TempDB and the log file
- make sure you have enough free space before you start, at least 1.5
times the table size
- set the database recovery mode to simple
- choose an appropriate fillfactor. Any value below 70 is probably a bad
idea
4 hours seems excessive. How much space does the table occupy (before
you start)? What is your I/O system (number of disks, etc.)?
Gert-Jan
SQL Server MVP

'CREATE INDEX' Statement...Faster?

I am indexing a 32 millon row table with a unqine clustered index and
the run time for the create index statement is over 4 hours long. This
runs is too slow and may cost me a job If i cant find a faster way to
create index on large table.
Can anyone help.Hi
What is the version of SQL Server?
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.|||i don't think it would be faster. Clustered Index is the way data is stored
physically in the hard disk.
bye!
"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
>|||Setting the database to simple recovery should improve compared to full recovery. At least it will
cut down on the logging. But the data shuffling still has to occur of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:15C6F565-D67E-4912-8424-BFBDF0EC88A2@.microsoft.com...
>i don't think it would be faster. Clustered Index is the way data is stored
> physically in the hard disk.
> bye!
> "GreenHillCourt@.gmail.com" wrote:
>> I am indexing a 32 millon row table with a unqine clustered index and
>> the run time for the create index statement is over 4 hours long. This
>> runs is too slow and may cost me a job If i cant find a faster way to
>> create index on large table.
>> Can anyone help.|||<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
As others have said, if you're creating the clustered index, not much you
can do other than perhaps look at a faster disk subsystem (say raid 10 vs
RAID 5, etc.)
If you mean you're creating a non-clustered index on a table that has an
existing clustered index, one thing that can certainly help is putting the
non-clustered index on its own set of disks.
> Can anyone help.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Have you tried creating the index without clustering? If I'm not mistaken,
it should then not have to physically re-order the data.
<GreenHillCourt@.gmail.com> wrote in message
news:a7661fdd-12e9-4a12-969f-6ec961eab992@.n1g2000prb.googlegroups.com...
>I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.|||"GreenHillCourt@.gmail.com" wrote:
> I am indexing a 32 millon row table with a unqine clustered index and
> the run time for the create index statement is over 4 hours long. This
> runs is too slow and may cost me a job If i cant find a faster way to
> create index on large table.
> Can anyone help.
- drop all unnecessary nonclustered indexes
- if possible, connect in single user mode
- add extra memory to the server, don't "pin" tables
- make sure you have enough I/O and bandwidth, both for your table, the
TempDB and the log file
- make sure you have enough free space before you start, at least 1.5
times the table size
- set the database recovery mode to simple
- choose an appropriate fillfactor. Any value below 70 is probably a bad
idea
4 hours seems excessive. How much space does the table occupy (before
you start)? What is your I/O system (number of disks, etc.)?
--
Gert-Jan
SQL Server MVP

Create HTTP request in Stored Procedure

Hi,

I want to know if it is possible to create and execute a http request in SQL Server. I want to run the request in the database as alot of the data in the url would be retrieved from the database.

I want to create a stored procedure that runs the request. The stored procedure must be called from a trigger on a table.

The reason I want to go this way is because it would be alot faster that to get the data from the database and then execute the http request in the application. Another reason that I want it done on the database side is because the internet connection of my client is not as fast as the one I am using for development so I don't want the pages to load to many times and get the data between the server and the client computer through as fast as possible.

Any ideas on how I would do this?

Without a more detailed understanding of your particular problem domain, it's difficult to be certain of this but in general I'd recommend against this approach. It seems like a poor use of resources that could be utilized by the core database services. For example, if the triggers are synchronous operations, I would imagine that they would hold a row lock for the duration of the HTTP request...and given that network requests are usually high-latency operations, that wouldn't be a good thing in a multi-user environment.

If the web data that is related to the row data is unchanging or slowly changing, you could consider using a service to collect this data and cache copies of it in the database (a benefit of which is that you would have a DB only query from your client application). If the web data changes frequently/continuously, expecting your DB client to handle this amalgamation seems appropriate. That client code could exist in the actual end-user client application or, if you're comfortable with multi-tier application programming, behind a web service that aggregates all of this information for the end-user client application (NOTE: your SQL Server can be exposed as a web service, which could help consolidate the design of the overall system).

You may also want to consider looking at SQL Server Integration Services...this technology is meant to aggregate data from heterogeneous sources as you've described.

As to writing a stored proc that makes the web request, I don't see anything in the TSQL reference that stands out as a likely candidate, but this meets my expectations for the reasons I mention above. If you were to write an extended stored procedure (see http://msdn2.microsoft.com/en-us/library/ms164716.aspx for more information), you could do whatever you wanted. You could also potentially use CLR Integration (additional information is provided via a link at the previous URL). Note that in both cases, you'd likely have to manage issues around configuration and delegation.

|||

Thank you!

I think I'll go the web service route on this one then.

The data is slow changing. I want to do the following:

I'm writing a system that manages rugby referees. A member would be appointed to official a game as either the referee or a touch judge and then there would be performance reveiwers appointed on some of the game. The web request functionality come in when the member would be notified of his appoint on a specific game and would also be notified if there are any changes on that specific game he appointed on - this would then happen for all the game he/she is appointed on.

You've mentioned the of a web service...from what I understand from my problem and what you have said, a web service would be a good solution? From what I understand what would happen with the web service is that I would monitor the specific table and then act as soon as there are changes to the table?

I am not at all familiar with SQL Server Integration Services or CLR Integration so I wouldn't actually know which would be the better more effective solution. If you need me to give more detail on what should happen I can do so, but I need the best and most effective solution. I have more than enough resource about SQL Server so if I don't know how to do it I would be able to find out, but I couldn't find anything on HTTP requests in SQL so I wanted to know if it was possible seeing I couldn't get any information.

|||What does the web request do? It seems like the intent of the system is to send a notification when some property of the match changes (e.g. start time changes from 4pm to 5pm)...I can envision a web front end for referees, touch judges, and performance reviewers to enter information (e.g. an email address to which schedule updates should be sent), but I'm not yet clear as to why this data would reside outside the database.|||

It should send out an sms to the referee who's match it is so that he/she can be notified of the change.

Email would also be used. The email will be used when a member is appointed, then the details will be emailed to the individual.

|||

Aaaah! I get it now...I was thinking the HTTP request would be pulling information into the database, not pushing data out via an HTTP POST or something similar.

Since many carriers allow for email to be routed to their customers phones via SMS, can you have a general email delivery of information? As an example, Cingular allows you to send an SMS to someone from your email account by sending the message to <10-digit phone number>@.cingularme.com. This would allow you to use sp_send_dbmail to send all of your outbound info...you could then choose different reports/queries to generate different notification types.

If that doesn't work for you, your next best bet is to write either an extended stored procedure or a managed stored procedure.

|||

Ok help me here.

I will be hosting remotely on a webserver over which I do not have control. The service provider won't allow me to send email throuh the server and from what I can gather I do not have access to SQl server integration services on that server.

With regards to the Extended Stored Procedures I understand this from it - help me if I'm wrong. I have to compile a dll which would need to be executed by a stored procedure. The dll would need to be on the same server as the database? When the stored procedure is then executed, it will pass the results to the dll and methods in the dll will then be executed?

So if I understand correctly it will work as follow in my sittuation...

A referee is appointed on a specific game or details on a game he is appointed on changes. When this happens I call a stored procedure which in turn call a dll that I have written which sends the notification to the referee.

You said something about a webservice as well. How would I be going about to send the data to the webservice to perfrom the functions a I require?

I have gone through a couple of stuff of which this one is best from what I have seen. I am still to figure out precily how I would impliment that to solve my problem but I will get there. In the meantime some extra help will do!!

Thanks

|||

That gives me a much better idea of the limitations you're faced with...

Your understanding of the extended stored procedure is essentially correct (NOTE: you could also potentially use a managed stored procedure -- which is the CLR integration you've linked above -- that used web request functionality from the .Net Framework...not sure if the security policy within SQL Server would support that, however). When a table update happened, a trigger would call the extended stored procedure.

Your options, as you've discovered, are somewhat limited because you don't control the server setup. Perhaps it would be drastically easier to implement a polling solution...as a simplistic example, you could implement a service/app that ran on your machine and periodically (every hour?) queried the database to determine if there were updated records for which updates needed to be sent.