Hi, I'm kinda new to using foreign keys, so don't kill me for the stupid question: Say, i have two tables: 1. CustomerInfo - with primary key column IDCustomer 2. CustomerOrders - also has IDCustomer column, but not unique Now I set a foreign key relation between these tables on IDCustomer ...more >>

How can I get a list of table names or SPs in my database? Not having much luck with BOL. thanks ...more >>

I thought that this was going to be easy but it's kicking my butt. I want to return a recordset showing the last PalletNo used by Plant based on the latest MfgTime. The results should look like this; 1 2005-03-18 14:15:00.000 1004 2 2005-03-18 14:20:00.000 1005 3 2005-03-18 1...more >>

Suppose you have two tables in a one to many relationship. How would = you build a query to return a row containing a value from the parent and = each value from the child separated by a comma (or some other = separator)? ParentVal | ChildVals ------------------------------------- Col...more >>

Hello, Is it safe to do this in a store procedure? Please share your comments or suggestions. Thanks! create procedure PerformAtomicDataCheck @ObjId varchar(100), as SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION // need to perform an atomic data operation here, mi...more >>

hi, all I have a table like: A B C 1 1 1 1 1 2 1 2 1 I would like the result looks like: A B 1 1 1 2 How to write this query? Thanks!...more >>

I am trying to create the stored proc below and am getting an "error 1012: The correlation name 'd' has the same exposed name as table 'd'. Can anyone help? Thanks. CREATE PROCEDURE [dbo].[mc_updPayInfoNHSDD] (@CheckDate [smalldatetime]) AS UPDATE PayInfoNHS SET NetPay = 0, PayrollBatc...more >>

Hi, I'm inserting into several table in a database with several insert statement. Do I need "If @@error != 0 rollback return" at the end of each insert? Or can I just do one at the end of all my inserts? something like "if @@error = 0 committ else rollback" Thanks, Al...more >>

Hi all, am using an access front end (XP) and ODBC. SQL Server 2k When doing an update statement in Query Analyser, QA will tell me I have violated a, eg. ForeignKey in table in database. Is there a way to return that error description from a Stored Procedure (the violation woul...more >>

Hello, Does anyone know the systax for the where clause, if i want to insert only records where it is not in the destination table? The destination table has 2 primary keys based on id and company. I've tried to used the following systax but that didn't work. insert into mytable2 select ...more >>

Hi! I'd like to implement my own log of events in sql server and would like to get exact sql statement text that invoked current running stored procedure. For example, if someone is deleting an item from an invoice, then in a stored procedure that's doing that I'd like to insert a row into...more >>

I have two tables - table a and table b I have simplified this as both tables have more then the 2 columns, so this needs to be an update! table a contains 2 columns, entry and billing_date table b contains 2 columns, entry and bill_timestamp I need to update table a, setting billing_d...more >>

trying to connect to remote MSDE database to manage it --msde instance with DISABLENETWORKPROTOCOLS=0 is installed and working. --local connection works to confirm username and password --firewall ports are configured correctly and listening --remote telnet connection works to confirm ip and ...more >>

I am having some problem creating a new DTS task or modifying an existing one. My goal is to transfer data from a table (tableX) to a text file (export.txt). I can not define the columns I want to transfer in the Destination tab under the Transfer Data Task Properties. If I use the Define Co...more >>

Hello Experts, Is this query is valid or not? it si giving me error invalid colum name "exceptCount" Update lib_RoundPerformance Set Successful = exceptCount select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount FROM dbo.MSC_ArchivedResult INNER JOIN ...more >>

We are using a Microsoft Access 2003 project connected to a SQL Server 2000 back end. It is multi-user, about 5 users have their own copies of the .adp and they all have rights to add and edit data. Al of the .adp projects are connected to the same SQL Server back end. I have a form frmCl...more >>

I am trying to resolve this issue for more than 3 hours. I have two tables Emp table and dept course table. I need to find employee who haven't took the course required by department or employee who already took all the required courses by dept. Employee table has this data emp_id name cour...more >>

I am bulk copying ascii files to database tables. I start running fine, as a matter of fact I copy the first 7 data files from a list to respective tables, but as soon as it starts copying the 8th table, right after the statement: i = oTable.ImportData(oBCP) I get the error line repeated 10...more >>

If I have a string field with a URL, how can I pull out the image name? Here are some examples and what I'd like returned: http://somewebsite.com/myimage.gif Return "image.gif" http://somewebsite.com Return "somewebsite.com" http://somewebsite.com/89sdmksdfds990s0s0s0kkkl.sldkfj.gif Re...more >>

I have a table recordset using rs.Open ...... I am adding records via rs.AddNew .... rs.Update Is the new Identity value available immediately after the Update method, for example rs.Update lngNewID = rs.RecordID where RecordID is the Identity field. Thanks. David ...more >>

I may be missing something obvious here (in fact it's probably a really simple problem, and it's probably me being silly - it is Friday after all), but I can't work out how to do this. I have a query that performs a full join on two tables, and pulls out four columns - two containing years,...more >>

I think the update statement listed below helped cause a deadlock. Please help me rewrite the statement in the correct update format. Thanks, update mt set status = 'K' from mvdtransfer mt inner join mvdheader mh on pt.mvdTrnsfrObHdrID = mh.HdrMID where status <> 'K'...more >>

Hi I have to install a lot of data wiich are initialized by an SQL file. My problem is that the file is too big. My SQL requests exceeds 64Ko and I cant start theses request in the SQL Editor of MS SQLSERVER 2K :-( I would like import this file like in MySQL or Oracle, with a command prompt...more >>

Can someone recommend a SQLServer documentation tool that will output database structure to help file viewable in a browser? Thanks. Alan ...more >>

What is the best way to accomplish a fully redudant database so that the secondary will automatically be used if the the primary fails? I see these options for the secondary (and other-ary) servers: (1) Shift the secondary responsibility to the storage device only - use RAID 1 for example....more >>

I have a table1(key, field1, field2, field3...). My primary key is "key". Then i create a unique index for fields "field1" and "field2". This works, but i want that sql server retrieve me the values thar are being duplicated. I'm using sql server 7.0. Ex: Table1 as the following values: ...more >>

I have had some problems with a web site that I am working on. The DBA set up all of the tables using the default collation sequence of SQL_Latin1_General_CP1_CI_AS HOWEVER, the recordset that is being returned is "out of order" according to my ASP code. For example, ASP believes that the str...more >>

I have a complex stored procedure (it's the strip-packing one, Dan) that uses several #temp tables along the way. There are typically 1000 to 50000 rows inserted into them during use. At the end of the procedure the final results end up inserted into permanent tables. Concurrency is not a facto...more >>

Coming from MS Access where you cannot live without queries, SQL is a bit different. I've used SP's for many of the things I'd used queries for in Access (like returing data for a report). But of course data sets returned by SP's are not editable (right?). But views puzzle me a bit. Since you...more >>

How can I programmatically know the default value of a column? TIA ...more >>

I have the T-SQL below and I am getting "Incorrect syntax near the keyword 'FROM'. " error. What am I doing wrong? Thanks. UPDATE dbo.WorkerTimesheets FROM dbo.BankInfo JOIN dbo.WorkerInfo ON dbo.BankInfo.EmployeeNumber = dbo.WorkerInfo.EmployeeNumber JOIN dbo.WorkerTimesheets ...more >>

I have a sysetem that's been running for over a year now with very few problems. Something occured to me the other day that I'm curious about. Users don't need to log into the MS Access front end (I have a similar set up with a different VB6 app as well). But of course the front end does need to...more >>

I have a function that has the following two parameters. When I try to compile I get an error on the GetDate(). If I remove the () from GetDate, it compiles but doesn't return the correct results. Example. ALTER FUNCTION dbo.SiteMaxFileByExt ( @Days int = -1, @Date datetime = GetDa...more >>

In query Analyzer I right click on a stored procedure and click edit. In the pass it displayed ALTER and now it displays CREATE. How can I put it back? Thanks, Dave...more >>

Hello All, I am stuck with a query ....If any one of you had any idea about the same.. I have two tables Table1 GroupID GroupName 1 Group1 2 Group2 3 Group3 -- -- Table2 GroupID Name Score 1 Name1 100 2 Name5 98 1 Name2 90 -- -- -- I want thejoined result set grou...more >>

I was just wondering if you knew what is encompassing or affecting every option on the flap 'Options' for 'Recovery' label (server properties): bulk-logged / full / simple Thanks in advance, ...more >>

Hi, I need to create a stored procedures that can pass table parameters in varchar format. for example: CREATE FUNCTION MyFunc (@table varchar(200) AS UPDATE @table + 'aaa' -> How can I do this? SET field1=tbl2.field2 FROM tbl2 WHERE tbl2.field3>10...more >>

Hi!, Can we use variables (declared with the DECLARE Statement), in SELECT Statement. e.g. DECLARE @tbnm varchar(20) SET @tbnm="tbbtmstr" SELECT * from @tbnm Secondly, how we can execute .SQL files in Query Analyzer. Thanks for your kind interest. PUSHPAK INDIA. ...more >>

Hi, Can anyone tell me how can I move my existing datatypes to Internationalized datatypes in SqlServer2000. i.e CHAR to NCHAR VARCHAR to NVARCHAR TEXT to NTEXT I am facing two Problems when I use Alter table Command to Modify the Col...more >>

I need ti getthe length of a string like this (varchar or char doesn't matter) 'AB12345678A '. The length is 14, but the function len('AB12345678A ') return 11. Len doesn't count the blank spaces. How con i get yhe length of my strings including blank spaces?...more >>

i have a table like below FieldA FieldB FieldC FieldD 1 AA BB CC 1 DD EE FF 2 DD EE BB 1 GG HH II 2 BB CC FF 2 N6 L...more >>