Hi there, I have a SP that create some indexes on large tables. When the tables have a lot of data, I get this sql server error: "Could not allocate space for object 'Import_FDSTAT' in database 'AS400CAD' because the 'PRIMARY' filegroup is full." The database is configured in SIMPLE mode, w...more >>

Hi, I try compiling the xp_hello.dll sample using the Visual C++ Toolkit, as follows: cl xp_hello.c /I "C:\program files\microsoft sql server\80\tools\devtools\include" /LD /nologo /MT /W3 /GX /O2 /D "WIN32" /D "NDEBUG" /D "_WINDOWS" /D "_MBCS" /D "_USRDLL" /D "XP_HELLO_EXPORTS" /YX /FD /...more >>

Hi all, i have a dynamic query being executed within a SP. How do i manage if the lenght of the string is going to be greater than 8000 - the length is not predefined and depends on the no. of rows in a table. (I need to pass a list of empIds to the dynamic sql) Is there any ways to ha...more >>

I have used raiserror() in my Sql Query but its output is printing within the Query Analyzer and not showing Popup Message, any idea why ? I am using MSDE on Windows XP, is that could be the reason ? or I have to install some Windows Component ? Best Regards, Luqman ...more >>

Does 5 represent the total numer of digits (including the fractional portion) or only the number of places BEFORE the decimal point? Moreover does the number include the decimal point? Are there differences between the databases servers ? Peter ...more >>

Hi, I'm getting data from a table using cursors: FETCH FROM sqlCursor INTO @sqlSys, @sqlSeq, @sqlComment It works just fine, but what if the table contains e.g. 50 columns - do I have to decalre 50 variables for each column, ain't there a more managable way e.g. by using some array sort of...more >>

Hello, Any idea of how I use a wildcard on a bit type field? The usual % and _ don't seem to work. I'm trying to pass a value to a stored procedure which will in one case filter for only records with a bit value of one, but in the other case include all records. Any ideas? <puzzled> M...more >>

I have two tables, each containing the field DOC_ID. I need to create a query that finds the DOC_ID in TABLE1, looks for the same DOCID in TABLE2. If the number of records for a DOC_ID in each table = 1, then update FIELD2 = 1. If the number of records for a DOC_ID in each table = 2...more >>

Hi all, Have to write an ASP app against an database working with binary ID-columns. What is the smartest way to work with these fields? I'm converting the field by using substring and bigint ex. CONVERT(varchar(15), CONVERT(bigint, SUBSTRING(Customer.CustomerID, 1, 4))) + CONVERT(varchar(1...more >>

I am creating an application that we will be distributing to end-users. This application uses MSDE. I am working on a utilities application for this that will allow the user to back and restore the database to and from disk. What I want to do is get a list of drives, files, and folders like ...more >>

I am setting up a table where it will need to find a record based on an exact match of all ten columns, about 5 columns will contain distinct data, the other 5 will contain data with little variation. There is a higher probability that recently accessed data will be accesssed again, but the ...more >>

I have two tables in a one-to-many relationship. Table subm is the 'one' and table limit_id is the 'many'. Table subm is: subm_no col2 col3 127587 ABC XYZ Table layer_limits is: subm_no limit_id layer_limit 127587 CA 25 127587 EQ 5 127587 FL 10 How can I produce a resu...more >>

I am trying to find invalid dates in a table. This could be invalid characters in the date coloumn e.g( **/04/04)or invalid dates, e.g Nov 31 (11/31/2003) or Feb 30th (02/30/2003) e.g in the Apdoc table the duedate has invalid values and i want to find them. Could you please assist. Thank...more >>

Following is the DDL CREATE TABLE order_details ( userid int NOT NULL, ordernum int NOT NULL, orderdate datetime NOT NULL, shipdate datetime NOT NULL, PRIMARY KEY ( userid, ordernum ), UNIQUE ( userid, orderdate ) ) -- Sample data: INSERT INTO order_details VALUES( 1, 1, ...more >>

This is what i have understood of Non clustered index. Non Clustered contains mainly two columns. One is the value on which index is created and the other is a pointer to the row ( or if defined Clustered index key ). Let the table be of row size 1K. So in a page there will be 8 Rows. In the ...more >>

I am running a Vbscript within a DTS package and am getting the following error.... "ActiveX Scripting Encountered a Run Time Error during the execution of the script." The offending script appears to be... Dim filesys, testfile Set filesys = CreateObject("Scripting.FileSystemObject") ...more >>

I have two tables in SQL Server 7.0. I want to update/insert rows into table 2, based on data in table 1. I want to: 1). Loop through rows in table 1 2). If table1 row exists in table2, update non-primary index columns in table2 with table1 data 3). If table1 rows DOES NOT exist in tabl...more >>

We have merge replication system. In a table, there is a insert trigger. When a record is inserted from SQL analyzer, the trigger works fine. When a C# application insert a record, the trigger did not fire. I even can not trace the insert from the application with profile. Where I missed ...more >>

I have an identity field on a table automatically updated by SQL. If I remove records from the table and put them into an archive table, will the identity field ever reuse the same number in the original table? I do not expect the number of records in the original table to ever exceed the max...more >>

Im using enterprise Manager on SQL 2000 to create a new table that contains a text data type that will hold a small memo. Im trying to set the data length but it wont let me change it from a size of 16 characters. Is there a way to get around this using the design interface in enterprise mana...more >>

when i update my messages table i want to make something like this (only one row i update) when i update a row i want to take the two values of this row if trash value of the row=2 and outbox value of the row=1 then i want to delete this row i have typed this code .. is it true? if it is wr...more >>

The following query results in NULL being in the field [ordTrackingNumber] if NULL was there originally. What am I doing wrong? How do I correct it? TIA! UPDATE tblOrderHd SET ordTrackingNumber = [ordTrackingNumber] + ' ' + '1Z9555E50348656440' WHERE ordInvoiceNumber=1043081 ...more >>

I would like to track which users of my database are hitting the database with queries most frequently. What is the best way to go about doing this? Any help would be greatly apprceated! I'm using SQL SERVER 2K with Service Pack 3 installed. Thanks in advance. ...more >>

How can I include IF in Case statement? SELECT ......... cost=case when T1.type=3 then 1 WHEN T1.type=5 then if (T1.cost*T1.count)>0 1 else 0 end,......... ................ Thank you, Simon ...more >>

I am trying to put together a function that will read a directory: CREATE FUNCTION ufn_ReadFolder( ) RETURNS @return TABLE ( [FileName] char(20) ) AS BEGIN DECLARE @ShellCmd varchar (1024) SET @ShellCmd = 'dir ' + 'somepath /B' EXEC master..xp_cmdshell @ShellCmd ...more >>

I am working with a table that has a string field with a numeric value for height. When I examine the database records, the field shows blank. Sidenote: what command can I use to show the HEX/ANSI in the field? If a valid number greater than 0, I want to the record. I have tried using ...more >>

I hope someone here can help me with a simple problem. I am trying to build a table in SQL Server with populating every days numbers and also at the same time building a column with matching last years numbers e.g DATE ADMS PDAYS LYADMS ...more >>

"raj" wrote: > Hi Steve > I haven't had a an ORDER BY in the curser before. All this issues turned up > before having the ORDER BY statement. I'll try to explain (in brief) the > situation. Unfortunately I'm not in in a position to post any codding as a > company policy. > > * I got...more >>

Hi all, How to transfer data from excel to MS SQL server without using DTS in SQL Server??? Thanks nad Regards, A.Chellammal. ...more >>

Hello! I have a database on ms sql server 2000. Today a noticed that the logfile had grown enormously. The database data file is about 5 megs and the logfile is 3000 megs. I also noticed that I didn't have autoshrink on, so I turned it on. Now, how do truncate the file so that it will take ...more >>

Hi, I want to format an address field that is in a database as 123 Happy Street| |Montgomery|AL|36104 I want to break it out into address1, address2, etc without the pipes. I found some code that seems to fix my problem below pretty well, but about half the time it doesn't work. I get an e...more >>

Hi, can anybody help me to convert tinynt to varchar(2) and have hex presentation of tinyint? Thanx in advance Vladimir ...more >>

I have the following two queries. The first returns several hundred records while the second query does not. I thought these two methods returned the same results. What am I not understanding here? Thanks, Robert select * from fte_import where not exists (select * from drUser where ...more >>

We have a 8 way processor box, which is used as a data warehouse. When users query the warehouse, parallelism takes place, and all cpus' are used. However during the evening, the warehouse builds, and only a single CPU is used for this! What would be the cause of this, and how do I correct it? ...more >>

Hello, I have to create a trigger that have the following functionalities : CREATE TRIGGER t_demo ON tableName AFTER INSERT AS --for each inserted record in 'inserted' --I have to execute a stored procedure EXEC p_runMe @val1,@val2 GO How should I do this ? the SP use some ...more >>

I'm attempting to use DTS Import/Export wizard to copy data from one table column to another table column within the same database. So far I have..... select [Customer].[CompanyName] from [Customer] order by [Customer].[CompanyName] How can I modify this query string to include the sy...more >>

Hi All, I would like a SIMPLE way to retrieve just the "Current_Execution_Status" value from the following stored procedure into a local variable so that I may evaluate it and process accordingly. I've found some stuff on the web but the scripting is like a couple hundred lines... Does it...more >>

I'm trying to find a way to create a data dictionary for a group of statisticians to use. I would like this to automatically reflect the structure of the database without having to run any extra code. I would like it to look something like what you get when you run: select table_name, col...more >>

I have a project that imports data out of an Excel file using an OPENROWSET query. Now, without asking me, the client has changed the file format to fixed width and I have to rewrite my procedure. So, my question is, can OPENROWSET/Jet be used to query a fixed width delimited file so I don'...more >>

I'm hoping someone has a quick fix for this issue. I've been developing a number of stored procedures and have run into the following issue. Here is the code in question: insert into tblSubscriberProvince exec ('select ' + @SubID + ', ProvinceID from tblProvince where ProvinceID in (' + @P...more >>

Hi I am having some trouble with the accuracy levels when using the SUM and COUNT functions. Please refer to the query and datastructure below. I am trying to get on figure as a percentage of the other (see Perval1 column) to do this I am dividing a Sum by a count function and multiplying by...more >>

I need to change the next identity value for an existing identity column that is populated with data. I tried the following, but I get a syntax error. Can this be done or is my syntax incorrect? Thanks, Robert DDL alter table fte_import alter column id int identity (3000,1) ***...more >>

How do I change the following recordset into a comma separated list? Current recordset Letters A B C D E Desired recordset A,B,C,D,E...more >>

I use microsoft access 2002 and sqlserver 2000. I have created a view and in that view I select all fields of a table (SELECT dbo.table_name.* FROM dbo.table_name). If, afterwards, I add a field to the table, the added field is not shown in the view. If I save the view again, then the field ...more >>

I use microsoft access 2002 and sqlserver 2000. Is it not possible to use a stored procedure as source of a subform in a form (both are linked by a field)? Problem is: the records shown in the subform are not only those who are linked with the record in the form, but all records are shown. ...more >>

Does anyone out there have any suggestions on how error handling should be handled in UDF's with SQL calls? We're trying to setup some shop standards for this, and the books/online resources with UDF examples don't include error processing. Thanks. -- Joe Palm Senior Technical Develope...more >>

I've read BOL, but I can't seem to find a good explanation on what an OUTER JOIN is and when (in general) would it be used?...more >>

I set a database to single user mode, for restoration reasons. There are users constantly trying to access the database. I cant seem to connect to the database, because it is in single user mode? Does this mean another user is connected? how would I remove them? Thanks ...more >>

I have heard people say that a clustered index is the data. But my question is does that mean my data is actually stored twice somewhere? I think that must be true otherwise how else would it be so fast to read from a clustered index since if it is very narrow and the table its based on is w...more >>

Hi group, I am trying to perform a bcp against a table in SQL Server 2000 to later be applied against a table in a SQL Server 6.5 database. The date is pulled out of the bcp in the format of "2004-10-07 12:00". However, the bcp utility installed with SQL Server 6.5 does not like the date...more >>

I want to confirm my understanding of execution plan reusage. The execution plan for a stored procedure is created when the SP is first called and kept in the buffer. This execution plan is resused if the same stored procedure is called from the SAME CONNECTION. It would NOT resue the execution p...more >>

Hello, I've created a trigger (see below). Now I have a question about it : What if I do this : UPDATE DOSSIER SET TYPE_VERBLIJF=1 This affects more than 1 record. Wil the trigger fire for each record in my update ? or just once? the last ? first ? CREATE TRIGGER T_OPN4 ON DOSSIER AF...more >>

I have a stored proc which accepts a parameter. When I run the stored proc once, I get a result in 5sec. When I change the paramter and run the proc again, it takes 10min to get a result. When i recompile the stored proc, and give it the 2nd parameter (which took 10min to return), I get a result ...more >>

Any good website with tutorials on how to select from a SQL Server table and put the results in HTML generated in VBscript? ...more >>

I have slotID and productID in my table as primaryKey. Now users decided that slotID and productID can be entered more than once. They will calculate the values again after couple days. So, my PK is no longer valid. I need to create a new one. What is the best way? I can add insertDate and ...more >>

I have a user who imported a file twice, into a table with the only unique key being an auto number key field. My question is what is the best way to go through comparing 3 or 4 fields, and deleting one record if two records have the same Code, Technician, Date, and Time. Thanks in advance, J...more >>

Hello, I want to have a stored procedure select a bunch of data and turn the rows it accumulates into columns. For instance, based on the data I pass to the stored procedure, it might find 5 rows of data. In my application, these rows will represent columns of data, and rather than transform...more >>

Hi! How do I store a picture n the database? I want to keep employee pictures in the databse and not upload the picture to a folder on the webserver. I have read the Help in SQL books online and I understand nothing.. it's just a bunch of tet on SFilesizes and stuff... but how do I actually ...more >>

In my database, there are 500 tables. I need to generate a report regarding the following for the past 24 hours. 1/ what tables are being accessed? 2/ What has been done to the tables? - view - update - delete - add 4/ who is the user that use such table? 5/ w...more >>

Is there a way we can link a Access table in an existing SQL Server database. If yes, what are the steps?...more >>

Hello.. I have a table with approximately 88 million rows and I need to delete about half that amount. The table is very heavliy hit and I am unable to do a delete in the normal sense because it causes blocking. So right now I am using a cursor and deleting one row at a time.. but it is taki...more >>

I have a non-mission critical (very non critical) in which I sometimes need to replace a table with an updated one from an Access database. The updating is done in an existing VB app but the webpage is using SQL server. What I currently do is use Enterprise Manager to drop the table, and then us...more >>

Is there a way I can update two tables in one update statement? Both tables are linked by two keys. Thanks ...more >>

Tables: CEW_Search ID (int, Not Null) - Primary tp_PageUrlID (uniqueidentifier, Null) tp_AllUsersProperties (nvarchar(400), Null) LeafName (nvarchar(128), Null) Docs LeafName (nvarchar(128), Not Null) WebParts tp_PageUrlID (uniqueidentifier, Not Null) tp_AllUsersProperties (image, N...more >>

Hi there (sorry about being pest today) I have two tables ("JuliImport" and "LogInandOutTime") both contain the following fields: LDUserName (varchar 35) Ccode (varchar 15) - both of these are part of the PK on each table Ctitle (varchar 75) What I want to know is which records in "JuliImport...more >>

Hi Guys, When scheduling a DTS, why is it sometimes stored as: DTSRun /~Z0x41518BF33262755A0DF4AFA379D as opposed to: DTSRun /SServer /N"test - DTS Package 1" /E Thanks ...more >>

Hi Recently I came accross a query that eneded with the line : OPTION (MAXDOP 1) I have read BOL and think I understand what it does, but what is confusing me is actually why would anyone use this option? BOL doesn't go into much detail, or give any examples. A good example of where one ...more >>

I have 2 tables: ORDERS & CUSTOMERS I need to create a relationship between these 2 tables so that the CUSTOMERS table can act as a lookup table for a column in the ORDERS table named "CustName" which stores the customer name of each order. The primary key of ORDERS is "ID", which is of ...more >>

I have 2 tables: ORDERS & CUSTOMERS...more >>

I've created a stored procedure but can't find any record of it on my HD. Where are theyt stored? Thanks, Carolyn...more >>

This questions is related with other message: "Big question - Average of my account per month" In my database I have these 3 objects: 1. CREATE VIEW dbo.vwFiltro AS SELECT M_ID, M_Data, M_Saldo FROM dbo.Movimento WHERE (M_Data >= '20040701') AND (M_Data < '20040801') AND ...more >>

Hi All, Just a quick question if anybody knows what I am doing wrong, this never happened to me before with the IN / NOT IN clause Scenario: Two tables with IDs Table1 = 180000 rows Table2 = 50000 rows SQL Server 2000 8.00.760 (Win2K SP4) Problem: Want to find which IDs are not in th...more >>

Dear All, I have just created a function, but having trouble executing it, the line I am executing is ;- select dbo.ftnProcedureSearch ('tblMyTable', NULL) the error message is Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.ftnProcedureSearch'. Any pointers ? ...more >>

Hi there I have two scripts- Script a: Select LduserName, [varchar 35] RecNo [varchar 9 PK] from tblJuliImport Where Status1 = 1 Returns 319 row (which is correct) Script b: Select PlannedEnd, [smalldatetime] Reference [varchar 9 part of the PK] from tblLISImportSingle Where Plann...more >>

Query is as below SELECT Phone1, CustomerContacts.CustomerCode + ' ' + ContactName as PhoneName FROM CustomerContacts INNER JOIN CUSTOMERS ON CustomerContacts.CustomerCode = Customers.CustomerCode WHERE Phone1 <> '' AND ArchivedDate IS NULL AND Customers.Supported = 1 AND Customers.CustomerC...more >>