how to display 10 rows at a time when 1000's returned form sql query how to select the 20th-30th row in a where clause? e.g. select the 20th to 30th employee who name is 'bob' in tsql. i need this because my select often returns 1000s of rows and i want to display them in pages of 10 at a tim...more >>

would like to know if I get to do some view tying a table SQL with sybase for the query analiser, I have a program that the database this in Sybase and other in SQL and I need to tie those tables. ...more >>

Does anyone know how you can select certain XML elements from an XML data field? I have an XML field in a table that contains XML like this: <Form> <Row/> <Row/> </Form> Right now, I have a query that will find all rows in the table that contains the XML that I'm looking for, but...more >>

Hi, I've been racking my brain for days on how to select all the details on a distinct field of only on one column. For Eg. TableOne ------------------------------ ProdID | Description ------------------------------ 1 | Toy 1 | Toys 1 | Xmas Toys 2 ...more >>

Hi all, What would be SQL Server's version of this Oracle code: ************************************************************* MERGE INTO destination_table dest USING (SELECT col1, col2, col3 FROM source_table) source ON (dest.col1 =3D source.col1) WHEN MATCHED THEN ...more >>

I am relatively new to SQL programming and I found some code that I am having a problem understanding. The basic format of the code is a SELECT statement followed by some column references with the final being 0 FROM (i.e. SELECT [column ref], [column ref], 0 FROM... I googled this type of se...more >>

we have a "snapshot" database used for development testing we it constantly (weekly only) synced with the development database (on same server) I wanted to set up a schedule that will backup the database called development every sunday morning, then restore that onto the "devsnapshot" databas...more >>

i've a database where huge data is dumped realtime, i'd like to run a query in DTS which should keep only the last 90 days data and every day delete the 91st day. hope i'm able to explain well. please advise necessary procedure ASAP....more >>

I have two queries and was wondering which would be considered more efficent? Please, all oppinions welcome! as to table structure, well i'll giv it if any one wants it, but all i got to say is i didn't create it! (included is a date create function) start declare @guid as varchar(...more >>

Hello - I am new to Sql Server 2005 and most probably screwed something up. I am getting an error "The user is not associated with a trusted Sql Server connection" when accessing my app using a regular user login. Is NT Authority\System supposed to be both under Security/Users in MyDB a...more >>

Hi, I have the foll sample table prodid saledate 01 2006-04-01 01 2006-05-26 02 2006-04-02 03 2006-06-05 01 2006-06-25 01 2006-06-26 01 2006-06-30 I am try...more >>

I pulled this example from Books Online. I cannot figure out how to make the CTE return the data in a different order. I don't want the data ordered by the Level. I want the child data to appear directly under the parent data. Is that possible? USE AdventureWorks; GO WITH DirectRepo...more >>

I need some assistance I am trying to create a stored procedure where data will be inserted into a temporary tbl. Now the name of the table will have a variable, which will include time. But when I try to run the procedure, i get an error stating that @tblname wasn't declared. Please help ...more >>

Hi, What I want to do is: DECLARE @sqlName varchar(255) DECLARE @temp NVARCHAR(100) SET @sqlName =(select name from master.dbo.sysdatabases where name like 'Job_%') SET @temp = 'USE ' + RTRIM(@sqlName) PRINT @sqlName EXEC (@temp) GO --rest of my SQL code -- Now basically I am...more >>

All, Is there anyway to ask SQL to run a Select statmant but not return the result set? Thanks in advance, Bill ...more >>

All, Is there any way I could ask SQL to run a "Select statmant" but not return the result set? Thanks in advance, Bill ...more >>

Say my parameter, @whereClause, has the following value passed to it: 'accomodationTypes IN ('Villas','Deluxe')' This is throwing an error if I wrap it in single qoutes like I need to. How do you wrap something like that in single qoutes yet account for the string values 'Villas' and 'Deluxe...more >>

In SQL2000 the "Registered Owner" and "Registered Organization" were available as registry values, but the values don't exist in SQL2005. Is there a way to report these values in SQL2005 - either by registry read, or perhaps by reading a database table? Note: SQLDMO fails to return any valu...more >>

Hi there, I guess this is the right group to post this question.... I have a SQL Server DB. We import tons of files every day. The DB data file size grows everyday. Which is understandable. But the TRN Log file also grows, currently its at 13GB. I tried shrink (from EnterPrise Manger), but...more >>

How can I find the last instance of a string? I was thinking of writing a loop that goes through the string, but that requires writing a few lines of code. I was also thinking of inverting the string (in this case, charindex would work since). But I'm not sure how to invert the string w/o writ...more >>

I have a sql query listed below, I would like to count the number of values that the first 5 characters are '80438' from iNum field. Please help me correct the sql query listed below. Thank You, SELECT count(iNum) from Call_Movement where substring(cast(iNum as money) as char(20),1,5) ...more >>

Is there any way to enable ansi nulls on an existing table w/o droppping/creating? thanks jim ...more >>

Is there any way to force sql to allow a table delete even if it has dependancies? I just need to drop and recreate the table with ansi_nulls enables so the table be absent briefly. Thanks, Jim ...more >>

I know you can't put an if statement in a where clause but this is what the user is requesting. Sql 2000 isnull(cnfd_x_dt, (req_x_dt - 5)) x_fcty_dt if(day(x_fcty_dt) <= 10, month(x_fcty_dt)-1, month(x_fcty_dt)) I need to be able to seach by month. I have a computed column x_fcty_dt t...more >>

Hello All, I am looking for best practises, procedures, dos-don'ts, and tools for implementing version control on a database development project. Three items need to be tracked: 1) code (stored procedures and user-defined functions) 2) database schema 3) data (lookup-table data and test d...more >>

Hello, when I use xp_logevent 60000,'blah', informational I find not one but two records in the log: the message itself, and another one : Error: 60000, Severity: 10, State: 1 which I think technically is wrong: if it's "informational", then it's not "Error". Is there a way to ...more >>

SQL Server 2000 I'm having a lot of grief trying to concatenate additional text to a text column and would appreciate some help. Here's some sample statements: CREATE TABLE Test (s TEXT) INSERT INTO TEST(s) VALUES ('The cat') UPDATE TEST SET s = s + ' on the mat' The last line above ...more >>

I'm using an IDENTITY column (datatype = integer) for the first time and am not quite sure it's what I want. I'm deleting rows from its table quite often. I'd like a way so that when my stored procedure inserts rows I can first issue a separate command that tells the column to restart to a...more >>

I'm trying to insert data into TABLE2 from TABLE1. My problem is that the column names don't match and my code below gives an error listed below. Can someone help me with my syntax? CODE ************** INSERT INTO table2.columnA, table2.columnB SELECT table1.columnX, table1.columnY WHERE...more >>

We have tables that belong to distinct categories. Right now I am using naming convention to keep them grouped together. Like for example all tables that belong to Reference Data area are prefixed with RD_ and so on. Is this an old concept? Should I really be using schemas? If schemas are th...more >>

A couple of questions. 1) I have a column Event_End_Date with smalldatetime data type. I also have following update scripts that gets executed every day. Basically I do not care about the time portion of smalldatetime (or rather it needs to be default time format 00:00:00) UPDATE tblEve...more >>

i am using bcp "exec db1.dbo.sp1 queryout test.txt -Sserver -T -c" the stored procedure return multiple resultset. however, bcp just generate the first one. Anyway to get all result set? Can use other tools rather than bcp. DTS?...more >>

I have a table with one row and a unique constraint on two columns Truncate the table I see zero rows If i try to insert a row with previously used values in the constraint columns, i get an error saying i cant do it because i have voliated the unique constraint??? any ideas on why this ...more >>

I can retrieve column names, data types, etc. from Tables or Views from information.schema.columns, but columns of Table Valued UDF's are not listed there. How do I retrieve column information for UDF's? Thanks in advance...more >>

I have two tables, Stock and Positions. Stock contains a Symbol column and a Price column, and Symbol is the key. Positions contain the columns as well as several other columns whose data should change with the Price. Positions can have the same Symbol multiple times (it is keyed by Symbol-Acc...more >>

Hello Group I want import Text files to database using DTS, but I only want to import first several Rows because the file size is very big and the first row contains colomn names, I hope to check the field names first. Anyone help me ...more >>

Hi, Using SQL Server 2000 and I have a pivot table query that I'd now like to total the rows making a calculated column. I've done this before on other queries but can't seem to get it to work now. Any suggestions? Here's my query as it stands now: (if you see something that could be im...more >>

We have some code that was copied and modified. When it ran, the server began generating access violation errors relating to "sp_OAMethod" and had to be rebooted to clear up the problem because it was totally locked up. Could someone please check over this code and advise what needs to be cor...more >>

Howdy, I have an update statement that seems to be requesting a "Sch-M", (Schema Modification) lock and I can't figure out why! The table -does- have a trigger on it that I expect to be fired. To my suprise when I run a server trace on the update sql the first two lines are: "SET STATISTI...more >>

I have code that when I use this code in a stored procedure in queryanalyzer the execution time is 3 times slower than when I execute this as as select-statement in query analyzer. (same machine, same parameters). The execution plan is different. (I've alreade updated the statistics for the ...more >>

I want to check to see if the accountnumber exists prior to one being generated via another sp. Here is the below code: CREATE PROCEDURE [dbname].[Account_Insert] ( @accountid UNIQUEIDENTIFIER, @datecreated DATETIME, @datemodified DATETIME, @modifiedby UNIQUEIDENTIFIER, @accountnumb...more >>

hello..im doing some loops that make the variables with ", "..but some are with double commas..like this: @PREFIXO_NOME=1, @MORADA='rya', @LOCALIDADE='dsadad', , @SEXO='M', @OUTROS_DESCRICAO=0, @CODIGO_POSTAL='1234-223', @ESTADO_CIVIL=1, @AREAS_DESCRICAO=Null, @PAIS='Portugal', @NR_CTRB=Null, ...more >>

Hi, I like to write a function similar to sum(), does anyone have the code for it? what I like to do is write a function that concatenate a group of strings of the same field those having the same group as defined by a user and return a string as varchar datatype. Thanks, Benjamin ...more >>

Hi all, Having this table ID ServerTime User 1 2004-05-18 14:44:30.000 A 2 2004-05-18 14:44:31.000 A 3 2004-04-30 08:28:06.000 B 4 2004-04-30 08:29:19.000 B 5 2004-04-30 08:30:46.000 B 6 2004-04-30 08:30:47.000 B 7 2004-04-30 08:30...more >>

Hi, I've been using CLR sqltriggers in an application that requires some old c++ DLL's to be called using pinvoke. This itself has not caused any issues. And the majority of the implemention of the triggers are fine. The question I have, is debugging. Currently I am hunting down issues by ...more >>

Does it matter to SQL if the IN() function has duplicates? ...more >>

Hi I have a table that contains mainly bit type information eg ClientID Anxiety Depression Marital etc 23 1 0 1 24 0 1 0 25 1 1 1 I have succes...more >>

Hi All, I am having a problem with a group of results being returned by a query with a group by. Basically the data is being populated into a table (BulkLoad) from a legacy system in a predetermined order. When I run a query on that data (without any order bys) the data is comming out in ...more >>

Hi all I am writing a stored procedure which takes 6 parmaters. They are all optional parameters . the search should be based on the parameters selected. Basically the where clause would be based on the parameters. Is it better to go for dynamic query or use IF ELSE for this case. If ...more >>

I have 2 tables, tblMaster,tblReplica of the following structure: category product tblReplica is meant to be a close replica of tblMaster but I want to find rows in tblReplica that - with a category where the product is different from what could be found in tblMaster. Note that a produ...more >>

After Retriveing DateTime data from sql server 2005 through vb.net 2003,the value of miilSecond Part of the datetime is different from one shown in database. ex: when i use sql query in query analyzer ,the value returns 04:32:00.140 when same query executing through vb.net ,the value ret...more >>

Hi all, I am facing one problem while getting data from stored procedure into ..net application in dataset. I have two tables say table1 and table2 in which i am continuously adding data. When i am adding data to table1 same time i am adding data to table2. This whole process is done throug...more >>

Hello I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) I have a big problem getting the right result from the this example table... Age...more >>

Hi. I'm trying to create a dynamic audit trigger for a rather large datbase. the trigger should be able to dynamically read the field names form the deleted tabel and check each column for updates to store them to a table (i only want to store fieldnames and values that changed) the spilifi...more >>

I'm not to good in programming sql, what i want to do is select 3 columns from 4 possible, what i mean is that i have 2 columns in the DB that i need to select only one of them everytime(one of them is null, but i can't no each row which one) and i need the result (the one that is not null) to ...more >>

Hello everyone, I am wondering if anyone can shed any light on a DTS issue that I am facing. At my company we are using multiple DTS packages to extract data from a 3rd party database using their ODBC driver. Every so often a couple of the DTS packages fail - which can prove to be a real pain ...more >>

Can Sql Server 2005 and Sql Server 200 work side by side ( both server and client on the same machine)? Thank you. ...more >>

Hi, I am trying to call the SSRS webservice from within SQL. Wrote the original code on a client m/c as a command-line app and it works just fine. Change the code to be a CLR Function and get : Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Cultur...more >>

I was just about to post a query about this issue when I realised the problem. In short, my SP retrieves the last row from the StockMovements table: Select Top 1 @Variable=ColumnX From Table Order By ColumnX Desc I then do some stuff, including deleting this row. Then I want to get the ...more >>

I am attempting to pass a string variable from a form to an SQL statement and I get this error "Syntax error converting character string to smalldatetime data type" Your assistance is appreciated in advance..... Here is my code <% Dim TESTTHIS__MMColParam1 TESTTHIS__MMColParam1 = "0" ...more >>

Hi. We are experiencing significant prolonged blocking when loading multiple files in through a partitioned view. Before we start working on a resolution we want to identify exactly what resource is causing the block. We can see which processes are being blocked by using sp_who2. But how do w...more >>

I have a test server running SQL Server 2000 and want to put 2005 on it to = start to learn about the new features, but I don't want to hose my current = installation. Is there a way to install it on the same server without = overwritting or messing up any of the 2000 objects or routines? Can t...more >>

Can you export data from SQL Server 2000 as insert statements?...more >>

SQL2005: Updating some tables in a database works fine. Updating others in the same database fails with "Could not find server 'picasso' in sysservers". How come it goes for some and not others? "picasso" is our old server that doesn't exist anymore. It was holding a SQL-2000 that has ...more >>