Hi, i have to insert values into two tables in one transaction. The first table (base table) has a primary key (type 'int') which is incremented automatically with each insert. The second table (detail table) contains a field (type 'int') which must get the value of the primarty key of the ...more >>

Hello, My DB has a table "resources" with a relationship one-to-many to a table "attributes". When I want to select all resources with their attributes (filtered by some criteria in the resources table), I have two possibility. 1) I could select the records in the resources table (SELECT * FRO...more >>

RecordStatusID RecordStatusName 1 Success 2 Failure I have to assign recordstatusid to variables for success and failure. select @i = RecordStatusID from Recordstatus where RecordStatusName = 'S...more >>

I'm just getting done with version 1 of a long term project that was planned for SQL 2k/MSDE (which one used by a customer depends on sevearl factors). We've hesitated to move to 05 because from what I understand sql express has more restrictions than msde. But I've noticed that the prices of SQ...more >>

Hi, I am moving over a query from MSAccess to MSSQL 2000 not 2005 unfortunately! I have never really used Access a whole lot and have come up against this query and really do not know where to start! ================== TRANSFORM Count(TMPRESULTS.RESULT) AS CountOfRESULT SELECT MGMTAREAS.MGMT_A...more >>

What TSQL can I use to find the server model such as Dell Poweredge 6400 or HP DL 385,etc. I see that info in msinfo32, but can I just extract that one piece of info using TSQL as I need to run it on many servers ? Is there some OLE object to extract it and if so, how do I write the TSQL fo...more >>

Microsoft SQL Server 2005 Service Pack 2 http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a kb http://kbupdate.info/ ...more >>

Hello At work we have some stored procedures which are nested.. sp1 sp2 and i'm creating sp1 sp1 ends with a select * from xxxxx sp2 has a exec sp1 but not a insert into sp2 ends with a select * from yyyy I want to make sp3 end with select * from zzzzz and all the "user"...more >>

I am running SQL 2000. The transcation log is growing overly huge, over 16G. Can you tell me how to truncate it? Thanks,...more >>

I have a query SELECT * FROM .... WHERE (DATEADD(dd, 0, @date) = CONVERT(varchar(10), SR_Service.Date_Entered, 101)) ERROR: Query execution failed for data set 'CurrentYear' Must declare the scalar variable @date. I use this same WHERE statement in other datasets within the same re...more >>

I am executing the T-SQL statement shown below in Management Studio (SQL Server 2005). When I execute this, an error should occur because a clustered index already exists on the table. What I don't understand is why I am seeing the error in M.S. because I've wrapped this in a Begin Try / Begin...more >>

Why do we not have many to many relationships and instead build an intermediary table ? Like to know why.. ...more >>

I followed the procedure outlined in http://support.microsoft.com/default.aspx?scid=KB;EN-US;q224071&ID=KB;EN-US;q224071 and all went well up to Item 7. of 'Moving the master database' - that is the line that tells you to restart Sql server. My SQL server will not restart - it shows (Conne...more >>

Can someone help me provide a logical E-R model of the different entities with relationship for collecting Application Inventory ? The goal is to collect all the Applications along with dependencies. On the top of my head, I can think of a Server/Hardware Table, an ApplicationName Table th...more >>

Hi Working with SQL Server 2005 I am trying to create a table with the following code create table department (dept_id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_department primary key (dept_id) ) but keep coming up with an error. I suspect th...more >>

Is the following statement performing a Left Outer Join? SELECT * FROM table1 JOIN table2 ON table1.brid = table2.brid ...more >>

I have a flat text ascii file with about 30 lines I'd like to read in and store in a single row column. I'm trying Bulk insert with no field and row terminators, but it is storing it in multiple rows. Is Bulk insert the way to go? if so, how? and even if possible, how will sql store my carria...more >>

I need to display my records grouped by Date only and not by time. I tried a couple of things including: SELECT count(*),Date = CONVERT(char(10), DatePosted, 103) FROM Applicant GROUP BY year(DatePosted),month(DatePosted),Day(DatePosted),DatePosted But this still groups by date and time. ...more >>

Hi, hope someone can help!!! I am programming with VS2005 with SQL Mobile Database. I've created a SQL mobile database and I can see it in my project. (MyTest1.sdf). I have the following code that should create a connection to the database: Imports System.Data.SqlServerCe Imports System.C...more >>

Can somebody tell me what this replace line is doing? changing | , < and > the front of the line?? select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>') Also, is there any way to enter a carriage return when entering data thorough the Open Table interface? ...more >>

Is it possible to run sqlcmd in silent, or quiet mode where the console window does not appear? I am setting up an automatic backup system using calls to sqlcmd -Q with the Windows Task Scheduler. The transaction log backups, especially, will likely be run as users are working on the applic...more >>

I have a network w/ Windows 2003 server and 2 XP Pro SP2 laptops. One laptop I can run the DTC with no errors (updates both the server and the local laptop). On the other laptop I get: The operation could not be performed because the OLE DB proviers 'SQLOLEDB' was unable to begin a distribu...more >>

Does SQL Server 2005 have some way to convert time from a column from GMT to EST? Our ERP system (BAAN) records all transactions in GMT, but our reports should reflect EST. ...more >>

I'm doing this assignment for school and don't know what to do. All he wants is the correct sql code for the follow:"we have too many kicking tees in inventory and need to plan our ordering a little better. How many Kicking Tees (ProductID: 17) did the customers buy each month last year? List...more >>

I've 3 tables : 1- Orders (Order_ID , Seller_ID , .....) 2- Sellers (Seller_ID, Name , ........) 3- Order_Pricing (Order_ID , Total_Price , ......... ) & The desired Query is : List top 5 Orders per Seller (the top total_price) Thanks ...more >>

Trying to undrestand Temp Tables here. I thought Temp Tables were prefixed with a generic thread ID that identified it to your session when you're signing on via SQL Query Analyzer. In the past, I have forgotten to delete my #Temp Tables when I was done with them. Are they dynamically dele...more >>

Hi My business scenario is that I want to create a view that read the data from 2 tables, depending on customer type. Say if the customer type is 1, I want to retrieve address information from table A, if the customer type is 2, I want to retrieve address information from table B. I think I can...more >>

Hi all, I am getting different results for the string data comparison provided by Order By clause of SQL provider and the comparison provided by CRT wcscmp/wcsicmp APIs. For example, I have two rows in nvarchar column with data as below. Row1 : AB Row2 : {AB If we sort this column in a...more >>

I am currently moving various SQL objects from an old server to a new server, the idea is to have a tidy up along the way since there is a great deal of redundant views and procedures on the old system. I have set up the new server exactly the same a the old server and have been moving object a...more >>

Hi, I want to be able to tell when a stored procedure or view was modified. Is there anyway I can find this out in SQL 2005? Appreciate the help Damon ...more >>

delete A from myTable A (nolock) where field = 'value' delete from myTable where field = 'value' ...more >>

Hi, I am experiencing increasing disk queues on one drive (which stores quiet a number of data files but not the indexes or trans. logs). I can use performance analyzer to check which stored proc is causing a lot of IO total, but I could not find out what would acutally cause the queues. Is t...more >>

Hi, the data model of my application is centered around one table ("items") with about 1 mio. rows, which receives very frequent updates plus there are a lot of selects happening on the table, too. Due to locking (although I use nolock everywhere) a select that joins in 5000 rows of this tabl...more >>

I host my DB for Blackberry on SQL 2000 and I am not at all an SQL guy. I am following KB931815 to see if my SQL is affected by DST. How can I check if I am running the Notification Services 2.0 with/without SP1? The article tells me to Stop event provider, notification services gener...more >>

I Want to insert data from Excel to SQL server, what it is the best way to do it. I can't use SSIS (DTS) beause we are using Excel just to display data. How to avoid concurency to data ? Thanks...more >>

Hi All, I have a very strange intermittent problem with MSSQL 2000. Im having a problem inserting data into a table. Occasionally data fails to insert in to the table, yet the id is skipped on the next working insert. eg. Insert - id =34 Insert - id = 35 Insert fails,no row can be found...more >>

Simple question but seems there is no answer to it at all! I have a table T1, and there is another table T2 where T2 has a foreign key on T1 (which allow NULL value), I want when I delete a row from T1 to set the foreign key in table T2 to NULL, I can't use the After Delete trigger on T1 cuz t...more >>

Hi, I appear to be getting a different degree of precision in Query Analyzer, depending on whether I square a number by straight multiplication vs. using POWER. 1. SELECT ( .0814 * .0814) = .00662596 2. SELECT POWER(.0814, 2) = .0066 3. SELECT POWER(.08140, 2) = .00663...more >>

Hello All, I have installed SQL Server 2000 on the my comp to try few things. However, what is happening is that when i restart the comp, it fails to logon. SQL service manager/database every things fails with message that Failed to start due to logon failure. During installation i have...more >>

Hi, I'm trying to retrieve data that will show where a person has joined a pension fund, left it, and then rejoined. Also, I will need to find people still active in the fund (i.e. they have a null date of exit) From the small results field below, I will need to recieve entries for ID 37.4...more >>

Hello, I'd like to import a varchar( ) into a Datetime format. This varchar( ) describes a duration that might be greater than 24h. Could someone help me to create generic conversion syntax? Thx. JN. select convert(datetime, '23:59') --> OK select convert(datetime, '23:00') + convert(dateti...more >>

Hi: I have this simple query, that updates dates with a time zone offset: UPDATE MainTable SET NewTime_EST = DATEADD(hh,b.EST_Offset,OldTime) FROM MainTable a INNER JOIN EST_OffsetTable b ON a.Site = b.Site AND DATEDIFF(d,OldTime,b.Date) = 0 (the "Site" indicates one a few cities-and it ...more >>

Hi, From where can I download Database Upgrade Test Toolkit? Ginny ...more >>

Hi My doubt is related to an output that i only can do it by using a temporary table and i didn't wanted to use that. So like i said before here i am to ask for a little hand/help on this subject. The above query represents the data i want to extract from the table So when appears two nam...more >>

I have table with primary key and index on columns: ID(varchar(12)),ID_POST(int) and RCO(char(3)). ..... CONSTRAINT [myConstraint] PRIMARY KEY NONCLUSTERED ( [ID] ASC, [ID_POST] ASC, [RCO] ASC ) ON [PRIMARY] But I have records with the same values in this columns. How is that possi...more >>

This Query works perfectly, what I need is an update Query which updates the, MaqrkDetails.Mark Column, in case it is null SELECT Mark = dbo.ExtractMark(XmlDoc_Mark) ,SumMark= dbo.ExtractMark(XmlDoc_Add) FROM Candidates c INNER JOIN MarkDetails MD ON c.ID = MD.ID INNER JOIN Address a...more >>

Hello there. I asked this question before and the problem hasn't been solved. Here is brief: I have database with 2.9 gb size of data and 1.4gb size of log. I have database maintenance plan that at 00:00am backup the data of the database and on 00:30 backup the log file. both of the...more >>

Hi, i'm trying to port a pivot query from access to sqlserver. I'm trying this query: SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS [Department-3], [4] AS [Department-4] FROM (SELECT IDMerce, Pezzi, IDMagazzino FROM Dispo...more >>