I have 3 tables. Table1(time,readingA) Table2(time,readingB) table3(time,readingC) Now the time can be same and it can be different. Now i want to know how do i join so that i get the data: time,readingA,readingB,readingC If the time is same then it is fine, but if the time is not same in tw...more >>

Can anyone tell me how to use the sp_xml_preparedocument to prepare a large string dynamically. What I am trying to do is this; -- Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, EXEC sp_executesql @sql The tricky bit is the last argument wh...more >>

Hi, I have only two fields in table 1. Integer and 2. Image I have application in VC++ . I have binded the table Now Problem is that If I use CursorLocation=adUseClient then the image data does't not reflect in Table while adding But If I use CursorLocation=adUseServer then it ...more >>

I'm selecting a big group of records for output, and I need to convert a couple columns from varchar to int. (SELECT CAST(mycharfield as int) as myintfield from ....) Problem is, some erroneous data has non-numeric characters in it, and SQL Server kills the whole SELECT, outputting no rows ...more >>

My app accesses a simple table (generated by a 3rd party tool) that has a 'datetime' column. My app needs to query for all rows in a specific date/time range. The prepare text is something like: SELECT * FROM TABLENAME WHERE COL >= ? AND COL < ? As mentioned above the 'COL' data t...more >>

Greetings one and all, my query stems from a migration of an existing MS Access 2000 query to SQL2000 in support of a reporting services implementation. The current access query first derives an Order Number from a reference field in a finance system, where the order number may be in one o...more >>

I have a table with the following data: 14794 3 Trond 14794 4 has 14794 5 new car Here is the table: CREATE TABLE [IText] ( [FKID] [int] NOT NULL , [SortIndex] [int] NOT NULL , [Content] [ntext] COLLATE Danish_Norwegian_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMAR...more >>

Hello! I am trying to execute following code against linked server LAOLAP: CREATE TABLE #tmp_Database( database_name sysname, database_size nvarchar(13) null, owner sysname, dbid smallint, created nvarchar(11), status nvarchar(600), compatibility_level tinyint ) INSERT INTO #tmp_Datab...more >>

I've installed transactional replication with subscriber initialization with snapshot on first distribution agent run. It works fine if I run snapshot agent prior to distribution agent. But if snapshot agent has never started I got error (something like: "there is no snapshot"). Is it possible...more >>

Does anyone know if you can check if an object (eg a table) exists. I am having trouble with procs that drop tables before creating them. Other users get the message, "cannot drop table tblTest ... doesn't exist in the system catalog". I need to do something like, If tblTest exists Drop tb...more >>

I am just getting back to SqlServer and TSQL after a 4 year hiatus. I want to write a trigger to update a view with the same record that is being inserted into a table. I have a trigger bound to the table to be inserted and since it is a simple process, I will probably forgoing using a sto...more >>

Hi, For two days I'm struggling with next: I have a table 'tblCompanies', a table 'tblWebsites' and a table 'tblLogins'. These 3 tables are related as 'many to many', the table 'tblLogins' connects both other tables. tblCompanies =================== ID CompanyName tblWebsites ====...more >>

Hello All... I am writing to an .xls via an ActiveX script in a DTS. I need to programatically move to the next column when the data dictates. After checking params, I'd like to simply say... If I am currently in column B, I need to make the CurCol (Current Column) = C Is there any...more >>

One of the field contains value B3 Rev AA, I want the result as B3RevAA. Any sql commant can help me to do this? As I have 1000 records needs to be clear up.Thanks...more >>

Hello, all -- I have a job that grabs the size of each SQL Server database and log file size and saves one row per database into a table. I run this routine on a daily basis, so I basically grab the size each day and can monitor growth. The table data is below. What I would like to do i...more >>

I have complicated queries in access I want to migrate to SQL. They're "complicated" only in the sense that I use the results of one calculated field in an imbedded if (iif) of another calculated field, which in turn is used in another imbedded if in another calculated field. I replaced the i...more >>

I have a Database called Products with 3 big tables Rims, Tires, and Stock my database is as follows. Products.mdf and Products.ldf How can I create file groups for the tables Rims, Tires, and Stock and attach these tables? Example: Products.mdf Rims.ndf Tires.ndf Stock.ndf ...more >>

Dear NG, After some poking around the Internet and reading Joe Celko's book on Trees and Hierarchies, I have made some progress getting a Product Structure into an indented Bill of Materials (BOM). Steve Kass demonstrated this in the program segments listed below. In order to move forward...more >>

February 15th, 2pm Pacific Time http://www.microsoft.com/technet/community/chats/chatroom.aspx Join members of the SQL Server development team as we discuss what's coming up in SQL Server 2000 Service Pack 4. See http://www.microsoft.com/technet/community/chats/default.mspx for more infor...more >>

I have the following structure with remote select permissions; I cannot create temp tables or use stored procs: tblEvent with event_pk, eventName tblReg with reg_pk, event_fk, person_fk, organization_fk I'm currently using a case statement to get counts for these categories: case when c...more >>

I am having difficulties with reclaiming the empty text space after I set the text column to null. I am running SQL 2K SP3a. The size of the text data does not change after I update over a million rows of text data to null. I dropped and recreated the clustered index column, shrunk the databas...more >>

I was asked to compile TSQL Practice document (for Developers/coworkers). Please correct factual mistakes below. This is proposed agenda for the meeting with developers. It is devoted to safe TSQL practice, mostly to availability of the data during updates/selects (middle layer is Dot Net, V...more >>

I have 1 Table has a field "docketNo" Varchar(50). the Foramt for the data is as follow. YY-12345 and YY-12345 AAA. I need help in selecting the docketNo different Format Example let say I have these format 99-58795 99-47896 99-58796 CRN This should be 2 types of format, bu...more >>

Hi all. I have inherited some table full of data. It has a primary key [int] clustered on one column. But this column was not created with IDENTITY. Now I need to add IDENTITY to this column (ALTER COLUMN, I guess) without loosing the data but can't figure out the syntax of proper script :(...more >>

Hey, I am trying to construct an ORDER BY clause for a SELECT DISTINCT query that attempts to order the data based on a particular bit value stored in a column named [Properties], but I get this failure message: "ORDER BY items must appear in the select list if SELECT DISTINCT is specified"...more >>

I´ve developed a testing stored procedure which inserts some values into a table. Inside the SP I´m controlling if there´s a duplicate column with the @@error flag. I´ve configured a job to execute this SP. My question is how can I tell SQLServer that I´m controlling the error produced by duplica...more >>

The follow generates the error: Server: Msg 207, Level 16, State 3, Line 19 Invalid column name 'UserID'. The inner derived table ("dt") cannot reference the outer derived table ("ActiveUsers") ? Nevermind the contrived example - it is only serving to illustrate the failing. The ...more >>

I'd like to create a query with a where clause that gets data for the previous month, ie: when running the report in January the report would use dates from 12/1 to 12/31. How can this be done?...more >>

Hi, How can I get table name and Column from SQL Server 2000. Thanks Dib ...more >>

Hello, Here is my problem, I maintain many websites for my company, each have = their own set of users. Sometimes, one user needs to be able to access = multiple websites (For example...me!). I'm attempting to come up with a = table structure that will allow for users and groups which I then...more >>

Hello everybody, I have table called Customer, where i have name of customers from arround the world..if i want to write a query to find name where ever it is special characters in name. how can i do it ? ...more >>

I have a stored procedure that runs a query and the result of the query is a varchar. I would like to use an output parameter to get the value and then pass that value into a variable to use elsewhere. Is this possible? Thanks...more >>

This may be a "how long's a piece of string"-type question but I'm trying to get a feel for the performance of the IN statement. Broadly, we've some software which generates SQL for counting but we've hit a situation where we could generate either re-engineer the SQL or simply wrap an existing...more >>

I have followed examples in a couple of books but am still unable to step into Stored Procedures in VS.NET. I enable sql debugging in the project properties, open the sp in server explorer and highlight a line where I want to start stepping through, then I select a line in my VB code such a...more >>

SQL debugging option is enabled in SQL server and in some client machines we are able to debug a SP but not in other machines. Is there any setting missing in client machines? Thanks in advance for your help Thanks, Ram Kumar ...more >>

Hi all, How do I find out what id's have execute permissions on each procedure in a database for all procedures using t-sql? -Thanks! dave ...more >>

Hi, I have a report I need to design which gets it's data form several tables in my SQL database. There are roughly 2300 records which are displayed but for some of them there could be people that display more than once, is there anyway in that instance where I can just get it to bring back t...more >>

Hi, I'm trying to report a months data, but the results differ when I use DATEPART as opposed to BETWEEN i.e. (The full queries are at the end of this post) this... where datepart(month,sessionstart) = 1 and datepart(year,sessionstart) = 2005 differs from this... where sessionstart...more >>

Using SQL 2000, we have been using the linked server functionality to connect two databases that up till now have resided on two separate servers. Our network people have now moved those databases so they are on the same server. Can we continue to use Linked Servers to pass queries back an...more >>

(I hope I don't have to provide a bunch of DDL, as it is a general question...but if I need to I will.) We have a rather complex SELECT statement in a STORED PROC that suddenly started to perform poorly. Suffice to say it builds a summary resultset from several tables, generally returning les...more >>

Does someone knows why SQL2000 is slower that SQL7 with queries using LIKE '%SearchValue%' ? I made a test with 2 servers (same hardware): Dell poweredge 1750 Bi Xeon 3.06 GHz 533 MHz 1 Mo cache level 3 2 Go RAM HardDrive 2x146 Go 10000 Rnd/sec RAID1 The first server has: Windows 200...more >>

I'm trying to come up with a more efficient way of doing a current SQL query. Right now, i'm doing the following: SELECT Assignees FROM Table WHERE Project = "1234" This gives me a dataset of all Assignees for the specified Project#. Then I loop through that returned dataset and use it ...more >>

Ok...below is a simple query that inserts some records into a temp table then updates another table using the temp table. It works great in Query Analyzer, but refuses to save in SQL Servers' stored procedure area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'" I'm banging my h...more >>

Hi, I wonder what the non-leaf-levels in an index contains. In a clustered index I know that the leaf-level is the data and in a non-clustered the leaf-level have references either to a clustered index or to the data in the table. But what about the non-leaf-levels? Do they contain the i...more >>

Hi, I am having a real headache with a table in my db, one of these days we found that some basic select queries were taking like 4 minutes, to execute, and they shouldn’t take more that one second, so we were getting hundreds of timeouts between our applications, I was assigned to check ...more >>

How can I update the following scenario... Table is: CREATE TABLE [InternationalRates] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Country] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AreaCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SwitchedRate] [varcha...more >>

The issue we are facing is that we need to determine if a string stored in a text datatype field is greater than 12,000 characters. How do you check for the length of a string in a text datatype database field?...more >>

I have the following cursor decleration that was working before. Each time I run it now I get the follwoing message: FOR UPDATE cannot be specified on a READ ONLY cursor. How do I resolve this? declare export_cursor cursor for select [RecordKey] from [ExportData] for update...more >>

Im parming a Nvarchar(4000) into a stored proc that uses xp_sendmail . is there any way i can include any sort of line feed or cr in the varchar currently if i parm in 'line 1 line 2' i get line 1 line 2 but what i want to get is line 1 line 2...more >>

I have simple question - could be a bit stupid I created a form in which several fields are obligatory, some are free to fill in now what is the best practice to follow Should i create several SP's for every possible combination ? Should i Create one SP where variables are possibly empty (...more >>

Over night we take a copy of various live SQL databases onto another SQL server for reporting purposes. I have a stored procedure that compares the latest live data against the 1 day old copies to ensure that they are up to date. I connect to the live databases using linked servers. Here's wh...more >>