Hi! This question is a bit embarrasing since it's so basic. Copying a database in SQLserver2005 is done in tasks/copy. Now I'm using the Express Edition and there is no "Copy" function under "Tasks", so I did a copy of the DB right in the folder where it's physically located and tried to ge...more >>

I have a select statement that results in a column of values being shown. I know how many values there will be as a result of that SELECT. e.g, SELECT UserID FROM Users WHERE FirstName = 'George' ORDER BY UserID ASC produces UserID 1 4 6 8 which are the IDs of different users named Geor...more >>

When sql server 2005 first came out I saw a demo where a tool that was some part of sql server 2005 was used to clean data. they were able to run a match on to tables based on a text column in each table and use a variable like 50% accurate where it used an algorithm to determine if there was...more >>

As you know,A is a noise word and it is saved in Noise.chs, Noise.cht, Noise.eng, Noise.enu and so on.After I removed A from upper files and re-created Full text index,I searched A in Full text index.Please refer to the following SQL. select * from searchresult_companyorder_business_e where...more >>

Hi, I am in the process of changing some sql created by access into Sequel. Most of the queries have worked fine but this one is causing us problems. Here is the sql in access: SELECT qV2CALC_PR_Expert1.Obligor_ID, qV2CALC_PR_Expert1.SIC_ID, qV2CALC_PR_Expert1.Weighting, qV2CALC_PR_Exp...more >>

Hi, Please see the code below... Is this overkill? Do I have to do both? Thank you guys! Rune -- Unique FieldS CONSTRAINT [UniqueName] UNIQUE ( [Field1], [Field2] ), CREATE UNIQUE CLUSTERED INDEX Unique IndexName ON [SchemaName].[TableName] ( [Field1] ASC, [Field2] ASC...more >>

We have a database named in the form 'x-y test data center'. This is a problem for some commands which seem to see the "-" as some kind of terminating character. eg: USE tries to find an entry named 'x'. So short of renaming, what can we do? I've tried [] and "", but no go. ...more >>

I need the code in my UDT that will direct the parsing for a UDT that acts just like the money type in SQL Server. I need the code that belongs in the parenthesis below: static readonly Regex PARSER = new Regex(); The UDT takes in data just like the money type. ...more >>

Hi all, I have a table: create table dbo.SomeObject(ID int identity(1,1) primary key,ParentID foreign key references SomeObject, ObjectName nvarchar(200)) filled with values: ID ParentID ObjectName 1 NULL Parent1 2 1 Child1 3 2 Child2 4 NULL Parent2 I have ...more >>

I hope this is the right place to post this. In teaching some ADO.NET, a student who can't remember precisely, says he has done stuff in his application level connection string so that the SQL Server client library / driver / data provider can specify record fetch size, so that: The appl...more >>

Hi, I have a stored proc which executes a dynamic SQL state where I build my select statement based on some conditions. I keep getting a Syntax error "converting the nvarchar value ' WHERE wo.ContractorID = b.ContractorID AND wo.StatusID = ' to a column of data type smallint" I try to s...more >>

This SQL statement returns the same result twice even though there is only one matching entry in the first table "tbl_Drivers" and none in the second table. Can anyone explain why? I would appreciate it very much. I am trying to improve my understanding of SQL. SELECT * FROM tbl_Drivers Drvs, ...more >>

I have a very huge sql procedure that loads data from 5 temp tables into 20 real tables. This is a financial application so it is very important that the data is loaded correctly. What is the proper way to write a "test script" to make sure that the data is being loaded correctly? I have neve...more >>

I have two datetime columns that store date and time information. I need to join on the columns, but only by the date and not the time. Is there a better/more efficient/index friendly way to do this in SQL2005 other than: Convert(Varchar(12), tablea.my_datetime, 101)) = Convert(Varchar(12...more >>

I need to replace test database (overwrite everything) in server 1 with the copy of the production database in server 2 once a day. We make complete database backup of production database once a day and store them in both local hard drive and tape drive. I am trying to setup a job in my t...more >>

Hi, I am trying to write a load script that implements two insert stored procedures. The first insert stored procedure will return an identity column for the row it inserted and then I need to use that value as a parameter for the second insert stored procedure. The problem is that my firs...more >>

I have a stored procedure that needs to call several other stored procedures and get a count of the total number of records retrieved. However, the scope of the @@RowCount variable is limited to each "child" stored procedure and not accessible from the "parent". Here is a sample of what I a...more >>

I have about 100 parametrs in my stored procedure. Every 10 have similar names. @inp_parint1, @inp_parvar1....... @inp_parint2, @inp_parvar2.... @inp_parint3, @inp_parvar3.. .... I want to insert or update records in my tables but don't want to have to type every insert or update stateme...more >>

Hi! Here's a newbie question on delete triggers, for anyone who has the patience... I have two tables: Artists artistID artistName CD-s cdID, cdName, artistName In Artists, artistID is the primary key In CD-s, artistID is the foreign key That's the only relation I've created a...more >>

I can't find a topic on this and need to get an import done today, so any help/suggestions is appreciated. I'm doing a DTS import (SQL Server 2000 SP3a) from an Excel spreadsheet source. One column in particular has mostly numeric values, but a few rows have values that start with a semicolon an...more >>

I am running SQL 2005 on a Win 2k3 R2. I currently have 4GB FB-DIMM and an Intel 5150 (Core 2 Duo Xeon) processor. I want to upgrade but don't have the money to upgrade both ram and processor at this time. Would a 2nd processor or 4 more GB of ram be my better investment? Thanks, CJ ...more >>

Hi, What is a timestamp field used for? Does it help performance (indexing)? Thanks ...more >>

I have a process that picks up files we receive from clients. If they get the data wrong (this happens often) we get the dreaded "String or binary data would be truncated." error. Without querying for "...where len(col1) > x" is there some what to find out which row (or even better, which ...more >>

I would like to be able to create a special form of "identity field" which is only unique within the context of another field. It's hard to explain, so I'll try to illustrate... Take a table (Table1) with the following fields: GroupID int ItemSequence int Whenever I create a new row in...more >>

Hi All, If we create an application that utilises SQL Server Express 2005, what would we have to distribute/install on the target machine to get it all working? Also, does the license allow us to do this? Thanks RobP 'There are only 10 types of people in this world - Those that understan...more >>

I have several similar queries. Since I am copying from one to another, it would be useful to be able to tile the two window (horizontally or vertically). I did see the splitter bar just above the scroll bar. It allows us to edit two different places within the same file. This familiar feature...more >>

I need to extra data using a query in a weekly basis, How can I setup my script where it will show the current day and the 5 previous days without imputting a date range in the script....more >>

I'm trying to update a field in MfrItemMaster to 'delete' if the same PK exists in the tmp table. When I run the below, it updates all rows in MfrItemMaster. But when I run just the subSELECT command, just two rows are returned. So I should be just updating two rows in MfrItemMaster, not all o...more >>

Hi, I have the following query...when it is embedded in ASP it works fine under any case...the ASP code is below: 'strSQL =" SELECT meter_contract.contract_id, meter_contract.latest_allowed_meter_input_dt, " & _ ' " meter_asset.serial_no, meter_asset.asset_desc, meter_asset.asset_id, mete...more >>

I have a query that I converted to a stored procedure. I used to type in the date that filtered the query. When I converted the query to a stored procedure I used a variable in place of the date. Now when i try to pass the date variable to the procedure I get an error message. "Syntax e...more >>

I am trying to e-mail the result of a COUNT use the following: ---------------------------------------------------------------------- declare @cresult int SELECT @cresult=COUNT(*) FROM userlist exec master.dbo.xp_cmdshell 'blat - -subject @cresult -body SMT-Inventory-Cleared -to bmellott@snd....more >>

Is it possible to do an aggregation that takes 2 parameters ? ex : SUM(Value1, Value2) The Accumulate method can take more than one parameter but an error occurs during the deployment. Thanks for your help....more >>

Hi Experts, Im getting error with the query result for "Divided by zero" actually im getting average for two records like (duty/freight) and (duty/kg_rcv) in the result, and now on i want if duy=0 then do not calculate, =========================== STOCK# DUTY FREIGHT KG_RCV ============...more >>

Anyone know of a good example of using the 2005 CLR to parse a text file into SQL 2005 tables ? Thanks....more >>

Right off the bat, I'm not interested in anyone giving me grief regarding this issue. I have (IMHO) valid reasons for this. And I also will refer you to Erland Sommarskog's response to a similar post a couple years ago: http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thre...more >>

Hi, I need some help on a make-table query. There are two tables table1 IDCUS, CUSNAME, DATEINVO, AMTINVO, PAID 001, John Smith, 2/12/2006, 210, 1 002, Bred Jones, 5/1/2006, 140, 0 003, Bob White, 9/29/2006, 2390, 0 004, Jill Scott, 8/14,2006, 530, 1 .. . . table2 IDCUS, OPTFIELD,...more >>

I'd like to copy/transform data from one database to another using SQL scripts (like moving data to an archival database before old production data is deleted from the production database). I've always done bulk transformations with a DTS package (SQL Server 2000) so I'm at a loss how I could do...more >>

Hi, On the subject of backups on SQL Server 2000, I'm looking at setting up Full backups WITH INIT and interim transaction logging between them WITH NOINIT. I'm looking at setting up 2 backup devices - 1 local to the server and 1 on across the network to a different server. For each device I'm ...more >>

I have 4 tables Projects, Requests, Project_Request_link, payments Create table #projects (projectid int, projectname char(12)) insert #projects select 1, 'AAA' union all select 2, 'BBB' union all select 3, 'CCC' Create table #requests (requestno int, requestname char (10), amount int)...more >>

I've always read that, in general, a function will return something but the procedure won't. Does this apply to stored procedures and functions in SQL Server? When I call s stored procedure from C#, I'm expecting that stored procedure to "return" a recordset. So in this case the SP and the fu...more >>

I have a table with following data. Type Value a NULL a NULL b NULL b NULL b NULL c NULL c NULL c NULL c NULL c NULL I need to update Value column like an enumeration. for example there ar...more >>

Hi, I would like to join few statements from few tables into a single one. Assuming these 3 tables has same type of ID ..Here is the example: tableA ====== SELECT id, name, COUNT( CASE WHEN FieldA ='IN' THEN 1 END ) AS 'TotalIN', COUNT( CASE WHEN FieldA ='OUT' THEN 1 END ) AS 'TotalO...more >>

I have got a program which pools every 5 secs an sql server for new records. I think this is not the good way . Is there any way i do something in the trigger so that whenever we get any records in db, my program be notified. I want your suggestions so that i implement best technique to cater ...more >>

Hello i have noticed a strange problem in the CLR of SQL 2005 We wrote a trigger in VB.Net wich works fine on server A but fails on server B so we investigated what was different and found that server A was installed from the start with US-English settings and server B was initially setup...more >>

I have 3 servers, A, B and C. A is linked to B which is linked to C. B has a view which pulls from tables on C. A has a procedure which carries out an 'Insert... Select...' statement from the view on B into a temporary table. The procedure on A is failing because SQL Server is trying t...more >>

HI How can I modify this to juts import however many rows are in the fikle. Thanks. bulk insert tmp FROM 'C:\Exports\Ref.csv' WITH (FIELDTERMINATOR=',', LASTROW =3200) ...more >>

If I have some simple text data like: 2005A115DWU 096003 24997.28 2005A115DWU 097003 -46718.25 2005A116DWU 000101 127129.38 2005A116DWU 035901 113579.90 2005A116DWU 041002 -7338.00 I can go i...more >>

Hi There I have a query that I was running in the query analyzer,master database that was taking about 1 sec. I created a view and paste the query into view, then created a stored procedure to acess the view. now when i execute the stored procedure it takes about 15 secconds. no ...more >>

Hi, I have a SP which have the @name input parameter, i will be passing the name of the person to this parameter. In the logic, the @name parameter should be included in the where clause with the wild card characters. example if i pass Tony, in the where clause it shd be like where ...more >>

Hi: How to write a query to query the following data ItemNo Vendor Qty ITM001 V001 100 \ ITM001 V002 200 > Group1 ITM001 V003 300 / ITM002 V004 100 \ ITM002 V005 200 > Group 2 ITM002 V006 300 / ITM003 V007 100 > Group 3 and result in ItemNo V...more >>

Hi, Does anyone know if it is possible to backup a SQL 2005 database with partitioned tables and restore it onto a standard edition which doesn't support partitioned tables? The expected behaviour ofcourse is to have the data in a regular non partitioned table after the restore. Thanks in a...more >>

I'm selecting Col1 from a table but giving it an alias of Col2. Col2 is also a column name in the table. That works ok but when trying to order by ABC.Col2 it actually uses the alias for Col1 and orders by col1. If the order by was just Col2 it would make sense but it's ignoring the facts it's...more >>

I have a database that contains a column called date. However for some reason the date is in the wrong format. E.G Date 6/2/2006 = 6 February 2006 Should be 2/6/2006 = 2 June 2006 I know that I could datepart and separate and then rejoin, however is there a better way? Thank...more >>

I have SQL2005 and I create new solution in SQL server managment studio. I create couple of queries in this solution and save them. When I click some of them to open, it always opens in master database. I would like to be opened in specified database otherwise I have to change it manually...more >>

when execute sp_sapceused i got the following result: name rows reserved data index_size unused TB_SF_AcOccur 369698 4447352 KB 95624 KB 41288 KB 4310440 KB the question is : if I don't recreate this table, how can i rearr...more >>