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 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 (...
I have the following data How do I transform it (with SQL Server 2005) into the following format? I have a example solution that I came up with but it seems a little clunky. It smells perhaps? DECLARE @ProductLanguage TABLE ( [PRODUCT_ID] int , [LANGUAGE] varchar(50) ) INSERT INTO @ProductLanguage ([PRODUCT_ID],[LANGUAGE...
I'm using SQL Server 2008 Management studio viewing a 2005 server and have just added 2 users. For some reason they both have slightly different icons and I'm not sure why. Anyone have a definitive list of the icons and their meaning or a link to microsoft's doc on it as I can't find anything anywhere. 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...
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...
I would appreciate opinions or suggestions. For several years, I have had an html and javascript website that was housed on a shared Apache server hosted by Wild West. Recently I decided to migrate it to .NET 3.5 and put it on a shared Windows IIS7 server also hosted by Wild West. I rewrote all of the pages using Visual Studio 2008,...
Can someone explain the use of DBCC DROPCLEANBUFFERS together with the CHECKPOINT operator and provide an example? I understand it is useful to test performances running before your queries but I don't fully grasp it. Also I have no clue about the use in combination with the CHECKPOINT operator. If I execute it before my queries they t...
We're on Team Foundation Server 2008 and I'm trying to find a way to report on the change in completed work from week to week at the task level. The MDX query below works pretty well, but I'd like to get rid of need to hard code last week's date. I've tried using prevmember and parallelperiod without success, but I'm no MDX expert. WIT...
what are the best practices for using the 'sqlserver 2005 database project' in visual studio 2008? I have created a project file. How to create the scripts for stored procedures, views and Tables? How would I generate scripts to populate some of the look up data? What would I do about modifications that may need to be run on the pro...
Folks, I have a webservice that returns data in ISO-8859-1 encoding - since it's not mine, I can't change that :-( For auditing purposes, I'd like to store the resulting XML from these calls into a SQL Server 2005 table, in which I have a field of type "XML NULL". From my C# code, I try to store this XML content into the XML field usi...
I have a table where I'm storing Lat/Long coordinates, and I want to make a query where I want to get all the records that are within a distance of a certain point. This table has about 10 million records, and there's an index over the Lat/Long fields This does not need to be precise. Among other things, I'm considering that 1 degree ...
SQL Server 2005 I have 10 million rows in DB, and run a select (with lots of "where" and joints.. pretty complex). The results are presented in grid (think goolge results) and because of that, the user cannot possibly use more then 1000 results. So I limit my SQL with a TOP 1000. Problem: User still wants to know that there are 5432 ...
I'll be starting a new project (asp.net) and I'll be using sql server express. Is there a reason NOT to use sql server 2008? P.S: I was considering Postgres 8.3 until I did some benchmarking and found out that sql server 2005 express is much faster for my needs (better integration I guess). ...
I'm trying to connect to a remote SQL Server 2005 db from a .NET Windows service running in Vista Home Premium x64. I can access the remote db from a console app with no problem. I can connect to a local db from the Windows service with no problem. I was able to connect from a service from XP with no problem. There's no firewall or anti-...
select (Case Remark01 When 'l1' then type1 when 'l2' then type2 end) AS [?]--Remark .....want to switch name in here from mytable Example .... select (Case level When 'l1' then type1 ('l1' mean check constant string) when 'l2' then type2(('l2' mean check constant string)) end) AS (Case when 'l1' then [type01] Else [type02]) from mytab...
When restoring a full DB backup to a different SQL Server 2005 Express server, I receive the following message: The file or filegroup "mydatabase_log" cannot be selected for this operation. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3219) I've checked the restore options to make sure that file...
Is there a way to restore an Sql Server 2005 DB to Sql Server 2000? I'm unable to attach it either. Any tools, ideas etc? ...
I'm looking for ways to display a single row of data as a single column (with multiple rows). For example, FieldA FieldB ------- --------- 1 Some Text [row] Header Value [col] ------ ------ FieldA 1 [row1] FieldB SomeText [row2] Is there a way to do this with SQL Server 2005? ...
I am using .NET 2.0 and SQL Server 2005. For historical reasons, the app code is using SQLTransaction but some of the stored procedures are also using T-SQL begin/commit/rollback tran statements. The idea is that the DBTransaction can span many stored procedures, which each individual sproc controls what's happening in its scope - in eff...