Creating table in different database

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

General SQL Server Forums

New to SQL Server Programming

Creating table in different database

Author

Topic

Macro1
Starting Member

8 Posts

Posted - 2010-10-14 : 05:03:11

Hi,

This is my first post so apologies if I have left anything out thats needed.

I have a neat little stored procedure held in a database thats used for all of our stored procedures. What it essentially does is creates a table with the values that the user has selected from a different database on a different server. I'm passing the server name and database name as a variable as there will be many users using this query with a different database being used everytime.

This all seems to work great however I can't get the table to be created in the database that the original values are coming from it keeps creating the table in the same database where the stored procedure is held. I'm sure its something simple but I'm all out of ideas.

Any advice would be much appreciated

Thanks in advance

Macro1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 05:10:03

create table databsename.schemaname.tablename ( col1 int, ...)
or
create table databsename..tablename ( col1 int, ...)
to take the standard schema

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-14 : 05:10:13

If you are having just statement like "Create table " then it will be in same DB where SP is present.

Can you show us the code portion where your are creating the table to help us to help you.

Macro1
Starting Member

8 Posts

Posted - 2010-10-14 : 05:19:33

Thanks for the quick replies, here is the code that I used to get the table to create. There is more to this stored procedure if needed.

Cheers

SET NOCOUNT ON

DECLARE @TableName VARCHAR(100), @sqlstr VARCHAR(8000)

SELECT @TableName = 'Map_'+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@PortNum, ' ',''),'+',''),',',''),'&','')))
FROM #tmp_Map
INNER JOIN ASPSP0036.2008_EUROPE_EUR.dbo.vw_map AS vw_map1 
ON #tmp_Map.MAPID = vw_map1.MAPID

SELECT @sqlstr = 'SELECT #tmp_Map.MAPID,'

+ CHAR(10)
+ '#tmp_Map.COUNTRY,'
+ CHAR(10)
+ '#tmp_Map.LOCATION,'
+ CHAR(10)
+ '#tmp_Map.VALUEUSD,'
+ CHAR(10)
+ '#tmp_Map.VALUE,'
+ CHAR(10)
+ 'vw_map1.VALUEUSD AS MrkVALUE,'
+ CHAR(10)
+ '#tmp_Map.VALUEUSD / vw_map1.VALUEUSD * 100 AS Mshare'
+ CHAR(10)
+ 'INTO ' + @TableName
+ CHAR(10)
+ 'FROM #tmp_Map '
+ CHAR(10)
+ 'INNER JOIN SERVER1.EUROPE_VALUES.dbo.vw_map AS vw_map1 '
+ CHAR(10)
+ 'ON #tmp_Map.MAPID = vw_map1.MAPID'

SELECT @sqlstr
EXEC(@sqlstr)
SET NOCOUNT off

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 05:28:55

SELECT @TableName = 'DATABASENAME.SCHEMANAME.Map_'+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@PortNum, ' ',''),'+',''),',',''),'&','')))
FROM #tmp_Map

or you need more parameters to get DB and Schema.

No, you're never too old to Yak'n'Roll if you're too young to die.

Macro1
Starting Member

8 Posts

Posted - 2010-10-14 : 05:40:33

Thanks,

So if I'm passing these variables at the top of my code like so:

ALTER PROCEDURE [dbo].[Map_File] (@ServerName varchar (20), @DBName varchar(50), @PortNum AS VARCHAR(50))

I need to change my code to

SELECT @TableName = '@DBName.SCHEMANAME.Map_'+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@PortNum, ' ',''),'+',''),',',''),'&','')))
FROM #tmp_Map

Cheers Again

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 05:45:27

No.
SELECT @TableName = @DBName+'.SCHEMANAME.Map_'+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@PortNum, ' ',''),'+',''),',',''),'&','')))
FROM #tmp_Map

or

SELECT @TableName = @DBName+'..Map_'+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@PortNum, ' ',''),'+',''),',',''),'&','')))
FROM #tmp_Map

No, you're never too old to Yak'n'Roll if you're too young to die.

Macro1
Starting Member

8 Posts

Posted - 2010-10-14 : 05:54:51

Thanks

I'm now getting

Msg 2702, Level 16, State 2, Line 1
Database ASPSP00362008_EUROPE_EUR' does not exist.

Do I need to add the Server variable before the @DBName as well?

Cheers

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 06:18:46

Is the database existing?
Is it on a different server?

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-14 : 06:19:02

quote:
Originally posted by Macro1

Thanks

I'm now getting

Msg 2702, Level 16, State 2, Line 1
Database ASPSP00362008_EUROPE_EUR' does not exist.

Do I need to add the Server variable before the @DBName as well?

Cheers

If database reside on different server then you need to add it. If database is in same server then its optional.

Macro1
Starting Member

8 Posts

Posted - 2010-10-14 : 06:23:05

Yes the database could be on one of two servers depending on which the user wants to use. I set up a variable called @ServerName and on receiveing the error message I tried the following:

SELECT @TableName = @ServerName.@DBName+'..Map_'+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@PortNum, ' ',''),'+',''),',',''),'&','')))

But still am still getting the same error, its like it needs to regonise the break between the Server and the database so it reads like so 'ASPSP0036.2008_EUROPE_EUR' rather than 'ASPSP00362008_EUROPE_EUR'.

Cheers again

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-14 : 06:51:23

quote:
Originally posted by Macro1

Yes the database could be on one of two servers depending on which the user wants to use. I set up a variable called @ServerName and on receiveing the error message I tried the following:

Try

SELECT @TableName = @ServerName+'.'+@DBName+'..Map_'+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@PortNum, ' ',''),'+',''),',',''),'&','')))

But still am still getting the same error, its like it needs to regonise the break between the Server and the database so it reads like so 'ASPSP0036.2008_EUROPE_EUR' rather than 'ASPSP00362008_EUROPE_EUR'.

Cheers again

Macro1
Starting Member

8 Posts

Posted - 2010-10-14 : 07:08:01

Thanks Guys for all your input, I really appreciate it. I'm now getting the follwoing error:

Msg 117, Level 15, State 1, Line 9
The object name 'ASPSP0036.2008_EUROPE_EUR..Map_EuropeAgrGlh' contains more than the maximum number of prefixes. The maximum is 2.

Would it help if I sent the whole code?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 10:28:00

pk_bohra hasn't told you that a different server has to be a linked server on the actual server.
If you don't know about that then you have to read...
In first step you also can go on to develop your thing only on the actual server...

No, you're never too old to Yak'n'Roll if you're too young to die.

Macro1
Starting Member

8 Posts

Posted - 2010-10-14 : 12:19:51

Hi Guys,

I've just been told that the way I'm trying to do this is not possible with our current setup. What I have been advised to do is to create the tabel first in my desired database using the variables. Now I guess my question is how do I do that?

So far I have:

CREATE TABLE 'Map_'+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(@PortNum, ' ',''),'+',''),',',''),'&','')))
	(
	ID VARCHAR(10) NULL,
	COUNTRY VARCHAR(2) NULL,
	location VARCHAR(5) NULL,
	ValueUSD FLOAT NULL,
	Value FLOAT NULL,
	MrkValue FLOAT NULL,
	Mshare FLOAT NULL
	)

Do I need to

1 - Put the Server and the database variable into the code first
2 - Do I need to declare the table name so that I can insert the data.

Sorry for all the questions but I really thought this would be a lot simpler even for my limited SQL skills,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources