Urgent! Stored procedure which updates Web DB? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Urgent! Stored procedure which updates Web DB?

Author

Topic

gbaia
Yak Posting Veteran

52 Posts

Posted - 2004-04-21 : 08:22:24

Hi there!

I've got a stored procedure which inserts row from one table to another. I've done it locally (from one DB in the local server to another DB) and it works fine.

Now, I need to know if I could run this stored procedure so that it updates the table in a web server?

Is this possible? Thank you so much for any help!

Grazi

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-21 : 08:32:53

It is, just prefix your table names with full 3 part qualifiers if you are running it from your local server...

gbaia
Yak Posting Veteran

52 Posts

Posted - 2004-04-21 : 08:50:55

Thank You Rick.
And if I want to run it from a local server to a web-server? Is it the same thing?

Grazi

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-21 : 08:53:31

Yeah, on the table on your local server it is just TableName, for the web server, it is Server.Owner.TableName

gbaia
Yak Posting Veteran

52 Posts

Posted - 2004-04-21 : 09:31:03

Cool! I'll try now :-)... Thank you so much!

Grazi

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-21 : 20:22:41

You will need to set the server up as a linked server first. You can do this inside Enterprise Manager pretty easily.

Damian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-21 : 20:28:17

You'll actually need to use the 4 part naming convention assuming you have a linked server setup as Damian mentioned:

ServerName.DatabaseName.OwnerName.TableName

Tara

gbaia
Yak Posting Veteran

52 Posts

Posted - 2004-04-23 : 11:30:52

oh no... I get an error... because I need to do:
serverName.DatabaseName.OwnerName.TableName.ColumnName
and it says: the number name... contains more than the maximum numer of prefixes, the maximum number is 3. How can I get around this? Thank you so much!!

Grazi

gbaia
Yak Posting Veteran

52 Posts

Posted - 2004-04-23 : 11:52:33

Ok, ignore my last message as I managed to work around with alias names.

Now... You're right I need to add them to the sp_addlinkserver...
Another question:
where do I find all the values for these parameters?
they're asking:
server - (that's fine)
srvproduct - (what is this?)
provider - SQLOLEDB
datasrc - johan or whatever
location - what is this?
provstr - what is this?

Sorry I'm sure I could find all this with a bit of research and I'll try now... but, it's for today and I'm leaving the office in 1 hour (I hope)...

Thank you so much for all your help!

Grazi

gbaia
Yak Posting Veteran

52 Posts

Posted - 2004-04-25 : 17:54:12

I've actually entered everything and it worked! One line was altered... however, when I run the query with the server names I get the error message:
'SQL Server does not exist'... ?

Sorry for being a pain! and thanks again for all the help!

Grazi

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-25 : 20:10:16

I've actually entered everything and it worked! One line was altered... however, when I run the query with the server names I get the error message:
'SQL Server does not exist'... ?

What line did you alter??? Can you give a before and after? How do you know the linked server setup worked? When you click on the linked server can you see tables and views?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

gbaia
Yak Posting Veteran

52 Posts

Posted - 2004-04-26 : 05:47:24

Hello!

I don't think it actually worked... what I mean by 'it worked' it's that it didn't return any error messages like before and the server has been inserted in the table 'sysservers'.

I managed to make it work with the right parameters in my local machine. I'm now running the sp_addlinkedserver in the webserver...

Could you let me know if the parameters are correct? (I'm really scared of messing up the webserver!)

@server: 192.34.556.8 (IP of the webserver)
@srvproduct: ' '
@provider: SQLOLEDB.1
@provstr: User ID= **; PWD=**;Initial Catalog=Image; Data Source=(Server2001)

Data Source is the name of the local server... Initial Catalog is the name of the DB on the 192.34.556.8 server.
Should data source be 192.34.556.8 again?

Sorry I just can't find information about this parameters and I'm pretty much guessing what they are.

Thank you so much for all the replies and help. I truly appreciate it.

Grazi

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-26 : 08:19:58

Do this.
1. Go into linked servers.
2. Select SQL Server on the first tab.
3. Go to security, click on "Be made using this security context"
4. Enter a valid SQL Server username and password.

Let me know if that won't work for you.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

gbaia
Yak Posting Veteran

52 Posts

Posted - 2004-04-27 : 04:25:11

Oh, I was really doing the long hard way! Thank you!

That makes things easier.

I made a test between our servers and it worked, all the tables and views are there.
Our web-server administrator will do it in his end! And I'll do it here.

I think it's all more clear now to me and it'll work...

Thanks again!

Grazi

vbseeker
Starting Member

8 Posts

Posted - 2004-06-25 : 08:00:24

Hi derrickleggett,

How can we do this using SP only.

Regards,
Anand

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-25 : 08:06:50

Look up sp_addlinkedserver and sp_addlinkedsrvlogin

Kristen
Test

22859 Posts

Posted - 2004-06-25 : 11:41:25

I use the following SQL "snippets" to set up linked servers. There are "parmaeters" at the toip to be replaced globally with Find&Replace, and then highligh&execute each, appropriate, statement in QA

Kristen

EDIT: Latest version of this script is here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164892



-- Create a Linked Server
-- Use this script to create a linked server by "Find&Replace" of key tokens

-- History
-- 26-Mar-2004 KBM   Started

-- Globally change the following:

-- IP Address: 111.222.333.444
-- Remote server name: MyRemoteServer
-- (This can be IP address in the format 123.456.789.123 
-- or name like SERVERNAME.HOSTS.MYDomain.COM)

-- User Login: MyUserID
-- User Password: MyPassword
-- (This should be a database login available on both machines with the same password)


-- *** Having done Find&Replace of parameters (above), highlight each section (below) and execute

USE master
GO

-- Show linked servers
EXEC sp_linkedservers


-- EXEC sp_helpserver			-- (Also shows services etc.

-- Delete any existing linked-server attempt (optional)
EXEC sp_dropserver 
	@server = 'MyRemoteServer', 
	@droplogins = 'droplogins'		-- 'droplogins' = Drop associated logins, NULL=Do not drop logins


-- Create Linked Server
EXEC sp_addlinkedserver 
	@server = 'MyRemoteServer'		-- local name of the linked server to create.
					-- If data_source is not specified, server is the actual name of the instance
,	@srvproduct = 'SQL Server'	-- product name of the OLE DB data source to add as a linked server
					-- If "SQL Server", provider_name, data_source, location, provider_string, and catalog do not need to be specified.
-- Execute ONLY to here IF you are connecting two SQL servers
	,@provider = 'SQLOLEDB'		-- unique programmatic identifier of the OLE DB provider (PROGID) 
	,@datasrc = 'MyRemoteServer'	-- name of the data source as interpreted by the OLE DB provider (DBPROP_INIT_DATASOURCE property)


-- Remove existing Linked Server Login
EXEC sp_droplinkedsrvlogin @rmtsrvname = 'MyRemoteServer'
,	@locallogin = 'MyUserID'


-- Create Linked Server Login
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MyRemoteServer' 
,	@useself = 'false'	-- true=Connect using current UserID/Password, false=use rmtuser/rmtpassword below
,	@locallogin = 'MyUserID'	-- NULL=All local logins use this remote login account, otherwise local login UserName being set up (repeat for each one required)
-- Execute ONLY to here IF @UseSelf='TRUE' (above)
,	@rmtuser = 'MyUserID'	-- UserName on Remote corresponding to this @LocalLogin.   
,	@rmtpassword = 'MyPassword'	-- Ditto password



-- Test connection - should list databases on remote machine
select top 10 name from [MyRemoteServer].master.dbo.sysdatabases



-- If you get this error message:
-- "Server '111.222.333.444' is not configured for DATA ACCESS"
-- then execute this statement
exec sp_serveroption 'MyRemoteServer', 'data access', 'true'

-- Test again!
select top 10 name from [MyRemoteServer].master.dbo.sysdatabases



-- Failing that try to PING the remote server (by Extended Procedure Command line call)
-- if that fails then your SQL box cannot see the remote server
DROP table #KBM_TEMP
go
declare	@strCommand varchar(255)

set @strCommand = 'PING 111.222.333.444'

create table #KBM_TEMP
(
	nRowID	int IDENTITY(1,1) NOT NULL,
	sText	varchar(255)
)

insert into #KBM_TEMP (sText)
	exec master.dbo.xp_cmdshell @strCommand
select * from #KBM_TEMP order by nRowID
go
DROP table #KBM_TEMP
go

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-28 : 06:02:31

Hi,

I have used the above post to create a linked server, I can see the tables ok. Is there any way to give the server an alias as at the moment it is named the name of the server. But I have around 40 databases there so I will have 40 with the same name just different connection details.

Kristen
Test

22859 Posts

Posted - 2004-06-28 : 06:27:17

Are all the databases on the same server? If so you can reference them with MyServer.MyDatabase.dbo.MyTable, you don't need a separate LinkedServer for each one - or have I got the wrong end of the stick?

Kristen

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-06-28 : 06:28:50

They are all on the same server but for security reasons each database has a different login.

Kristen
Test

22859 Posts

Posted - 2004-06-28 : 08:15:06

Ah, OK. You can set up the LinkedServer stuff to "pass through" the currently logged-in users details to the remote server - would that do?, or are you logging on to the local server with a single login and need it to link to each remote DB with a different UserID/Login?

I don't know what the Learned Thinking is on this - whether you should "campaign" for some special logins, or whether you should accomodate them all being different.

An alternative might be to have the Application get the user to provide login details for the remote DB's and use that as part of the process?

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources