Friends I have face very silly problem. I have insert following rows and find the result is totally different why?, Is sql server 2000 has not consider time correctly. Create Table #temp ( ColA VarChar(4), ColB SmallDateTime ) Insert Into #temp Values('A', '2007-03-11 17:59:59...more >>

I have a server/client application that has multiple user accounts. Each user can see/modify his data and also might see other users data. Think of a CRM app where you can see everybody's contacts but can only modify yours. Now each user needs: user name, pwd, user info like address, etc. My ...more >>

Hi All Is there any way to make the following code error: CREATE PROC foo AS SELECT * FROM TableThatDoesNotExist If a table does not exist that is referenced in a Stored Pocedure I want the CREATE PROC to fail. Thanks...more >>

Let's say that there are 3 tables with columns as follows: A (AID, Name) B (BID, Name) C (AID, BID) Table C is simply a map table for A and B (actually it also maps other tables to B but this is not relevant here). There is an implicit one-to-many relationship between A and B. AID and B...more >>

I have an analysis package and it has a column defined in the package as a system.GUID, I'm now creating in integration package uisng .NET. How can I define that column in the analysis service packing as a GUID in my integration service package when I run my create table script? ...more >>

Is there a commonly accepted convention for naming FKs ? Perhaps a recommendation by a leading author that's been widely followed ? Or perhaps Microsoft has a recommendation ? Or perhaps a popular tool auto-generates names ? I've seen so much variation amongst developers on this topic. I'...more >>

Hi all, I am using SQL Server 2000. I am trying to build a where statement....looks like this I have two fields that represent date and time, both are smalldatetime. So.... my where statement would be Select * from table WHERE date + ' ' + time Between @date-1 + ' ' + '18:00:00' and @dat...more >>

Hi Experts, I have a table named "Visit" to track the visits of patients at clinic location Table: Visit Fields: - VisitID (int) primary key - ClinicID (int) - VisitDateTime (datetime) - DoctorID (int) I would like to make a simple query as such: for each clinic (ClinicID), give me...more >>

I am having some issues importing information into a table using bcp. I either get a error " unexpected EOF encountered in BCP data-file" or it says o rows copied. The file is in csv format, I am assuming that the issue I am having has to do with the formating of the cells in the csv file. ...more >>

I have a sproc that is a wrapper for 3 other sprocs. My last sproc appears to be inheriting table locks from the second script. I say that because sp_lock shows tbl locks on tables that my third sproc doesnt touch. How do I break those locks from the prior sproc? force a checkpoint perhaps? ...more >>

Hi, I'm running SQL Server 2000. I have a query that looks like this: SELECT orgs.name as 'Payer', SUM(CASE ar_detail.source WHEN 'CLAIM_LINE' THEN ar_detail.amount ELSE 0 END) AS 'Claimed', SUM(CASE ar_detail.source WHEN 'PREADJUST' THEN ar_detail.amount ELSE 0 END) AS 'PreAdjust', ...more >>

I have gotten this answer before but cannot find the message thread with it. Column1 char(20) I need to create a new column with just the digits (0-9) from column1. For example: '123A*4123' would return '1234123' Thanks, CJ ...more >>

What is the best way to move data between databases where I have situations where tables have foreign key constraints based on an identity column that changes as the data is moved to the new database. Enabling identity inserts is not an option. I need to insert the data into the new database a...more >>

Is it possible to set (or limit) the size of the error log file for SQL Server 2000? I have looked all over the Ent Mgr and been Googling for a while now, but in neither case finding anything. I have only seen where I can set the location (Server properties, Startup Parameters) and setting h...more >>

Hi, Is there a way to get a query to use an index where it has a string concatenation? Like this: select id, webPage from tbl_visits where (refUrl + ' -> ' + pageUrl) = '/comedians/show/4238 -> /comedians/show/4289' ) Both refUrl and pageUrl are indexed, but with this query, it's s...more >>

hey, I have a table of users, each with a User ID. my boss wants me to figure out how to tell if a user's ID is used as a foreign key relation anywhere. how do I do that? I'm pretty sure it just involves a select statement, and count the rows. if the rows is greater than zero than yes, th...more >>

I am posting this again as I don't think many understand what I am trying to accomplish. I need to run a SELECT DISTINCT query across multiple fields, but I need to add another field that is NON-DISTINCT to my record set. Here is my query: SELECT DISTINCT lastname, firstname, middleinitial,...more >>

I am posting this again as I don't think many understand what I am trying to accomplish. I need to run a SELECT DISTINCT query across multiple fields, but I need to add another field that is NON-DISTINCT to my record set. Here is my query: SELECT DISTINCT lastname, firstname, middleinitial,...more >>

Environment: SQL 2000 I am trying to run DBCC SHRINKFILE() agsinst a specific database and the task has been running for hours now. Is it safe to kill the task in the middle? are there any chances it will bring the db to suspect if it is killed in the middle. Any schematics to diagnose...more >>

Hi guys, I have an auditing table which contains several million rows and will continue to expand. It does not have a surrogate key and the PK is an int and a varchar(255) clustered. The int is sequential but the varchar is not - for each int there will be between 1 and ~50 varchars. There ar...more >>

I have some 40 stored procedures that contain a certain string. I have the list. Is there any way to programmically open them up for edit using some command, or do i have to manually locate and open them them. thanks. ...more >>

Hi guys, I have an auditing table which contains several million rows and will continue to expand. It does not have a surrogate key and the PK is an int and a varchar(255) clustered. The int is sequential but the varchar is not - for each int there will be between 1 and ~50 varchars. There ar...more >>

How to make telephone calls (landline or mobile telephone) from any country to any other country. The first part of the telephone number to dial is the exit code - the digits you must dial to get a telephone line out of the country you are calling from. The following is a list of exit codes f...more >>

Hi all. We do have a problem loading SSIS's when running them under a scedule task. Running it direktly works fine. Any ideas out there? In converting DTS to SQL 2005, I can run the new SSIS without problems. But when I schedule it, ir gets an error message. "The package could n...more >>

I was wondering if anyone could shed any light on this. I am using SQL Server 2000. I start with 1 view (VIEW A) that uses a UNION statement that includes 2 queries. Each query is not too complicated (i.e. few joins but no subquery). I then run a report and it take a long time to run. ...more >>

If there anyway we can tell if there is any data lying on .NDF files in the database or not? This file is currently part of a filegroup and we would like to know what is lying in that .NDF file. Any tools or any command that can provide this kind of information? Thanks in advance Nitin...more >>

Hi, This is a basic question and could be a stupid Q. Q: I'm new to my project and most of the old folks/ seniors have left including the architect. There are no documents available. And the question is, How to check the level of normalization implemented in the DB? Are there any tools/ re...more >>

Dear Madams and Sirs, I must delete duplication within a table, Last_Name || City || Country ROW 1 COUGLOFA || MONACO || FRANCE ROW 2 COUGLOFB || MONACO || EUROPE ROW 3 COUGLOFC || MONACO || EUROPE I have to delete ROW 3 because is a duplication of ROW 2 based on...more >>

hello, i have an application i have to modify, currently, the click event executes a stored procedure. i've just created another stored procedure i have to execute. i'm not sure if i should embed one stored procedure within the other OR just call them separately from the click event. BUT, ...more >>

Hi, Been a while and forgot how to do this. Imagine table "creditcards" and "consumers" Now I wish to set the field creditcards.blocked to 'Yes', only if consumers has less than 10 dollars credit: UPDATE `creditcards` SET creditcards.blocked = 'Yes' WHERE creditcard.id IN ( SEL...more >>

Hi Is there any way throught which i can display the column name as a variable value Declare @var1 as varchar(10) set @var1 ='col1' select field1 as @var1 from table1 but this flashes an error. is there any way that i can do this Thanks for replying me. ...more >>

Hi, I'm trying to use the OVERLAPS statement to compare 2 date periods. At the moment, I'm just trying to see if it works like I think it does, but I can't even get this working! Here's what I'm doing.. SELECT (DATE '2006-06-29', DATE '2006-07-31') OVERLAPS (DATE '2006-07-10', DATE '2...more >>

Which differences are between these two version of SQL Server? Version 1 = 9.0.1399 RTM Version 2 = 9.0.2047 SP1 Thanks!...more >>

but does SELECT TOP 1 or MAX give me back the value/number in the field? I am using a batch and query from the command line with OSQL... so what I will get back is just a number? ...more >>

Hello folks! I posted an earlier question on log files and one of the things I'm interested in doing is installing and running my sql server from a flash drive and also saving my data and logs to flash drives. Is anybody doing this? What are the repercussions? Is it slower than a disk d...more >>

Hi I'm current investigation a chance to increase performance of backups to a logical drive over the network on the same LAN. As far as I can understand from BOL named pipes are relatively fast for one way communication on the same LAN. This was what caught my attention of using this t...more >>

Hello folks! I'd like to know more about sql server logs. How do I view what is in the log? Is there an extended sp that allows me to truncate the log? What is logged? Only inserts, updates, and deletes? Does it also record each sp and sql statement? I'm thinking of storing both my...more >>

Hi. I need advice. I'm going to transfer database from sql2000 to sql2005 by detach/attach. When do index defragmentation before transfer or after. I need any reference many thanks...more >>

How can I get the VALUE of the last Item in a coumn? something like SELECT COLUMN_xx FROM DATABASE_xx ORDER BY COLUMN DESC? and with Fields(0).value? I need just the value of that field. thank you in advance ...more >>

Hi all, I have a stored proc that outputs the recID with "SET @param1 = SCOPE_IDENTITY()". Most of this time it works fine. However, when there is a large amount of data (5000 chars) insert'ed into the single nvarchar(max) field the SCOPE_IDENTITY() returned is similar to: "5.6457616...more >>

Hi All, I've got an update trigger on one table that does an insert on another based on the value updated in a specific field. The update is controlled from a client app where one can only update records one at a time and only one specific field is updated. My problem is that it inserts two ...more >>

I have a sproc that returns a bunch of records that meet some DateTime criteria. I would like to be able to run this sproc two ways: when the input date params are supplied, use them. If they're not, calculate the dates and then use them. It looks like this: ALTER PROCEDURE DateSproc @...more >>