i have an interesting problem when i use an insert command in a stored procedure to insert data to a temp table, sql server rounds the value even it is not decimal for example, my procedure creates a value of 15499999998 into a variable called @ASum when i debug the procedure, the value does not...more >>

Hi there, I've got a stored procedure that needs to order by a column that is passed to it. I also want it to order the data by parameter. Without dynamically building the SQL statement, is there any way to put a parameter in for the order by ASC/DESC? I need something like: SELECT * FRO...more >>

Hi, I'm an ASP.NET developer and i'm currently using MSDE2000. I'm working on a Win2003 OS and I'm having trouble accessing the sql server using "Integrated Security=SSPI;". It comes up with the error, "Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.". My question is, what is the SQL co...more >>

Hello, I have: Declare @ErrorHolder int Declare @Rowcount Set @ErrorHolder = @@Error set @Rowcount = @@rowcount Why do I get @Rowcount =1 here if I comment out 'Set @ErrorHolder = @@Error' then I get 0 ( zero ) What does Set @ErrorHolder = @@Error effect @@rowcount at all...more >>

Hi, Im running a DTS package that works nicely from design view, but when running as a job it fails for some reason (it fails right away, the same second as the job is launched)... This is the first task in the Job: '********************************************************************** ' V...more >>

Hi. I need to import a sheet from an Excel file into a table in a database in a MS SQL server. the text in some cells in the Excel sheet in broken into a few lines, when using the import data wizard the data is copied into the table, but the line break vanishes and words are stuck together,...more >>

Hello all, I'm working on a small programm (VB) which converts data between two databases, both on a sql server. Everything works fine but it goes very..very slow I have to convert 9500 records and about 600 record are handled in one minute. This means it takes 16 minutes to convert all 9500...more >>

Here is my situation: I have a "master" table that has the following PK Visit decimal(5,2) Test integer I have a "detail" table with this PK Subject integer Visit decimal(5,2) Test integer I cannot have enforced referential integrity between these tables, but...more >>

Hi, I have a table called EXTRA_TYPE which has TYPE_ID DESCRIPTION column. There are four rows in there currently: 1|VIDEO, 2|PHOTO, 3|WALKWAY, 4|BOUTIQUE. I have a related table which is called EXTRA_PRICE, which sets up the prices for these types, this table has PRICE_ID TYPE_ID (...more >>

I'm using fast forward cursors in my stored procs. They are used only by these stored procs, so I'd like to use local cursors. Do have they the same level of (in)efficiency as global ones? Maybe locale cursors are slower? I couldn't find any information about that. With global cursors it hap...more >>

Hi, I have a table Parties: ID Title Name 107202 President FRANCES FRALEY 107202 Vice President FRANCES FRALEY 107202 Treasurer FRANCES FRALEY 107202 Secretary FRANCES FRALEY 4 rows...more >>

Using SQL Server Enterprise Manager I can change field type, press button Save change script and get script for change this column. My question is: HOW Enterprise Manager get this script ? Used DLL call / stored procedure or something else? Is any way to get the same script in my code (VB) ?...more >>

I'm not sure if this is possible or not but I've been unsuccessful so far. I have a table with several thousand URLs along with their title. Some are from the same domain and others are not. What I would like to do is limit the query to returning a maximum of two from the same domain, instead o...more >>

I run a query having a date constant in the Where part. The date is given in the form 'yyyy-mm-dd hh:nn:ss' which I believe is the correct way to pass dates irrespective of any regional settings. My problem is that in a certain SQL Server installation I get the message "[Microsoft][ODBC SQL Serve...more >>

How do I format a date in a query so that it will return in the following format ddmmyy eg 05/01/2004 as 050104 Thankyou Paul ...more >>

Hi, When I install MS SQL 2000 Desktop Engine on a win2K computer, I got an error message: "The instance name specified is invalid." and then cannot proceed. Why ? Please advise how to solve the problem. Regards, Derek ...more >>

If a stored procedure or a trigger is created encrypted in the database, shall I expect slower execution when called? ...more >>

I want to test for whether an Excel spreadsheet is already linked before I link it. The only proc I can find is sp_helpserver but I don't know how to retrieve the result set in T-SQL to check whether the server I'm looking for is in the list. How's it done?...more >>

Hello When I try to invoke a udf function I get an error('fnX' is not a recognized function name). If I qualify with owner name, ie. dbo.fnX(), it works fine Is it necessary to qualify function names during invocation Thanks in advance Alwin S....more >>

I wonder if braces ('{' & '}') are preserved keywords in SQL? -- Best regards, Laser Lu ...more >>

Does everybody else use char a lot? I very seldom use char and would much prefer it to default to something else. Is there a setting I can change? Bob ...more >>

Hi all, I feel like an idiot asking this question, but what is the best way to ensure relational integrity when inserting into 2 tables (parent-child relationship) from a webpage. I am needing to do this for SQL Server 7 which does not support Scope_Identity(). How do I ensure that I can retri...more >>

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'trimTime') DROP FUNCTION trimTime GO CREATE FUNCTION trimTime (@d DATETIME) RETURNS DATETIME AS BEGIN /* We could simply do this... SELECT @myDate = CAST(CONVERT(CHAR(10), @d, 102) AS DATETIME); However Tod H Sals sug...more >>

Is there any Object-Oriented Database Management System available by now? -- Best regards, Laser Lu ...more >>

I have a project which requires creating and deleting thousands of temporary records. For example deleting 600 thousand records take about a minute. It's ok but the biggest problem is that transaction log grows at unbelievable rate. I do not need those operations to be transactional. I can no...more >>

HI: I have an updatable view in Enterprise Manager. I create a simple ADP project and the form based on the view is not updatable. Is asking for a unique table. No problem. Provided one in a rush. Now only those fields are updatable on the form. Any help. Moving from Access 2000 to SQL ...more >>

I'm trying to execute a stored procedure in query analyzer and pass null values for some of the parameters. What is the syntax for this? ...more >>

Hi, I would like to calculate the exact amount of months between two dates. I know there is the DATEDIFF function, but this returns the number of month bundaries crossed from the start date to the end date. i.e. if i do DATEDIFF( m, '2003-11-29', '2003-12-01' ) it will return 1 month when i...more >>

Could someone help me with a query? I have a table with the following fields: 1. Timestamp, datetime 2. ItemName, varchar 3. Value, float Where the primary key is Timestamp and ItemName. EX: 1/1/2004 12:00:00, 'ITEM_A', 10.0 1/1/2004 12:00:00, 'ITEM_B', 15.0 1/1/2004 12:00:20, 'ITEM...more >>

Hi, In Visual Studio.NET there is a PPmt function in the VisualBasic Namespace I would like to use the PPmt function in SQL. Is this possible? Thanks! ...more >>

Does any one know how to write a query to get the following information: THANKS CAN I GET A COUNT ON THE CALENDAR. FOR INSTANCE IF THE EMPOYEE IS ON THEIR THIRD DAY OF BEING OFF IT WOULD SAY 3/6 AND THE NEXT DAY WOULD SAY 4/6. Thanks in advance for any help Stephanie ...more >>

Has anyone seen the following statement used in a SQL query before? 0 IS NOT NULL ? It is being used like so SELECT Something FROM Table t WHERE 0 IS NOT NULL OR ( SELECT Something Else From Table t2 ...more >>

Hi all, I have a table named ROUTE. In the table there are four columns: SNAME, TName, FOOTAGE & TYPE. TYPE is a text field and can have 1 to 13 different codes. What I have been tiring to do is sum everything in footage that = Sname, TNAME, and type. My problem is I need to find two differ...more >>

Receiving an error message trying to process email in my InFolder. Using Outlook 2002 SQL 2000 SP3 [Microsoft][ODBC SQL Server Driver][SQL Server]xp_readmail: failed with mail error 0x8004010f Best I have found was this is suppose to be fixed with SP1 any ideas anyone? Here is the code: ...more >>

Hi, I am useing SQL 2000 sp 3a. The below update trigger on a table do not fire when a record is updated. Any Clue [ CREATE TRIGGER Update_Trans ON [tbl_Transaction] FOR UPDATE AS declare @ID int select @ID =tr.AttendID from [tbl_Transaction] as tr DELETE FROM tbl_Attend WH...more >>

I need to write a procedure that will disable all the triggers in the database. And then i need to write another procedure to re-enable them all. Any suggestions? Thanks. ...more >>

I have a table TEMPTABLE (companynumber, seqnum, text) Contains COMPANYNUMBER SEQNUM TEXT NEWSEQNUM 1 10 HELLO 1 1 20 ...more >>

I am pulling my hair out at what I consider to be a fairly simple problem. I have three tables. Leads, click_count and advertisers. I would like to query both the Leads table to figure out how many leads we have received by AID. The advertisers table contains the Description and AID. I would als...more >>

Hi, I have a database with lots of foreign keys. I need to refresh data from production to our dev system. These are the steps i need to perform a) Truncate table b) Load data using a DTS package But when I try to truncate table, it complains about foreign keys. Delete is not an optio...more >>

I am looking for a way to pull out integers from my data. These integers contain 01 or 01 at the beginning of the intger and our 5 digits long. (i.e. 010999). What can I use to pull out the numbers with 01 and 02. Can I use LEN and assign length to a variable. Thanks, -ben m ...more >>

I have now been working on this for about 1 hour, figuring out how to put the quotes in my stored procedure to create a sql statement to call xp_sendmail with. I think I have tried everything, but if any one has any other ideas please let me know!! The problem is with @CheckGroupQuery, every thin...more >>

Hi, I've a bunch of SQL stmts. declare @dbname char(12), @exchrate float, @sq_code char(3) I would like to dynamically generate and execute this sql stmt. /*update @dbname.dbo.curr set exchrate=@exchrate where code=@sq_code */ I tried EXEC ('UPDATE ' +@DbName+ '....more >>

Hello all: Is there a way to find out the last time a stored precedure gets editted? Just the way the "Create Date" shows up in Enterprise Manager. thanks for your anticipated response. Tunji ...more >>

Hello all: Is there a way to find out the last time a stored precedure gets editted? Just the way the "Create Date" shows up in Enterprise Manager. thanks for your anticipated response. Tunji ...more >>

Hi, my procedure cannot return null values as the application retrieving the data will throw an exeption. I therefor use the IsNull-function, but it does not solve the problem completely - some values are returned as null regardless. When I debug the procedure - @BundleLength can be retur...more >>

dum = strSessionCustCode cn.Open "DSN=SECWAT;UID=sa;PWD=sa;DATABASE=Master;SERVER=sql2000" stemp = "EXEC Custbycode 'dum'" Customer.Open stemp, cn, adOpenDynamic, adLockPessimistic, adCmdText The above code tries to execute the stored procedure "Custbycode" and passes the arguement dum cont...more >>

I am attempting to create a stored procedure with the following code: CREATE PROCEDURE "hvp_ValidateAcctID" @ID AS INTEGER AS If Exists(SELECT * FROM Acct WHERE Acct_ID = @ID) RETURN 1 Else RETURN 0 GO The net result is that I want to return a 1 if the acct_id exists, otherw...more >>

Hi all, What are the limitations for the number of joins or tables in a SELECT query in SQL Server 7 and 2000. Thanks in advance. Cheers, Nash Hoogwater ...more >>

Hi, I use the exportdata method under the DMO: Dim oServer As SQLDMO.SQLServerClass Dim oDatabase As SQLDMO._Database Dim oBCP As SQLDMO.BulkCopyClass oServer = New SQLDMO.SQLServerClass() oBCP = New SQLDMO.BulkCopyClass() oServer.EnableBcp = True oServer.LoginSecure = True oServer.Conn...more >>

Hi all, I am running SQL Server 2K Personal Ed on a Win2K pro. I also have the Novell client on my machine. My machine is also a member of a Windows Domain. I use the Windows Domain user profile and Novell user profile to access my machine. My local instance of the SQL Server is started us...more >>

SS 7.0 Is there a "Date" function that will enable me to return every our of the day (i.e 00:00:00 01:00:00 02:00:00....) without having to create a temp table to store all 24 values? TIA, Jordan ...more >>

H I have implemented my own autonumber system, as for various reasons an sql server identity field is not appropriate. I have been testing this by having 5 connections to the sql server just calling the SP and generating numbers and inserting them into a table. It seems that every 10000 records...more >>

Good day! I have several databases in one SQL Server, and having problem maintaining these - so that they are equal. Not the data, but the tables, views and SP. Are there any way in SQL Enterprize manager or any other programs that is generating a sql script based on alter table, and alter v...more >>

I need to debug a closed app and obtain the sql code this app uses to access my database. In the past I have written triggers to raise errors with key values that I knew would be in the inserted record to help give me more information. But this is limited at best. Is there a way to see the e...more >>

SQL Server 2000 SP3 (Windows 2000 SP4 + MDAC 2.8) Access 2000 SP3 (Windows 2000 SP4 + MDAC 2.8) Help! I have a Form within an Access Data Project, with a sproc as its record source. The form has the unique table property set to a sql server table which I would like to update, ALL tables ...more >>

When I try to access any table on any server using Enterprise Manager I get this message. Unknown error: 8007007F This is all of a sudden. My Server is running but I cannot access any data? Please can Someone Help. ...more >>

I have a table that has a nullable column which gets used a lot in my program. I am finding myself using an aweful lot of "IF @Variable IS NULL .... ELSE ..." syntax in my stored procedures, triggers, etc. Is there a way around having to use this syntax every time I need to do a "WHERE Variabl...more >>

Hi Code was working fine on diefferent machines, but suddenly on one of the machines from time to time it gives this erro 01/27/04 03:58:40 DB-LIBRARY error Attempt to initiate a new SQL Server operation with results pending 01/27/04 03:58:40 DB-LIBRARY error Attempt to bind to a non-existe...more >>

Hi! I need urgent HELP I have successfully set up my SQL Mail to send auto emails. The problem now is, the message in my subject line gets truncated. Is there anyway I can resolve this. Appreciate any help Thanks Carro ...more >>

This is probably a very simple question, but I will ask anyway ... Why is it necessary to have a fully qualified table name in a select statement issued by a login ID that doesn't own the table? For example, I have a database (sampledb01) with a table named customers. The table was cr...more >>

Hi, How can I write a query for following problem. I have a table Inventaris with Year, ProductId and Number Another Table Product with ProductId,Name,..... I want to fill Table Inventaris with all the records from Products and Inventaris.Year = 2003 (I do it in access) Insert INTO I...more >>

Hi All, I havea field logtime with datetime datatype. I would like to remove the time part and then group by date, however whether i remove the time part or not, i get the same results. I m givng the code below to convert the date, can anyone plz help? many thanks select convert(datetime,Conver...more >>

I need to set the default value of a field. Pretty simple task. But there is quite a bit of text to use. Doing this in Enterprise Manager is only allowing me to enter some text. Is there a query that will allow me to update the default value of a field? My field type is TEXT so I know I h...more >>

I have a table which is returning inconsistent results when I query it! In query analyzer: If I do "SELECT * FROM TABLE_NAME" I get no rows returned. If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned. In Enterprise manager: If I do "return all rows" I get 4 rows retur...more >>

As the subject says, we are looking to join two tables from different databases. We have two different databases which reside on the same server. In databse 1 we have a number of tables which are used solely as lookup tables. Database 2 has recently been created, and we would like to use ...more >>

Which statement will be more optimized, and will utilize minimum servers resources IF EXISTS(SELECT * FROM ABC where some condition) UPDATE ABC ......where some condition ELSE INSERT INTO ABC OR UPDATE ABC.....where some condition IF @ROWCOUNT = 0 INSERT INTO ABC ........more >>

I run the command: SELECT * FROM openrowset ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\commission manager phase ii data\dealer data.xls',[ticking$])Rowset_3 where (0=1) What I need to know is where it looks for the file? Does it look on the local computer or on the server? When I...more >>