Firstly thank you all for a wonderful site every day I find answers to problems by searching these pages it really is fantastic. My Problem which I have not been able to find an answer for. I have two tables as setup below. Table 1 ++++++++++++++++++++++++ ID unique...more >>

To all, I thank you in advance for your help. I am quite new to triggers. 1. I want to see if my grammar is correct. 2. I want to ONLY take action based on a column "TarType" column in the Cmd table is the item modified. I saw a BOL article about "COLUMNS_UPDATED". Is this what I need ...more >>

I need to run a single query to delete a large number of rows (hundreds of thousands of rows). Will this grow the transaction log by a large amount? If so, how do I go about temporarily suspending transaction logging, either in the query or by using Enterprise Manager? Using SQL Server 200...more >>

Hi All I have a table of sales and a table of prices and I am tyring to write a query so that the prices associated with the sales are reported at the correct price based on the effective date. I am struggling with this query and any assistance anyone can provide would be greatly appreciat...more >>

Now I need to lock the selected records for some time and others can not update these records meanwhile. How can I do? any help will be very appreciated!! -- marxi...more >>

Hi, I am trying to use Reflection for dynamic assembly loading and method invocation inside a SQLCLR procedure but it fails giving the following error, LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host. Is there a workaround for this or is it a known lim...more >>

Hi all, I guess you can copy all in query analyser. /* I'm looking for the measured time for each operator with each customerid over a daterange that is past in. I would like the results to be grouped by operator and ..... I will try my best to explain: Measured time withi...more >>

Hi All. In the past all my tables have allowed null values on almost everything. For the first time, I'm working on a DB that does not allow null values. I'm so used to the ISNULL and COALESCE functions when performing simple concatenations, I don't know how to live without them. Example...more >>

Suppose I have a field called 'Street' with the data below in three records of a table: Peninsula Rd. Peninsula Way Peninsula Blvd. If I want to select all records that have a street that constains the string 'Peninsula' what is the correct SQL to do this filter. Something like: SELEC...more >>

Hi all, I have an audit table which among other events, logs when a user logs in or out of the application. The table has these fields: (script below) OpCode 0 for login operations (tinyint) OpType 0 for logins, 1 for logouts (tinyint) User id (int) OpDate (smalldate...more >>

Hi, Do you guys know how to pull the date for the first Sunday in February of any year? I got it to work with a hardcoded February date, but I want it to work for any year. SELECT DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, 6 - DATEPART(day, '2007-02-01'), '2007-02-01')), -1) Thanks. ...more >>

Hi SELECT [column],[column],[column],[column],[column],DISTINCT([column]) FROM table The above query fails, I'm guessing because the DISTINCT needs to be at the start. I do a INSERT INTO <table> SELECT... with the data later on so I'd rather not reorder the SELECT unless its 100% necessar...more >>

SQL Server 2005 Hi, I found the alternate snapshot folder path in table <PubDB>.dbo.sysmergepublications but I can't seem to find the view/ table that will return the default snapshot folder path - does anybody know where the default snapshot folder path is stored? ...more >>

I am trying to reference a table with an alias that contains a special character and SQL Server does NOT like it. Is there any way that I can provide a special character in my Table alias and use it??? Any help is greatly appreciated. Thanks LEFT OUTER JOIN DMD_Data.dbo.FEES AA[FEES...more >>

I need a solution that will enable me to fetch specific page of SQL Server data using different criteria much like a search engine. From what I've read paging via ADO is very inefficient especially in querying against very large tables (> 100,000 rows). An alternate solution is to use te...more >>

Say I do full backup at 12:00 AM everyday. Every 4 hours I do differential backup and every 30 minutes, I do log backup. Howerver I do not do log backup when I am doing differential backup FULL BACKUP AT 00:00:00 LOG BACKUP AT 00:30:00 LOG BACKUP AT 01:00:00 LOG BACKUP AT 01:30:00 LOG B...more >>

We have a database (OLTP) which is accessed through the a web interface. Part of that interface allows users to retrieve data into a pre-set form, the data is retrieved using about 8 views. Everything was working well. This weekend we reset replication between the OLTP and a Report Db. Tuesda...more >>

I need recommendation on following tools for Performance Monitoring. These tools are from Idera. If you know any other competitior tool that perform better, please advice. SQL Diagnostic Manager SQL Defrag Manager -Nitin ...more >>

Hi... We have an app where we want to keep an audit history of changes to a row. So we have the main table and a history table. When someone does an update to the main table, we also insert a new row on the history table saying who and when. The problem we're seeing is that there's a r...more >>

hey all, i have the following sql statment: SELECT EmployeeID, MAX(StartDate) AS Recent FROM HistoryTable GROUP BY EmployeeID Now this is the resultset i want to work with but now i want to filter all the EndDates that aren't null. do i need to do this in a stored procedure with one ...more >>

So, I just wanted to verify that I will not screw up any database elements if I am running a "sp_helptrigger Table1" statement through QueryAnalyzer. Does this simply list out Trigger info on that particular table? Or are there other adverse affects that might happen? Thanks, j...more >>

Someone very kindly answered my memory post with good information but it really didn't answer my question. One thing I've never quite understood is how you should configure the max server setting (for 2000 SP4). Here's what I mean: I've got a server that has 6G of physical RAM (SS 2000 ...more >>

I need to import a flat file as follows: Column1,Column2,Column3 1,2,3 4,5,6 (snip) 123,456,789 Downloaded from www.abcdef.com on 02/28/2007. Total1: 12344556 Total2: 234523 Total3: 56456 Copyright 2004-2007 by ABCDEF Group LLC. All Rights Reserved. The last line is some kind of signat...more >>

hey all, i was just wondering when you have a group by and a where clause which happens first? does it group first the perform the where clause or vice versa? thanks, rodchar...more >>

If you have a table that is built based on 5 sub tables OR If you do a view of the 5 sub table using UNION ALL: Which would be faster to report on? The built table has the same keys as the 5 sub tables. Pro of the view is online data, but the built table would be updated once per hour which...more >>

Hi all, I have the following code: DECLARE CrdIDCursor CURSOR FOR SELECT CardholderID FROM ReportCardholder WHERE (ReportCardholder.ReportItemID = 2658) OPEN CrdIDCursor FETCH NEXT FROM CrdIDCursor WHILE @@FETCH_STATUS = 0 BEGIN SELECT TOP 1 TrnHis.TrnHisID FROM ReportCardho...more >>

Pretty easy request, but I'm not sure if it's possible. When I Export data from one database to another, I prefer to select: 'Delete rows in destination table' instead of 'Append rows to destination table' in the 'Transform' options. Obviously this is a pain if I'm transferring 100 tab...more >>

One thing I've never quite understood is how you should configure the max server setting (for 2000 SP4). Here's what I mean. I've got a server that's got 6G (sitting on top of Win Adv'd Server 2000 SP4). And this is a dedicated box by the way: only Sql Server is on it. I know that I s...more >>

hey all, i noticed if i don't qualify my function with dbo my stored procedure won't work. what is the reason for this? thanks, rodchar...more >>

I have a sql server dts program calling an execute sql task. With in that sql task I call xp_cmdshell to execute a report services script using rs.exe. With in that script I am accessing a system environmental variable that I have created. When I run rs.exe from command prompt it gets envir...more >>

I wanted to displays ALL the permissions users are having on different objects in the database (SQL Server 2000, 2005). Like tables, SPs, Views, UDF's etc., Can anyone help me with a query for this? Regards Pradeep...more >>

I am getting this error on the following script; how do i fix it? Incorrect syntax near 'sp_Products'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'END'. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO USE WSANALYZER GO CREATE PROCEDURE sp_ProdTypes AS BEGIN IF ...more >>

Does anyone know of a variable or way to display the number of rows that are unmatched in a join operation? I am doing a full outer join and I want to know how many rows are unmatched....more >>

I have 2 identical tables from 2 separate databases that I would like to pull into a view. I would like to have the combined data from the 2 tables to show as 1 list rather that side by side. ie table1 and table2 has Firstname, Lastname as the 2 columns. Table1 contains: Joe, Smith Table2...more >>

I have a large number of users connecting to SQL Server database from an application that uses a System DSN for connection. How do I setup SQL Server so the users are able to connect to the SQL Server using 'Windows Authenication' ?...more >>

I have two SQL 2000 Servers, one is live and one is now spare. I am wanting to use the spare Server to monitor activity on the live Server. I can do this using Profiler but I am wanting to create an sp and run the procedure using a SQL Job. The only problem I am having is specfying the S...more >>

This may be a stupid question but.. If I have a stored procedure that returns a dataset, could I call that stored procedure in a view?...more >>

We have a dimension table in star schema that has only 7 rows. The possibility that it will grow more than a row or two is extremely unlikely. We have a primary key on the generated key number, and a clustered index on what would be called the natural key. Is there any point to having an ...more >>

Hi all. I have 2 identical queries (simple ones). Then only difference is that I replaced the hardcoded F_KundeID with a declared integer. Also: One query with hardcoded value. One query using a variable @F_KundeID (integer). The executionplan are totally differen and much more inefficie...more >>

What is the correct syntax for a sql statement that does multiple joins but also filters on a table that is joining. Here is a sample query, it does not like how I have 2 tables in the from clause ex. select distinct VehSym.VehicleVinId from dbo.VehicleSymbols as VehSym, dbo.Competitor a...more >>

In order to strip out the time, I used the DATEDIFF and DATEADD function. The two statement below yield the same result. Why than, people recommend the longer version @Date2? Is there any danger to use @Date1 statement? declare @Date1 datetime, @Date1 datetime SET @Date1 = DATEDIFF(D...more >>

Hi I have the following structure CaseStudy ---> CaseStudyClassification <-- ClassificationItem I have written the following query but I know that their must be a more elegant solution but I cant really figure it out. Can anyone help me? Thanks Rippo ------CODE CUT set nocount on decl...more >>

Hi. I have a inhous application with some 50 users, with around 5 profile values each (access level, team, login etc.). Further the application have a set of list values which should only be changed by adminstrators. All the data/values are stored in tables on a MS SQL database/server. The ...more >>

Hello I want to extract the date and time in separate columns from a datetime filed. the datetime is collected from the eventlog on a 2003 server. I have this: TimeGenerated 2007-02-15 08:06:55.000 using this to extract the values gives wierd answer. Select TimeGenerated, CONVERT(VAR...more >>

Hi guys, I have three tables. currentNewsStory ( PK is StoryID and versionNumber and only holds one record per storyID) HistoricNewsStory(PK is StoryID and versionNumber can have many per StoryID versionNumber combination) delayInfo has storyID and versionNumber. How would I best search both...more >>

SQL Server 2000, I found some code written by a colleague which looked interesting. Here's an example showing this technique : == create table dbo.tt (id int primary key, name varchar(50)) GO insert tt values (1, 'one') insert tt values (2, 'two') GO CREATE FUNCTION [dbo].[f_Test] () R...more >>

Hi all, I have a result set like ALBANY BAKERIES GAUTENG ALBANY BAKERIES GRAINS TIGER BRANDS How can I combine these rows intoa single column row as in ALBANY BAKERIES GAUTENG->ALBANY BAKERIES->GRAINS->TIGER BRANDS Thanks RObert ...more >>

I have two tables: Tb1 SerialNum 123 234 544 611 732 Tb2 RegisterNum 511 123 732 521 I want do as follows: 1.Create a new colomn,named "UsedNum". 2.Check SerialNum.Tb1, if it contains numbers in RegisterNum.Tb2,put a "Y" in "UsedNum"; if it doesn't contain any numbers in Regi...more >>

I have an application that is a log for purchase orders. Users enter customer info & the purchase order info into this application & data is collected in a table, sales_order_log, on one of the two databases I use (they run on alternating days to load sales info). On every other day, I run...more >>