Need help syntax to create and copy a table

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

Development Tools

ASP.NET

Need help syntax to create and copy a table

Author

Topic

marc11h
Starting Member

2 Posts

Posted - 2011-07-17 : 19:31:04

I am trying to pass a value to a Stored Proc, that value is the name of a table that I want the SP to create first by copying data from an exiting table then creating the PK for the new table. Here is the VB code behind that calls the SP, and below is the SP.....

  Dim myString As String = "tblLangEN" 
 
        itemDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("myConnString").ToString() 
 
        itemDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure 
        itemDataSource.InsertCommand = "usp_createNewLangTable"      ' Call stored procedure 
 
        itemDataSource.InsertParameters.Add("langTable", myString.Trim())

Here is the SP....

ALTER PROCEDURE usp_createNewLangTable
	/* *********************************************
	  
	
	*/
	
	@langTable varchar(50)
	
	
		AS  
		SET NOCOUNT ON;   
		  
		DECLARE @sql varchar(max);   
			BEGIN
				SET @sql =  'SELECT * INTO ' + @langTable +' FROM UniqueStringsMaster';
			END
		print (@sql) 
		EXECUTE(@sql);
		
		SET @sql = ''	
			BEGIN
				SET @sql = 'ALTER TABLE ' + @langTable + ' ADD CONSTRAINT String_ID PRIMARY KEY (String_ID)' ;
			END
		print (@sql) 	
		EXECUTE(@sql);
			
		SET @langTable = Null;
		
		RETURN

Not sure why this is not working, it works in SQL Mgmt Studio

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2011-07-18 : 02:00:09

Very likely that the SQL login that is used in your application does not have adequate permissions, particularly as you are using dynamic SQL. You most probably logon to SQL Management Studio using an SA or windows login that has full permissions. Take a look at the permissions defined on your application login.

However, I don't like the idea of having an SP that can trigger DML changes in your database, particularly when you can call it from directly from your application. What happens if your application gets compromised? The hacker would then have sufficient privileges to modify any part of your database. Also, you should have some fail-safe in place, such as checking if the column already exists before you run the statement. Good coding practices can go a long way in preventing some major pain when you go live with your application.

OS

marc11h
Starting Member

2 Posts

Posted - 2011-07-18 : 07:43:44

I agree, this was to just try and get the code to work. The intention is for the web app to dynamically build the tables as needed. I am adding an IF condition to check if the table already exists.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources