I'm using SQL Server 2005, and I would like to know how to access different result sets from within transact-sql. The following stored procedure returns two result sets, how do I access them from, for example, another stored procedure? CREATE PROCEDURE getOrder (@orderId as numeric) AS BEGIN select order_address, order_number fro...
Context: I'm in charge of running a service written in .NET. Proprietary application. It uses a SQL Server database. It ran as a user member of the Administrators group in the local machine. It worked alright before I added the machine to a domain. So, I added the machine to a domain (Win 2003) and changed the user to a member of the P...
Question is pretty self explanitory. I want to do a simple find and replace, like you would in a text editor on the data in a column of my database (which is MsSQL on MS Windows server 2003) ...
I'm trying to run some queries to get rid of XSS in our database using Web Data Administrator but I keep running into this Potentially Dangerous Request crap. How do I disable validation of the query in Web Data Administrator? ...
Post your favorite book about Microsoft SQL Server (single book per comment please) or vote it up if someone has already listed it. Edit: Please include a small review or overview of subject matter and the reason for its applicability. ...
When setting up foreign keys in SQL Server, under what circumstances should you have it cascade on delete or update, and what is the reasoning behind it? This probably applies to other databases as well. I'm looking most of all for concrete examples of each scenario, preferably from someone who has used them successfully. ...
Edit: Let me completely rephrase this, because I'm not sure there's an XML way like I was originally describing. Yet another edit: This needs to be a repeatable process, and it has to be able to be set up in a way that it can be called in C# code. In database A, I have a set of tables, related by PKs and FKs. A parent table, with chi...
Is there a system stored procedure to get the version #? ...
I have a web page that has I'm hooking up a stored procedure on. In this SQL Data Source, I have a parameter that I'm passing back to the SP of type int. ASP seems to want to default to int32 but the number won't get higher than 6. Is it ok to override the ASP default and put in 16 or will there be a conflict somewhere down the road...
I realize that CHAR is recommended if all my values are fixed-width. But, so what? Why not just pick VARCHAR for all text fields just to be safe. ...
I have been hearing the podcast blog for a while, I hope I dont break this. The question is this: I have to insert an xml to a database. This will be for already defined tables and fields. So what is the best way to accomplish this? So far I am leaning toward programatic. I have been seeing varios options, one is Data Transfer Objects (...
I am getting the following error in an MS Access ADP when trying to add a record on a form linked to a MS SQL Server 2000 table: Run-time error '31004': The value of an (AutoNumber) field cannot be retrived prior to being saved. Please save the record that contains the (AutoNumber) field prior to performing this act...
I'm getting an error whenever I load Management Studio or open a folder in the server explorer, etc. Additionally, If I try to create a new database it constantly is updating and does not finish. I have attached a screenshot of the error. Please let me know what I can do to fix this because it's really aggravating. ...
I'm writing a pretty straightforward e-commerce app in asp.net, do i need to use transactions in my stored procedures? Read/Write ratio is about 9:1 ...
I have a query where I wish to retrieve the oldest X records. At present my query is something like the following: SELECT Id, Title, Comments, CreatedDate FROM MyTable WHERE CreatedDate > @OlderThanDate ORDER BY CreatedDate DESC I know that normally I would remove the 'DESC' keyword to switch the order of the records, however in this...
Is there a way to tell MS SQL that a query is not too important and that it can (and should) take it's time? Likewise is there a way to tell MS SQL that it should give higher priority to a query? ...
A DB Audit Trail captures the User Last Modified, Modified Date, and Created Date. There are several possible implementations: SQL Server Triggers Add UserModified, ModifiedDate, CreatedDate columns to the database and include logic in Stored Procedures or Insert, Update statements accordingly. It would be nice if you include implem...
I know this is a broad question, but I've inherited several poor performers and need to optimize them badly. I was wondering what are the most common steps involved to optimize. So, what steps do some of you guys take when faced with the same situation? Related Question: http://stackoverflow.com/questions/39331/what-generic-techniq...
What is the best way to shred XML data into various database columns? So far I have mainly been using the nodes and value functions like so: INSERT INTO some_table (column1, column2, column3) SELECT Rows.n.value('(@column1)[1]', 'varchar(20)'), Rows.n.value('(@column2)[1]', 'nvarchar(100)'), Rows.n.value('(@column3)[1]', 'int'), FROM @x...
We are rewriting our legacy Accounting System in VB.NET and SQL Server. We brought in a new team of .NET/ SQL Programmers to do the rewrite. Most of the system is already completed with the Dollar amounts using Floats. The legacy system language, I programmed in, did not have a Float so I probably would have used a Decimal. What is your...