Database

Tom Wright31-Jan-05 11:53

Tom Wright

31-Jan-05 11:53

The probelm is my app will not reside on the same machine as the SQL server. I was wondering if, since I have a connection open to my SQL database, that it would be possible to send an event to my app telling me that something has changed.

Let me ask this. If at the beginning of my app I read in all the records from the table....connections. And someone uses another app to delete one of these records. How will i know that a record has been deleted. Will I have to do a refresh every so many seconds?

Thanks

Tom Wright
tawright915@yahoo.com

cmk31-Jan-05 14:23

cmk

31-Jan-05 14:23

Yes, that is the scenario i expected.

Before going farther let me state - i am not a DBA. I am a C++ developer who writes a lot of database code (ODBC and OLE DB), but does not know all the ins and outs of SQL Server.

I had the same issue a couple years ago.
I wanted my server application to be aware of any changes made to certain tables by other applications.

What i ended up doing is writing a couple extended procedures for SQL Server.

Given:
Machine S contains my server program.
Machine D contains SQL Server.
The extended procedures are all contained in x.dll which has been installed on D.

My server on S, using ODBC, connects to MSSQL on D after setting up a listening port on S.
It then executes (via ODBC statement) 'xp_cmkInitSvcMsg <@server> <@port> [@handle OUTPUT]'.
The output handle uniquely identifies the MSSQL connection to S.

The triggers on the tables then call 'xp_cmkBroadcastSvcMsg <@msg>' when modified.
This sends @msg to all connected servers (i.e. those that called xp_cmkInitSvcMsg).

Just before the server on S disconnects from MSSQL on D it executes 'xp_cmkTermSvcMsg <@handle>'.
This just closes the socket connection from MSSSQL to S and cleans up the state.

As long as the server is connected it gets messages from MSSQL on its listening port.
It also allows more than one program to get change messages from MSSQL at the same time.
It's a bit of a round about way of doing things, but i needed a solution and this was the best i came up with.

...cmk

Save the whales - collect the whole set

Tom Wright1-Feb-05 11:11

Tom Wright

1-Feb-05 11:11

I thought it would be this difficult. I've never called a sp on SQL2000, can you shoe me a code snippet on how to do this. Also where can I find info on how to create a sp like the one you show...or any that return something back to the calling app. I'm going to assume that they are all the same.

Thanks

Tom Wright
tawright915@yahoo.com

cmk1-Feb-05 16:47

cmk

1-Feb-05 16:47

I was afraid you were going to ask this ... start with the following links and then feel free to ask any specific questions.

http://www.codeproject.com/database/mssqltutorial.asp[^]
http://www.codeproject.com/database/xp_md5.asp[^]
http://www.codeproject.com/database/extended_sp.asp[^]
http://www.codeproject.com/database/xp_pcre.asp[^]

...cmk

Save the whales - collect the whole set

Connecting to the remote Sqlserver instance.

Tom Wright2-Feb-05 4:39

Tom Wright

2-Feb-05 4:39

Awesome. Thanks for the help.

Tom Wright
tawright915@yahoo.com

RuchirD31-Jan-05 3:05

RuchirD

31-Jan-05 3:05

Hi Firends,
I am trying to access remote Sqlserver instance using program, Can anyone tell me how to achieve this.
I am having difficulty because My machine is having more that one instance of MSDE/Sqlserver instance how to provide exact information of the Sqlserver instance to which to connect.

Please anyone tell me.

Thanks n Regards,

RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.

David Salter31-Jan-05 8:54

David Salter

31-Jan-05 8:54

If you have more than one instance, you need to specify the machine and instance name to connect.

i.e. instead of connecting to "server_name", you would connect to "server_name\instance_name".

Database FAQ

RuchirD31-Jan-05 16:25

RuchirD

31-Jan-05 16:25

Hi,
Since the server is on the remote machine in that case How to do this,
Let me give an example suppose my server name is Foo and the instance name is Bar, IP address of the remote sever is ppp.qqq.rrr.sss
Can anyone tell me what would be the connection string.

RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.

David Salter1-Feb-05 9:09

David Salter

1-Feb-05 9:09

Does this link help?

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer[^]

Database FAQ

JOIN tables in different databases

RuchirD1-Feb-05 16:23

RuchirD

1-Feb-05 16:23

Hi,
I have gone through that site but
To connect to SQL Server running on a remote computer (via an IP address)

oConn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"

in this example it doesn't provide any information about what to do in the case where I have a named instance.

Plz help??

RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.

Just Greeky Creek30-Jan-05 22:27

Just Greeky Creek

30-Jan-05 22:27

I have n databases, each one has the same tables,Sure same tables in different databases has different values. I want to join them in a just one table in dataset. For example, i have 2 databases MB0001 and MB0002 , also a table named IntegratedChequeAccounts in two . I want to join two to one.
SELECT * FROM mb0001.DBO.IntegratedChequeAccounts
, Mb0002.DBO.IntegratedChequeAccounts
In this sample IntegratedChequeAccounts tables has 3 columns and each of them has 2 row. after running "select", i get 6columnsX4row as a table. But i desire 3columnsX4rows.
Any Idea to successed in.

Re: JOIN tables in different databases

Colin Angus Mackay30-Jan-05 23:14

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

30-Jan-05 23:14

You need to UNION the results into one dataset. What you are doing here is putting the rows side-by-side by joining the result set of one with the result set of another.

SELECT column1, column2, column3
FROM mb0001..IntegratedCheckAccounts
UNION
SELECT column1, column2, column3
FROM mb0002..IntegratedCheckAccounts

Re: JOIN tables in different databases

Just Greeky Creek31-Jan-05 3:54

Just Greeky Creek

31-Jan-05 3:54

Thanks for answer but i dont know why it is not working in my way.

I have a table named "DebitsAndCredits" which has fallowing columns
AccountNumber char(12)
DebitCredit bit
Amount money

I have the same table in two databases which are MB0001 and MB0002

I would like to join them in a one result set.I write

SELECT DISTINCT (AccountNumber ) ,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit
FROM DebitsAndCreditsf Order BY AccountNumber

UNION

SELECT DISTINCT (AccountNumber ) ,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit
FROM DebitsAndCreditsf Order BY AccountNumber

What do u offer for me?

Re: JOIN tables in different databases

Colin Angus Mackay2-Feb-05 3:04

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

2-Feb-05 3:04

Try this:

SELECT AccountNumber, 
    SUM(CASE DebitCredit=0 THEN Amount ELSE 0 END) as Credit, 
    SUM(CASE DebitCredit=1 THEN Amount ELSE 0 END) as Debit
FROM MB0001..DebitsAndCredits
ORDER BY AccountNumber
GROUP BY AccountNumber
UNION
SELECT AccountNumber, 
    SUM(CASE DebitCredit=0 THEN Amount ELSE 0 END) as Credit, 
    SUM(CASE DebitCredit=1 THEN Amount ELSE 0 END) as Debit
FROM MB0002..DebitsAndCredits
GROUP BY AccountNumber
ORDER BY AccountNumber

SQL transaction rollback and commit

ppp00130-Jan-05 21:54

ppp001

30-Jan-05 21:54

Hi all,

I have a problem for data transactions across two databases.
That is one object's data is comprised of two tables from
two different databases. I can perform rollback and commit within
one database. But for this case, how can I perform rollback and commit
across two databases(for two different SQL connections) for the object ?

Thanks

Re: SQL transaction rollback and commit

David Salter31-Jan-05 7:28

David Salter

31-Jan-05 7:28

You need to use a two phase commit mechanism. What database are you using?

Database FAQ

Crosstab in SQL Server 2000

ColdWaterBlue30-Jan-05 10:51

ColdWaterBlue

30-Jan-05 10:51

Re: Crosstab in SQL Server 2000

Colin Angus Mackay30-Jan-05 12:45

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

30-Jan-05 12:45

Gosh! I could not even follow that. I'd have to sit down with paper and pencil to work that out.

Here is an example of doing a cross tab query from the SQL Server books on-line: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp[^]

I'm guessing that both (the query you linked to and the one I've linked to) probably suffer from the same weakness. If there is a null in the source data then the value in the pivot table will be null also.

I don't know if that helps any - but the Microsoft example does at least show how to do a simple cross-tab query, even although it isn't a catch all like the one you linked to. I suggest starting with the Microsoft example and working from there.

Need your help on SQL learning.

denniskang200430-Jan-05 7:19

denniskang2004

30-Jan-05 7:19

hi,dear buddies:

I have finished some instroductory books on SQL, but need some practice.
Do you guys have any suggestions on where I can find some good exerciese or quizs or real-world cases I can play with? OR any exercises based on the pre-installed database in SQL-server2000 (such as pub,northwind)?

Thanks a lot.
Waiting online !
Dennis

Handling quote marks in sql?

davebarkshire28-Jan-05 23:52

davebarkshire

28-Jan-05 23:52

I currently strip out any quote marks from data input (and many other characters) when sending data to the database but would like the user to be able to enter quote marks if they wish.

I use mostly stored procedures to update data but often compose sql on the fly for ad hoc searching. Can anyone tell me how I should be handling the characters which are used in T-SQL such as ' and %

Thanks in advance - Dave

Re: Handling quote marks in sql?

Colin Angus Mackay29-Jan-05 0:04

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

29-Jan-05 0:04

davebarkshire wrote:
Can anyone tell me how I should be handling the characters which are used in T-SQL such as ' and %

Use parameterised queries.

davebarkshire wrote:
compose sql on the fly for ad hoc searching

It also sounds like you should read up on SQL Injection Attacks[^]

Re: Handling quote marks in sql?

davebarkshire29-Jan-05 1:18

davebarkshire

29-Jan-05 1:18

Nothing but porn on that link.

Re: Handling quote marks in sql?

Colin Angus Mackay29-Jan-05 1:35

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

29-Jan-05 1:35

What the....

Here is the link again: http://www.codeproject.com/useritems/SqlInjectionAttacks.asp[^]

Reading Dataset's DataTable

ZisisGR28-Jan-05 0:22

ZisisGR

28-Jan-05 0:22

Can anyone tell me how can i read the DataTable of a Dataset..
Let's Say i used an adapter to access a dataset ,i put a query to the database (through the adapter)and the results are then stored in the Dataset's dataTable(after filling it)...How can i read the contents of the datatable(each row) and pass it to a parameter of mine..Let's Say i want to read each cell and return it to my String* Reader variable..
PLs help guys...It's an emergency..
Keep up the good work..
Thnx in advance

May it be your fate to live in interesting times

Re: Reading Dataset's DataTable

Colin Angus Mackay28-Jan-05 0:36