Hi, I have two tables ItemMaster and ItemCondition. One item can have multiple conditions in the form of rows in ItemCondition. But while returning these, I would like to return it in the form of columns. Is that possible? Below is the DDL of my table structure CREATE TABLE [dbo].[ItemC...more >>

HI to All! I have posted previously that database resotre giving me Device Activation Error which is solved. Now it is giving me the following error: Server: Msg 3270, Level 16, State 1, Line 1 An internal consistency error occurred. Contact Technical Support for assistance. Server: Msg 301...more >>

Hi to All! I m trying to restore a data base but it gives me the following error: Server: Msg 5105, Level 16, State 2, Line 1 Device activation error. The physical file name 'c:\Data\MyDB_Data.mdf' may be incorrect. Server: Msg 3156, Level 16, State 1, Line 1 File 'MyDB_Data' cannot be r...more >>

Hi I have a problem with collation orders as follows. The server collation order is set to Latin1_General_CI_AS. How ever a few databases, a small number, have the collation order of SQL_Latin1_General_CI_AS. Now, when some operations are requested from the interograting App, involvi...more >>

Hi, I am in the process of moving my back end to the SQL server. I have a test version in the SQLserver and the front end is in Access 2000 with linked tables. When the user enters a duplicate key (primary key) and the user tries to save the record in Access (using a save button), an erro...more >>

I want to put a check constraint on a table to enforce the following condition: Of four columns A, B, C, D one must have a value and the other three must be null. A is a varchar column, the others are integer. It seemed like I needed to create a bitwise Exclusive OR expression, with some...more >>

Can someone tell me how i can create an iif function to do the following? I need to look a field that has an amount in it. if it is 90K or less make it 1 if its 180 to 90001 make it 2 etc... So for every 90K in that amount add 1.. I tried to do a VB code to create a ceiling funciton but Ac...more >>

Hi, I need help with writing an SQL command. I need SELECT command with IF..ELSE senteces inside and I'm getting an error: "Incorrect syntax near the keyword 'if'." It should look something like this: SELECT column1, column2, (IF (1=1) SELECT 'Ok') FROM Table WHERE column1 = 3 Can an...more >>

hi i have the following variables. declare @viStock int set @viStock = 1000 declare @vdStartDate datetime declare @vdEndDate datetime set @vdStartDate = getdate() set @vdEndDate = dateadd(week, 10, getdate()) i want to insert into a temp ttable the following amount date 100 24-10-2...more >>

I need to query a linked server which contains a table of million records and need to fetch only the relevant records from the linked server. Any ideas? Please help...more >>

(I posted this to sqlserver.server newsgroup but did not get a helpful response) Globalization gurus, The problem is storing in a single column character data from mixed languages and then providing a search capability to find the best match given a search string in some arbitrary language...more >>

Hi, I'm trying to find out if any scheduled jobs use a file that is coming in from a main frame job overnight. All of the people who would know anything about this file were laid off, so now I'm supposed to clean up things. We have 73 sql servers where I work, and I'm looking to query eac...more >>

Hi there, I've fiddling with a query, and somewhere I made an error - Server: Msg 8152, Level 16, State 2, Procedure kn_insertCust, Line 25 String or binary data would be truncated. The statement has been terminated. Line 25 points to the first line in the query, and since it's INSERT cus...more >>

How can I do this so that if the user hits a cancel button, I can issue a SQL command to stop the query execution for that users' session ???. Ive configured the connection by default for a 90 sec time out, but they can still navigate other places leaving the query to run the full 90 secs when...more >>

I have a situation where I am casting text to varchar(8000) but it is not removing the newline characters in the Text data. How can we can do that ?...more >>

Hello All, I have a simple table that stores messages of different types from different sources. The definition of the table is shown below. I need to devise an efficient query to return a "list of the N more recent messages for a subset of sources within a specified time frame." create tab...more >>

I'm running a stored proc against MS SQL from my Delphi7 app with several parameters. . Program executes fine on win xp, fails on 2k. The exception message is "Bad variable type". Jeremy ...more >>

Hello NG Why wont this work? CREATE FUNCTION HBAisleRecords RETURNS CHAR(2) AS BEGIN RETURN 'AB' END How can I return a string value? I want to SELECT 2 charaters from a field in a record and return those characters to a stored procedure can this be done? TIAFAH Randy ...more >>

Can anyone please advise: What the easiest way of making a diary showing working days in SQL. I want to pass a month and year to a stored procedure and have it create a diary for the month showing working days that i can set data against before returning the recordset Regards John ...more >>

*** Sent via Developersdex http://www.developersdex.com ***...more >>

How would you do this statement: Select rank=count(*), Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then r.ItemName end as Description, Price, PurchaseQty, TotalPrice = Price * PurchaseQty from PurchaseDetail pd join PurchaseMaster pm on (pd.PurchaseMasterID = pm.Pur...more >>

What' s wrong with this code? Declare @UDName varchar(150) Declare @UName varchar(50) Declare @Domain varchar(50) Declare @EMail varchar(20) Declare @GName varchar(50) Declare @Description varchar(150) Declare @FName varchar(50) Declare @ShortName varchar(150) Declare UserCursor Cursor ...more >>

I'm trying to create a view in EM. SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active, dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty FROM dbo.wf_styles LEFT OUTER JOIN dbo.wf_bom ON dbo.wf_styles.code = dbo.wf_bom.style_code AND ...more >>

I'm selecting an ID, the count of the ID, and then the percentage of that count. Something like this: DECLARE @TOTAL NUMERIC SELECT @TOTAL = COUNT(*) FROM #TABLE WHERE ID IN (27) SELECT ID, [COUNT] = SUM( CASE WHEN ID IN(27) THEN 1 ELSE 0 END), [PERCENT] = (CAST(CAST(ROUND(((sum(CASE WH...more >>

I'm selecting an ID, the count of the ID, and then the percentage of that count. Something like this: DECLARE @TOTAL NUMERIC SELECT @TOTAL = COUNT(*) FROM #TABLE WHERE ID IN (27) SELECT ID, [COUNT] = SUM( CASE WHEN ID IN(27) THEN 1 ELSE 0 END), [PERCENT] = (CAST(CAST(ROUND(((sum(CASE WH...more >>

When monitoring errors concerning connection and updating data on my MS SQL Server2000 from a VB application I get these errors. -2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). -2147467259 [Microsoft][ODBC SQL Server Driver]Connection error -2147...more >>

From time to time I get blocked tables in my database and application stope working. So I try next example: declare @n int set @n=50 while @n>0 begin SELECT * FROM table1 INNER JOIN table2.... set @n=@n-1 end While this selects are working I try in other query analyzer w...more >>

I am trying to find an easy set based alghoritm for comparing data in two different tables. Given tables like this create table dbo.IwExclusions ( Id bigint not null, Number bigint not null, constraint IwExclusionsPKCO primary key clustered ...more >>

Hey! I wanted to show only 1 to 10 records (or the next 10 records ect,). I used 'select * from MyTable where Key between 1 and 10'. Key is a serial number that is automatically increased by one. One record has been deleted, so the first ten are really from 1 to 11 but my query shows only 9 (1 ...more >>

Is there an easy way to do get a row number in each row in a select statement? Something like: select rownumber,description price from lineorder order by row number Thanks, Tom ...more >>

I'm trying to create a function that insert data in a table and returns the primary key but I get the following error: Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function. Here is my script: create function [dbo].[AddStateProvince] (@StateProvinceCode [nv...more >>

Hi, Which is faster This (select evulates after) If(@amt <= (SELECT tran_amt from Transactions where trans_id = @trans_id)) or (select evaluates before) If(SELECT tran_amt from Transactions where trans_id) >=@amt Thanks ...more >>

Using SQL Server 2000. I'm looking for an solution that does what the code below should do, according to me. :) Explaination: CREATE TABLE Table2 ( Id int, Table1Id int, Type char(4), TableId int, StartDate smalldatetime, EndDate smalldatetime ) The idea is th...more >>

Hi All, I have been trying to learn and use SQL, and have learned quite a bit (relatively speaking!!). Still, cannot get the below query to work... if anyone could help out, I would be very grateful (and have learn't something new!!!) =) SELECT T0.ItemCode, T0.ItemName,T1.ItmsGrpNam, SUM(T3...more >>

Hi all, I am trying to run an INSERT SELECT statement. This should be an easy query!!! Code: SET IDENTITY_INSERT asmt_v1_areas ON INSERT INTO asmt_v1_areas ('asmt_v1_area_id','name','mid') SELECT assmnt_area_id, name, mid FROM assmnt_areas When i try to run this query i get an error m...more >>

Hello NG I am trying to use a declared variable of datatype INT within a SELECT TOP n * FROM table WHERE clause ORDER BY column Statement. I keep getting an incorrect syntax near my variable. What am I doing wrong? IF @varPRFI > @varMinimumLabels SELECT TOP @varMinimumLabels * FRO...more >>

Hi, Is it possible to PAD and justify output from a select statement? I have to build an input record from two fields in a table that I am concatenating together. The result is then going to be passed as a parameter value to a dll. The dll is looking for input of the form Field Name posit...more >>

I would like to get all records from table1 which ID not exists in table2: select a.* from tabel1 a LEFT JOIN table2 b ON a.ID=b.ID WHERE b.ID is null I don't get any record. If I try: select a.* from tabel1 a LEFT JOIN table2 b ON a.ID=b.ID AND b.ID is null I get all records. W...more >>

Dear Group, I have the following problem with an UPDATE trigger. I trigger a field (named CD_AccountPL) and i need to do some action depending if the entered value exist into a key formed by the triggered field itself plus a second field named CD_AccLocalPL. for example: ID CD_AccountPL ...more >>

Dear Group, I have the following problem with an UPDATE trigger. I trigger a field (named CD_AccountPL) and i need to do some action depending if the entered value exist into a key formed by the triggered field itself plus a second field named CD_AccLocalPL. for example: ID CD_AccountPL ...more >>

I have not been able to successfully shrink my transaction log. I'm receiving the infamous "the log file for database is full". I have tried: backup log database with truncate_only go dbcc shrinkfile (database _log,0) go I receive this message when I am importing a large excel spreadsheet...more >>

We have an SQL server with 5 databases on it. Databases 1 - 4 share data with database 5. The SP in databases 1- 4 directly link via the query to database 5. These databases have tables anywhere from 1 to 40 million records per table. Database 5 is where mist of the multi million row tables...more >>

Can anybody help me to write Cross Tab Query in SQL Server 2000. Thank you, ...more >>

I came across a select statement that has me all confused! There is a reference to a table in a case clause which does not appear anywhere else in the Select. In the example below, TableC is a regular database table which does not appear in the From or the where or anywhere else. I had no ide...more >>

I need to update some Access table from SQL. Is there anyway to connect to Access table and run the following query via a DTS package (I imported the tables into SQL to do some testing.) /* the access tables are HoldWithdrawalTracking, HoldOrderDetails_View*/ /*sql table tblHoldWithdrawalPU */...more >>

Im going mad trying and failing to figure this out . running SQl server 2000 (sp3). I have had code snippets and examples thrown at me, yet im missing something fundermental .. if i use the following code in TSQL i get a sucess on the executon of the DTS Declare @Packagename varchar(255) ...more >>

I have a table caled 'Test' with column caled 'Msgtext' on SQL 2000. The column Msgtest have ca 800 000 rows. One ex. on some rows on Msgtext column is: -Oct 19 08:24:17 security[success] 538 SRV1 User Logoff: User Name:Rolf Domain: -Oct 20 10:56:17 security[success] 540 SRV2 User Name: Do...more >>

Currently, I can chose two ways to implement a function: The easy way would lead to many (300) tables. The harder way would lead to considerable fewer tables. Is there a reason to chose the harder way? Are there any penalties, if you have many tables (with few entries) compared to few table...more >>

Dear all, I've got some problems with date and time (very silly, I know) select * from crm_1 where log < datepart(yyyy,getdate()) and log < datepart(mm,getdate()) and log < datepart(dd,getdate()) The aforementioned query doesn't find this value: 2005-06-09 08:27:17.810 CREATE TABLE...more >>

I have the following query which returns a sum of hours for each month for a specific year. select month(tDate) as month, sum(hours) as total from tablename where Year(tDate) = 2005 group by month(tDate) order by month This works ok, however, is there a way to specify the month start date ...more >>

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. --- I need the last identity generated for a specific table for the cu...more >>

I am tyring to use SqlHelper to execute a SP with some passed parameters. When I try the statement shown below I get an error "Object reference not set to an instance of an object" I suspect the connection string as I am not sure exactly what SqlHelper wants for that value? I've tried inclu...more >>

Hi Why do I have to compile a vb or cs file via the command line to integrate into Sql Server 2005. Why can't I include assembly compiled in Visual Studio? Many thanks Julia....more >>

Is there anyway to write this SP, --NOT-- using Dynamic SQL? Please don't ask me why I need this query (it's wacky), I just do. Any solution that does not use Dynamic SQL would be appreciated. Please notice the Input and Output on the SP, I need to pass in a tablename and receive the Max Pr...more >>

Hi I would like to know if I can improve the performanace of a stored proc which I have written so I was wondering if someone could tell me if there is a better way of writing the following two pieces of logic Declare @Count bigint Select @Count = Count(*) from orders where orderid = 10 If ...more >>