trying to write a query from 2 tables and join them only they share a column name of the same name.I can define both aliases seperatrly but get an error when trying both,
select
case_HDR.case_nbr as "test"
Case_DTL.case_nbr as "test2"
From
Case_HDR, case_dtl
I need to join them also but trying to work in stages.
Please help...
I need help analyzing this query. I am not sure why the person that wrote this query used the same table for 3 different aliases.
FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role
I tried to get rid of :
code_table country,
code_table prop_role
and put "state" in any line that referenced:
code_table country,
code_table prop_role
to see if the query worked the same, but I different results. Why?
Please help...
--7 seconds to omplete.
--set explain on;
--UNLOAD TO '/export/home/permit/owneraddress.txt'
SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
state.code_table_cd,
address.zip_postal_code,
country.code_description,
address.line_care_of,
prop_role.code_table_cd
FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role
WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate()
AND state.id = address.province_state_cd
AND country.id = address.country_cd
AND prop_role.id = ppi.prop_role_cd
AND prop_role.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'
Does anyone know of a neat way of aliasing tables in MS SQL Server 2000? I have a table [X] and I want to refer to it (in TSQL statements, stored procedures, etc.) by two names, say [X] and [Y]. There should be only one copy of the table's data.
I could handle this in my site logic (ie convert [Y] to [X]), but it would be really clean to handle this in the database.
Thanks for your advice.
Matt
I have a SELECT query with an alias in it.
The intellisense shows all field except the alias one.
What goes wrong?
I'm in the process of building a site and converting views/tables/queries from an Access database to SQL. I've done this quite a few times, and never had any significant issues I couldn't figure out on my own.
In Enterprise Manager, I've created a view and in the query, I need to create an alias that is similar to below:
SELECT ((monthmult) + ((b2avg*15)-(av2*10)) + (lp1+lp2) + ((b1avg*30)-(av1*20))) as PIndexValue
which is how the formula reads in the Access view.
However, when I got to run the query, SQL strips out all of the parentheses and calculates the value in left to right order:
(monthmult + b2avg*15-av2*10 + lp1+lp2 + b1avg*30-av1*20) as PIndexValue
Which gives me an incorrect value.
Does anyone know why this is happening, or am I just unaware of the right way of doing it?
Thank you,
Derrick
Hi,
I’m working with a really old design migrated to SQL 2005, in which I basically have two tables…
Table 1 contains all the “proper” data, and has columns called: col_1, col_2, col_3
Table 1’s data is something like:
col_1, col_2, col_3
Jack,jack@yahoo.ca,Toronto
Jill,jill@hotmail.com,Montreal
Table 2 contains meta-data for Table 1, specifically, it has two columns: column, meta-data
Table 2’s data is something like:
column,metadata
col_1,name
col_2,email
col_3,city
(Hopefully, my description of the design makes sense….basically; Table 2’s data describes what’s in each column of Table 1).
So, the question, if I want to write a ‘SELECT’ on Table 1, how can I use the data in Table 2 as aliases (or column) headers.
I’m currently going down the path of building dynamic SQL statements in T-SQL….but before I get too far, wanted to vet this idea here (it’s always been a fantastic resource for me)
Thanks in advance!
TABLE1
======================
PriceList
---------
1
2
3
1
2
3
1
2
3
Price
-----
777
888
999
777
888
999
777
888
999
(pretend these columns are side by side)
======================
I need to make a query to:
SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3
the output that i want is:
PRICE1
------
777
777
777
PRICE2
------
888
888
888
PRICE3
-----
999
999
999
(pretend these columns are also side by side)
Hi,
is it possible to assign a numeric value as a column alias:
select ... as 1234 from ...
does not work.
Cheers
Does anyone know how to create SQL Server aliases for Itanium servers?
Alternatively does anyone know how to use SMO to create a SQLAliasCollection for a specific server? I can't seem to find how to get that collection.
I've added aliases that work for 32 bit applications but they don't seem to be used for 64 bit applications.
Formatting question. The query below is failing on the columns with spaces in the name. I've tried brackets and single quotes with no luck. How should this be formatted?
----------------------------
SELECT x.trkuniq, s.mstuniq, t.meetuniq,
c.coursec AS Course_Code,
c.descript AS Course_Name,
[q.cactus #] AS Cactus_#
s.sectionn AS Section,
RTRIM(f.lastname) + ', ' + RTRIM(f.firstname) AS Teacher, f.funiq,
t.termc AS Term_Code, zd.cycldayc AS Day,
zp.periodc AS Period, zp.periodn,
p.schoolc AS School
FROM mstmeet t INNER JOIN
mstsched s ON t.mstuniq = s.mstuniq INNER JOIN
trkcrs x ON s.trkcrsuniq = x.trkcrsuniq INNER JOIN
course c ON x.crsuniq = c.crsuniq INNER JOIN
track p ON p.trkuniq = x.trkuniq INNER JOIN
facdemo f ON s.funiq = f.funiq INNER JOIN
courses q ON c.coursec = [q.course number] INNER JOIN
trkper zp ON t.periodn = zp.periodn AND
x.trkuniq = zp.trkuniq INNER JOIN
trkcycle zd ON t.cycldayn = zd.cycldayn AND
x.trkuniq = zd.trkuniq
I have been working with SQL for quiet a while but think this perhaps is a very basic question that has always escaped me:
At my work I was exposed to both, MS SQL Server 2000 and Sybase Adaptive Server Anywhere/Sybase SQL Anywhere.
Under Sybase I was able to use aliases in other calculations and filters but i have never been able to do the same with SQL.
Example:
In Sybase I can write this:
Select Price * Units as Cost Cost * SalesTax as TotalTaxFrom Invoice Where TotalTax > 3.5
However if i want to do this in MS SQL 2000 i have to go trough
Select
Price * Units as Cost
Price * Units * SalesTax as TotalTax
From Invoice
Where (Price * Units * SalesTax) > 3.5
In the long run this is costing me a lot of code redundancy, not to mention a debugging nightmare. Is there a way to replicate this alias usage in MS SQL Server?
TableX
-------
X1
X2
X3
TableY
-------
Y1
Y2
Y3
I need to write a query with the following joins:
TableX.X1 = TableY.Y1
TableX.X2 = TableY.Y2
What is the proper way to do this in SQL? Would it be:
select x.*
from TableX x
join TableY y1 on y1.Y1 = x.X1
join TableY y2 on y2.Y2 = x.X2
Is there a more proper way to do this without creating multiple aliases of the same table?
Just stumbled over the interesting bug -
if you have an SQLExpress server and the "allow remote connection" in SQL Instance is turned ON, it doesn't means that it works if you create some aliases for this instance
If Im trying to connect to SQLExpress via new created alias ('localhost' for example) I got the "SQL Server does not allow remote connections" error. Albeit, connecting to ".SQLExpress" is successful.
I've checked the properties of locahost DB instance in SQL Management Studio - remote connection is allowed.
I've restarted the SQLExpress instance.
There is no SQLServer or other instances on my PC
But it doesn't work
How to fix this and allows to connect to sql instance via aliases?
HI,
I have a view where I want to add Rundate and Prev Month Rundate as computed columns to simplify my joins and calculations.
Rundate will be a select from another table that has a list of rundates for each month like this.
SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend <= getdate()) AS smalldatetime) AS CurrRunDate
Now, I want to add Prev Month Rundate on the basis of CurrRunDate but it does not accept CurrRunDate. The query is like this
SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend < CurrRunDate) AS smalldatetime) AS PrevMonthRunDate
Can anyone help me to work around with the alias as I dont like to put a whole bunch of code inplace of CurrRunDate(Alias).
Thanks,
Shariq
Hi All,
I have this query :
select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then col99
end
when i execute the above query it gives me the following error message.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'col99'.
Thanks in advance.
Thanvi.
I have a question regarding the nature of virtual sql servers, specificially what protocol is used to communicate to the server when a request is made by a client.
For example, if a scheduled job is run on the virtual sql server, what determines the protocol used (e.g. TCP/IP, named pipes etc.) by SQL Server agent? Is it the client network alias set up on the virtual server?
I am asking because currently the client aliases on some of our virtual sql servers are using named pipes and I think this is causing a problem with our backups.
Thanks,
Adrian.
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
I have the following query
SELECT [KPI].*
FROM
OPENQUERY(LINKED_OLAP,'SELECT
HEAD(TAIL(DESCENDANTS
(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,
([Game].[Game Code].&[1] ,
[Code] ...
The last three columns are dynamically generated because they change during time. Next month they will be different.
I like to introduce aliases for them and to have them in the select as 'TWO_MONTHS_AGO','ONE_MONTH_AGO', 'CURRENT_MONTH'
I wonder if exists something like [KPI].(0), [KPI].(1), and etc.. of the OPENQUERY to get the selected columns by their ordering number...
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
View 11 Replies View RelatedUsing SQL Server 2000. How can I refer to one alias in another column?
E.g., (this a contrived example but you get the idea)
SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data
Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
I'm currently receiving the following error message whilst attempting to install SQL Server 2005 Standard Edition on Windows Server 2003 (32 Bit):
Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The system cannot find the file specified.
This server already has an install of SQL Server 2000 as the default instance. I'm attempting to install a new named instance of SQL Server 2005.
Extract from log:
<Func Name='LaunchFunction'>
Function=Do_sqlPerfmon2
<Func Name='GetCAContext'>
<EndFunc Name='GetCAContext' Return='T' GetLastError='0'>
Doing Action: Do_sqlPerfmon2
PerfTime Start: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007
<Func Name='Do_sqlPerfmon2'>
<EndFunc Name='Do_sqlPerfmon2' Return='0' GetLastError='2'>
PerfTime Stop: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007
MSI (s) (4C:FC) [10:20:02:833]: Executing op: ActionStart(Name=Rollback_Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Removing performance counters,)
<EndFunc Name='LaunchFunction' Return='0' GetLastError='0'>
MSI (s) (4C:FC) [10:20:02:849]: Executing op: CustomActionSchedule(Action=Rollback_Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1281,Source=BinaryData,Target=Rollback_Do_sqlPerfmon2,CustomActionData=100Removing performance counters200000DTSPipelineC:Program FilesMicrosoft SQL Server90DTSBinnDTSPERF.INI)
MSI (s) (4C:FC) [10:20:02:849]: Executing op: ActionStart(Name=Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Installing performance counters,)
MSI (s) (4C:FC) [10:20:02:849]: Executing op: CustomActionSchedule(Action=Do_sqlPerfmon2.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1025,Source=BinaryData,Target=Do_sqlPerfmon2,CustomActionData=100Installing performance counters200000C:Program FilesMicrosoft SQL Server90DTSBinnDTSPERF.INIC:Program FilesMicrosoft SQL Server90DTSBinnDTSPERF.HC:Program FilesMicrosoft SQL Server90DTSBinnDTSPipelinePerf.dllDTSPipeline0DTSPipelinePrfData_OpenPrfData_CollectPrfData_Close)
MSI (s) (4C:94) [10:20:02:864]: Invoking remote custom action. DLL: C:WINDOWSInstallerMSI1683.tmp, Entrypoint: Do_sqlPerfmon2
<Func Name='LaunchFunction'>
Function=Do_sqlPerfmon2
<Func Name='GetCAContext'>
<EndFunc Name='GetCAContext' Return='T' GetLastError='0'>
Doing Action: Do_sqlPerfmon2
PerfTime Start: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007
<Func Name='Do_sqlPerfmon2'>
<EndFunc Name='Do_sqlPerfmon2' Return='2' GetLastError='2'>
PerfTime Stop: Do_sqlPerfmon2 : Tue Jun 12 10:20:02 2007
Gathering darwin properties for failure handling.
Error Code: 2
MSI (s) (4C!F0) [10:23:46:381]: Product: Microsoft SQL Server 2005 Integration Services -- Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The system cannot find the file specified.Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The system cannot find the file specified.
You can ignore this and it will complete the installation, but subsequently trying to patch with SP2 will fail on the same sections - Hotfix.exe crashes whilst attempting to patch Database Services, Integration Services and Client Components (3 separate crashes).
I've removed SQL Server 2005 elements and tried to re-install, but it's not improved the situation.
Any ideas?
Hi -
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
Where do I change the security settings?
Thanks - Grace
I cannot execute a package by using Execute Package task.
I supplied sa credentials to connection manager, and it shows the list of Packages on SQL Server but when running the task it says
Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
Any clue ?
Thanks,
Fahad
Hi,
I am running dts in Sql Server 2005 management studio from Management, Legacy and data Transformation Services.
Once the dts has run, I get this error message "Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error accessing Windows Event Log."
Please help me
thanks in advance
Srinivas
We have reports deployed in the Report Server. While connecting from client, we are getting the error
"An internal error occurred on the report server. See the error log for more details. rsInternal Error)"
Then we went to Report Server, Reporting Service and SQL Server service are all are running fine.
Important thing is some time the reports are working fine, sometimes i am receiving this error. Please help.
We predict whether the services are automatically restarted or transaction logs exceeding the limit or any other parameters to set to avaoid this error?
Please help.
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>
<xsl:template name="identity" match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Its 5th line is the first xsl:template element.
What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.
Thanks!
I have the error above, but no error log. I can preview the sub report - but this main report fails after working this morning. This is for internal company reports and I rebuilt this one after converting from access.
I have looked where the error logs should be, but there are no error logs.
I rebuilt the query as I needed to change this, but this did not help.
Is there someone who could point me in the correct direction.
Thanks!
Terry
This has worked fine for weeks, and months.
I'm running Vista Ultimate. SQL 2005 is set as my Default instance, and SQL2000 is set as (local)SQL2000.
Today, actually half way through today, I restarted my computer after installing Photoshop Updates.
Upon getting my computer back up and running, I cannot access SQL2000 from any website on my computer, nor can I access it from SQL2005 Management Stdio. I CAN access it from Enterprise Manager (SQL2000 tool).
Whenever I run an web app that connects to it I get this error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Now I usually get these when ASP.NET can't point to the right spot, but in this case I'm pointing exactly where I need to go. Any thoughts?
--Edit
I should also add my password got changed a few days ago on our Domain. This was the first time restarting after the PW change.
My link server was working just fine until friday evening.
It stopped worked over the week end.
with and error Error 7399: OLE DB provider 'MSDAORA' reported an error. OLE DB error .
---my oracle 10g client is working just fine
--TNS names looks fine
---i recreated the link but i am still getting the same error.
I need your help because a lot of jobs are using that link on Monday it is going to be crazzzy.
Hi,
We have a production SQLServer 6.5 running with service pack SP5a update:
I got the following 2 errors.....
1.
Error : 806, Severity: 21, State: 1
Could not find virtual page for logical page 67833121 in database 'tempdb' database 'tempdb'
2.
I got error when I ran a job for Update statistics
Error : 614, Severity: 21, State: 3
A row on page 2697653 was accessed that has an illegal length of -8631 in database 'abc'.
For Error 2: I ran update statistics using query analyser. It is fine
Is there anything I have to do further?
For Error 1 : The work around given by Microsoft
=================================================
I ran
DBCC CHECKTABLE(syslogs)
I am getting the following message on :
master:
Checking syslogs
The total number of data pages in this table is 1.
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.
Table has 11 data rows.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
model:
Checking syslogs
The total number of data pages in this table is 47.
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.
Table has 532 data rows.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
tempdb:
Checking syslogs
The total number of data pages in this table is 1.
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.
Table has 31 data rows.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
I ran dbcc checkdb on master,model and tempdb . Still I get the same problem.
for tempdb:
Checking 8
The total number of data pages in this table is 1.
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.
Table has 19 data rows.
for master:
Checking 8
The total number of data pages in this table is 1.
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.
Table has 27 data rows.
for model:
Checking 8
The total number of data pages in this table is 47.
*** NOTICE: Notification of log space used/free cannot be reported because the log segment is not on its own device.
Table has 532 data rows.
All system databases and userdatabase recovered successfully when I restarted sqlserver.
Please advice how to get rid of this problem.
Thanks in advance,
Anu
where can i find it?
thanks
Hi,
I have application in which i am performing synchronization between SQL Server 2000 and SQL Server 2005 CE.
I have one table "ItemMaster" in my database.There is no relationship with this table,it is standalone.I am updating its values from Windows Mobile Device.
I am performing below operations for that.
Step : 1 Pull To Mobile
Code BlockmoSqlCeRemoteDataAccess.Pull("ItemMaster", "SELECT * FROM ItemMaster", lsConnectString,RdaTrackOption.TrackingOn);
Step : 2 Using one device form i am updating table "ItemMaster" table's values.
Step : 3 Push From Mobile
Code BlockmoSqlCeRemoteDataAccess.Push("ItemMaster", msConnectString);
So i am getting an error on 3rd step.
While i am trying to push it says,
"The Push method returned one or more error rows. See the specified error table. [ Error table name = ]".
I have tried it in different ways but still i am getting this error.
Note : Synchronization is working fine.There is not issue with my IIS,SQL CE & SQL Server 2k.
Can any one help me?I am trying for that since last 3 days.
Hi All,
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Thanks & Regards,
Prakash Srinivasan