I have the following piece of code -- Prepare xml data to be transfered into an xml table in sql server DECLARE @xmlTable varchar(8000) DECLARE @DocHandle int select @xmlTable = '<DATA><xmlRow outcome_id="35" dt_outcome="12/1/2004" patient_regimen_id="21" regimen_id="2" record_type="existi...more >>

I have a stored proc which executes a BCP. The problem is that the BCP is some what erratic! Sometimes it hangs, sometimes it creates the .csv file. Is there an alternative to getting some table outputted to a .csv file? The problem I have with the CSV is that the CSV file name needs to be ...more >>

Hi All, We have a security requirement that the user account used to connect to sql should not have sysadmin priv. Now, we peform operations in SQL that make use of things like sp_OACreate/sp_replicationdboption/sp_dropdevice etc. BOL says "Only members of the sysadmin fixed server role can ex...more >>

Hi, I have set up an Access 97 database as a linked server. The connection works fine as I'm able to access it correctly. However, when I create a view, data in some fields of an Access table are causing errors. That is, when I run the view it returns each row correctly until it hits a...more >>

Hi All, Suppose I will performa a Bulk Insert from Client side, Mean using Con.Execute, Does that can raise any Time out? Thanks Prabhat ...more >>

Since I've been googling a lot for a solution to this problem, I decided to post this so others with the same problem can benefit: As you may all know, SQL Server 2000 can generate a single script for all views in the database, but when it does that they appear in alphabetical order. This m...more >>

Does anyone have a cross-reference table between PLSQL and TSQL. Also a have and have not table as well? Thank you! Tom...more >>

My query return a list like this Day Qty Mon 1 Tue 2 Wed 3 Mon 4 Tue 5 Wed 6 .... Mon, Tue, Wed repeats N times, that we do not know N in advance. Is there a way to reform the query output such that it will look like the following Day Qty Qty ... Mon 1 4 Tue 2 5...more >>

Hi - I'm trying to programmatically detach a SQL (actually, MSDE) database using sp_detach_db. I'm using VB.NET, and I'm getting a General Network Error. I'm the only user. Can anyone tell me what I'm doing wrong, or how to programmatically detach a database? Thanks for your help. - Je...more >>

Hello, I have a legacy field called ReferralName and I want to split it into Ref_FName, Ref_LName and RefCoName. I want to scan the contents of ReferralName and if it spots a comma such as in value "Smith, John" to write those values to the First and Last Name fields. If there is no comma...more >>

I want to search a text field for a certain set of characters and order the results by the number of occurrences of those characters in a field. For example, SELECT * FROM table_1 WHERE nvarchar_field_1 LIKE ?%sql%? [ORDER BY relevance] So, if nvarchar_field_1 in one row had 'sql' in it 4 ...more >>

Hi, following query is written in a book which is old and also sql server complains on "~" operator so I'm assuming the only way we can use this sql statement is <> ! right? thanks select s.s# from s where exists ( select p.city from p where p.city ~= s.city ) ...more >>

Hello, I am trying to connect to an SQL 2000 database. I am working with ASP.Net. I allways get this error: "Login failed for user SA. Reason: Not associated with a trusted SQL Server Connection" Here is my connection: Persist Security Info=False; Data Source=(local); Initial Catal...more >>

Hi, I come from an Oracle Background and am trying to understand why i'm observing certain behavior in Sql Server. I have read the posts here as well as Kalen's book but still have some rather fundamental questions, 'cos the behavior I'm seeing puzzles me.. A READPAST hint is used to bypa...more >>

I have a stored proc which outputs data to a .csv. The process had a problem and had to be killed. Once killed, the process begins rolling back. However the rollback is 0% and unknown amount of time until successful. When I delete .csv files, they are re-created, within seconds by the SQL ...more >>

I know this topic was addressed but I can't seem to find it. I'm looking for the best way to find and use the last inserted PK in a table. I've used something like.... insert into MyTable(f1, f2, f3) Select 'a', 'b','c' declare @LastInsertedPK int Select @LastInsertedPK = max(myPk...more >>

We have several laptops that run SQL Server databases and have Windows XP on them. After updating to Service Pack 2, we cannot connect to the SQL Server databases using a Java application. If SP2 is uninstalled, connection is fine. We have tried opening port 1433 for TCP/IP and opening port ...more >>

Is there a tool that one can use to analyze if a database has been configured efficiently. In particular, I would like to know if all the necessary keys and indexes have been created to support views and stored procedures thanks m ...more >>

I have a people table which joins to a address and a phone numbers table (each are many to many though a junction table) so each person can have multiple addresses and multiple phone numbers, but here is what I want to do... I want to get back a distinct listing of people with only their home ...more >>

I've killed a process which was doing a select statement. When doing a who_2 active, I can still see the process, with the command field: KILLED/ROLLBACK. It has been sitting liket his for approx 30min. What should I do? Thanks, Justni ...more >>

Hello all, when I alter a table (in order to add a new field to an existing table). it automatically will be added to the end row or the table. Is there any way to insert between two specific field (using sql commands in query analyzer) ? Your help will be appreciated Regards, ...more >>

I have a query that runs in a website via asp, it has a drop down that lists all of my names. I want a option to LIST all names.. This is my current query. SELECT * FROM Allinfo WHERE Store = '::Store::' AND Name = '::Name::' AND datetime > ('::m1::/::d1::/::y1::') AND datetime < ('::...more >>

Hello, I appreciate if you can suggest me some workarounds (oh, one will be enough...) for this problem: I have a function dbo.GetStoreID () - no calling param - which returns a scalar value. It makes some calculations and return a scalar. I have a view like: CREATE dbo.MyDocs AS SELECT * F...more >>

I have a column in a table named "gpa". It's grade point averages. All the data is numeric, but the table data type is VARCHAR. I tried to simply change the type by selecting DECIMAL from the pull down menu in the Design View, but it would'nt let me. Is there any way to convert the data ty...more >>

Hello ! I've got two tables : create table T1 ( a int primary key not null, b int ) go create table T2 ( a int primary key not null, b int ) go insert into t1 values (1, null) insert into t2 values (2, null) Why the following query does not return anything? set...more >>

I have a table name Trxs, now I would like to create another table TrxHistory which should have exact same structure as Trxs. I want to create this table in the same database. Can anyone suggest me quick way? Thanks. ...more >>

I need help in determining what kind of access I need to a table to help our Sys Admin give me rights. We don't have a DB admin so none of us knows too much about security. I need to be able to create view/create SP's/Create temp tables/Delete temp tables For the most part I should be able...more >>

Friends, I have a table contains daily log information. I need to find out if any rows missing out in a given date range. Is it possible to find out in a single select statement? or What is the near best solution? Scenario: Date UsrId ----- ------ 10/Oct/2004 3 12/Oct/2004 4 13/O...more >>

Hi, I would like to ask you do you know how to return a resultset and int value from Stored Procedure. If we have a table Teachers ========= ID INT PK NAME VARCHAR(25) ADDR VARCHAR(75) I would like to write a SP which must return the @COUNT of all teachers and also the resultset from...more >>

Hi I am looking to translate some data as in the example below, as far as I know this is the unpivot function in SQL 2005, I am trying to do it in SQL 2000, can someone help me write a query or function to do this. Thanks in advance. Example: Starting Data: Month A B ...more >>

Hi Is there any way in TSQL to select a column (with header) knowing just order number of this column? In example sth like that: Select *.1 from table Thanks ...more >>

Hello, I am building a large commerce system, hosting potentially thousands of companies but with relatively light activity for each one. I would like to keep everything in a single database with a company key in each table, but am trying to understand in advance the implications of this de...more >>

I was given some great advice on how to move info from one field to another within the same table. Now I need a little more help moving the rest of the info. Here is my dilemma. I have a table that when originally created only had one point of entry for addresses. Now I am in the process o...more >>

Hi, We are designing a system where in which the data is stored in thousands of tables. (its a ASP model. We store each client's details in separate table) e.g.) TableA1( a, b,c) TableA2( a, b,c,d,e) TableA3( d,g,h,a, b,c) .... TableA1000( a, b,c,j,k) TableA1999999( a, l,m,n, b,c) I wa...more >>

1) sending the code below always times out for the current day 2) sending the code below never times out for any other day except the current day ------------------------------------------ code snippet -------------------- ' Create a connection to SQL database located on the strConnection...more >>

I have a question for all you experts out there. Tell me if it can be done. I have a firewall device that I am exporting the logs from into a temporary table (using SELECT INTO). What I would like to do is then insert these records into a more permanent table. Problem is the number of ...more >>

I tried this in Query Analyzer (because I am also getting an error executing it through ADO in an ASP script): EXEC SaveFormData_Sp 'Client2',18,'Client2Test','Site 1','10/14/04','Don''t know','Desired work to be done','12314','1231231','1231@dsfasdf.com','','','','','','','','' All para...more >>

Got a big problem guys... In a nutshell, I've got a database structure that includes (names changed and columns dropped for simplicity reasons): Table1 Table1_id uniqueidentifier PK Table2_id uniqueidentifier FK Table1_data varchar(20) Table2 Table2_id uniqueiden...more >>

Hello, I am just moving from Access to SQL and I have a few questions: 1. What should be the type for the primary key? (In Access I use Autonumber. This type assigns an integer value to each record. Value is increased by 1 in each new record) 2. What shoud I use for short and long...more >>

I've been working on a database for over a year now and it has about 200 tables, lots of 'many to many' relationships, and about 20 mb of data. Over the past year its evolved quite a bit. Now when I want to export it to another server the DTS will fail due to some kind of data integrity conf...more >>

Have a database that I'm not sure how to determine which to use... varchar or char. I've read up on it and am getting conflicting views. What if I have a lot of fields that are 8 characters or less? I thought I should use char, but then I've read that you shouldn't if those fields can be n...more >>

Hello, Is it possible to set a code page for osql output file? ...more >>

I have SQL2000 Personal version on windows XP. For development purposes it works fine. Now, I would like to start to learn programming in new SQL server 2005. Can I install SQL 2005 beta2 version on windows XP or I must first install windows server 2003? Than, I have a lot of projects on my...more >>

Hi Folks, I am working with very small numbers in a life science application. I am having trouble reading some of these values from the record "as is". What I mean is that no matter what data type I use in variables to read these values into, I get varying "rounded" results. The key word ...more >>

I have two columns that I need to create a datetime column from in SQL Server 2000. The first is a float that represents military time. The second is a date field. They both are being imported from Oracle. So, date: 1/12/2003 time: 1223 should become: 1/12/2003 12:23 PM. Any ideas? R...more >>

I am trying to get a query to run using the following format: select ... From... Where 'Fed' in ('' + replace(evntClss,',',''',''') + '') EvntClss is a non quoted comma delimited list which may contain any combination of 1 or more of Fed,Mun,Prov, or Priv . The where clause successfully ...more >>

Please pardon my newbieness. I have an access query that joins two tables on a SQL database and returns some data. Easy enough. I want to start using ADO from Excel to just send the SQL string directly to the database instead of using Access as the middle man. Problem is that I don't know ...more >>

Hi The code: create table before (col1 nvarchar(99) ,col2 nvarchar(99)) alter table before add col3 nvarchar(99) declare @before table (col1 nvarchar(99) ,col2 nvarchar(99)) alter @before add col3 nvarchar(99) The problem: Is it possible to alter the @table , I can't seem to get it...more >>

I am using sql server 2000. Do any of the data modeling tools like ERwin, ER/Studio etc have the capability to work with extended properties? In other words, can one put in table and/or column level comments etc in one of these tools when data modeling is being done and the tool would put ...more >>

I am having a Query to Select all the name and some additional field from a table where name is equal to 'samay' or 'ruchika' My Query looks like Select Name, Address,County from Name where Name = 'samay' or Name = 'Ruchika' This returns me all the records in the table instead of returnin...more >>

I am trying to export data out of my database as a textfile. I am running into a small problem. I have a specific layout for the file. The last name in the database is 15 characters The first name in the database is 20 characters My file layout requires there to be 16 characters for each in...more >>

I have the following table decDuration strLogonName intYear intAbsID 3 AdrianE 2004 2683 4 AdrianE 2005 2683 I only require the 2005 record. I've tried max(intYear) but it still returns both records?...more >>

I don't really care whether Britain adopts the euro. It probably wouldn't make any difference. In fact, it'd make my life far easier if it adopted the dollar - just because the symbol for it is a lot more common! I've already found a bug in the AtlRegExp object in ATL server library <atlrx....more >>

Hi all In my database I have this 3 tables: I'm finding it very hard to understand some statistics/plans on this queries (bellow). I would expect query 3, 4, 5 and 6 to perform equally and better than 1 and 2. Whys is this happening? -- Paulo Morgado ------------- table Ap...more >>

How can I run two or more stored procedures within a transaction? Do I create each stored procedure separately then create another stored procedure that execute them within a transaction? if so how would I pass values to each parameter? Thanks ...more >>

Hi all, I’m trying to create an interface on VB that allows the user to enter a value (like “Zipcode = 55555” on the Address table) then transfer data which only match this value on the specific table from one SQL server to another (the two servers have the same tables). That means I ...more >>

I am generating stored procedures and functions using sp_execresultset. I've run into a problem where sometimes these run very slowly. I've tracked it down to the fact that they are created with SET ANSI_NULLS OFF. I have the default set to ON but this command seems to ignore it. If I run...more >>

I have a table name data which has data like this Msd Value Date 121 34 3 121 33 4 121 33 5 and so on I want to replace 121 by 11 -- Kittie -- Kittie...more >>

if i have a table TableA(col1 varchar(10)) with the following data: dataA dataB ..... dataJ how can i create/get an autoicrement field next to the data in my select statement ? (a single select statement if possible) the resultset should look like id_____data -------------- 1____...more >>

Is there a way to execute an import of a DBaseIII file into a SQL database using a stored procedure. I've tried BULK INSERT, but the file fields have extra spaces so I get an invalid null error and it doesn't work. I've tried sp_addlinkedserver to connect and transfer the info, I can get the s...more >>

I want to pass a string to be used as WHERE clause to an SP. The basic format of the WHERE clause is: Units.Fullname Like 'target%' The target can include an apostrophe - for example, the target could be "St. John's" I cannot get the syntax to call the SP and pass the criteria correct. My...more >>

I have the following sql code in a view. SELECT TOP 100 PERCENT COUNT(CASE WHEN blnSick = 1 THEN dtmDate ELSE dtmDate where blnWeekday = 1 and blnHoliday = 0 END) AS decDuration, dbo.aSpanSource.intAbsID, dbo.aSpanSource.strLogonName, dbo.aSpanSource.strAbsRsnCode,...more >>

Hi, I want to be able to use SQL authentication on my local MSDE 2000 , I've added a user using sp_addlogin, and try to connect using it, however it always gives me 'not associated with a trusted SQL server connection'. Is there any way I can force it to go to SQL mode without reinstalling it...more >>

i'm sorry if my question is not relevant, for i am a begginer in SQL server. I am looking a suggestion for this scenario: I have all my data in tables in SQL server. My windows applications access and modify those data. I read a bit that i can capture this modification event and do things (b...more >>