database errors: select

Showing posts with label select. Show all posts

Thursday, March 8, 2012

crystal report 9

how to select only the last row of a group in crystal report 9<moved thread>

Wednesday, March 7, 2012

hi All,

i m using Vb 6.0,M.Access and crystal report 9

i have one form in vb which have id and date field,
i want to select values from that vb form and seaching records in table in Access and display record in crystal report....

plz..help ma...it's urgent..

Thanx in Adavance

Regards..

Sabina Maniar

U.A.epass parameters to crystal report , then use those parameter in report query or in record selection formula.|||Hi..

Thankx to reply

i have already pased a para to crystal report ,

but i dont know how to pass CR para in vb in crystal viewer...

plz...if u have any related topics then plz..plz...reply me..

thanx again

Regards,

Sabina Maniar
U.A.E|||Set Report = crystal.OpenReport(App.Path & "\report1.rpt")

Report.DiscardSavedData
Report.Database.SetDataSource rs
Report.ParameterFields(1).AddCurrentValue ("aaa")

You can use this method to set parameters..

Regards,
Ahmed

Saturday, February 25, 2012

Crosstab?

Hi,
I run the following query "SELECT * FROM tbl_A", and get the following
result set:
Surname Firstname
Smith John
Smith Richard
Smith Simon
What I want to see in my result set is:
Surname Firstname
Smith John, Richard, Simon
How do I achieve this?
Thanks
Jon Derbyshire
Can you do it in the client? This kind of thing is extremely inefficient in
SQL Server.
If you must do it in SQL Server, refer to the following:
http://www.aspfaq.com/show.asp?id=2529
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
"Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
> Hi,
> I run the following query "SELECT * FROM tbl_A", and get the following
> result set:
> Surname Firstname
> Smith John
> Smith Richard
> Smith Simon
> What I want to see in my result set is:
> Surname Firstname
> Smith John, Richard, Simon
> How do I achieve this?
> Thanks
> Jon Derbyshire
|||Thats brilliant - Thanks
"Adam Machanic" wrote:

> Can you do it in the client? This kind of thing is extremely inefficient in
> SQL Server.
> If you must do it in SQL Server, refer to the following:
> http://www.aspfaq.com/show.asp?id=2529
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
> news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
>
>

Crosstab?

Hi,
I run the following query "SELECT * FROM tbl_A", and get the following
result set:
Surname Firstname
Smith John
Smith Richard
Smith Simon
What I want to see in my result set is:
Surname Firstname
Smith John, Richard, Simon
How do I achieve this?
Thanks
Jon DerbyshireCan you do it in the client? This kind of thing is extremely inefficient in
SQL Server.
If you must do it in SQL Server, refer to the following:
http://www.aspfaq.com/show.asp?id=2529
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
--
"Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
> Hi,
> I run the following query "SELECT * FROM tbl_A", and get the following
> result set:
> Surname Firstname
> Smith John
> Smith Richard
> Smith Simon
> What I want to see in my result set is:
> Surname Firstname
> Smith John, Richard, Simon
> How do I achieve this?
> Thanks
> Jon Derbyshire|||Thats brilliant - Thanks
"Adam Machanic" wrote:

> Can you do it in the client? This kind of thing is extremely inefficient
in
> SQL Server.
> If you must do it in SQL Server, refer to the following:
> http://www.aspfaq.com/show.asp?id=2529
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in messag
e
> news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
>
>

Crosstab, Pivot Query representation

Hello,

I need help with data representation.

I have a query :

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

The above query returns the following results:
USERLOGIN SOURCE DBUSERNAME

Don Crilly FC8 Don Crilly
Don Crilly ACT Donald Crilly
Don Crilly SFS Don Crilly

I need the output in following format:

USERLOGIN ACT FC8 SFS
-
Don Crilly Donald Crilly Don Crilly Don Crilly

Can you please guide me as to what I should do to achive the required results.

Thanks.

If you use SQL Server 2005:
SELECT * FROM (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) p
PIVOT (MIN(DBUSERNAME) FOR SOURCE IN ([FC8], [ACT], [SFS])) pvt
--Or You can use the following for earlier versions
select a.USERLOGIN
, min(case a.SOURCE when 'FC8' then DBUSERNAME end) as [FC8]
, min(case a.SOURCE when 'ACT' then DBUSERNAME end) as [ACT]
, min(case a.SOURCE when 'SFS' then DBUSERNAME end) as [SFS]
from (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) as a
group by a.USERLOGIN|||

If you're using SQL Server 2005, then you can use the PIVOT operator to do this.

For example, this will work for your query:

SELECT *

FROM (SELECT USERLOGIN, SOURCE, DBUSERNAME

FROM TBL_USER

WHERE USERLOGIN LIKE 'Don Crilly'

) SOURCEQUERY

PIVOT (

MIN(DBUSERNAME)

FOR SOURCE IN ([ACT], [FC8], [SFS])

) AS PIVOTTABLE

The restrictions for using the PIVOT operator are that the PIVOT clause must include an aggregate operator (the MIN in this case, which will work unless a single USERLOGIN and SOURCE combination can have multiple DBUSERNAMEs associated with it) and that the resulting column names (ACT, FC8 and SFS) must be listed explicitly (unlike MS Excel, for example, which just creates columns for every value in the pivotting column).

Hope that gives you a start at least.

Iain

|||

This query will do..

Select * from dbo.TBL_USER
PIVOT
(
Min(DBUserName) For Source in ([FC8],[ACT],[SFS])
) as T
Where UserLogin = 'Don Crilly'

|||Thanks a lot :)

Friday, February 24, 2012

Cross-server SELECT query

Hi,

Recently the powers-that-be migrated the largest databases from one server to another, more powerful server while keeping some support data on the original server. My problem: I need to run queries on tables spanning both database servers. Unfortunately, I can't find any documentation on how to do this. Does anyone have any ideas?

Thanks!lookup linked servers and sp_addlinkedserver.

cross-section select

I have table pool with columns:
USERID,QUESTIONID,ANSWERID
I have about 50 questions in table and each question can have couple
answers.
For example:
USERID QUESTIONID ANSWERID
----
1 1 2
1 2 1
1 3 5
.....
2 1 6
2 2 1
2 3 4
...and so on
Now I have to select all user id's which had answered with answerID=2 to
first question
and with answerID=1 to second question and so on...
So, I have array of questions: (1,2,4,5,8,12,17,18,20)
and
array of answers: (2,1,4,5,3,1,1,2,3)
Now, I have to find cross-section of users who has answered to required
questions to required answers.
Any idea?> array of answers: (2,1,4,5,3,1,1,2,3)
Ugh. Stop thinking about these in terms of arrays. They are not arrays!

> Any idea?
Canyou please provide better specs, because we don't know what "and so on"
means. Please see the following:
http://www.aspfaq.com/5006|||To Aaron and others who frequently contribute answers:

> http://www.aspfaq.com/5006
Often times when I post a question, I'm not looking for a solution to a
specific instance but more or less "is this a good idea". (See my recent
post on "Replace temp table with inline table-value function"). I try to
include as much information as is necessary to be clear about my problem.
In these cases, do you still want fully functional DDL, sample data, and
desired results? I'm not looking for a person to run through and test these
things, I'm simply looking for someone who has worked through a similar
issue and can say, "Yes, inline table-value functions are good for this" or
"No, here's why its bad and here's an alternative".
I'm not trying to undermine what you are requesting, because I understand
the value of what you are requesting, I simply want to clarify if that is a
blanket requirement (DDL, data, results) or a general requirement for
problems that need to be reproduced.
Thanks,
Mike|||> I'm not trying to undermine what you are requesting, because I understand
> the value of what you are requesting, I simply want to clarify if that is
> a blanket requirement (DDL, data, results) or a general requirement for
> problems that need to be reproduced.
No, I think you'll notice that I don't always post a link to that article,
only when it is relevant. Unfortunately, the information is included by
default, about 1% of the cases where it should be, so you might see the link
posted a lot. I think questions that are more general in nature (how do I
choose a primary key, should I use temp tables, what are the benefits of
identity vs. guid, etc) do not require any of this low-level detail, and you
won't be pressed for it, either.|||Hi Simon,
Please do post DDL, DML so that we can test our queries
I hope the Select statement at the end will solve your pupose
create table QuesAns(USERID int ,QUESTIONID int,ANSWERID int)
insert into QuesAns values( 1 , 1, 2 )
insert into QuesAns values( 1, 2 , 1 )
insert into QuesAns values( 1, 3 , 5 )
insert into QuesAns values( 2, 1 , 6 )
insert into QuesAns values( 2, 2 , 1 )
insert into QuesAns values( 2, 3 , 4 )
select * from QuesAns
-- An array(sorry all SQL standard supporters) can be easily replaced
by a Table like this
create TABLE CORRECTANS
(
QUESTIONID INT,
ANSWERID INT
)
INSERT INTO CORRECTANS VALUES(1,2)
INSERT INTO CORRECTANS VALUES(2,1)
SELECT QuesAns.USERID,COUNT(*) TotalCorrectAns FROM QuesAns ,
CORRECTANS
WHERE QuesAns.QUESTIONID = CORRECTANS.QUESTIONID
AND QuesAns.ANSWERID = CORRECTANS.ANSWERID
GROUP BY QuesAns.USERID
drop table QuesAns
DROP TABLE CORRECTANS
With warm regards
Jatinder Singh

Sunday, February 19, 2012

Cross-database UDF with a parameter

Will this be supported in SQL Server 2008:

Select * From [ServerName\InstanceName].DatabaseName.SchemaName.FunctionName('Parameter')

Thanks.

David Walker

There are currently no plans to include this functionality in SQL Server 2008. We are considering this for a future release. A request for this functionality has already been filed on connect.microsoft.com here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276758. If you haven't already done so, please indicate how important this functionality is for you using the 'Rating'.

Cross-database UDF with a parameter

Will this be supported in SQL Server 2008:

Select * From [ServerName\InstanceName].DatabaseName.SchemaName.FunctionName('Parameter')

Thanks.

David Walker

cross-database query from ASP.NET

How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?You don't need 2 connections. Are your 2 databases on the same server?

If they are on the same server, the syntax would be like this:


SELECT
 D1.column1,
 D2.column2
FROM
 database1.dbo.Table1 D1
INNER JOIN
 database2.dbo.Table2 D2 ON D1.ID = D2.ForeignKey

If they are on different servers, the syntax would be like this:


SELECT
 D1.column1,
 D2.column2
FROM
 Server1.database1.dbo.Table1 D1
INNER JOIN
 Server2.database2.dbo.Table2 D2 ON D1.ID = D2.ForeignKey

You might encounter this error:Could not find server 'Server2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. which means that the system stored procedure sp_addlinkedserver would need to be run to allow access to Server2 from Server1. SeeSQL Server Link Server Performance Tips for some information pertaining to linked servers.

Terri

Cross table copy working in Query Analyser, but not from code

I'm trying to copy a row from one table to another for audit purposes
using a 'INSERT INTO x SELECT y FROM z' statement. This works
absolutely fine in query analyser, however, when running the exact same
statement from code (.NET via oledb), it fails with the error:
An explicit value for the identity column in table 'x' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
or when specifying all columns:
Cannot insert explicit value for identity column in table 'x' when
IDENTITY_INSERT is set to OFF
Table 'x' has no identity columns, table 'y' has one (integer ID)
identity column.
Anyone have any ideas why this statement would work in the query
analyser, and not in code?
(SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)I would guess you are connected to different databases/instances and the
table schema are different. If needed, you can run a Profiler trace to
verify this.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137432432.481007.178730@.f14g2000cwb.googlegroups.com...
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
> (SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)
>|||That's definately not the issue. I'm fairly experienced with database
applications, despite using .net. I've just never come across an
instance whereby the query analyser gave different results to the oledb
components.
Does either the Query Analyser or oledb .net component do anything
unusual behind the scenes? Or is it a possibility that this is
happening because in my program it is taking place inside a
transaction? (it's the first thing in the transaction, and the
exception is thrown immediately on adding the command to the
transaction)
Thanks for the reply Dan|||A Profiler trace should show all that is going on. I can't think of
anything that would cause different behavior for a single statement like
this. The error clearly indicates the target table has an identity column.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137437890.787728.312550@.o13g2000cwo.googlegroups.com...
> That's definately not the issue. I'm fairly experienced with database
> applications, despite using .net. I've just never come across an
> instance whereby the query analyser gave different results to the oledb
> components.
> Does either the Query Analyser or oledb .net component do anything
> unusual behind the scenes? Or is it a possibility that this is
> happening because in my program it is taking place inside a
> transaction? (it's the first thing in the transaction, and the
> exception is thrown immediately on adding the command to the
> transaction)
> Thanks for the reply Dan
>|||Rory (rory.smith@.gmail.com) writes:
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
One thing to check is that there are not two table x in the database.
Own owned by dbo, which I assume that you run as from Query Analyzer,
and one owned by the user that you connect with from the application.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland - you were right - I'm unsure of where the 'phantom' table came
from - I created the database from a script which only has one instance
of the table. Anyway, all sorted now.
Many thanks to you both

cross table

Sir,

My query is

select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/11/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/12/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/13/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/14/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/15/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/16/2006'
group by task_date

My out put is

Date Program Design Upload Testing Meeting Others

2006-09-11 00:00:00.000 42 0 0 8 2 1

2006-09-12 00:00:00.000 77 0 0 4 0 0

2006-09-13 00:00:00.000 56 0 0 8 0 1

2006-09-14 00:00:00.000 63 0 0 6 0 1

2006-09-15 00:00:00.000 63 0 0 6 0 1

Now i want in below format

2006-09-11 2006-09-11 etc

Program 42 77

Design 0 0

Upload 0 0

Testing 8 4

Meeting 2 0

Others 1 0

Total 53 81

How to convert in this format .

--From your output as table:transposetable$:

SELECT ISNULL(cat, 'Total') ,SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',

SUM([9/15/2006]) AS '9/15/2006'

FROM (SELECT cat, MIN(CASE WHEN t.tDate = '9/11/2006' THEN Program END) AS '9/11/2006',

MIN(CASE WHEN t.tDate = '9/12/2006' THEN Program END) AS '9/12/2006',

MIN(CASE WHEN t.tDate = '9/13/2006' THEN Program END) AS '9/13/2006',

MIN(CASE WHEN t.tDate = '9/14/2006' THEN Program END) AS '9/14/2006',

MIN(CASE WHEN t.tDate= '9/15/2006' THEN Program END) AS '9/15/2006'

FROM

(SELECT 'Program' as cat, tDate, Program FROM transposetable$

UNION ALL

SELECT 'Design', tDate, Design FROM transposetable$

UNION ALL

SELECT 'Upload',tDate, Upload FROM transposetable$

UNION ALL

SELECT 'Testing', tDate, Testing FROM transposetable$

UNION ALL

SELECT 'Meeting', tDate, Meeting FROM transposetable$

UNION ALL

SELECT 'Others', tDate, Others FROM transposetable$

) t

GROUP BY cat ) x

GROUP BY cat

WITH ROLLUP

|||

--SQL Server 2005, Louis's idea. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326847&SiteID=1

WITH myCTE as(

select tDate, cat, value

from ( select tDate, Program, Design, Upload, Testing, Meeting,Others

from transposetable$) p

UNPIVOT

(value for cat in ([Program], [Design], [Upload],[Testing],[Meeting],[Others])) as unpvt)

SELECT ISNULL(cat, 'Total'),SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',SUM([9/15/2006]) AS '9/15/2006'

FROM

(select cat, tDate,value

from myCTE) as rotated

PIVOT

(SUM(value) FOR tDate in ([9/11/2006],[9/12/2006],[9/13/2006],[9/14/2006],[9/15/2006])) as pvt

GROUP BY cat

WITH ROLLUP

|||

Hi,

have you looked at the UNPIVOT function?

Sten-Gunnar

Cross Tab SELECT

I am using MS-SQL2000, on a table called tblItems with these columns:
ID Descripction
-- --
0012 Washer
0145 Oven
2345 Heater
7834 Fan
and other called tblFeatures
ID Description
-- --
0012 White
0012 Large
0012 Electrical
0145 Brown
0145 Small
0145 Hybrid
I want to make a query with next result
ID Item F1 F2
F3
-- -- -- --
--
0012 Washer White Large
Electrical
0145 Oven Brown Small Hybri
d
.
.
.
How can I do this? Please, help me.
Beforehand, thank you very much.
Luis Garcia
IT ConsultantLuis, try:
SELECT ID, Item
, MAX(CASE WHEN pos = 1 THEN Feature END) AS F1
, MAX(CASE WHEN pos = 2 THEN Feature END) AS F2
, MAX(CASE WHEN pos = 3 THEN Feature END) AS F3
/* add more items here */
FROM (SELECT F.ID, I.Description AS Item, F.Description AS Feature,
(SELECT COUNT(*)
FROM tblFeatures AS F2
WHERE F2.ID = F.ID
AND F2.Description <= F.Description) AS Pos
FROM tblItems AS I
JOIN tblFeatures AS F
ON F.ID = I.ID) AS D
GROUP BY ID, Item;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"LUIS" <LUIS@.discussions.microsoft.com> wrote in message
news:6D153B9E-5373-49B3-8E27-AD6B69F27B35@.microsoft.com...
>I am using MS-SQL2000, on a table called tblItems with these columns:
> ID Descripction
> -- --
> 0012 Washer
> 0145 Oven
> 2345 Heater
> 7834 Fan
> and other called tblFeatures
> ID Description
> -- --
> 0012 White
> 0012 Large
> 0012 Electrical
> 0145 Brown
> 0145 Small
> 0145 Hybrid
> I want to make a query with next result
> ID Item F1 F2
> F3
> -- -- -- --
> --
> 0012 Washer White Large
> Electrical
> 0145 Oven Brown Small
> Hybrid
> .
> .
> .
> How can I do this? Please, help me.
> Beforehand, thank you very much.
>
> --
> Luis Garcia
> IT Consultant|||Itzik Ben-Gan
Thank you very much. Your answer was very useful to me.
Luis Garcia
IT Consultant
"Itzik Ben-Gan" wrote:

> Luis, try:
> SELECT ID, Item
> , MAX(CASE WHEN pos = 1 THEN Feature END) AS F1
> , MAX(CASE WHEN pos = 2 THEN Feature END) AS F2
> , MAX(CASE WHEN pos = 3 THEN Feature END) AS F3
> /* add more items here */
> FROM (SELECT F.ID, I.Description AS Item, F.Description AS Feature,
> (SELECT COUNT(*)
> FROM tblFeatures AS F2
> WHERE F2.ID = F.ID
> AND F2.Description <= F.Description) AS Pos
> FROM tblItems AS I
> JOIN tblFeatures AS F
> ON F.ID = I.ID) AS D
> GROUP BY ID, Item;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "LUIS" <LUIS@.discussions.microsoft.com> wrote in message
> news:6D153B9E-5373-49B3-8E27-AD6B69F27B35@.microsoft.com...
>
>|||Yes you can walk..but if you want to ride check out Rac:
for dynamic crosstabs.(See the @.rank parameter).
www.rac4sql.net|||Dear Itzik:
Here, once again, Luis. Sorry! I was in a hurry when I saw your answer and
implemented it as soon as I can in my application. I have noted that you are
a very experimented developer, and very succeful too. Thank you very much fo
r
your time dedicated to me. I did not finish sorprised by the query you gave
me, it is extraordinary by utility and simplicity. I wonder for some
literature to learn more about, but I already access your links and see your
books. Nothing more to say. I hope to keep in touch with you and increase
knowledge.
Once again, thank you very much
Luis Garcia
IT Consultant
"Itzik Ben-Gan" wrote:

> Luis, try:
> SELECT ID, Item
> , MAX(CASE WHEN pos = 1 THEN Feature END) AS F1
> , MAX(CASE WHEN pos = 2 THEN Feature END) AS F2
> , MAX(CASE WHEN pos = 3 THEN Feature END) AS F3
> /* add more items here */
> FROM (SELECT F.ID, I.Description AS Item, F.Description AS Feature,
> (SELECT COUNT(*)
> FROM tblFeatures AS F2
> WHERE F2.ID = F.ID
> AND F2.Description <= F.Description) AS Pos
> FROM tblItems AS I
> JOIN tblFeatures AS F
> ON F.ID = I.ID) AS D
> GROUP BY ID, Item;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "LUIS" <LUIS@.discussions.microsoft.com> wrote in message
> news:6D153B9E-5373-49B3-8E27-AD6B69F27B35@.microsoft.com...
>
>|||Luis,
No need to apologize; it's my pleasure. Your original thank you was more
than I could handle.
I'm actually lost for words when the subject matter is not SQL; and as
proof, replying to you took me more time then producing a transitive closure
of a graph with a recursive query. ;-)
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"LUIS" <LUIS@.discussions.microsoft.com> wrote in message
news:CB89285B-CD92-4DA5-B91D-AD668FEC358F@.microsoft.com...
> Dear Itzik:
> Here, once again, Luis. Sorry! I was in a hurry when I saw your answer and
> implemented it as soon as I can in my application. I have noted that you
> are
> a very experimented developer, and very succeful too. Thank you very much
> for
> your time dedicated to me. I did not finish sorprised by the query you
> gave
> me, it is extraordinary by utility and simplicity. I wonder for some
> literature to learn more about, but I already access your links and see
> your
> books. Nothing more to say. I hope to keep in touch with you and increase
> knowledge.
> Once again, thank you very much
> --
> Luis Garcia
> IT Consultant
>
> "Itzik Ben-Gan" wrote:
>

Thursday, February 16, 2012

Cross referncing databases

Does Referencing databases in queries make queries any
slower
For ex
use DB2
go
select col1 from DB1..Table1
will above be any slower than
use DB1
go
select col1 from DB1..Table1
sanjayI don't think it would make much difference in speed.. But having cross
database references does introduce a level of complexity that you might not
otherwise have... Since each database can be restored independently of the
others, they could get 'out of sync' ... leaving you with big referential
problems... So , I *try* to keep all references local whenever possible... I
have seen some major applications which have 20-30 databases for a single
app and tons of cross database references, these companies are successful in
their products, but it makes my skin crawl...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9a6b01c34636$605f4320$a401280a@.phx.gbl...
> Does Referencing databases in queries make queries any
> slower
> For ex
> use DB2
> go
> select col1 from DB1..Table1
> will above be any slower than
> use DB1
> go
> select col1 from DB1..Table1
> sanjay
>
>

Cross Join without Table?

I have the following structure with remote select permissions; I cannot create temp tables or use stored procs:

tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk

I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end

But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:

event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown

Thanks for any help,
jbDear Lord, save us from those who would require us to eat sphagetti with chopsticks, swim with boxing gloves, and develop databases without stored procs or temporary tables. And forgive them, for they know not what the hell they are doing.

Fortunately for you, it is probably possible to get a reasonable solution for your problem even without using stored procs or temp tables. :(

I think this will work...

Select SubQuery.event_pk, isnull(SubQuery.myCount, 0) as myCount, RegTypes.countCat
From (Select 'Employer' as countCat
UNION
Select 'Individual' as countCat
UNION
Select 'Both' as countCat) RegTypes
Left outer join
(Your Query Goes Here) SubQuery
on RegTypes.countCat = RegTypes.countCat|||You're to best. Didn't realize you could "create" a derrived table without selecting at least one field from a real one. Thanks so much.

It is by coffee alone I set my mind in motion. It is by the beans of java that thoughts acquire speed. The hands acquire shaking. The shaking is a warning. It is by coffee alone I set my mind in motion.

Cross Join Without Table?

Tuesday, February 14, 2012

Cross Database Permission

I have a stored procedure that I created on one database with permission
granted to a select group of users. The proc grabs information from another
database on the same server. When users with permissions to the proc try to
run it, they get permission errors saying that they do not have permissions
to access the other database that is referenced in the proc. It was my
understanding that when you run a proc, it runs under the context of the proc
creator (in this case dbo), otherwise you have to grant select, insert,
update, etc. on all tables that your proc references. Does this not work
cross database?Clark,
You do not mention which version of SQL Server you are using, but both 2000
and 2005 have in the Books Online index an entry for "cross-database
permissions" which you should read.
Basically, the ownership-chain is broken at a database boundary as I quote:
"Cross-database permissions are not allowed; permissions can be granted only
to users in the current database for objects and statements in the current
database. If a user needs permissions to objects in another database, create
the user account in the other database, or grant the user account access to
the other database, as well as the current database."
There is a setting to turn on "cross database ownership chains", but that is
not recommended due to the (potentially serious) security side-effects.
RLF
"Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
news:92FF4E5C-9690-41AE-A4EA-362018397F79@.microsoft.com...
>I have a stored procedure that I created on one database with permission
> granted to a select group of users. The proc grabs information from
> another
> database on the same server. When users with permissions to the proc try
> to
> run it, they get permission errors saying that they do not have
> permissions
> to access the other database that is referenced in the proc. It was my
> understanding that when you run a proc, it runs under the context of the
> proc
> creator (in this case dbo), otherwise you have to grant select, insert,
> update, etc. on all tables that your proc references. Does this not work
> cross database?|||It is running on SQL Server 2000 with the allow cross-database ownership
chaining disabled. So I take it dbo does not have cross database access,
which makes sense now that I think about. Thanks.
"Russell Fields" wrote:
> Clark,
> You do not mention which version of SQL Server you are using, but both 2000
> and 2005 have in the Books Online index an entry for "cross-database
> permissions" which you should read.
> Basically, the ownership-chain is broken at a database boundary as I quote:
> "Cross-database permissions are not allowed; permissions can be granted only
> to users in the current database for objects and statements in the current
> database. If a user needs permissions to objects in another database, create
> the user account in the other database, or grant the user account access to
> the other database, as well as the current database."
> There is a setting to turn on "cross database ownership chains", but that is
> not recommended due to the (potentially serious) security side-effects.
> RLF
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:92FF4E5C-9690-41AE-A4EA-362018397F79@.microsoft.com...
> >I have a stored procedure that I created on one database with permission
> > granted to a select group of users. The proc grabs information from
> > another
> > database on the same server. When users with permissions to the proc try
> > to
> > run it, they get permission errors saying that they do not have
> > permissions
> > to access the other database that is referenced in the proc. It was my
> > understanding that when you run a proc, it runs under the context of the
> > proc
> > creator (in this case dbo), otherwise you have to grant select, insert,
> > update, etc. on all tables that your proc references. Does this not work
> > cross database?
>
>|||Clark Kent,
1 - Cross-Database Ownership Chaining should be enabled
2 - If the owner of the sp is the owner of the objects being referenced from
the other db, then there should be no problem, but if the owner of the sp
just has rights to "select", then SS will check if the one executing the sp
also has "select" right on those objects.
Ownership Chains
http://msdn2.microsoft.com/en-us/library/ms188676.aspx
SQL Server 2005 Security Overview for Database Administrators
download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLSecurityOverviewforAdmins.doc
Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
AMB
"Clark Kent" wrote:
> I have a stored procedure that I created on one database with permission
> granted to a select group of users. The proc grabs information from another
> database on the same server. When users with permissions to the proc try to
> run it, they get permission errors saying that they do not have permissions
> to access the other database that is referenced in the proc. It was my
> understanding that when you run a proc, it runs under the context of the proc
> creator (in this case dbo), otherwise you have to grant select, insert,
> update, etc. on all tables that your proc references. Does this not work
> cross database?

Cross Database Issue

I have a view which is like following

DB1.View1

Select * from Accounting,Employee

where Accounting.Emp_Id = Accounting.Emp_Id

and i am using this view in DB2.storedProcedure1, but this gives me an error when I execute this Stored procedure from VBA code. the same SP works from query analyser with same user_id and pwd

Error - Could not find database id 9. Database may not be activated yet or may be in transition.

If I change the view to following the SP works

DB1.View1

Select * from DB1.Accounting,DB1.Employee

where DB1.Accounting.Emp_Id = DB1.Accounting.Emp_Id

But I dont want to change the view as i dont own it.

Please suggest the solution for this.

Thanks in Advance.

Ashutosh

Moving your question to a different thread, where they can offer more help.

Thanks

Richard Cook

VS Debugger Team

|||

Is there a synonym defined on the objects mentioned, forcing SQL Server to go to a non-existing database ? Did you trace the command which is fired against the database using SQL Profiler ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

You did not mentioned the version and the service pack of sql server. IF its 2000 check this, it seems to be u have not applied the patches

http://support.microsoft.com/kb/834688

Madhu

|||

Thanks Jens,

the issue is - I have a view in one database which i refer from another database.

e.g. DB1 has view V1 and i need to use it in a SP1 in DB2

so i refer to the view as DB1.V1 in DB2.SP1. but the tables inside DB1.V1 are without database reference i.e. without [db].[tables]..its just- "select a from [table]"

and i run this DB2.SP1 from sql server query analyzer with login "abc",pwd - "abc". This works perfectly fine.

but when i run the same SP from my VBA code with same login "abc" pwd "abc"..it gives me above specified error.

but..if i fix the view..to "select a from [db].[dbo].[table]...it works from my VBA code as well.

therefore it makes me think that i need to change the view to set all the tables as [db].[table]...but as the db name changes from environment to environment i.e. development/integration...we can not put [db].[table]..

INSTEAD i need something like [currentDB].[Table].

Is that possible in sql server 2000

|||

Thanks Madhu.

I did saw that post on support.microsoft.com but here there are 2 things

1. It was working till yesterday and stoped working today..so can it be an issue with user privilages?

2. Its working with the solution that i have implmeneted, but that is too specific and i need generic solution.

Its SQL server 2000.

please see this

the issue is - I have a view in one database which i refer from another database.

e.g. DB1 has view V1 and i need to use it in a SP1 in DB2

so i refer to the view as DB1.V1 in DB2.SP1. but the tables inside DB1.V1 are without database reference i.e. without [db].[tables]..its just- "select a from [table]"

and i run this DB2.SP1 from sql server query analyzer with login "abc",pwd - "abc". This works perfectly fine.

but when i run the same SP from my VBA code with same login "abc" pwd "abc"..it gives me above specified error.

but..if i fix the view..to "select a from [db].[dbo].[table]...it works from my VBA code as well.

INSTEAD i need something like [currentDB].[Table].

Is that possible in sql server 2000

Cross Database access via Linked Server

Hello. When doing CROSS server CROSS database
select/joins, I use Linked servers. But for CROSS
database SAME server access, I'm thinking of a standard
where we ALSO use linked servers, even though the database
MIGHT be on the same server usually. I'm looking to do
that because on all test boxes the databases might not be
on the same server.
Question>> Is there any performance issue at all in doing
using linked servers when the databases involved are all
on the same server'
Option #1
select * from database2.dbo.sysobjects
Option #2
select * from Server.database2.dbo.sysobjects
Any performance hit in using Option #2, if the databases
are all on the same server? THanks, Bruce"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:010301c3c017$6aa27b80$a401280a@.phx.gbl...
> Hello. When doing CROSS server CROSS database
> select/joins, I use Linked servers. But for CROSS
> database SAME server access, I'm thinking of a standard
> where we ALSO use linked servers, even though the database
> MIGHT be on the same server usually. I'm looking to do
> that because on all test boxes the databases might not be
> on the same server.
> Question>> Is there any performance issue at all in doing
> using linked servers when the databases involved are all
> on the same server'
>
Yes. The performance hit is not small.
I always use views for cross-database access. It makes your life easier in
a number of ways.
For each foreign table, create a view like
create view FT
as
select * from otherdb..FT
or
create view FT
as
select * from otherserver.otherdb..FT
Then your application just uses FT, and the view resolves it to the other
database or other server.
David|||ok David. I wasn't thinking about views as an option on
cross-database stuff. Thanks for the reply, that's a good
approach! I'm not sure about having THAT many new views
though for all the combos of tables in other tables beign
accessed, but if self-referencing linked servers are that
much slower, then I'll be checking it out... Thanks, Bruce
>--Original Message--
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:010301c3c017$6aa27b80$a401280a@.phx.gbl...
>> Hello. When doing CROSS server CROSS database
>> select/joins, I use Linked servers. But for CROSS
>> database SAME server access, I'm thinking of a standard
>> where we ALSO use linked servers, even though the
database
>> MIGHT be on the same server usually. I'm looking to do
>> that because on all test boxes the databases might not
be
>> on the same server.
>> Question>> Is there any performance issue at all in
doing
>> using linked servers when the databases involved are all
>> on the same server'
>Yes. The performance hit is not small.
>I always use views for cross-database access. It makes
your life easier in
>a number of ways.
>For each foreign table, create a view like
>create view FT
>as
> select * from otherdb..FT
>or
>
>create view FT
>as
> select * from otherserver.otherdb..FT
>Then your application just uses FT, and the view resolves
it to the other
>database or other server.
>David
>
>.
>

Cross Apply Function

I am unsure how to select data within one table, and then select additional data from the result set.

There is an audit log table with columns:

Year, Month, Amount, AuditType, UniqueID, DateTime

AuditType: O = Original

AuditType: U = Update

Sample Data:

2007, January, 500, O, 333555, 3/10/2007 3:30:00 PM

2007, January, 1000, U, 333555, 4/10/2007 3:30:00 PM

2007, January, 1200, U, 333555, 5/10/2007 3:30:00 PM

2007, February, 500, O, 333556, 3/10/2007 3:30:00 PM

2007, March, 500, O, 333557, 3/10/2007 3:30:00 PM

I am trying to find all Updates in the table, ie where AuditType = U.

Then, for every Update, I want to see what was updated, ie the last record with that same UniqueID. Im guessing I should use an INNER JOIN or UNION with a SELECT TOP 1 ORDER BY DateTime but not sure how do to this.

Data returned should be:

2007, January, 1000, U, 333555, 4/10/2007 3:30:00 PM, 500

2007, January, 1200, U, 333555, 5/10/2007 3:30:00 PM, 1000

Any ideas greatly appreciated!

Which version of SQL Server do you use?|||SQL 2005 SP1|||

You could use CROSS APPLY statement:

Code Snippet

create table Log(

[Year] int,

[Month] varchar(10),

Amount decimal,

AuditType char(1),

UniqueID int,

date DateTime

)

insert into Log values(2007, 'January', 500, 'O', 333555, '3/10/2007 3:30:00 PM')

insert into Log values(2007, 'January', 1000, 'U', 333555, '4/10/2007 3:30:00 PM')

insert into Log values(2007, 'January', 1200, 'U', 333555, '5/10/2007 3:30:00 PM')

insert into Log values(2007, 'February', 500, 'O', 333556, '3/10/2007 3:30:00 PM')

insert into Log values(2007, 'March', 500, 'O', 333557, '3/10/2007 3:30:00 PM')

--Returns previous update

CREATE function udf_earlyUpdate(@.ID int,@.dt datetime) RETURNS TABLE

RETURN SELECT TOP 1 Amount FROM Log WHERE UniqueID = @.ID and date<@.dt ORDER BY date DESC

select * from LOG CROSS APPLY udf_earlyUpdate(UniqueID,Date)

WHERE log.AuditType='U'

|||

I'm a bit confused about your goals.

You indicate that

the last record with that same UniqueID

, but your expected results show two records for the same UniqueID.

From you description, I would have expected that you wanted only one record for UniqueID 333555.

Please clarify.

|||

Every update should have one record. But a record with the same Unique ID can be updated multiple times.

The results should show every update, even if its the same Unique ID and what was updated.

|||

Here's the basic idea, I'll leave it to you to fill in the specific blanks.

select <alias>.columns

from (<any select statement that you want to specify, if calcing aggregates, alias each column) AS <alias>

Why does this work? A select statement simply returns a result set, which has exactly the same definition as a table. If you make sure each column in the select statement has a specific "column name", then it works just like a table. So, you simply wrap parens around the select statement within the FROM clause. The key to the whole thing is that you alias the select statement so that it really can be referenced in memory looking just like a table.

|||

The CROSS APPLY function works great with a simple SELECT. However, when I attempt to create a VIEW with this function combining two tables, I get an error because they contain the same column.

How can I get the result of the Function without using SELECT *, ie without selection all columns?

"Column names in each view or function must be unique. Column name 'BUDGETAMT' in view or function 'V_BUDGET_UPDATE_ALL' is specified more than once."

Thursday, March 8, 2012

crystal report 9

Wednesday, March 7, 2012

crystal Report

Saturday, February 25, 2012

Crosstab?

Crosstab?

Crosstab, Pivot Query representation

Friday, February 24, 2012

Cross-server SELECT query

cross-section select

Sunday, February 19, 2012

Cross-database UDF with a parameter

Cross-database UDF with a parameter

cross-database query from ASP.NET

Cross table copy working in Query Analyser, but not from code

cross table

Cross Tab SELECT

Thursday, February 16, 2012

Cross referncing databases

Cross Join without Table?

Cross Join Without Table?

Tuesday, February 14, 2012

Cross Database Permission

Cross Database Issue

Cross Database access via Linked Server

Cross Apply Function

database errors

Blog Archive

About Me