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  SQL Server Development (2000)  creating a sp using asp

Author  Topic  trxman Starting Member 7 Posts Posted - 2001-06-04 : 22:29:14 Is there a way to add a stored procedure to my database when I am not hosting the database myself. In other words I do not have access to the Enterprise Manager. I have search the archives and though this would have come up before. My idea was to use ado to do it. I run this file on my server. I get an error. I think it is to do with the with the plus (+) signs. The plus signs are required as it is a dynamic sp. Does anyone have a better way to do it ? Many thanks, Trav =============== This is the file I created, createsp.asp <% ssql = "CREATE PROCEDURE sp_Pagedbook ( @Page int, @RecsPerPage int, @book varchar(50), @tabletouse varchar(50))" ssql = ssql & " AS SET NOCOUNT ON" ssql = ssql & " CREATE TABLE #TempItems(ID int IDENTITY, progid int, title nvarchar(50), series nvarchar(50), show_date smalldatetime, speaker nvarchar(50)" ssql = ssql & " DECLARE @SQLStatement varchar(500)" ssql = ssql & " SELECT @SQLStatement = ""INSERT INTO #TempItems (progid, title, series, show_date, speaker)" ssql = ssql & " SELECT progid, title, series, show_date, speaker FROM "" + @tabletouse + "" WHERE book LIKE "" + @book + "" OR show_text LIKE "" + @book + "" AND show_date <= GETDATE() ORDER BY show_date""" ssql = ssql & " EXEC (@SQLStatement)" ssql = ssql & " DECLARE @FirstRec int, @LastRec int" ssql = ssql & " SELECT @FirstRec = (@Page - 1) * @RecsPerPage" ssql = ssql & " SELECT @LastRec = (@Page * @RecsPerPage + 1)" ssql = ssql & " SELECT *, MoreRecords =(SELECT COUNT(*) FROM #TempItems TI WHERE TI.ID >= @LastRec), TotRecords = (SELECT COUNT(*) FROM #TempItems TI)" ssql = ssql & " FROM #TempItems" ssql = ssql & " WHERE ID > @FirstRec AND ID < @LastRec" ssql = ssql & " SET NOCOUNT OFF" myDSN="DSN=dsnname;uid=userid;pwd=password" set conn=server.createobject("adodb.connection") conn.open myDSN set rs = conn.execute(ssql) %> ================== the result I get Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'INSERT INTO #TempItems (progid, title, series, show_date, speaker) SELECT progid, title, series, show_date, speaker FROM '. /cworks/admin/createsp.asp, line 27