sql reports: primary

Showing posts with label primary. Show all posts

Tuesday, March 27, 2012

Can we do Transaction log backup and log shipping on same server.

Hi,
I know that Log Shipping and Transaction log backup cannot be
done on same primary server side by side because i tried it, but i want
possible reason for that.
Next i want to know is that can we Detach and re Atach Log
Shipping Database on secondary server in a Standby mode (as you know
this database is itself in standby mode before detaching).
thanks and regards,
Sajid.What's version are you using?
<csajid@.gmail.com> wrote in message
news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> Hi,
> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
> thanks and regards,
> Sajid.
>|||> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
know, log backups has to be performed in sequence. So if the first log backup is shipped to the
other server, and the second is done by you and not shipped to the server, then the third log backup
done my log shipping won't restore as the second haven't been restored. It is possible that the
built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
thing to do.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
No.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> Hi,
> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
> thanks and regards,
> Sajid.
>|||Hi All,
Thanks for this reply.
It clears my all doubt.
thanks again.
Regards,
Sajid.
Tibor Karaszi wrote:
> > I know that Log Shipping and Transaction log backup cannot be
> > done on same primary server side by side because i tried it, but i want
> > possible reason for that.
> You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
> know, log backups has to be performed in sequence. So if the first log backup is shipped to the
> other server, and the second is done by you and not shipped to the server, then the third log backup
> done my log shipping won't restore as the second haven't been restored. It is possible that the
> built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
> thing to do.
>
> > Next i want to know is that can we Detach and re Atach Log
> > Shipping Database on secondary server in a Standby mode (as you know
> > this database is itself in standby mode before detaching).
> No.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> > Hi,
> >
> > I know that Log Shipping and Transaction log backup cannot be
> > done on same primary server side by side because i tried it, but i want
> > possible reason for that.
> >
> > Next i want to know is that can we Detach and re Atach Log
> > Shipping Database on secondary server in a Standby mode (as you know
> > this database is itself in standby mode before detaching).
> >
> > thanks and regards,
> > Sajid.
> >|||Hi All,
I want to schedule a job in ms sql server 2000 which will
copy the backup file from one server whose drive is mapped to
destination server where the backup file should get copied.
After copying the file i want to restore it on a same
destination server.
I can restore it with the help of Restore Database with move
option command, but the problem is the backup file is in format like
'sample_db_200605122100.bak' where sample is database name and numbers
indicate the date and time when backup was taken.
Now, i want to make a batch file which will delete the
previous same backup file, copy the backup file and rename it to
standard filename say 'sample_db_backup.bak'.
This batch file i can shedule to run through ms sql server
jobs by using 'xp_cmdshell'.
Please revert me on this ASAP.
Thanks and Regards,
Sajid.
csajid@.gmail.com wrote:
> Hi All,
> Thanks for this reply.
> It clears my all doubt.
> thanks again.
> Regards,
> Sajid.
> Tibor Karaszi wrote:
> > > I know that Log Shipping and Transaction log backup cannot be
> > > done on same primary server side by side because i tried it, but i want
> > > possible reason for that.
> >
> > You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
> > know, log backups has to be performed in sequence. So if the first log backup is shipped to the
> > other server, and the second is done by you and not shipped to the server, then the third log backup
> > done my log shipping won't restore as the second haven't been restored. It is possible that the
> > built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
> > thing to do.
> >
> >
> > > Next i want to know is that can we Detach and re Atach Log
> > > Shipping Database on secondary server in a Standby mode (as you know
> > > this database is itself in standby mode before detaching).
> >
> > No.
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> > > Hi,
> > >
> > > I know that Log Shipping and Transaction log backup cannot be
> > > done on same primary server side by side because i tried it, but i want
> > > possible reason for that.
> > >
> > > Next i want to know is that can we Detach and re Atach Log
> > > Shipping Database on secondary server in a Standby mode (as you know
> > > this database is itself in standby mode before detaching).
> > >
> > > thanks and regards,
> > > Sajid.
> > >sql

Can we create two servicess in a same database?

Hi,

I tried creating two services in a same queue but i couldnt see them working properly. Now i got a primary doubt that can i create two services which communicate with each other in a same database? Please help me....

here is the script which i used to create the services

/************************************Scipt *********************************/

/********************** Sender Queue and Service ***********************/

CREATE QUEUE SenderQueue WITH Status = ON;

CREATE CONTRACT SenderContract

(

ArchivingMessage SENT BY INITIATOR

);

CREATE SERVICE SenderService

ON QUEUE SenderQueue(SenderContract);

/********************** Receiver Service ***********************/

ALTER PROCEDURE pArchiveMessageHandler

BEGIN

DECLARE @.V_Message AS NVARCHAR(1000);

DECLARE @.V_Handle AS UNIQUEIDENTIFIER;

INSERT INTO TestServiceBroker VALUES('Starting Message', @.V_Handle);

RECEIVE TOP(1) @.V_Message = message_body,

@.V_Handle = conversation_handle

FROM ArchivingMessageQueue

INSERT INTO TestServiceBroker VALUES(@.V_Message, @.V_Handle)

END CONVERSATION @.V_Handle

WITH cleanup

END

CREATE QUEUE ReceiverQueue

WITH ACTIVATION(

PROCEDURE_NAME = pArchiveMessageHandler,

MAX_QUEUE_READERS = 5,

EXECUTE AS 'dbo'

);

CREATE CONTRACT RecieverContract

(

ArchivingMessage SENT BY INITIATOR

);

CREATE SERVICE ReceiverService ON QUEUE ReceiverQueue

( RecieverContract )

/********************** Sample script ***********************/

DECLARE @.V_Handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.V_Handle

FROM SERVICE SenderService

TO SERVICE 'ReceiverService'

ON CONTRACT SenderContract;

SEND ON CONVERSATION @.V_Handle

MESSAGE TYPE ArchivingMessage('<XmlType/>');

select * from TestServiceBroker(nolock)

Hi:

Why do you need to create two services in the same database...in the first place. I know it works between two different databases but have not tired with same database.

Pramod

|||

Although it is possible to associate two services with the same queue, this is a very atypical usage. Messages sent to the target service as well as messages received from the target service will be delivered to the same queue.

In your script above, you are not doing this, so I'm not sure if you meant to say "same queue" or "same database".

Having both the initiator service and target service is in the same database is certainly a valid and useful configuration. You would use this typically for doing asynchronous database work.

In your script, the stored proc is RECEIVing messages from ArchivingMessageQueue (which doesn't exist) instead of ReceiverQueue. Also why are you ending the conversation with cleanup. Cleanup should not be used during normal working of an app. It is used as an admin command when things go wrong.

|||

You can have as up to ~65000 services in a database. Communication between services in the same database is identical with communication between services in different databases or different instances.

Can you specify what you mean by 'but i couldnt see them working properly' ?

A small correction to the code posted by you:

CREATE QUEUE ReceiverQueue

WITH ACTIVATION(

STATUS = ON,

PROCEDURE_NAME = pArchiveMessageHandler,

MAX_QUEUE_READERS = 5,

EXECUTE AS 'dbo'

);

|||

I modified the activation stored proc as well as the receiver queue based on your feedback. But still i could see the service running. When i say the services are not running means.. my query "select * from TestServiceBroker(nolock)" is not returning any rows.

I Would like to tell you people about what exactly is my requirement. We have a master table X and around 20 transaction tables say A, B, C, D, E,..etc. The transaction tables uses data available in the master table. When ever a row in the master tables is disabled(we have a flag column which holds state) we would like to disable all the trasaction tables(A, B, C, D....) data which is referring to the master X row. The process of disableing the transaction table rows can happen in an asynchronous way. To achieve this we planned to implement Fire and Forget style of service brokering. When ever a master table row is disabled we need to queue a message(using a trigger on the master table) for disabling the transaction table rows. An activation procedure needs to do the job of reading the queued request and disableing transcation table rows.

Also, can you please give me links of e-books and white papers on Service Brokers?

Regards,

Gopi

|||

Have you looked over my troubleshooting mini-guide at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx ? Please follow this guide to figure out where do your messages 'disappear'. If I'd have to guess in your case I'd say that the database is lacking the master key required for secure dialogs.

One thing to note is that you should not use the WITH CLEANUP clause in the END DIALOG statement. The CLEANUP clause removes the conversation endpoint (target) without sending any notification to the other conversation endpoint (initiator). See also this post here http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx

The design you're trying to achieve shouldn't be any problem if the asynchronous processing is required or desired. One thing you'll have to consider is the order of execution of these enable/disable operations. Service Broker guarantees the order of delivery only within a conversation.

HTH,
~ Remus

|||

Thanks Remus.

I'll debug the problem with your suggestions.

Regards,

Gopi

|||

Gopinath

I took the liberty of rewriting your sample script. I changed several things and also added numerous explanatory comments. You can compare your original to this to see what was wrong. Just copy/paste this into mgmt. studio and run the whole thing at once.

-Gerald Hinson

use master

drop database gopinath

create database gopinath

use gopinath

/********************** Sender Queue and Service (nothing else needed )***************/

CREATE QUEUE SenderQueue;

CREATE SERVICE SenderService

ON QUEUE SenderQueue;

/********************** RECEIVE MessageType, Contract, Queue and Service ****************/

CREATE MESSAGE TYPE ArchivingMessage;

CREATE CONTRACT ReceiverContract

(

ArchivingMessage SENT BY INITIATOR

);

CREATE QUEUE ReceiverQueue;

CREATE SERVICE ReceiverService ON QUEUE ReceiverQueue

(ReceiverContract)

/********************** Receiver Service ***********************/

create table TestServiceBroker (message_body nvarchar(max),

conversation_handle uniqueidentifier);

CREATE PROCEDURE pArchiveMessageHandler

BEGIN

DECLARE @.V_Message AS NVARCHAR(1000);

DECLARE @.V_MessageTypeName AS NVARCHAR(256);

DECLARE @.V_Handle AS UNIQUEIDENTIFIER;

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION

WAITFOR (

RECEIVE TOP(1)

@.V_MessageTypeName = message_type_name,

@.V_Message = message_body,

@.V_Handle = conversation_handle

FROM ReceiverQueue), TIMEOUT 2000; -- wait 2 seconds before giving up

/* you shouldn't be inserting here unless you actually got a message back, so I added

the "if" below */

IF (@.@.rowcount > 0)

BEGIN

/* if you get and END DIALOG message, then you should END CONVERSATION as well */

IF (@.V_MessageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

BEGIN

END CONVERSATION @.V_Handle;

-- WITH cleanup -- (you shouldn't have this in your app....see explanations on the forum)

END

ELSE

BEGIN

INSERT INTO TestServiceBroker VALUES(@.V_Message, @.V_Handle);

END

END -- end if

ELSE

BEGIN

break; -- timed out waiting for messages, break from loop

END

COMMIT TRANSACTION;

END -- end loop

COMMIT TRANSACTION;

END

/********************** Sample script to send a message***********************/

BEGIN TRANSACTION;

DECLARE @.V_Handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.V_Handle

FROM SERVICE SenderService

TO SERVICE 'ReceiverService'

ON CONTRACT ReceiverContract

WITH ENCRYPTION=OFF;

SEND ON CONVERSATION @.V_Handle

MESSAGE TYPE ArchivingMessage(N'<XmlType/>');

COMMIT TRANSACTION;

BEGIN TRANSACTION;

-- This will prevent you from seeing any error messages/replies.It would

-- be better to wait for the END DIALOG message from 'SenderService'

-- before doing this... Then you will know that your message actually got

-- processed....

END CONVERSATION @.V_Handle;

COMMIT TRANSACTION;

/********************** Sample script ***********************/

select * from ReceiverQueue;-- this will show that the messages are in the target queue

/**********************Turn on Activation on the receiver queue **********************/

ALTER QUEUE ReceiverQueue

WITH ACTIVATION(

STATUS=ON,

PROCEDURE_NAME = pArchiveMessageHandler,

MAX_QUEUE_READERS = 1,

EXECUTE AS 'dbo');

WAITFOR DELAY '00:00:02'; -- wait a second and then look in the activated task view to see your proc running

select * from sys.dm_broker_activated_tasks;

WAITFOR DELAY '00:00:02'; -- wait another couple of seconds and the proc should have finished running by now

select * from sys.dm_broker_activated_tasks;

/* look at the queue and your table again, not that the proc is done */

select * from ReceiverQueue;-- messages are gone

select * from TestServiceBroker;-- rows are now in the table

/* if the message could not be delivered for some reason, they would be in

sys.transmission_queue along with an explanation why they could not be delivered */

--select * from sys.transmission_queue

|||

Hi Gerald,

Thank you very much for you help. It's working.

Thursday, March 22, 2012

Can two Tables have the same primary key ?

I have been given a project to complete where ... two tables "Flights" and "ScheduledFlights" have a column called "FlightNo" . In both these tables it is mentioned that "FlightNo" is primary key ?
Is this possible ? i am not talking about foreign key here....

Here is the script that i have created..

create table flights
(
FlightNo char(5) constraint FlightNo primary key clustered not null,
DepTime char(5) not null,
ArrTime char(5) not null,
AircraftTypeID char(4) references Aircraft(AircraftTypeID) not null,
SectorID Char(5) references Sector(SectorID) not null
)

create table ScheduledFlights
(
FlightNo char(5) constraint FlightNo2 primary key clustered not null,
FlightDate datetime not null,
FirstClassSeatsAvailable int not null,
BusinessClassSeatsAvailable int not null,
EconomyClassSeatsAvailable int not null
)

Now the problem is with FlightNo attribute .. ? Can two Tables have the same primary key ?
(FlightNo2 is given as a temporary solution)

I think your question sounds a bit ambiguous because you do not distinguish between Primary Key itself and Primary Key's Name.

Two tables can have primary keys defined on the columns with the same name. But no two tables can share the same Primary Key name. To awoid confusion you can call your Primary keys :

CONSTRAINT pkFligts PRIMARY KEY (FlightNo )

CONSTRAINT pkSheduledFligts PRIMARY KEY (FlightNo )

NB.

|||Thanks. I will ask my teacher about that... can u tell me one more thing ...

I have another table called " Passenger" which has an attribute called Travel Date which is>

"Date Of travel. The flight number and the date of travel together form a foreign key that references the flight number and flight date in the flight table."

In short ,

FlightNo(in Flights Table)+DeptTime(in Flights Table)

how will i write this in the create table statement... sorry i am learning if this sounds stupid.|||

That explains it :-)

When you need to create a multi-column Foreigh Key , you add your constraint separately after a list of columns. Make sure , the columns references by

CREATE TABLE Passenger

( <your list of columns>,

CONSTRAINT fkPassengerFlights FOREIGN KEY (FlightNo, DeptTime)

REFERENCES Fligts (FlightNo, DeptTime)

)

NB. Good luck with your studing.

|||

Hello,

What you need is a composite foreign key. A composite key is made of more than one column. A composite primary key will have say col1, col2 and col3 etc in it. Any foreign key referencing such composite primary key will have col1, col2 and col3 in it. Such a Foreign Key is called Composite Foreign Key. Below is a sample code..hope it helps...

create table.....

constraint [FK_AnyRelevantName] Foreign Key (col1, col2, col3)

References [TableName_Containing_Primary_Key] (Col1, Col2, col3) -- these will be the primary key columns defined on another table.

I Hope this is helpful.

Thanks......

|||Thank you very much for taking time off and helping me out.

Can two Tables have the same primary key ?

Here is the script that i have created..

Now the problem is with FlightNo attribute .. ? Can two Tables have the same primary key ?
(FlightNo2 is given as a temporary solution)

I think your question sounds a bit ambiguous because you do not distinguish between Primary Key itself and Primary Key's Name.

Two tables can have primary keys defined on the columns with the same name. But no two tables can share the same Primary Key name. To awoid confusion you can call your Primary keys :

CONSTRAINT pkFligts PRIMARY KEY (FlightNo )

CONSTRAINT pkSheduledFligts PRIMARY KEY (FlightNo )

NB.

|||Thanks. I will ask my teacher about that... can u tell me one more thing ...

I have another table called " Passenger" which has an attribute called Travel Date which is>

"Date Of travel. The flight number and the date of travel together form a foreign key that references the flight number and flight date in the flight table."

In short ,

FlightNo(in Flights Table)+DeptTime(in Flights Table)

how will i write this in the create table statement... sorry i am learning if this sounds stupid.|||

That explains it :-)

When you need to create a multi-column Foreigh Key , you add your constraint separately after a list of columns. Make sure , the columns references by

CREATE TABLE Passenger

( <your list of columns>,

CONSTRAINT fkPassengerFlights FOREIGN KEY (FlightNo, DeptTime)

REFERENCES Fligts (FlightNo, DeptTime)

)

NB. Good luck with your studing.

|||

Hello,

create table.....

constraint [FK_AnyRelevantName] Foreign Key (col1, col2, col3)

References [TableName_Containing_Primary_Key] (Col1, Col2, col3) -- these will be the primary key columns defined on another table.

I Hope this is helpful.

Thanks......

|||Thank you very much for taking time off and helping me out.

Tuesday, March 20, 2012

can this be done with a check constraint?

i have a zip code table downloaded from the usps in excel and imported
into a sql database table.
zipcode is not a primary key in the table, as it lists all the cities
and aliases for cities that include each zipcode.
however there is a rule in the data:
for any given zipcode, only ONE row of data can have a citytype value
of 'D'
what i am wondering is how i can turn that rule into a database level
constraint. i can't create a unique constraint on zipcode + citytype,
because any number of rows for the same zipcode can have citytype N and
A, for example. it is only the D value that can appear only once per
zipcode.
is this something a check constraint can accomplish? i'm new to check
constraints so i'm not sure what that constraint rule would look like.
it seems more complicated than "thisfield > thatfield" type logic.
thanks,
jasonI think this is beyond the capabilities of a check constraint.
A Check constraint is an expression which is used to check that the contents
of a column conform to a given rule. The constraint cannot reference
information on a different row of data than the one which is being checked.
You may be able to accomplish your scenario using a trigger. There are
times however, then the integrity checking would be so expensive that it's
simply more efficient to push said rule to a different business logic layer.
Colin.
"jason" <iaesun@.yahoo.com> wrote in message
news:1142884753.165122.99270@.u72g2000cwu.googlegroups.com...
>i have a zip code table downloaded from the usps in excel and imported
> into a sql database table.
> zipcode is not a primary key in the table, as it lists all the cities
> and aliases for cities that include each zipcode.
> however there is a rule in the data:
> for any given zipcode, only ONE row of data can have a citytype value
> of 'D'
> what i am wondering is how i can turn that rule into a database level
> constraint. i can't create a unique constraint on zipcode + citytype,
> because any number of rows for the same zipcode can have citytype N and
> A, for example. it is only the D value that can appear only once per
> zipcode.
> is this something a check constraint can accomplish? i'm new to check
> constraints so i'm not sure what that constraint rule would look like.
> it seems more complicated than "thisfield > thatfield" type logic.
> thanks,
> jason
>|||Jason,
2 ways to accomplish that:
1. google up "nullbuster" and create an index on computed columns
2. create an indexed view for
select zipcode from mytable where citytype = 'D'
Good luck!sql

Thursday, March 8, 2012

Can SQL Server message other processes?

I'm a developer for a rich client application with a primary grid that should be refreshed when data is changed by other users. I loath to resort to some sort of polling. It would be really cool if there is a native way to raise an event on the client from a SQL Server trigger. RAISEERROR can send a message to the Connection's InfoMessage() event. But of course this will only send the message back to the user who made the change. Is there anyway in SQL Server to raise an error message on another process so that this ADO event can pick it up?

This is not possible without some elaborate engineering in SQL Server 2005 (even more so in SQL Server 2000). With SQL Server 2005, you can use service broker to do this. It is hard to tell without knowing more details but I suggest that you take a look at the new features in SQL Server 2005 and see if those address your needs.|||You could always use firebird/interbase. probably on of the best rdbms's around.

Other that that (hypotheticals follow)

in SQL 2000 ->
You could put a com+ object in DTS. Trigger on a data-change notifies the com+ object. COM+ object has list of registered clients that it notifies of the changes.

SQL 2005 it would be even easier with the integration of .NET

wouldn't that work?

Oh dont make me do this in VBSUX because VB, well vb simply sux - how did it become so successful?! I dont want to do it in VB.NET as I want to cater the lowest common denominator. And boy is VB the lowest denominator!

Simple to do in delphi! but give me an hour. . . I'll do it in VB
God delphi rox!|||Thanks for your help. I'm not suprised that there isn't anything. However I'm disapointed SQL Server 2005 doesn't have it. SQL Server has all the capabilities as a simply event conduit between it's clients. Kind of a waste then that some other messaging service is required for simple data related events to be passed around.
For an alternative I have an idea to referesh based on user activty. A few events will call a function and refresh if some criteria has been met. Eg the form is activated after five minutes.
|||

well trying to do it using vbsux further ingrained my disdain for the piece of sh|t that vb6 is. Its not hard to do in delphi. It is Actually rather simple.

About 20 lines of actual code you need to write. . .

Create an activeX exe (DBNotifier) that defines an object (NotifyClient) that implements the interface

INotifyClient
{
Notify()
}
with an event OnNotify

another singleton object (Manager) implements the interface -

IManager
{
void RegisterClient(INotifyClient client);
void UnRegisterClient(INotifyClient client);
void NotifyClients()
}
During the call to NotifyClients you need to synchronize access to a collection it contains that will hold references to the clients.

Finally an object that (NotifyAgent) implements
INotifyAgent
{
void Notify(long ID)
}
NotifyAgent calls the Managers.NotifyClients Method

In the GUI app, get a reference to the IManager Object, Instance An INotifyClient and register it. Define an OnNotifyEvent to do what ever you want.
Now in the database (I will use northwind for example) attach a trigger of this sort:
=============================

create trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'

EXEC @.hr = sp_OACreate 'DBNotifier.Agent', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=============================

Again. . . dont try in VBSUX, its not worth the effort use a real development environment. Why you might ask? The synchronization is impossible. VB is just a load of cr@.p (have they yet shot the guys who developed it? shoot their mothers too!!!)

Now. . . why is this not implemented natively? Well, because you aren't supposed to remain connected to the database. Its bad design!

Connect, get your data and get out!!!!
Connect, change your data and get out!!!!

|||You can do it using service broker and query notifications mechanisms in SQL Server 2005. It depends on your requirements. As I said, you should check out those features in Books Online.|||

Boy, I could complicate a wet dream!

Its not difficult at all, provided you use Delphi as you can't do it with VBSUX alone because VBSUX is a piece of cr@.p and cant create COM+ event objects. . . Have those guys been shot yet?

Total time of implementation 10 minutes depending on how many SQL objects you want sending notifications!!!

Three parts. . .

● Build, register and install a COM+ Event Object - SQLEvents (this is not hard but you cannot do it in VBSUX!!! Use Delphi!)
● Add triggers to your SQL objects that should initiate the SQLEvents
● Create an ActiveX Event Sink for handling the COM+ Events - SQLEventSink (This can be done in VBSUX!!!)
If you don't have delphi, go home - you suck!

[Part One - Looks like alot, but it takes all of 3 minutes!!!]
1. create a Delphi Active X library, Call it SQLEvents.
2. From the file menu - > Add >Other -> ActiveX -> Automation Object call it SQLEvent with Apartment Threading (no events)
3. From view Menu -> Type Library. . . In the the TypeLibrary editor, add a method to the ISQLEvent interface called Notify that takes a long parameter called SQLObject [see here]
4. Generate the code and call the generated pas file SQLEvents_impl.pas; you dont need to write ANY code!!!!!
5. From the Run Menu -> Register Active X Server (this also builds the DLL)

On the SQL Server machine:
6. Open Component Services and drill down to COM+ Applications. Right Click and select New -> Application - Next -> Empty Application Call it 'SQLEvents' and set Server Application as your activation type -> Applciation Identity Interactive User -> use the default application roles -> dont add any roles -> finish

7. Expand the SQLEvents Aplication folder to the Components folder and right click and select New -> Component -> Next -> Click Install New Event Class(es) and locate and open the SQLEvents.dll you built in step 5. -Next -> Finish

The event object is done. and the tree should look like this when expanded.

[Part 2]
8. Add triggers to the SQL Objects that need to send notifications -

NOTES: My build has a CLSID of "{8FD50E86-203D-4939-9CAE-0F2865C69465}" yours will be different! You can find it out by right clicking the Component installed in step 7 and selecting properties.
Also. this example uses northwind and will trigger events on Update Delete and Insert on the employees table:
=====================================================
CREATE trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'

EXEC @.hr = sp_OACreate '{8FD50E86-203D-4939-9CAE-0F2865C69465}', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=====================================================

Part 3 [you can use VB here, total time 2 minutes!!!]

9. Create a new VB ActiveX DLL Project, save it as SQLEventsSink, Rename Class1 to SQLEventsSink and save the file as SQLEventsSink.cls.

10. From the Projects menu Add a reference to the SQLEvents Lbrary you built in Step 5 above.

11. Add this code to SQLEventsSink:
=================================
Option Explicit
Implements SQLEvents.SQLEvent

Private mSQLObjectID As Long 'local copy
Public Event OnNotify()

Public Property Let SQLObjectID(ByVal vData As Long)
mSQLObjectID = vData
End Property

Public Property Get SQLObjectID() As Long
SQLObjectID = mSQLObjectID
End Property

Private Sub SQLEvent_Notify(ByVal SQLObjectID As Long)
If SQLObjectID = Me.SQLObjectID Then RaiseEvent OnNotify
End Sub
=================================

12. Build the Library and you are done. . . you have an event system!!!

Here's how to use ->

1. Create A New VB Application Project Call it TestApp
2. From the Project Menu, add references to MS Active Data Object 2.6 (minimum), COM+ Admin Library and the SQLEventSink library you built in step 12 above. In the Toolbox add the MS DataGrid 6.0
3. Add a Module, name it globals and add the following code
==============================================
Option Explicit

Public Const CONNECTIONSTRING = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=.\SQL2000"

' NOTE: CHANGE THE FOLLOWING TO REFLECT THE CLSID
' OF THE SQLEvents.SQLEvent OBJECT YOU PREVIOUSLY
' BUILT AND INSTALLED IN COM+

Public Const CLSID_SQLEVENT = "{8FD50E86-203D-4939-9CAE-0F2865C69465}"
==============================================

4. Add a Module, name it ComUtils and add the following code (funny, in delphi it only takes about 6 lines of code to accomplish the same!!! Have I mentioned that VBSUX, sucks?!?)
==============================================
' This method creates a Transient Subscription to a COM+ Component
' Refer to The Windows Platform SDK and Particularly ICOMAdminCatalog
' clsID is the COM+ Event Component to which you are subscribing
' objref is the subscriber
' hostname is the machine on which the COM+ Event Component is registered
' If empty, connection stays on local machine

Public Function CreateTransientSubscription( _
ByVal clsid As String, _
ByVal objref As Object, _
Optional ByVal hostName As String = "") As String
Dim oCOMAdminCatalog As COMAdmin.COMAdminCatalog
Dim oTSCol As COMAdminCatalogCollection
Dim oSubscription As ICatalogObject
Dim objvar As Variant
On Error GoTo CreateTransientSubscriptionError
Set oCOMAdminCatalog = CreateObject("COMAdmin.COMAdminCatalog")
'Connect to the
If hostName <> "" Then oCOMAdminCatalog.Connect hostName
'Gets the TransientSubscriptions collection
Set oTSCol = oCOMAdminCatalog.GetCollection( _
"TransientSubscriptions")
Set oSubscription = oTSCol.Add
Set objvar = objref
oSubscription.Value("SubscriberInterface") = objref
oSubscription.Value("EventCLSID") = clsid
oSubscription.Value("Name") = "TransientSubscription"
oTSCol.SaveChanges
CreateTransientSubscription = oSubscription.Value("ID")
Set oSubscription = Nothing
Set oTSCol = Nothing
Set oCOMAdminCatalog = Nothing
Set objvar = Nothing
Exit Function
CreateTransientSubscriptionError:
CreateTransientSubscription = ""
Err.Raise Err.Number, "[CreateTransientSubscription]" & _
Err.Source, Err.Description
End Function
==============================================

5. Drop a DataGrid control on your form leaving the properties as their defaults. Add a Timer to the form as you need it because VBSUX does not natively support multi-threading. . . Have I told you how much VBSUX sucks? It really does! I wouldn't lie to you!

6. In the Form1 code add the following:
=========================================

Option Explicit
Private WithEvents mSink As SQLEventSink

Private Sub Form_Load()
Set mSink = New SQLEventSink
CreateTransientSubscription _
"{8FD50E86-203D-4939-9CAE-0F2865C69465}", _
mSink
LoadGrid
End Sub

Private Sub mSink_OnNotify()
Timer1.Enabled = True
End Sub

Private Sub Timer1_Timer()
Timer1.Enabled = False
LoadGrid
End Sub

Private Sub LoadGrid()
Dim con As Connection
Set con = New ADODB.Connection
con.Open CONNECTIONSTRING
If DataGrid1.DataSource Is Nothing Then
With con.Execute("SELECT ID FROM SYSOBJECTS O " & _
" INNER JOIN SYSUSERS U ON O.UID = U.UID " & _
" WHERE U.NAME = 'DBO' and O.NAME = 'EMPLOYEES'")
mSink.SQLObjectID = .Fields(0)
.Close
End With
End If
Dim rst As Recordset
Set rst = New Recordset
rst.CursorLocation = adUseClient
Set rst.ActiveConnection = con
rst.Open "SELECT * FROM EMPLOYEES"
Set rst.ActiveConnection = Nothing
con.Close
Set DataGrid1.DataSource = rst
Set con = Nothing
Set rst = Nothing
End Sub
=========================================

7. Run the app -
if your Northwind has not been changed, the name for employeeID 7 should be: King, Robert.

In SQL Query Analyzer, execute:

update employees set FirstName = 'Stephen' where EmployeeID = 7

and "voila!!!" Stephen King automatically appears in your datagrid!!!

References:
ICOMAdminCatalog
Registering a Transient Subscription

Complete Source Code can be found here. Zip also contains a compiled SQLEvents.dll,, just in case you don't have delphi and are still hanging around!

|||1. Don't implement the sink in VBSUX as the VBSUX com object is unstable.
No problems with a sink implemented in delphi. Have I told you that VBSUX is a total piece of CR@.P?

2. You need to enter a critical immediately upon entering the handler. After entering the critical section, null the Sink reference. Reinitialize the sink right after make changes inside the thread that does the response to the notification. So Don't Implement a Sink Client in VBSUX because Critical sections in VBSUX are a total pain in the @.SS - and threads in VBSUX are even worse!!! Have I told you that VBSUX is a PIECE OF CR@.P?

3. Best perfromance is not kicking the COM+ event off in the sql server but in the applciation that makes the change to the database. Immediately after making a change you want to publish, instance a COM+ Event and call Notify.

On a 2.6 P4 H/T w 775mb mem, I had 22 publishers notifying 30 subscribers. . .slow but no blow-ups.

Saturday, February 25, 2012

Can sp_changearticle help when needing to change the primary key?

We need to add a new column to a table that is part of a multi-table
publication replicated via transactional replication. We've read (and
re-read) the sp_changearticle page in Books Online without fully
understanding how we might be able to use this command to help with our task.
Can someone please provide two important answers -- 1) can we use
sp_changearticle to make this kind of change to our publication? 2) can you
offer an example of how sp_changearticle is coded for such purposes.
Actually, we'd be interested in seeing how sp_changearticle is coded in
general, even if it cannot be used for our particular task.
Thanks,
Barry Spiegel
barry.spiegel@.eds.com
2) sp_changearticle 'pubs', 'jobs','description','this is the new
description'
1) no, you use use sp_repladdcolumn like this:
sp_repladdcolumn 'jobs','intcol','int not null default(1)'
This column will be modified in all publications and their subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Barry Spiegel" <Barry Spiegel@.discussions.microsoft.com> wrote in message
news:732D9DB5-C5A6-4A65-AEB7-37384C791401@.microsoft.com...
> We need to add a new column to a table that is part of a multi-table
> publication replicated via transactional replication. We've read (and
> re-read) the sp_changearticle page in Books Online without fully
> understanding how we might be able to use this command to help with our
task.
> Can someone please provide two important answers -- 1) can we use
> sp_changearticle to make this kind of change to our publication? 2) can
you
> offer an example of how sp_changearticle is coded for such purposes.
> Actually, we'd be interested in seeing how sp_changearticle is coded in
> general, even if it cannot be used for our particular task.
> Thanks,
> Barry Spiegel
> barry.spiegel@.eds.com
>

Thursday, February 16, 2012

Can replication handle existing database?

Hello:
I have the following situatioin. A few months ago, I setup a secondary SQL
server with the same schema and data as the primary. But that server was
shut down for a few months.
Currently, I am thinking to set up replication (transaction). Since the
database is rather huge, I think I will try transaction replication. Do you
think it will work so that the secondary server will be in sync with the
primary server? This is the first time I am doing it.
Thanks,
Q
If the data is now out of sync, this'll need attending to first. You could
use Redgate's DataCompare to synchronize the subscriber with the publisher
then after that do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

can primary key be derived/created from 2 columns?

Hello,
In MS Access a primary key can be created from 2 (or more) non-unique
columns which would comprise a unique column -- as follows (from these 2
repeating columns) using the Indexes dialog box in table design and this
places a Key symbol on the respective columns:
col1 col2
1 mon
1 tue
1 wed
1 thu
1 fri
2 mon
2 tue
2 wed
2 thu
2 fri
...
The combination of col1 and col2 would comprise the primary key here. So in
Access I create a PrimaryKey name and just add fields to that name. Is
something like this doable in sql Server?
I have been able to create a unique key from 2 non-unique fields in a sql
server table and they serve as a constraint. But I don't get the Primary Ke
y
symbol on the columns like in Access. Matter of fact, I observed that only
one column can take the Primary Key symbol. I guess my real question (or
next question) is what the significance is of the Primary Key Symbol and if
it is correct that only one column in a sql server table can have the Primar
y
Key symbol on it?
The purpose of my question(s) is to have as much understanding of Primary
Keys in Sql Server as I can. Right now, it appears that a primary key serve
s
mainly as a constraint. What are other significances of the Primary key?
Thanks,
RichNever mind about the question for getting the double keys. I just figured i
t
out. But if I want to join the primary key to a foreign key in another
table, how can I make a unique join to the primary key from the other table?
Thanks,
Rich|||>> But I don't get the Primary Key symbol on the columns like in Access. <<
What does that mean? It sounds like you are drawing pictures to
program! Do you know how to program? With real code, in a language?
While youy are learning to be a real programmer, instead of a video
game, learn DDL and the "PRIMARY KEY(<col1>, <col2> )" syntax.|||Rich (Rich@.discussions.microsoft.com) writes:
> Never mind about the question for getting the double keys. I just
> figured it out. But if I want to join the primary key to a foreign key
> in another table, how can I make a unique join to the primary key from
> the other table?
SELECT ...
FROM a
JOIN b ON a.col1 = b.col1
AND a.col2 = b.col2
Or did you ask how to do this through point and click? I'm afraid that
I don't know the answer to that question. I know that there is a query
designer in Enterprise Manager, and also in Management Studio. But
this designer is a very limited tool, that only can handle simple queries.
If you feed it more complex queries, you will find that it is prone to
rewrite the queries, so that the meaning of them changes.
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|||"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:86745BD5-0521-43BB-B615-D1C68A78ABFB@.microsoft.com...
> Hello,
> In MS Access a primary key can be created from 2 (or more) non-unique
> columns which would comprise a unique column -- as follows (from these 2
> repeating columns) using the Indexes dialog box in table design and this
> places a Key symbol on the respective columns:
> col1 col2
> 1 mon
> 1 tue
> 1 wed
> 1 thu
> 1 fri
> 2 mon
> 2 tue
> 2 wed
> 2 thu
> 2 fri
> ...
> The combination of col1 and col2 would comprise the primary key here. So
> in
> Access I create a PrimaryKey name and just add fields to that name. Is
> something like this doable in sql Server?
> I have been able to create a unique key from 2 non-unique fields in a sql
> server table and they serve as a constraint. But I don't get the Primary
> Key
> symbol on the columns like in Access. Matter of fact, I observed that
> only
> one column can take the Primary Key symbol. I guess my real question (or
> next question) is what the significance is of the Primary Key Symbol and
> if
> it is correct that only one column in a sql server table can have the
> Primary
> Key symbol on it?
> The purpose of my question(s) is to have as much understanding of Primary
> Keys in Sql Server as I can. Right now, it appears that a primary key
> serves
> mainly as a constraint. What are other significances of the Primary key?
> Thanks,
> Rich
SQL Server isn't Access. Try learning SQL rather than playing with the
Enterprise Manager interface. It may seem hard work for you right now but
eventually you'll benefit from more control, better results and better
understanding of what you are doing. Example:
ALTER TABLE your_table
ADD CONSTRAINT pk_your_table
PRIMARY KEY (col1,col2);

> Right now, it appears that a primary key serves
> mainly as a constraint. What are other significances of the Primary key?
Primary key is a constraint. SQL Server also creates an index for a primary
key constraint but the first purpose of a primary key is to support entity
integrity and referential integrity.
David Portas
SQL Server MVP
--|||Thank you very much for your explanantion. This was very informative. My
learning is derived from reading on the subject, doing, and asking a lot of
questions. Thus, I thank you kindly for sharing your explanation and for
giving an example. I was not aware that you could use more than one column
in this statement:
ALTER TABLE your_table
ADD CONSTRAINT pk_your_table
PRIMARY KEY (col1,col2);
These are the little details that I miss in my reading, and so I ask the
question and someone points out these more obscure details.
Thanks again,
Rich
"David Portas" wrote:

> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:86745BD5-0521-43BB-B615-D1C68A78ABFB@.microsoft.com...
> SQL Server isn't Access. Try learning SQL rather than playing with the
> Enterprise Manager interface. It may seem hard work for you right now but
> eventually you'll benefit from more control, better results and better
> understanding of what you are doing. Example:
> ALTER TABLE your_table
> ADD CONSTRAINT pk_your_table
> PRIMARY KEY (col1,col2);
>
> Primary key is a constraint. SQL Server also creates an index for a primar
y
> key constraint but the first purpose of a primary key is to support entity
> integrity and referential integrity.
> --
> David Portas
> SQL Server MVP
> --
>
>|||Thank you for your explanation on how to join tables where the primary key
consists of more than one column.
Rich
"Erland Sommarskog" wrote:

> Rich (Rich@.discussions.microsoft.com) writes:
> SELECT ...
> FROM a
> JOIN b ON a.col1 = b.col1
> AND a.col2 = b.col2
> Or did you ask how to do this through point and click? I'm afraid that
> I don't know the answer to that question. I know that there is a query
> designer in Enterprise Manager, and also in Management Studio. But
> this designer is a very limited tool, that only can handle simple queries.
> If you feed it more complex queries, you will find that it is prone to
> rewrite the queries, so that the meaning of them changes.
>
> --
> 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
>|||Try using the product tools before bashing somebody.
The symbol the OP is talking about is on Enterprise Manager table designer.
Not everybody wants to learn DDL, they just want a simple method of storing
data for their application - that can all be done through the GUI now,
despite what you might say, it is a good thing and widens database use.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136241163.051698.144990@.g49g2000cwa.googlegroups.com...
> What does that mean? It sounds like you are drawing pictures to
> program! Do you know how to program? With real code, in a language?
> While youy are learning to be a real programmer, instead of a video
> game, learn DDL and the "PRIMARY KEY(<col1>, <col2> )" syntax.
>

Tuesday, March 27, 2012

Can we do Transaction log backup and log shipping on same server.

Can we do Transaction log backup and log shipping on same server.

Can we create two servicess in a same database?

Thursday, March 22, 2012

Can two Tables have the same primary key ?

Can two Tables have the same primary key ?

Tuesday, March 20, 2012

can this be done with a check constraint?

Thursday, March 8, 2012

Can SQL Server message other processes?

Saturday, February 25, 2012

Can sp_changearticle help when needing to change the primary key?

Thursday, February 16, 2012

Can replication handle existing database?

can primary key be derived/created from 2 columns?

sql reports

Blog Archive

About Me