I am losing my hair....and my mind.... Is there any reason why I wouldn't be able to cast a varchar value of say 7.8 to decimal? I have a whole bunch of lab results that come with a bunch of garbage in the result column. I have stripped it away so that it is only a format [1-x].[0-9]. I...more >>

I have the following State with many transitions to other States Tables include State StateId Name StateTransitions StateId DestStateId How could I get a list of all the possible states for a given state id? Thanks in advance, Mardo ...more >>

Hi, Can I get previous and current values of row in trigger for update operation. Regards, Shah Adarsh. ...more >>

Is there a special way to be able to reference an Aliased column name in the WHERE clause? For example, the following works fine (Northwind): SELECT CompanyName, (SELECT COUNT(OrderID) FROM Orders WHERE Customers.CustomerID = Orders.CustomerID AND ...more >>

Hi! I am just learning SQL 2005 and also programing C# (Studio 2005). Why is it a good idea to use namspacing on the tables in a database? such as if those schemas exist Purchase, Sales. Purchase.PruchaseHead Purchase.PurchaseDetail Sales.SalesHead Sales.SalesDetail Why just not ...more >>

Hi, I have a database on SQL-2000 , I take backup of it and trying to restore in SQL-7 but I am fail, Is it possible to restoring SQL-2000 to SQL-7 or not. Thanks in advance. Hardik Shah ...more >>

When importing / exporting tables between databases in SQL 2005, the fields lose any properties that have been set, i.e. primary key. Any idea if there is a solution to this. Thanks ...more >>

Does anyone know what optimizations SQL Server 2000/2005 carries out if an UPDATE statement sets a column to its existing value? For example, the MySQL documentation explicitly states that if an UPDATE statement sets a column to its existing value, MySQL will not attempt modify the column at...more >>

Hi, I am developing a web based application that deals with financial info (coldfusion and sql server 2000 on shared server). All the numbers that carry this info are of float data type. I ran into a situation when this gives all kind of problems. For example, I calculate cost after very...more >>

I have a SQL 2005 database with 4 million+ rows. One table in particular has about 35 columns. I have implemented a paged data grid results view for them. They want to be able to sort on the majority of the columns. When they sort they want all the results sorted not just the visible r...more >>

Could somebody please help me on following SQL Query. Single table data likes this: SerialNumber PassedTestType 20001 1 20001 2 20001 3 20001 4 20001 ...more >>

Sybase has implemented a useful aggregate function called list() which takes a column and returns all values in a comma delimited string. In Oracle10g, there is a Java solution to duplicate this aggregation function. Now I have to do the same in SQLServer2005. I found the UDA (User Defined Agg...more >>

I have a situation where I need to run several Queries from different NON RELATED tables and I would like to print the results on a single formatted line. Code: PRINT 'FUTA--------------' --FUTA SET @QUERY = 'SELECT sum(CompanyAmount) as CompanyAmountTotal, sum(CompanyAdjWage) as Com...more >>

I have created a database in 2005 Express and now I need to move it onto a SQLServer 2005 box (regular edition). Can it be done and how? I have web surfed and found nothing. Thanks. ...more >>

Hello, I want to retrieve a distinct list of elements from a column not including any aggregate functions. I can do this by Select col1 from tbl1 Group By col1 or Select Distinct col1 from tbl1 Is there any criteria for using Distinct vs group by or group by vs distinct when not...more >>

Can't I perform Math functions in deriving a column??? Check out the following SQL which DOES NOT work... SELECT GEN.loan_num, MKT.yield_prem, MKT.yield_fee, GEN.ysp_to_wire, GEN.loan_amt, (MKT.yield_fee = (GEN.loan_amt * (MKT.yield_prem/100))) FROM DataTrac.dbo.GEN, DataTra...more >>

HI, i have table with with a column named expire_date , i want to make a rule for block enter a date < today date , how i can make this?? only dates > today must be enter in this column. Thanks in advance alejandro carnero ...more >>

I have 6 sql servers running sql2k sp3 that process data. One part of my process consists of updating two varchar(8) feilds with the latest date. This code has been working for 3 years now. While I do not use any tran statements, yesterday the updates started rolling back one of the columns ...more >>

When configuring ms sql to permit authentication from users over a domain. Is it possible to poll from system tables like sysusers / sysmembers / sysgroups / sysprocesses / etc what active directory group's a specific user is a member of once they are authenticated into a ms sql server? I've bee...more >>

Are inserts really slow in 2005 or am I doing something stupid? Here's the table: CREATE TABLE [dbo].[Tickets]( [ticket] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [data] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [added] [datetime] NOT NULL CONSTRAINT...more >>

I created a user function fn and MSSQL did not let me call it without a schema prefix. Do I need to create a synonym to get around this behavior? Thanks. select fn() error: 'fn' is not recognized built-in function name select dbo.fn() works fine. ...more >>

Hi im having a few problems saving a file with the data and time i am using the below t-sql but can only get the date and not the time. CONVERT(NVARCHAR(16),GETDATE(),112) Any guidance would very much be appreciated. thanks in advance ...more >>

hi can any one guide me, how to send email using stored procedure in sql server 2000 in windows 2003 o/s without using mail client installed on server. Thanks Kalyan ...more >>

I hate to do this, but I need to now. The program below sends an e-mail alert for each server/drive that is below the critical threshold. Well it turns out that I need an exception list for some servers. So I created that list in a new table with the following fields Computer VARCHAR (128), ...more >>

I was interested in optimizing a query I created in SQL Server 2000 for adjusting time zones for daylight savings time (only USA DST for simplicity). In essence I have one table "tblDaylightSavingsTime" where the PK is Year, with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back ...more >>

Hello, I am trying to locate where/when data is being inserted into a table from a DB I recently inherited. So I write this in QA select * from syscomments where text like '%Insert Into CompareSubscribers%' This will retrieve the procedure(s) that contains the text "...Insert Into.....more >>

We have to import huge quantities of data into our database on a SQL server 2000 box (running under NT 2003 server). Our requirement is to create temp tables that house anything from 5 Million to 7 Millions rows This will happen once a week as run by whatever account runs a DTS package is it...more >>

I have a developer that has databound datagrids as follows: Datagrid.datasource= DataSet (yes, the compiler allows this) The dataset is returned from stored procedures. Each of the sps return a single recordset, but periodically we are seeing some nondeterministic behavior where the code for...more >>

I'm terrified, and could use some opinions from the user community... I'm about to take my first ever Microsoft MCP exam on SQL Server 2000 Development (70-229). To prepare, I've been reading the SQL Server 2000 Design Exam Study Guide from Sybex Books. One of the problems I have is with a...more >>

I tried to write a CLR stored procedure using C# in SQL 2005 to access an Access database. When I use the OleDbConnection class in System.Data, the procedure throws SecurityException at runtime. Output as following: System.Security.SecurityException: Request for the permission of typ...more >>

Hi there, i have a problem with updating rows using a cursor, my code is below: -- Fill column DataId for each row in the Data table DECLARE @index1 int, @index2 int DECLARE DataID_Cursor CURSOR FOR SELECT index1, index2 FROM data OPEN DataID_Cursor FETCH NEXT FROM DataID_Cursor INT...more >>

I was looking for a solution for this problem - but none of what I found in Internet helped - so finally I made something myself ( based a little bit on someone's idea). I hope it will be useful to someone - or maybe u'll see some bugs in it? please report. there 2 assumptions 1. before execu...more >>

Not sure if this is correct group, but I cannot access DirectoryServices namespace (directoryservices.dll) when creating (or running) a C# UDF. It seems that this is a 'feature', but why - and is there a way round it - I need to access Active Directory from the UDF. Thanks Howard. ...more >>

Is it possible for an event coming into the database (I have an access control system that uses SQL server, and it has an event type column for alarms, troubles, etc. Is it possible to set up a command to output a specific ASCII command based on incoming data? Any help would be appreciated...more >>

Hi. Does anyone have any advice to how to connect to the SQL Server2000 via the Internet? Background: 1. SQL Server2000 setup as NT Authen only 2. Internet access from the public (that is no work NT accounts) 3. I do not want to code any SQL Server password into the web application (e.g...more >>

Hi all, I have a trigger for update on a table. It does a whole bunch of things, then calls a SP. I want to then check something afet the SP is completed. To check this out I put a print statement after the line that called the SP exec calcdeductables @claimid, 1 print 'Check SIP' The SP, ...more >>

Hi, I want to audit add/drop of windows login to my sql server. Through SQL Profiler Add/Drop login I can only Audit Add/Drop login of SQL Security Login and not the windows login. Is there any way to audit Adding of windows login? Thanks Pushkar ...more >>

HI all, I covert your input. I am designing a claims database. I am looking at have a common claims header table, that will keep all common thing to every claim, and then seperate tables depending on the type/class of claim. So I could have a motor, marine, asset for example. Reason being that ...more >>

Dear Sir, I used dynamic SQL on stored procedure to do the paging in my site. I often hit a connection time out problem with that dynamic SQl when I call it from .NET code. However if it run it under query analyzer, everything works fine. Who can explain this to me and what should I do i...more >>