Combining data from multiple columns or views

I am trying to combine/report on data that is in one table. Due to system
limitations I have 3 columns that store the same type of business data.
Salesperson A, B and C are separate columns but need to be combined for
reporting. Can I create another table where I can use a select into to put
Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
into Column 1? If so, how?
Here is a sample data line:
Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
SalesAmount
I thought I could combine it into the following layout:
Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.Hi Dan,
Do you want to concatenate Salespersons A, B, and C all into one column, or
do you want to create a new row for Salesperson A, Salesperson B, and
salesperson C?
To concatenate, you can do the following:
select SalespersonA+SalespersonB+salespersonC, CommissionRate, DocNumber,
SalesAmount from tbl_name
If you want to create a new table and then insert into it from the other
table where each salesperson has their own row, then it would look something
like this:
create the table with columns for Salesperson, CommissionRate, DocNumber,
SalesAmount
Insert into NewTable
select SalespersonA, commissionRate, DocNumber, SalesAmount
Insert into NewTable
select SalespersonB, CommissionRate, DocNumber, SalesAmount
ect.
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Thanks for the help... I tried the following syntax and it was not working:
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS, THRDPRTYNAME,
COMMRATE, REFERRATE) VALUES (SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW)
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Change it to
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME,
COMMRATE, REFERRATE) SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW
Regards
Amish Shah|||you can also do it with unions and renaming the columns.

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
>
>.
>

columns in full text query

Hi
Is there a system query that tells me which columns are a fulltext index for
a particular catalog?
Cheers
James
you could try sp_help_fulltext_columns in a full text enabled database which
will tell you all the tables and the columns in these tables which are being
full text indexed.
Or you could use sp_help_fulltext_tables_cursor and pass it the catalog name
and then iterate the results set as illustrated below. In the below example
the catalog name is test.
USE pubs
GO
DECLARE @.mycursor CURSOR
EXEC sp_help_fulltext_tables_cursor @.mycursor OUTPUT, 'test'
FETCH NEXT FROM @.mycursor
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @.mycursor
END
CLOSE @.mycursor
DEALLOCATE @.mycursor
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23WqhRpGsEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that tells me which columns are a fulltext index
for
> a particular catalog?
> Cheers
> James
>

columns in full text query

Hi
Is there a system query that tells me which columns are a fulltext index for
a particular catalog?
Cheers
JamesFrom the BOL:
sp_help_fulltext_columns
Returns the columns designated for full-text indexing.
Rick Sawtell
MCT, MCSD, MCDBA
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:uZCdpjGsEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a system query that tells me which columns are a fulltext index
for
> a particular catalog?
> Cheers
> James
>

columns in full text query

Hi
Is there a system query that tells me which columns are a fulltext index for
a particular catalog?
Cheers
JamesFrom the BOL:
sp_help_fulltext_columns
Returns the columns designated for full-text indexing.
Rick Sawtell
MCT, MCSD, MCDBA
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:uZCdpjGsEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a system query that tells me which columns are a fulltext index
for
> a particular catalog?
> Cheers
> James
>

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.
The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.
|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.
|||Uytkownik Roger Wolter[MSFT] napisa:

> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much more
> efficient than converting a character column to integers which you're making
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn't
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||Uytkownik Roger Wolter[MSFT] napisa:

> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much mo
re
> efficient than converting a character column to integers which you're maki
ng
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn'
t
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||U¿ytkownik Roger Wolter[MSFT] napisa³:
> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much more
> efficient than converting a character column to integers which you're making
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn't
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Collecting data from remote DBs

Got to start planning for a project that requires our system to collect data from different types of DB platforms remotely from our customers and store them in our SQL DB. Anybody know of any references I could read or where to start with this? I have a couple of ideas, but need to look at all the aspects of this to ensure that it's done correctly.

Thanks.What kind of database? Locating on what platform? How to remotely to access it? Through HTTP or something else?

In general, web service could be a possible solution.|||A couple of our clients are using SQL server, and others are using proprietary DB systems that have XML export capabilities. All are MS based systems and the data collection preference would be via http requests.

Thanks.

Collation troubles with SQLServer Express 2005

Greetings,
I installed SQL Server Express 2005 in my system, and created a simple database to store mailng addresses. The database has an email field and a name field. The data to populate the database came from an Excel file saved as a Unicode Text file containing the data as:
xxx@.somemail.com; To?o Peres yyy@.someothermail.com; Iván Cárdenas Note that this data has names with accented characters.
I then tried to import the data into the SQLServer Express with the following command:
bulk insert lista
from 'c:\temp\todas.csv'
with (
DATAFILETYPE ='widechar',
FIELDTERMINATOR =';'
)
I got the following error message:
Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.
And the data was imported as:
xxx@.somemail.com To±o Peres yyy@.someothermail.com Ivfn C?rdenas
I tried using the DATAFILETYPE ='native' and DATAFILETYPE ='widenative' options but these caused the following error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I want to avoid re-installing the server and the Express 2005 edition does not come with the tools needed to rebuild the master database anyway (see this link).
When I type the data with the Server Management Studio Express program, I can insert the accented characters correctly.
Does anyone out there know how to deal with this problem? How do I tell SQL Server Express 2005 to insert the text from the text file and keep the accented characters as they were initially?
I will welcome any help.
Ivan.

Hi Ivan,

Are you sure you saved the spreadsheet as Unicode Text? The .csv filetype suggests you didn't - unless you manually changed it.

I tried using the data above in Excel, saving as Unicode Text and then bulk inserting and it worked fine.

Thanks