Hello ! I am using this query, my tables contains more then 900000 rows, and i am getting result very slow. pl. help to optimize it . GetDocumentsOfIndex is function whcih will return table. select distinct document_id, cached_folder_id from rep_document where document_id in ((select docu...more >>

Hi, I am serializing a collection of objects of a Type(Class). The class contians a DateTime type property in it. Now, I am serializing this collection. For some of the DateTime values, I get the DateTime with the TimeZone Information included in the XML. Like this ... 2007-05-30T16:12...more >>

I installed a copy of SQL 2005 Express on another computer here using the automatic download and install from Microsoft supported by the setup toolkit. I can't access this server from another machine on the LAN using SQL Management Studio Express. I can 'see' the server but cannot connect. ...more >>

Can I just say, SELECT * FROM ServerA.DatabaseA.SchemaA.TableA UNION SELECT * FROM ServerB.DatabaseB.SchemaB.TableB Without explicitly creating a "linked server" ? -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "Escriba coda ergo sum." -- vbSensei ...more >>

I'm trying to create a sql statement to use as a recordset on an Access data form. I have 2 tables called "users" & "accounts". The "users" table on contains an id field, first name and last name field. Any "user" record may have many records in the "accounts" table. They are joined by the "us...more >>

Hi there, Im writing a query in which I wan to calculate a colum based on the values of other columns, so what I wanted to do was something like IF colType=3 then colRecovery * .234 ELSE colReverb * 0.6 How can I do this ?? Cheers ...more >>

I can't do this client side as my "client" is excel Desire result is three fields returned 1,8,A;B 2,30,B;C;D CREATE TABLE #Orders ( Order_ID int ) CREATE TABLE #OrderAccounts ( Order_ID int, Account char(1), Amount int ) INSERT INTO #Orders (Order_ID) VALUES (1) INSERT INTO...more >>

I have an indexed view iv_cm. And I have a query like this: select * from iv_cm with(noexpand) where iv_cm.col_a = 12345 The optimizer does the right thing for this query. It does an "index seek" on iv_cm using the index on col_a. But if there is any other constraint on any other column ...more >>

When using the SQL Server Management Studio to restore a database from a device (filename.bak, for example), the options page shows the logical name and the fully qualified file name for each of the database files. Does anyone know how I might determine that type of information from a backup ...more >>

I am trying to summarize data based on a period range in related tables. Table one has a column that includes a date/time field. Table two has an array field (MTD_1 to MTD_12) that has values I want to summarize based on the date field in table one. Example... Chekdate is 03/15/2007 in ta...more >>

In SQL 2000 I could create one script that both dropped (if existed) and created a view. Does SQL 2005 have anything like it anywhere. I can only find separate DROP and CREATE scripting. Thanks. David ...more >>

OK, I can't think of a good way to do this so I'm hoping someone else has some ideas: My boss and I are trying to create a help desk system for our customers. Boss decided that instead of only assigning tickets to one particular tech, he wants to be able to assign techs to groups and assign ...more >>

Is it possible to create generate xsi:schemaLocation="http:// http://.../name.xsd" while using the for XML Path syntax? I am able to define everthing else except that (see example) below. Any help would be greatly appreciated...Thanks Example: <Example xmlns:xsi="http://..." xsi:schemaLoca...more >>

I have to update the prices in SQL2K. if the price is 201.10, decimal ends with 0, I need to add 1 so it is 201.11. How to select the record with amount's decimal ending with 0? Thanks ...more >>

This is concerning a problem on an application database on SQL Server 2005 SP2 (Build 3159) If I run the following command everything works fine. BEGIN TRAN UPDATE Table9 SET Remarks = 'Test' WHERE Id =1 ROLLBACK TRAN but if I change the above command to BEGIN TRAN UPDATE T...more >>

Hi, I'm looking for a simple and freeware data generator application. I've googled for an hour, and couldn't find something reasonable that is also free. Do you guys have some recommendations for something like this? Which tools do you use? Thanks, Shlomo ...more >>

I have a stored procedure that returns a value from a linked server. I am unable to enable remote DTC access on that server. The sproc returns the correct value, but when I try to insert that value in to a table, I receive the "unable to begin a distributed transaction" message (Msg 7391). I'm t...more >>

I got these msgs on one of my servers (SS 2000 SP4). It looks like someone was playing around with this which is rather chilling. Is there any legitimate reason for this? What I mean by that is that I know that clicking on Enterprise Manager or running the Copy Database Wizard will sometim...more >>

Okay, brainiacs, I need some help. I have a field in a database that is defined as text and contains some characters and some bars (|). The bar separates the values in the field into columns. For example, 5235D4793912C9O|120543||385 would mean 4 columns equal to: A: 5235D4793912C9O ...more >>

Hello, I will step right into this with an example. Let's assume we have the following tables: CREATE TABLE #ProductType( ProductTypeId int Primary key, ProductTypeDescription varchar(50) ) Insert Into #ProductType Values (1, 'Gizmos') Insert Into #ProductType Values (2, 'Gadgets') ...more >>

Hello world, I have a table i'm trying to get the number of days difference using the DATEDIFF() funciton. I was wondering if I can use something other than MIN or MAX function to get the datediff from the Next MIN date. (i.e. 2006-08-15 ) table_b order_no rel_no rel_date 1150 ...more >>

Hi, I delete some rows from a table in Microsoft SQL Server 2000, and the result of the sp_spaceused stored procedure shows that lots of space in the table is not released. I went thru the Article ID:934378 related to Space is not released after you delete some rows from a table in S2K and...more >>

hi, can this be done? i have stored procedure 1 that calls stored procedure 2 in the FROM clause. Stored procedure 2 is supposed to return a table. For example: select a, b, c from table1 , (exec storedproc2) table2 where a = b and..... please advise...more >>

Hi, One of my procedure is extracting data from flat files and insert that records into 4 tables. Two of them in same DB and rest of that tables in some other server(using linked server we are inserting into these table). This procedure has 'set xact_abort on' in two places one of that in...more >>

HI , I have two tables create table locations (loc_id int primary key,location_code char(20)) sample data insert into locations values (1,'xxx') insert into locations values(2,'yyy') insert into locations values(3,'zzz') insert into locations values(4,'www') select * from locations Tabl...more >>

What am I doing wrong when I execute a SROC (using Output parameters) from within another? The following code: Declare @IPAddressId Int Exec Login_GetIPAddressId '127.0.0.1', @IPAddressId Select @IPAddressId Go gives Null. Why? ALTER PROC Login_GetIPAddressId (@IP...more >>

I am using SQL Mgt. Studio Express (2005). I want to use it to create a script that will create a database already on one of the servers. I thought "Script Database As CREATE to" would do this. However, the script created produces these errors when I run it in SQLCMD.EXE: Changed database c...more >>

Hello, I need help in converting a correlated query to an uncorrelated query to optimize the performance. The query looks something like this: SELECT DISTINCT gwcompanyguid FROM ADDRESS0 as adresse WHERE (SELECT count(Document0.updatetimestamp) FROM Document0 WHERE ( gguid IN (SELECT gui...more >>

Using SQL 2000. Database is based on 14 test questions. Each question is a separate column that can be answered yes or no. I need direction in creating a query that would produce results like: Yes No Question 1 11 5 Question 2 2 7 Que...more >>

Hi! I have a Stored Procedure which takes 48 parameters and have a few hundreds of SQL-code lines. The SP uses few temporary tables, 10-15 selects, some aggregate functions, groupings etc... Case 1: When I execute SP's SQL-code as a 'simple' query (instead of input parameters I declare ...more >>

How can I insert value into field "datetime" type. I try like '2007-05-29 00:00:00' but I got "Error converting data type varchar to datatime.". I also try convert(datetime,'2007-05-29 00:00:00',112) but this did not work too. ...more >>

Hi I'm not sure if I understand the effects of left joining properly. I had assumed that the effects of filter would take precedence over the effects of the left join but I have an example where that doesn't seem to be the case. I have 6 temporary tables which I am left joining together an...more >>

Hello, I have a complex view in a SQL2K Database, made by another person. Designer Screenshot: http://freenet-homepage.de/schuttvim/With_Join.gif In it there is a grafical join symbol which I would really like to understand. It is a symbol which looks like an Outer Join icon with the char...more >>

I've read literally dozens of posts where people want to set a field to its default value, but I need to iterate through the fields on a table and do something if its value does not equal its default value. I'm in a trigger and so I know the table name. I then loop through each of the fields u...more >>

I am working with HIPAA Taxonomy codes. 5 tables are involved as a base. Before we really begin, this is an integration project, the original data sits in a db2 database and the structure is untouchable. Since taxonomy data is pretty static we are creating a SQL version in our application....more >>

Hi, I want to write a stored procedure which takes some parameters and inserts parameter values into a table. I also want some of the parameters to be optional. If I omit passing optional parameters SP should insert corresponding field's default value. I try using -default- keyword for a ...more >>