Combining tables

We have an application that uses Access databases and archives its data
every year. Then there is the current year's data is an Access database. I
have imported the prior years data into their own table in a SQL database.
I've set up a DTS job to run every day that will import the current Access
database. So, for example, we have tables b2003, b2004 and b2005 in a SQL
database. Now I'm trying to create a report that will be using all these
tables. What I was trying to do was to create a view that included all
these SQL tables. However, I can't think of the SQL code that would join
those tables together. Can anyone help me out?
Thanks.I tried to enter in the follwoing:
SELECT *
FROM b2003
UNION ALL
SELECT *
FROM b2004
UNION ALL
SELECT *
FROM b2005
UNION ALL
But I got this error:
"The Query Designer does not support the UNION SQL construct."
Did I do something wrong?
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23I54A8irFHA.2588@.tk2msftngp13.phx.gbl...
> We have an application that uses Access databases and archives its data
> every year. Then there is the current year's data is an Access database.
> I have imported the prior years data into their own table in a SQL
> database. I've set up a DTS job to run every day that will import the
> current Access database. So, for example, we have tables b2003, b2004 and
> b2005 in a SQL database. Now I'm trying to create a report that will be
> using all these tables. What I was trying to do was to create a view that
> included all these SQL tables. However, I can't think of the SQL code
> that would join those tables together. Can anyone help me out?
> Thanks.
>|||Hello,
You may test the following code in SQL server Query Analyzer:
SELECT * FROM b2003
UNION ALL
SELECT * FROM b2004
UNION ALL
SELECT * FROM b2005
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Joshua,
Was it: "The Query Designer does not ~GRAPHICALLY~ support the UNION SQL
construct"?
If so then it is all right, the designer just won't produce a diagram.
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23b5HtBjrFHA.1172@.TK2MSFTNGP11.phx.gbl...
> I tried to enter in the follwoing:
> SELECT *
> FROM b2003
> UNION ALL
> SELECT *
> FROM b2004
> UNION ALL
> SELECT *
> FROM b2005
> UNION ALL
> But I got this error:
> "The Query Designer does not support the UNION SQL construct."
> Did I do something wrong?
>
> "Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
> news:%23I54A8irFHA.2588@.tk2msftngp13.phx.gbl...
database.
and
that
>

Combining subquery results into one field

Hello there

I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms. I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking. Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query. The table structures are as follow:

bookingrequestion - bookingrequestid, startdate, enddate

roomsbooked-id, bookingrequestid, roomname

i'm basically trying to use the following query

select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms

i'd like the results of the subquery to return the room names as A,B,C. I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me.

thanks

I would look at returning two results to a dataset then creating a relationship between them. Displaying the results is pretty easy using nested repeaters:http://gridviewguy.com/ArticleDetails.aspx?articleID=185

COM+ Event error in relation to SQL Server

Hello,
In my Application log, I received this error from
SQLSERVERAGENT:-
Unable to read local eventlog (reason: The data area
passed to a system call is too small).
I then received lots of the following errors, source
EventSystem:-
The COM+ Event System detected a bad return code during
its internal processing. HRESULT was 800706BF from line
42 of .\eventsystemobj.cpp. Please contact Microsoft
Product Support Services to report this error.
The Server needed a reboot.
Has anyone ever seen this?
Thanks
SueThey are related but different. SQL Agent does not use COM+, but both are
complaining they can not read the NT Event Log.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"SueB" <sue.bridges@.aculab.com> wrote in message
news:057801c354f2$0c90a250$a601280a@.phx.gbl...
> Hello,
> In my Application log, I received this error from
> SQLSERVERAGENT:-
> Unable to read local eventlog (reason: The data area
> passed to a system call is too small).
> I then received lots of the following errors, source
> EventSystem:-
> The COM+ Event System detected a bad return code during
> its internal processing. HRESULT was 800706BF from line
> 42 of .\eventsystemobj.cpp. Please contact Microsoft
> Product Support Services to report this error.
> The Server needed a reboot.
> Has anyone ever seen this?
> Thanks
> Sue|||Hi,
Any clue as to how to fix it? Also, received the following error in the System log this morning from DCOM:-
Access denied attempting to launch a DCOM Server using DefaultLaunchPermssion. The server is:
{0002DF01-0000-0000-C000-000000000046}
The user is Unavailable/Unavailable, SID=3DUnavailable.
Also in the past I've seen the following DCOM error:-
The server {1BE1F766-5536-11D1-B726-00C04FB926AF} did not register with DCOM within the required timeout.
Any clues on these?
Thanks
Sue
>--Original Message--
>They are related but different. SQL Agent does not use COM+, but both are
>complaining they can not read the NT Event Log.
>GertD@.SQLDev.Net
>Please reply only to the newsgroups.
>This posting is provided "AS IS" with no warranties, and confers no rights.
>You assume all risk for your use.
>Copyright =A9 SQLDev.Net 1991-2003 All rights reserved.
>"SueB" <sue.bridges@.aculab.com> wrote in message
>news:057801c354f2$0c90a250$a601280a@.phx.gbl...
>> Hello,
>> In my Application log, I received this error from
>> SQLSERVERAGENT:-
>> Unable to read local eventlog (reason: The data area
>> passed to a system call is too small).
>> I then received lots of the following errors, source
>> EventSystem:-
>> The COM+ Event System detected a bad return code during
>> its internal processing. HRESULT was 800706BF from line
>> 42 of .\eventsystemobj.cpp. Please contact Microsoft
>> Product Support Services to report this error.
>> The Server needed a reboot.
>> Has anyone ever seen this?
>> Thanks
>> Sue
>
>.
>|||I only found that a reboot to corrects this.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"SueB" <sue.bridges@.aculab.com> wrote in message
news:113e01c35a6d$43453510$7d02280a@.phx.gbl...
Hi,
Any clue as to how to fix it? Also, received the
following error in the System log this morning from DCOM:-
Access denied attempting to launch a DCOM Server using
DefaultLaunchPermssion. The server is:
{0002DF01-0000-0000-C000-000000000046}
The user is Unavailable/Unavailable, SID=Unavailable.
Also in the past I've seen the following DCOM error:-
The server {1BE1F766-5536-11D1-B726-00C04FB926AF} did not
register with DCOM within the required timeout.
Any clues on these?
Thanks
Sue
>--Original Message--
>They are related but different. SQL Agent does not use
COM+, but both are
>complaining they can not read the NT Event Log.
>GertD@.SQLDev.Net
>Please reply only to the newsgroups.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>You assume all risk for your use.
>Copyright © SQLDev.Net 1991-2003 All rights reserved.
>"SueB" <sue.bridges@.aculab.com> wrote in message
>news:057801c354f2$0c90a250$a601280a@.phx.gbl...
>> Hello,
>> In my Application log, I received this error from
>> SQLSERVERAGENT:-
>> Unable to read local eventlog (reason: The data area
>> passed to a system call is too small).
>> I then received lots of the following errors, source
>> EventSystem:-
>> The COM+ Event System detected a bad return code during
>> its internal processing. HRESULT was 800706BF from line
>> 42 of .\eventsystemobj.cpp. Please contact Microsoft
>> Product Support Services to report this error.
>> The Server needed a reboot.
>> Has anyone ever seen this?
>> Thanks
>> Sue
>
>.
>

COM+ Application server has problems with SQL2000 both Win2003

Hi,
I want to run the following scenario:
1 server with COM+ components do DTC transactions against a SQL-2000
database. While this runs perfectly under Windows 2000, this does not work
at all under Windows 2003. I have checked all MSDN stuff, run the DTCTESTER
utilitly and came to the result, that is must be some network problem.
But: I have no idea how to resolve it, both are set to use TCP/IP, no
firewall is active, ping and drive mapping (in both directions) is
successful, also retrieving data via query analyzer is possible. Now which
setting must I change, so these machines can do those DTC transactions?
Regards
Is netbios name resolution working in both directions?
Can you make connections using ISQL.exe both ways without errors?
Did you restrict the rpc ports on the firewall for MSDTC?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

COM+ Application server has problems with SQL2000 both Win2003

Hi,
I want to run the following scenario:
1 server with COM+ components do DTC transactions against a SQL-2000
database. While this runs perfectly under Windows 2000, this does not work
at all under Windows 2003. I have checked all MSDN stuff, run the DTCTESTER
utilitly and came to the result, that is must be some network problem.
But: I have no idea how to resolve it, both are set to use TCP/IP, no
firewall is active, ping and drive mapping (in both directions) is
successful, also retrieving data via query analyzer is possible. Now which
setting must I change, so these machines can do those DTC transactions?
RegardsIs netbios name resolution working in both directions?
Can you make connections using ISQL.exe both ways without errors?
Did you restrict the rpc ports on the firewall for MSDTC?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

COM security Policy

I am trying to create a data extension and this extension has to access a
COM+ application in order to run as a domain user to access files on the
network but every time I try running this code it errors out with a security
permission error. How do I setup permission to access the COM+ application?
--
RYAN SCHOUTENNever mind this is not an issue
--
RYAN SCHOUTEN
"RYAN SCHOUTEN" <ryanttr@.yahoo.com> wrote in message
news:uyU736ERFHA.2744@.TK2MSFTNGP10.phx.gbl...
> I am trying to create a data extension and this extension has to access a
> COM+ application in order to run as a domain user to access files on the
> network but every time I try running this code it errors out with a
security
> permission error. How do I setup permission to access the COM+
application?
> --
> RYAN SCHOUTEN
>

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not re

Hello,

I have a asp.net application that calls a DTS package. This application is running fine on my machine (where SQL Server and Web Server are running). When I moved the application to a Web Server (where SQL Server is not installed), I am getting the following error due to the DTS package.

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not registered.

I tried every thing I could find on the web but with no luck.

Can someone please help me?
Many thanks in advance for your help.

A COM error means that something was not registered, so I am guessing the product SQL 2005 or DTS part was not installed in that computer, did you try to reinstall SQL 2005?|||

Your error is explained in the link below and I know the plain way to deploy DTS so if you decide to change your means of deployment post again so I can help. Hope this helps.

http://www.velocityreviews.com/forums/t93159-com-object-with-clsid-10020200eb1c11cfae6e00aa004a34d5-is-either-not-valid-or-not-registered.html

|||

Hiya guys.

Sorry for the late update!!!

Special thanks to Caddre!

Just want to give an update on this issue.
Basically, I have never been able to manage to sort this one out, despite viewing many articles on the net.

However, I just want to share one option to overcome this problem.

You can invoke your DTS package through a JOB using SP_START_JOB stored proc. This SP Instructs SQL Server Agent to execute a job immediately. This would do the job of executing the package!!! Of course, you will need the right to run the job (or use impersonation).

Hope this helps.

COM ADO and SQLXML v3

Hi,

I'm in the process of updating an HTA hosted application to SQL Server 2005 Express, using javascript with ADO for database access. I have one function to which I pass an SQLXML query template or updategram for all database queries and updates:

function doSql(sXml) {
var cmd = new ActiveXObject('ADODB.Command');
var conn = new ActiveXObject('ADODB.Connection');
var strmIn = new ActiveXObject('ADODB.Stream');
var strmOut = new ActiveXObject('ADODB.Stream');
var xml = new ActiveXObject(sDOM);
xml.async = false;

try {
conn.Provider = "SQLOLEDB";
conn.Open("Provider=SQLOLEDB.1;Persist Security Info=True;"+
"Initial Catalog=CGIS;Server=(local)\\ocean;Integrated Security=SSPI;");
conn.Properties("SQLXML Version") = "SQLXML.3.0";
cmd.ActiveConnection = conn;
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}";
strmIn.Open();
strmIn.WriteText(sXml);
strmIn.Position = 0;
cmd.CommandStream = strmIn;
strmOut.Open();
cmd.Properties("Output Stream").Value = strmOut;
cmd.Properties("Output Encoding").Value = "UTF-16";
var iCount;
cmd.Execute(iCount, null, 0x400);
conn.Close();
xml.load(strmOut);
return xml;
} catch(e) { alert('A database error occured: \n'+e.description+'\n\nQuery:\n'+sXml); }

I understand that SQLServer 2005 has SQLXML built in, which can be accessed using ADO.Net. Can it also be accessed using COM ADO? When I changed the connection string to this:

conn.Open("Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=\\.\pipe\SQLLocal\SQLEXPRESS");

I get this error:

'A database error occured:
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].'

Sounds like a connection string error however this was the connection string I pulled out of a .udl file that connected successfully.

Any help/suggestions greatly appreciated!

Andrew

Try changing the provider to see if that's it. My bet is that it is something to do with the network connection settings, so look in Books Online in the Database Engine section to learn more about the settings and various issues they have. You can also check out this article:

http://support.microsoft.com/default.aspx/kb/914277

Buck Woody

COM ADO and SQLXML v3

Hi,

I'm in the process of updating an HTA hosted application to SQL Server 2005 Express, using javascript with ADO for database access. I have one function to which I pass an SQLXML query template or updategram for all database queries and updates:

function doSql(sXml) {
var cmd = new ActiveXObject('ADODB.Command');
var conn = new ActiveXObject('ADODB.Connection');
var strmIn = new ActiveXObject('ADODB.Stream');
var strmOut = new ActiveXObject('ADODB.Stream');
var xml = new ActiveXObject(sDOM);
xml.async = false;

try {
conn.Provider = "SQLOLEDB";
conn.Open("Provider=SQLOLEDB.1;Persist Security Info=True;"+
"Initial Catalog=CGIS;Server=(local)\\ocean;Integrated Security=SSPI;");
conn.Properties("SQLXML Version") = "SQLXML.3.0";
cmd.ActiveConnection = conn;
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}";
strmIn.Open();
strmIn.WriteText(sXml);
strmIn.Position = 0;
cmd.CommandStream = strmIn;
strmOut.Open();
cmd.Properties("Output Stream").Value = strmOut;
cmd.Properties("Output Encoding").Value = "UTF-16";
var iCount;
cmd.Execute(iCount, null, 0x400);
conn.Close();
xml.load(strmOut);
return xml;
} catch(e) { alert('A database error occured: \n'+e.description+'\n\nQuery:\n'+sXml); }

I understand that SQLServer 2005 has SQLXML built in, which can be accessed using ADO.Net. Can it also be accessed using COM ADO? When I changed the connection string to this:

conn.Open("Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=\\.\pipe\SQLLocal\SQLEXPRESS");

I get this error:

'A database error occured:
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].'

Sounds like a connection string error however this was the connection string I pulled out of a .udl file that connected successfully.

Any help/suggestions greatly appreciated!

Andrew

Try changing the provider to see if that's it. My bet is that it is something to do with the network connection settings, so look in Books Online in the Database Engine section to learn more about the settings and various issues they have. You can also check out this article:

http://support.microsoft.com/default.aspx/kb/914277

Buck Woody

Com + ....what Port Does It Use?

Hello,
I hope someone could help me out here....

I have set up a new web server in my DMZ. This web server needs to "talk" to an application server located on my LAN. It communicates via a COM + Component.

I would like to keep the access that is open between the DMZ & LAN to minimal, obviously for security reasons. Does anyone know what port is used for COM + communication?

Thank you!

SpootnicksThis is the site that I always use to answer port questions:
http://www.iana.org/assignments/port-numbers

Having said that, I don't immediately see the answer. I think it's because COM+ isn't what you're looking for. I think it MS's port for remote procedure call or something.

Sorry for the vague answer. I hope the web page helps at least.

Regards,

hmscott|||Would the DCOM (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndcom/html/msdn_dcomfirewall.asp) information help?

-PatP

column name alias concatenation

I have a web application where I would like to return a dynamic column name using aliasing. below is an example:

select hours as 'Fri<BR>' + cast(Day(getDate()) as varchar(2)) from todayshours

I get an error trying to do concatenation as part of the alais. Any ideas?

Luke
lgraunke AT 4invie.comWhats <BR>

Is this being done in SQL Server?|||Ideally I would like the column name/header to show something like 'Fri<BR>20'. The '<BR>' is just some web formating that is automatically incorporated.|||This should float your boat...

USE Northwind
GO

DECLARE @.cmd varchar(8000)

SELECT @.cmd = 'SELECT Quantity AS ['
+ CASE DATEPART(WeekDay,GetDate())
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
+ '<BR>'
+ cast(Day(getDate()) as varchar(2))
+ '] FROM [Order Details]'

SELECT @.cmd

EXEC(@.cmd)|||Thanks, that was exactly what I was looking for.

column collation

Hi,
I am using C++ to develop an application to support SQL Server 2005. I have
a problem whant I use SQLColAttribute function. It only returns "Collation
Name", instead of the real column's collation name. Does any body has any
idea?
Here it is my code:
retcode = SQLColAttribute(handleSTMT,i +1,
SQL_CA_SS_COLUMN_COLLATION ,
columnCollation,
256, &nameLength, &valueInInt);
Thanks for you help.
--Lijie
I believe is not supported in SQLColAttribute

column collation

Hi,
I am using C++ to develop an application to support SQL Server 2005. I have
a problem whant I use SQLColAttribute function. It only returns "Collation
Name", instead of the real column's collation name. Does any body has any
idea?
Here it is my code:
retcode = SQLColAttribute(handleSTMT,i +1,
SQL_CA_SS_COLUMN_COLLATION ,
columnCollation,
256, &nameLength, &valueInInt);
Thanks for you help.
--LijieI believe is not supported in SQLColAttribute

Column Alias in views

Hi All,
I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.

e.g.
Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;

In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.

My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?

Thanks for your time.

Regards:
Prathmeshhi

try this

Select field1 as Alias1, field2 as Alias2, field1 + ' ' + field2 as Alias3 from table1;

hope this will solve ur problem|||Hi,
Ok, I think I need to explain a bit more detail. I have got a database table that stores data about different equipments. Each equipment is identified by 3 distinct fields Area, Type, No. So a particular equipment tag would be of type:
Area+Type+No.

Now at the same time the table also holds the description of the equipment which comes from 2 fields desc1 and desc2. So the whole equimment desc would be desc1+desc2

Now on the reports the equiptag and equipment desc need to be concatenated to form one equipment number i.e. Area+type+No+Desc1+desc2

So what I wanted to do was
Select Area+type+No as Equiptag, Desc1+Desc2 As EquipDesc, EquipTag+EquipDesc As EquipNo from equipment;

but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

So, Is there any way to do this?

Regards:
Prathmesh|||Hi,

So what I wanted to do was

Select
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc,
EquipTag+EquipDesc As EquipNo
from
equipment;

but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

So, Is there any way to do this?

To the best of my knowledge, you can't use an alias as part of a formula within the same SQL. You would either have to do this:

Select
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc,
Area+type+No+Desc1+Desc2 As EquipNo
from
equipment;

or you could try creating a subquery like this:

SELECT
t.EquipTag,
t.EquipDesc,
t.EquipTag+t.EquipDesc As EquipNo
FROM
(SELECT
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc
FROM
equipment) t

Regards,

hmscott|||Thanks hmscott,
The subquery idea is a good one. I'll give it a try. I was just curious if this could be done similar to Access or not. I must say, being an Access programmer, there are certain things in SQL Server which really annoy you. Most of my queries use this type of aliasing, so I now have to go and rewrite them to replace Aliasing.

Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.

Thanks.

Regards:
Prathmesh|||All databases are different. All databases have things that are worse than other databases or extra things that are better than other databases. There is no reason. What is included in the SQL Standard should be the same accross databases but for anything else ...|||hi Prathmesh,

try this

SELECT ISNULL(columnwithnull,'') + nonnullcolumns from yourtable|||Hi baburaj,
Yep, that is what I am using now. However, I have decided on something else. I am planning to use SQL Server backend to Access frontend, because all my forms , reports, etc. are in Access.I am going to do all the complex join queries on SQL Server side as views and link the tables via odbc to Access using the Access "link tables" facility and the required formatting I will still do on Access side. This way I can have best of both worlds. I can make use of SQL server's performance and Access' formatting features.

Thanks to all for your help and suggestion guys.|||Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.
Not entirely true - Access also provides the "+" concatenation operator where Null + "Something" = Null.
Rather than thinking of it as a bind you need to think through the implications. The + operator is great, for example, when putting together a csv address string for presentation - you don't need to use a load of conditional statements to exclude the comma if, for example, the address has no House Name.

Colum Name - Alias

We are thinking of using general purpose column names in our application schema. We want to give the option to the end user to customize the filed names to fit their business . We want to build the functionality on the generic names so that it will work for multiple customes.

Example: We may want to have 10 Strings, 10 numbers and 5 booleans pre defined and reports running off of the table. The customer can name first number as pressure, second one for length and map their data to the table. Other customer can use the first number for temperature and the second one for width.

Is there a way to do it in SQL server w/o having a lookup table for column name aliasing?

Thanks

Option 1: Any Reporting application generally has provision to display a customized column names for the table reports.

Option 2: When you query the table - you can provide column alias for the columns queried for example:

SELECT Column1 AS Pressure, Column2 AS Length FROM TableName

Option 3: You can create multiple views over the base table and the created views can have appropriate column names.

Thanks,

Sankaranarayanan MG

Colour Printing in CR 8.5

Hi
I'm using Crystal reports in My VB application. All but one of my reports are printing in colour. All my reports/designers are in the same project. Any ideas why this one report is printing in black & white?
any help would be appreciatedI do not know if this will apply to you because I am using Crystal 10.

I had a similar problem with users trying to print crystal reports in color, but they were printing in black and white. What I found was that since I created the crystal reports, all users who were accessing the crystal reports were printing from my printers default settings. I have a black and white printer in my office, so all the users were printing in balc and white.

What I had to do was, open the crystal report setup/creator, open the printer setup, select the color printer, and then save the report. After this was done, all users were able to print the crystal reports in color.

Let me know if this helps.

Collation+Greek

Dear all,
I am using an application which loads data from an SQL Database 2000 (no
service pack installed).
Some fields of the application use drop-down lists in order to enter a
value. The problem is that when I try to create the drop-down list and fill
it with values (in my case, words written in Greek and words written with
latin characters). The latin written words are sorted alphabetically but the
words written in Greek are not sorted.
Is this a problem of collation? I have installed SQL server with the
default settings (typical installation)
Thank you
It sounds like a collation problem. I've had the same problem but that
was few years ago on 6.5, and it was caused because of the use of wrong
page code. I didn't see this behavior in later versions, so I might be
wrong here, but my bet would be that the cause of the problem is using
wrong collation.
Adi

Collation+Greek

Dear all,
I am using an application which loads data from an SQL Database 2000 (no
service pack installed).
Some fields of the application use drop-down lists in order to enter a
value. The problem is that when I try to create the drop-down list and fill
it with values (in my case, words written in Greek and words written with
latin characters). The latin written words are sorted alphabetically but the
words written in Greek are not sorted.
Is this a problem of collation? I have installed SQL server with the
default settings (typical installation)
Thank youIt sounds like a collation problem. I've had the same problem but that
was few years ago on 6.5, and it was caused because of the use of wrong
page code. I didn't see this behavior in later versions, so I might be
wrong here, but my bet would be that the cause of the problem is using
wrong collation.
Adi

Collation Sequence Incompatibilities in ODBC

We are getting the following errors when running our application on a new database server that has a default collation sequence different from the server we developed the application on.

We are using SQL_Latin1_General_CP1_Cl_AS on our dev box, but some production boxes may not have this collation sequence since some of our clients are international.

The database itself uses this sequence and show this sequence even on the other server. If we restore the database on a server with this sequence the problem disappears, but that is not always an option.

I want to figure out how to bypass it, by either altering the collation sequence of the database or something else. In short I have to figure out how to make it work in their environment. Any ideas are much appreciated. Thanks. -- Henry :-)

P.S. I checked Microsoft and know that the issue exists. The only article I could find had to do with SMS and their recommendation of installing on a server with a different collation sequence does not resolve my problem. I can't always do that.
Our application is written in Cold Fusion 5.0

============ Error Messages ============

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve
collation conflict for equal to operation.

Error Occurred While Processing Request
Error Diagnostic Information

System Error &H80004005 (-2147467259).

===================================While this does not directly address the issue (My guess is both installs are 7.0?), perhaps your issue would be addressed by Sql Server 2k features. Sql Server 2k supports collation configurations somewhat differently from 7.0 i.e.(Sql Server 2K should support installing a server instance with a given collation and sucessfully hosting DBs with a variety of different collation configurations). It may be worth your while to test Sql Server 2k against your specific application(s) collation requirements.|||No. Both installs are SQL Server 2000. At least one is service pack 2.|||No. Both installs are SQL Server 2000. At least one is service pack 2.

S1 Then you must know about / have attempted using the tsql collation clause in queries (to force character string expressions which may include columns to apply a particular collation cast)? While one might have to add the clause to numerous queries and tsql statements in application code or withing stored procs, views, etc., the advantage of this approach is that the DB itself is not changed. You might want to isolate some problem code on a test restore configuration to get a better idea of how involved the approach may be.

S2 Another option may be (as you mentioned) to use Alter Table on the tables of the restored DB (on the target) to make it compatible with the applications in the locale where it is restored. Have you tested the results with this approach using a development test install / DB restore setup?

S3 I suggest keeping service packs and hotfixes the same accross installations (if for nothing else, to eliminate added vairables in addressing possible issues).

Collation problem with SQL 2000

I need to migrate an application from SQL 7.0 to SQL 2000. I know
that they use different collations, and this causes problems when
7.0 applications are moved to 2000. I would like to find a
permanent fix (so I'm not happy with using "COLLATE" in future
stored procedures) and I'd like to keep the default 2000 collation
(Latin1_General_CI_AS) in order to avoid problems if the
application needs to be moved to another 2000 server in the future.
So the only option is to change the collation of the restored DB.
What is the most efficient way of doing this?
Thanks,

Marco"Marco" <mdi00@.hotmail.com> wrote in message
news:1106659656.008295.140310@.c13g2000cwb.googlegr oups.com...
>I need to migrate an application from SQL 7.0 to SQL 2000. I know
> that they use different collations, and this causes problems when
> 7.0 applications are moved to 2000. I would like to find a
> permanent fix (so I'm not happy with using "COLLATE" in future
> stored procedures) and I'd like to keep the default 2000 collation
> (Latin1_General_CI_AS) in order to avoid problems if the
> application needs to be moved to another 2000 server in the future.
> So the only option is to change the collation of the restored DB.
> What is the most efficient way of doing this?
> Thanks,
> Marco

ALTER DATABASE can change a database's collation, and ALTER TABLE can change
a single column's collation. I seem to remember changing the database's
collation once and discovering that it didn't change the collation of
existing tables, only new ones, so make sure you test properly first.

Simon|||Marco (mdi00@.hotmail.com) writes:
> I need to migrate an application from SQL 7.0 to SQL 2000. I know
> that they use different collations, and this causes problems when
> 7.0 applications are moved to 2000. I would like to find a
> permanent fix (so I'm not happy with using "COLLATE" in future
> stored procedures) and I'd like to keep the default 2000 collation
> (Latin1_General_CI_AS) in order to avoid problems if the
> application needs to be moved to another 2000 server in the future.
> So the only option is to change the collation of the restored DB.
> What is the most efficient way of doing this?

You will need to bulk out the data, build the database from scripts
and reload data.

But I would only do this if this agrees with business requirements. If
you don't have an SQL 2000 installation, you can install with the
collation of your preference. If there already is an SQL 2000 instance
on the box with a different collation, one option is to install a
second instance on the box.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp