Hi, I'm in the process of writing an application to open a database as read only to view historical log data on a DVD. The databases were created with SQL Server 2000. I am able to open them with SQL Server 2005 Express Edition, but have to copy them onto my harddrive first. I've had a ...more >>

I posted a question earlier today (9:25 AM). Subject: Storing DateTime Values - Sometimes with time, Sometimes without. If you review that thread you'll see how quickly it got WAY off topic. So I hope it's not considered rude for me to repost the same question again within 24 hours. I reall...more >>

Three quick questions on SQL syntax if you don't mind. I appreciate your response. In my code below, 1) Is it necessary to put things in brackets? Since we use Hungarian notation, we'll never have a naming conflict with a SQL reserved keyword. 2) Is it necessary to preface with "dbo"? ...more >>

Hi, I have a table which includes a ([login_sname] sysname) column (used for row-level security, SUSER_SNAME() joins). However, I am having trouble figuring out how to add a FOREIGN KEY (login_sname) constraint, which REFERENCES valid server logins for a given database. Using [sys].[database_...more >>

Hello using INFORMATIONSCHEMA.COLUMNS I get IS_NULLABLE as varchar ("NO"/"YES") 1. Is this always english (constants NO and YES) or does it depend on settings ? 2. Is there a way to get a boolean from this column (language save cast)? 3. Is there an alternative source (except sp_columns)? ...more >>

Can I use the CONVERT function to change the way a date/time is displayed in a query without actually commiting the change to the database? Please excuse my ignorance. Further, how do I convert the date/time into mmm-yy format? Thanks SHC ...more >>

Hello All Developers and SQL Gurus. I am almost complete with my query thanks to Jeff Tuner a fellow Google Groups user. This is what my final table structure looks like and along with my queries. The query below works fine only does 1/2 of what I want it to do. Basically right now it will give ...more >>

Hi guys I am trying to create a query which counts records for all tables in a database. But it seems that I can't use query in From clause as I recieve errors. Any idea how I can have such a query with variable value in From section? Select count(*) from ??? Is there something like Dual t...more >>

I have a small list of names. I want to create a table with two columns using this list. The first would be the names as per the list. The second would replace all the males names with 'Male', and the female names with 'Female'. I'm able to run a statement with a single REPLACE command, SEL...more >>

Hi We have a view that appears to be duplicating a record. We have two tables identical struction apart from one additional field (which is replaced with a null in the other table within the view). We have a view that joins the two tables showing all fields, it is a basic union replacin...more >>

I want to know that how to take a BACKUP of a Table in Sql Server.If any one can help me with this issue. Thanks ...more >>

I've got a CLR function that runs fine on my workstation running SQL Server Express; however, it throws OOM exceptions on the full SQL Server 2005. The function is very simple: it takes an XML file and a stylesheet, transforms the XML using the stylesheet, and returns the result as a string. ...more >>

Thanks to Tracy McKibben and Geoff Hiten for pointing me in the right direction in a previous post. However, I found another solution. In order to backup an SQL Server database to another machine, you must specify a UNC path to a share. The backup is performed with the credentials of the SQ...more >>

Hi, I'm learning to use sql server ('00) and am trying to convert this query from access that will count the # of increases, declines, and no changes for a field. SELECT dDate, Grp, Sum(IIf([DRet]>0,1,0)) AS Adv, Sum(IIf([DRet]<0,1,0)) AS [Dec], Sum(IIf([DRet]=0,1,0)) AS NoChg FROM Dail...more >>

Hi all, I have a table with a string column, most of the data in there is in a format that can be converted into a datetime type. How can i just retrieve the rows that cannot be converted? ie.. TestColumn Row1 12/04/2004 Row2 03/09/2003 Row3 dd/mm/yyyy Row4 string Row5 0...more >>

I dont have an DDL for this so I am just going to write it out by hand CREATE TABLEA ( AddressID BIGINT, Address1 nvarchar(100), Address2 nvarchar(200), zipcode nvarchar(5) ) Primary key is AddressID CREATE Table JunctionA ( AddressI...more >>

I created a stored procedure to return a number of leads for customers, the list of metro cities between a specific time frame and group by time frame This is working but I also want it to return the list of metro cities even if there are no leads produced during that specific time frame. For...more >>

i am writing stored procedure that will allow me to create a flat file to be send to another company. in order to do this I need to write a query, create a cursor or temp table for each main record meeting criteria, then looping through child records and concatenate the data in one of these chi...more >>

Suppose you have a query involving an aggregate on a derived table such as: SELECT C.CustomerID, C.CustomerName, C.CustomerType, C.Address1, C.City, C.State, S.TotalSales FROM Customers C INNER JOIN (SELECT CustomerID, SUM(SalesAmount) as TotalSales FROM Sales GROUP BY Cus...more >>

Quick question: Is there any syntax to select all except 1 or 2 columns? I have a table with 80+ columns, and I want to return all of them except 1 or two, which I will convert when I select them. Right now, I do: "SELECT *, CONVERT(varchar, birthdate, 120) as birthdate FROM workers..." I thi...more >>

We have a problem that we have been wresting with for the last month with no success. Any help would be greatly appreciated! We have a complex stored procedure that has been running very slow from within SQL Job but only for a couple of hours every morning, then it goes back to normal. The str...more >>

Hi there, I've created an FTS Catalog based off an indexed view. For example purposes, say it has two columns: Name, Description. Is it possible to perform one query that will search both columns, but have them weighted differently (ie: Name carries much more weight than Description)? ...more >>

If I execute exec sp_executesql N'sp_helpindex N''[dbo].[authors]''' within EM/Mgmt Studio, this statement runs fine. If I try and parameterize it, like this: exec sp_executesql N'sp_helpindex N''@QualifiedTableName''', N'@QualifiedTableName nvarchar(15)', @QualifiedTableName = N'[dbo].[auth...more >>

What is a standard or acceptable practice for storing datetime values in a column for which 1. the date is always known 2. the time may not be known (say 40% of rows do not have a time value). Specific question: What value is to be stored for the time value [of the datetime column] when the...more >>

I'm wondering what "best practices" there are out there for keeping control on what has been deployed to a database. I've seen some companies have files (e.g. 001.sql, 002.sql, 003.sql) that keep up with incremental changes in a database (e.g. new procs, changes to procs, tables, etc). But...more >>

I need to take a row in a table, and duplicate it in that same table, while changing the values of two columns. Can anyone give a suggestion of the easiest way to do this? Currently I am trying to dump the data to a temporary table, changing the values of the two columns, then using INSE...more >>

I am designing a document management system and wanted to impliment groups similar to what you would find in Active Directory... Basically I want to query a table and have the SQL server go through the results, and based on a value returned per row either output that row to a view, or get the ...more >>

I have a program that reads a parameter table. The table has gotten quite big and I don't know what values are actually used and what aren't. I have a stored proc that you can call to get a value from the table but not everybody/everything uses it (some stored procs reference the table.) Is...more >>

Hi All, I created the following user-defined function. It passed the syntax check so I selected "OK". However, it was not added to the list under "User Defined Functions". Anyone can tell me what is wrong with my UDF? Thanks! Create FUNCTION dbo.FnReportInstancesGet --@AfterTimesta...more >>

Hi, Seems like very simple but I couldn't figure out why I'm having issues with nested queries. I have two tables T1 and T2 in which there is a common field which is SoldTo. Here T1 is a master table from which I insert data into T2 if there are any new SoldTo's in T1. For which, I wrote a...more >>

This problem is tricky to explain but I'll give it a go. I am trying to build a single select statement that takes a data column from a join early in the select and passes this into a function in a later join. e.g. function_a takes 1 parameter as input and returns a list of 2 columns called...more >>

I have a report currently, It shows everything I need it to show. Heres how it looks. It shows what areas have completed what courses and how many total completions it has Area Course ID Total EAST 11111 100 EAST 12222 110 WEST ...more >>

Hi guys, I have a SP that is executing a simple task, and I=B4m forcing an error, but the SQL Server is not catching the error, please help me. The code is: insert into table1 select * from table2_bad print @@error Notes: The table2_bad is not valid in the database...more >>

Hello, I am trying to use SQL2k5 to import and analyze data from various thrid-party Visual Fox Pro database applications we have. I'm stuck having to use SQL 2000 for now because I cannot figure out how to build the query to extract the data. SQL 2000 included a graphical query build...more >>

Hello! I’m using a statement with LIKE in the WHERE clause. Searching for rows containing a prefix (LIKE ‘MyPrefix%’) SQL Server seeks the index on that column – that’s fine. But if I want to search for a suffix (LIKE ‘%MySuffix’) an Index Scan is used and takes very long. Do ...more >>

Hi, I use a dynamically crafted SQL Statement to show the newest n orders for a given customer: SELECT TOP 15 dbo.vADR_subORD.* FROM dbo.vADR_subORD WHERE (ADR_Key = 17519) ORDER BY ORD_Key DESC which works as expected. As soon as the user goes below TOP 8, SQL Server retur...more >>

Hi, We are having performance problem with the below mentioned query. This stored procedure execution takes around 30 hours to complete. This fetches around 18 Million records. This process runs in three steps. 1:- Query fetches 18 million records from one database and insert it into t...more >>

Can SQL 2000 perform a cross table query like MSAccess97 eg: TRANSFORM Sum(Hours) AS [The Value] SELECT Resource, Sum(Hours) AS [Total Of Hours] FROM tblHours GROUP BY Resource PIVOT Format([Start_Date],"mmm yy"); ...more >>

If I insert a primary key like so: [ID] [int] IDENTITY (1, 1) NOT NULL I get presumably a circular key that ranges from 1...4294967296 (I've never actually experienced a "rollover" - the point where the key reaches 4294967296 and the next key goes back to 1, but I understand that in theory...more >>

I am not sure how to do this correct. I have a nullable datetime column and I would like to update the values in this column via a stored procedure which has a default param of getdate() for the column. The problem I face is that if I pass a date to the sp then it works OK but if I dont which...more >>