conversion

Hi all,
I want a good tool to write scripts with to convert data from a MSSQL
database to any other database (possibly, but no neccasarily, also MSSQL)
Anyone have any good ideas?
Luuk
Well SQL Server has built in tools for that. IN SQL2000 it is called DTS and
in 2005 it is SSIS. I would check BooksOnLine for more information.
Andrew J. Kelly SQL MVP
"Luuk" <luuk@.invalid.lan> wrote in message
news:4608286a$0$329$e4fe514c@.news.xs4all.nl...
> Hi all,
> I want a good tool to write scripts with to convert data from a MSSQL
> database to any other database (possibly, but no neccasarily, also MSSQL)
> Anyone have any good ideas?
> --
> Luuk
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
news:O5FlD8$bHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Well SQL Server has built in tools for that. IN SQL2000 it is called DTS
> and in 2005 it is SSIS. I would check BooksOnLine for more information.
> --
> Andrew J. Kelly SQL MVP
> "Luuk" <luuk@.invalid.lan> wrote in message
> news:4608286a$0$329$e4fe514c@.news.xs4all.nl...
>
so, i have to investigate DTS, and after that i have to investigate SSIS, an
after that ..... ;-(
?

controlling security through stored procedures -- 2005 behaviour

Hi!

I'm trying to control security through sps -- meaning execute permissions are granted on stored procedures, and no users have read/write permissions on tables, etc directly.

Which works fine as long as all objects referenced are in the same db as the procedure.

An issue arises when a stored procedure accesses a table in another database:

Getting a : Msg 229 SELECT permission denied on object 'blah' Even though the procedure is created by sysadmin.

Has this changed since 2000? I'm pretty sure in 2000 it would've worked as the sp would be executed in sp owner's security context.

Moreover, when I try to use EXECUTE AS in the sp as a workaround, I am getting the following, no matter what account I try to impersonate:

Msg 916, Level 14, State 1, Procedure vvv, Line 4
The server principal % is not able to access the database "blah" under the current security context.

any ideas?
Thanks!

Most likely this scenario worked on Windows 2000 with cross-database ownership chaining enabled. Turning on this feature is not recommended, as it may lead to an elevation of privileges (i.e. the DB administrators of the source database may escalate their privileges to become DB administrators on the target DB).

The reason why your stored procedure marked with “execute as” is not working is because the impersonated context is (by default) scoped only to the surrent (source) database, and stripped down from it's server-scoped permissions and privileges. If you wish to use this impersonated context outside the source DB, you need to establish a trust relationship on the target DB.

To solve this problem, you can probably use digital signatures to solve your problem; by signing the stored procedure with a certificate you have a way to ensure that the code has not been tampered with. If at run time the signature matches the code, the certificate can be used in two ways:

* As a secondary identity for the execution context. This means that if there is a user mapped to the signing certificate, the permissions on that user will be used to calculate the permissions on the object.

* When the module (SP) is marked with execute as, the signature will work as an authenticator, that means the signature will be used to vouch for the impersonated context in the stored procedure

Note that for the secondary identity approach, the signature will be added to the current context therefore, if the current context is not a valid one on the server scope (i.e. the caller is an approle), the certificate as secondary identity cannot be used on cross database scenario.

The second approach on the other hand establishes a whole new context on top of the calling context, and it is the signature the one vouching for this new context on the target database.

I am posting a small demo at the end taht I hope will help you.

Thanks a lot for your comments and feedback.

-Raul Garcia
SDE/T
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.

-

/*******************************************************************

*

* This posting is provided "AS IS" with no warranties, and
* confers no rights.

*

* Author: Raulga

* Date: 08/24/2005

* Description:

* This demo shows how to use digital signatures to access
* resources on a different database by using digitaly signed stored
* procedures to control the access rather than using cross database
* ownership chaining.

*

* The first SP will be using the siganture as a secondary identity
* on top of the calling context. This means that only a context with
* a server-presence will succeed on this call (i.e. approles will not
* be able to accsss the resources on the target database as they

* don't have a server presence).

*

* The second approach will be by specifying a context switch
* (EXECUTE AS) on the stored procedure and using the signature as an
* authenticator; this means that the signature can vouch for the
* impersonated context (specifid on the module). This mechanism will
* allow to access the resources regardless of the original calling
* context because a new context (vouched by the signature) is placed
* on top of the orginal one, but requires more managment.

*

* (c) 2005 Microsoft Corporation. All rights reserved.

*

***********************************************************************************************/

CREATE DATABASE db_Source

go

CREATE DATABASE db_Target

go

CREATE LOGIN dbo_db_Source WITH PASSWORD = 'My S0uRc3 D8 p@.55W0rD!'

CREATE LOGIN dbo_db_Target WITH PASSWORD = 'My +@.r637 D8 p@.55W0rD!'

go

-- Change the ownership for the source and the target databases

ALTER AUTHORIZATION ON DATABASE::db_Source to dbo_db_Source

ALTER AUTHORIZATION ON DATABASE::db_Target to dbo_db_Target

go

-- This principal will be the data owner, he can access the data on

-- the target database, and he controls the stored procedures on the

-- source database

CREATE LOGIN data_owner WITH PASSWORD = 'd@.+4 0wn3R'

-- This principal should only have access to the data via the stored

-- procedures

CREATE LOGIN someuser WITH PASSWORD = 's0m3 p@.55w0Rd'

go

use db_Target

go

CREATE USER someuser

CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner

go

CREATE SCHEMA data_owner AUTHORIZATION data_owner

go

CREATE TABLE data_owner.MyTable( data nvarchar(100) )

go

INSERT INTO data_owner.MyTable values ( N'My data' )

go

use db_Source

go

CREATE USER someuser

CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner

go

CREATE SCHEMA data_owner AUTHORIZATION data_owner

go

-- ALlow someuser to execute any module on the schema called data_owner

GRANT EXECUTE ON SCHEMA::data_owner TO someuser

go

-- Create a stored procedure that uses the default execution context

-- (the caller's context) at runtime

CREATE PROC data_owner.sp_GetMyData01

AS

select * from db_Target.data_owner.MyTable

go

-- Create a stored procedure similar to teh previous one, but this time we will explicitly

-- use the data_owner context via EXECUTE AS

CREATE PROC data_owner.sp_GetMyData02

WITH EXECUTE AS 'data_owner'

AS

select * from db_Target.data_owner.MyTable

go

-

-- Let's see what is the behavior without any signatures

--

-- You can either start new connections or just use the

-- EXECUTE AS LOGIN & REVERT statements I show here for testing

-- Execute as the data owner

-

EXECUTE AS LOGIN = 'data_owner'

go

-- will succeed

EXEC data_owner.sp_GetMyData01

go

-- Will fail as the impersonated context is not trusted on the target
-- database

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Execute as someuser

-

EXECUTE AS LOGIN = 'someuser'

go

-- will fail due to the lack of permissions on the target database

EXEC data_owner.sp_GetMyData01

-- will fail as the impersonated context is not trusted on the target
-- database

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Signing the stored procedures

--

-- Create 2 certificates one to sign each SP.

-- Note that I am using passwords to protect the private keys.

-- It is also possible to use a DB master key to protect private the
-- keys, please refer to BOL for more information on the key
-- hierarchy

CREATE CERTIFICATE cert_GetMyData01

ENCRYPTION BY PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'

WITH SUBJECT = 'Certificate to sign sp_GetMyData01'

go

CREATE CERTIFICATE cert_GetMyData02

ENCRYPTION BY PASSWORD = 'GetMyData02 c3r+ P455W0Rd'

WITH SUBJECT = 'Certificate to sign sp_GetMyData02'

go

-- Now sign the stored procedures, as the cert's

-- private keys are protected by passwords, we have to use the
-- passwords to sign

ADD SIGNATURE TO data_owner.sp_GetMyData01 BY CERTIFICATE cert_GetMyData01

WITH PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'

go

ADD SIGNATURE TO data_owner.sp_GetMyData02 BY CERTIFICATE cert_GetMyData02

WITH PASSWORD = 'GetMyData02 c3r+ P455W0Rd'

go

-- Let's take a quick look to the metadata for the signed modules

SELECT schema_name( c.schema_id ) as schema_name, c.name,

b.name, a.crypt_property as 'module siganture' FROM

sys.crypt_properties a,

sys.certificates b,

sys.objects c

WHERE a.thumbprint = b.thumbprint AND a.class = 1
AND a.major_id = c.object_id

go

-- Depending on your application and environment, sometimes you may
-- not want to leave the private keys on the database, and either
-- destroy the private keys (this way, they can never be used to
-- sign anything else), or back up a copy of the private keys and
-- store them in a safe place. For this demo I will just destoy the
-- private keys as we don't need them anymore

ALTER CERTIFICATE cert_GetMyData01 REMOVE PRIVATE KEY

ALTER CERTIFICATE cert_GetMyData02 REMOVE PRIVATE KEY

go

-- Now, we need to create a backup for the certificate public data.

-- We will need to import it back on teh target database.

BACKUP CERTIFICATE cert_GetMyData01 TO FILE = 'cert_GetMyData01.cer'

BACKUP CERTIFICATE cert_GetMyData02 TO FILE = 'cert_GetMyData02.cer'

go

use db_Target

go

-- Import the certificates on the target database, note that we don't
-- need the private keys

CREATE CERTIFICATE cert_GetMyData01
FROM FILE = 'cert_GetMyData01.cer'

go

CREATE CERTIFICATE cert_GetMyData02
FROM FILE = 'cert_GetMyData02.cer'

go

-- Now let's create users mapped to each one of the certificates.

-- As permissions can only be granted to principals and not directly

-- to a certificate, we need to map the certificate to a user.

-- Note: The cert-mapped user SID is derived from teh certificate
-- thumbprint

-- therefore any 2+ principals (login or user in any database)
-- mapped to the

-- same certificate will have the same SID and will refer to the same

-- principal for practical purposes.

CREATE USER cert_GetMyData01 FOR CERTIFICATE cert_GetMyData01

go

CREATE USER cert_GetMyData02 FOR CERTIFICATE cert_GetMyData02

go

-- For the first SP, grant the permissions to the cert-mapped
-- user directly

GRANT SELECT ON data_owner.MyTable TO cert_GetMyData01

go

-- For the second SP, we want only AUTHENTICATE permissiion, this
-- will allow teh certificate to vouch for the context only on this
-- database.

-- Note: As the trust is only accross database and not accross the
-- instance, the new context is only valid for database operations,
-- and will not honor any server-scoped permissions.

GRANT AUTHENTICATE TO cert_GetMyData02

go

USE db_Source

go

-

-- Let's see what is the behavior without any signatures

--

-- You can either start new connections or just use the

-- EXECUTE AS LOGIN & REVERT statements I show here for testing

-- Execute as the data owner

-

EXECUTE AS LOGIN = 'data_owner'

go

-- will succeed

EXEC data_owner.sp_GetMyData01

go

-- will succeed as the module is executing as "data_owner"

-- (the module is specifying the context itself), and the

-- signature is vouching for this context

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Execute as someuser

-

EXECUTE AS LOGIN = 'someuser'

go

-- will succed as the certificate will be granting the required
-- permission to select the data from the table

-- Note that someuser is a valid context accross the server at
-- this point

EXEC data_owner.sp_GetMyData01

-- will succeed as the module is executing as "data_owner"

-- (the module is specifying the context itself), and the

-- signature is vouching for this context

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- cleanup

USE master

go

DROP DATABASE db_Source

go

DROP DATABASE db_Target

go

DROP LOGIN dbo_db_Source

DROP LOGIN dbo_db_Target

DROP LOGIN data_owner

DROP LOGIN someuser

go

|||

Raul -- thanks a lot for taking the time to do this. Excellent explanation and demo!

Control of flow around "CREATE PROCEDURE"

Hi there.
I am trying to write a single script to create some stored procedures. One
of the stored procedures however, refers to a database which may or may not
be present on the server. In the case of that database NOT being present, I
would like to create the stored procedure with different contents (as the
original contents cause script errors when the missing database os referred
to). However, I'm having trouble controlling the flow of execution in the
script around CREATE PROCEDURE as it needs to be the first instruction in a
batch.
Basically I'd like to do something like this:
Use SomeOtherDatabase
GO
IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
CREATE PROCEDURE p_MyStoredProc
AS
SELECT * FROM MyDatabaseName.dbo.SomeTable
GO
ELSE --The database doesn't exist
CREATE PROCEDURE p_MyStoredProc
AS
PRINT 'The Database doesnt exist on this server'
GO
The reason I want to take this approach is to avoid script errors when the
script is run on servers where that database is missing.
Any ideas how I should go about this?
Any help would be much appreciated!!Len,
Kinda questionable approach. Try something like this using Dynamic SQL:
IF DB_ID('MyDatabaseName') IS NOT NULL
EXEC('CREATE PROCEDURE ...')
ELSE
EXEC('CREATE PROCEDURE ...')
Also see Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"len" <len@.discussions.microsoft.com> wrote in message
news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
> Hi there.
> I am trying to write a single script to create some stored procedures. One
> of the stored procedures however, refers to a database which may or may
> not
> be present on the server. In the case of that database NOT being present,
> I
> would like to create the stored procedure with different contents (as the
> original contents cause script errors when the missing database os
> referred
> to). However, I'm having trouble controlling the flow of execution in the
> script around CREATE PROCEDURE as it needs to be the first instruction in
> a
> batch.
> Basically I'd like to do something like this:
> Use SomeOtherDatabase
> GO
> IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
> CREATE PROCEDURE p_MyStoredProc
> AS
> SELECT * FROM MyDatabaseName.dbo.SomeTable
> GO
> ELSE --The database doesn't exist
> CREATE PROCEDURE p_MyStoredProc
> AS
> PRINT 'The Database doesnt exist on this server'
> GO
> The reason I want to take this approach is to avoid script errors when the
> script is run on servers where that database is missing.
> Any ideas how I should go about this?
> Any help would be much appreciated!!|||Why not just fix the code that is calling the wrong proc? Seems like an
unusual architecture if neither your client code or your procs will
know whether a database exists or not.
Where possible I find it better to reference other databases only in
views and then write procs against the views. That way views act as
your database indirection and the database names are hard-coded in as
few places as possible.
David Portas
SQL Server MVP
--|||Perfect - thanks! - I had tried dynamic SQL but got stuck on sp_executesql a
s
my stored proc was over 4000 chars long - Erland's article covers this thoug
h
"Jerry Spivey" wrote:

> Len,
> Kinda questionable approach. Try something like this using Dynamic SQL:
> IF DB_ID('MyDatabaseName') IS NOT NULL
> EXEC('CREATE PROCEDURE ...')
> ELSE
> EXEC('CREATE PROCEDURE ...')
> Also see Erland's article:
> http://www.sommarskog.se/dynamic_sql.html
> HTH
> Jerry
> "len" <len@.discussions.microsoft.com> wrote in message
> news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
>
>|||I'm not too happy with the architecture myself! Unfortunately it's a legacy
thing whereby my principal aim was just to minimize the number of scripts
needed to install some additional stored procs.
"David Portas" wrote:

> Why not just fix the code that is calling the wrong proc? Seems like an
> unusual architecture if neither your client code or your procs will
> know whether a database exists or not.
> Where possible I find it better to reference other databases only in
> views and then write procs against the views. That way views act as
> your database indirection and the database names are hard-coded in as
> few places as possible.
> --
> David Portas
> SQL Server MVP
> --
>

Consuming results sets in a calling SQL procedure

Dear All,

This is a query surrounding a problem I encountered
yesterday.

In SQL Server, it is possible to write a procedure that
has one or more select statements in it.

The results from these select statements will all be
individually returned to SQL Query Analyser where they
can be viewed in "grid" views. Also, these individual
results sets can be consumed by eg ADO.NET by stepping
through each results set in turn and processing the
respective results.

My question is, can you do the same in a SQL Server
procedure? ie:

Create Procedure Proc1
AS
begin
select Col1, COl2
from Table1

select Col1, Col2, Col3
from Table2
end

Create Procedure Proc2
AS
begin
exec Proc1
end

Can both/either of the results sets from Proc1 be
consumed by the calling procedure Proc2?

I can see that you could design the procedures up-
front to do almost anything without consuming the
result sets in this way, but if the procedures
returning the results sets are already built and
in use in other places (for instance in client code),
can they be re-used on server-side SQL procedures?
Thanks in anticipation!

Paul.In the example you have, the result to the caller of Proc 2 will see
the two results of the Proc1 procedure. As far as Proc2 "consuming" the
results of Proc1, there is no operation happening on the results in
your example.

Are you asking if you could return multiple tables from another proc
and do some manipulation on them, before you return those to the caller?|||Thanks for the quick reply!

> As far as Proc2 "consuming" the results of Proc1,
> there is no operation happening on the results
> in your example.

That is because I don't know how to represent it at
the moment - hopefully that's where you come in! ;-)

> Are you asking if you could return multiple tables
> from another proc and do some manipulation on them,
> before you return those to the caller?

yes - whether I can return and manipulate one or more
data sets into a calling procedure, *without* changing my
original (called) procedures which take a form similar to:

Create Procedure Proc1
AS
begin
select Col1, COl2
from Table1

select Col1, Col2, Col3
from Table2
end

I hope this makes sense...

Thanks!

Paul.|||You can use the NextResult method of a SqlDataReader object to process
multiple result sets returned from a single command. For example:

SqlDataReader myDataReader = myCommand.ExecuteReader();
while(true)
{
while(myDataReader.Read())
{
ProcessMyResults();
}
if(!myDataReader.NextResult()) break;
}
myDataReader.Close();

--
Hope this helps.

Dan Guzman
SQL Server MVP

<p_le_sueur_1@.hotmail.com> wrote in message
news:1102508250.805756.307860@.z14g2000cwz.googlegr oups.com...
> Thanks for the quick reply!
>> As far as Proc2 "consuming" the results of Proc1,
>> there is no operation happening on the results
>> in your example.
> That is because I don't know how to represent it at
> the moment - hopefully that's where you come in! ;-)
>> Are you asking if you could return multiple tables
>> from another proc and do some manipulation on them,
>> before you return those to the caller?
> yes - whether I can return and manipulate one or more
> data sets into a calling procedure, *without* changing my
> original (called) procedures which take a form similar to:
> Create Procedure Proc1
> AS
> begin
> select Col1, COl2
> from Table1
> select Col1, Col2, Col3
> from Table2
> end
> I hope this makes sense...
> Thanks!
> Paul.|||On 8 Dec 2004 04:17:30 -0800, p_le_sueur_1@.hotmail.com wrote:

> Thanks for the quick reply!
>> As far as Proc2 "consuming" the results of Proc1,
>> there is no operation happening on the results
>> in your example.
> That is because I don't know how to represent it at
> the moment - hopefully that's where you come in! ;-)
>> Are you asking if you could return multiple tables
>> from another proc and do some manipulation on them,
>> before you return those to the caller?
> yes - whether I can return and manipulate one or more
> data sets into a calling procedure, *without* changing my
> original (called) procedures which take a form similar to:
> Create Procedure Proc1
> AS
> begin
> select Col1, COl2
> from Table1
> select Col1, Col2, Col3
> from Table2
> end
> I hope this makes sense...
> Thanks!
> Paul.

The INSERT INTO ... EXEC command can let a T-SQL batch (or procedure)
consume ONE resultset from another procedure, but not multiple resultsets.
Sorry.

Dan's method shows how to consume multiple resultsets from a .NET client,
but not from another stored procedure.|||(p_le_sueur_1@.hotmail.com) writes:
> My question is, can you do the same in a SQL Server
> procedure? ie:
> Create Procedure Proc1
> AS
> begin
> select Col1, COl2
> from Table1
> select Col1, Col2, Col3
> from Table2
> end
> Create Procedure Proc2
> AS
> begin
> exec Proc1
> end
> Can both/either of the results sets from Proc1 be
> consumed by the calling procedure Proc2?

When you call Proc2, the result sets go to the client. You can use
INSERT/EXEC to catch the data, but it only works if the result sets
are equally structures. (I think it works then, I am not sure.)

Anyway, I have an article on my web site, which discusses this in detail:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Consuming a Recordset in a Script Transformation

I have a situation where I have created a recordset in a previous data flow task where the output was a recordset.

Now I want to write that recordset to a flat file.

There is no direct recordset input data flow source. So I thought I would use a source script transformation to read the recordset. Then I searched on this and found no information on how I might do this.

Anyone have any ideas on how to do this in a script transformation?

There are reasons that I would like to do it this way, but if I have to choose another way, well that's life.

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

|||

DarrenSQLIS wrote:

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

I echo what Darren has said. And if you need proof, go here:

Comparing performance of a raw file against a recordset destination
http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4159.aspx

-Jamie

constraints for e-mail

Is it possible to write a check constraint for a column in a table which says that the entry must contain a '@.' and a '.'? (It's an email address field)
I'm guessing this is the best way to do it anyway,
Thanks in advance, newbie!USE Northwind
GO

CREATE TABLE myTable99(
Col1 varchar(255)
CONSTRAINT myTable99_ck1
CHECK ( LEN(Col1)=LEN(REPLACE(Col1,'@.',''))+1
AND LEN(Col1)>LEN(REPLACE(Col1,'.',''))
)
)

INSERT INTO myTable99(Col1) SELECT 'brett.kaiser@.somewhere.com'
INSERT INTO myTable99(Col1) SELECT 'brettkaiser@.somewhere'
INSERT INTO myTable99(Col1) SELECT 'brett.kaiser somewhere.com'

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||What? You don't like it?

:-o|||How about

check (col1 like '%@.%.%')|||Originally posted by MCrowley
How about

check (col1 like '%@.%.%')

That'll allow more than 1 @. sign...

CREATE TABLE myTable99(
Col1 varchar(255)
CONSTRAINT myTable99_ck1
CHECK ( (col1 like '%@.%.%')
)
)

INSERT INTO myTable99(Col1) SELECT 'brett.kaiser@.somewhere.com'
INSERT INTO myTable99(Col1) SELECT 'brett@.kaiser@.somewhere.com'
INSERT INTO myTable99(Col1) SELECT 'brettkaiser@.somewhere'
INSERT INTO myTable99(Col1) SELECT 'brett.kaiser somewhere.com'

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||thanks guys thats great! :)

sorry i took so long to get back|||While i'm on..

could anyone help me with this trigger!!

Create Trigger Maintain_employeedata
On payroll_tbl
After update on employee_tbl
For Each Row
Begin
Insert into payroll_tbl
(payroll_id, employee_id)
Values
(:New.payroll_id, :Old.employee_id);
End;

This does not seem to work, it produces the following errors:

Server: Msg 156, Level 15, State 1, Procedure Maintain_SurveyorData, Line 3
Incorrect syntax near the keyword 'on'.
Server: Msg 170, Level 15, State 1, Procedure Maintain_SurveyorData, Line 9
Line 9: Incorrect syntax near ':'.

Basically when I add a new employee to my employee table it should add this employee to the payroll table via their employee_id.. and subsequently create a new payroll_id to match. the payroll_id is the primary key of the table and should be an increment of the last one (eg if we have got up to P0007, it should create P0008 for the new person).

Is this actually possible? its really bugging me!|||First it looks like you have an Oracle background...

Second, you're inserting in to the same table, after an update to the sane table...I don;t understand this..

Third SQL does not have a FOR EACH ROW syntax, you have to join to the virtual table "inserted" (oracles new) and "deleted (oracles old)..

fourth, the error message is for something else..

CREATE TRIGGER <triiger_name> ON Table

is correct

AFTER UPDATE (which isn't required, is the default) doesn't use the ON Table syntax...|||ah, well actually I don't have a background in any form! that was just syntax i picked up, and i'm only working on MS SQL server using the query analyzer :|

I have these ammendments, but I don't think ive quite grasped what youre saying?

Create Trigger Maintain_employeedata on payroll_tbl
After update on employee_tbl
For Each Row
Begin
Insert into payroll_tbl
(payroll_id, employee_id)
Values
(:inserted.employee_id, :deleted.payroll_id);
End;|||I don't pretend to understand what you're trying to do...

but at least this should compile...

You need to make sure you identify the key of the row...is it employeeId?

CREATE TRIGGER Maintain_employeedata ON Employee_tbl
FOR UPDATE
BEGIN
INSERT INTO payroll_tbl (payroll_id, employee_id)
SELECT i.employee_id, d.payroll_id
FROM inserted i
INNER JOIN deleted d
ON i.key of the row = d.key of the row
END
GO|||yeah ive altered the keys so that they are right. i.employee_id and d.payroll_id

but i have

Server: Msg 156, Level 15, State 1, Procedure Maintain_employeedata, Line 3
Incorrect syntax near the keyword 'BEGIN'.|||I forgot the AS...place in a line before the BEGIN|||It's actually easier to help if you post the DDL of the table, and some sample data, and some sample DML (The Updates statements)

Sample data should look like

INSERT INTO myTable99(col1,col2,col3,ect)
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
ect

DDL looks like

CREATE TABEL mtTable99 (Col1, char(1), col2 int, ect...

You'll get answers that are correct, and fatse that way...

MOO

constraint expression for unique keys

if i have a table which defines a rule as "combination of two field
must be unique", how can I write this in a constraint expression
section?
i started learning more about ms sql side to handle all the necessary
rules in back-end instead of front-end.
also any good learning links, references, or book recommandations?
thanksan excerpt from BOL:

"C. Using UNIQUE constraints
UNIQUE constraints are used to enforce uniqueness on nonprimary key
columns. The following example enforces a restriction that the Name
column of the Product table must be unique.

Copy Code
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED

"|||can you explain what "NONCLUSTERED" is doing there? is that for
non-relation to a field in other table?

Alexander Kuznetsov wrote:
> an excerpt from BOL:
> "C. Using UNIQUE constraints
> UNIQUE constraints are used to enforce uniqueness on nonprimary key
> columns. The following example enforces a restriction that the Name
> column of the Product table must be unique.
> Copy Code
> Name nvarchar(100) NOT NULL
> UNIQUE NONCLUSTERED
> "|||SQL Server implicitly creates an index to implement a uinque
constraint. In this case NONCLUSTERED means the index will be
non-clustered.|||HandersonVA (handersonva@.hotmail.com) writes:
> if i have a table which defines a rule as "combination of two field
> must be unique", how can I write this in a constraint expression
> section?

CONSTRAINT u_tbl UNIQUE (col1, col2)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> if i have a table which defines a rule as "combination of two field must be unique", how can I write this in a constraint expression section? <<

CONSTRAINT unique_location UNIQUE (x, y)
>> any good learning links, references, or book recommandations? <<

I recommend buying all of my books :)