I have a master table and a child table. I have created a foreignkey constraint on the child table with OUT the ON DELETE CASCADE option. Now when I delete records from the Master table, can I specify "on delete cascade" at that time. Or I can do that only if I specify when the foreign key is ...more >>

Hi, the following statement fails on SQL 2K5: if not exists(SELECT * FROM master.dbo.sysxlogins where Name=N'User') begin exec sp_addLogin 'User', 'test', 'User' end Does anybody know how to check if login already exists with SQL Server 2005? Thanks in advance Graham -- Graha...more >>

hello, As we are using SQL 2005 server we are unable to create a database, i am able to connect to the server but not able to login to the database.I used default login name "sa" and a null password but unable to login please help me on the same. While logging in i found an error. Micr...more >>

Hi guys, Do you know how to convert a datetime to UTC seconds: eg: nov 17, 2005 21:00:00 UTC in seconds: 1132261200 thank a lot -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200511/1...more >>

Is there any way i can get distinct values in one column from a join of 2 tables with the same columns? FOr example: table1, column fname frank bob bob dave frank A distinct yields frank bob dave table2, column fname bob alan dave alan I want to join these tables and g...more >>

hi all, can i delete first 'n' records in a table as how we do 'select top 5 * from tble' ?? thanks in advance Regards, C.C.Chakkaradeep ...more >>

I have a need to get the results of a stored procedure into a temporary table. The results of the query will be larger than 8000 characters so I can't use a variable. Following is what is being done in the stored procedure (findorphans): ========== PRINT 't1' SELECT mco_itn,chg_dt,chg_in...more >>

Hello all, I just got floored by a failed transaction working TOTALLY counter-intuitively. Luckily this is a development environment, or id be in sorry trouble. I have a procedure which takes records from a staging table, moves them to a final table, and then deletes them from the staging t...more >>

I noticed SQL2005 XE installation does not come with SQL Server Management Studio. If I install SQL2005 XE on a machine, how can I connect to it thru SQL Server Management Studio from my workstation? In sql2k, you can register a SQL MSDE thru EM, is there a change to this in sql2k5? I went...more >>

Hi, I have a table col1 char(5), col2 decimal(9,2) I have an SP create proc test( @val1 char(5), @val2 decimal(9,2)) when I execute the sp like exec test 'test','' I get the error Error converting data type varchar to decimal. How can I avoid this on COL2? Thanks ...more >>

I am having trouble with an Update statement with a few records that have NULL in the fields that I'm wanting to update. The PrimarySysAdminID field in the ServerInfo table has some records that have NULL in them and are ignored by the a.PrimarySysAdminID <> v.PrimarySysAdminID part of the s...more >>

I've just installed SQL 2005 and SSIS. Now I want to import a sql2k database using SSIS. All I've been able to do is create a package that only gives me an option to select the tables I want to import. When I ran it, it didn't even import the triggers or indexes. How do I get EVERYTHING ...more >>

Hi, I have a table that stores transaction entries for an item. How do I select the record that has the most recent transaction date i.e. record w/ most recent date in the TransactionDate field. I thought I could use LAST function but apparently it doesn't exist in SQL2K. What other opti...more >>

Hi guys, I created IGNORE_DUP_KEY on ssn key on a table. so if I have duplicate rows inserted to my table, duplicate ssn row will be ignored. but what I want to monitor is which ssn is ignored. Is it possible to insert duplicate row to an user-defined audit table "t_duplicate_ssn" afte...more >>

I have a third party product that creates a seperate physical DB in SQL Server for each project that the customer opens. To do real time reporting, I need to create triggers on tables in that new DB that copies data to a reporting DB. I can create a trigger on a table in the products admin D...more >>

i have 2 columns of type decimal that are divided and rounded and return zero if the divisor is zero. it all works fine until i try to AVG() them. now it seems my CASE to avoid dividing by zero is no longer working...or i'm just approaching it all the wrong way. my brain is a bit fried from s...more >>

WHen comparing to tables for records that exist in one table but not the other, which is faster WHERE NOT EXISTS or NOT IN? ...more >>

Can multiple stored procs be printed all at once....more >>

Hi guys, I'm in a design dilemma. Speed or physical memory? Explanation: I'm performing a row by row operation on a table. At first I implemented a helper function that uses a cursor to implement this row by row operation and discovered (I'm still pretty new at this, straight out of colle...more >>

I use OLE DB to call a stored procedure to add records to database tables within one transaction. I got the following errors: Description: Cannot create new connection because in manual or distributed transaction mode. Source Description: Microsoft OLE DB Provider for SQL Server Below is...more >>

I registered my clustered servers in Management Studio, but the green arrow does not appear. All my other servers show the green arrow. Is this a clustered server issue?...more >>

Hi all, We have one master table with all records of the second table but the second table doesn't contains all the records of the master table. Like: Master Table ID Value 1 A 1 B 1 C 1 D 2 A 2 B Second Table ID Value 1 A 1 ...more >>

Hi guys, I have a rather complex query which accepts 2 date parameters. When the dates are hard-coded the query runs in 2 seconds; however when a parameter is passed with the same values the query runs in excess of 2 minutes. I got around this by building the entire query into a va...more >>

ARRRRRRRGGGGGHHHHH!! Please can you help, I'm going round the bend with this. I have a simple and small table called STOCKCATS, which I need to query to get back a dataset in a particular order, but although it looks simple I can't get it to work. My table schema plus sample data to see t...more >>

Dear folks, I'm looking for a tool which must be able to compare two scripts and inform the differences, e.g, we've got two "create table" scripts and we'd like to know if are exactly alike or not. Let me know your doubts with this post and I'll try be more explicit if necessary Rega...more >>

Hi, Can I with SQL insert new column in a defined postion of table structure? How? I can't find any option or keyword for command ALTER TABLE for this purpose. Thanks Martin ...more >>

Hi, I am new at stored procedures. I am running SQL Server 2005 standard on XP Pro sp2. I have a database that has about 100 tables. For example I have a table called 'People' with a primary key called 'PeopleID', which is an int and also of column type 'Identity'. This 'People' table...more >>

Hello everyone, Here is what I'm trying to accomplish! I run a babysitter referral website, and I constantly receive emails from sitters asking if they're charging to little or to much to baby-sit. I want to provide them with a listing of what other sitters in their general area are chargin...more >>

If I have a table with 10,000 rows, no keys and no indexes, and I do a SELECT * FROM myTable how is the order of the table determined (if so) ? If I do the select now and again in year (without records, table og server setting is changed), will the records be listed in the same order or m...more >>

I am using Visual Studio .NET 2003 (Enterprise Architect) for programming.In an particular application the user has to upload his image.Here i am using HTMLInputFile Control to browse the image and finally upload the image to a designated folder(set by me) with proper naming conventions.The ap...more >>

I have a stored procedure that has the following format: CREATE PROCEDURE XXX1 ( @param1 Varchar(10) , @param2 INT OUTPUT ) AS DECLARE @X1 INT ... ... ... IF @X1 = y EXEC XXX1 SET @param2 = @X1 RETURN 0 When the condition IF @X1 = y is t...more >>

no cursor pls select the first product in order details for each order in northwind result set -orderid, productid -- thanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787...more >>

Hi people! Can anyone help me with this one? Using SQL Server 2000 and VB5, calling a stored procedure from VB code. When a message occurs like: Server: Msg 515, Level 16, State 2, Procedure Ve_KarticeIsplate, Line 235 Cannot insert the value NULL into column 'konto', table 'ibis.dbo.promk...more >>

Hi, I am very new to Microsoft Technologies, infact new to database world :)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i restore this backup by using SQL-Server 2000Enterprise edition, how should i go about it? Any help or comments will be highly appreciated. -Erland ...more >>

hello, i have two tables tableA typeInfo (example pc,dvd,cd..) week (ex. 43,44,45 ..) PriceRef (100,150 ....) tableB typeInfo (example pc,dvd,cd..) week (ex. 43,44,45 ..) Price (110,100 ....) Price/PriceRef - must be updated with a calcualted value ('110', '66 ,67' ....) ...more >>

I can't find any equivalent of Query Analyzer in sql 2005. How can I run scripts? I also miss the old DTS even though its UI kind of sucked. At least I could pick what tables, SPs, to import and also specify things like import users, roles, etc. 2005 is more like all or nothing. This real...more >>

My question is quite simple, but I haven't been able to find any good resources on web yet so I thought I'd give it a try here; Is it possible to generate an xml file using the "FOR XML EXCPLICIT"-clause and save the generated xml as a xml-file? sincerely, Kjetil Klaussen...more >>

1. I am using sql server express and visual studio 2003. I have been able to create the connection to the database in server explorer but cannot update table definitions. It gives an error that I may need a patch or changes would not be saved. So I have to do the table changes in SQL Expre...more >>

Hi All, I'm sending mails from SQL Server & SMTP using CDO.massage objects...But now i need to send using Lotus Notes Mail (IBM) Server. How to communicate to the Lotus Notes Server and send mails from SQL Server Store PROCEDURE ??? Thanks, - Elan *** Sent via Developersdex http://w...more >>

Dear folks, I can’t work out with that. I’m trying to add into my Enterprise Manager (Sql2k) a sql Server 6.5 but appears this error: Sql Server Registration failed because of the connection failure displayed below: Do you wish to Register anyway? [SQL- DMO] Sql Server SRVSMS2 must be ...more >>

I am looking for a query to get the PrimaryKey Name, primary key column for a particular table. The follwing query will give me the Key Name and the Table Name but not the column Name. Can I get the column name also in this query. select A.ID,A.Name as PrimaryKey, B.Name as MasterTable from ...more >>

Hi, I was just wondering if there are any web sites about SQL and its interaction with batch files. For example, can SQL determine when a batch file window has opened/closed etc. I am using xp_cmdshell and a 3rd party app [PsExec] to interact with a reporting tool to run report suites af...more >>

Can anyone assist me with a problem I have encountered? I have a table that contains comments associated with transactions. It has a many-to-one relationship with the other table. Thus there are many comments per transaction. I want to create a query that will select only the last comment ...more >>

Hi, i was wondering if someone could help me. when i debug a procedure within sql2k that contains insertions into temporary tables, i am having diffculty selecting from these tables to view the content. It seems that when i do a stepwise debug of a stored procedure (that contains inserti...more >>

Is there a difference between inserting data into a temp table vs a real table? I'm using an SP and I created a temp table. Then tried to INSERT and failed. Then, for troubleshooting, I created a real table and INSERT works fine. What characteristic about temp tables am I missing? Both tabl...more >>

New to SQL server 2000 and wondering if it's possible to define a check constraint on an input parameter (stored procedure). My input parameter(defined as smallint) should only accept the values 0, 1 or 2. Would appreciate any help. -- Message posted via http://www.sqlmonster.com...more >>

Hi, I am running a stored procedure on the database DB01, that SP got some statement looking like: use DB02 DROP Trigger DB02_USER.BOOK_DELETE It does not run and says: a USE database statement is not allowed in a procedure or trigger. If I replace the code by DROP Trigger DB02...more >>

Hi, I know that under oracle you can do simple minus calculations of set data extracted via select statements i.e. select client from tableA minus select client from tableB But how do ytou do this in sql? I tried two methods: method 1: select a.client from tableA a where a.client ...more >>

Hi all, Very quick question... Is there any way that I can switch off SQL2000 QA from returning error messages in my current session? Almost like a SET statement? i.e. If I hit a duplicate record etc... I know that I have hit it and I am handling it, but i just dont want to reutrn any me...more >>