Pedro,

Sorry. I wasn't clear. The locks on systems tables will be released if your procedure fails. Don't worry, you can't leave any locks lying around.

But your error handling code isn't...

I'll probably miss some out, but when creating a table rows are added to sysobjects, syscolumns and sysindexes. There are probably more. So running a create table inside a transaction...

You’re almost correct. When compiling a stored procedure, SQL Server does not try to parse strings in EXEC statements. That happens when the stored procedure executes the string. Hence there...

Try executing the update statement which is failing as a dynamic SQL string. That way at the time it is executed the column will exist on your table.

Mike

Backing up the Log file won't cause replication a problem. Transactions which require replication will remain in the Log file until the Log Reader Agent has marked them as replicated....

Blast! Foiled again. In that case your idea of a table with the relevant columns and data is a perfectly reasonable thing to do. You can then build up a...

I haven't used OPENQUERY a lot in my time but this should work. Use a generic name for your linked server, e.g. ERPSERVER and then, after creating it in all...

You get the error because the value between your two date is bigger than the maximum value allow for an integer.

Try this instead

declare@dt1datetime,

@dt2datetime

set @dt1 = '2007-11-09 13:23:25.000'

set @dt2 = '2007-11-09...

J-F,

You're welcome. I've obviously not entirely understood your problem. You should do as you say with InvoiceDetails table and put the MfrId and CustomerId in there. Then, as I've suggested,...

You could adopt your "old" developers approach but it's not what I'd do. To me, keeping the

history of your MfrCode in your Invoice table is wrong as you have identified....

The answer to your question is "yes", given the information you have supplied. We have to do it because of distribution agent performance problems when running them across a WAN....

You could try the sysarticles table in the published database or in the distribution database on your distributor you could run something like:

selectss.srvname,

p.publication,

a.publisher_db,

a.article

frommspublicationsp

inner join

msarticlesa

ona.publisher_id= p.publisher_id

anda.publication_id= p.publication_id

anda.publisher_db= p.publisher_db

inner join

master.dbo.sysserversss

onss.srvid= p.publisher_id

order by...

What about something like

SELECTPL.MyMonth,

PL.MyDay,

SUM(CASE WHEN PL.ProfitAndLoss > 0 THEN 1 ELSE 0 END) AS GreaterThanZero,

SUM(CASE WHEN PL.ProfitAndLoss < 0 THEN 1 ELSE 0 END) AS LessThanZero

FROM(select sum(profitandloss) as ProfitAndLoss,

...

Read-Only is the default. The procedure sp_addsubscription accepts an @update_mode parameter which controls the setting.

Mike

There are a couple of things that could happen. If, for example, your distribution database is on the same server as the Subscriber which has gone offline then the transactions...