Hello all In my VB6 programme, I have numerous functions that retrieve data from a Jet 4.0 database. As a some of the data retrieval is pretty much the same except for the table and the fields that I require, I have written several generic functions to get my data, similar to this: Publi...more >>

Hi, in the query first i am inserting a set of records into a table variable(@orderlist) and then deleting the records from the @orderlist for different conditions. this query takes 23 minutes to execute. please help to fine tune this. my query Declare @Startdate datetime set @startd...more >>

Hi! I have a table that contains the folowing fields: ProjectID ProjectAssociation ProjectDescription ProjectTotalMinutes Now an example of the table data could look like this. 1, 0, My Project #1, 0 2, 1, Subproject for myproject #1, 0 3, 2, Sub sub project for myproject #1, 0 N...more >>

Hi Guys, I was asked an interview question today: "Have you worked on SQL server in an enterprise environment" My answer was yes, but I don't know what it means. Is this about the editions of SQL server? I don't think it's that simple. TIA ...more >>

Hello, I changed machine name on my SQL Server 2005 and than I changed default instance name by executing the following statements: sp_dropserver <old_name> GO sp_addserver <new_name>, local GO After that I restarted all SQL related services and executed: select @@servername and the v...more >>

Is it possible to create my own global variable? I want to have a trigger not fire when running from a certain stored procedure but i can't use alter table disable trigger because the table is replicated. Any ideas around this?...more >>

Is there a way to convert a DATETIME value to ticks? I'm looking for something analogous to C#'s DateTime.Ticks property. Cheers, Jon ...more >>

I need to create a query where lines will become columns (pivot table) Does anybody know the easyest way to do that? It's a SQL Server 2000. Thanks!!! Carlos Felipe França da Fonseca ...more >>

[VS2005 Pro, SQL Server 2000, Win XP Pro] Hello, I have a task which requires me to know the last row updated/inserted. There is an identity column in the table which is used in further operations. When a row is inserted, I can use IDENT_CURRENT() to retrieve this value. However when a ro...more >>

Here's the goal. Our developers have been selecting from tables in stored procs without adding the with(nolock) hint. This is a no-no for our client as it causes contention issues. They have asked me to identify every occurence of the absence of a with(nolock) in any SP for select statements. I'...more >>

I am new to writing User Defined Functions in SQL Server (Ok theses are my first). I need to write two functions. One that will determine the Quarter Beginning and another that will determine the Quarter End dates. Here is what I wrote below. CREATE FUNCTION dbo.QuarterBeg (@when DATETI...more >>

I have a view that calculates the number of days between two dates. I want to remove the weekends that occur between those two dates. Any suggestions on how I would do that? Example: If it had been 10 days between the two days then really my view should show 8 days (10 days minus 2 weekend ...more >>

Max Row length in bytes is 8060. Through a third party software I found we have records exceeding the max data length. I further investigated and found its true. We have over 600 rows of data that exceed 8060 bytes. In some cases 5 to 6 times this long. Why aren't there any error messages...more >>

What's the difference between the following 2 queries especially the 1st line after declaring the variable @OrderCount int in the 2 queries (both the queries output the same resultset): -------------------- DECLARE @OrderCount int SET @OrderCount = (SELECT COUNT(OrderID) FROM Orders WH...more >>

I have a table with following data. Type Value a NULL a NULL b NULL b NULL b NULL c NULL c NULL c NULL c NULL c NULL I need to update Value column like an enumeration. for example there are ...more >>

Hi everyone, I'm new to this group I've tried a ruby and perl group but no one seems to understand how to do this. I have a pdf (coming in through a webapp, i can make a copy of it to disk) and I need to store it into a image column in sqlserver 2005. the file is too big for me to just conv...more >>

To all, I am new with XQuery and XPath so please forgive my ignorance. This is also an extension of a previous post called "XQuery and Hierarchical Data". I needed to post a new one because it was already closed. I am trying to produce a query that returns a table with a list of every des...more >>

Hi all, I have the following problem: I have two tables on a one-to-many relationship. I need to return all the rows from the first table and only one row for the second table. Declare @A table ( IdA int ,Name varchar(100) ) Declare @B table ( IdB int ,IdA ...more >>

Hello All, I use several local temp tables in an application. These tables store information that is built from some text files by the user. This data cannot be mixed up by another user logging in at the same time. Is there a chance that another user can run the app at the same time as another ...more >>

If my stored proc has three sql statements wrapped in if statements like this: if @thing = something <big 'ole sql query 1> else if @thing = somethingElse <big 'ole sql query 2> else <big 'ole sql query 3> Can sql server cache a reusable query plan for this sproc? If so, will...more >>

I have following cenario where I want copy columns from one table into 2 other tables. (source) tbl1 tbl1ID column1 (bit) column2 (date) where column1 is true and and column2 is not null (destination) tbl2 tbl2ID new_column2 (date) fk_tbl1ID tbl3 tbl3ID new_...more >>

Hi Guys, I have a revenue table for my application With followin template data Store ID Reservations Revenue R/N Month Year 1 200 20000 175 1 2006 1 239 37461 183 2 2006 1 235 37213 164 3 2006 1 10 282 15 4 2006 1 139 38471 157 5 2006 2 128 19284 121 1 2006 2 123 19283 193 2 2006 I need...more >>

Table GPS_Vehicle columns: siteID vehicleID firstName lastName datetimestamp lat long I would like to select only records with datetimestamp = max(datetimestamp) for each vehicleID I used the following query and it worked well. Only that I would like to get all other columns ( fir...more >>

Hello, all. I'm xfering data from a SQL 2000 table to an Oracle 10G database. The table in question contains 41 million records, and is a faily wide record. I've xfer'd this table from another SQL Server months ago, and it ran within an hour...very fast indeed. However, my DTS xfer to Orac...more >>

Hi, I am logging process data every 5 min, every record has a datetime field in addition to the process data one of the requierements of the application is to avg the data every 3 hours. is there any simple way to accomplish this with a query or will it be better create a job that runs every 3...more >>

hi, I want to debug a store procedure and need to pass a datetime value( 02/11/2001) to it, but it said: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification how to pass datetime value in? also, I found if I change regional options to English(Canada) instead of ...more >>

I am about to scream and was wondering if anyone could help me with my issue. I recently wrote some triggers to act as a changelog system on our production database. The problem I am running into is that we have some legacy code that passes dynamic sql into a stored procedure and uses @@identity...more >>

Hi, I have a database where the dbo user id is associated with a login name of 'Wizard'. How can i change this so that it is associated with sa login. 2)Suppose I try to create a UserId of Wizard in this database it gives me an error that the uSer alreay exists. But i dont see it under Users...more >>

Is it possible to put VBScript in a stored procedure and run it? If so how. Thanks....more >>

Hi, I'd like to sync data between two sql servers. I have the exact same tables on server A and server B. What I need is that whenever data is inserted in one table of server A that it gets inserted in the same table on server B in close-to realtime. I've read that launching a DTS from a tr...more >>

how can i get the number of seconds since midnight ?...more >>

Is it possible to have a conditional FROM clause in a stored proc? i.e DECLARE @dtDate DATETIME SET @dtDate = '30 oct 2005' SELECT item1, item2 FROM CASE @dtDate WHEN < '1 nov 2005' THEN tableA ELSE tableB END Basically, dependi...more >>

I am storing notes from a web application into a text data type. It is a running progress of paragraphs separated by "<br />" so that line breaks display on the web view. However, when I export that column to Excel it creates multiple rows. What can I store (and run REPLACE() against) as an...more >>

I am trying to return a recordset, and if there are no records matching the criteria I want to return a default recordset. I am using the @@rowcount, but it is not working. Am I using it incorrectly? Thanks ALTER PROCEDURE dbo.usp_ServiceType_DDL_Customer @intCustomerID as int = -1 A...more >>

I have a stored procedure that takes string parameter as input like below @CustomerCode = "'C1', 'C2','C3'" I am trying to use that in SELECT * FROM Customer where CustomerCode IN (@CustomerCode). The Above statement is not working. I know I can do it using dynamic SQL but I DONT WAN...more >>

Hi Friends I am using SQL server 2005 as a database in my project. Situation is like this: I have two tables namely Applicant and PersonalDetails. First we are inserting values in Applicant Table and after that in PersonalDetails. In Applicant Table, ApplicantID is primary key and also Applican...more >>

hello, I've a problem with one of my stored procedures why does sql server return no result when I preform [code]EXEC('SELECT * FROM trajecten WHERE rit_afd_nr = ''' + @bedr_afd_code + ''' AND rit_nr = ''' + @rit_nr + '''') [/code] but when I do this then I get a result [code] EXEC('S...more >>

Can anyone see any problems with this code? I'm updating a large table ( 10 million rows ) in a SQL2K database and am trying to break the update up. Thanks wBob DECLARE @rowcount INT -- Initialise SET @rowcount = -1 -- Limit rowcount so update occurs in batches SET ROWCOUNT 10000...more >>

A SQL Server 2005 DB Table named 'NETOrder' has the following 2 columns - OrderID (int, IDENTITY) & UserID (int). Assume that the table has the following 3 records: ------------------- OrderID UserID ------------------- 4 12 7 12 9 12 --------------...more >>

Hi All, I want to transfer data through BCP utility, evrything i've done this, when i test Txt file on my local machine it is working fine but when i run this on client machine( Staging server) it is giving this error: SQL State=s1000,Native error=0 Error=[Microsof][ODBC SQL Server...more >>

Hi All: I work in Data Warehousing domain and I am new to SQLServer. Can you guys suggest me which exams will be useful to me as a developer (not DBA) and the order in which I have to take them? Also, please provide me info on the study materials. Thanks in Advance, -Prasanna...more >>

There is a table "searchresult_companyorder_business_e" in my database and this table has a column "branch_name_all".I have created full text index for "searchresult_companyorder_business_e"."branch_name_all". I want to serach those records with 'car','cars'.But I failed,I can only search t...more >>

Hi All I need to maintain uniqueness on two of the columns (id, qty) in the table test1 which has the duplicate data already as shown below. CREATE TABLE dbo.test1( id int, qty int, nametext varchar(25) ); insert into dbo.test1 values (1,10,'YASH'); insert into dbo.test1 v...more >>

@Language nvarchar(10), @FunctionID uniqueidentifier, @Name nvarchar(50), @ShortName nvarchar(50), @Description nvarchar(150) Insert Into UsrFunctions (FunctionID, ShortName) Values (@FunctionID, @ShortName) CASE @Language WHEN 'ENU' THEN UPDATE UsrFunctions SET NameENU...more >>

Hi I'm working with sql 2000 and I want to know if it's possible to change a server collation. by right-clicking on the name of server and selecting properties in general tab I can find the current server collation. but how can I switch to the other type of it? any help would be thankf...more >>