stored-procedures

How can I get a stored procedure to call an outside database on the same SQL server?

I am dealing with a web application that uses 2 different databases to store information. These databases have some database keys that refer to records in the other database. I would like to be able to write a stored procedure in SQL 2005 that can modify data in the current database as well as go out and modify some data in the other...

Stored procedure does not return data if executed from VBA

I had stored procedure MySPOld in Sybase db. I created new sp MySP. This new sp returns data while executed from Sybase Sql Advantage. But not returning the data when called from VBA - Excel 2003 (EOF property of recordset is True). Here is my code.. Dim dbCon As ADODB.Connection Dim rstTemp As New ADODB.Recordset Dim query As String qu...

In Linq to SQL is it better return multiple results sets at once or call the DB using multiple stored procedures?

I am creating a very data intensive, high volume web site. Every aspect of the website is driven by interactions with the MSSQL DB that I am using. On one page there are 10-12 different resultsets that I need to utilize in my page. So I need to know the best practice when it comes to using Linq-to-SQL and multiple results sets with a ...

Advantage of using Views in mysql

I've learned that views can be used to create custom "table views" (so to say) that aggregate related data from multiple tables. My question is: what are the advantages of views? Specifically, lets say I have two tables: event | eid, typeid, name eventtype | typeid, max_team_members Now I create a view: eventdetails | event.eid, eve...

DROP SQL Tables if created date is more than a week - MS SQL Server

Hi, I want to create a Stored procedure and Job in Ms SQL server that would delete all tables in a schema that are more than a weeks old. i create backups every single day and i want to automate the process by scheduling a job to delete any backups(SQL Tables) that are older than a week. Your help would be greatly appreciated. ...

Counting records in SQL Server

Hello friends, Take a look at this SP. ALTER PROCEDURE [dbo].[sp_GetRecTitleVeh] AS BEGIN select a.StockNo, c.ClaimNo, v.VIN, v.[Year],v.Make, v.Model, c.DOAssign, t.DOLoss, t.RecTitleDate From dbo.Assignments a, dbo.Assignment_ClaimInfo c, dbo.Assignment_TitleInfo t, dbo.Assignment_VehicleInfo v Where ...

C# ASP.NET Syntax to Call Oracle Stored Procedure

How from ASP.NET I can call a stored procedure in an Oracle package that accepts a VArray. The VArray will pass a list of keys that the stored procedure will use to modify appropriate records. I know I could send the keys in separate calls or send a delimited list, but I'd rather use an array. ...

What is a good way to represent simple tabular data accessible by a SQL Server fuzzy search?

Hi all, I'm trying to define a table in a SQL Server database that will hold rules. The rule data will be keyed on a number of columns. Where rules apply to a number of scenarios I want the the key columns to contain wildcards to avoid having to maintain lots of data. I then want to find the best match row with some kind of fuzzy search...

OLE Error in SQL

I have Windows Server 2003 Standard x64 Edition SP2 with Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) I downloaded CAPICOM Platform SDK Redistributable: http://www.microsoft.com/downloads/details.aspx?FamilyId=860EE43A-A843-462F-ABB5-FF88EA5896F6&displ...

Is my stored procedure executing out of order?

Brief history: I'm writing a stored procedure to support a legacy reporting system (using SQL Server Reporting Services 2000) on a legacy web application. In keeping with the original implementation style, each report has a dedicated stored procedure in the database that performs all the querying necessary to return a "final" dataset tha...

convert in stored procedure call?

Hi all, I have the following piece of SQL that does not work: declare @id INT; set @id=0; exec insert_mail @id OUTPUT, 'ZLgeOZlqRGC6l57TyD/xYQ==', 4928, '2010\01\14\14\03131_2.eml', 'Suz, Katie and Kourtney''s Housewarming Party', CONVERT(DATETIME, '2015-01-18 14:03:13', 120); select @id; and changing it this way fixes it: declar...

Migrating a database from SQL Server 2000 to SQL Server 2005

What technical issues am I likely to encounter when migrating a database from SQL Server 2000 to SQL Server 2005? Are there any common problems I should be aware of? Should i be worried about any T-SQL changes that may break when stored procs are executed against the newer version of the database? What about DTS packages? Are there any...

Is this an effective and efficient SQL Query or is there a better way?

We have a passwords table which references a user table. Records never get deleted from the password table so if a user changes their password, a new entry with a more recent Created date gets inserted. The hash of the password is salted with various things, most importantly the created date of the actual record. In a stored procedure...

Detect this parameter difference in C#

Following are the ways by which I can declare parameters inside a StoredProcedure. @Doc_Type_Id int and @Doc_Type_ID int=null Second implementation shows that this parameter is optional. Now I am dynamically detecting parameters using SqlCommandBuilder.DeriveParameters(cmd); How can i differentiate between these two types of p...

Sql Server print affected rows during while loop

I have a stored procedure running on sql server 2008 looping over a table of ~50 million rows, deleting records one day at a time (approx. 25,000 records a day). I want to be able to watch this occur as the process runs via print statements to the messages window. I'd like to be able to see a message each time a day's worth of deletions ...

Can an SQL stored procedure simply return its inputs, concatenated?

To fit an edge case, I'd like to create a stored procedure (Access SQL!) which simply returns the concatenation of three inputs. So MyProcedure('AAA','BBB','CCC') returning 'AAA,BBB,CCC' Obviously this is elementary in most programming languages, but I didn't know if SQL was capable of this at all. ...

Nested stored procedures containing TRY CATCH ROLLBACK pattern?

I'm interested in the side effects and potential problems of the following pattern: CREATE PROCEDURE [Name] AS BEGIN BEGIN TRANSACTION BEGIN TRY [...Perform work, call nested procedures...] END TRY BEGIN CATCH ROLLBACK TRANSACTION RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, ...

sql server delete slowed drastically by indexes

I am running an archive script which deletes rows from a large (~50m record DB) based on the date they were entered. The date field is the clustered index on the table, and thus what I'm applying my conditional statement to. I am running this delete in a while loop, trying anything from 1000 to 100,000 records in a batch. Regardless of ...

How to use Full-text or Soundex in SQL Server 2008 for Silverlight application?

Why not integrated Soundex into Full-Text search in SQL server? Those functions good for SQL query and easy to use for traditional ASP.NET app, but it is very hard to use it on Silverlight app because SL app use Linq to get data and Stored Procedure is very hard to use. (Complex datatype for result set is not supported yet). How to res...

What should an INSERT stored procedure do with the Identity value?

I need to write a stored procedure to insert a row into a table. The table has an Identity column called ID. My question is should I return the value and if so, how? As far as I can see, I could return it as the return value, I could return it as an out parameter, or I could do nothing with it. What would a DB programmer do as a...