creating views in mssql

hey guys,
can you share an example of how I can create views in MSSQL where the table
is stored in oarcle server.
thanks guys.
neil
Hi,
For that you have to create a linked server to the Oracle server.
see this link to get info on creating linked Server to ORACLE
http://support.microsoft.com/default...b;EN-US;280106
After that create the view from sql server .
Sample:
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
Thanks
Hari
MCDBA
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> hey guys,
> can you share an example of how I can create views in MSSQL where the
> table is stored in oarcle server.
> thanks guys.
> neil
>
|||Hi Hari,
Thanks for the link. I'm gonna try it tomorrow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eEf6BjYqEHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> For that you have to create a linked server to the Oracle server.
> see this link to get info on creating linked Server to ORACLE
> http://support.microsoft.com/default...b;EN-US;280106
> After that create the view from sql server .
> Sample:
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
>
> Thanks
> Hari
> MCDBA
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
>

creating views in mssql

hey guys,
can you share an example of how I can create views in MSSQL where the table
is stored in oarcle server.
thanks guys.
neilHi,
For that you have to create a linked server to the Oracle server.
see this link to get info on creating linked Server to ORACLE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
After that create the view from sql server .
Sample:
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
Thanks
Hari
MCDBA
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> hey guys,
> can you share an example of how I can create views in MSSQL where the
> table is stored in oarcle server.
> thanks guys.
> neil
>|||Hi Hari,
Thanks for the link. I'm gonna try it tomorrow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eEf6BjYqEHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> For that you have to create a linked server to the Oracle server.
> see this link to get info on creating linked Server to ORACLE
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> After that create the view from sql server .
> Sample:
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
>
> Thanks
> Hari
> MCDBA
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
>> hey guys,
>> can you share an example of how I can create views in MSSQL where the
>> table is stored in oarcle server.
>> thanks guys.
>> neil
>

Creating Views

Hai Guys,
Can i create a view by joining 2 tables that r on two different database.
Good Day.
Thanks.
Shabuyes, it's possible.|||How do i do this.
I have one server with 2 databases.
my DB server is Sqlserver2000.

Please Help.

Thanks .|||This should probably do it, if you have linked the servers ...

create view NameOfTheView as
select someField, someOtherField
from
ServerName1.DBName.dbo.TableName Server1,
ServerName2.DBName.dbo.TableName Server2,
where
Server1.Field1 = Server2.Field1 and
<insert some other suitable conditions here ...>|||Something like:SELECT s.sysadmin, d.owner
FROM msdb.dbo.sysdtspackages AS d
JOIN master.dbo.syslogins AS s
ON (s.name = d.owner)-PatP|||you need to run sp_addlinkedserver for the remote server
then run sp_addlinkedsrvlogin to create login maps
then invoke the four part name in the query

BOOKS ONLINE{sp_addlinkedserver}
BOOKS ONLINE{sp_addlinkedsrvlogin}
BOOKS ONLINE{Distributed Queries}

this can be done easily in the enterprise manager
SECURITY>LINKED SERVERS

this is kind of the way a partitioned view is created check out federated servers in books online|||you need to run sp_addlinkedserver for the remote server
then run sp_addlinkedsrvlogin to create login mapsDid I miss a meeting somewhere? When did a second server join this discussion?

-PatP|||Did I miss a meeting somewhere? When did a second server join this discussion?

-PatP

whoops
my bad

ignore my pre-post-question post that i had just posted

Creating stripped accounts for accessing MS SQL Server

Hi there,
I tried to find an answer to my question but I wasn't able to find it on the Web. Maybe you guys could help me out.
I have an installation of MS SQL 2000. I made a new login on the database with only "select" permissions on a few views in one database. But when I use the account with a copy of SQL Server Manager, all databases on the instance of SQL Server are visible as well as the log files, DTS'es et cetera. Is there a way to 'strip' the account so only the views I want are visible and nothing more?
TIA,
Michiel
Leiden, The Netherlands.Unfortunately No. Enterprise Manager by default displays all databases,
though you don't have access to all of them.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"MiKE" <anonymous@.discussions.microsoft.com> wrote in message
news:B9031289-38B7-49E7-8A2D-8FF180EE20C3@.microsoft.com...
Hi there,
I tried to find an answer to my question but I wasn't able to find it on the
Web. Maybe you guys could help me out.
I have an installation of MS SQL 2000. I made a new login on the database
with only "select" permissions on a few views in one database. But when I
use the account with a copy of SQL Server Manager, all databases on the
instance of SQL Server are visible as well as the log files, DTS'es et
cetera. Is there a way to 'strip' the account so only the views I want are
visible and nothing more?
TIA,
Michiel
Leiden, The Netherlands.|||>--Original Message--
>Unfortunately No. Enterprise Manager by default displays
all databases,
>though you don't have access to all of them.
And is there something to do to prevent the user to view
the DTSes that are on the server, or to prevent the user
from making new ones?
thanks again,
Michiel

Creating SSIS Package to Extract Data from MySQL and Append to SQL 2005

Hi guys,

I'm a newbie DBA and i'm trying to create a package that would extract data from MySQL and inserts them to a SQL 2005 Server. I'm quite new to this SSIS and would like to ask help from you to help me go through with this.

I hope you guys can help me with this.

Hoping to hear from you soon.

Thank you so much.

Kind regards,
Neil
I'm sure everyone on the forum will be happy to help. I'd suggest starting by going through the SSIS Tutorials in SQL Server Books Online. Then, as you encounter specific problems or design questions, we'll be happy to answer them.|||

Yep, I agree with John.

Just a hint though, you'll be using a DataReader Source against your MySQL ODBC driver to retrieve the results.

|||

Actually, i have gone through the BOL tutorial and have started creating the SSIS package using DataReader Source. i then provided the connection manager which is in my case using ADO.Net connection (MySQL ODBC 3.51 Driver) but as soon as i enter the SQLCommand and click ok this error displays on the bottom part of the Advanced Editor for DataReader Source.

Error at Data Flow Task [DataReader Source[1]]: System.Data.Odbc.OdbcException: ERROR [HY010]

[MySQL][ODBC 3.51 Driver][mysqld-4.1.12-Debian_1ubuntu3.4-log]

What do u think is causing this?

Thanks,

Neil

|||

If you could provide the query, that would help troubleshoot the issue.

If you are trying to use parameters or a complex query, try building it in a variable and using an expression on the data flow to set the SQL source. Expressions for the data flow can be found by clicking the data flow, opeing the properties window (F4), and looking for the expressions item.

|||

Below is the SQLCommand i am using. What's troubling me is that the DTS for this in SQL 2000 is running smoothly that is why i was thinking maybe i am using the wrong steps in SSIS.

SELECT chatrequestlogs.`chat_session`,
chat_admin.`name`,
chatrequestlogs.`ip`,
FROM_UNIXTIME(chatrequestlogs.created) AS Time_Requested,
FROM_UNIXTIME(chattranscripts.created) AS Time_Created,
chatrequestlogs.status,
LTRIM(substring(chattranscripts.plain, 1, locate(':',chattranscripts.plain, 1) - 1)) AS Name_Registered,
LTRIM(substring(substring(chattranscripts.formatted, locate('<question>', chattranscripts.formatted,1), locate('</question>', chattranscripts.formatted, locate('<question>', chattranscripts.formatted, 1)) - locate('<question>', chattranscripts.formatted, 1)),11)) AS Question,
LTRIM(substring(chattranscripts.plain, locate(':', chattranscripts.plain, 1) + 1)) AS Comments,
chatrequestlogs.deptID,
substring(substring(formatted, locate('<tstamp (', formatted), locate(') tstamp>', formatted) - locate('<tstamp (', formatted)), 10) AS Time_Started,
reverse(substring(substring(reverse(formatted), locate('>pmatst )', reverse(formatted)), locate('( pmatst<', reverse(formatted)) - locate('>pmatst )', reverse(formatted))), 10)) AS Time_Ended,
chat_admin.`email` AS CSCEmail,
chattranscripts.`email` AS CustomerEmail
FROM chatrequestlogs
JOIN chat_admin ON chatrequestlogs.userID = chat_admin.`userID`
JOIN chattranscripts ON chattranscripts.chat_session = chatrequestlogs.chat_session
WHERE FROM_UNIXTIME(chatrequestlogs.created) >= DATE_ADD(NOW(), INTERVAL - '1440' MINUTE)
AND FROM_UNIXTIME(chatrequestlogs.created) < NOW()

|||

Hi guys,

The problem went away after I turned the ValidateExternalMetadata property of the datasource reader to false.

Thank you so much for the replies.

Forums like these are of great help to newbies like me.

Keep up the good work guys.

Kind regards,

Creating SQL Server Stored Procedures in Deployment

Hey guys,

I'm having a problem. I've been given a task to complete. I was given a database, and asked to wrap a website around it with certain functionality. I did this, and added seven stored procedures in the proccess. Everything works, Business Logic Layer, Data Access Layer, error validation, even screwed around with SQL-injection protection. Lovely, yes?

However, when my little website gets tested, it's going to be plugged into a fresh database - the exact same database I was given, only with different data in the tables. My stored procedures won't be in that database. I can detect if my stored procedure doesn't exist easily enough by catching the error at runtime and checking the code.

I would like to create the stored procedure inside that catch block. I just don't know how.

The easy answer is just to use embedded SQL in my application instead of stored procedures. This isn't a commercial application, it's just a task I've been given to test my abilities. But embedded SQL is icky. I'd rather do it properly.

With the help of the executenonquery u may create the stored procedures which is not the best way. stored procedures,functions or triggers creation means generally people will implement in database not through front end. Not only in this case u can perform ddl commands through executenonquery but max no one will prefer.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

Creating relationships between tables in different databases

Hello guys,
Does anybody know how can i create a relationship between tables in differen
t databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different d
atabases and i'd like to have some kind of referencial integrity in the data
that is stored on the separated databases by using the same shared tables.
I could use triggers for do
ing that but this will decrease the performance of my databases dramatically
.
Thanks for the help,
Paulo R.Cross database constraints are not supported. You would need
to go the route along the lines of triggers to achieve
something like this.
-Sue
On Mon, 29 Mar 2004 13:41:08 -0800, "paulo"
<anonymous@.discussions.microsoft.com> wrote:

>Hello guys,
>Does anybody know how can i create a relationship between tables in differe
nt databases? Does SQL Server supports this kind of operation?
>The main problem is that i have some data that is shared between different database
s and i'd like to have some kind of referencial integrity in the data that is stored
on the separated databases by using the same shared tables. I could use triggers fo
r d
oing that but this will decrease the performance of my databases dramatically.d">
>Thanks for the help,
>Paulo R.

Creating relationships between different databases

Hello guys,
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Even if SQL Server did support it (which I don't believe it does) it would
be a very poor design. What if the databases were installed on separate
servers? That would be a real mess. If the tables are that important for a
database then some sort of replication from one database to the other would
be a better solution.
Jim
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:4B7733D7-AFCE-4992-9734-59C7FA38194A@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||>>Does SQL Server supports this kind of operation?<<
Cross database ref. integrity is not supported in SQL Server.
BOL: "FOREIGN KEY constraints can reference only tables within the same database on the same
server. Cross-database referential integrity must be implemented through triggers. For more
information, see CREATE TRIGGER. "
So this can be accomplished through triggers.
See following topic in bol.
"FOREIGN KEY Constraints"
Vishal Parkar
vgparkar@.yahoo.co.in

Creating relationships between different databases

Hello guys,
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Triggers work, but as you point out there are performance problems.
You can't do cross database constraints or you will get a message like:
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'master.dbo.jobs'.
I'm not sure what to suggest other than triggers.
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:A4BC16DB-C2BB-4EC9-88C4-284F0BA4E957@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||Paulo,
if you really want this functionality, then you could enforce the constraints in your stored procedures, assuming database access is via sps, or use triggers, or use filegroups. This last option is unconventional and in my opinion not ideal, but it is pos
sible to put all your tables into one database and declare constraints there. What were databases now become filegroups. You can back up individual filegroups like you back up databases. I know one financial services institution that uses this methodology
. However, this is limiting - there is no posibility of a point-in-time restore and you need to be able to get hold of the last transaction log if you want to do a full restore. However, if you only use database backups, then this is an option to consider

Creating random numbers

Guys, I wanted to create random numbers for some strange purpose using SQL
Server 2000. Even if the numbers are going to repeat its fine. [Pls note I
don't want to use newid() ].
For ex: I want to pass two integer values say, 10, 30 ... now i expect
randoms to be generated between these two numbers. How do to it in SQL
Server. Can any one help me in this?
Regards
SqlBeginnerdeclare @.id int
SET @.id=0
-- create a random sting
SELECT @.id = RAND()*30
SELECT CASE WHEN @.id <10 THEN @.id + 10
WHEN @.id >30 THEN @.id - 10
ELSE @.id END AS Val
Regards
Amish Shah|||declare @.a int,@.b int
set @.a = 30
set @.b = 40
select @.a + cast((@.b - @.a) * rand() as int)|||Check out http://www.drdev.net/article07.htm
Extract from that link for your easy reference:
-- Create the variables for the random number generation
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
-- This will create a random number between 1 and 999
SET @.Lower = 1 -- The lowest random number
SET @.Upper = 999 -- The highest random number
SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
To adjust the upper and lower limit of the random number simply change the
values of @.Lower and @.Upper
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"Omnibuzz" wrote:

> declare @.a int,@.b int
> set @.a = 30
> set @.b = 40
> select @.a + cast((@.b - @.a) * rand() as int)|||Doesn't that formula exclude 999 from the random number pool so that the
highest random number generated will be 998?
Ta,
M. E. Houston
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:9CFD8D24-E6A5-42CD-BDFB-363081B22FBE@.microsoft.com...
> Check out http://www.drdev.net/article07.htm
> Extract from that link for your easy reference:
> -- Create the variables for the random number generation
> DECLARE @.Random int;
> DECLARE @.Upper int;
> DECLARE @.Lower int
> -- This will create a random number between 1 and 999
> SET @.Lower = 1 -- The lowest random number
> SET @.Upper = 999 -- The highest random number
> SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
> To adjust the upper and lower limit of the random number simply change the
> values of @.Lower and @.Upper
> Hope this helps!
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Omnibuzz" wrote:
>|||The first problem is that there are two kinds of random selection from
a set:
1) With replacement = you can get multiple copies of the same value.
This is shooting dice.
This one is easy if you have a random function in your SQL product.
Most of the pseudo-random generators return a floating point fraction
value between 0.00 and 0.9999... at whatever precision your SQL engine
has. The choice of a seed to start the generator can be the system
clock or some other constantly changing value.
SELECT S1.key_col
FROM SomeTable AS S1, SomeTable AS S2
WHERE S1.key_col <= S2.key_col
GROUP BY S1.key_col
HAVING COUNT(S2.key_col)
= (SELECT COUNT(*)
FROM SomeTable AS S3) * RANDOM(seed) + 1.0;
Or you can add a column for this.
CREATE TABLE RandNbrs2
(seq_nbr INTEGER PRIMARY KEY,
randomizer FLOAT -- warning !! not standard SQL
DEFAULT (
(CASE (CAST(RAND() + 0.5 AS INTEGER) * -1)
WHEN 0.0 THEN 1.0 ELSE -1.0 END)
* (CAST(RAND() * 100000 AS INTEGER) % 10000)
* RAND())
NOT NULL);
INSERT INTO RandNbrs2 VALUES (1, DEFAULT);
INSERT INTO RandNbrs2 VALUES (2, DEFAULT);
INSERT INTO RandNbrs2 VALUES (3, DEFAULT);
INSERT INTO RandNbrs2 VALUES (4, DEFAULT);
INSERT INTO RandNbrs2 VALUES (5, DEFAULT);
INSERT INTO RandNbrs2 VALUES (6, DEFAULT);
INSERT INTO RandNbrs2 VALUES (7, DEFAULT);
INSERT INTO RandNbrs2 VALUES (8, DEFAULT);
INSERT INTO RandNbrs2 VALUES (9, DEFAULT);
INSERT INTO RandNbrs2 VALUES (10, DEFAULT);
SELECT * FROM RandNbrs2;
2) Without replacement = you can each value only once. This is dealing
playing cards.
This is trickier. I would start with a table that has the keys and a
sequentially numbered column in it:
CREATE TABLE CardDeck
(keycol <datatype> NOT NULL PRIMARY KEY,
seq INTEGER NOT NULL);
INSERT INTO CardDeck (keycol, seq)
SELECT S1.keycol, COUNT(S2.keycol)
FROM SomeTable AS S1, Sometable AS S2
WHERE S1.key_col <= S2.key_col
GROUP BY S1.key_col;
Now shuffle the deck by determing a random swap pair for all the rows:
BEGIN
DECLARE i INTEGER, j INTEGER;
SET i = (SELECT COUNT(*) FROM CardDeck);
WHILE i < 0
LOOP
SET j = (SELECT COUNT(*) FROM CardDeck) * RANDOM(seed) + 1.0;
UPDATE CardDeck
SET seq = CASE WHEN seq = i THEN j
WHEN seq = j THEN i
ELSE seq END;
WHERE seq IN (i, j);
SET i = i - 1;
LOOP END;
END;
You don't really need j, but it makes the code easier to read.
Biography:
Marsaglia, G and Zaman, A. 1990. Toward a Univesal Random Number
Generator. Statistics & Probability Letters 8 (1990) 35-39.
Marsaglia, G, B. Narasimhan, and A. Zaman. 1990. A Random Number
Generator for PC's. Computer Physics Communications 60 (1990) 345-349.
Leva, Joseph L. 1992. A Fast Normal Random Number Generator. ACM
Transactions on Mathematical Software. Dec 01 1992 v 18 n 4. p 449
Leva, Joseph L. 1992. Algorithm 712: A Normal Random Number Generator.
ACM Transactions on Mathematical Software. Dec 01 1992 v 18 n 4. p 454
Bays, Carter and W.E. Sharp. 1992. Improved Random Numbers for Your
Personal
Computer or Workstation. Geobyte. Apr 01 1992 v7 n2. p 25
Hulquist, Paul F. 1991. A Good Random Number Generator for
Microcomputers.Simulation. Oct 01 1991 v57 n 4. p 258
Komo, John J. 1991. Decimal Pseudo-random Number Generator. Simulation.
Oct 01 1991 v57 n4. p 228
Chambers, W.G. and Z.D. Dai. 1991. Simple but Effective Modification to
a Multiplicative Congruential Random-number Generator. IEEE
Proceedings.Computers and Digital Technology. May 01 1991 v 138 n3. p
121
Maier, W.L. 1991.. A Fast Pseudo Random Number Generator. Dr. Dobb's
Journal.May 01 1991 v17 n 5. p 152
Sezgin, Fatin. 1990. On a Fast and Portable Uniform Quasi-random Number
Generator. Simulation Digest. Wint 1990 v 21 n 2. p 30
Macomber, James H. and Charles S. White. 1990. An n-Dimensional Uniform
Random Number Generator Suitible for IBM-Compatible Microcomputers.
Interfaces. May 01 1990 v 20 n 3. p 49
Carta, David G. 1990. Two Fast Implementations of the "Minimal
Standard" Random Number Generator. Communications of the ACM. Jan 01
1990 v 33 n 1. p 87
Elkins, T.A. 1989. A Highly Random-number Generator. Computer
Language. Dec 01 1989 v 6 n 12 p 59
Kao, Chiang. A Random Number Generator for Microcomputers. OR: The
Journal of the Operational Research Society. Jul 01 1989 v 40 n 7. p
687
Chassing, P. 1989. An Optimal Random Number Generator Zp. Statistics &
Probability Letters. Feb 01 1989 v 7 n 4. p 307
Also, you can contact Kenneth G. Hamilton 72727,177 who has done some
work with RNG's. He has implemented one (at least one) of the best.
"A Digital Dissolve for Bit-Mapped Graphics Screens" by Mike Morton in
Dr.Dobb's Journal, November 1986, page 48.
CMOS Cookbook by Don Lancaster; Sams 1977, page 318.
Art of Computer Programming, Volume 2: Seminumeral Algorithms, 2nd
edition by Donald Knuth; Addison-Wesley 1981; page 29.
Numerical Recipes in Pascal: The Art of Scientific Computing by Press
et al.; Cambridge 1989; page 233.|||dear celko, if you had read the question, you would see that teh
gentleman doesn't mind duplicates, and he wants his results between two
parameters.
your solutions don't solve any of that.
Regarding your second "solution", wouldn't it be if you could do
this set based?
oh. Wait, You can.
make seq real instead of integer.
for the second answer, UPDATE CardDeck
SET seq = RANDOM(seq)
Select * from carddeck order by seq
It is somewhat interesting that there has been no advancement in random
numbers since the year 1992.
regards,
doug

Creating MSins / MSdel / MSupd

Hi Guys,
Need you help on this one.
I have a eplicated database that is rather large (1GB+), and I am getting
more and more requests to alter articles in the existing publication. As
there is lots of data I am having to carry out the following:
1.) drop the article
2.) drop the subscription of the article
3.) alter the table at the publisher
4.) add the article via t-sql
5.) add the subscription via t-sql
However, ... I am always left manually editing the MSins / MSdel and MSupd
stored procedures.
Is there an easy method for creating these automatically one an article has
been 'tweaked' so that the replication stored procedures are always correct
?
Any help on this would be greatly appreciated.
Regards,
David Mash
you should be using sp_repladdcolumn or sp_repldropcolumn for making schema
changes.
If for whatever reason you can't you can always quickly create a dummy
publication holding your article you just modified and then use
sp_scriptcustomprocs
for instance here is an example of doing this for the authors table
use pubs
sp_addpublication 'dummy'
go
sp_addarticle 'dummy','authors','authors'
go
sp_scriptpublicationcustomprocs dummy
go
--in the results pane copy out the procs and apply them on your subscriber
sp_droppublication 'dummy'
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SplinX" <davidfmash@.hotmail.com> wrote in message
news:cjjukc$uoa$1@.kermit.esat.net...
> Hi Guys,
> Need you help on this one.
> I have a eplicated database that is rather large (1GB+), and I am getting
> more and more requests to alter articles in the existing publication. As
> there is lots of data I am having to carry out the following:
> 1.) drop the article
> 2.) drop the subscription of the article
> 3.) alter the table at the publisher
> 4.) add the article via t-sql
> 5.) add the subscription via t-sql
> However, ... I am always left manually editing the MSins / MSdel and MSupd
> stored procedures.
> Is there an easy method for creating these automatically one an article
has
> been 'tweaked' so that the replication stored procedures are always
correct
> ?
> Any help on this would be greatly appreciated.
> Regards,
> David Mash
>