I'm trying to create a simple stored procedure which queries a sys.tables table. CREATE PROCEDURE dbo.test @dbname NVARCHAR(255), @col NVARCHAR(255) AS SET NOCOUNT ON SET XACT_ABORT ON USE @dbname SELECT TOP 100 * FROM sys.tables WHERE name = @col GO This does not seem to work cause I should put GO ...
Hi, we have a stored procedure that ran fine until 10 minutes ago and then it just hangs after you call it. Observations: Copying the code into a query window yields the query result in 1 second SP takes > 2.5 minutes until I cancel it Activity Monitor shows it's not being blocked by anything, it's just doing a SELECT. Running sp_rec...
I created a sql 2005 stored proc to tell me if the CRID I am searching for is approved by the supervisor. [SuperApproved] is a bit, [CRID] is char(36). Even if the CRID doesn't exist, I am still getting 1. Any help in writing this? USE [cr_Saturn2] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- =================================...
I hava a stored procedure named PROC_fetchTableInfo (simple select from sys.tables) in a database named Cust I would like use this procedure in a another database named Dept I tried to execute this sp in this database using command EXECUTE Cust.dbo.PROC_fetchTableInfo but as a result I get tables from the database Cust. How to g...
Is it possible to edit the data returned from command.ExecuteReader, and then return that to SqlContext.Pipe.Send()? Are there any forseeable issues (I have to reset the cursor to the beginning)? I have a .NET stored procedure that will query a table like this (code from MSDN) public class StoredProcedures { /// <summary> ///...
I'm a junior developer in our team. In one project we have about twenty databases in one SQL Server instance. We have db_owner rights to these databases. My intention is to monitor certain things from these databases (e.g. file size). But because we don't have sysadmin rights, we don't have all those management tools for these database...
This stored procedure doesn't work. I've checked the SQL and it returns the correct value when parsed directly to the DB. It's really strange! It just returns 0 rows. What could be wrong? ALTER PROCEDURE dbo.GetSaltOfUser ( @eMail nvarchar ) AS DECLARE @result nvarchar /* SET NOCOUNT ON */ BEGIN SELECT @result = salt FROM ...
In MySQL I want to write a script and put multiple CREATE PROCEDURE statements into it, the usual (;) wont work for some reason. Is there another way to run multiple CREATE statements in the same script? If so, how? ...
I want to create a procedure that will A) check to see if TicketNumberInput is in table Tix_number.TicketNumber. B) if it does exisist, update the record to set UID to @uid and Set the Claimdate to GetDate() AND ... C) INSERT A RECORD INTO ANOTHER TABLE (TRANSACTION LOG TABLE) If the record does exist, simply add a record to the tr...
I'm trying to write a Stored Procedure which'll get a string, hash it with SHA1 and then return the hash. I can't seem to make it return @hashedString. I'll admit I'm a total beginner with T-SQL. I'm writing the T-SQL directly in the db. This is what I've gotten up to now: ALTER PROCEDURE dbo.ConvertToHash ( @stringToHash nvarcha...
I've created a stored procedure that takes parameters to create a user. If the user already exists it sets the output parameter to 'User already exists' and does nothing more. Now I've mapped this function (InsertNewUser) to my Entity Framework and am calling it like so: context.InsertNewUser(email, name, passwordhash, salt, ???) ...
Hi Everybody, i want to know whether views or stored procedures are faster. i am developing application in asp.net with sql server. Which one should i use for good performance of asp.net application. Thanks ...
Sorry, lots of code coming up.. I saw another question like this that used output parameters. I'm using the RETURN statement to return the value I want to use. I have one stored procedure InsertMessage that looks like this: ALTER PROCEDURE dbo.InsertNewMessage ( @messageText text, @dateTime DATETIME, @byEmail bit, ...
I don't know why, but CLR User Defined Functions are not allowed to update tables. Is it possible to work around this restriction by calling a stored procedure from the CLR UDF that updates tables for it ? ...
I need to pass a table from Access to SQL-server and execute a stored-procedure. I'm using pass-through queries in Access to do this. My pass-through query: DECLARE @MyVar TABLE { .....<variables> } INSERT INTO @MyVar SELECT * FROM [MyTable] EXEC sproc_test @Myvar My stored-procedure: ALTER PROCEDURE [dbo].[sproc_test] @M...
I have an insert statement that was deadlocking using linq. So I placed it in a stored proc incase the surrounding statements were affecting it. Now the Stored Proc is dead locked. Something about the insert statement is locking itself according to the Server Profiler. It claims that two of those insert statements were waiting for the ...
Hi i am working on crystal reports 11. Just added a stored proc with 2 parameters. but refreshing the report does not prompt for new parameters. Any suggestions. Thanks Amrita ...
Hi I got stuck not knowing how to create API in PHP. I have store procedure that I created in the MySQL and want the PHP to call that store procedure and use the value return from the store procedure as return value of the API. There might be many ways to do it and I would like to know all of them and want to know how they are differen...
I have 2 questions : i) How can put this code in a transaction ? With ObjectContext in EF, I use ExecuteStoreQuery() method to start some stored procedure. I have a block of code like this : { foreach(...) { objectContext.ExecuteStoreQuery( @"INSERT MyProcedure (arg1, arg2) VALUES ({0}, {1}...
Hi! I have this Table structure: Id int not null --PK Title varchar(50) ParentId int null --FK to same Table.Id I'm writing a SP that returns a row's "brothers", here's the code select * from Table where Table.ParentId = (select Table.ParentId from Table where Table.id = @Id) and Table.Id <> @Id It works perfectly for rows having ...