Here is the scenario. My company uses a table to construct what-if scenarios. After playing around with different values in the table, they may want to keep the final table or start over with the table from the point they start the sessions. These sessions can last for days or weeks. Is th...more >>

I have a stored procedure that I am using and feeding a parameter of 1, 2, or 3 1 = criteriaA is null and criteriaB is null 2 = criteriaA is not null and criteriaB is not null 3 = criteriaA is null or is not null criteriaB is null or is not null can I IF @param = 1 Begin select * from tm...more >>

how do you select distinct on an VARCHAR(10) column when the other columns in the same table are TEXT? ...more >>

Hi, Does these indexes get stored somewhere ? If yes where ? Which index is better Clustered or Nonclustered ? Why ? TYVM ...more >>

Hello, Could you please tell me what's wrong with this query and correct it? insert into tableA (col1, col2, col3, col4) select col1, col2 from (select col1, t1.col2 from tableB t1 join TableC T2 on t1.col1=t2.col1 where t1.col2 = 'value') a, col3 = '1', col4 = '2' When running this, I g...more >>

How to Identify whether a Dead Lock has occured. thanks ...more >>

Hi, Is there anyway I can drop multipile Columns in a stored Procedure I know i can do it like Alter Table TableName Drop Column Name1, Name2, Name3 but somehow I need to add If Else Statement so I might need... If xxx ALter table tablename drop column name If xxx alter table tablename dr...more >>

Hi, I was hoping that somebody might have some experience with rounding and financial statements. If this seems to be out of context for the board, could you please let me know what resources where I could look for the answer to my questions? I have a field that is a money type (four digit...more >>

in tsql is there a way split like method for strings? ...more >>

are there any sql commands like "select top .." but that let you select between a range for example selecti the 20th through 30th rows? ...more >>

Apologies for the long post, I'm trying to describe my problem as fully as I can: As part of a suite of SQL statements I am trying to write a data integrity validation statement[2] to check the consistency of a legacy unique ID[1]. I have managed to produce a working solution basued on a user ...more >>

I receive "Incorrect syntax near '@PdQty' when I try to run this in Query Analyzer. What am I doing wrong? Declare @PdQty as int set @PdQty = 6 Declare @Cursor CURSOR SCROLL FOR Select top @PdQty Period, DateTo From Period Order by Period desc...more >>

What is the table sysremote_columns use for? Someone asked me this question and I am stumped!...more >>

Table PhoneNumber: PhoneNumberKey INT IDENTITY... AreaCode NVARCHAR(10) LocalNumber NVARCHAR(20) Extension NVARCHAR(10) I have to JOIN this to another column that contains a whole composite phone number. I would prefer to parse the whole composite phone number in the other table, but I...more >>

Hello. I update our SQL QA server about four times a day and back it up before every update. There are three DBs that I have to backup. Each backup is given a specific name accoring to the 'Update Name and Number". This take a lot of time to go through Enterprise Manager every time. For one...more >>

Is there any way to run the following query on SQL Server 2000? If so, what method would provide the quickest results? Thanks. TRANSFORM Sum(CRData.PAmt) AS [The Value] SELECT CRData.PDate, CRData.StoreNo, CRData.Field3 FROM CRData WHERE (((CRData.Descr1)=1 Or (CRData.Descr1)=107 Or (CRData.D...more >>

How do I Do? I have a test database and a production database. I want to take a number of selected tables and update my test database with the existing data. It is inconvenient to do a restore database because I may have procedure or any other object that I am working on and that would de...more >>

Hi i am running a query using OSQL.exe inserting some german characters namely a "a" with Umlaut over the top. When i look at the SQL in notepad the character is correct, and when i run this query in query analyser then the char is inserted OK If I insert the character using OSQL then the ...more >>

What is the 4 part Naming Method or convention ...more >>

Hi, I created a new user but when that user is logged in he is unable to run an openquery against the linkserver. Do I need to give him admin privledges. He gets the error Server: Msg 7416, Level 16, State 2, Line 1 Access to the remote server is denied because no login-mapping exists. ...more >>

The following XML is supposed to be mapped and INSERTED into table Employees with empid autonumber primary key field. Query Analyzer says that three rows were affected, but all I get are new rows with NULL values? In addition to this issue, can I mix attribute and element mappings? Thanks, ...more >>

Hi, I'm trying to use the TOP function in a stored proc using dynamic SQL but am having some trouble. From what I've read, you should be able to use a parameter with TOP if you're using dynamic SQL but I can't get it to work. I'm using the Northwinds database with the following sp: ALTER ...more >>

I have a few questions on joins. 1. How do i join 3 tables. 2. Can i have different types of joins in the above. For eg 1st join will be inner join and the second join will be outer join. 3. Links to study Joins in details (except BOL i have gone through that.) thanks ...more >>

Here is the statement I'm trying to write select (select sum(field1) from table1) as SUMFIELD1, (select sum(field2) from table2) as SUMFIELD2, SUMFIELD1 - SUMFIELD2 as MYDIFFERENCE from dummytable I know I can't do "SUMFIELD1 - SUMFIELD2 as MYDIFFERENCE" because I just declared the n...more >>

I was looking at the indexes on a table in EM in the table design view with that tabbed indexes dialog, and it occurred to me that the clustered primary key was listed as a constraint, not an index, and the foreign keys are not listed on the index tab at all. As far as I know, the only thin...more >>

Hi All, Will the "Order By" on the Column Name will be Fatser if the Column on Which "Order By" is used is Indexed? Or the Index on the Column will not effect the Order By. Hint: The column is a Varchar Column. Thanks for any Suggestion. Prabhat ...more >>

I have a text file that I would like to import on a regular basis into my SQL Server using either Bulk Copy / BCP. The problem I have is that the number of fields in the text file varies per row. The maxiumum number of fields that a row will contain is 26. If I edit the file so that eac...more >>

We have a cluster supporting 10 DBs all over 20 GB. They all collect data and grow over time but at different rates. Until recently disk space was tight and we had autogrow on set to 10% and were were shrinking the db files regularly. We were unaware however this was fragmenting the hec...more >>

I have a database of customer accounts and related services from which I need to export updated data to a stand alone system using .csv files. I'm sure DTS can do the job, but I could use some help on a column of data in the export that uses bit-array logic to relate services to the account. He...more >>

Hi I have createed a import dts package by using the all task import then i supplied a SQL query near the end of the wizard it asks if i want to create a DTS package. the sql query is quite complex. is there a way that i could view the SQL that this DTS package is using thank you so much!...more >>

Is the following a true statement: The BIT data type in SQL is the same as the Yes/No data type in MS Access? Thanks....more >>

WHERE dbo.IsDoubleByte(ssa.FirstName + ssa.FullName) = 1 This doesn't give me performance issues, but I was just curious, is there any way to apply indexing to make this more efficient? -- Peace & happy computing, Mike Labosh, MCSD Feed the children! Save the whales! Free the mal...more >>

I have two tables: 1) temp_pvs (100 records) 2) temp_pvs2 (90 records) I need to find the 10 records that are in temp_pvs but not in temp_pvs2 I tried: select * from temp_pvs p where p.claim_id not in( select * from temp_pvs2) Can someone rewrite this for me?...more >>

I have SQL Server 2000 Version 8.00.194 and Outlook 2003 SP1. I have a mail profile named Exchange set up that uses an Exchange server. I can send and recieve email in Outlook. Outlook is the default mail client and the Exchange profile is the default (and only) profile. In the SQL Serve...more >>

I have the following stored procedure SELECT CON_ID, CLI_ID, CON_LastName FROM dbo.Contacts WHERE (CON_ID = CASE WHEN @CON_ID = 0 THEN CON_ID ELSE @CON_ID END) AND (CLI_ID = CASE WHEN @CLI_ID = 0 THEN CLI_ID ELSE @CLI_ID END OR C...more >>

Quite a debate going. If isolation level is set to read repeatable, I realize that all select statements are going to use the default locking for that level ( which will prevent updates ) Developer feels that a transaction should be places around two select statements to avoid another tra...more >>

Hello Guys, I´ve been facing the issue of building a translating logic between the codes of two different databases so for every code in one database I´d get the equivalent code from the other. I´ve got two options for developing that: Stored Procedures and User Function. I wonder which way i...more >>

Dear Experts I am running following Query from SQL 2000 UPDATE [14.15.93.25].[CORPDB].[DBO].[ItemMaster] SET ItMas_IsReffered = 1 FROM [14.15.93.25].[CORPDB].[DBO].[ItemMaster] INNER JOIN ItemMasLocal ON ItMas_Id = IMM_MasId Go While Executing It is giving me following error...more >>

I have a requirement of allowing the user to filter records based on various criteria that can span multiple tables. The user should be able to give the query a name and save it so that the next time they pull it up the same resultset is displayed(unless there are changes in the data). What wou...more >>

I've tried posting to the JMail forums over at dimac and got no response, so I'm turning to the experts to here to see why my ActiveX scheduled job step keeps failing and killing the whole job. I'm using JMail 4.4 in an ActiveX scheduled Job in SQL Server MSDE 2000. When I use the msg.Send fun...more >>

Hi, Please come someone explain how I can page through large sql server tables (e.g. ~3,000,000 rows) as efficiently/quickly as possible. Although I may not have to do this all the time (i.e. I may only be working with a subset of data most of the time) there will be cases where I will have t...more >>

My network guys want to upgrade our raid controllers on one of our production boxes because it is just getting slammed. Can anyone tell me what upgrading will do for our performance? I would think we would rather invest in more memory or processors. Thanks....more >>

Hello, all. We have a few tables that will hold general person information. The main table, tblGeneralPerson, holds the person's name and a URL link to his/her photo. Now we're trying to design the table for the person's address; he/she can have any number of addresses in the system -- home, wor...more >>

can anyone provide me any links on how to generate Hirerarchy query.... Thanks Ed...more >>

We use a lot of comma-delimited lists of integers to represent sets of rows and IDs in SQL Server, since we can't use arrays, etc. for this. The problem is that it can be a pain to select a set of these values from several rows and then concatenate them into a single value. I've found an eleg...more >>

I'm working on a stored procedure that will have double duty. Basically, it will return a SELECT of the requested data, plus I wanted to have an OUTPUT parameter that would include a string of the same data. For example, here's a proc similar to the one I have (but simplified for the sake of p...more >>

I have a web page that opens an SQL view and prints the TOP 10 records using the following SQL: SELECT TOP 10 * FROM dbo.vw_ActivityView WHERE entity_seq_nbr=2896 The users want to be able to get 10 records at a time so that a "next" request would get 11-20 and if on 11-20 a "previous" reque...more >>

I have a stored procedure that selects from tables in multiple databases on the same server. The stored procedure is cataloged in one of the databases. For example: The stored procedure TESTA is cataloged in cede_70 by cede_70_dbo who is the dbo of both databases create procedure dbo...more >>

Hi all. I have some identical databases that I want to insert the same information to. I simply want to do an insert into a table called users into both databases. This is a third party application so I am interested in running this query on multiple databases......about 100 of them....more >>

I have a query where the result set does not have a column that uniquely identifies a row. Is it possible to have this query return something like an identity column for this result set without having to use a temp table? If I use the identity(int,1,1) function, I need to use INTO <#tmpTable>. ...more >>

I have a table that contains keys, names & date_changed values. When the date_changed date value is null it is the current name, otherwise names table key PK not null name varchar(255) not null date_changed date null what i am trying to do is return the name, the current name if date_chan...more >>

Hi there, I have a table with a field "StartDate" which is smalldatetime. When I run "Select CONVERT(CHAR(8),StartDate,3) from LISImportSingle" it returns the data in the format I want (dd/mm/yy). What I want to do now is change the format of LISImportSingle.StartDate to dd/mm/yy and can't f...more >>

I am debugging a program that uses MFC to access a SQL database. The program uses the CRecordclass::Open() function to open a recordset based on a query. The queries run fine except when I include a JOIN statement in my query to join the elements of different tables. The query runs fine in ...more >>

Hi there ...more >>

Hai all, I need a help, If i delete a table by giving "delete from table1" then is there any possibility to retrieve it from the ldf file? Also, the delete statement was not under a transaction thanks, V.Boomessh...more >>

I need to pass into a stored procedure a variable number of IDs at a time that will then return the records for those IDs. Sounds Easy! I therefore need to use these IDs in an IN operator. Select * From MyTable Where MyTable.ID IN({IDs}) Now. I cannot pass and array (or comma seperated lis...more >>

How can I change the query listed below to run faster? Thanks, Paul G. select max(KrcssLgBgn) from DetailLog (nolock) where KrcssLgPrcssID = 492 and KrcssLgStts = 'C' and KrcssLgBgn < ( select max(KrcssLgBgn) from DetailLog (nolock) where KrcssLgPrcssID = 493 and KrcssL...more >>

I have created a trigger AFTER INSERT BEGIN INSERT INTO Log (@@IDENTITY) EXEC mySP param1, param2 END Now the problem is that the EXEC does not work and yep, the whole stuff is rolled back. If I run it from the query analyser ( as sa) it works fine. What could be wrong...more >>

Hello All can the 1 procedure return multiple result sets? if it so how the application will react...more >>

Hello, Can anyone help me on this one. Since i read you cannot use record changing formulas in een User Defined Formula. I can use a stored proc the get the data. But now i cannot find how to use an proc in an UDF. The normal exec method does not work. ...more >>

Hi, I have a merge publication and a subscriber has already subscribed to that. How can I start the merge agent using SQL-DMO? Is it possible to receive the success or failure result by something like an event to notify the user of my app? Any help would be greatly appreciated. Thanks, Leila ...more >>

Hi, we use heavily openxml to insert data into our sqlserver 2000 sp3 tables. We have some performance issues that seems to be caused by our use of OPENXML. Doing a trace and wait analisys (1) we found that much of the time is taken by oledb operation. Looking at the trace file we saw a remote...more >>

I found that the encryption value e.g. select encrypt ('000') is different between Sql7.0 and SQL2000, and that create a data retrieval problem after SQL upgrade because some data are encrypted before they saved in database. Can someone please help....more >>