I would appreciate good advice about the following Schema for a Table called Messages. The table will hold archived messages from Microsoft Outlook. The SQL 2005 schema that I have posted, is simply what the data looks like in it's *raw* form. I intend to modify the schema in the most efficien...more >>

Is there a method or procedure name that will let me know if a word is a sql server reserved word? i.e. sp_isReservedWord 'Null' --returns true sp_isReservedWord 'Count' --returns true sp_isReservedWord 'OrderNumber' --returns false ...more >>

Hi, I am developing small insurance application using VB.NET and SQL server 2000. My tables in SQL server are: tbl_Customer (stores the custmer information) tbl_CustImage (stores the customer picture and signature) tbl_InsuranceCompanies (stores the Insura...more >>

/* We have a requirement to run certain FSO (file system object) commands like file check, file copy, file delete, etc. It has to be implemented using SQL Server 2000 procedures. I have attached sample code. It works like a champ when the file path is local. However, it fails when network-mappe...more >>

Hi All, I have the following situation. Every month, I populate data from a source table. This table has a field called process_date (char data type) and the format is mmyy. So, 0406 means data for the month of April of 2006. This source table always overlaps with old data. For example, for...more >>

I need to write .sql file in a dynamic way. inside the file i need to decide at run time (of the sql script) whether or not to run a SQL statement that I don't know it's content while dynamically creating the sql script, this sql statement might include a GO statement what makes a problem put...more >>

I want to check a user table's size, Maybe 20M or more? what can i do ,pls kindly help me Regards Grant ...more >>

We're having to install new SQL servers on a new naming convention. We know the changes we have to make on the applications that hit the SQL boxes. However, we are not sure what DTS or SQL Agent jobs would need to be changed. Is there a way that we can search the DTS & SQL Agent jobs for ...more >>

Below in "ACCESS SQL CODE" is a query that I use in Access. It uses the IIf and IsNull functions to concatenate the full name. If there is a middle initial, my statement returns a comma after the LastName field, the middle initial followed by a period. If it doesn't exist, sql returns nothing....more >>

I am required to set up a cursor (which I hope to eventually replace with set-based processing) Here is the code without the variable: Declare cs_RT_Extract Cursor for SELECT Branch, ARRIVDATE FROM LocalServiceDataCheck WHERE WOTYPE in ('CA', 'CB', 'DC', 'EF') The variable @vTypeOut wi...more >>

Hello all, One of my clients using IIS/SQL2000 was infected by a SQL injection attack recently where the attacker used a transaction log backup to as the attack point. By exploiting a dynamic SQL hole in an ASP page (it wasn't code that I wrote....), they created a table that contained a...more >>

I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes th...more >>

I'm trying to use bcp to load a textfile of data into a db table. With my real data, I always get "Unexpected EOF encountered in BCP data-file". So I created a very simple test -- now I don't get the error, but I don't get any results, either. Am I missing something obvious? Here's the simp...more >>

Here is what I am trying to accomplish, and I would like to know if it is possible through the use of check constraints. Two tables Table_A and Table_B. When I insert new record in Table_A I would like to check for the existence of a record, through the use of a check constraint, in Table_B...more >>

When I have a table with two columns, can the second column default to a value based on the value from the first column on an inserted record? I read the section below in BOL ALTER TABLE but can't make head nor toes. E. Alter a table to add several columns with constraints ..... column...more >>

Is it possible to change the SQL Server default UDP port? If so, how do we do it and does it have any effect on how users connect to SQL Server afterwards. Thanks in advnace ...more >>

Hi All.. I have a vendor supplied application that writes to a very large table. I have identified a set of data that the vendors client software sends the database that we do not use, but adds 1 million rows of data each day. I've added an "After" trigger to delete this data as it gets inser...more >>

I am running the following script in Query Analizer: begin transaction xxxx with mark update logmarks set logmark=2 commit transaction xxxx go The script runs without error and the logmarks table is updated correctly, but when I check the logmarkhistory table to see if a row was inserted ...more >>

Does anyone know the limit? I was able to test more than 8000 ORs without any problem. However, at around 15,000, I got stack overflow error. AND seems to blow up much sooner. I am looking for an official word on this - what's the limit? Thanks in advance -a...more >>

Hi all, I hope I can make this understandable. I'm new to SQL and I'm trying to recreate some tables from an older program. I had to create new keys, since the old data didn't use keys in the same way and I'm having problems populating the new keys. I'll give you one abridged example and see ...more >>

One of those idiotic lawfirms: "If you have committed suicide, call ...." WTF?!? -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "Escriba coda ergo sum." -- vbSensei ...more >>

Hi, It seems that this issue is occurring at several client sites, though never in house. At some point, some (all?) queries run by our software start returning no rows. But the rows are there in the database - and restarting IIS (this is a web product) seems to resolve the issue. Even ...more >>

Can some one help me with writing a query to update a table with information from another table? I would also like to perform some calculation - Table One (Previous Month Data) Key Rate Date 322 4.5 08/31/05 378 5.5 08/31/05 399 6.5 08/31/05 Table Two Key Ra...more >>

Here's a stored procedure I'm working on: CREATE PROCEDURE spTestSize @iCustID AS INT, @bTooBig AS BIT OUTPUT AS DECLARE @iSize AS INT SET @iSize = (SELECT DATALENGTH(ItemPic) AS ItemPicSize FROM tblCusts WHERE CustID = @iCustID) insert into table1 (testsi...more >>

My DB has a table named Category to display product categories in multiple languages. The following SP inserts translation ID in the TranslateID & Translation tables and a CategoryID / CategoryTransID values in the the Category tbl. This should happen in a transactional manner. However, if an ...more >>

In an after insert/update trigger I have the following: IF UPDATE(CustName) BEGIN SET @iCustID = (SELECT CustID FROM INSERTED) ... END It compiles but when I run it, I get a message: "Invalide object name 'INSERTED'" But if I take the SET out of the IF like this, it works fin...more >>

I'ma having trouble calling a remote pl/sql proc from SQL server 2000 using a linked server provider=OraOLEDB.Oracle Here's the call: EXEC PS..TESTDTA.esp_F0101Z2_EDSP_set 'C', 'MCHAMBERS', '23577', '107399', 2000.0 This is the proc, straight forward... ...more >>

Basically what I am trying to do is to get all the names of all the database names that do not have a status of 32, 256, 512. Unfortunately I cannot seem to use the & bit select. Anyideas SELECT name, status INTO #databases FROM master..sysdatabases DELETE FROM #databases WHERE status & 32 SE...more >>

Hello SQL Champs, From the query below, I am using 2 TOP functions to return the desired row. I am wondering if someone can shed some light on how to AVOID using 2 TOP statements and combine into just one select query? select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Nu...more >>

The XML Datatype does not support the case-insensitive search makes the XML DataType (almost) useless, if I stored the xml in the XML column, I certainly want to search it, if I could not performance case-insensitive search, why do I need store the data in the XML column? I try the followin...more >>

I found sql2005 is very confusing in this area. (sp1 didn't improve anything in this area either) in sql2000, you set up an operator, email, pager, test them, then specify pager on duty schedules and types of system alert you wish to receive, and you are done! you can use it in the job notificat...more >>

I'm trying to run this query on a test db but it's updating 3 times the amount of data it should. Can someone help and tell me if my where clause is wrong? THANKS! Update table1 Set table1.YZBX01 = table2.YZBX01, table1.YZBX02 = table2.YZBX02, table1.YZBX03 = table2.YZBX03, table1.YZBX04...more >>

Hello Let's suppose we have a stored procedure (sp1) that accepts input parameters and returns output parameters and a result set (works fine when called from a client with NOCOUNT ON). Now is it possible to call sp1 from within a second stored procedure (sp2) and get this result set int...more >>

Is there a way to do a left outer join on three tables? I need to return information from three tables (actually views) and I'm running into trouble. I can do the join with any two of the tables, and I'm looking for a strategy for how to tie all three together. First I tried the following ...more >>

Hi, I Merged two SELECT query into one as following code. I found the query execution time was doubled. I think it should be faster than old code. Could anybody explain why? -------------Old code----------------- select Table1.*, .... into #tempTbl1 from Table1 where ...... Group By .....more >>

Please help me to improve the performance of the stored proc. It takes 6 mins to run. Is there any other way I could design it differently?? I have created following index but no effect- Please suggest me good index. create index [idx_service] on [dbo].[wintrack](TRK_PARENT,TRK_OPDTTM,TRK_CUSE...more >>

Hi, I have 3 tables, a person table, a timeRecords table, and a RegionPersonHistory table. The timeRecords table holds how many days were worked for a particualr date, and the RegionPersonHistory keeps track of the persons Region. People can be allocated to work on different regions so t...more >>

Mother Celko's Monday SQL Puzzle #2 I am gathering material for a second edition of SQL PUZZLES & ANSWERS. The easiest way to do this is to post a puzzle and harvest answers. The solvers get fame (15 seconds, not minutes), glory and their name in the book. The first edition did a lot of SQL-8...more >>

I am attempting to automate a monthend snapshot of my data, and the following code works when I run it from a query window in SQL 2005, but it generates and error message when I schedule it as a job. The error I get is... Msg 1023, Level 15, State 1, Line 10 Invalid parameter 1 specified...more >>

I have a column with data in the format of: 4 lbs 9 oz That I would like to convert to Grams I have been able to strip the data using CHARINDEX and SUBSTRING to create a lbs and oz column but can't figure out how to get the data into numeric format so that I can convert it to grams. Any i...more >>

Hi all, Is there any way to get the list of the tables used in the select statment ? I want to create an application in which when user fires any query i want to store tables used in that query in other table. for e.g if user execute -- select * from table mytablea join mytableb on id = id ...more >>

Hi All, I was working on the issue posted today by asween on deleting duplicate records and stumbled on this behaviour. Can anyone throw some light on this? First create a table and insert some rows create table #tdup (a int, b int) insert into #tdup select 1,1 union all selec...more >>

I have a T-SQL statement in a DTS lookup, which Enterprise Manager rather unhelpfully re-orders for me. I was wondering if anyone agrees that the way it's done it is correct or not? My original code is... select a.person_refno from tblPersons a join tblPersonStats b on a.person_refno = b.p...more >>

hello, i have two tables table TActions actionnr(int) / ActionName (varchar(50) table TCustomerRegistration custNr(int) actionNr(int) example TActions 1 Basket 2 Football 3 Rugby TCustomerRegistration 10 1 11 3 12 2 12 3 now i want for a customer to see...more >>

Dear all, Does anyone know how to define local policies in a workstation –either XP or 2000- in order to lock USB ports?? I already know that at the outset this request it no seems very related with SQL and so that kind of stuff but at the end of the day we are seeing developers are doin...more >>

Could someone have a look at this normalization problem for me? Specifally, what rule is being broken by the first normalization attempt? I know it's wrong, but why? /* DROP TABLE #farm_spreadsheet DROP TABLE #farm_animals DROP TABLE #farm_animals2 DROP TABLE #farms */ -- Origi...more >>

I have often encoutered this problem of having duplicate records in a temporary table. To delete this I generally create another table and transfer all the distinct records to the new table. I am using SQL Server 2000 Can anyone help me with any better way of doing that(Read without creating ...more >>

Dear fellows, I am looking for any Sql25k good book. I would rather that it’d available on-line because of I live in Spain and unfortunately here we don’t have books regarding leading edge technology. Focused on SSIS. Thanks for any thought or advine, ...more >>

Hi, Is it possible to use variables in the from clause in the declaration of a cursor? -- The declaration of the nested curser declare tables cursor for select user_name(o.uid) as TABLE_SCHEMA ,o.name as TABLE_NAME from @dbname.dbo.sysobjects o where o.xtype in ('U', 'V') and o.name no...more >>

I've compiled this function: <SqlFunction(Isdeterministic:=True, dataaccess:=DataAccessKind.None)> _ Public Shared Function udf_RegexReplace( _ ByVal input As SqlString, _ ByVal pattern As SqlString, _ ByVal replacement As SqlString) As SqlString If inp...more >>

Hi Group Other than the fact that PRIMARY keys seem to be defined at table creation time - what's the difference between a primary key and a UNIQUE index? If there is a difference, what's the typical use of a PRIMARY key? TIA Markus ...more >>

Like to hear SQL Server gurus' comment on this issue. Say I have a column with the unique index. Now I want to insert a row into the table. Should I first check whether the value exists in that column and insert OR should I just perform insert, knowing that the unique index will throw an ...more >>

Ok Joe, now I have your attention... What is the alternative to using IDENTITY ? Should I use MAX(IDColumn)+1 every time I perform an insert ? Thanks in advance and... be kind ! ...more >>

Sorry if this is a dumb question, Can a row with the IDENTITY property be of type bigint? Could it also be of type binary (i.e. binary(8) NOT NULL IDENTITY(1,1) ) ? TIA Markus...more >>

I'm trying to limit the size of images that can be inserted into an Image type column. The client side tools are not letting me do this so I thought I could do it in a trigger or constraint in the backend. So I discovered that I can't use Image datatypes in constraints. And also discovering that ...more >>

Hello! Can i remove IDENTITY property of the column by ALTER TABLE command? Thanks! Best regards, Konstantin Knyazev ...more >>

Hello, anyone can help??? below SQL works in sybase but fail in sql2000. sql2000 show error on the sum of netweight (cannot perform aggregate function) and group by commodity_code (invalid column name !!) >>>>>>>>>>>>> SELECT (select HTS from bur_inv_item_list where item = materi...more >>

HI all, I have a contraint on a table, the actual contriant does'nt matter for this question, however it is something like "(len(ltrim([perildesc])) <> 0)' which in conjucntion to not allowinh nulls, does not allow spaces (If there is a better way let me know) But what I want to know is, when t...more >>

I would like to have indexed view, but I need MIN and MAX functions: CREATE VIEW dbo.v_ordersP WITH SCHEMABINDING AS SELECT productID,min(quantity),max(quantity),count_big(*) FROM dbo.Orders GROUP BY productID I can't create it because it doesn't support min and max. Is there any other wa...more >>

The log file for database 'dbname' is full. Back up the transaction log for the database to free up some log space. How can i free up the space? ...more >>