Here is my endpoint script. CREATE ENDPOINT XMLImport STATE = STARTED AS HTTP ( path='/sql', AUTHENTICATION=(INTEGRATED), PORTS = (CLEAR) ) FOR SOAP( WEBMETHOD 'XMLTEST' (NAME = 'db_chad.dbo.XMLTEST' ), WSDL = default, BATCHES=DISABLED, database='db_chad' ) GO F...more >>

Hello I have a legacy VS C++ program which works well with SQL Server 2000, creating tables etc as required. The same program does not work with Server 2005 express, eg CREATE TABLE fails. Is this a known issue with a fix or do I need to rewrite code? ...more >>

How do you generate ALTER scripts for all Views, Procs & UDFs in the database into individual files? I would like to do the same thing that I can do when I right click a database, select Tasks/Generate Scripts, but I would like them to be ALTERs not CREATEs. If not, is there another way to do ...more >>

Hello Is possible to connect with SqlServer 2005 express edition from other machine? ...more >>

Hi all I have several databases on SQL 2000 sp 3a + that I am looking after and want to maintain their statistics. The problem I have is that the tables can have millions of rows which gives me the following issues: 1. the default sample size does not appear large enough to keep stored ...more >>

I have two tables that I need help with inserting and/or updating. The tbl_1 table has data but there is no data in the tbl_2 table. Some of the columns in the tbl_2 table need to be updated with data from some of the columns in the tbl_1 table and the rest I need to pass the values. Below is...more >>

Assume each record in one table called "oraTab" of a Oracle Database Table need to get copied to another table in SQL Server. Most data are just copied field by field, except.... see next. Assume the SQL table is defined in such a way that all fields have defaults and do not allow nulls. In...more >>

Is there a way I can query for a string in any field in any table in a database? For example, I would like to do something like this: SELECT * FROM <any table> WHERE <any field> LIKE '%mysearchtext%' Thanks! ...more >>

When you try to back up a transaction log before the database has ever had a full backup, is there a system table that stores that information? Like if such and such is 0, then log can't be backed up.... Thanks, Mitch...more >>

Hi all, I have a question on how to calculate volume discount dynamically. The following is my setup: My cost table: Product_Code Discount_Lowwer_Bound Discount_Upper_Bound Price ItemName 1 500 $100 ItemName 501 1000 $80 ...more >>

Hi, I have a SQL server 2005 installed on windows 2003. I have to change the NetBios name of windows. Is there any set of instruction to reconfigure the SQL server to work with the new windows server name? Thank you, Max ...more >>

I have a query that uses a temporary table, tmpServicesWithAgentID. I just wonder if I can use a temporary table the same way as a regular table in an INNER JOIN statement below: SELECT TOP 1 @ErrorText = ErrorText, @AgentName = a.ServerName FROM #tmpServicesWithAgentID INNER JO...more >>

Can someone tell me high level what this is doing? I have never been able to understand the purpose of this kind of NOT EXISTS (SS 2000). This is the bottom part of the WHERE cluase off of a rather lengthy query: AND NOT EXISTS( SELECT NULL FROM dbname.dbo.tblname fsep WHERE fsep.Syste...more >>

Hi, I'm having trouble figuring out why in one case OVER() and GROUP BY work together, and in another they don't. I do feel that it's got something to do with CTE, not quite sure how exactly. Maybe someone could point me in the right direction? Here we go: --this works fine with both OVE...more >>

I would really apprecaite help with creating a SQL Query Batch Table which is the header table and each batch table has multiple GLTran records which are the transaction records per batch. Some batches have multi company transactions which is the value in the CPNYID coloum. So some batches (n...more >>

Hi! Do anyone have an idea how make data more compact: table t amount money, mtime smalldatetime 5, 2007-05-01 01:01:00 5, 2007-05-01 01:02:00 6, 2007-05-01 01:03:00 5, 2007-06-01 05:00:00 5, 2007-06-01 05:01:00 should result in table tt amount money beginTime smalldatetime endTi...more >>

Hi, I know I can select records randonly thru' SQL by using the following query: SELECT TOP 100 * column_list FROM table_name ORDER BY NEWID() Question: If I want to break this group randomly further, how would I do it? Do you think taking the first 25 records into group 1, ...more >>

Hello, I could use some help regarding converting an EPOCH datetime value, combined with getting the results greater than or equal to 4 days based on a normal SQL server datetime value. I'm able to convert the EPOCH datetime value such as: DATEADD(s, DATE_OPENED, '19700101') AS CONV_DATE_...more >>

Hi all MSSQL2000 From within the Enterprise Manager, I have created a linked server (in the sub item "security"). I have named it as "MyLinkedServer" After this, I try to create a new view: SELECT * FROM MyLinkedServer.MyDatabase.dbo.MyTable And this works fine (The view itself add a cert...more >>

Hi There i've a field integer in my table, when i import sql file which contains sql lines like insert the sql insert contains string which should be stripped off when i import data into table. Is there any way can i use instead trigger to strip off this field. I would pass the sql like thi...more >>

Hi I have to move around 100k rows between MDB to SQL server and move back the rows to from SQL Server to MDB. The MDB file can be any as specified by user. and MSSQL server is fixed. What are the different efficient ways to move the data between MDB and SQL server. Thanks, Prakash ...more >>

Hello, I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like: public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z) { //Do Something... x = .... y=... z=... } I want to call this code fro...more >>

Hi, In my SQL 2000, I would like to have a field which default value is "00000", what kind of datatype should I use? Thanks for help. Jason ...more >>

statement 1: EXISTS (SELECT * FROM publishers WHERE pub_id = titles.pub_id AND city LIKE 'B%') statement 2: EXISTS (SELECT City FROM publishers WHERE pub_id = titles.pub_id AND city LIKE 'B%') Do both statements have the same best performance? Or Statemen...more >>

Is there any way to convert a Hex passed as varchar Below works select cast(0xAD as int) But not this declare @name varchar(10) select @name= '0xAD' select cast(@name as int) --Gives a conversion error. Thanks Shiju Samuel ...more >>

I try to create an index that references three columns in a table: --------------------------------------------------------------------------------------- CREATE UNIQUE NONCLUSTERED INDEX [AK_DataReadyStateCache_Unique] ON [dbo].[DataReadyStateCache] ( [DataReadyStateTypeID] ASC, [Questi...more >>

I am joining 2 tables using a bunch of criteria - I also want to check for the existence of data from one field in one table to another field in the other table e.g I have "streetaddress" in one table "Barton", the other table has "address" field "1020 BARTON RD". I want to match if stre...more >>

Hi, I am using MS SQL2000. I tried to use the query to do the following. Why does it not work out? I could not get DateAdd("d",-1,Today) work correctly. select distinct a, count(*) from test where [timestamp] > DateAdd("d",-1,Today) group by a Thanks a lot for your help!! Michael ...more >>

Hello, I have a table where one field is a part number. These part numbers consist of a "base" number, then a dash ("-") and then a dash number. Examples: 300-102, 1534-32, 98872-12345. I would like to create a SP where I pass it a base number and it returns the next sequentially available...more >>

Hi, changing the database per name is no problem USE master GO .... and everything is okay. But i need this in a script and have the database name in a variable. How to make this? select @cmd = N'use ' + @oldDb + N';GO' exec sp_executesql @cmd doesn't work. Is something possible a...more >>

I'm trying to create a mailing list using a query. Simple enough. I have two tables. One is 'Practices' (doctor's offices), a parent table, and the other, 'Members', is a child table that has all the people who work for these practices. There is address information in both tables. If an ind...more >>

Hello, I've implemented the sample Printer Report Delivery extension in my installation of SSRS (SQL Server 2000). However, for some reason the Printer option doesn't show up in the combo box of the Report Delivery Options. Any ideas what I should look for? According to the tutorial, all...more >>

Hi All, I've been trying to implement the sample Report Services Printer Delivery extension. I've been through the tutorial and haven't been able to add the following security code group (shown at the bottom) successfully, since there is no indication of where this should go or why. I not...more >>

hey all, select * from categories where categoryid = 1 or categoryid = 2 what's the best way to create a stored procedure that would accept a variable amount of categories for the where clause? for example if i just wanted to search for categoryid = 1 instead of both 1 and 2. thank...more >>

I'm using Sql Server 2005. I have a table with two columns; PropertyName and PropertyValue. PropertyName has values like LastName, FirstName, Title and PropertyValue has the valus for those PropertyNames. Ex: AccountName Doe FirstName John LastName Doe Manager Jane Smith AccountName Cram...more >>

I had a development system crash (OS HDD failed) and had to reload things recently. My data drive and backups of that were intact. I reinstalled SQL 2k and tried to reattach the mdf files. Here's what I did in QA: EXEC sp_attach_single_file_db @dbname = N'Caser', @physname = N'D:\Data\MSSQL\Da...more >>

how can iwe identify tht the page is post back? ...more >>

I have a string type of column called 'Question' in a table, DataReadyStateCache. Each record for this column contains 7 commas (',') in the middle of the string. Each record for this column is in the format of: substring1,substring2,substring3,substring4,substring5,substring6,substring= =AD ...more >>

i have to find 7 th highest salary in a table. i use order byb clause in a sub quarry? is it work? ...more >>

Hi the BOL DDL trigger below is it possible to add rollback to it and still see changes that people have made to the database schema. I have tried with the rollback and nothing is entered onto the ddl_log table. I would like to capture all events even if rolled back is this possible. thank...more >>

I have a table of 10,000 rows .i have to made 1 cluster index and some non cluster index in that table.what i should make first i.e. made cluster index first or make non cluster index? ...more >>

I have whole bunch of rows in a table that contain 7 commas (',') as a separator. Each row is in the form of: substring1,substring2,substring3,substring4,substring5,substring6,substring7,substring8 I'll need to replace the LAST ',' with a '=' for each row of data. What's the most efficient S...more >>

Does anyone know if Microsofts' obsolesence policy still holds? That is, when a product is 2 versions behind, it is no longer supported with SP's and hotfixes? So, when SQL 2008 comes out, will SQL 2000 still be supported? I'd certainly expect that PSS would still take call on it, but most of...more >>

hey all, i'm doing a full backup from SQL Management studio and when i go to restore that database on a different server my user-defined login doesn't show up. I have to delete the old login which shows up in the database users and then recreate the user in Logins and remap to database. ...more >>

SQL Server 2000 I have a simple select statement to calculate the average: SELECT avg(duration) FROM tablename The problem is the average is being rounded incorrectly. For example, if I am calculating the average of 718, 721, 1032 the avg is 823.6. However, the avg is coming out to 8...more >>

Hi all, I am unable to export a small table (50 rows) into an excel on a remote = machine (called MyPC in this case). I run the following code in a Query = Analyzer: SELECT * FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel = 8.0;Database=3D\\MyPC\testshare\testing_new.xls;', 'Select * From ...more >>

How can I create a query for the following that will return results for say; * 'Phoned' and not 'Ordered' -> ('Harry') * 'Phoned' and not 'Invoiced' -> ('Dick' & 'Harry') - Jamie CREATE DATABASE [test_db] GO use [test_db] GO CREATE TABLE [dbo].[Customer] ( [id_Custom...more >>

When running this huge select statement, I got an error: "Invalid length parameter passed to the substring function" The statement is as below: --------------------------------------------------------------------------------------------------------------- SELECT DataReadyStateCacheID, ...more >>

hello I use MS SQL server 1) Example of a table with compound primary key would be: CREATE TABLE A ( b integer, c char(5), d integer, PRIMARY KEY ( b, c ) ); But how do we create compound for...more >>

I am trying to update a column that has the XML datatype. I get this error XML parsing: line 4, character 16, unexpected end of input when executing the sql below. What am i doing wrong? declare @temp nvarchar(100) set @temp =N'<books> <book name="Golf for dummies" /> <book name="Footba...more >>