Anyone know this answer? [New].[Configuration:] O/S: Windows XP Service Pack2 on a machine named MERCURY SQL Server Developer's Edition SP4 [Old].[Configuration] O/S: Windows 2000 on a machine called PIONEER SQL Server Developer's Edition SP4 [Problem].[ Description:] 1. Created 25 cle...more >>

I am passing in a datetime value into a extended stored procedure, and thought I could read it into a double. its failing and I am not sure why. ... BYTE bType; ULONG ulMaxLen; ULONG ulActualLen; BOOL isParamNull; int inputParamIndex = 1; srv_paraminfo(srvroc, input...more >>

Hi friends, I want to convert datetime that is stored as seconds to the actual datetime. Example Expiredate is stored as seconds since 1/1/1970 (ctime) Expiredate = 1200373200 convert this to actual datetime Please help me to solve this. It is very urgent. thanks vanitha ...more >>

Hi, Novice user here. I am developing a large number of stored procedures and user defined functions and I want to be able to export them everynight for back up. I know that I can user the Query Analyzer tool to export them to .sql files one at a time, but I am approaching about 100 proc...more >>

How do you check if a temp table exists? I usually do the following for tables and Views: if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME = 'EarningsDeductions') DROP Table EarningsDeductions if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NA...more >>

Hello all, question on a query How do I turn this into an update query? SELECT MTE.dbo.GL10110.YEAR1, MTE.dbo.GL10110.PERIODID, tblEdit.Acct, SUM(MTE.dbo.GL10110.DEBITAMT) - SUM(MTE.dbo.GL10110.CRDTAMNT) AS Net FROM tblEdit INNER JOIN ...more >>

Hi All I have table Tab1 with a column Col1 of data type VARCHAR. I want to replace every "Kop " string in that column with "HY". Is there an easy way to do this. Below is an example with column Col1 with 2 rows of data: Col1 -------------------------------------- J Dixion K Kop ...more >>

Hi, it's possible that all indexes will be forced to be UNIQUE INDEXES? No mather how I will define them? Regards, -- Javier...more >>

I have a remote Oracle server that has data the updates infrequently, maybe a few records out of 5000 that would be added, deleted or changed every week. However I need this data to replicate to the local MSSQL server occasionally, probably nightly, so the information is available for a web...more >>

Good afternoon all, I'm trying to write SQL that adds a descriptive column and groups on that column but I get an error saying my inserted column is invalid. Can anyone help? An example follows. Sales Table: Sales Type Amount A 5.00 A 6.00 B 2.00 SQL: SELECT (CASE WHEN Sales ...more >>

I am trying to combine a table from one DB with another table in a different DB...I am using SQL Server 2000...Any help would be apreciated...thanks -- The Saint MCP, MCDST...more >>

Hi, I have a table with column" - RowID - CustomerID - Date - Comments I would like to make a query which can return me the last row of each customer based on date (one row for one customer) - so many rows as many different customerID is ithe table. I habe beec trying different ways a...more >>

Hi, I am creating a view in the following way CREATE VIEW TableView AS SELECT OriginalTable.* FROM OriginalTable WHERE ChangedFlag is null UNION ALL SELECT TableWithChanges.* FROM TableWithChanges OriginalTable and TableWithChanges have identical schemas. The data is in Origi...more >>

I have a table called table1 that looks like this: CName, Calls,Lost ---------,-----,---- Test, 18 Test1, 3 Test2, 4 Test5, 23 Test6, 21 Test7, 8 Test8, 4 Test9, 2 Test10, 23 I need to set Lost to 1 (True) ONLY where Calls is the minimum. So in this case Lost would be equal to 1 f...more >>

I want to optimize a database table I have where the size of a field, let's say named MEMO, is typically about 1,000 characters but can be any size. The ideal field would be a Text field. The problem with using a Text field is that the data is stored on a seperate page, so I came up with a so...more >>

Hello, I am running Sql 2000 Dev and I have four databases: Load Prep Staging and Live I am loading a fair amount of data from files (~800 MB) into the Load db. I have a set of stored procedures that transfer the data from Load to the Prep database. I have a set of stored procedure...more >>

I am creating an invoicing structure in our database. Our system allows multiple companies to be setup within the one database. Each company should have seperate invoice number sequences. E.g. Company One's last invoice number was 10000 Company Two generates a new invoice. They should be...more >>

I have two tables: Document --- DocumentId DocumentTotalPages Page --- PageId DocumentId PageNumber I'd like to fill Document.DocumentTotalPages with the count of items in Page where the DocumentId matches. I've done this before, but I can't remember how I did it. Can anybody re...more >>

Is it possible to execute a store procedure via a link server to oracle. I tried these two options but none of them worked. select * from openquery(oracle_linked_svr,'exec oraschema.ora_proc_name') select * from openquery(oracle_linked_svr,'oraschema.ora_proc_name') -- Deepak Verma...more >>

Hi! I am trying to pass two variables @date and @days in the DATEADD function but getting syntax error message saying "Syntax error converting the varchar value 'select dateadd(day, ' to a column of data type int.". I think I am not using the correct syntax. Can you please help in correcting ...more >>

hi there to use the sql agent proxy account, there is an option to set: 'Only users with sysadmin privileges...' in the settings of the sql-agent in Enterprise Manager. Is there a way to do this in code? Since my application uses MSDE in most cases, not everyone can use Enterprise Manager for...more >>

THANK YOU SO MUCH! i dont know why what i was doing wasnt working, i was doing the same thing but putting the command to a string and executing the string. but regardless your way works. thank you!...more >>

noob. first post. hopefully this is in the right group. if there is a better way to handle this .. i'm all ears. what i am trying to do is duplicate the following code for a numeric field: @Param Select STATECODE where STAECODE like @Param so if @Param has "CO" i get CO, but i can a...more >>

hello I have a stored procedure that calculates the values of column data based on an input variable. I would like to rename the columns to the input variables so that i can directly graph the results of the table. Is this possible? for example, a column in the table #summary is called...more >>

Hello, I have a column of money datatype defined in a table (sql server 2000) and I would like to split it into the the Dollar Amount and Cents... I was hoping that there would be some function to do this, but haven't been able to find anything. I came up with a solution (see below), ...more >>

I have created a maintenance plan to do the backup for a set of databases in a server, I created the backup device first and checked to see that there is enough space on that drive to hold the backups and the tran log dumps. the job is failing the optimization is failing too. In the plan log i...more >>

Hey, I'm trying to figure out how to perform a SQL query such that it selects distinct rows based on specific select fields, BUT at the same time, return the entire contents of the rows? E.g. Select Distinct colA, colB, colF, colG from tablez ...more >>

Hi all, This is related to renaming of a file with sql code. I have a stroed procedure that deals with FTP on exchange server, rename the file and then send email to the proper group. Platforms: FTP server is on Windows 2003 Exchange Server with IIS 6.0., SQL Server 2000. The portion...more >>

Hi, I'd like to know if there is a way that I can generate a script with the INSERT INTO instruction of the content of the table. I know how to generate a script for the create table, drop the constraint, index, recreate the table, constraint... but I need to have the content of the table ...more >>

Is there a way in Transact-SQL to do a "runtime" parameter query within a MS Access adp frontend? This is easily done in a pure MS Access mdb using the square brackets, e.g. [ENTER Customer Number]. ...more >>

Here is the content of "tab1" from two database: tab1 (from DB1) | tab1 (from DB2) ----------------------|------------------------- col1 col2 ... | col1 col2 ... 1 A | 1 C 1 B | 1 D 1 C | 1 ...more >>

I am trying to shrink a database using EM, but I keep getting an error which starts off like this: Error 8909: Table erroe: Object ID 0, index ID 0, page ID (1:121320)... Any ideas what is going on? Thanks. Archer...more >>

Hey all, I'm trying to use the 'Bulk Insert' statement to load data from text file into tables. The text files are placed on different servers as per the environment (Dev, Test, Prod). I have many stored procs using these bulk insert stmts and I'm trying to avoid changing the static IP addresse...more >>

I want to show a list of farmers. There are two types of farmers. One type of farmer bought product by himself. The other type of farmer bought product with another farmer and this purchase is called a split. When I list the farmers who bought product, I want to list the split farmers who b...more >>

Hi , Can someone help me with the what is the isolation level used in financial companies when a data is to be inserted into the production. Is it necessary to state the isolation level other than the default isolation level. Any help regarding this wd be highly appreciated...more >>

Hi there folks, I downloaded from MSFT a SQL Server DataSizer tool. http://www.microsoft.com/downloads/details.aspx?FamilyId=564C5704-D4F5-4EE8-9F3C-CB429499D075&displaylang=en It asks to enter the total length of the fixed cols. My question is: Are int, numeric, etc. considered fixed lengt...more >>

I have an app that runs stored procedures to do basic updates, deletions, insertions, etc. of records. Usually these take less than 1 second. Occasionally (say 1 out of 500 or so) they will "lock up" and take up to 15 minutes. I know that using sp_who2 I can try to catch one in the act to ...more >>

Hi all, As there is a statement in SQL server to set the date format of the server (SET DATEFORMAT format), is there a way to get/reset the current date format of the server. Thanks, Swapna. ...more >>

Hi all, We were using MS Access as database and VB as frontend. Now we need to support the database SQL Server also. We have following code which works fine for MS Access. For i = 0 To rsRecordset.Fields.Count - 1 Set objField = rsRecordset.Fields(i) If (objFie...more >>

Hi, I have a SQL Server 2000 sproc that selects data into a temporary table. If certain conditions are met, the sproc then uses a join on the temporary table to update an other table. In the join for this update, the join column on the temporary table is named incorrectly. This code ...more >>

When creating cursors against any database on my server, I'm getting Wrapper Read errors. Typically, I'll create my cursors as FAST_FORWARD READ_ONLY but it doesn't help. To further confuse me, the errors come at different intervals, sometimes reading 15 records, sometimes 50. Is there some...more >>

Please have a quick read and add your opinions. I work in the insurance industry. In our datawarehouse there's a policy header table and a policy transaction header. The description of each policy number is in the form 'ABC/XYZ/1234567' My problem is the header table holds this value as a ...more >>

Dear folks, I'm pursuing for any snippet of code which shows me how do a sp by Visual Basic .Net o c#. I've got the last CTP for Sql Server 2005. Related links or external references will be welcomed. Thanks for your input and regards, Enric ...more >>

I'm using the UDF iter_intlist_to_table to break up a string @strPortals of the form 1,2,3 and populate an IN clause. .... WHERE (HitLog.PortalID IN (select number from iter_intlist_to_table(@strPortals)) ) AND .... Is there any clever I can include *all* rows if @strPortals is empty...more >>

I got the following error: ...more >>

i have a stored procedure that processes new incoming data. during this procedure i have many INSERT commands that may result with a duplicate key violation. this kind of error should not stop the process and the algorithm continue. when i run it manually ( EXEC SP1 ...) it behaves ok and co...more >>

Hi, How do i retrieve 2nd Largest Amount in a table using Query: Table: sales Sno. Name Amount 111 abc 10000 222 xyz 23000 333 mbn 100 444 iriri 50000 -DNK ...more >>

Folks, I have written some stored procedures wich take more than 30 minutes to run. How can I output some "progress information" (just some small lines of text) which tell me if the procedure is still running as it should do? If I use the normal "print" statement, the output only appears when...more >>

Hi, I am writing a application that require to read some DBCC Pages. But when I execute dbcc page command through ADO, I get a page in terms = of error collection. In that error collection each row of output is just a string. I need to interpret that string value. Is there some other way t...more >>

Hello Other than the following character:' are there ay other character that may cause a problem in SQL Server specifically invisible characters? Thank you, Shmuel Shulman SBS Technologies LTD (UK) ...more >>

Hi, I'd like to run the following command: ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255) but it falls over because the current [purpose] column is 'text' I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need...more >>

--SCRIPT : CREATE TABLE [t1] ( [id] [float] NULL , [charid] [varchar] (10) ) GO INSERT INTO [t1] VALUES(1.0 , null ) INSERT INTO [t1] VALUES(3.1099999999999999 , null ) INSERT INTO [t1] VALUES(2.1000000000000001 , null ) What is required that copying data from column [id] to colum...more >>

Hi, I have a statement similar to this... SELECT DATEDIFF(year, acc.acct_anuit_birth_dt, GETDATE()) AS 'age' FROM rpsacct_t acc The acc.acct_anuit_birth_dt field is a date field. The statement returns whole numbers based like 64 etc... What I would like it to do is to return the age to...more >>

Hi, How to concatenate a text data type value with another text data type value or varchar data type value. Regards Krishna ...more >>