Click here to Skip to main content

Database

How to access a table located in different database server.

Spunky Coder3-Mar-09 19:19

3-Mar-09 19:19

Hi all,

How can we access a table located in different database server under different database.(In a stored procedure)
I mean can we do some thing like the following in a stored procedure to get the data from the remote table...
{
CONNECT TO THE REMOTE SERVER
SELECT THE TABLE DATA

}

Please let me know how to do this...

"Don't worry if it doesn't work right. If everything did, you'd be out of a job." (Mosher's Law of Software Engineering)

Re: How to access a table located in different database server.

Vimalsoft(Pty) Ltd3-Mar-09 20:47

Vimalsoft(Pty) Ltd

3-Mar-09 20:47

NO NO NO

Lets say i have Table 1 in Server1 and Table2 In Server 2 and want to run a Query based on it. Well you must use a link Server and Call it with the instance of the Link Server like this

SELECT NAME.TABLE1,LASTNAME.TABLE
FROM TABLE 1 --IN THE CURRENT WORKING SERVER
INNER JOIN SERVER2.DATABASE.USER.TABLE2
ON USER_ID.TABLE1 = USER_ID.TABLE2

Hope this Helps

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za

SQL server taking 100% of proccesor

AlexeiXX33-Mar-09 18:40

3-Mar-09 18:40

Hi, im having a little rpblem with sql's performance

The database engine installed fine and had been working fine
Then i installed the client tools and after installing that and restarting pc, the procesor suddenly goes to a 100% for a little while, when i open the task manager, the System process is the one that takes the 100% of the procesor capacity
This had happened before, and i had just unistalled the SSMS and the problem would disappear, but this is weird, i have installed sql server (complete installation) and it works fine (on windows xp)
This is a clean windows installation of windows xp also

Does anyone have had this problem??
Any suggestions

Thanks in advance

Alexei Rodriguez

Get only updated and newly record

Abdul Rahman Hamidy3-Mar-09 17:44

Abdul Rahman Hamidy

3-Mar-09 17:44

Dear All,
i have a database in MSSQL 2005 which i am doing DDL,DML and others from c#.
i am searching for a tool in MSSQL 2005 that it could give only updated and newly inserted records in afile.
one possible i can do is to create publication, but i dont want to go through publication.
is there any method or tool that can provide me such facility.

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: Get only updated and newly record

Riaan Booyzen3-Mar-09 20:23

3-Mar-09 20:23

Hi, what about having datetime (datetimestamp) columns, one for last_updated and one for created_date?

Re: Get only updated and newly record

Isaac Gordon27-Mar-09 3:06

27-Mar-09 3:06

Hello
U Must Have To use Time Stamp With Help Of time Stamp You Can Find It.

Very basic Prolog question

aab199023-Mar-09 13:34

3-Mar-09 13:34

This is my first time coming in contact with GNU Prolog and I cannot find the answer to my problem. I have a very simple program and I have to write a few queries to find some answers.

Since the program is so short i will post the whole thing here...

d(iron, steam).
d(pumps, mines).
d(mines, railroads).
d(iron, railroads).
d(iron, guns).
d(electricity, aluminum).
d(iron, electricity).
d(aluminum, jumbojet).
d(writing, printingpress).
d(printingpress, comicbook).
d(iron, printingpress).
d(aluminum, printingpress).

depends(X,Y) :- d(X,Y).
depends(X, Y) :- d(X, Z), depends(Z, Y).

Here are the questions...

What do you need to invent the comic book?
What do you need to invent electricity?
If you don't have steam engines, what can you not invent?
Given aluminum and electricity, what can you invent?
Which inventions are dependent on iron?

thanks in advance

Trouble understanding following sql

pzn3xq3-Mar-09 9:31

3-Mar-09 9:31

I'm reviewing the procs in http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FileSpaceMon&referringTitle=Home, and have a couple question at the sql statement below:

1) where is the tempdb..sysobjects in sqlserver manager? I see a tempdb under the 'master' db...is that created by default? I don't understand why they have two periods (dot dot) in the tempdb..sysobjects. I do not see a sysobjects table within the tempdb.

2) Can someone explain the '#' in the LIKE '#FileSpaceStats%'. The % is the wildcard, but what is the leading # used for?

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#FileSpaceStats%')
BEGIN
DROP TABLE #FileSpaceStats
END
Thanks

Re: Trouble understanding following sql

Syed Mehroz Alam3-Mar-09 19:24

Syed Mehroz Alam

3-Mar-09 19:24

pzn3xq wrote:
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#FileSpaceStats%')
BEGIN
DROP TABLE #FileSpaceStats
END

# means a temporary table and such tables are stored in the tempdb. So the above query looks for a temp table starting with FileSpaceStats and drops #FileSpaceStats in case it exists.

Hope that helps.

Regards,
Syed Mehroz Alam

My Blog
My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

ROWDELIMITER for bulk insert [modified]

Brian Kavanagh3-Mar-09 5:57

3-Mar-09 5:57

I am trying to bulk insert a text file. The file is delimited by a pipe '|'. Everything works except that it does not recognize when there is a new line (record) and attempts to insert only 1 row.

I am using the default value for ROWTERMINATOR of '\r\n', but it does not work. Are there any other values that I can use to make this work?

Thansk...Brian

modified on Tuesday, March 3, 2009 2:29 PM

Re: ROWDELIMITER for bulk insert

Ashfield4-Mar-09 1:11

4-Mar-09 1:11

Brian Kavanagh wrote:
Are there any other values that I can use to make this work?

You use whatever the records are seperated by - usually\r\n, but not always, particularly if coming from a unix box. Have a look at your file in a hex editor (plenty of free ones on google) to see what values you really need.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: ROWDELIMITER for bulk insert

Brian Kavanagh4-Mar-09 6:39

4-Mar-09 6:39

Bob:

Thanks for the response. Unfortunately my thread is a little confusing since I indicate both ROWDELIMITER and ROWTERMINATOR.

I am talking about the row terminator. I have tried \r\n and other combinations to no avail.

The files that will be imported are simply ANSII file types created in a MS Windows environment.

Whenever it gets the the end of the line it continues to insert data into the same row and does not create a new record like it should.

Any guidance will be appreciated!

Thanks...Brian

Re: ROWDELIMITER for bulk insert

Ashfield4-Mar-09 8:42

4-Mar-09 8:42

My answer still stands, the chances are the lines are not terminated by crlf as expected, and although they are created in a windows environment there is no guarantee they are terminated by crlf (\r\n). My first check would be to see the real values in hex.

Give it a try and let us know.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: ROWDELIMITER for bulk insert

Brian Kavanagh4-Mar-09 10:09

4-Mar-09 10:09

Bob:

I looked at the file using a hex editor (as suggested) and at the end of each line there are 2 hex values "0d" and "0a". When I look up the values there are a carriage return and a line feed, repectively.

There are no other values except for the actual data before or after these values.

So...what next?

Thanks...Brian

Re: ROWDELIMITER for bulk insert

Ashfield4-Mar-09 21:00

4-Mar-09 21:00

Well, at least it has confirmed the line terminator. Are you using a format file? Do the number of fields match the expected number?

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: ROWDELIMITER for bulk insert

Brian Kavanagh6-Mar-09 2:57

6-Mar-09 2:57

Eureka!

I took a step back and asked what was missing? I updated to SQL Server 2005 with Service Pack 2 and that solved the issue.

Once installed, I removed the line that set the ROWTERMINATOR and allowed it to use the default value it worked.

Thanks for all your help!

Brian

Re: ROWDELIMITER for bulk insert

Ashfield6-Mar-09 4:53

6-Mar-09 4:53

Brian Kavanagh wrote:
Thanks for all your help!

No problem, pleased you found the solution.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

what are the correct Microsoft SQL server versions for Windows server 2003 x64 edition?

pubududilena3-Mar-09 3:32

3-Mar-09 3:32

HI,

what are the correct SQL SERVER versions for Windows server 2003 x64 edition operating system?
becasue we have tried to install SQL SERVER 2000 enterprise edition on there,but it was failed.

Thanks in advance.

Re: what are the correct Microsoft SQL server versions for Windows server 2003 x64 edition?

Vimalsoft(Pty) Ltd3-Mar-09 20:49

Vimalsoft(Pty) Ltd

3-Mar-09 20:49

Why dont you install SQL 2005 or 2008 they will work

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za

Revoking Oracle Roles and Privileges

Muammar©2-Mar-09 23:21

2-Mar-09 23:21

Good morning professional DBAs,

I have a problem revoking privileges from an oracle user and I dont understand where is he getting his privileges from.. Here's what I'm doing:

SQL> SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'MyUser';

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'MyUser';

no rows selected

SQL> SELECT PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'MyUser';

no rows selected

Yet, when connecting to MyUser/MyPassword, I still can insert, update and delete any table without granting those privileges first!!

Please help guys.

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Revoking Oracle Roles and Privileges

scottgp3-Mar-09 0:27

3-Mar-09 0:27

Shouldn't you be selecting from DBA_TAB_PRIVS WHERE GRANTEE = 'MYUSER'?
Also, shouldn't you use uppercase 'MYUSER' in the other two select statements as well?
Scott

Connecting to a SQL database programmatically

Richard Blythe2-Mar-09 17:25

2-Mar-09 17:25

I've programmed against a Access database for awhile and I think it's time I start using SQL server. My initial problem is a bit embarrasing but I can't seem to figure out how to connect to a SQL database programatically. I've googled the issue and couldn't find the answer I needed.
I also would like the answer to include how to connect to a password protected db.

Thanks,
Richard

There cannot be a crisis today; my schedule is already full.

Re: Connecting to a SQL database programmatically

Rupesh Kumar Swami2-Mar-09 19:10

Rupesh Kumar Swami

2-Mar-09 19:10

try Connection String for differetnt type of databases[^]

Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

My Company
Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Re: Connecting to a SQL database programmatically

Eddy Vluggen3-Mar-09 4:38

3-Mar-09 4:38

You forgot to mention the programming language that you want to use, and the query language of your preference Smile | :)

Smile | :)

There's a nice example in C# here[^], but you could also scout the MSDN video's about VB.NET[^]. There are more video's on the MSDN-site, also about the combination of SQL Server and C#.

Happy learning Smile | :)

Smile | :)

I are troll Smile | :)

Smile | :)

Logging both text and numbers

User 127822-Mar-09 17:01

2-Mar-09 17:01

I have a sensor network where the various sensors can either return a string or numeric measurement, I want to store these values along with a timestamp. I see two possible ways to handle this situation:

1. I have a general sensor data table with value column being an nvarchar of reasonable length. I can convert the numerics to strings and store them in the table as text. Pros: simplier design, Cons: wasted memory, loss of mathematical operations (min, max, etc) on the dataset.

2. Have two tables: one for text measurements, one for numeric measurements. Pros: retain ability for math on records, no wasted memory, Cons: extra step to determine table to log/retrieve from, more difficult to do aggregate queries.

Is there a third option? Any opinions on how to design this?

Thanks

modified 12-Jul-20 21:01pm.

General News Suggestion Question Bug Answer Joke Praise Rant Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.