I'm working on a stored procedure for a calendar application. Each event in the calendar can have several dates. This information is stored in two different tables. Rather than write two stored procedures and call the second one multiple times to save the dates, I'd rather just pass them in using XML. The trouble is that I want to conver...
Hi i want to execute this StoredProcedure from c# program How can i do this using c# Any Help is Greatly Apreciated I have written the following stored procedure in sqlserver query window and saved it as stored1 use master go create procedure dbo.test as DECLARE @command as varchar(1000), @i int SET @i = 0 WHILE @i < 5 BEG...
Hi All, I'm trying to understand a historical stored procedure I need to fix. I found this DRVTBL key word, and I couldn’t find out what it means??? (This is a sql2000 database) SELECT ... FROM ( ...) ) DRVTBL ...
I have quite a few stored procedures following the pattern of selecting the row for which a date column is the latest up to a certain date, inclusive. I see two forms in use: select top 1 x, y, z from sometable where a=b and date <= @date order by date desc or select x, y, z from sometable where a=b and date=(select max(date) from so...
So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a 'running total' into a third temp table which is created using CREATE. Then this resulting temp table is joined with other ...
I Have created a Stored procedure dbo.test as follows use sample go create procedure dbo.test as DECLARE @command as varchar(1000), @i int SET @i = 0 WHILE @i < 5 BEGIN Print 'I VALUE ' +CONVERT(varchar(20),@i) SET @i = @i + 1 END Now i am created a c# console application to call the stored ...
This works, but i would like to remove the redundancy. Is there a way to merge the update with a single select statement so i don't have to use vars? DECLARE @OrgAddress1 varchar, @OrgAddress2 varchar, @OrgCity varchar, @OrgState varchar, @OrgZip varchar, @DestAddress1 varchar, @DestAddress2 varchar, @DestCity varchar, ...
I'd like to know if this kind of stored procedure is possible, do i need some sort of looping structure or something? I want to do this, basically in this order: get all rows from one table or view. (table1) based on columns from table 1, i want to set variables for use in insert/update table2. i want to reference another table, (table...
I wanted to convert my existing stored procedures to LINQ-to-SQL Precompiled Dynamic SQL. Does anyone know of the best way to do this? I mention precompiled as I believe I can get a performance increase - not the same as SPROC but similar, correct? Also with LINQ-to-SQL Precompiled Dynamic SQL, I get strongly typed fields, etc. Are thes...
This is the error I am getting: Syntax error near 'online' in the full-text search condition '""online"*" and "and*" and ""text"*"'. This is my stored procedure: ALTER PROCEDURE dbo.StoredProcedure1 ( @text varchar(1000)=null ) AS SET NOCOUNT ON declare @whereclause varchar(1000) SET @whereclause = @text SELECT articles...
Hi Can anyone tell me how I can control the output from an SQL stored procedure that returns more than one set of output? I am currently doing the following: DataTable allData = new DataTable(); SqlConnection connection = new SqlConnection(mySource); SqlCommand cmd = new SqlCommand(procedureName, connection); ...
I have the following SQL query and so far it works the way it should and gets the top 40 tag ids that I have stored in the tagmap table. SELECT TOP 40 tbrm_TagMap.TagID, Count(*) FROM tbrm_TagMap GROUP BY tbrm_TagMap.TagID ORDER BY COUNT(tbrm_TagMap.TagID) DESC I also want to join to the Tags table which contains the actual name of ea...
I have a weird problem here. In short to the environment we have: There is a (newly set up) Win2003 Server with a SQL Server 2005 Express database. I connect to it via a MS Access application. Since I switched to the new server (restored a backup from the former server on it) all SPROCs (only in Access) have a ;1 after their name, he...
Hi, Stored Procedures in SQL 2005 - with field type NText Im Writing a stored procedure to tidy up some data before importing it into Microsoft CRM. So far all works fine. However i need to do a case statement on a nText Field. It needs to check this field against about 3 or 4 text values and set a new field (already in the destina...
How do you calculate a date value in a stored procedure? This: CStr(DateAdd("m", -6, Date)) Is the line in the original code and I am wondering if rather than pass the value to the stored procedure if I can calculate that value in the stored procedure but not sure how to do it? ...
I use system stored procedures all the time, but always need/want to filter or sort the results of the sproc. I typically use a table variable for this, but defining the fields/datatypes for the table variable is a pain. For instance... DECLARE @SpWhoResults TABLE ( spid INT, STATUS VARCHAR(100), ...
Out of curiosity, does a stored procedures have the ability to delete a file from the OS? If not I will have to make a windows Batch file that deletes the file and then runs the stored procedure using OSQL. ...
Hello, I'm using .NET 3.5 SP1. I have a stored procedure returning a result of a transaction as shown below: Create PROCEDURE SetPrice @itemId int, @price int AS DECLARE @myERROR int -- Local @@ERROR , @myRowCount int -- Local @@ROWCOUNT SET NOCOUNT ON BEGIN TRAN1 UPDATE item_price_table SET price=@price WHERE...
I'll just put it out there that I'm new to LINQ to SQL. Hell, i'm relatively new to programming in general. Anyways, I would like to learn and use LINQ to SQL to a project that was built using .NET 2.0 Framework. The project uses stored procedures to access the database (there's no dynamic SQL queries on the front end servers). LINQ to ...
Due to a legacy report generation system, I need to use a cursor to traverse the result set from a stored procedure. The system generates report output by PRINTing data from each row in the result set. Refactoring the report system is way beyond scope for this problem. As far as I can tell, the DECLARE CURSOR syntax requires that its ...