Hi, I run a stored procedure with a parameter given from a text box in an access adp. If the text box is empty then what is passed to the parameter? I can't seem to get it to flag up as either null, or 0 length. Any tips? Cheers, Chris ...more >>

I am in the process of testing an EMC Clarion install with our Data Warehouse. Performance is fast and consistent for queries using a clustered index but very poor for queries using non-clustered indexes. Performance on non-clustered indexes is very slow and inconsistent compared to our curren...more >>

Hi all. I know that since you know nothing about my database it will be a stretch to answer this - but does anyone have a boiler plate checklist for moving a database, dts packages, agent jobs, etc...from one server to another? Maybe something that just says I should at least do x,y,z. Also i...more >>

How do I loop thru a result set Without using a curosr?...more >>

I've Proc1 and Proc2, the output of Proc2 can be in XML or thru a normal Select statement, depending upon the input parameter specified (@xmflag). The default of @xmflag = 0 which means non-XML output. Now Proc1 is calling Proc2 and inserting the output of Proc2 in a temp table without specifing...more >>

Hi, I tried to use the following query to update a value in one table with a value from another table: UPDATE tbl1 SET col1 = tbl2.col2 FROM tbl1, tbl2 WHERE tbl1.[id] = tbl2.[id] but it won't work. I also tried this with a subquery using "TOP 1", but that wouldn't work either. SQL Ser...more >>

Hello All, I have a stored procedure which will act like a main/controller script which will then invoke more stored procedures (~20). Basically, it looks something like below: -- start script create procedure ... print 'process started' exec sp_1 exec sp_2 exec sp_3 .... print 'proc...more >>

Hi, I used SQL Server 2000 Personal Edition. I created a stored procedure having input parameters as smallint,tinyint,char,varchar and smalldatetime. When I executed it by Query Analyzer, it's ok. But when I executed it by ASP code that used ADODB.command, it showed error: Invalid character v...more >>

I have a problem with a stored procdure I wrote. This proc will process 80 to 100 million every night, it aggregates, inserts into a different table, and then deletes the original data. The process will hang after processing 20 to 40 million records. I must stop the proc and then restart it befo...more >>

i have installated on my computer ms sqlserver 7 .I want to try some transactions.Is it possible to connect as 2 or tree users just to simulate working with many users? ...more >>

Hi, Is there any replace for "Select @@identity" that could return "just inserted" GUID as a primary key? Has anyone tested what's faster, working with Guid or Autonumber ? ...more >>

Greetings, I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call them parent, child1, and child 2. On parent, I create a view called item as follows: CREATE view Item as select * from child1.dbchild1.dbo.Item union all select * from child2.DBChild2.dbo.Item On child1...more >>

Hello When I use a PreparedStatement (in jdbc) with the following query: SELECT store_groups_id FROM store_groups WHERE store_groups_id IS NOT NULL AND type = ? ORDER BY group_name It takes a significantly longer time to run (the time it takes for executeQuery() to return ) than ...more >>

Hi, I would like to convert a dollar amount ($1,500) to represent Fifteen hundred dollars and 00/100 cents only for SQL reporting purposes. Is this possible and can I incorporate the statement into an existing left outer join query. Thanks in advance, Gavin...more >>

We have a Dell 6450 quad 1.6MHz, 4GB RAM running SQL 2000 on Win 2000 server with 2 PERC3/QC cards. Server has 4 internal drives configured as two raid-1 drives (OS and SQL logs). Server has 7 more drives in external drive rack for RAID-5 SQL data. We did not have a UPS available for server at ...more >>

Hi, I have a stored procedure that needs to know the name of the table from which it is called. If you are familiar with a "this" pointer in Java or C++, that's very similar to what I need. I know I can use db_name() to retrieve the database name, but how do retrieve the table name? Than...more >>

Hi, I'm having some trouble finding any information on this topic - can MS SQL server 2000 copy/delete files on the host computers disk. Any info on this would be great. Thanks, Eirik ...more >>

I have a statement that works in compatibility mode 8 of SQL Server 2000 while it fails in 7: declare @P3 int exec sp_prepexec @P3 output, N'@P1 float', N'select custname from customer where custnr = @P1', 12600034 custnr is a varchar(15). As long as the float p1 is less than 1,000,000 the...more >>

Hi. I did this SELECT BOTTLEA, BOTTLEB, BOTTLEC FROM MYPRODUCTS But, I would like to have the result returned only for distinct BOTTLEB. BOTTLEA and C can be anything. But the result that I get from the statement must have BOTTLEB as distinct. How do I do that? I tried SELECT DISTINCT...more >>

Hi everyone I had an access database running as the source for a website but it has become too large to run correctly so it has been ported to MS-SQL the problem is that 4 of the fields were Memo fields in access and as such are 5000+ characters long each this overflows the allowed size on the ...more >>

We have a series of stored procedures that utilize some of the master extended SPs such as xp_cmdshell. We migrated to SQL 2000 in April and everything has worked great for over 3 months. However, this week we discovered that the xp_cmdshell SP was missing from the master database. Here is the e...more >>

Does anyone know how to run a DTS package through a normal MS Access? I've got some code from the web and it doesn't seem to work - perhaps it is because I'm connecting to an instance on a particular sql server "servername\instance" Function SimpleExecutePackage() Dim oPKG As New DTS.Pa...more >>

How does one get the primary key of the row that is joined in via a group by aggregate clause when the aggregate is not performed on the primary key? For example, Person table ( PersonID int, FirstName varchar(50) LastName varchar(50) ) Visit table ( VisitID int, ...more >>

I am having a problem with a query, I am not sure if i would use a join or a subquery to complete this problem. I have two queries, and i need to divide one by the other, but i cant seem to get any type of join to work with them. Here is the situation. I have a projectDB table that has a lis...more >>

In SQL Server 2000, developer edition (on my desktop), I created a complex DTS package that truncates several tables and imports fresh data into them (from Oracle 9i). This package was scheduled and executed by the SQL Agent with no problem. When I moved the database to a server, the scheduled...more >>

I got a new Job where I have to handle Oracle databases on Linux. My previous experience is with Sql Server on Windows NT. I want to know how difficult will this shift be and what do I need to learn first and quickly. How different is Oracle on Linux then Oracle on windows. What are the major di...more >>

I have a view that will return say 5000 records when I do a simple select query on that view like. select * from vw_test_view How can I set up my query to only return a certain # of records, say the first 300? Here is what is going on, we have a large amount of data that returns in ...more >>

I'm using ODBC to interface a Microsoft SQL Server 2000. One of the operations involves placing files within BLOBs. I'm using the image data type for this purpose. Most of the time this works okey, but when trying to add a 21,3 MB file I get an error. The error code is 22001, which means "String...more >>

Hi all, I have some trouble with SQLAgent...when I try to schedule a job that invokes a script (vbs, java, cmdshell,...), SQLAgent crashes at the moment of scheduled execution. If I try to execute it manually, all works fine. This installation runs on a cluster Win2003 and the node initiate a fa...more >>

Does anyone have possible explanations for the following results? select count(*) from tablename >>> 2,500,000 select count(*) from tablename where fieldname is null >>> 2,400,000 select count(*) from tablename where fieldname is not null >>> 900,000 Just my luck, I was showing som...more >>

Hi to everybody, I'd like to know how to extract a progressive number in a select. Example.. Select * from employers Name Surname Position Age Hire Date Progressive Mario Rossi Consultant 25 26/07/2003 1 (this field doeas not exists in ...more >>

Hi I am trying to find out best practices for SQL Server 2000 installs for corporate servers. We currently have someone doing it 'in their spare time!' (I kid you not). When installing SQL server 2000 out of the box on a freshly formatted machine, I would like to get some feedback on the fol...more >>

I have little problem and I dont have any idea how to make my trigger. I have table MyTable where I have many column with almost same name and same type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1)). I`d like to make some trigger on UPDATE this table. I must to check which column was changed. For e...more >>

Hi, I'm new to ASP and have stumbled across what appears to be a common problem, but after trying several solutions from other posts I've had no luck. My SQL SELECT statement is fine elsewhere (e.g. in ACCESS), but when executed from ASP I get this syntax error: Error Type: Microsoft OLE ...more >>

I would like to update a decimal column in a temporary table based on a set of Glcodes from another table. I search for a set of codes and then want to sum the value for each row matching the Glcodes. The problem is I keep getting multiple rows returned errors. "Subquery returned more than 1 v...more >>

I want to create a DTS package that will. Cleardown all rows in a table called stock Then populate stock with all data from another table called stock1. I also want to schedule it to run everymorning. Can anyone help? Regards, Ciarán...more >>

I'm a newbie when it comes to the SQL Server side of things. I've been doing Access on and off for years. I've got a form that is the main point of entry for all data. As a matter of technique, should I use one stored procedure to validate data (testing for uniqueness mostly) then another on...more >>

I have recently started receiving failures on a differential backup that previously succeeded. Nothing has changed with the structure of the db. Here is the message, any ideas are welcomed. Thanks. Executed as user: fsafood-net\bisqlservice. The backup data in 'E:\SQL_Backups\Biz_Sales\Biz...more >>

We have a SQL 7.0 Standard Server running on a Windows 2000 Server machine with 2 800mhz Pentium III with 2GB memory. Our front end is Access 97 and 2000 with most ADO connections for the scripts but some DAO for forms and reports. We recently "released" a new version of the "database" that ca...more >>

Guys, Got this query... --------------------------- SELECT TOP 5 Tbl_admin_hotels.HotelName, (SELECT COUNT(Tbl_marketing_history.HotelID) FROM Tbl_marketing_history WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID) AS CountTotal, (SELECT MAX...more >>

I have a table that's of type date/time (i.e. 01/01/1900 00:00:00). What I want is to do the following: Say you have these records: person | date-time -------+--------------------------- jim | 06/02/2004 00:05:52 jim | 06/02/2004 05:06:21 jim | 06/02/2004 05:46:21 jim | 06/...more >>

I realize the irony of asking this here, but does anyone know of any MySQL groups where I can ask my questions? Thanks. ...more >>

I don't know what newsgroup to post this to so I'm posting it to both. I'm receiving sporadic deadlock problems. I get messages which look like the following: "Transaction (Process ID 57) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. R...more >>

It seens that a simple script: Update sysxlogins set name = 'AA001' + substring(name, 9, LEN(name)-8 ) where name like 'ILLINOIS%' Will replace the SQL2000 domain name correctly in sysxlogins: ILLINOIS\JonesP becomes AA001\JonesP But for some strange reason via ILLINO...more >>

I want to know if exist a free version of MSSql Server with a limited access. Thank you. S.L....more >>

I have an SP which I use to insert into a table data from another. For example... INSERT INTO myWorkTable (Field1, Field2) SELECT myField1, myField2 FROM myNewTable It's a pretty straight forward sp. Now, the data that gets inserted seems to have remained in a state that stops...more >>

How can one identify the current licensing model on an existing (production) SQL Server?...more >>

Any good SQLServer DBA guides available online? Preferably a downloadable pdf. Or online in HTML. Thank you. ...more >>