I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
I would like to access a table in MS Access from a stored procedure on MSSQL 2000. The MS Access DB is running on a diferent computer attached on thesame network. I think I need to create an ODBC connection from the MS SQL2000 to the MS Access DB but not sure. Secondly how do I refer to the tablein the MS SQL stored proc?Thanks for your input,Joe Seamour
I want to load a file into a blob column using a stored proc. The file will be on a UNC path on the network. Is there a system stored proc or any utility that can be fired off from the sp, using xp_cmdshell that will take a file path from the filesystem and load it into a blob column in a database table.
I have worked with blob fields in the past but in those instances I loaded the blobs into the database from front end ADO.net code. This time around I need to pull this off stricktly from a stored proc.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug". You can then step through the SP one line at a time.I don't see this in SQL Server 2005 and have searched bu cannot find any documentation on what happened to this feature.What did Microsoft do with this?
Assume I've created a web service named 'SaveActivityData' for some SQL 2005 database on some remote server. Also assume that the stored proc takes two parameters; the first is an integer value and the second an XML value.
1) Is it possible for a stored procedure on a completely different SQL 2005 server to consume this web service?
2) If the above is possible, could someone show me an example of the T-SQL syntax required to consume this web service, passing the two required parameters? In your example, you can make up whatever URL you like for the remote web service location.
I am moving a fairly large app from Access 2003 to C#/SQL Server 2005.
The app has one front end mde, but it has two backend data mdbs, one is used in the US and one in the UK.
The two backend mdb's are identical in structure, but of course the data is different. There are 150 tables or so, and we plan to maintain separate databases after the upgrade.
Now, of course, all of the queries are in the front end, the backend db's contain only tables.
In the new world, I do not want to have to maintain the same views and stored procs in two different SQL Server databases.
Is there a way to have all views and stored procs in one database, but have them draw on the data in the two other databases. There must should be a way to deal with this scenario in SQL Server.
In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".
You can then step through the SP one line at a time.
I don't see this in SQL Server 2005 and have searched but cannot find any documentation on what happened to this feature. What did Microsoft do with this?
Hi all, I have a problem while i create a proxy account in SQL Sever 2005.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --. But this job fails and gives the error message as [298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Hi, In SQL 2000, to debug a stored proc I would launch QA, right click and hit debug. How do I accomplish this with SQL 2005. I can't see that it came with QA. Thank you, Steve
I am new to web services and as a DBA I only have limited .net experience. Our development team is creating a web services interface for our new IVR phone system, and we've decided on a solution that will send each of the phone call parameters from our database to the phone system in an XML based on line number and extension. I've found that Http Endpoints in sql server will be a perfect solution for exactly that and I won't need any .net to develop it. Thats the good news. There are, however, other requirements for our phone system to communicate back to our database. All of the examples I've seen for endpoints use a stored procedure performing a select statement.
Is it possible to create an endpoint that references a stored procedure performing an insert or update? To be more specific, can I create an endpoint that references a stored procedure that has parameters? If so, how do I pass those parameters through a web service request? Is it as simple as adding &variable="value" to the url?
If so I could develop this entire solution from the database side and not involve our .net programmer!
Another alternative: the phone system does have it own database in sql server. I'm not sure if our phone system provided can or will do this, but if they are willing to work with this, maybe a service broker can fit into this solution.
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Hi! I use sql 2005 express on my computer for testing. The server that hosts my site is a sql 2000 server. Therefore I tuned the one database I use down to sql 8. When I create stored procedures from web developer express, to the local sql 8 database I have in my sql express, it works nice. But when I copy all my files and my stored procedure to the webhost, it doesn't work.
Do you guys have any suggestions? Thanks in advance.
i have a assignment on hands to transfer store procedures of 2005 to 2000. I have tryed by scripting SPs and then runing them in 2000 QA, but it gives me error every time ....... so please help me to resolve this problem.
I am writing a sales managing software using C# in .NET 2005. The program was used to work properly with sql server 2000. I decided to write a "FILE Version" of sofware using sql server express 2005 , and detached db from 2000 , attaching it to 2005 using AttachDBFileName clause in connection string.
But the problem is when program executes something like this: EXEC [AddOrder] ... two rows inserted instead of one.and when I use server explorer of VS2005 to launch SP, it works fine. I should mention again that same code works correctly when I change connection string and force it to use sqlserver 2000.
I have a rather complex sp that runs for 4 minutes in SQL2000. It computes some messy oil and gas revenue/cost transactions. It involves lots of calls to functions that return single values of all types and also returns recordsets. There are all kinds of joins with temp and memory tables, etc. Just a mess, but it works. However, in SQL2005, it runs and apparently hangs, as it never ends.
I have run the Upgrade Advisor and otherwise, have not found any information that tells me that there are issues with functions or SQL-specific functions, tables, etc. that might cause this. Does anyone on this forum have some pointers on where I might look for assistance on this matter? Surely someone knows something about things working differently in 2005.
Hi allI am looking for the best method to automate a website's database management. Lets say I have a user registration database and the users register. This sends an automated email to the user with a link to activate the users registration. If the user does not register within 24 hours, his registration must be automatically deleted from the database using a stored procedure.I know how to do this using the global.aspx file, however there must be an alternative way of doing this, especially if the database is an SQL database. I do not know how much MSSQL server access is given to a developer by an as ISP who hosts the website.Can anyone tell me what would be the best method to use.ThxWarren
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
