I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.
Hello,I have been searching and reading a lots of information on the microsoft website about the different version of SQL server, but still can not make my decision.In term of performance, is there a real big difference between the workgroup and the standard version? The workgroup is limited to 3 GB of RAM while the standard is unlimited, would that really change the performance if my server has 16Gb or RAM?The price difference is pretty substantial so if could only have to buy the workgroup , it would be better.One more question, regarding the type of licence, my server has 2 processors, could I avoid buying 2 licences and get the Server plus CAL instead. I am using this server to host 4 web-application running on SQL server. Each database is about 15 MB.Thanks in adavance for your advises.Arno
In MSDE sql server 2000 dtsrun was available to execute the dts packages but now with sql server 2005 express edition integration service is not part of express/workgroup edition and the dtsexec run time is also not available.
When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:
"Object reference not set to an instance of an object"
Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:
1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:
create proc usp_testsp
as
begin
select 'whatever' ;
end
2. I create a new SSIS package in BIS.
3. I create an ADO.NET connection to the above SQL 2005 database
4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.
5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None
When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.
And here's what I get with ResultSet set to Single row:
Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.
Does anyone see the error below before? I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure. It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY €œObject Reference Not Set to An Instance of an Object€? when I click on PARSE QUERY.
This is going against SQL SERVER 2005 SP2 x64 Enterprise.
Note that this task executes fine and the stored procedure updates data.
The stored procedure does the following. There are other stored procedures of different kinds and they all worked. But all of them give this error when I click on PARSE QUERY.
Code Snippet DECLARE @TodayDate datetime SET @TodayDate = GETDATE()
My product was developed for and works correctly on SQL Server 2000. However, when we upgraded to 2005, we found that certain system stored procedures were different, causing our product to break.
We can easily change our stored procedures to work in 2005, but we have a large client base, some of whom will be using each version. Our current solution is to check the version of SQL Server during installation and choose which script to use at that time in order to have an appropriate stored procedure for that version, but we are concerned about users who install our product with SQL Server 2000 and then upgrade to SQL Server 2005.
How can I make a stored procedure that will run differently depending on the version? I tried something like:
if (select charindex('2000', @@version)) > 0
begin -- SQL Server 2000
SELECT
... FROM
... WHERE end else -- SQL Server 2005
SELECT
... FROM ... WHERE end
Unfortunately, the system tables I'm selecting from have different stuctures in the different versions (one example is msdb.dbo.sysjobschedules and msdb.dbo.sysschedules), and even though the code never gets into the SQL Server 2000 section on 2005, it parses the whole procedure for errors before allowing it to be saves and will not allow this.
When I create the chart from the query it works fine but when I convert the same query to the procedure I get the following error. I have proper execute permission of the users.
PLEASE HELP...........
Server Error in '/' Application.
EXECUTE permission denied on object 'mscrm_procname, database 'Servername', schema 'dbo'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
(Didn't know in wich group this question belongs....) Hi group,
In QA there is an option called 'Script object as....' Were developing a lot of sp's. When I use this option the 'skeleton' of a sp is created. Eg Create proc uspMySP @Message varchar(10) AS SELECT @Message
The mention option would generate the following: DECLARE @RC int DECLARE @Message varchar(10) -- Set parameter values EXEC @RC = [EMGLSP3].[EM3_OWN].[uspMySP] @Message
The question: Is it possible to customize this? I would like to *** SET statements... Is this possible?
I add DB to my apllication using the ASPNET membershipBut I got this ErrorEXECUTE permission denied on object 'aspnet_CheckSchemaVersion', Here is the problem page:http://www.tslaw.co.il/default.aspx any Idea how can I fix this problem.Thanks
Environment: Visual Studio .NET 2003 on pc (Windows XP Pro) in workgroup A, ASP .NET application on server (Server 2000 w/ IIS 5 in workgroup A), SQL Server 2000 on same server (Enterprise Edition)..........all on Intranet with static IP's for all machines.
I have the {servername}VSDevelopers group with sysadmin priveleges on SQL Server. I have the {servername}ASPNet user with public on SQL Server.
I can create stored procedures in database ABC on SQL Server from Visual Studio on pc(default named to dbo.{stored_procedure_name}).
I can edit same stored procedures in database ABC on SQL Server from Visual Studio.
I get EXECUTE error when trying to run stored procedure via ASP.NET application on server.
I check, and permissions to objects on SQL Server for VSDeveloper group shows as no permissions.
I set the permissions for all user created objects (tables, stored procedures) to "full" for VSDevelopers.
trying to execute an ssis package from the web - classic asp page
I am using windows authentication to login to the web site
which I assume means i am executing the package under that account
That account being administrator
I am using the code below to execute
Dim objWshShell, obj, objStdOut Set objWshShell = Server.CreateObject("Wscript.Shell") set obj = objWshShell.EXEC("cmd /c dtexec /sq single /ser MPDNETWEB")
this runs find on the command line but I cannot get it to run from the web page.I get the following error.
The LoadFromSQLServer method has encountered OLE DB error code 0x80040E09 (EXECUTE permission denied on object 'sp_dts_getpackage', database 'msdb', schema 'dbo'.). The SQL statement that was issued has failed.
I get this error when I try to preview a report that uses a stored procedure. The stored procedure does not exist in the database, but in the directory I run the report from. The stored procedure declares which database to use, and to create the temp table I want. Do I require special permissions on the sql db I am using to be able to create temp tables? Any help would be great, thanks!
A development database server is receiving "EXECUTE permission denied on object 'sp_fulltext_getdata'" when trying to run fulltext-related commands such as the following:
These commands appear to run successfully in that the job does not fail or report any error messages, but when I enable auditing for Error 229 the error is logged for every execution. The job is run under the security context of the SQL Server service account so permissions should not be an issue. This problem does not occur on any other servers and I can replicate the problem on the same server by creating a fulltext index in another database and running the same commands.
Any suggestions?
By the way, the server is SQL Server 2000 SP3a Enterprise Edition.
Last week, I set up a SQL Server login and assigned it to the MSDB role of SQLAgentOperatorRole. A couple of jobs were created and this login was assigned as being the owner of those jobs.
The login was able to successfully edit and execute the jobs. Per the documentation, only these 2 jobs would show up in the jobs list for the login to view.
Now, when the login attempts to expand the jobs list, the following error appears: EXECUTE permission denied on object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'
I'm not excited about granting explicit execute permissions to extended stored procedures . . . . but will if I have to.
I think the senior DBA changed something that hosed this up.
How do you use sqldmo object to execute system stored procedures? Anysuggestions would be appreciated.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have SQL Server 2005 database, and have linked it with three SQL Server 2000 databases, and using Windows Authentication.
I'm using ASP.NET with my main database (SQL Native Client), which is authenticating to the linked databases with the computer hostname (DOMAINCOMPUTER$) credential, which is all working fine on all databases for doing SELECT queries.
However, now I'm trying to run a stored procedure on the linked databases. Two of the three databases are working flawlessly in this regard, but the third I'm getting the error: EXECUTE permission denied... I've checked the logs, and it's showing the connection as being trusted, and I've already added the execute permission for the stored procedure in question, but I'm not getting anywhere.
I have restarted the database server, but that hasn't helped any.
In order to enable the report cache and set data refresh options, in sharepoint library on report file,I am selecting "Manage processing options" as mentioned in MSDN
[URL] ....
But I am getting error "EXECUTE permission was denied on the object xp_sqlagent_enum_jobs" I have granted the execution rights for all procedures as mentioned here but still error isnot going
[URL] .....
Where can I find SSRS failure logs, I can see execution3 but it is not storing this information...
hi,I use DataGrid in name UserTable.I use this code: SqlCon.Open() UserTable.DataSource = SqlCom.ExecuteReader(System.Data.CommandBehavior.CloseConnection) UserTable.DataBind()and i get this error: EXECUTE permission denied on object 'Test', database 'DI', owner 'dbo'I craete stored prcedure in name Test with the simple SQL code:CREATE PROCEDURE [dbo].[Test] ASselect Users.*from UsersGOIf instead i put the SQL code : select Users.* from Usersin my command as a text i get the error : SELECT permission denied on object 'Users', database 'DI', owner 'dbo'i have already create a local premmision for my DB & tables as MYMACHINE/ASPNETHow i can solve this problem?Thanks, Moshe
I have a user login that is executing an sp. It gets the follwoing error: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1 EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1 EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1 EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1 EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1 EXECUTE permission denied on object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1 EXECUTE permission denied on object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'. Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line 1 EXECUTE permission denied on object 'sp_OADestroy', database 'mssqlsystemresource', schema 'sys'.
Everything ic an find on the net refers to Sql Server 2000 but this is 2005, all the resolutions say you must grant exec permissions to the user account for these sp's in the master database.
BUT is SS2005 they are in the mysqlsystemresource database.
WHen i try the following
grant exec on mssqlsystemresource.sys.sp_OACreate to UserLogin:
I get this error:
Cannot find the object sp_OACreate, becuase the object does not exist or you do not have permission.
I am logged in as sysadmin so i doubt it is permission.
How do i get a user login to be able to exec these sp's?
Hi!I have a problem. i have created a website with a login page, i have moved all my aspnetdb tables to my remote host. but when iam trying to logon i get this error messages. EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'dbname', owner 'dbo'. Someone? I really need help...
I just installed this application on my new server and I get this error for each object in the database including the tables and the stored procedures. I have been going in for each one and opening the properties and then going to permissions and making the changes for each one. Is there a way to do this for the entire database at once? Thanks
I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error. EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'. How canI solove this problem?
I copy script that use SQL proc to call DTS package from this site.
When I run this procedure in SQL query alalyzer. I got an error messages: EXECUTE permission denied on object sp_OACreate/method/GetProperty/Destroy. I did not change anything in spExecutePKG. Even I used sa as ServerPWD. How to fix this problem? my code is exec spExecutePKG 'myserver','execl_DTS','sa','0','' Thanks jimmy
------proc original code CREATE PROC spExecutePKG @Server varchar(255), @PkgName varchar(255), -- Package Name (Defaults to most recent version) @ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME()) @IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security @PkgPWD varchar(255) = ''-- Package Password AS SET NOCOUNT ON /* Return Values - 0 Successfull execution of Package - 1 OLE Error - 9 Failure of Package */ DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT IF @hr <> 0 BEGIN PRINT '*** Create Package object failed' EXEC sp_displayoaerrorinfo @oPKG, @hr RETURN 1 END
I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error.
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.
I am having this issue with my application developed in VB.Net. The thing was working properly for the last couple of years. But suddenly it is giving me this error message when ever I try to execute the following code.
mycommand = New SqlCommand(sqlstr, SqlConnection1)
SqlConnection1.Open()
mycommand.ExecuteReader()
The operation is never related to sending e-mail and even the master Database, it is used just to display records on grid.
Here is the error message comeing out when it reaches the mycommand.ExecuteReader():
EXECUTE permission denied on object 'xp_startmail', database 'master', owner 'dbo'.
I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:
1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )
2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).
In my ssis 2012 package, I have a 'object' type variable with some table like records. I want to do some SQL operations like insert/update on the records in another table based on this 'Object' type variable records. Basically I want to use a MERGE statement with another physical table with the records in the 'Object' type variable.how to map/use the Object type variable in Execute sql task.I am not good in script task. How to utilize this Object variable in a Execute sql task? Â
I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.
BEGIN SET NOCOUNT ON; WITH Darin_Import_With_Key AS ( SELECT [pra_id] ,[pra_ClientPracID]
I have an application that uses Integrated Windows authentication. My Web.config looks like below <add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" /> When users try to access my application, they get the below error: Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo' The Only way I could get rid off the error is if I set DBO permissions for the user group on the databse. Can someone suggest how to set up a security group with the ‘necessary’ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO. Thanks,
Is there any way to restore a database of higher version to lower version.
E.g. I have created a database in sql server 2012, created some tables & procedures in that.I took Full backup of that database. Can I restore it to sql 2008r2 or any lower version.
I know direct restore is not possible, I have to use either import or export option or generating script,but i want to know is there any easy step to do so.
Since some analysis services features are only available in Enterprise version , I have to upgrade my SQL 2005 server from standard edition to enterpise edition.
So I uninstall originial standard version of analysis service and install a Enterprise version. However, the analysis service is still a standard version after installation.
Is it possible to keep data engine as standard version and install a enterprise version of analysis service?
I am wondering if it is possible to change from SQL Server Enterprise Evaluation Version to Developer Version. The reason is because the Enterprise Evaluation version expires after 180 days. So if I create reports and cubes in Enterprise Evaluation I can import them into Developer Edition right? Should I remove the Enterprise Evaluation version after 180 days or leave it then install Developer Edition?
