Hi, I need to get a list of columns and corresponding datatypes for the given table name. I am using the query: select a.name, b.type_name from syscolumns a, master.dbo.spt_datatype_info b, systypes c, sysobjects d where a.xusertype = c.xusertype and b.ss_dtype = c.xtype an...more >>

Hi,, I'm having a problem with calling a function from an activex script within a data transformation. the function takes 6 inputs and returns a single output. My problem is that after trying all of the stuff on BOL I still can't get it to work. It's on the same database and I'm running sql 2...more >>

Hello! We have a maintenance job on MSSQL server scheduled for the weekend. some times we arrive at the beginning of a week and see that a user left his screen open on a SQL record and the job is still waiting blocked by that user. Please give me an idea how to overcome this. Thanks in advanc...more >>

Can anyone help with this error message I get with vbullettin Database error in vBulletin 2.2.9: Invalid SQL: INSERT INTO useractivation (useractivationid, userid, dateline, activationid, type, usergroupid) VALUES (NULL, 21, 1063544677, '27618960', 1, 3) mysql error:...more >>

Is there a way to set a default database for a server registration? I'm with a shared host and clicking my web hosts server entry in Enterprise Manager lists all the database on the server, which takes a few minutes of waiting. Maybe there's a way to set one default database for the server regis...more >>

Hello. I have query performance question. I need to optimize procedure CREATE PROCEDURE dbo.SECUQUSRCOMPACCES @P1 VARCHAR(50), @P2 INTEGER AS DECLARE @IORGANIZATIONID INTEGER EXECUTE dbo.ORGNQGETORGID @PORGUNIQUEID = @IORGANIZATIONID OUTPUT SELECT TSECCOMP.ID, CASE TSECPROFILEGRP.ACCESS...more >>

Hi, i am having problem to install sql2000 server, the steps i did are: 1. Local machine -> next 2. Create a new instance of SQL server -> next 3. type name and company -> next 4. accept -> next 5. Server and client tools -> next 6. Default -> next 7. Typical -> next 8. use the same account ...more >>

Whatever you do, don't try aspwebhosting.com . One day recently I got an email from them saying that my account had been suspended for using too much cpu time. There was no further explanation. Those bastards took my site offline, removed my email account, blocked my ftp access and even refus...more >>

We've been running a database on a shared server. The traffic on our site has been picking up steadily and it seems as though we're running into sporadic downtime on the database. Sometimes when we do large data imports, the transaction log fills up and the schema somehow fails to truncate it ...more >>

Hi all, Question from a guy who knows PL/SQL, but is starting with Transac-SQL on SQL Server.... Is it possible to have a sub procedure in a Stored Procedure, like we do in a PL/sql package ? Or better yet, have a kind of general Stored Procedure that would contain procedures called by...more >>

Seems the XMLBulkload does not support the "Trigger". But I really need to execute some rules after bulk load, anyone can give me some hints about this? I wonder whether I can fire a trigger in my asp code after I execeuting xmlbulkload? Or any other ways to fire trigger? Thanks for your ass...more >>

Hi, I have (amogst others) three tables in my database named Bookings, User, and Representative. A User and a Representative are different types of user, however I now want to merge these tables into one - User. When a Booking is made, the User_ID and the Rep_ID are stored in the Booking table...more >>

We have a product that we were experiencing high recompile rates do to temp tables, we have re-written them to use table variables to reduce recompile rate. The problem I am having is that we have some customers still on SQL 7 where the table variables were not valid. I would like to have one ...more >>

Hi I am trying to import several master detail records from files to ms sql server. I have orders file and order_items file that has several rows for each order. If I insert programmatically these records how can find out which order ID was the last inserted, so that I can attach the subsesq...more >>

trying to make a script to view data in a excell spreadsheet. I can get all the data I need except one. select ordernumber, itemcode, quantity, rate, total from dbo.charges where ordernumber = '45676' the problem is there are more than one itemcode example: run results ordern...more >>

Greetings, I had a hard-drive failure this week and lost my root drive, with operating system and SQL Server 2000. Fortunately my data was on another volume and still exists. I know about sp_attach_db, but looking at BOL, it says that sp_attach_db only works with databases that were pre...more >>

We are writing a C application that is using ODBC to insert records into a database. We have a NOT NULL column that can legitimately have an empty value, i.e. we know the value and it is empty (i.e. a zero length string). We are using SQLBindParameter() to bind a variable to the parameterized...more >>

Hi, The application setup is: Access frontend + ODBC (TCP/IP) + MSSQL7 Few days ago i was informed that the app generates high network traffic. Thus i started to look for some savings. I found out (sniffer) that even if the client is idle every 30sek. a TCP packet comes from client to s...more >>

I have a sql server automatic job set up containing 2 steps. First step is an Operating System Command (CmdExec) type job which runs a .bat file which copies across a backup file to this machine. The second step is a Transact -SQL Script (TSQL) type job which runs the RESTORE DATABASE REPLAC...more >>

Hi everybody, this view runs just fine but when I try to save it I have an error message: ODBC error: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect of syntax error. Where is the problem? I use questionmark because I need unnamed parameter veiw. select ProductID, NameOfProduct fr...more >>

hi, does anybody know something about sql-server and rollback ? my problem: I added some data to a database but the writing process was aborted, now I'm not sure if the the data are in the database or not ? Does the sql-server support the feature rollback automaticatly ? thx. for your help...more >>

i've one dev and one prod, one of the table in dev database having 70 millions records and need to export to one of the table in prod database. both machine got 2 nic, one 10/100 mbps and the other 1gbps speed. databases on raid5. the transmission between two servers is very slow, it's transmitt...more >>

SQL Server 2000 Enterprise Edition Access 2000 Front End One of our clients has recently been experiencing problems with an app that has run satisfactorily (though slowly) for some time. To overcome the slowness, they have installed a new server with SQL Server 2000 Enterprise Edition with '...more >>

I want to insert values into a table and the same time select a value from the same table. I insert a record of a parent type and use a function to create a subscriberType for the record. Next i insert a record of child type and want to select the subscriberType from the parent record. T...more >>

Hi all. I have an keyword table by 2000000 rows that is an int(4) key and a varchar(50) Keyword(Unique index) in Sql Server 2000. After about 80 seconds of starting full population, speed of adding new items t in Enterprise manager/Fulltext ctalog, decreases to (1/4) and more less. plea...more >>

I have an Access 2000 database connected to a SQL Server and am trying to execute my first stored procedure. I created the stored procedure and verified that it works, but when I try to execute it from Access: cnn.Execute("sp_IPT") it says: 'Could not find stored procedure 'sp_IPT' A...more >>

Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName, Employees.LastName, TerritoryID, Employees.EmployeeID, RegionID, ProductID from Employees inner join Emp...more >>

I am relatively new to SQL Server. I have a command file with the following contents : osql -E -i%1.sql -d%2 -oq:\%1.log The sql script file has a number of insert/update statements. The log file produced looks something like this : 1> 2> (1 row affected) 1> 2> (1 row affected) 1> 2> (0...more >>

hi How I could print a report on printer using SQL 2000? I am not going in detail about the report as at the moment I need to know how produce reports on printer. Thank you *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

We recently added a new database at the company. It has only one purpose - to hold massive amounts a daily data generated by telephone calls on a network. The amount of data was so large (several gigabytes a day) that the guy who set up the database creates a new table for it each day. His...more >>

I am trying to order user roles by privileges Would ideally like it to look like this severole permission ******************************* sysadmin Kill restore database restore log ******************************** instead of severrole premission *********************...more >>

Hi, I have developed an application using VB 6 (SP 5), MS Access 2000 and Crystal Reports 9. I migrated from from access to SQl Server 2000. This wasn't a problem as i could import all the tables to SQL Server. Also, i could make VB6 talk to SQl Server. The problem arsies when i run my app...more >>

I believe I found a Bug in MS SQL Server 8.0. I have a query with an ORDER BY clause. The resultset is about 10000 rows. Sometimes the ordering of the result is not correct, but when I restart the sqlserver-process, at least the first time the ordering is correct, but then after executeing...more >>

MsSQL Server 2000 PowerBuilder 8 Application 20 client on LAN ethernet 2 client on 64 K router-to-router LAN on 2 remote client I have this error. There are two router Cisco whit idle-timeout on 1800 sec, but evry 1/2 minutes on the 2 remote clients there are the error: 10005 dbprocess i...more >>

I seem to remember reading many moons ago about a function where you can retrieve a count of the last recordset you opened. For example: I've got a stored procedure that returns a recordset using the TOP 10 so I only get the top 10 records. I need to know the recordcount but I dont want to ...more >>

Hi Please help for this simple problem DTS Transfer or any other method? I have Customer_Order Table and customer_Order_Details Table. For OrderID = 1, I have 3 rows of Order Details.I want to transfer Customer_Order Table for OrderID 1 in DTS, the system should transfer ...more >>

If I have two sql server databases that started out with identical table/key/index structures, but were not properly kept in sync, is there any way I can generate a table change script to essentially 'diff' the two databases and come up with table change scripts to bring one in line with the oth...more >>

Hi, I want to write and use a VB program to access my SQL server through a VPN. 1) is this possible? 2) will it damage my SQL tables in any way? Thanks!!! ...more >>

Just wondering if there are other people getting mail from Pinnacle software demanding payment of $179 for a subscription that they didn't request. I am now on the "Fourth Notice - Invoice past due" I know only one other SQL Server DBA and that person is also getting the mail each month. In...more >>

Someone entered a lot of incorrect data into our SQL 2000 database. Is there any way to determine who made the changes? Thanks in advance. ...more >>

trying to make a script to view data in a excell spreadsheet. I can get all the data I need except one. select ordernumber, itemcode, quantity, rate, total from dbo.charges where ordernumber = '45676' the problem is there are more than one itemcode example: ordernumber, itemc...more >>

Hi, Given 2 tables: Table1: id Auto,int,PrimKey table2id int txt nvarchar50 Table2: id Auto,int,PrimKey order int The scenario: Table2.order defines how the table1.txt is should be ordered. Table1.table2id contains the id of the order. This cannot be changed :( How ...more >>

HI, can i export all tables and data into a ms access file? thanks:) ...more >>

I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and notic...more >>

Hello. I have one serious problem with COUNT in TSQL. I use MS SQL Server 2000. I would like to count rows depending on data in it. What I meat is: I have fields like: region | month | year | some_count | -------|-------|------|-------------| LA | 1 | 2003| 4 ...more >>

Hi all, I am using the Oracle Workbench for migrating my DB currently on Sql Server 2000 to Oracle 9i. During the migration the following happens SELECT @tBaseTable = tBaseTable, @tDBColumnName = tDBColumnName FROM tblColumnMain WITH (NOLOCK) left join tblCustomField WITH...more >>

hi Could someone please help me in following: I need to update Policy status base upon commencement date and then print with new Policy status. I written a stored procedure to update the policy. Now I need to work out how I can print a report. We already doing the report manually in our ...more >>

hi Could someone please help me in following: I need to update Policy status baseupon commencement date and then print with new Policy status. I written a stored procedure to update the policy. Now I need to work out how I can print a report. We already doing the report manuall in our VB...more >>

Anybody know of a way to view SQL Queries sent to a SQL Server 2000 box ? I have tried using the 'current activity' option in enterprise manager, and ODBC tracing but neither show the queries made by this 3rd party app. I have no db schema and wondered if there was a tool to let me see the ...more >>

Hi, I'm hoping someone can help me When trying to export from SQL to a file using the following: -------------------------------------- DECLARE @FileName varchar(50), @bcpCommand varchar(2000) SET @FileName = REPLACE('c:\tmp\tblComponentType_'+CONVERT(char(8),GETDATE(),1)+'.txt',...more >>

The best way to explain this is by example. I have a source table with many columns. Source SYMBOL EXCHANGE_NAME CUSIP TYPE ISSUE_NAME and so on Then I have 3 other destination tables. Exchanges EXCHANGE_ID IDENTITY EXCHANGE_NAME UNIQUE ...more >>

We're using SQL Server 2000 and we'd like to be able to track our view usage to tell which of our views get used most often. This will help us determine which views we need to concentrate on first for optimizing, indexing, etc... Does anybody know if there's a way to tell when a view is opened? ...more >>

I want the sum of the last payments (amount) for all customers. The last payment is with one with most recent date. And if there are more than one payment on the most recent date then the one with the higher paymentid is the last payment. for example in the given data the insert statement that s...more >>

Hello all, I got a chance to peak into a database system. Part of its design is rather unfamiliar to me. When I look at the diagram generated by SQL Server, there are many floating tables. Eventually it turns out that these many floating tables are actually not floating. Their table names...more >>

Hello We've got a string field with some words, and I created a form so that anybody could search someting into this field. SELECT * FROM customer_table WHERE description LIKE '%query%'"; But if you search '100', it returns '45,100'. Or if you get 'plugged', it reurns 'unplugged'. I tr...more >>

With Oracle, there is a comprehensive tool called SQL Navigator as well as TOAD. Does such a tool exist for SQL Server 2000? @drian. ...more >>

Hi I am trying to use the WAITFOR function to make each loop in a cursor occur every 4 seconds until the curdb is empty the prtocedure is as follows ---------------- start create procedure q_additionalrabatt @additionalrabatt float, @ordernr int AS declare @ordradnr int decla...more >>

Why cant I use the same temptable name i a stored procedure after i have droped it? I use the Pubs database for the test case. CREATE PROCEDURE spFulltUttrekk AS SELECT * INTO #temp FROM Jobs SELECT * FROM #temp DROP TABLE #temp SELECT * INTO #temp FROM Employee SELECT ...more >>

Hi all, I have to translate an Access query into sql. The query has the following statement. I know SQL doesn't support iif, so can someone tell me how to use the case statement to get the same result? select field1, IIf(Grand_total-50>0, grand_total-50, 0) AS field2, field3 Thanks. ...more >>

Hi, Somtimes the agent will just die on some computers... I'd like to have a job monitoring the agent every 20 minutes or so.. Do you know of a way to tell if the agent is up or no using plain Tsql? Thanks Gaspar...more >>

Hi, In order to establish a security enhanced SQL server setup, I tried to switch off network access by disabling all networking protocols, so that the server can only be reached through a pipe. Nevertheless the server was visible in the network and could be accessed from all clients. Does an...more >>

Erland Sommarskog <sommar@algonet.se> wrote in message news:Xns93EFA9C57954AYazorman@127.0.0.1... > MAB (fkdfjdierkjflafdafa@yahoo.com) writes: > > What I want is the sum of the amounts of the last payments of all > > customers. Now the last payment of a customer is not necessarily the one > > ...more >>

Does anyone know of a SQL Server product that would let me do the following? Connect to the instance Configure thresholds like, 1.Show databases not backed up in the last X days 2. Show databases that are full that have logs not backed up in the last x days/hours. 3. Show jobs th...more >>

Hi I've done a search and found plenty on ORDER BY problems, but mines hopefully a syntax error. This is a test segment from my stored procedure. DECLARE @Name varchar(50), @SortType Int SET @Name = "canal lock" SET @SortType = 8 SELECT dbo.ite_Item.ite_ID FROM dbo.ite_Item LEFT ...more >>

When you guys talk about "dynamic SQL," to what exactly are you referring? Is dynamic SQL anything that isn't a stored procedure? Specifically, I use ASP.NET to communicate with my SQL Server 2000, using an SqlConnection object to open the database and an SqlCommand object to transfer my S...more >>

hi all, i need some helpful advise from you. i need to write a sp that you may be able to help me with. i have a table that only contains a numeric field. i need to write a sp that, when executed, the numberic field increments by one and then returns the new numeric. i have looked on the ne...more >>

How to restore a database dump file (.BAK) that backed up from SQL 2000 server to a SQL 7.0 server? Please help. Saiyou...more >>

I have a SP that I use to insert records in a table. This SP is called hundreds of times per minute. Most inserts complete very fast. And the profiler data is as follows: CPU: 0 Reads: 10 Writes: 1 Duration: varies from 1 to 30 But once in a while the insert SP seems to stall and takes ...more >>

Hi! Whether there is an example where is used "Left Anti Semi Join"? andrey@tic.com.ua ...more >>

I am sure this is a configuration or permissions problem, but I cannot figure out what it might be. I have 2 SQL 2000 database servers: one is a linked Windows 2003 based server using a specified login account and the main server is Windows 2000. The user on the linked server is an SA. Both ...more >>

Hi - I know this isn't really a specific SQL Server question but I can't think of a better forum. I want to implement a more sophisticated UK postcode search on a site - "LIKE 'NW%'" etc, is not bringing back good enough results. A feature that would allow me to ask: "give me all outward pos...more >>

In SQL Server 2000, I've got a rather lengthy stored procedure, which creates a lot of temporary tables as it processes down through a few sets of data. When testing it through Query Analyzer, it runs fine (a bit slow though). But when I try to run it through the ade, it doesn't do anything....more >>

Hi all I am trying to do a Bulk Copy from a "tab delmimited" text file to a table in my database. I have it almost working except when the file has too few columns for the table (table has 421 columns). Some of my the files will have 419 columns some others files will have 421 columns. W...more >>

Hello everyone! I'm from El Salvador and i have a problem, i have a sybase system running on windows 2000 server, probably a programmer drop a database...but i dont know who did it?, is it possible to know who drop the database?, if the answer is yes... plese help me, because we need to ...more >>

I have the MS access query below which uses an IIF statement to put a value in either the Owned Pallets or Rented Pallets field, how do I do this in SQL ?? ... SELECT KC_COPA1.[Posting date], KC_COPA1.SKUCode, KC_COPA1.HierLevel3Code, KC_COPA1.HierLevel5Code, KC_COPA1.LineNo, KC_COPA1.EndMarke...more >>

Hello, I am administering a DB which is about 2GB in size and sees few hundred, maybe up to a few thousand inserts a day plus very few deletions and updates (less than few hundred). DB has about 200,000 records in it total. For some time now I have noticed that the transaction logs grow a lot ...more >>

Hello We ran into a peculiar problem. We copied all of our DTS packages from one server to another server (both of which run SQL 2000 and are on the same Service Pack 3/3a). When we execute an embedded DTS package from within anothe DTS package, we get the following error: Error Title: Se...more >>

I have two values and I want to express a third derived value as a percentage of the other two values. I thought it would be a simple division of the first two numbers and then a multiplication by 100 to give me a percentage, but all I get is 0. Here is my select statement, SELECT db...more >>

I will explain (or at least try to) first and then give an example after. I need to append a number of rows from several tables into one master table. Unfortunately there are certain columns (which are UNIQUE columns in the master table) in the source tables that are repeated in the same tabl...more >>

Hi, I am using SQL Server 7.0 TABLE1 ====== Fld1 Fld2 Fld3 Fld4 Fld5 Fld6 Primary Key = Fld1 + Fld2 + Fld3 TABLE2 ====== Fld1 Fld2 Fld3 Fld4 Fld5 Foriegn Key = Fld5 (Referencing TABLE1.Fld3) The datatypes are matching in both tables. I am trying to create the f...more >>

Anyone know if there is method that can insert all record from a table in an MS Access 2000 database to a table in MS SQL Server 2000 database by a SQL statement? (Therefore, I can execute the statement in my program) -- Posted via http://dbforums.com...more >>

Hi Gurus, i am having problems with restoring a ms sql database. i have restored the database using veritas to a different location ('g:\datafiles') in no recover mode. when i view the database through the Enterprise Manager, it shows the database as silver icon (loading). i go to sql ...more >>