I have information that people put in once a month, that I would like to report on periodically. I tried doing sums in a function, but it takes a long time. Are there any recommended ways of storing it periodically, or creating a view? ...more >>

This following stored procedure works fine, SELECT .... WHERE myYear = @searchYear UNION SELECT... myYear = @searchYear UNION SELECT myYear = @searchYear What I want to do is to set up a loop function and run the above sp for each year available from a table tblYear within this functio...more >>

can have snap shot of database offline & sync it back to maindata. What I want is to take a snap shot of data on my laptop so that I can work on it at night and sync changes made with the original data (the main data will also be changed in the night). Rajesh ...more >>

declare @custID int, @s varchar(50) set @custID = 123 select * from tbl1 where coalesce(@custID, custID) --return everything for custID = 123 set @s = 'smith%' select * from tbl1 where coalesce(@custID, custID) And custName like @s --now include And custName like @s Rather than havi...more >>

Table TKT_Ticket has one row per ticket. Table TKT_History has zero-to-many rows per ticket TKT_Ticket has a field ImpactID which holds the initial Impact for this ticket. TKT_History has a field called ImpactID which can change the Impact from one update to another. Because of a program...more >>

Just moved to SQL 2005, and in query analyizer have always hit ctrl n to create a new query, and now it does nothing. Is there a way to reset this? It looks like in the keyboard portion of options, there are only methods to change Ctrl 1 - 0. Thanks for anyone's help. ...more >>

Hello everyone I have a problem and I need your help : I have an SP that create and drop a table dynamically, this is the code If Exists (Select * From sys.objects Where object_id =3D OBJECT_ID(N'[dbo].[tmp_Obj_Combos]') AND type in (N'U')) Delete dbo.tmp_Obj_Combos Else CREATE...more >>

We have experienced a very significant slow down in the execution time of the sp_fkeys routine on SQL Server 2005 compared to that on SQL Server 2000. I have found it referenced frequently in other listings, but find no reference on the MSDN site. Is this a problem recognized by Microsoft? ...more >>

Declare @s varchar(50), @custID int set @custID = 123 set @s = '%' 1) Select * from tbl1 Where (custname like @s or custName is Null) and custID = Coalesce(@custID, custID) vs 2) Select * from tbl1 Where custname like @s and Coalesce(@custID, custID) if @s Like '%' statement 1) will r...more >>

SQL Server 2000, programming in .NET C# on a web server. One of the developers is working on something where he serialized some XML data using UTF-8. The result is then stored into a text column in the database. The columns collation is SQL_Latin1_General_CP1_CI_AS. When using UTF-8, a lit...more >>

When I have had a full drive before, I have usually found a log I could shrink to get a little wiggle room and then shrunk the mdf afterward. (Yes, I know all of this is terrible from the lack of planning perspective and from the fragmentation perspective.) But here's my q: would I be able ...more >>

Hi, I have a query that returns rows with columns b, c, d. I want to add into this query and add CalcColumn. This column is sum and then avg make sure that it ignores nulls and calculate avg based on values. num b c d CalcColumn 1 2 2.1 <null> ...more >>

Hello, I have 2 SQL tables as follows: [Articles] > ArticleId (PK), Title, Body [Categories] > CategoryId (PK), ArticleId (FK), CategoryName I want to display a GridView with the articles. For each article I want to have child gridview with the comments. I will need to fill a dataset w...more >>

How do I code a trigger that will allow me to update te Last_Updated column for a given record in a table with no Primary Key. Create Table Test Column1 Varchar(10), Column2 Varchar(20), Updated_On Datetime Table Temp Column1| Column2| a 1 b 2...more >>

Hello, I have a procedure which inputs are @id, @title and @body. Basically I need to insert a new record if there is no record with the given @id or update the record if one is found with the given @id. My question is: can be this done without using IF? Just like a mixture of Insert/U...more >>

Hi, I would like to hear from experets here about how do you define a Enterprise Level database. e.g. good database design, good documentation, good database standards and guidelines, etc. I am working at a company that now asks to rebuild the web portal with the database at Enterpris...more >>

I have the below stored procedure. It takes 1.5 - 2.5 Mins to run. However if I run the queries by themselves they each execute within 13 seconds (Most take under 1 Second). In total they take less than 25 Seconds to run doing them by themselves. But when run all together like this it takes far ...more >>

hey all, DECLARE @test nvarchar SELECT @test = 'FT' PRINT @test why would it only show the 'F' i know char would do that but not varchar? thanks, rodchar...more >>

Hello, What is the default value for NOCOUNT in SQL 2005? On or Off? This is what I know: When NOCOUNT is OFF, @@ROWCOUNT returns the number of records if ExecuteNonQuery is used But in my ASP.NET code I don't need to get the number of records. Should I use it On or Off? Thanks, Mi...more >>

hey all, in my stored procedure i'm trying to say the following: SELECT @recentStartDate = MAX(HI_Start_Date) , @recentEndDate = HI_End_Date , @recentStatus = HI_Employee_Status FROM EmploymentHistory WHERE HI_EmployeeID = 14 but i get an error saying: Msg 8120, Level 16, State 1, Lin...more >>

hey all, is it possible to return a table from a function called by a stored proc? if so, what would the syntax look like utilizing the values in the table in the calling stored procedure? thanks, rodchar...more >>

I have a stored procedure that I run to move data from one table to a warehouse table. I use a local package to run the stored procedure, and schedule it to run daily in the SQL agent Jobs. I'm using SQL 2000. The stored procedure actually runs correctly, but the job reports back an error ...more >>

Hello, I have a few procedures for deleting, updating and inserting records on a table. I created an output variable as follows: @Feedback INT OUTPUT Is there a way to make @Feedback = 0 or = 1 if an error has occurred or not? And if possible, what kind of error ... Thanks, Miguel ...more >>

We have a requirement form our host information security officer to check for the presence of a particular string in any SQL table or the SQL logs on all SQL installations under our control. Can anyone give me a simple (ore reasonably simple) way to query all columns in all tables in an SQL...more >>

Hello, What is the difference between using: insert into Table(A, B, C) select @A, @B, @C And insert Table(A, B, C) values (@A, @B, @C) Which one should I use in SQL 2005? And can I use OUTPUT in both cases to output the ID of the record just created? Thanks, Miguel ...more >>

Hello, I have 3 tables: [Article] > ArticleId, ... [Category] > CategoryId, ... [CategoriesInArticles] > ArticleId, CategoryId Given an @ArticleId, I need to: 1. Delete the record in [Article] with the given @ArticleId 2. Delete all records in [CategoriesInArticles] with t...more >>

I need to monitor the disk space on my servers. My problem is that most of my servers have drives attached to mountpoints not to letters. Has anyone found away to monitor these disks? Thanks ...more >>

How do I export data from a table with a specific criteria to a CSV file TIA Sam ...more >>

Hi All, Is it possible to check if a local variable exists and create it if it does not? Any workarounds or ideas welcomed. Thanks, Mark ...more >>

Hi, The following query: SELECT DISTINCT * INTO [dbo].[DistinctedTable] FROM [Table] WHERE ColumnX IS NULL Results in the following error: "Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint." table consists of 6 columns C...more >>