Dear all, Anyone has ever used any editor so poweful as by open a file with 320 Mb? I would need in order to see one line which is failing in a DTS pump. I never has had issues before but this time I am using a editor called 'Textpad' which is not be able to open whole that text file. ...more >>

Hi, I have a table with only 1 colum. Is it possible to tansform it into a multi-colums tabel using a SQL SELECT query? Col ------- 1 2 3 4 5 Cols in new table -------------------- 1,2,3 4,5, Nul Any assistance is highly appreceiated Larry larry@widextech.com ...more >>

I have a VPN connection to my Sql Server at work. It works fine most of the time. I actually use my Windows 2000 Server as my client from home. What happens is that I will usually have 2 instances of Sql Server EM , Query Analyser and the Profiler running at one time. Normally, there is no...more >>

A table named tblDetails has the following columns: oid int NOT NULL pid int NOT NULL qty int NOT NULL Consider the following 2 stored procedures: SP1: ---------------------------------------- CREATE PROCEDURE spInsert @oid int, @pid int, @noid int=NULL, ...more >>

Please visit http://msdn.microsoft.com/library/en-us/dnsql2k/html/sql_refintegrity.asp?frame=true#sql_refintegrity_topic05 & click the link 'Implementing Cascading Operations Using Stored Procedures'. Please refer to the sub-topic titled 'Inserting a Row into the Primary Table'. There are 2 scri...more >>

I'm writing an employee timeclock application using VB 6 and storing the data in a SQL database. The application is running well so far, but a co-worker with more programming experience pointed out a password vulnerability that I need to address. When adding a new employee to the program, the ...more >>

SQL2K - I can't see what is wrong with the script below. Any suggestions? --Server: Msg 170, Level 15, State 1, Line 4 --Line 4: Incorrect syntax near '='. update _tcaindexes set indexseq = case indexid when (indexid % 2) = 0 THEN indexid-1 -- when indexid % 2 = 0 THEN indexid-1 ...more >>

I am working on being able to transfer data from one table in one database, (imantest_ben.mhgroup.custom2) to another table in another database, (LADOCS.TMPCLIMATVAL). I have a script but continually get an error. --Update the clientid's for the matters which we know exist in Worksite updat...more >>

I just came across an upsized Access DB that had 250 binary records in it. This swelled the size of the database to about 1.2 GB. I don't need the data, so I deleted the whole table. Then I closed EM and re-opened to find that the database is still the same size. How is it the same size? D...more >>

Here is a code snippet that I am using from Ken's sp_run_xml_proc. I want to modify it, such that when I invoke sp_run_xml_proc I want to pass a SP and a parameter. Something like EXEC sp_run_xml_proc 'get_xml_LAC', @parameter. This @parameter should be used as a parameter to get_xml_LAC st...more >>

Take the Pubs sample database. Look at the Sales table. Pick an arbitrary store. Is there any way to find the set of all stores that sold the same (exact) set of titles as the arbitrarily picked store? (It doesn't matter if each title was sold one or more times.) I see that store 6380 ...more >>

Hi, I am using the followng query: SELECT DISTINCT BRAND FROM ITEMS WHERE ACTIVE ='T' ORDER BY BRAND ASC In all the data displayed by this query, there is also a Null value and a Blank value. Is there any way, I can display the null value but not the blank value? -- pmud...more >>

We are looking at combining some systems that would allow our Sql Server update the other systems Sql Server system when certain events happens - names change for instance. Looking at using Web Services as one way of handling the Data movement between the 2 systems. But if they are both ...more >>

set nocount on go create table z_test_del ( i int,number int,typeid int) go insert z_test_del values (1,102,68) insert z_test_del values (1,102,69) insert z_test_del values (1,102,113) insert z_test_del values (56,110,113) insert z_test_del values (56,110,68) insert z_test_del values (...more >>

How in the world can this possibly be valid syntax? It apparently works, because QA is chewing on it now. Why not just simply say WHERE LEN(MiddleName) = 35 ? > SELECT * > FROM CMR WITH (nolock) > WHERE ({ fn LENGTH(MiddleName) } = 35) > > SELECT * > FROM ...more >>

I have a question regarding the Index Tuning Wizard. I've got a trace file that contains about 2 million rows of data - one full day's worth of database activity. All of our database activity is comprised of stored procedure calls - or in the eyes of SQL Profiler, it's all a bunch of sp_exe...more >>

Hello, I have the following query: Select #tWidra.Code, Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight, Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight From #tWidra Where TestCode = 20000 Group By Code, TestNo Having TestNo = Max(TestNo) Or...more >>

We're seeing some rounding errors in a financial application. We had chosen to use Decimal(38, 10) fields for both dollar amounts and allocation percentages. However, when we multiply the numbers together we loose significant precision, as in the following example: DECLARE @TNA_PERCENT A...more >>

Which one is faster and cost less for tables with a lot of rows: 1. update atable set acol = case when cond1 then 1 when cond2 then 2 ...... end 2. update atable set acol = 1 where cond1 update atable set acol = 2 where cond2 update atable set acol = 3 where cond3 ...... ...more >>

Hi All, I have a trigger on a table tracking changes to certain fields. However, when I do a batch update on that field, it looks like the trigger only gets fired once. However, I do want to track changes on every record that the update statement touches. So, is there anyway to make it work o...more >>

I am trying to get the following procedure to work and I am getting hung up on the @strClaim parameter, this could be either 1 or more claim numbers for one terminal number. I want to be able to get all the claim detail information for, say, terminal # 1222222abc that are in claims 521, 522,...more >>

sql2k sp3a I have a program named Unix2Dos that converts flat files from a Unix format to a Dos format. --http://www.bastet.com/ -- I made a Stored Procedure that can gather all the files in a specific folder and should then convert them all for me. ALTER procedure admin_ConvertUn...more >>

Hi, I'm wondering if anybody wants to take the challange since I'm out of ideas for this particular problem. Basically, I'm trying to build a query that selects certain customers with specific criteria. Here is the thing: I need to pull the customers who have purchased a certain product l...more >>

I'm need a query that takes the number from the identity column, then uses that for the next query....something like; Select IdentityNumber From TableName Where LastName = 'Somebody' (Then takes that number and does a between to get a range of rows based on that identity number) Selec...more >>

Hi Group, I'm using SQL 2000. I have 2 temporary tables ( let's call them A and B) and the following query : select (...fields...) from A inner join B on A.SetupIndex = B.SetupIndex where A.userid = 'john' and B.userid = 'john' There is also this option of writing the que...more >>

Through a stored procedure I want to insert twelve rows into a table with each value dependent on a single date value passed to the procedure. I've tried various combinations of SET and GO, but always lose the variable definition. What would be the structure of the statements for inserting the...more >>

Consider: DECLARE @v decimal(38, 16) SET @v = 7 SELECT 22 / @v Results in: ---------------------------------------- 3.14285714285714285714 (1 row(s) affected) Which is good, and as expected. but if a user tried to be dilligent, and cast their operand: SELECT CAST(22 ...more >>

Hi I'm getting some reads of 1.5 million in some of my queries. What is this number? I can't imagine it being efficient. Can you give me some advice (not "plant your corn early") based on what I said above? Thanks-In-Advance ...more >>

Hello, How can I limit the select statement to 1 single row ? How can I limit the select statement to specific no. of rows ? Thanks :) ...more >>

SQL 2k I got 3 tables Table 'Employee' with columns EmpID,Salary, JanHours, FebHours....DecHours Table 'Department' with columns DptID, DptName Table 'Assignment' with columns EmpID, DptID One employee can be in multiple department, I need to extract Department details like DptNa...more >>

Hi all, I have 2 tables, and I would like know if have way to block delete of 'DadosBancarios', for example: if I try delete of 'Estabelecimentos' is OK, becauseI have a trigger (tIOD_Estabelecimentos) that delete first of 'DadosBancarios' and then of 'DadosBancarios' What I want is, ...more >>

All, I have two functions where one calls the other: -- simplified example create function plus(@a as integer, @b as integer) returns integer begin return @a + @b end go create function t(@a as integer) returns integer begin return cr_user.plus(10, @a) end go Is there any ...more >>

I am trying to run a bulk insert in a SP using the filename as a variable: BULK INSERT #tblTemp From '+@FilePath+' WITH (ROWTERMINATOR = '\n') My procedure is not recognizing @Filepath as a variable file name. Is there a way to do this without resorting to full dynamic SQL? -- Larry...more >>

hi Freinds, SQL 2000 How can I insert the output of sp_helptext mytriggername into a table? Sp_helptext out put is a table ! I need to collect the rows into a custom table. Thanks in advance, Pat ...more >>

Hi Freinds, SQL 2000 I need to find out that if any of triggers on my database has a word "dup_order" in it is ther eany schema lime infromation_schema.column_name that I can use to scan all triggers and find out which one contains the word? Thanks in advance, Pat ...more >>

Hi all, Does anyone known where I can find a script to search all the tables or at least pass in table name to find all the single quote ' data in all the columns within the table(s). Thank you in advance. drop table test go create table test ( policy_id int NUL...more >>

I have inherited a couple of databases (large) where the original developer used the sp_ prefix for all the stored procedures. Is there any fairly easy way to rename all the procedures in bulk, instead of one by one? I'd like to just change them to usp_ , so at least SQL isn't looking in th...more >>

Hi All I have two columns. How can i get a result set with only the 'A' column and the 'T' column with counts more than 1? 'A' can have more than 2 entries in clomun 'K'. Below is my query: select A, count( K) as T from dbo.tableV group by A The result has to exlcude all those that a...more >>

I have to capture XML stream into a table and I am using Ken Henderson's SP sp_run_xml_proc for this task. When executing this proc, I am getting empty resultset back. Looking into that I found 'Object Access, Failure Audit, Event Id 560 ' error in my event log. Any Idea why this is happening?...more >>

Hello, I'd like to point out that, obviously, I'm not a Database Admin - but I have read a fair amount on the topic and examined several case studies similiar to this issue. Is there a recommended size limit for the size of a table in SQL Server 2000? We have a system that is gathering t...more >>

Is there a Stored Procedure that compares 2 records and give you the diffrences between them? Or do I have to manually compare each record? ...more >>

Hi, I am writing a UDF that returns a table variable. In the UDF, I have a cursor that I want to update. I am getting a syntax error on the UPDATE. Is there a reason I cannot do this is a user defined function? Thanks Steve...more >>

I'm not a real wizard with SQL but am working with it more lately. I've only recently experimented with JOINS, and am having a problem with the following. It says there's an issue "near the JOIN". Select HoldXref.HoldXrefId, HoldXref.FolderName, HoldXref.ImageRecId,HoldXref.HoldListId, Hold...more >>

Hello, I am starting to work with XML Explicit. I am having problems with the tags and the level they generate in. Can anyone please help me, for I have looked aroung and it seems that I am doing everything fine!!! I am attaching below an example of the query and the XML it generates. =...more >>

I need to store month and year in a table. I'm not concerned about date or time. Should I still use a date/time field? Should I use 2 separate char fields? Calendar table? Looking for best practices that would facilitate validating data input and querying? ...more >>

Hi i have a stored procedure that looks like the following ---------------------- CREATE PROCEDURE dbo.usp_AvailableStudents @grouping nvarchar(50) AS DECLARE @SQL varchar(4000) set @SQL = 'SELECT REGISTRATION_NUMBER, PERSON_CODE, BUILDING_CODE, ROOM_CODE, ROOM_START_DATE, ROOM_...more >>

I need to check if string could be converted to a int without throwing any errors. I need to do something like this DECLARE @s varchar(20) DECLARE @i int --if following is possible @i=CAST (@s as int) --then SELECT @1 --else SELECT 0 if string is not a number I really don't nee...more >>

I have two datetimes dt1, dt2. dt2 is always greater than dt1 I want to do: dt2-dt1 and the result has to be written in the hh:mm:ss format Could anybody help me?...more >>

Hi, I'm doing some work with the profiler and I'd like to get down to a little more detail. I'm interested in the CPU, Reads,W rites, and Duration numbers. Are these milli-seconds? Where can I get a nice and neat, short explanation of the sql profiler, it's not well documented in BOL. ...more >>

Hello, this is my first post in this group, and i understand that the etiquette is quite high, so please forgive me if I haven't provided enough information. So here goes: building a patient reminder system with MS access 2000 on backend. need correct syntax for following problems. Basi...more >>

Hello folks! I am running a query and I keep on getting a "timeout expired" error. I have Profiler running and I'm lookingat the Performance Monitor. Both Physical disk idle time and processor idle time are nearly at 100%. How can I get a timeout when the server is virtually idle? ...more >>

I am trying to write a Function that takes 4 columns in a row and adds them together and returns the value. I can do that but the hard part is that several rows need to be added together due to the fact that they are the same lot. It is more then one row because they are being pulled into this...more >>

I am not able to use 'case' in a view. how do you get select int_StudentNo, chr_Pass = Case When int_pass = 1 Then 'Pass' Else 'Fail' end from tb_StudentResult into a view -- Best Wishes & Regards Shibu P...more >>

Is there a way to parse a name like this: JONES JOHN A received from mainframe files using only T-SQL to obtain a last name, first, name, and middle initial (if it exists). I use the CharIndex function to search for the first space and extract the last name, but am having difficulty wit...more >>

Hi, I have created a SP with 5 to 6 output parameters. The values of the output parameter is from the same table but different condition. I want to retrieve the values in the single query. Eg table name : ExpertMessages id Type Prod Rev 7670 Online Y N ...more >>

I am in the process of translating Access 97 Databases into SQL, and working through many very complex interrelated queries which work fine in Access. On translating many queries all work fine apart from when I get to the top level query which effectively nests many level of queries. On trying...more >>

If we use cast or convert in where clauses, will SQL Server 2000 still treat that as a possible SARG (search argument) with the possibility of using indexes, or do we lose that ability? I'm thinking in particular of dates and numbers from text. Thanks. ...more >>

Hello, I need a good help (*.chm file is fine), for sql-server functions & bit more... Thanks :) ...more >>

Hello. How can I select the current date in a sql statement ? Need sample code, please. Thanks :) ...more >>

You know when you stare at something for long enough, you start to question if it's right or not? This afternoon I've completely tied myself up trying to work out the sequence in which parameters to a stored proc are evaluated, and could really use someone to point out the obvious. Basic...more >>

my view returns the following information... username, logon1,logoff1, logon2, logoff2 user1234 2005-05-25 08:10:00.000 2005-05-25 09:10:00.000 2005-05-25 08:20:00.000 2005-05-25 08:20:00.000 user1234 2005-05-25 08:20:00.000 2005-05-25 08:20:00.000 2005-05-15 08:10:00.000 2005-05-25 09:10...more >>

Hello, I have 6 SQL Server in different locations running same applications. In a table a column has a default contsraint, It works in 3 servers but the other 3 servers it does not work the column gets NULL value. Any Idea? Thanks in advance, Erdal, ...more >>

Hello, How can I select the maximum value between 2 values something like select max(value1, value2) (but max is not the function) Thanks :) ...more >>

How do I get a textual description of the data type of a SQL Server table column? (Returned to ASP preferably) ...more >>

Hi all, Let say that I have a table Customer CREATE TABLE [dbo].[Customer] ( [CustomerId] [int] NOT NULL , [CustomerName] [nvarchar] (50), [CustomerAge] [int] NOT NULL ) ON [PRIMARY] GO Let say I have an index on CustomerAge. If I have a view defined as: CREATE VIEW dbo.VIEWCu...more >>