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
>
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Combining multiple columns into one column.
Combing multiple columns like [LastName],[FirstName] and
[MiddleName]into one column named as [Name] is very simple in Access,
but how will i do that in SQL? Any suggestions? PLease?
Thanks in advance,
Geri
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You have to add another column, update with existing data,
and then drop existing columns.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Geri Gavertz" <gerific@.yahoo.com> wrote in message
news:ezPCuV8GFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Combing multiple columns like [LastName],[FirstName] and
> [MiddleName]into one column named as [Name] is very simple in Access,
> but how will i do that in SQL? Any suggestions? PLease?
> Thanks in advance,
> Geri
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||same as in Access
Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
Madhivanan|||same as in Access
Select LastName+' ' + FirstName + ' ' + MiddleName as Name from Table
Madhivanan|||<madhivanan2001@.gmail.com> wrote in message
news:1109400766.869281.200560@.o13g2000cwo.googlegroups.com...
> same as in Access
> Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
> Madhivanan
>
You might want to wrap them in IsNull so that a NULL in one of the columns
doesn't NULL out the entire result:
Select IsNull(FirstName, '') + ' ' + IsNull(MiddleName, '') + ' ' +
IsNull(LastName, '') As FullName from MyTable
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
[MiddleName]into one column named as [Name] is very simple in Access,
but how will i do that in SQL? Any suggestions? PLease?
Thanks in advance,
Geri
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You have to add another column, update with existing data,
and then drop existing columns.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Geri Gavertz" <gerific@.yahoo.com> wrote in message
news:ezPCuV8GFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Combing multiple columns like [LastName],[FirstName] and
> [MiddleName]into one column named as [Name] is very simple in Access,
> but how will i do that in SQL? Any suggestions? PLease?
> Thanks in advance,
> Geri
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||same as in Access
Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
Madhivanan|||same as in Access
Select LastName+' ' + FirstName + ' ' + MiddleName as Name from Table
Madhivanan|||<madhivanan2001@.gmail.com> wrote in message
news:1109400766.869281.200560@.o13g2000cwo.googlegroups.com...
> same as in Access
> Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
> Madhivanan
>
You might want to wrap them in IsNull so that a NULL in one of the columns
doesn't NULL out the entire result:
Select IsNull(FirstName, '') + ' ' + IsNull(MiddleName, '') + ' ' +
IsNull(LastName, '') As FullName from MyTable
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
Combining info from two tables?
Hello, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other.
So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written? Sorry if this is a little confusing but im having a hard time visualising how to do this.Your query will look something like this:
select t1.col1, t1.col2, t1.col3, t1.code, t2.description
from t1
join t2 on t1.code = t2.code
where ...;|||Cheers, it works. I've only been using very basic SQl views in the past so joins etc are new to me. I do have another question tho, I'm writing these queries in an ASP page and they are very long single lines at the mo, how do I break them up into seperate shorter lines?|||Something like this, if I recall correctly:
strSQL = "select empno" _
& " from emp" _
& " where ename = ?"
i.e. the underscore is the "continued on next line" marker for VBScript.|||Thanks again for you help andrew|||Ok i've hit another snag with this, there are two different columns in the first table that are codes that need to be compared to the code listing in the 2nd table and then their code meanings sent back. I ve done this with one which is where u're first piece of code comes in handy but i can't join two columns in the 1st table to the one in the 2nd.|||Oh no, not the "One True Look-up Table"? :(
You can join to the same table twice like this:
select t1.col1, t1.col2, t1.col3, t1.code1,
t2_1.description, t1.code2, t2_2.description
from t1
join t2 t2_1 on t1.code1 = t2_1.code
join t2 t2_2 on t1.code2 = t2_2.code
where ...;
t2_1 and t2_2 are "aliases" for the table t2 which now appears twice in the query.|||that's funny, i took "one of the columns in this table is a set of codes" to mean something completely different (a denormalized table)
looks like you may have understood the requirements better than i did, tony
see http://forums.devshed.com/t193376/s.html|||Who knows? Maybe you are right - it's equally possible!|||Sorry guys I think i'm over complicating the matter, let me try to explain it in a minimal way,
Table 1 with 3 columns, we'll call it date, the next column we will call code1 and the last column we will call code2.
Table 2 has 2 columns, one called error_codes and another called translation with is the text meaning of the error codes.
I want the query to select all the records in the first table for a certain criteria and give the translations for the code columns in the first table from the 2nd table.|||Well, I think I have already given you the SQL for that above.|||I've actually found that the 2nd column in the first table referred to a different set of codes in another table so the join will work now, thank you both for your help, i'm learning it bit by bit!sqlsql
So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written? Sorry if this is a little confusing but im having a hard time visualising how to do this.Your query will look something like this:
select t1.col1, t1.col2, t1.col3, t1.code, t2.description
from t1
join t2 on t1.code = t2.code
where ...;|||Cheers, it works. I've only been using very basic SQl views in the past so joins etc are new to me. I do have another question tho, I'm writing these queries in an ASP page and they are very long single lines at the mo, how do I break them up into seperate shorter lines?|||Something like this, if I recall correctly:
strSQL = "select empno" _
& " from emp" _
& " where ename = ?"
i.e. the underscore is the "continued on next line" marker for VBScript.|||Thanks again for you help andrew|||Ok i've hit another snag with this, there are two different columns in the first table that are codes that need to be compared to the code listing in the 2nd table and then their code meanings sent back. I ve done this with one which is where u're first piece of code comes in handy but i can't join two columns in the 1st table to the one in the 2nd.|||Oh no, not the "One True Look-up Table"? :(
You can join to the same table twice like this:
select t1.col1, t1.col2, t1.col3, t1.code1,
t2_1.description, t1.code2, t2_2.description
from t1
join t2 t2_1 on t1.code1 = t2_1.code
join t2 t2_2 on t1.code2 = t2_2.code
where ...;
t2_1 and t2_2 are "aliases" for the table t2 which now appears twice in the query.|||that's funny, i took "one of the columns in this table is a set of codes" to mean something completely different (a denormalized table)
looks like you may have understood the requirements better than i did, tony
see http://forums.devshed.com/t193376/s.html|||Who knows? Maybe you are right - it's equally possible!|||Sorry guys I think i'm over complicating the matter, let me try to explain it in a minimal way,
Table 1 with 3 columns, we'll call it date, the next column we will call code1 and the last column we will call code2.
Table 2 has 2 columns, one called error_codes and another called translation with is the text meaning of the error codes.
I want the query to select all the records in the first table for a certain criteria and give the translations for the code columns in the first table from the 2nd table.|||Well, I think I have already given you the SQL for that above.|||I've actually found that the 2nd column in the first table referred to a different set of codes in another table so the join will work now, thank you both for your help, i'm learning it bit by bit!sqlsql
Combining Cross-Tab and charts
Dear all,
I'm a noob to SQL Reporting Services but have plenty of experience with
other MS applications (including excel and access). I was wondering whether
it's possible to combine a cross-tab and a chart within a single report. If
so, how do you do this? I believe the power of a report (often) exists out of
numbers combined with a graphical display.
thanks in advance,
mischaOn Apr 26, 10:22 am, mischa <mis...@.discussions.microsoft.com> wrote:
> Dear all,
> I'm a noob to SQL Reporting Services but have plenty of experience with
> other MS applications (including excel and access). I was wondering whether
> it's possible to combine a cross-tab and a chart within a single report. If
> so, how do you do this? I believe the power of a report (often) exists out of
> numbers combined with a graphical display.
> thanks in advance,
> mischa
If I understand you correctly, you should be able to use a matrix
control and a chart control.
Regards,
Enrique Martinez
Sr. Software Consultant
I'm a noob to SQL Reporting Services but have plenty of experience with
other MS applications (including excel and access). I was wondering whether
it's possible to combine a cross-tab and a chart within a single report. If
so, how do you do this? I believe the power of a report (often) exists out of
numbers combined with a graphical display.
thanks in advance,
mischaOn Apr 26, 10:22 am, mischa <mis...@.discussions.microsoft.com> wrote:
> Dear all,
> I'm a noob to SQL Reporting Services but have plenty of experience with
> other MS applications (including excel and access). I was wondering whether
> it's possible to combine a cross-tab and a chart within a single report. If
> so, how do you do this? I believe the power of a report (often) exists out of
> numbers combined with a graphical display.
> thanks in advance,
> mischa
If I understand you correctly, you should be able to use a matrix
control and a chart control.
Regards,
Enrique Martinez
Sr. Software Consultant
Thursday, March 22, 2012
Combinig fields
Hi all,
In Access I can do a simply query and put something like:
SELECT FirstName & ", " & LastName AS FullName FROM People
doing this woud combine the two fields into one... can this be done in a SQL
stored procedure?
Thanks
GavUse the + operator to combine columns...just make sure that you don't =
add numbers if you really want to combine them as strings. =20
use pubs
go
select au_lname, au_fname, 'FullName' =3D au_lname + ', ' + au_fname =
From authors
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>=20
> In Access I can do a simply query and put something like:
>=20
> SELECT FirstName & ", " & LastName AS FullName FROM People
>=20
> doing this woud combine the two fields into one... can this be done in =
a SQL
> stored procedure?
>=20
> Thanks
> Gav
>=20
>|||I have tried this but it simply returns null all the time.
Regards
Gav
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
Use the + operator to combine columns...just make sure that you don't add
numbers if you really want to combine them as strings.
use pubs
go
select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
authors
Keith
"Gav" <spam@.spam.com> wrote in message
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> In Access I can do a simply query and put something like:
> SELECT FirstName & ", " & LastName AS FullName FROM People
> doing this woud combine the two fields into one... can this be done in a
SQL
> stored procedure?
> Thanks
> Gav
>|||I can see whats happening, if one of the fields is null it only returns
null... can I get it to ignore the field if it is null?
Regards
Gav
"Gav" <spam@.spam.com> wrote in message
news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
> I have tried this but it simply returns null all the time.
> Regards
> Gav
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
> Use the + operator to combine columns...just make sure that you don't add
> numbers if you really want to combine them as strings.
> use pubs
> go
> select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
> authors
> --
> Keith
>
> "Gav" <spam@.spam.com> wrote in message
> news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> SQL
>|||There are a few options that you can use...
Here are a few that come to mind:
CREATE TABLE #foo (col1 char(5), col2 char(5))
INSERT INTO #foo (col1, col2) VALUES ('test', null)
INSERT INTO #foo (col1, col2) VALUES ('test1', 'test1')
GO
SELECT col1 + ' ' + col2 FROM #foo=20
SELECT col1 + ' ' + ISNULL(col2, '') FROM #foo
SELECT col1 + ' ' + COALESCE(col2, '') FROM #foo
SELECT col1 + ' ' + CASE WHEN col2 IS NULL THEN '' ELSE col2 END FROM =
#foo
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only =
returns
> null... can I get it to ignore the field if it is null?
>=20
> Regards
> Gav
>=20
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
don't add
From
done in a
>=20
>|||You can replace that column value with an empty string:
SELECT
au_fname + COALESCE(initial, '') + au_lname AS full_name
FROM tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gav" <spam@.spam.com> wrote in message
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only returns
> null... can I get it to ignore the field if it is null?
> Regards
> Gav
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
add
a
>|||Thanks for the help Keith and Tibor thats works great.
Cheers
Gav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU0HchECEHA.3400@.tk2msftngp13.phx.gbl...
> You can replace that column value with an empty string:
> SELECT
> au_fname + COALESCE(initial, '') + au_lname AS full_name
> FROM tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Gav" <spam@.spam.com> wrote in message
> news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> add
From
in
> a
>
In Access I can do a simply query and put something like:
SELECT FirstName & ", " & LastName AS FullName FROM People
doing this woud combine the two fields into one... can this be done in a SQL
stored procedure?
Thanks
GavUse the + operator to combine columns...just make sure that you don't =
add numbers if you really want to combine them as strings. =20
use pubs
go
select au_lname, au_fname, 'FullName' =3D au_lname + ', ' + au_fname =
From authors
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>=20
> In Access I can do a simply query and put something like:
>=20
> SELECT FirstName & ", " & LastName AS FullName FROM People
>=20
> doing this woud combine the two fields into one... can this be done in =
a SQL
> stored procedure?
>=20
> Thanks
> Gav
>=20
>|||I have tried this but it simply returns null all the time.
Regards
Gav
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
Use the + operator to combine columns...just make sure that you don't add
numbers if you really want to combine them as strings.
use pubs
go
select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
authors
Keith
"Gav" <spam@.spam.com> wrote in message
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> In Access I can do a simply query and put something like:
> SELECT FirstName & ", " & LastName AS FullName FROM People
> doing this woud combine the two fields into one... can this be done in a
SQL
> stored procedure?
> Thanks
> Gav
>|||I can see whats happening, if one of the fields is null it only returns
null... can I get it to ignore the field if it is null?
Regards
Gav
"Gav" <spam@.spam.com> wrote in message
news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
> I have tried this but it simply returns null all the time.
> Regards
> Gav
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
> Use the + operator to combine columns...just make sure that you don't add
> numbers if you really want to combine them as strings.
> use pubs
> go
> select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
> authors
> --
> Keith
>
> "Gav" <spam@.spam.com> wrote in message
> news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> SQL
>|||There are a few options that you can use...
Here are a few that come to mind:
CREATE TABLE #foo (col1 char(5), col2 char(5))
INSERT INTO #foo (col1, col2) VALUES ('test', null)
INSERT INTO #foo (col1, col2) VALUES ('test1', 'test1')
GO
SELECT col1 + ' ' + col2 FROM #foo=20
SELECT col1 + ' ' + ISNULL(col2, '') FROM #foo
SELECT col1 + ' ' + COALESCE(col2, '') FROM #foo
SELECT col1 + ' ' + CASE WHEN col2 IS NULL THEN '' ELSE col2 END FROM =
#foo
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only =
returns
> null... can I get it to ignore the field if it is null?
>=20
> Regards
> Gav
>=20
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
don't add
From
done in a
>=20
>|||You can replace that column value with an empty string:
SELECT
au_fname + COALESCE(initial, '') + au_lname AS full_name
FROM tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gav" <spam@.spam.com> wrote in message
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only returns
> null... can I get it to ignore the field if it is null?
> Regards
> Gav
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
add
a
>|||Thanks for the help Keith and Tibor thats works great.
Cheers
Gav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU0HchECEHA.3400@.tk2msftngp13.phx.gbl...
> You can replace that column value with an empty string:
> SELECT
> au_fname + COALESCE(initial, '') + au_lname AS full_name
> FROM tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Gav" <spam@.spam.com> wrote in message
> news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> add
From
in
> a
>
Monday, March 19, 2012
Combine many rows to one row?
Dear friends,
I have a problem that need some help from expert.Is there any way I could combine many rows into a row in Access using Visual Basic. I want to change the below table from TABLE A to TABLE B
Output:
Your help would be greatly appreciated
Thanks a lot,
Chicky
Chicky
You might want to give this thread from yesterday a look:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1335992&SiteID=1
Sunday, March 11, 2012
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+ 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 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 access to SERVERPROPERTY values
Is it possible to get the properties retrievable with T-SQL
SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
object that can provide this kind of information (e.g. LicenseType or
NumLicenses)? Any Ideas?You could use SQL DMO object model. See SQL Server Books Online for more
info. See SQLServer2 object for a start.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Holger" <atlan@.tournedos.de> wrote in message
news:1124040877.686226.191970@.g43g2000cwa.googlegroups.com...
> Is it possible to get the properties retrievable with T-SQL
> SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
> object that can provide this kind of information (e.g. LicenseType or
> NumLicenses)? Any Ideas?
>|||Thank you for your reply but in COM programming it is always good to
read the manual first. The object SQLServer2 does not provide access to
the properties LicenseType or NumLicenses exposed by SERVERPROPERTY.
These properties are of special interest to me. Any further object you
can recommend?|||Another approach is simply the registry to get these values
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL
Server\80\MSSQLLicenseInfo\MSSQL8.00]
"ConcurrentLimit"=dword:00000006
"Mode"=dword:00000002
Mode = 2 means PerProcessor
Mode != 2 means PerSeat
ConcurrentLimit = Number of Seats OR Processors
Just to complete this topic,
Holger
SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
object that can provide this kind of information (e.g. LicenseType or
NumLicenses)? Any Ideas?You could use SQL DMO object model. See SQL Server Books Online for more
info. See SQLServer2 object for a start.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Holger" <atlan@.tournedos.de> wrote in message
news:1124040877.686226.191970@.g43g2000cwa.googlegroups.com...
> Is it possible to get the properties retrievable with T-SQL
> SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
> object that can provide this kind of information (e.g. LicenseType or
> NumLicenses)? Any Ideas?
>|||Thank you for your reply but in COM programming it is always good to
read the manual first. The object SQLServer2 does not provide access to
the properties LicenseType or NumLicenses exposed by SERVERPROPERTY.
These properties are of special interest to me. Any further object you
can recommend?|||Another approach is simply the registry to get these values
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL
Server\80\MSSQLLicenseInfo\MSSQL8.00]
"ConcurrentLimit"=dword:00000006
"Mode"=dword:00000002
Mode = 2 means PerProcessor
Mode != 2 means PerSeat
ConcurrentLimit = Number of Seats OR Processors
Just to complete this topic,
Holger
Thursday, March 8, 2012
Column-order an access speed?
Is there any truth to this: the placement of fields in a table relates to field access speed. So, frequently accessed fields should be placed in the beginning of the table while fields infrequently used can be placed toward the end.
TIA,
Barkingdog
Doubt if placing frequently accessed fields first have any sense. Maybe there are something when you are accessing fields by names in client application, but overhead (if any) is hardly noticeable.|||In general I don't believe this is true and in my experience this would be a micro optimisation and its highly likely there are 10's if not 100's of more impactful ways to impact the perf of your app...|||I agree with Euan. I do think that his is just nit-picking in performance optimization. Being on the battleground in my consultant work I always see that the most optimization regarding indexes and writing queries was not considered by the customers. *If* this *should* get you a performace gain, this should be that small you you probably will not feel the effect.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Sunday, February 19, 2012
Column header properties gone after using URL report access
I set a border and interactive sort action properties to columns in a
report. When I render the report using a URL I lose those column header
properties. Is there a URL command to pass those settings with the URL?
(I'm using 'rs:Command=Render&rs:Format=HTML4.0' plus passing some
params)
For more description see my post at: Pagination question - retrieve
only records for each page
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/024cb61912a7630e/4e5c08587fee845a#4e5c08587fee845a
FredMy mistake. I copied and pasted the url from the previous version and
didn't update the file name in the url. I was just calling the old
version when ever I hit my 'next' button. Everything is working now.
report. When I render the report using a URL I lose those column header
properties. Is there a URL command to pass those settings with the URL?
(I'm using 'rs:Command=Render&rs:Format=HTML4.0' plus passing some
params)
For more description see my post at: Pagination question - retrieve
only records for each page
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/024cb61912a7630e/4e5c08587fee845a#4e5c08587fee845a
FredMy mistake. I copied and pasted the url from the previous version and
didn't update the file name in the url. I was just calling the old
version when ever I hit my 'next' button. Everything is working now.
column description?
Hi All
how can i access a normal sql server 2000 tables column description and give it a value from a query or from a stored procedure?
thanks for the help!Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
--
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!|||http://www.aspfaq.com/2244
--
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
how can i access a normal sql server 2000 tables column description and give it a value from a query or from a stored procedure?
thanks for the help!Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
--
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!|||http://www.aspfaq.com/2244
--
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
Thursday, February 16, 2012
column description?
Hi All
how can i access a normal sql server 2000 tables column description and give it a value from a query or from a stored procedure?
thanks for the help!
Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!
|||Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!
|||http://www.aspfaq.com/2244
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||http://www.aspfaq.com/2244
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||Thanks for the help!
I got it!!
have a nice day!
"Hari" wrote:
> Hi,
> Did you meant extended property? If yes then;
> Have a look into procedure sp_addextendedproperty in books online.
> Sample:-
> This example adds the property ('caption,' 'Employee ID') to column 'ID' in
> table 'T1.'
> CREATE table T1 (id int , name char (20))
> GO
> EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
> 'table', T1, 'column', id
>
> See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
> --
> Thanks
> Hari
> MCDBA
> "m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
> news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> give it a value from a query or from a stored procedure?
>
>
|||Thanks for the help!
I got it!!
have a nice day!
"Hari" wrote:
> Hi,
> Did you meant extended property? If yes then;
> Have a look into procedure sp_addextendedproperty in books online.
> Sample:-
> This example adds the property ('caption,' 'Employee ID') to column 'ID' in
> table 'T1.'
> CREATE table T1 (id int , name char (20))
> GO
> EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
> 'table', T1, 'column', id
>
> See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
> --
> Thanks
> Hari
> MCDBA
> "m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
> news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> give it a value from a query or from a stored procedure?
>
>
how can i access a normal sql server 2000 tables column description and give it a value from a query or from a stored procedure?
thanks for the help!
Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!
|||Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!
|||http://www.aspfaq.com/2244
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||http://www.aspfaq.com/2244
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!
|||Thanks for the help!
I got it!!
have a nice day!
"Hari" wrote:
> Hi,
> Did you meant extended property? If yes then;
> Have a look into procedure sp_addextendedproperty in books online.
> Sample:-
> This example adds the property ('caption,' 'Employee ID') to column 'ID' in
> table 'T1.'
> CREATE table T1 (id int , name char (20))
> GO
> EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
> 'table', T1, 'column', id
>
> See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
> --
> Thanks
> Hari
> MCDBA
> "m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
> news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> give it a value from a query or from a stored procedure?
>
>
|||Thanks for the help!
I got it!!
have a nice day!
"Hari" wrote:
> Hi,
> Did you meant extended property? If yes then;
> Have a look into procedure sp_addextendedproperty in books online.
> Sample:-
> This example adds the property ('caption,' 'Employee ID') to column 'ID' in
> table 'T1.'
> CREATE table T1 (id int , name char (20))
> GO
> EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
> 'table', T1, 'column', id
>
> See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
> --
> Thanks
> Hari
> MCDBA
> "m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
> news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> give it a value from a query or from a stored procedure?
>
>
column description?
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!|||http://www.aspfaq.com/2244
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!|||Thanks for the help!
I got it!!
have a nice day!
"Hari" wrote:
> Hi,
> Did you meant extended property? If yes then;
> Have a look into procedure sp_addextendedproperty in books online.
> Sample:-
> This example adds the property ('caption,' 'Employee ID') to column 'ID' i
n
> table 'T1.'
> CREATE table T1 (id int , name char (20))
> GO
> EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
> 'table', T1, 'column', id
>
> See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended propert
y.
> --
> Thanks
> Hari
> MCDBA
> "m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
> news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> give it a value from a query or from a stored procedure?
>
>
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!Hi,
Did you meant extended property? If yes then;
Have a look into procedure sp_addextendedproperty in books online.
Sample:-
This example adds the property ('caption,' 'Employee ID') to column 'ID' in
table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', T1, 'column', id
See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended property.
Thanks
Hari
MCDBA
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!|||See fn_listextendedproperty and sp_addextendedproperty in SQL Server 2000
Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
Hi All
how can i access a normal sql server 2000 tables column description and give
it a value from a query or from a stored procedure?
thanks for the help!|||http://www.aspfaq.com/2244
http://www.aspfaq.com/
(Reverse address to reply.)
"m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> Hi All
> how can i access a normal sql server 2000 tables column description and
give it a value from a query or from a stored procedure?
> thanks for the help!|||Thanks for the help!
I got it!!
have a nice day!
"Hari" wrote:
> Hi,
> Did you meant extended property? If yes then;
> Have a look into procedure sp_addextendedproperty in books online.
> Sample:-
> This example adds the property ('caption,' 'Employee ID') to column 'ID' i
n
> table 'T1.'
> CREATE table T1 (id int , name char (20))
> GO
> EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
> 'table', T1, 'column', id
>
> See the function ::FN_LISTEXTENDEDPROPERTY to retrive the extended propert
y.
> --
> Thanks
> Hari
> MCDBA
> "m.ahrens" <mahrens@.discussions.microsoft.com> wrote in message
> news:AD5C515B-5CF1-41A5-B264-29BB565DE8C6@.microsoft.com...
> give it a value from a query or from a stored procedure?
>
>
Tuesday, February 14, 2012
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.
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.
Subscribe to:
Posts (Atom)