creating triggers in transactional replication on the subscriber side

Hi all

i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?

Thanks,

Arslan.

are these triggers defined at the publisher, or were you wanting to create new triggers? Assuming the latter, you can put them in a SQL file and reference @.post_snapshot_script in sp_addpublication.|||

If what you would like to do is to replicate triggers defined on the published table, you can take a look at @.schema_option 0x100 in sp_addarticle. If you are using UI, there is also a "copy user triggers" option in article properties dialog.

Peng

creating SQL statement

Alright, so let me explain the details first.

I have two tables. One is the default aspnet_users table that themembership class builds. that has GUID, username, lowereduser, and such.

then I have another table called "UserSkills". That stores the GUID of the member, then the skills they have. so in that table i have. userID as GUID, then about 12 languages in 'bit' format.. (thats becuase in the webpage when they fill out there profile, all these are checkboxes. Basically all of the info is here http://www.listofcoders.com/profile.aspx?name=fenixsn. so there are a couple of bit fields, 1 text, and couple of varchars.

anways, so i wanna build a powerful search thingy. where the users have the option to search a user that only does for ex say php, asp, asp.net. and is from location "Canada". ok so when they fill out the info, I want my SQL statement to do the following

search the userskills table for the required fields. there might be more then 1 person that has the same profile, but different GUID. and then maybe using "Join" or another sql statement, grab there username, and last activity date from the users table that memberhship createes.

so in short, how do i make a dynamic sql statement.

anyone can help me out here?|||anyone help me pleas|||

Hi masfenix,

If you're trying to search according to the user input, you will need to use a WHERE clause after SELECT statement.

SELECT * FROM Table1 WHERECountry=@.Country ANDSkill=@.Skill

If the skill is not in the same table, you can use a JOIN

SELECT *,Skill.SkillName FROM Table1 LEFT OUTER JOIN Skill ON Table1.SkillID = Skill.SkillID WHERE .....

In your page code, pass the criterias through parameters and the expected result will be returned.

|||

Hi, I wanna make a SPROC out of this.

how do I write the select statement then RETURN THE DATA (there could be more then 1 row returend).

and how do i read that data and put it in a gridview?

Creating SQL Data base

Hi,
I want to create SQL Data and default user base using Visual Basic. any body help me to create it
thanks for your advise
byeif you have a script file
you can use shell command or winexec api, e.g.

winexec("osql.exe -S -usa -p -iscriptfile", SW_HIDE)

about osql' parameters, to see sql server online help|||You can execute script string in ADODB.Command object.|||The simplest way is:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=myserver"
cn.Execute "create database test"
cn.Close
Set cn = Nothing

You can get more fancy with this. If you will use this more than once I would recommend creating a stored procedure that you would execute. This can also be used if you need to pass parameters like database name, location, size ...

If you are using vb.net take a look at the following ms article:

article (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q305079&ID=kb;en-us;Q305079&SD=MSDN)

Creating small index takes LONG time!

Hi- I am trying to add a simple, non clustered index, using Enterprise
Manager all default settings, to a table with about 30 columns and about 15K
rows of data. The index is on a smalldatetime column. For some reason, when
I
try to add this index, it takes FOREVER (let it go at least 6 hours, still
not completed). Killed process (which had very high processor count),
restarted SS, tried again, no go. No sign of any locks (in fact, no one even
connected to this database). Plenty of disk space.
Any ideas why this might occur? There are several other indexes on the
table, including primary key id column and other indexes referencing the
column I am trying to index as secondary, tertiary column, but never as
primary column. This is a large database with many tables (100+), lots of
data (4GB or so). But, this seems unusual to me...
I can access the table and the data in it from another instance of EM while
I am trying to create this index.
Any ideas?
Thanks,
ChrisEM is notorious for generating inefficient code. It's far better to script
it by hand because:
1) a skilled human will write better code
2) the script can be used elsewhere and be checked into source code control
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
Hi- I am trying to add a simple, non clustered index, using Enterprise
Manager all default settings, to a table with about 30 columns and about 15K
rows of data. The index is on a smalldatetime column. For some reason, when
I
try to add this index, it takes FOREVER (let it go at least 6 hours, still
not completed). Killed process (which had very high processor count),
restarted SS, tried again, no go. No sign of any locks (in fact, no one even
connected to this database). Plenty of disk space.
Any ideas why this might occur? There are several other indexes on the
table, including primary key id column and other indexes referencing the
column I am trying to index as secondary, tertiary column, but never as
primary column. This is a large database with many tables (100+), lots of
data (4GB or so). But, this seems unusual to me...
I can access the table and the data in it from another instance of EM while
I am trying to create this index.
Any ideas?
Thanks,
Chris|||Chris,
Either it is not building the index, or something is seriously wrong.
An index like you describe should be built in seconds.
When I just created an index using Enterprise Manager (within Design
Table) it did not start the process until I closed the Design Table
window, when it prompted me with the question asking if I wanted to
save changes. Did you reach that point?
Roy
On Mon, 27 Feb 2006 17:09:27 -0800, querylous
<querylous@.discussions.microsoft.com> wrote:

>Hi- I am trying to add a simple, non clustered index, using Enterprise
>Manager all default settings, to a table with about 30 columns and about 15
K
>rows of data. The index is on a smalldatetime column. For some reason, when
I
>try to add this index, it takes FOREVER (let it go at least 6 hours, still
>not completed). Killed process (which had very high processor count),
>restarted SS, tried again, no go. No sign of any locks (in fact, no one eve
n
>connected to this database). Plenty of disk space.
>Any ideas why this might occur? There are several other indexes on the
>table, including primary key id column and other indexes referencing the
>column I am trying to index as secondary, tertiary column, but never as
>primary column. This is a large database with many tables (100+), lots of
>data (4GB or so). But, this seems unusual to me...
>I can access the table and the data in it from another instance of EM while
>I am trying to create this index.
>Any ideas?
>Thanks,
>Chris|||Hmmm... looking at the process info, I can see that the query generated by
Enterprise Manager is
CREATE
INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
I can't imagine a much more efficient means of creating an index than that?
Thanks,
Chris
"Tom Moreau" wrote:

> EM is notorious for generating inefficient code. It's far better to scrip
t
> it by hand because:
> 1) a skilled human will write better code
> 2) the script can be used elsewhere and be checked into source code contro
l
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
> Hi- I am trying to add a simple, non clustered index, using Enterprise
> Manager all default settings, to a table with about 30 columns and about 1
5K
> rows of data. The index is on a smalldatetime column. For some reason, whe
n
> I
> try to add this index, it takes FOREVER (let it go at least 6 hours, still
> not completed). Killed process (which had very high processor count),
> restarted SS, tried again, no go. No sign of any locks (in fact, no one ev
en
> connected to this database). Plenty of disk space.
> Any ideas why this might occur? There are several other indexes on the
> table, including primary key id column and other indexes referencing the
> column I am trying to index as secondary, tertiary column, but never as
> primary column. This is a large database with many tables (100+), lots of
> data (4GB or so). But, this seems unusual to me...
> I can access the table and the data in it from another instance of EM whil
e
> I am trying to create this index.
> Any ideas?
> Thanks,
> Chris
>|||Are there any processes blocking it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
Hmmm... looking at the process info, I can see that the query generated by
Enterprise Manager is
CREATE
INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
I can't imagine a much more efficient means of creating an index than that?
Thanks,
Chris
"Tom Moreau" wrote:

> EM is notorious for generating inefficient code. It's far better to
> script
> it by hand because:
> 1) a skilled human will write better code
> 2) the script can be used elsewhere and be checked into source code
> control
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
> Hi- I am trying to add a simple, non clustered index, using Enterprise
> Manager all default settings, to a table with about 30 columns and about
> 15K
> rows of data. The index is on a smalldatetime column. For some reason,
> when
> I
> try to add this index, it takes FOREVER (let it go at least 6 hours, still
> not completed). Killed process (which had very high processor count),
> restarted SS, tried again, no go. No sign of any locks (in fact, no one
> even
> connected to this database). Plenty of disk space.
> Any ideas why this might occur? There are several other indexes on the
> table, including primary key id column and other indexes referencing the
> column I am trying to index as secondary, tertiary column, but never as
> primary column. This is a large database with many tables (100+), lots of
> data (4GB or so). But, this seems unusual to me...
> I can access the table and the data in it from another instance of EM
> while
> I am trying to create this index.
> Any ideas?
> Thanks,
> Chris
>|||Nope, neither blocking nor blocked by...
"Tom Moreau" wrote:

> Are there any processes blocking it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
> Hmmm... looking at the process info, I can see that the query generated by
> Enterprise Manager is
> CREATE
> INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
> I can't imagine a much more efficient means of creating an index than that
?
> Thanks,
> Chris
> "Tom Moreau" wrote:
>
>|||I'm actually using the "manage indexes" window; after specifying the index,
I
click ok, and then another window appears which implies that the index is
being built, and this is where the hang occurs. Also, I know the request has
been sent at that point b/c I can see it as a process. Yes, something is
definitely wrong; I am also used to indexes like this being built in
seconds...
"Roy Harvey" wrote:

> Chris,
> Either it is not building the index, or something is seriously wrong.
> An index like you describe should be built in seconds.
> When I just created an index using Enterprise Manager (within Design
> Table) it did not start the process until I closed the Design Table
> window, when it prompted me with the question asking if I wanted to
> save changes. Did you reach that point?
> Roy
>
> On Mon, 27 Feb 2006 17:09:27 -0800, querylous
> <querylous@.discussions.microsoft.com> wrote:
>
>|||On Mon, 27 Feb 2006 17:46:26 -0800, querylous
<querylous@.discussions.microsoft.com> wrote:

>I'm actually using the "manage indexes" window; after specifying the index,
I
>click ok, and then another window appears which implies that the index is
>being built, and this is where the hang occurs. Also, I know the request ha
s
>been sent at that point b/c I can see it as a process. Yes, something is
>definitely wrong; I am also used to indexes like this being built in
>seconds...
So what happens if you run the same CREATE INDEX in Query Analyzer?
Roy Harvey
Beacon Falls, CT|||Could you please give us the SQL edition, version number, hardware, etc.?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:90639871-203F-4402-ADC0-D5D583E5BAB0@.microsoft.com...
Nope, neither blocking nor blocked by...
"Tom Moreau" wrote:

> Are there any processes blocking it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
> Hmmm... looking at the process info, I can see that the query generated by
> Enterprise Manager is
> CREATE
> INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
> I can't imagine a much more efficient means of creating an index than
> that?
> Thanks,
> Chris
> "Tom Moreau" wrote:
>
>|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows N
T
5.0 (Build 2195: Service Pack 4)
Never had any trouble with it.
"Tom Moreau" wrote:

> Could you please give us the SQL edition, version number, hardware, etc.?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:90639871-203F-4402-ADC0-D5D583E5BAB0@.microsoft.com...
> Nope, neither blocking nor blocked by...
> "Tom Moreau" wrote:
>
>

creating replication and not using default database name

We currently have transactional replication from our ERP system to our
datawarehouse. Our ERP system is being upgraded and will have a new database
name. I am going to break and recreate replication to our datawarehouse. I
would like to change the default database name to be the same as our current
(ie: the old version) database name so we don't have to go update all our
ODBC connection strings. Is there any reason why I should not do this? Any
problems this may cause? Also, I was thinking of not even dropping the old
database on the datawarehouse, but just simply replicating the new table to
this old database. Any red flags there? Thanks so much.
I'm confused, I realize your upgrade path requires you to rename your
database name on your ERP system.
How does the ODBC connection string fit in here? Is it pointing at the ERP
system, or the data warehouse? Replication couldn't care less what the name
of the subscriber database name is. So this shouldn't be a problem. You may
have to edit the publication scripts for the new database name.
One trick for you, the SQL Server account or the NT account used in the ODBC
DSN will have a default database. You should be able to change this on the
publisher or subscriber and avoid having to modify the ODBC DSN's.
You should also be able to leave the old database intact with the same name
on the dataware house/subscriber. Replication will fix the tables there the
way it wants them to be - which will likely be ok for you.
Things to watch out for
1) PK's are replicated as unique indexes which are very similar to PKs (only
they allow a single null). When you are creating your publication and get to
the specify articles dialog box, click on the browse button, snapshot tab,
and select include DRI to replicate the PK's as PK's and pray to your God
sysdepends is accurate on the publisher.
2) triggers on the subscriber will be dropped unless they are replicated
from the publisher (in the snapshot tab as well)
3) normally data warehouses require different sets of indexes on the
subscriber; you may have to rebuild these.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AW" <AW@.discussions.microsoft.com> wrote in message
news:066C2F4C-947A-469B-A0B7-6129584C9987@.microsoft.com...
> We currently have transactional replication from our ERP system to our
> datawarehouse. Our ERP system is being upgraded and will have a new
database
> name. I am going to break and recreate replication to our datawarehouse.
I
> would like to change the default database name to be the same as our
current
> (ie: the old version) database name so we don't have to go update all our
> ODBC connection strings. Is there any reason why I should not do this?
Any
> problems this may cause? Also, I was thinking of not even dropping the
old
> database on the datawarehouse, but just simply replicating the new table
to
> this old database. Any red flags there? Thanks so much.
|||Sorry for the confusion. I was referring to all of our ASP code that hits
the datawh and has the db name in the ODBC connection. It's just a reason
why I want to keep the old name, it doesn't have anything to do with the
actual replication.
Thanks for all your answers. I don't really get #1 though. I've never done
that when I've started replication before. Is it necessary?
"Hilary Cotter" wrote:

> I'm confused, I realize your upgrade path requires you to rename your
> database name on your ERP system.
> How does the ODBC connection string fit in here? Is it pointing at the ERP
> system, or the data warehouse? Replication couldn't care less what the name
> of the subscriber database name is. So this shouldn't be a problem. You may
> have to edit the publication scripts for the new database name.
> One trick for you, the SQL Server account or the NT account used in the ODBC
> DSN will have a default database. You should be able to change this on the
> publisher or subscriber and avoid having to modify the ODBC DSN's.
> You should also be able to leave the old database intact with the same name
> on the dataware house/subscriber. Replication will fix the tables there the
> way it wants them to be - which will likely be ok for you.
> Things to watch out for
> 1) PK's are replicated as unique indexes which are very similar to PKs (only
> they allow a single null). When you are creating your publication and get to
> the specify articles dialog box, click on the browse button, snapshot tab,
> and select include DRI to replicate the PK's as PK's and pray to your God
> sysdepends is accurate on the publisher.
> 2) triggers on the subscriber will be dropped unless they are replicated
> from the publisher (in the snapshot tab as well)
> 3) normally data warehouses require different sets of indexes on the
> subscriber; you may have to rebuild these.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "AW" <AW@.discussions.microsoft.com> wrote in message
> news:066C2F4C-947A-469B-A0B7-6129584C9987@.microsoft.com...
> database
> I
> current
> Any
> old
> to
>
>
|||Probably not. Some apps do want to see PK's. Not many of them though, and if
they do they are probably badly written.
I think you should be ok changing the name of your ERP database, but keeping
the old name of your DataWarehouse database.
Next time you build an ODBC connection, don't specify a database name, or
use default database account to handle the database connection for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AW" <AW@.discussions.microsoft.com> wrote in message
news:13877D10-371E-4632-842F-35192B8E4CC5@.microsoft.com...
> Sorry for the confusion. I was referring to all of our ASP code that hits
> the datawh and has the db name in the ODBC connection. It's just a reason
> why I want to keep the old name, it doesn't have anything to do with the
> actual replication.
> Thanks for all your answers. I don't really get #1 though. I've never
done[vbcol=seagreen]
> that when I've started replication before. Is it necessary?
> "Hilary Cotter" wrote:
ERP[vbcol=seagreen]
name[vbcol=seagreen]
may[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
name[vbcol=seagreen]
the[vbcol=seagreen]
(only[vbcol=seagreen]
get to[vbcol=seagreen]
tab,[vbcol=seagreen]
God[vbcol=seagreen]
datawarehouse.[vbcol=seagreen]
our[vbcol=seagreen]
this?[vbcol=seagreen]
the[vbcol=seagreen]
table[vbcol=seagreen]

Creating new instances of SQL Server 2005 Express

Greetings,
I am trying to create new instances of sqlserver express (while the
default SQLEXPRESS is still there). How can i do that?
I'm trying to set up a development and production instance in one
machine. is this a right way to do it?
please advice.cerebellum,
Run the SQLEXPRESS installation again, during the installation time setup
will ask for an instance name., give the new instance name there
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"cerebellum" <wilsoneden@.gmail.com> wrote in message
news:1179906209.618347.241560@.p77g2000hsh.googlegroups.com...
> Greetings,
> I am trying to create new instances of sqlserver express (while the
> default SQLEXPRESS is still there). How can i do that?
> I'm trying to set up a development and production instance in one
> machine. is this a right way to do it?
> please advice.
>|||am i doing the right thing? do people really do this? as in separating
development and production with the sql server instance?
On May 23, 3:49 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
> cerebellum,
> Run the SQLEXPRESS installation again, during the installation time setup
> will ask for an instance name., give the new instance name there
> regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/
> "cerebellum" <wilsone...@.gmail.com> wrote in message
> news:1179906209.618347.241560@.p77g2000hsh.googlegroups.com...
> > Greetings,
> > I am trying to create new instances of sqlserver express (while the
> > default SQLEXPRESS is still there). How can i do that?
> > I'm trying to set up a development and production instance in one
> > machine. is this a right way to do it?
> > please advice.|||Hi
Actually , it will be better if you would have a separated server for
developing. Make sure that you have enough memory on the server as each
instance will consume memory and it may hurt performance
"cerebellum" <wilsoneden@.gmail.com> wrote in message
news:1179988261.600407.241950@.a26g2000pre.googlegroups.com...
> am i doing the right thing? do people really do this? as in separating
> development and production with the sql server instance?
> On May 23, 3:49 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
>> cerebellum,
>> Run the SQLEXPRESS installation again, during the installation time setup
>> will ask for an instance name., give the new instance name there
>> regards
>> VT
>> Knowledge is power, share it...http://oneplace4sql.blogspot.com/
>> "cerebellum" <wilsone...@.gmail.com> wrote in message
>> news:1179906209.618347.241560@.p77g2000hsh.googlegroups.com...
>> > Greetings,
>> > I am trying to create new instances of sqlserver express (while the
>> > default SQLEXPRESS is still there). How can i do that?
>> > I'm trying to set up a development and production instance in one
>> > machine. is this a right way to do it?
>> > please advice.
>