I am considering adding validation on some insert and update statements for MS SQL Server 2005. I want to be able to check the values inserted before it is written into the table - in this particular case, it is an integer field where the data must conform to a rule. So a simple schema could be: ([id] [int] identity(1,1), [name] [nvarch...
I'm using SQL Server 2005. I have a field that must either contain a unique value or a NULL value. I think I should be enforcing this with either a CHECK CONSTRAINT or a TRIGGER for INSERT, UPDATE. Is there an advantage to using a constraint here over a trigger (or vice-versa)? What might such a constraint/trigger look like? Or is the...
I have a problem with removing an attribute from a node. Example: DECLARE @processID int SET @processID = 8 DECLARE @xml XML SET @xml = ' ' SET @xml.modify('delete (/Process/Shapes/Shape/@subProcessID[/Process/Shapes/Shape/@subProcessID = sql:variable("@processID")])') SELECT @xml Gives the result: ...
I restored my development database from production, and the stored procedures I need in my development environment doesn't exist in my production database. Is there a command Ii can use to import the developmetn stored procedures back into SQL Server. There are about 88 files, as each procedure is in a different text file. TIA! Chris ...
Is it possible to add a "metadata"-like description or comments to a table in Microsoft SQL 2000 and above? How would you do this through the CREATE TABLE statement? Is it possible to add a description or comment to fields? How do you query this info back in MSSQL 2000? 2005? ...
We have a bunch of VB6 applications that access two different database servers (both 32-bit windows 2003, one SQL Server 2000, one SQL Server 2005). About every ten minutes or so, we are getting a few errors: [Microsoft][ODBC SQL Server Driver]Timeout expired [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or acce...
Your basic SP with a default parameter: ALTER PROCEDURE [usp_debug_fails] @DATA_DT_ID AS int = 20081130 WITH RECOMPILE AS BEGIN /* Usage: EXEC [usp_debug_fails] WITH RECOMPILE */ -- Stuff here that depends on DATA_DT_ID END The same SP with a local that is hardcoded. ALTER PROCEDURE usp_debug_works] WI...
I work at a company that has some very non-standardized SQL conventions (They were written by Delphi Developers years ago). Where is the best place that I can find SQL industry standard convention definitions that are most commonly used? ...
I have an assortment of database objects (tables, functions, views, stored procedures) each scripted into its own file (constraints are in the same file as the table they alter) that I'd like to be able execute in an arbitrary order. Is this possible in SQL Server 2005? Some objects as an example: Table A (references Table B) Table B (...
This code involves a recursive Stored Procedure call and a "not so great" method of avoiding cursor name collision. In the end I don't care if it uses cursors or not. Just looking for the most elegant approach. I'm mainly going to use it as a simple method to track down Stored Proc hierarchies (without buying a product). I tried cursors ...
Maybe I'm not looking in the right places but I'd like to use SQL Server Compact 3.5 as a back-end for an existing Perl app that uses DBI (on Windows). I'm looking specifically at SQL Server Compact 3.5 because I'd like to use the existing support for it in MS Sync Framework to get started instead of the SQLite db I'm currently using. ...
For example, will SQL Server warn you or does it just die? ...
My create view code like this create VIEW [dbo].[vw_test] AS SELECT 'B' AS rtype, ID FROM TB_A UNION ALL SELECT 'V' AS rtype, ID FROM TB_B GO How to create a PK with. I need to do full-text search. Tks ...
Details are as follows: Environment: SSRS 2008 Server, SQL Server 2008 for database, Report Developed in SQL Server Business Intelligence Devlopment Studio - Reporting Project Error Messages: "An error occurred during client rendering." "An error has occurred during report processing." "Query execution failed for dataset 'DataSource...
Who is worth reading? Who is the Scott Gu of the SQL world? thanks ...
Hello, I have a problems with 3rd dimension in MDX Query (on MS SQL Server 2005). I can use 3rd dimension in Visual Basic (I have a cube there, using browser I can make 3 dim. queries -- owing to ON PAGES). I snooped it via MS SQL Profiler (it records databases queries). But when I tried to put the query into MS SQL SERVER, only thing wh...
I have created an asp.net website that connects to a SQL server database. Currently on my development machine I have the connection set to a SQL server express database with the file residing in the App_Data file of the website. I now need to upload the site to my host of which I have an SQL server database set up. They have provided me...
Can I get some constructive feedback about the following architecture? Simplified Architecture Summary: Return XML from your SQL Server (using FOR XML) and pass it straight into a XSL transform to produce a rich HTML web site. What are the pro’s and con’s of such a system when compared with a conventional 3-tier ASP.NET architecture?...
This is my setup: SQL server 2005 UserA already setup as user on the server but not on databaseA. GRANT Execute ON [GetOrders] TO [UserA] AS [dbo] As you can see, i have missed out a step. I haven't added UserA to the database yet. This doesn't fail and lets me grant execute on the object. I believe in 2000 it would have thrown an...
Are there any ways to secure stored procedure consistency at the software level, so as to be sure that they're going to do what they're expected to do? I'm reluctant to use sprocs because reading and modifying them is so easy as long as you have administrative access to the computer and a copy of Management Studio Express. This is a com...