Hi All, I am trying to export the data in a excel file using "bcp". right now i am exporting data from two different table in two separate files and it is running fine. But, i want the data of two tables in a single excel file but in separate sheets. Please suggest. Thanks in advance. ...more >>

I am just starting to use SQL Server so this is probably sql 101 but has had me stumped for a few hours. I want to create records in a table that consist of a project number and a user ID. The project number comes from a select statement and the user ID is passed as a parameter to the stored...more >>

Client tool for SQL Server 2005 What client tool should I use to control my SQL Server 2005? I don't have SQL Server 2005 installed on my server and therefore not the client tool, but I need it to control the database at my ISP. TIRislaa ...more >>

Is this kosher SQL? select m.*, y.* from mywidgets m inner join yourwidgets y on m.key = y.key or m.key = 'All' or y.key = 'All' This is for a situation where a widget may have option 1 or option 2 or option 3 or all of the above. I think I could normalize this by using ...more >>

Hi all, I have an SQL query (which I'm going to dumb down here) which I am trying to output. Say I currently have an output of REQNUMBER USERCODE --------------------- ------------------------ 1123 SMITH 1245 MOR...more >>

Here is the query I am running... Select * From master.dbo.SysDatabases Where Name = 'abc' Using SQL 2000 it takes 0.01 seconds Using SQL 2005 it takes 1.06 seconds Why? ...and what can be done about this?...more >>

Hi, I have a SP in sql2k sp4. insertint a record into a table. I ran from query analyzer, runs OK without any errors. If run from a sql job that calls the SP, i rec'd the error says, String or binary data would be truncated Any ideas? ...more >>

I have created a User Defined Type (UDT) . I declare it this way: IntervalUDT u = new IntervalUDT(); It is formatted like this: 3:4 I would like to Add the value of the UDT at the point in the loop to the ArrayList orderedpair. I could use: orderedpair.Add(u.ToString()); //see...more >>

Hi, I've just been trying to debug an SP on SQL server 2000 using QA when users started reporting that the server was inaccessible. This is a 2 server failover cluster so luckily the second server took over. Just wondering if anyone has seen this issue before and how to stop it. The SP ...more >>

I need some help using Transact SQL on SQL Server 2005. I'm passing a variable that has multiple items in it as a variable to a SQL Statement. Specifically I have a where clause that uses "IN" and it isn't taking my variable. I have a webform that users select multiple customer names, t...more >>

Hi, We are in search of beta testers for our new software tool for SQL Server : Remote Keys allows you to define and enforce cross databases integrity constraint. With Remote Keys, you can create a foreign key between to SQL tables located in distinct databases. Beta version can be downl...more >>

Hi, I have the following script which enables full text search on the database: ------------------------------------------------------------------------------ sp_fulltext_database 'enable' sp_fulltext_catalog 'cntdocimg', 'create' sp_fulltext_table 'ContactDocumentImage', 'create', 'cnt...more >>

I have a view that looks like CREATE VIEW foo AS SELECT <bunch of cols> FROM <bunch of joined tables> WHERE t1.EntryDate >= DATEADD(mm,-18,GETDATE()) This takes about 10 minutes to run, and when I join the view to other tables in a larger query, the whole thing takes several hours. Howeve...more >>

Hi Guys, In my company, the database is in Frence and I am in Montreal. I write a script to update a table, which has 70k rows. I try to update a column for all rows that has non-cluster index on using other column in the same table. It haven't finished after 40 minutes. I stop it. It is too ...more >>

I want to do a Drop Table Whatever then I would like to do the following: Select a, b, c from CurrentTable into Whatever where yada = 'A' the question is, is there a way to see if the Whatever table exists so my code doesn't bomb? SQL 2005 ...more >>

I have successfully recreated the stored procedure to export the image of a given file to a path with a given name. Create PROCEDURE [dbo].[sp_textcopy] ( @filename varchar (175), @whereclause varchar (175)) AS DECLARE @exec_str varchar (255) SELECT @exec_str = 'C:\tex...more >>

To get the database path in SQL 2000 I use 'select * from sysdatabases' and then interrogate the FileName field returned in the recordset. How would I do the same in SQL 2005? Thanks, Mark...more >>

My SP has a query in which the parameter sent to it is used in an inner join ("table.val = @value"). Now I need to add the logic so that the value being sent is a specific value or a wildcard. I was thinking of changing the inner join to a "LIKE" operator so that I can send it the actual pa...more >>

Hello, I have 3 columns in the table - Col1, Col2, Col3. I want to output all three as one column i.e need to concat the strings. However, I need to display a ',' between column values i.e the format is "Col1, Col2, Col3". Additionally, if Col1 is null then I have to remove the ',' before Col...more >>

I have a decimal which represents how many hours it will take to complete a task. I need to format it in a nice way DD:HH:MM, preferrably in a single statement. timetocompletion = .09 need to show like this: 00:00:05 Is there an easy way to do this in SQL Server 2005? ...more >>

Hi all I have a stored procedure that's showing me some weird results. The relevant portion of the SP is: create procedure foo as set nocount on declare @version int set @version = (select max(version)+1 from myTable) [...snip...] In myTable, the version field is an int not null defau...more >>

create table T ( x integer, y integer, ); create index Ti on T(x,y); insert into t select i/1000 as x, mod(i,1000) as y from Integers where i < 1000000 select * from T where x = 1 and y = x+1 What is the best access path? How do you influence it if optimizerd didn't find i...more >>

Hi, Using sql2005. I have a simple trigger set up on a table for UPDATE... all it does is: UPDATE SIMS_Config SET LastUpdate=getdate() WHERE FilePolicyGroupID IN (SELECT FilePolicyGroupID from inserted) It fails with the error: "Subquery returned more than 1 value. This is not permi...more >>

IN SQL Server 2005, is there any way to make the pivot function dynamic?? i.e. USE AdventureWorks; GO SELECT VendorID, * --[164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p ...more >>

All, I was wanting to know if it was possible to use a parameter in a SQL query for a list. It's better if I give an example... SELECT * FROM MYTABLE WHERE A IN (1,2,3); I want to know if it's possible to SELECT * FROM MYTABLE WHERE A IN @value and still have it interpret it as three...more >>

Hi, How can I achieve proper casing in SQL Server 2005? Thanks...more >>

Has anyone successfully built or updated a data table and updated a SQL table where some of the column names have dashes (eg. a-personnelNumber)? I have code that works when I limit the update to just columns with no dashes, but as soon as I include one of the columns with a dash in the name,...more >>

Hello All, I would like to make some tests on the database to tune it. First of all there are indexes to be modified. I started with Query Analyzer: SET STATISTICS IO ON SET STATISTICS TIME ON and execution plan to provide as many rates as it is possible. Statistics information are easy to sto...more >>

I've got a beginner question. I created an SSIS package to read data from a text file to a table I have several hundred text files to read to the same table and the layout of the text files are all the same. I have been loading each text file one at a time, by modifying the source filename in...more >>

Is it possible to determine if any rows in a table are locked? Ideally, what I'd like to do is: WHILE(Record/Table is locked) WAIT(1000) UPDATE <Table> ...more >>

Hi all, Is there a way to check a date column - in a table - in a bak file to validate a date before it is restored in a job. I need to make sure the max date in the column is yesterdays date. Should always be yesterdays date no matter what date it is. The table contains millions of rows. ...more >>

I've got a table with a field for Month and a field for Year and an IDENTITY field: create table #t (id int identity, m int, y int) insert #t values(12, 2005) insert #t values(1, 2006) insert #t values(3, 2006) insert #t values(4, 2006) insert #t values(5, 2006) insert #t values(9, 2006) ...more >>

I ALWAYS seem to struggle with CAST and CONVERT...Maybe just because I don't use it that often. I have to perform some division. SQL follows... CUSTDATA.value/GEN.p_and_i AS 'Reserves Months' The CUSTDATA.value is a user-defined field built into our application and we can store numb...more >>

I have a table (lets say, table1) with two fields (lets say, Field1, Field2). Some sample data in the table : Field1, Field2 1 --- a 1 --- b 1 --- c 2 --- e 2 --- f 2 --- g I need to write a query to get an output like this: 1 --- a,b,c 2 --- e,f,g I would like to write ...more >>

Hello, in SQL Server 2005, is it better to query the Dynamic Management Views to find information like database size, or use the traditional DBCC SHOWFILESTATS and DBCC SQLPERF(LOGSPACE)? I am writing a script to gather database trending information for historical purposes. Thanks, Mitch...more >>

Hi guys Hope you can help me. I've got the following scenario... Windows 2003 network domain with a PDC and a Secondary DC. Active Directory is replicated. Both servers are running an ASP.NET 2.0 website on IIS, and we're using DNS "round robin" so if one server goes down, the .net website...more >>

Is there any best way to synchronize two tables which is on different servers and it could be on same server. These tables has same schema. Co-related queries for extract rows which has to be updated, inserted or deleted from destination table does not work when these tables are on differen...more >>

I need to quickly reverse engineer a database diagram just to display FK relationships and table names. I just need to provide a couple of acceptable pictures to the management. Ideally the output should be in jpeg. I have the database on both SQL Server 2000 and 2005. Any suggestions would b...more >>

How can i rewrite this query so i no longer get a table scan? select name from person where isnull(personid, -1) = isnull(@personid, -1) personid is a nullable column and the passed in variable is also nullable. Do i need 2 statements in a union? ...more >>

I am passing a value to a SQL select statement via VB.NET but the select statement recognizes only the first chacracter in the string. Example: data type = nchar(100) passed value = '300' select statement recognizes only '3' When I test the statement in query analyzer it works fine. When c...more >>

Well, I have hundred of tables currently doesn't have foreign keys at all. The existing design use tables without the Primary and Foreign keys. What I am trying to do is find all the foreign keys in that database. For example, I have tableA with column_Num that is indexed. There are tab...more >>

Hi - I am looking into possibly attending a few seminars/courses in Sql Server 2005; preferrably in the northeast. Does anyone have any recommendations? Also, any recommendations for good books/CDs, etc.? -- Sandy...more >>

Hi, I have a stored procedure, sp_3col which will return 3 columns. What is the syntax so that I can select something from that 3 columns output. For example, the sp_3col output is liked id , zip , date 1 10001 1/1/2001 2 20932 2/2/2002 3 30883 3/3/2003 I am trying to return the rows ...more >>

I want to create sql that produce result in dynamic column month headings. My table structure is; productcode qty invoicedate BBS1 500 20060905 BBS2 1000 20060915 BBS1 100 20061015 BBS2 500 20061025 BBS2 500 20061005 BBS1 1000 20061030 i need sum(qty) by monthyear in a date range.e....more >>

hi, i want to set getdate() as the default for the SP parameter example create procedure sample @date datetime = getdate as select @date = convert(char(10),@date,101) + ' 23:59:59.997' it's throwing me conversion error. i want that conversion, because if i pass value, that is requi...more >>

Hi all, There is a huge table in the database and there are about 25,00,000 records in it. I need to delete about 24,00,000 records from this table. Simplest way is to copy the 1,00,000 records that we need to a newly created table(temporary) and then truncate the origninal table and tran...more >>

Hi, What's the fastest way to compare one table with another and display the missing data. I have two tables TABLE1 Cust_City,Cust_State,Cust_Zip TABLE2 City,State,Zip I am trying to display all data in TABLE1 which does not exists in TABLE2 (Check must be done on all columns)...more >>

hi, if the user is not passing value to the parameter, then i shd set the default value to that parameter. example create proc sample @Startdate datetime as print @StartDate if the user is not passing value to @StartDate then i want to set the default value to it as 01/01/2006 ...more >>

hi, i want to find the leftmost characters before space. example emp id AA Adhvika BBB Tony C2 Partha Sarathy i want the result as AA BBB C2 i.e string before first space please help me to solve this. thank you vanitha ...more >>

Hi, How can I disable an integer column from being IDENTITY? I need to do it with TSQL statements, not SSMS or EM. Thanks in advance, Leila ...more >>

i try to combine 2 query result into 1 table. The query sort data from the same table. However the result is into 2 columns (UserCode, count(UserCode)). Can i do it into 3 column (UserCode, Column1, Column2)? do i use the wrong method? Thanks a lot. ********************* select Us...more >>

i need to do some calculation on date. How can i add quote(') at pre & post @begin in the sql query? Thanks a lot. declare @begin varchar(20) declare @query1 varchar(300) set @begin = '2006-11-1' set @query1 = 'select UserCode, count(UserCode) from TableA where Done_Time > ' + @begin ...more >>

Hello there After several checks i've got some conclusion on using indexes. If i have two tables. And i join Table1 and Table2 with relate on Table1.ID and Table2.FK_ID and FK_ID is not part of clustered index and in the select i bring other fields from Table2 the relationship between t...more >>

Hi, I have posted this earlier on the 15th of this month but still looking for a solution... --------------------- Hi, I have the following problem: sql server 2000 sp3a I need to get the last (most recent) Value column from table CurrencyPairMidsTmp in a defined time period per each ...more >>

Hi - SQL 2005 provides for schemas to be setup and managed as entities separate from users. This allows one to group database objects by function into different schemas within a database; say performance related objects in a perform schema and administration in an admin schema. Possible ben...more >>

i wish to make sure it can retrieve 2 digits. Jan - 01, Feb -02 instead 1 or 2 what's the fastest way to do it? Thanks a lot. tony ...more >>