Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 17th, 2005, 02:02 PM
Registered User
 
Join Date: Jan 2004
Location: , RI, .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default execute sql

I have a table called SearchStrings that stores SELECT statements as nvarchar(250). The SearchStrings table has an ID field and a SearchString field.

What is the syntax to create a View/SP/Function to execute the last SearchString in the above table?

I wrote a Function that gives me the last string but I still can't figure out how to execute that string.

Reply With Quote
  #2 (permalink)  
Old May 17th, 2005, 02:23 PM
Friend of Wrox
 
Join Date: Jan 2004
Location: , , USA.
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have not tried this but this will give you an idea

CREATE PROCEDURE ProcGetSearchString
AS
BEGIN

DECLARE @SQL nvarchar(250)
SELECT TOP 1 @SQL = SearchString
FROM TableName
Order By ID DESC

EXEC sp_executesql @SQL

END
Reply With Quote
  #3 (permalink)  
Old May 17th, 2005, 04:08 PM
Registered User
 
Join Date: Jan 2004
Location: , RI, .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

CREATE PROCEDURE ProcExecSearchString
AS
BEGIN

DECLARE @strSQL nvarchar(250)

SELECT TOP 1 SearchString AS [@strSQL]
FROM dbo.SearchStrings
ORDER BY ID DESC

EXEC sp_executesql @strSQL

END

The SELECT statement is all that is being returned. The EXEC statement doesn't seem to do anything. Any ideas?? I had to substitute the AS instead of the "=" assignment because of an error that was generated.

Reply With Quote
  #4 (permalink)  
Old May 18th, 2005, 08:17 AM
Friend of Wrox
 
Join Date: Jan 2004
Location: , , USA.
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

can you confirm that you have all the rights on your database tables?
******
When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.
********
Reply With Quote
  #5 (permalink)  
Old May 18th, 2005, 09:00 AM
Registered User
 
Join Date: Jan 2004
Location: , RI, .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You were right on target with that. As soon as I assigned myself to the db_owner role, the code worked. I had upsized this db from Access and I assumed I was owner, but I was only assigned to the Public role. I wish the error I had received would let me know it is a permission issue.

-- Thanks -

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQLExpress: 1.about "Execute sql button" 2.about garaxan SQL Language 1 September 3rd, 2007 07:26 AM
how can i execute SQL in UDF vinod_mnr SQL Server 2000 1 March 18th, 2005 12:51 PM
sql execute from an include. robprell PHP Databases 1 October 4th, 2004 12:28 PM
how do I execute an SQL update chrisangus@btinternet.com VB How-To 1 June 22nd, 2003 06:50 AM



All times are GMT -4. The time now is 08:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.