Hi, In SQL 2000, users could select from master.dbo.sysprocesses to determe the total number of users currently in that database e.g. select DISTINCT Loginame, Program_Name, Hostname from master.dbo.sysprocesses where master.dbo.sysprocesses.dbid = db_id() and master.dbo.sysprocesses.Progr...more >>

i have two fields. One is id (otomatic number) and another is email.. there are some duplicate records in email records. I want to use distinct query for email field to eliminate duplicate records..I also want to get id fields of resultset of distinct email. I thought that there are more t...more >>

Dear all, I want to join three tables A,B,C, so what is the difference between the following joins Select field-1, field-2 from A join B on A.field-1 = B.field-1 join C on C.field-1 = A.field-1 Select field-1, field-2 from A join B on A.field-1 = B.field-1 join C on C.field-1 ...more >>

Is there a way to automatically insert a row into a table when a row is created in another table? For example, suppose a row is added to the "Current Data" table. I would like another table, "Historical Data", to be automatically updated with data from from the row added to "Current Data...more >>

I have an application that calls many stored procedures. They are occassionally getting into a deadlock situation. I have used DBCC TraceOn (-1,1204) to trace the problem and have had moderate success. However, I have hit a particular deadlock that I just don't understand and was hoping som...more >>

Hello, I moved a sql 2000 DB to SQL 2005 and it seems to be very slow. The same DB on a slower machine running 2000 runs mutch faster. For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102 seconds, 20 times slower. I reindexed and that did not help. Any Ideas? W...more >>

(This is a crosspost as I believe one of you should be able to answer this.) First, I am led to believe that I must create a Primary Key on a view to allow apdates to be performed. I am getting this error in an application I am writing as I choose a recordset update on a VIEW that I have c...more >>

Please can someone tell me how to get this working. It seems impossible. I just want to debug my stored procedures remotely, from a client PC. Surely I don't have to physically sit at the server to debug the stored procedures? Owen -- http://www.binarybaby.co.uk :: home-made electron...more >>

I have a table A which has the following structure: Column Name(Type) ---------------- ------ ID(int) Repair Code(varchar) Damage Code(varchar) Location Code (varchar) I also have a the master code table Column Name(Type) --------...more >>

Hi, hope im posting in right group... Im trying to return a value from 1 of two tables depending on the value in one table. I.e. TableA has an action_id and action_type fields. Depending on if action_type = S, return the Desc field from TableB, where action_id = Desc_ID. if action_type = ...more >>

I'm looking for some ideas on how I can accomplish the following: I have a web server in the DMZ, so it is not part of the domain. There is a requirement that the only port open from this web server into the company's network is the SQL Server port. There is an ASP.NET application running o...more >>

Hello, I have a procedure that reads a table where each row contains the name of a text file to be imported into a Microsoft SQL server table. The problem is I don't ahead of time if the text file is Unicode or ANSI encoded. The 'BULK INSERT' command works fine in all cases. I would just like ...more >>

I've been reading a lot of threads in this group that reccommend against applications interfacing directly with a production database. My company uses accounting software with a SQL backend and they used to pull just specific tables nightly using a DTS package from the old software backend to ...more >>

Now I am trying to bulk insert a text file with 6996 rows. I found the last 19 rows always failed to be imported. But if I copy the 19 rows to a seperate file and bulk insert that file, they were all imported successfully. I cannot find any format problem in the 19 rows. Do you know what can c...more >>

I'm trying to assess all of the tables that a particular table has relationships with in a database. Is this the best way (selecting contraints from sysobjects)? SELECT [name], xtype FROM sysobjects WHERE xtype IN ('F','D','UQ') AND parent_obj = (SELECT [id] FROM sysobjects WHERE [name] = 'tab...more >>

Hi I am getting this strange error : Server: Msg 1934, Level 16, State 1, Procedure dataIntegration_mergeCompanyData_prc, Line 90 UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' the problem seem to be with a table in an update statement in the...more >>

I have a function that just stopped returning records all of a sudden. It would just return a time out message after about 20 seconds. I had to recreate the function from sratch, delete the noworking one and rename the new one and it works just fine,for now. I'm worried there is some underlyin...more >>

Hi, hope this is the right place for me to post this, but I couldn't find a more appropriate group. In a C# app, I have a large set of structured data that will map to multiple tables, associated with foreign keys, similar in structure to: The xxxID columns are Identity columns TABLE It...more >>

I'm trying to extract a numeric code part and the rest of a field in table tmpMachine. I have a DDL below that creates the tmpMachine table and inserts sample records. Can someone help me create a sql script that parses the machineCodeName field and inserts the code part into the machineCod...more >>

I need to add a Case statement to my WHERE clause and I'm having some trouble. Basically, I need to say when the country code is 'CHI' the inputdate has to be between @start1 and @end1 else (for any other country) the inputdate has to be between @start and @end I've tried a few variations ...more >>

I have a stored procedure that is calculating 3 different percentages. Two of the three are correct; however, one is not. The one that is not calculating correctly is the sum of the other two, so I thought I'd just add those together. However, it still is coming out incorrectly! Can anyone tel...more >>

I have run across a very strange problem in the SQLCLR. For some reason the SQLCLR crashes reporting: ..NET Framework execution was aborted. Another query caused the AppDomain JunkDb.dbo[runtime].13 to be unloaded or an unhandled .NET exception happened. The weird thing is that this cod...more >>

I am running a stored procedure that uses xp_cmdshell to move a file. I keep getting an error that the Directory can't b found. After countless checks and tests I assure you that the file exists in the location. I am wondering if the spaces in the directory name are throwing it. Can anybod...more >>

I wanted to verify the output of two stored procedures I hoped I could use EXCEPT. But that doesn't work. I tried execute usp_myProc @param=1, @param=2 except execute usp_myProc1 @param=1, @param=2 so I could see if the results were different. Then I tried: select * from ( usp_myProc ...more >>

Hi, Can anybody please help me in writing the syntax to view the structure of the table in sqlserver 2005. For example in oracle if we want to see the table structure we will use the command desc table name.In similar way is there any command to view the table structure we have created in sq...more >>

How can I determine if a table has an identity column using auto-increment? Thanks, Ben ...more >>

How can I get the datetime that a view was last altered? Can I tell who altered it? We are trying to unravel a mysterious bug introduced into an environment. In other words, I need to bust someone for making an object change outside of a build. Thanks! ...more >>

I have a memo field in a table that has some text that I need to replace with some new text. Is there a way to this using SQL? I do not want to do it manually because there is over a 2000 records. Thanks ...more >>

I have a situation where I am querying the master.dbo.sysaltfiles to return the path to the datafiles. What I am really interested in is the path...not the filenames. Example: select @DataPath = FileName From master.dbo.sysaltfiles WHERE name = @CurrentDB This returns: M:\Microsoft S...more >>

I having a fight with what I think is the message queue system when running stored procedures. If I have this as a sp print 'Handling First File' (do some processing on the first file) print 'Handling Second File' (do some processing on the second file) print 'Handling Third File' (do some...more >>

Is it possible to get a list of all users that accessed a particular Sql Server database? Thanks ...more >>

How would i add order by to this syntax below: "Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 & " and " & Box2 i have tried adding it to the end after box2 but its doesnt work. Any ideas? ...more >>

Hi guys, I have another question.... How do you select the next top N rows from the table? example: items ----------- 1 2 3 4 5 6 7 8 9 10 select top(5) items returns items 1-5 The question now is how do i return rows from 5-10 Many thanks for your help again, Maya. ...more >>

Hi, I am using an inner join on 2 tables. But he inner join can be based on either of the 2 columns. I am using the folliwng query. Is there a better way to write this query since this one runs very slowly, SELECT Sprint_Carrier_Report.ARC$, Sprint_Carrier_Report.ActivationDate, Sprint...more >>

Hi folks, I know that the standard is to provide a lot of DDL and DML to make clear what is being asked...but here's is a very straighforward occurence in business data and I wondered if there was a feature of Transact-SQL that made it unnecessary to use any inner queries/subqueries etc... ...more >>

I have a situation where one column in a database has become slightly corrupted (in the sense that I wrote incorrect data to this column). I've got a mixture of data in one of 3 formats: C:\path\subpath/\filename.bin C:\path\subpath\filename2.bin C:\path\subpath/filename3.bin As you can...more >>

Hi guys, I need to connect to a newly created sql 2005 express edition database. I tried to recycle the connection string used with sql 2000 server but it doesn't work anymore: dbConn.Connectionstring="DRIVER=SQL Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" & sSQLServer ...more >>

i am trying to build a report off of a table of order information. a simplified example is like so: create table orders ( orderid uniqueidentifier primary key not null, amount money not null, salespersonid uniqueidentifier not null, -- fk to the salesperson table datecreated datetime de...more >>

Hi , I have a table like this : row_id (integer), value (char(6)) I want to select data from this table only in a row So, if the rows are : 1 , 'AA' 2, 'BB' 3, 'CC' .... I want to create a view to obtain a result set like this 'AA', 'BB', 'CC' Ther's someone that can help me...more >>

Hi, I work at a university and am dealing with government data. We obtain a recordset from them called ISIRS. These are in a fixed length field format that I put into a table. This table has upwards of 450 fields (there was no better way to break it up, no relations exist). Each student ca...more >>

When I try to use the Upsizing Wizard in Microsoft Access I get an Overflow information message. I can not find an information describing the reason. -- Thanks, George...more >>

Hello guys, I have table "Shopping" that has 3 columns (Item, Category, Price) Is there a straight forward way in T-SQL to select the Item that has the highest price for each category as below: Item Category Price -------- ---------------- -...more >>

Dear all, I got a table with different columns of same client id as follows: e.g. ClientID Name Age Country 1 Peter 1 32 1 ...more >>

Hi, Is there any tool (freeware or licensed) which can automate code reviews for stored procedures based on various parameters. I am looking out for something similar to FXCop for stored procedures on SQL Server 2000. -- Afaq Choonawala...more >>

OK to get straight to the point, is it possible to use CLR stored procs from SQLExpress instance or from an MDF external database file ? If the answer is "no and no" then there's no need to read on, otherwise please read on..... I have a very simple stored proc... [Microsoft.SqlServ...more >>

Hello. I'd like to reveive notifications on the client application when table data is changed. How could I use Service Broker, queues and services? In my tests I have test queue (NotifyTestQueue), test service (NotifyTestService). If only one client application instance is running - all wo...more >>

Hey, Substring function doesn't accept a negative length: so in sqlserver2000 we added a case to first check the length, and if it is >= 1 then calculate substring. SELECT ALL CASE WHEN -3 >= 1 THEN {fn SUBSTRING('12345', 5, -3)} ELSE '' END In sqs2005 this stmt gives "Invalid length par...more >>

I am performing a number of complicated mathematical calculations as part of an update statement. When I execute the statement Sql Server throws one error 'A Domain Error Occured' and aborts the whole update. There are thousands of rows in the update I can't work out for which row the update...more >>

Hi , Could i check whether port#25 is required to be opened in order for SQL Mail & SQL Agent Mail to work with either Lotus Notes Client , OutLoook 2000 & above or the XP_SMTP ? appreciate any advice tks & rdgs -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Foru...more >>

I need some help with writing a User-Defined function in SQL Server 2000. I would like to return a space-delimited string, which contains the column data of several records from a table. Here's an example: table_fruit id textid --- ------ 1 APPLE 2 BANANA 4 ORANGE 8 PEAR 1...more >>

Hi there, i have the following problem: in a database i have to detect if a certain column in a certain table exists. I use the function 'ColumnAlreadyExists'. If not i have to create that column and then i have to fill this column. This is my code: ********************************...more >>

Hi everyone. I am very familiar with Access but I am new to SQL Server. I am trying to import data to a table in SQL Server that comes from an AS400. Everything is coming through except the date. The date field in the Excel sheet looks like 21706. When this imports into the table it changes t...more >>

Hi I have two databases: Customers and Operations. In Customers database I have made a view based on a few tables from both Customers and Operations (left join - customers without any operations). In the same database (Customers) I have created a stored procedure based on the view. Finally ...more >>

is there a simple way to remotely (using a ColdFusion application that can access the database) making a copy of an entire SQLServer2000 database into Access? I can make an Access DB with empty tables that match the SQLServer tables, they only have to be filled. Somebody suggested this ...more >>

Hi, I want to find some people in my SQLServer 2000 database. It's a quite large database, with approx 200 tables. Together with the person, I want some information attached to him. However, this information is in another table that can be reached via some other tables. My question is: ...more >>

Hi all. I have an application that uses MS SQL 2000 for which the data is crucial. This app works in company which db admin grants access to various people to operate with the database. Here comes the threat that anyone can modify the data so I can't prove that this modification is from a thir...more >>

how do i copy all the users, roles, permissions etc from on database to another database?...more >>