Inverse of COALESCE
Hi all, is there a function in SQL Server 2005 that returns NULL [or a boolean value] if any of the arguments (of any type) is NULL, which would save me from writing IF a IS NULL OR b IS NULL OR c IS NULL .... ...
sql-server-2005
SQL Server Agent 2005 job runs but no output
Essentially I have a job which runs in BIDS and as as a stand lone package and while it runs under the SQL Server Agent it doesn't complete properly (no error messages though). The job steps are: 1) Delete all rows from table; 2) Use For each loop to fill up table from Excel spreasheets; 3) Clean up table. I've tried this MS page (s...
Query takes time on comparing non numeric data of two tables, how to optimize it?
I have two DBs. The 1st db has CallsRecords table and 2nd db has Contacts table, both are on SQL Server 2005. Below is the sample of two tables. Contact table has 1,50,000 records CallsRecords has 75,000 records Indexes on CallsRecords: CallFrom CallTo PickUP Indexes on Contacts: PhoneNumber I am using this query to find matches ...
How to retrieve distinct values from multiple columns
Hello Experts, I have a flat text file data which I import into a SQL Server table. It creates and table with specified name along with multiple columns as per data file. Now I need a query which will return the data and its count. e.g. data file : BREAD,MILK BREAD,DIAPER,BEER,EGGS MILK,DIAPER,BEER,COKE BREAD,MILK,DIAPER,BEER BREAD,...
How to get resultset with stored procedure calls over two linked servers?
I have problems filling a temporary table with the resultset from a procedure call on a linked server, in which again a procedure on another server is called. I have a Stored Procedure sproc1 with the following code, which calls another procedure sproc2 on a linked server. SET @sqlCommand = 'INSERT INTO #tblTemp ( ModuleID, ParamID) ' ...
SSAS Reporting Services - Set specific language / translation
Hi all, in the data warehouse there's a default language for the measures, and I added a translation for German captions. In a Visual Studio Report Server project, when creating a query with my German OS, the cube and its measures are displayed in German language. When dragging measures to the mdx query windows, the default measure name...
SQL Server related question
Hi guys, I have this thing that i need to do and some advices will be greatly appreciated. I have a SQL server table with some phone calls.For each phone call i have the start and end time. What i need to accomplish: a stored procedure which for a certain period of time, let's say 5 hours at a x interval, lets say 2 minutes returns the...
red herring error "The user does not have permission to perform this action"
When running a stored procedure, we're getting the error 297 "The user does not have permission to perform this action" This occurs during times of heavy load (regularly, when a trim job is running concurrently). The error clears up when the service accessing SQL Server is restarted (and very likely the trim job is finished as ...
How to use a LIKE statement inside an IF statement?
I need to be able to check if a variable has a certain string of text inside it. Here's what I have now: --This sample always goes to the ELSE block. IF( @name LIKE '%John%' ) BEGIN --do one thing END ELSE BEGIN --do the other thing END ...
"select * into table" Will it work for inserting data into existing table
I am trying to insert data from one of my existing table into another existing table. Is it possible to insert data into any existing table using select * into query. I think it can be done using union but in that case i need to record all data of my existing table into temporary table, then drop that table and finally than apply union...
Exporting query results to a file on the fly
Hi all, I need to export the results of a query to a csv file and put the file on a network shared folder. Is it possible to achieve this within a stored procedure? If yes, comes yet another constraint: can I achieve this without sysadmin privileges, aka without using xp_cmdshell + BCP utility? If no to 2., does the caller have to h...
SSAS dimension source table changed - how to propagate changes to analysis server?
Hi, Sorry if the question isn't phrased very well but I'm new to SSAS and don't know the correct terms. I have changed the name of a table and its columns. I am using said table as a dimension for my cube, so now the cube won't process. Presumably I need to make updates in the analysis server to reflect changes to the source database? ...
Restore Database from SQL Server 2008 to SQL Server 2005
I have created a set of tables (around 20) in SQL Server 2008 and entered around 1000 records to appropriate tables. But the issue is that I want that same tables with all the entered data into SQL Server 2005 (SQLEXPRESS). Obviously it won't work by taking a backup and restore it into SQL Server 2005 as it won't support backward compa...
Rebuild all XML indexes in SQL Server 2005 using T-SQL
How can I use T-SQL to force a rebuild / reindex of all XML indexes in a SQL Server 2005 database? I only have Management Studio Express locally so I don't have the luxury of any UI options such as the "Rebuild Indexes" dialog that I know exists in SQL Server 2008. ...
How can I force a subscriber to be synchronized from a local snapshot?
Hello, I have a SQL 2005 server replicating(merge\push) to SQL 2005 and SQL 2000 servers. I have multiple subscribers spread througout the United states. I have set , @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = N'c:\snapshots\Merge\' (sample location). I take the snapshot from the publisher that is in the same locat...
Create Schema in SSMS
I am installing an application that requires me to set up a SQL Server DB with a schema. According to the SSMS 2008 documentation, after creating the DB I can expand the DB in the tree then right click on Security and I should have an option New Schema but I only have New User, Database Role.. and Application Role.. I tried just doing ...
Finding gaps (missing records) in database records using SQL
I have a table with records for every consecutive hour. Each hour has some value. I want a T-SQL query to retrieve the missing records (missing hours, the gaps). So for the DDL below, I should get a record for missing hour 04/01/2010 02:00 AM (assuming date range is between the first and last record). Using SQL Server 2005. Prefer a set ...
Can you insert the results of a dynamic sql call into a SQL Server 2005 table variable
Is there anyway to do this in SQL Server 2005? declare @tv_tablelist table (recnum int identity(1,1) primary key, newvar varchar(500)) declare @mysql nvarchar(4000) set @mysql = 'insert into @tv_tablelist(newvar) values (''test test test'')' Exec sp_executesql @mysql, N'@tv_tablelist table (recnum int identi...
SQL Server tuning tools for finding overload
I use SQL Server as a DBMS for my very big corporate DB (with different financial data). And some times my system go down. I don't understand why. What programs/tools I can use for finding process/program/thread, that overload my SQL Server? Thanks for all answers! ...
Is there a way to rewrite the SQL query efficiently
hi, I have two tables with following definition TableA TableB ID1 ID2 ID3 Value1 Value ID1 Value1 C1 P1 S1 S1 C1 P1 S2 S2 C1 P1 S3 S3 C1 ...