Replication cursor [modified] - Database Discussion Boards

From what you describe I don't think you need intermediate tables. A compound primary key on the note table might suffice, assuming you don't have one note that needs to be shared among multiple other tables.

The MainId would be what other tables use to link to the notes - so it uniquely defines a group of notes. The SecondaryId is just to uniquely identify each note within the group.

Perhaps that might work. If your system is going to be busy and there may be the scenario where two processes want to create a new group of notes simultaneously then I'd recommend a GUID for the MainId because they won't collide as you cannot use an Identity because it would cause problems when you wanted to put a second note in an existing group and you can't use MAX(MainId) because two processes might try the same thing at the same time.

Your other option is intermediate tables. I don't really think they clutter up the place, but if your think they do, then you could always prefix their name with something to keep them out of the way.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Rob Philpott wrote:
What's the weather like in Scotland? It's been a bit stormy down in London over the past few days.

The rain this afternoon was magnificent. It was almost Mediterranian in its ferocity. It was the same yesterday afternoon just as the sun was setting.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

HI

Guys can any one let me know, which is the largest data type in SQL Server, plz reply ASAP,thankx in advance.

Check this[^]

"image" max length 2^31-1 bytes

Note that starting at SQL Server 2005, "image" is deprecated.
Use "varbinary(max)" (max length 2^31-1 bytes) instead.

Mark

Sir,
I am trying to create a stored procedure in the database in sql server 2005.I have written for the stored procedure .But when i am trying to save it , then the saved stored procedure file doesn't come under the stored procedure hierarchy.and abother problem is when i try to access the stored procedure using asp.net2.0 I mean the saved stored procedure it says the stored procedure i am accessing does not exist.
Please help.

VS 2003/2005,
when you edit a procdure, "save" means create/alter

SQL Server 2005 Management Studio,
if you want to create a procedure, writing code first then run and create the proc

I am adding articles to be replicated but I would like to use a cursor to do it due the amount of tables it may take forever to select all from the wizard. I have written a cursor and I am having issues running it. Any suggestion will be greatly appreciated.

Cursor:

declare @tableName varchar(100)
declare c cursor for
select
table_name
from
information_schema.tables

where table_type = 'base table'

open c

fetch next from c into @tableName

while @@fetch_status = 0

begin

--print 'table name: ' + @tableName

--Adding the transactional articles
exec sp_addarticle @publication = N'One_Way_Trans', @article = @tableName , @source_owner = N'dbo', @source_object = @tableName, @destination_table = @tableName, @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000CEF3, @status = 16, @vertical_partition = N'false', @ins_cmd ='CALL sp_MSins_' + @tableName, @del_cmd = N'CALL sp_MSdel_' + @tableName, @upd_cmd = N'MCALL sp_MSupd_' + @tableName, @filter = null, @sync_object = null, @auto_identity_range = N'false'

fetch next from c into @tableName

end
close c
deallocate c

I am getting the following error when I run this script: Line 99: Incorrect syntax near '+'.

Here are the values I can not populate with @tableName.

@ins_cmd ='CALL sp_MSins_' + @tableName, @del_cmd = N'CALL sp_MSdel_' + @tableName, @upd_cmd = N'MCALL sp_MSupd_' + @tableName,

PS: I got the code below to do exactly what I want it to do but notice it is only printing the reults not executing the stored procedure.

declare @tableName varchar(100)
declare c cursor for

select
top 10 table_name
from
information_schema.tables

where table_type = 'base table'

open c

fetch next from c into @tableName

while @@fetch_status = 0

begin

--print 'table name: ' + @tableName

--Adding the transactional articles
print 'exec sp_addarticle @publication = N''One_Way_Trans'', @article = N ''' + @tableName + ''' , @source_owner = N''dbo'', @source_object = N''' + @tableName + ''', @destination_table = N'''+ @tableName + ''', @type = N''logbased'', @creation_script = null, @description = null, @pre_creation_cmd = N''drop'', @schema_option = 0x000000000000CEF3, @status = 16, @vertical_partition = N''false'', @ins_cmd = N''CALL sp_MSins_' + '' + @tableName + ''', @del_cmd = N''CALL sp_MSdel_' + ''+ @tableName + ''', @upd_cmd = N''MCALL sp_MSupd_' + '' + @tableName + ''', @filter = null, @sync_object = null, @auto_identity_range = N''false'''

fetch next from c into @tableName

end

close c

deallocate c

-- modified at 22:23 Friday 29th September, 2006

Greg

Coding makes the world go round!!!

Never Mind, I got it working. What I did was I ran the "print" script, pulled copied the result and rn it in Quer Analzer. It ran just fine and created all my Articles.

PS: I could not believe I was such a dork. But then again no where tells you that you need to run one script to create another.

Thanks guys.

OBTW: It would be nice if someone can explain wh I can not add a variable name to a stored procedure dnamically.

Greg

Coding makes the world go round!!!

how can i save the value of a field into a parameter in T-SQL?what i wrote is-

Create Procedure a_c
@C_Name varchar(100),
as
if exists(
select id as 'ID' from ABC where C_Name = @C_Name)
begin
----
end
else
begin
---
end
go

is this syntax correct?and should i declare 'ID'and how can i do it?

-- create proc
create proc abc
@para int output
as
set @para = 1
go

--exec proc
declare @p int

exec abc @p output

select @p

I have this info

Desc1................Desc2
-------------......----------
BudgetControl......RamonDilan
DirectorContr......JoseAleman

I wanna to show

Desc1................Desc2.............Desc1.............Desc2
-------------......----------......-------------...---------
BudgetControl......RamonDilan......DirectorContr...JoseAleman

Please help me on that.. Thanks

johnny

Try something like this:

<<tr>
<asp:repeater>
<td>Desc1 <br/><asp:Label ID="Desc1></td>
<td>Desc2 <br/><asp:Label ID="Desc2></td>
</asp:repeater>
</tr>

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

Hi
Thanks for your response. But I using SQL Statements. I'm using SQL 2000 server in Query Analyser. Do you have any idea?.

Johnny

There are two options, using T-SQL to dynamically generate the query or use a join and eliminate null records.

If the join will work it is the best option as it is fast. The T-SQL option is usually ugly.

On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage

Im returning ratios with rounded off to 3 decimals places, but when the rounding off function doesnt get rid of of the trailing 10 zeroes. How do I get rid of the trailing zeros because it shows in SQL reports. I have tried TRUNCNUM, ROUND, TRUNC. I just cant seem to get rid of those zeroes. Help would much be appreciated.

Hi!

Try FormatNumber function.... see the example below. If you are not going to define 0 in FormatNumber statement, it will show decimal places.

NewNumber = FormatNumber(OldNumber,0)

thanks bro, but i sorted it out now. first casted it to a 'real' number then used the 'round' function. and it worked. all trailing zeroes are now removed

hi all
i have a colunm in my DB ( nvarchar (50)) i use it to store customer's e-mail but this column takes only the first 20 Cahrs from the user's input
i tried to make it 100 in length but nothing work
plz tell me wht to do
thank u

abdelhameed81

You have a problem in your client-side application. Either the text is being truncated to 20 characters somewhere in that application, or you are using a SqlParameter object (or other similar object appropriate for your database and development language/platform) with size 20.

Stability. What an interesting concept. -- Chris Maunder

What is SQL injection, and how it occurs,

How can we rectify the SQL injection

-

A quick search of code project reveals SQL Injection Attacks and Some Tips on How to Prevent Them[^]

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Difference between DELETE, TRUNCATE, DROP

-

DELETE removes rows from a table and ensures that referential integrity is maintained, triggers are run and so on.

TRUNCATE just removes the rows and to hell with the consequences

DROP removes an object (like a table, stored procedure, function, view, etc.) from the database

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

I'd add to Colin's post that you can recover from DELETE with a rollback, while TRUNCATE and DROP do not provide any mechanism to recover.

Chris Meech

I am Canadian. [heard in a local bar]

Nobody likes jerks. [espeir]

The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]

I wish I could remember what it was like to only have a short term memory.[David Kentley]