View Behaviour In Join

Jul 20, 2005

Hi Everybody,

I have a complex view, that includes a "group by" clause. I'm trying
to join this view with a table, in a very simple query.

The problem is that the optimizer is not using the table data as input
for the view (I expect this because I have arguments for the table,
but not for the view), but executing the view in a different step and
then joining to the table by a merge/hash join. This is obviously very
slow.

I tried to force nested loops by using hints but it still doesn't use
the table data as input.

Has anybody ever seen this?

Thanks in advance...

View 3 Replies

View Behaviour With Index.

May 12, 2006

Hi

If I have a view:

SELECT A,B
FROM tblTable
WHERE A = 1

And then I have a s-proc using that view:

SELECT *
FROM MyView
WHERE B > 6

My question: If I add an index to tblTable for the column B (not used in the view's WHERE clause, but used in the s-proc), will it have a performance improvement, because of the WHERE B > 6 on the view, assuming that this condition would benefit from the index if it were in the view itself.

I guess I could also put it this way: can an index on a column in a table improve the performance of a condition on a view using that table.

Thanks

Peter

View 2 Replies View Related

Locking Behaviour On JOIN

Jul 23, 2005

Hello all,I have what I hope is a simple question:Does SQL Server have an 'all-or-nothing' locking policy? Or does itacquire as many locks as it can and then sit and wait for the rest?Example:SELECT * FROM TABLE_A INNER JOIN TABLE_BON TABLE_A.dbid = TABLE_B.dbidNormally a SHARED lock would be acquired on both objects (pleasecorrect me if I'm wrong). But let's say TABLE_B was being updated byanother process at the same time, and so we couldn't get the sharedlock. Would the dbms go ahead and acquire the shared lock on TABLE_Aand then wait for the other lock, or would it not acquire any lock atall until locks on both TABLE_A and TABLE_B were available?I ask because I'm investigating a deadlocking problem that's drivingme mad :)Thanks,Tommy.

View 3 Replies View Related

Outer Join Behaviour

Nov 25, 2007

Hi

It may be the stupid mos question but i am stucked badly.

I am having data like this.

Table 1

Table 2

data

data

data

data
Now i am performing a outer join like

select * from table 1
full outer join table2
on table1.data=table2.data.

What output i am expecting is

Table 1

Table 2

data

data

NULL

data

NULL

data
But output i am getting is

Table 1

Table 2

data

data

data

data

data

data
Could you guys please help me in getting desired result in optimum way?

Thanks a lot in advance.

View 9 Replies View Related

Strange Behaviour Of Data Source View

May 29, 2008

I have a fact table which is a custim query in the data source view, I process and work with the data, after somewhile I make some changes in the data source view query but all my changes does not reflect in the cube after processing the same old data returned.

please help ASAP, I want to fix this issue

View 12 Replies View Related

Strange Behaviour Sort And Merge Join

Jun 29, 2007

I have encountered an annoying problem which causes the Merge Joins to lose records in the dataflow. The problem is caused by 2 unusual behavoirs.

1/ Sort of SSIS is not sorting the same as ORDER BY in SQL
example:

Code Snippet

CREATE TABLE [dbo].[table_2](
[test] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

With data as following:

Code Snippet1000
1-1.00.00.00
2000

When select this data with an order by like: select test from table_2 order by test
The result will be:

Code Snippet

test
1-1.00.00.00
1000
2000
If you sort the data by the SORT block of the SSIS the result will be:

Code Snippet

test
1000
1-1.00.00.00
2000This is annoying and dangerous, because it causes the next bug.

2/ Two datasources sorted by ORDER BY clause can give problems in a Merge Join.

If you have 2 data sources both correctly sorted by an order by in the query. When you join these 2 datasources with a Merge Join, you can lose some records in the dataflow.
This happens with larger datasets than examples above.

http://img340.imageshack.us/my.php?image=strangebehavior2tw1.png

When I join the datasources (see image ) inside SQL I will get a correct result of 15271 records.

Is this a bug which I should report? or is there a flaw in my logic?

View 6 Replies View Related

Inner Join With A View

Oct 20, 2006

anyone know how to inner join with a view?

View 1 Replies View Related

Replace View By Join

May 23, 2007

Hi all,

I have a list of Clients like:
Table_Client

ID
Name

10
Bill

11
Frank

12
Carl

13
Rita

14
Jan

15
Bonny

16
Bart

17
George

18
Ann

Now I want to ad some variable data to each client, so I have created a second table like:

Table_Client_Data

ID
ClientID
FieldName
FieldDataString
FieldDataInt
FieldDataDate
FieldDateBoolean

2
10
CustomerNr
g146

4
11
CustomerNr
g121

5
12
CustomerNr
g147

6
13
CustomerNr
g236

7
15
CustomerNr
g245

9
10
Dog

yes

10
11
Dog

No

10
12
Dog

yes

10
13
Dog

No

10
15
Dog

yes

Now I want to have the next table as result in one query:

ID
Name
CustomerNr
Dog

10
Bill
g146
yes

11
Frank
g121
No

12
Carl
g147
yes

13
Rita
g236
No

14
Jan

15
Bonny
g245
yes

16
Bart

17
George

18
Ann

First I have made a VIEW to create, I had used a inner Join

SELECT Table_Client.ID,
Table_Client_Data.FieldDataString AS CustomerNr,
Table_Client_Data_1.FieldDataString AS Dog

FROM Table_Client_Data

INNER JOIN
Table_Client_Data ON Table_Client.ID = Table_Client_Data.ClientID
INNER JOIN
Table_Client_Data as Table_Client_Data_1 ON Table_Client.ID = Table_Client_Data_1.ClientID

WHERE (Table_Client_Data.FieldName = 'CustomerNr') AND (Table_Client_Data_1.FieldName = 'Dog')

View_CliuentData

ID
CustomerNr
Dog

10
g146
yes

11
g121
No

12
g147
yes

13
g236
No

15
g245
yes

Now I join View_CliuentData with Table_Client and I have the right result.

Now my question,
Is there any way to skip the View an do this all in a join. I have tried several things but ... no result.

Tks Bart

View 5 Replies View Related

Join In A View - Simple Question

Jan 19, 1999

I've got a simple ( I think) question on views. I've got a view that has a table join in it.
With this view, we want to be able to perform updates, inserts, and deletes. At this time
we can do the updates and inserts, but not deletes. I've checked the permissions and
the users have SELECT, INSERT, UPDATE, and DELETE. Am I missing something or are
deletes just not possible in a view with a join?

CREATE VIEW update_bd_view
AS select
D.BD_ID, D.BD_DESC, T.BT_TYPE_TID, T.BT_TYPE_FID, T.BT_JOB_FID
FROM BILLING_DESC D JOIN BILLING_TIME T ON D.BD_ID=T.BT_ID
GO

thank you for your time!
Toni Eibner

View 1 Replies View Related

Cross Join/? Query In A View Help

Feb 4, 2004

View 2 Replies View Related

Using Union And Join In A Single View

Aug 8, 2006

I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5and table B has field 1,2,3,4,5. I want to do a union on these. (I havedone so successfully if I stop here) I also want to join table C whichhas field 1,6,7,8,9. I would like to join on field 1 and bring in theother fields. I can join table C to A or B. I can union table A and Bbut I do not know how to both union A and B then join C. Can someoneplease help me? Thanks in advance.

View 7 Replies View Related

SQL 2005 BUG - RIGHT OUTER JOIN On View

Oct 12, 2006

I am having no luck reporting this bug on the feedback link. I type in all information and click "Submit" and it just refreshes the page. So, here it is, I hope someone from MS will post this for me.

The problem appears to be a RIGHT OUTER JOIN on a VIEW causes the query to never return, or return very, very slowly. In 2000 SP4 the query returns in 2 seconds, in 2005 (2153) 64bit, it ran for 42 MINUTES before I killed it.

The is a duplication script to show a problem I have. This script uses the AdventureWorks database to demonstrate the problem. This is script is not the best, but shows the problem.

Please no comments on how to work around the problem. This query is generated by a user using and AdHoc reporting tool. I have NO control over how the user or the tool generates the SQL query.

The word "FAILURE" shows the original query which as far as I can tell never returns. The queries after, are different solutions, if I was able to change the user query, which I am not able to do.

-- Create Test Data Table CustomerListTom and Views

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewATom'))
DROP VIEW [ViewATom]

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewBTom'))
DROP VIEW [ViewBTom]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'CustomerListTom') AND type in (N'U'))
DROP TABLE CustomerListTom
GO

CREATE TABLE [dbo].[CustomerListTom](
[CustomerID] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RegionID] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] NOT NULL,
[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCountry] [dbo].[Name] NOT NULL,
[Phone] [dbo].[Phone] NULL,
[BillAddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCountry] [dbo].[Name] NOT NULL,
[BillPhone] [dbo].[Phone] NULL,
[ModifiedDate] [datetime] NOT NULL,
)

INSERT INTO CustomerListTom
SELECT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,

BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate
--,*
FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID

-- Create a big enough set of data for testing
DECLARE @i INT
SET @i = 1
WHILE (@i < 30)
BEGIN
INSERT INTO CustomerListTom
SELECT TOP 15 PERCENT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID+@i AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,

BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate + CASE WHEN @i > 3 THEN 10 ELSE -25 END + @i

FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID

SET @i = @i + 1
END

-- Cleanup - Delete Dups for PK
DELETE FROM CustomerListTom
WHERE CustomerID+RegionID IN (
SELECT CustomerID+RegionID
FROM CustomerListTom cu
GROUP BY CustomerID, RegionID
HAVING COUNT(*) > 1)

ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)

GO

-- Create Views
GO
CREATE VIEW ViewATom
AS
SELECT *
FROM CustomerListTom cu
WHERE cu.RegionID = '004'
UNION
SELECT *
FROM CustomerListTom cu
WHERE CustomerID NOT IN
(SELECT CustomerID FROM CustomerListTom c2 WHERE c2.RegionID = '004')
AND (CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID IN
(SELECT MAX(CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID)
FROM CustomerListTom
GROUP BY CustomerID))

GO
CREATE VIEW ViewBTom
AS
SELECT DISTINCT CustomerID
FROM CustomerListTom
GO

QUERY:

USE AdventureWorks

-- FAILURE
-- This query FAILS to return in over 15 mins, cancelled
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- SOLUTIONS
-- Change WHERE TABLEB to WHERE TABLEA, this query returns in less than 1 second
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEA.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Remove RIGHT OUTER on TABLEB, this Query returns in less than 2 seconds
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Drop PK and run ORIGINAL query, returns in less than 4 seconds

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO

SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Create PK with NONCLUSTERED and run ORIGINAL query, returns in less than 1 second

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO
ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY NONCLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)
GO

SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

View 5 Replies View Related

Join Stored Procedure And View

Jun 22, 2005

Hi everybody

View 3 Replies View Related

Self Join Using Instead Of Update Trigger On A View

Jan 11, 2008

Hello all...I'd appreciate any help on this one.

I created a View...the view looks at four seperate tables.

Next, I created an Instead of Update trigger on that view. It works fine...for a regular UPDATE...SET.

However, it throws an error when I try an update and self join based on that view:

Update T1
Set RateUsed = T1.RateUsed
From Taxroll..Taxroll T1 Join
Taxroll..Taxroll T2 on
T1.Asmt = T2.Asmt and
T1.Taxyear = T2.Taxyear
Where T1.Asmt = '123456789012'
And T1.Taxyear = 2007
And T2.RollChgNum = ''
And T1.RollChgNum Like '%X'
And IsNull(T1.RateUsed,'') > ''

Msg 414, Level 16, State 1, Line 2
UPDATE is not allowed because the statement updates view "Taxroll..Taxroll" which participates in a join and has an INSTEAD OF UPDATE trigger.

There are a few caveats:
First, I thought the join issue was in the view itself. I re-created the view using no joins...all subqueries and still get the error. Second, I re-created the Instead of Update Trigger with no joins, and still get the error.

Thanks!

View 1 Replies View Related

Cross Database Join In A Data Source View

Jun 21, 2007

Hi,

Is it possible to do a cross database join in a report services data source view? It doesn't look like it.

If not I was thinking of linking the table into the other database.

TIA,
Darren

View 3 Replies View Related

(SQL 2000) Incorrect Results When Using An Outer Join And A View!

Mar 29, 2008

Hi,
I have a query written in SQL 2000 which returns incorrect result. The query uses left outer join and a view. I read an issue related to this in one of microsoft bug report in this article http://support.microsoft.com/kb/321541.

However, there's a slight difference in the sympton second bullet wherein instead of a expression the query returns a fixed string for one of the column value.

Although the issue mentioned in article seems to be fixed. The later one still seems to be reproducible even with Service Pack 4. However, this issue doesn't appear in SQL Server 2005.

Here's the query to reproduce this error.

Code Snippetcreate table t1 (pk1 int not null,primary key (pk1))
create table t2 (pk1 int not null,label1 varchar(10) not null,primary key (pk1))
go
insert into t1 values (1)
insert into t2 values (2, 'XXXXX')
go
create view V as
select pk1, 'ZZZZ' as label1 from t2
go
select A.pk1 as A_pk1, B.pk1 as B_pk1, B.label1 as B_label1
from t1 as A left outer join V as B on A.pk1 = B.pk1
go

This query is similar to the one mentioned in the article except that in the SELECT clause of CREATE VIEW statement I am passing a fixed value for column "label1".

I just want to confirm that this is an issue and no fix is available for this so far.

Regards,
Naresh Rohra.

View 2 Replies View Related

Created View With Join Query - Unable To Fetch Details

Feb 12, 2014

I created a view with a simple join query

there are 2 rows with orderid as null, i am unable to fetch the details when i give WHERE Condition as

"select orderid from joins where orderid=NULL"

Query :

create view joins as
select A.customerid,A.Companyname,A.Contactname,A.City,B.OrderId from Customers A
left join orders B
on A.Customerid=B.Customerid

View 1 Replies View Related

Speed/efficiency Of View Vs. Common/nested Table Expression In A Join

Mar 2, 2008

i have been trying to determine which is the most efficient, with regards to speed and efficiency, between a view and a common/nested table expression when used in a join.

i have a query which could be represented as index view or a common table expression, which will then be used to join against another table.

the indexed view will use indexes when performing the join. is there a way to make the common table expression faster than an indexed view?

View 2 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses

Thank You

View 1 Replies View Related

Strange Behaviour

Oct 3, 2006

This is the actual statement displayed from Response.Write in classic ASP. INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '06-012497',TaskNum,TaskDesc,TaskMemo,Account,'2006-Oct-3',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum = '000002' There are 4 records returned from the SELECT part of the statement. In some situations, 4 records are inserted to WOTasks table, in others, only 1 record is inserted. I can't find out why 1 record, instead of 4, record is inserted. A form page submits the form to the save page using post method. The above statement is contained in the save page. When one of the form textbox is filled, 1 record is inserted. When the textbox is not filled, 4 records are inserted. You may think the textbox has something to do with the behaviour. I also think so but the content of the textbox does not affect the sql statement. In both cases, the insert statement is the same. In the actual codes, only strings in quotes are variables and the rest are hardcoded. When I run the statement in SQL Server, 4 records are affected. No such problem when connected with Access.The actual code belowSub AddTask(ByVal proc, ByVal wonum) Dim sSQL sSQL = "INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '" & wonum & _ "',TaskNum,TaskDesc,TaskMemo,Account,'" & curDate & "',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum='" & proc & "'" 'Response.Write sSQL:Response.End conn.Execute sSQL, , 128 End Sub

View 2 Replies View Related

Odd Order By Behaviour

Feb 24, 2004

Might be out to lunch, but I can't figure why this is being ordered the way it is:

SELECT '1' as t
UNION ALL
SELECT 'A' as t
union all
SELECT '['as t
ORDER BY t

I'd expect it to be 1, A, [ but instead it's [, 1, A [ is ascii 91 which is greater than both 1 and A, so why does it come first?

Karl

View 2 Replies View Related

Odd (slow Behaviour Of SQL)

Apr 13, 2006

Hi,

I am using MSDE and Analysis Services (lastest packs) and the same installation on the same machine has been working great for the last 18 months or so untill yesterday. Whenever I try to open a DTS (in order to edit it) the machine just goes into a coma.... I have tried to re-start many times but of no use.

Can someone kindly guide me what should I look for in order to solve this.

I will be very grateful for your help.

View 14 Replies View Related

Weird Behaviour On Net Use

Jul 5, 2004

When I run the command:
exec master..xp_cmdshell 'NET USE'
from the analyzer the box responds there are no entries in the list.

After that, I run the command:
exec master..xp_cmdshell 'NET USE Z: /DELETE'
after which the box responds with a "network connection could not be found."
and that's all okay.

The weird thing is:
exec master..xp_cmdshell 'NET USE Z: \MACHINESHARENAME'
results in a "The local device name is already in use.".

The machine in this particular case is the box itself. I have no problem accessing other disks on other systems. I can see the share using the view command. There's no maximum on the share itself and I can connect to the share using another sql box with the same user.

I don't know why it won't budge, worked before like a charm. After six months or so it just stopped. Anyone seen/solved this behaviour?

thanx,

View 5 Replies View Related

Query Behaviour

Dec 2, 2005

Hi there,
I wonder if one of you worthy folks can help me out with some strange behaviour exhibited by a piece of SQL. Its my first post here , so please be gentle. :)

Here is my simple example :-

<my test table>

create table test
(ind int,
message varchar(255))

insert into test (ind, message) values
(1,'date=01/06/2006')

insert into test (ind, message) values
(1,'date=20/12/2005')
insert into test (ind, message) values
(2,'test')

The first query is

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )

fine.... 2 rows

second query

select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

fine same 2 rows...

but If I try to combine the 2 clauses in

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

I get a
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Please can anyone help me on this?

thanks

Simon

View 9 Replies View Related

Can Someone Explain This Behaviour?

Jul 23, 2005

Hello All,The following script is reproducing the problem assuming you haveNorthwind database on the server.Please note it gives you the error message on line 12.USE tempdbGOsp_addlinkedserver 'Test17'GOsp_setnetname 'Test17', @@SERVERNAMEGOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_works]GOCREATE PROCEDURE This_works@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.OrdersSELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITgoIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_does_not]GOCREATE PROCEDURE This_does_not@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITGOPRINT 'This_works'EXECUTE This_works 0PRINT ' 'PRINT 'This_does_not'EXECUTE This_does_not 0Thanks for any help or hint,Igor Raytsin

View 8 Replies View Related

Sp_getapplock Behaviour After SP2

Mar 23, 2007

We have an application that executes a few queries against an SQL Server 2005 (64-bit) database. Since there can be several instances of the application running at any given time, and parts of the logic must be serialized, we've been using sp_getapplock and sp_releaseapplock. This has all been working fine since RC1 on which the system was released. However, after installing SP2 about a week ago, we have been having problems. The serialized portion of the code almost always stall now.

To see what is happening we've been using both Management Studio and Profiler. We have two applications running, let's call them A and B. Both create a prepared statement which begin with a call to sp_getapplock and ends with sp_releaseapplock. In between some tables are queried and inserts may be made in others. The accessed tables are never used anywhere else but in the serialized code. This is what is happening:

Application A: Calls sp_getapplock.
Application A: Queries a table.
Application B: Calls sp_getapplock.
Application A: Inserts a row in a table.
Application A: Calls sp_releaseapplock.
Application B: Waits indefinitely (or at least more than 4 hours, after which we killed the spid).

Profiler cannot detect any deadlocks when this is happening. There are no blocking operations according to Management Studio. I can see the application lock having been set when I look at the spid for Application B in Management Studio.

Since this started to occur frequently after installing SP2 and had not been seen before, we are wondering if any changes has been made that could cause this behaviour? Has anyone else had problems using application locks, where a query would stall indefinitely waiting for the lock to be released? How then did you resolve it?

Any suggestions or ideas are welcome,
Thanks,
Lars

View 9 Replies View Related

CTE Behaviour In SQL 2005

Feb 17, 2006

I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there? Have any one seen this or maybe can explain this?

USE AdventureWorks;

GO

DECLARE @SortExpression Varchar(50)

Set @SortExpression = 'SalesPersonID ASC';

WITH Sales_CTE (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by @SortExpression) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE;

WITH Sales_CTE1 (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by SalesPersonID ASC) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE1

View 1 Replies View Related

DBDate - Different Behaviour

Nov 20, 2007

When I run the package from business solution environment, DBdate cast converts my date column (correctly) into a European date format dd/mm/yyyy and as such is inserted into sql server table.

When I run a package as a job, the same date in inserted into the database as mm/dd/yyyy.

So, if I have 3rd January 2007 in the source, in the first case i'll find 03/01/2007 in the database.
When I run the package as a job, I find 01/03/2007 in the db.

The problem comes when I run different select statements - the 01/03/2007 behaves as if 1st March 2007

How can I avoid inserting of American data format into the db?

View 23 Replies View Related

Strange Behaviour

Aug 8, 2007

Hi,

In My report I have 2 drop down list called Cusip and Period as parameters and they are populated using a sproc

I have a list and in my list i have 2 textboxes which have the value First(Fields!FundNameNoFee.Value) and First(Fields!FundDescription.Value)

The Cusip drop list has the following values <All Cusips> whchi takes the Value Null, 33335454, 115544454.

If i give all cusips in preview mode Nothing populates in the report, but i just run the dataset i get 2 records with 33335454,1155445454.

and if i give individual cusip, the data shows up in the report...

So can some one tell me how can i show the values on the reports when All Cusips are selected....
Regards
Karen

View 3 Replies View Related

ADS - A Strange Behaviour

Oct 11, 2007

Hi to all!
i had tried to install ads on a windows 5. i am able to connect to the pocket database. however when i trie to connect to the database that's on desktop i see a little window (probably a message) without anything and when i tap ok i see a message telling that the connection to the desktop database was not done! i had made the configuration before.
can you tell me what is going wrong?

View 1 Replies View Related

SetRange Behaviour

Jan 22, 2008

I'm trying to use SetRange method on a table with multi-columns index, but I didn't get the result I was hoping for from the query.

Here is the DDL for the table

Code Block

CREATE TABLE [Products] (
[ProductId] INT IDENTITY(1,1) NOT NULL ,
[ArrivalDate] DATETIME ,
[Name] NVARCHAR(100) ,
[CategoryId] INT,
CONSTRAINT [PK_Products] PRIMARY KEY
(
[ProductId]
)
)
;
CREATE INDEX IDX_ProductArrival
ON [Products]
([ArrivalDate], [Name], [CategoryId])
;

--insert some sample data

Insert into [Products] ([ArrivalDate], name, categoryid) values ('2008-1-22 13:00:00', 'PC 1', 1);
Insert into [Products] ([ArrivalDate], name, categoryid) values ('2008-1-22 13:00:00', 'PC 2', 2);
Insert into [Products] ([ArrivalDate], name, categoryid) values ('2008-1-22 13:00:00', 'PC 3', 3);

And here is the code I'm using

Code Block

using System;
using System.Data.SqlServerCe;
using System.Data;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
using (SqlCeConnection conn = new SqlCeConnection(@"data source=d:mydb.sdf"))
{
conn.Open();

int counter = 0;
using (SqlCeCommand cmd = new SqlCeCommand("products", conn))
{

cmd.CommandType = CommandType.TableDirect;
cmd.IndexName = "IDX_ProductArrival";

object[] start = new object[3];
object[] end = new object[3];

start[0] = new DateTime(2008, 1, 22, 0, 0, 0);
start[1] = "PC 2";
start[2] = 2;

end[0] = new DateTime(2008, 1, 22, 23, 59, 59);
end[1] = "zzzzzzzzzzzzzzz";
end[2] = 3;

cmd.SetRange(
DbRangeOptions.InclusiveStart | DbRangeOptions.InclusiveEnd,
start, end);

using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
counter++;
}
}
}

conn.Close();
Console.WriteLine("Row count {0}", counter); //should show row count 2 but shows row count 3 instead
}

Console.ReadKey();
}
}
}
I'm setting the starting range to a date and a product name (PC 2) but it seems that the datareader only see the first array member and completely ignore the product name and show all 3 data instead of 2.

Am I missing something here?

Thanks

View 2 Replies View Related

SQL Express' Strange Behaviour

Oct 25, 2007

We have this webiste which uses SQL express as database engine. Sometimes certain features of the website stop working. Like membership provider and other database related things. I have described the problem in more details here: http://forums.asp.net/t/1172253.aspx
In consice the problem is: One query with fixed inputs does not always return the same results, though the data has not changed, you restart the SQL express and the problem resolives!
I think thats a problem with SQL express, because when you restart SQL express everything starts working. Our database is kinda big. Like above 500 MB with up to 50 concurrent users. And our machine got a 3.2 CPU with 512 MB of ram. And our application is the only application runing there.
What do you think please?

View 5 Replies View Related

Strange Query Behaviour

Aug 12, 2000

Hi...

Not sure what's wrong with the query or table, but I just can't get the result I want.

The column is varchar(35) and contains toll free no, like 18001234567...

But when I queried using the query below, I can't get any results. Same if I use 1*, 18*, 180*...

SELECT ... FROM ...
WHERE CONTAINS(toll_no, '"1800*"')

However, if I used 18001* I do get results.
Can anybody show me how to get result for these cases?

Thanks.

View 2 Replies View Related