a query output question..

is there a way to copy and paste the column headings to excel from query
analyzer or table views ? quite often i need to send people the results of
my queries. Results in Text mode provides heading but does not cut and
paste and display well in Excel and email. Output file option is a
cumbersome process.
I used to be able to run the query in table sql view mode, then copy and
paste the results (including headings) to a spreadsheet. but it no longer
works in sql 2k.
any tips? thank you.
SteveTools... Options... Results... 'Results Output format'... and select Tab
Delimted. You'll then be able to copy and paste direct into excel...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:%23%230dgyYHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> is there a way to copy and paste the column headings to excel from query
> analyzer or table views ? quite often i need to send people the results of
> my queries. Results in Text mode provides heading but does not cut and
> paste and display well in Excel and email. Output file option is a
> cumbersome process.
> I used to be able to run the query in table sql view mode, then copy and
> paste the results (including headings) to a spreadsheet. but it no longer
> works in sql 2k.
> any tips? thank you.
> Steve
>

a query output question..

is there a way to copy and paste the column headings to excel from query
analyzer or table views ? quite often i need to send people the results of
my queries. Results in Text mode provides heading but does not cut and
paste and display well in Excel and email. Output file option is a
cumbersome process.
I used to be able to run the query in table sql view mode, then copy and
paste the results (including headings) to a spreadsheet. but it no longer
works in sql 2k.
any tips? thank you.
Steve
Tools... Options... Results... 'Results Output format'... and select Tab
Delimted. You'll then be able to copy and paste direct into excel...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:%23%230dgyYHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> is there a way to copy and paste the column headings to excel from query
> analyzer or table views ? quite often i need to send people the results of
> my queries. Results in Text mode provides heading but does not cut and
> paste and display well in Excel and email. Output file option is a
> cumbersome process.
> I used to be able to run the query in table sql view mode, then copy and
> paste the results (including headings) to a spreadsheet. but it no longer
> works in sql 2k.
> any tips? thank you.
> Steve
>

a query output question..

is there a way to copy and paste the column headings to excel from query
analyzer or table views ? quite often i need to send people the results of
my queries. Results in Text mode provides heading but does not cut and
paste and display well in Excel and email. Output file option is a
cumbersome process.
I used to be able to run the query in table sql view mode, then copy and
paste the results (including headings) to a spreadsheet. but it no longer
works in sql 2k.
any tips? thank you.
SteveTools... Options... Results... 'Results Output format'... and select Tab
Delimted. You'll then be able to copy and paste direct into excel...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:%23%230dgyYHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> is there a way to copy and paste the column headings to excel from query
> analyzer or table views ? quite often i need to send people the results of
> my queries. Results in Text mode provides heading but does not cut and
> paste and display well in Excel and email. Output file option is a
> cumbersome process.
> I used to be able to run the query in table sql view mode, then copy and
> paste the results (including headings) to a spreadsheet. but it no longer
> works in sql 2k.
> any tips? thank you.
> Steve
>

A Query

Hi All,

I am working on a project where I had to upload data from Excel
file into SQL Server.

Now the problem is that the data in Excel sheet can have any
data(garbage) ,the data entered may not have any significance to the
data already present in the database.

e.g I have an Author table
I have an Titles table

Now if I try to upload data present in Excel sheet which contains
Author name and title .It should show me all exceptions like
title is empty Author is not present in the Author Table etc.
My Solution

What I am doing is loading all the data into a temporary table checking
for not null , length of fields (using cursor) etc.
If null value exist then stop the upload

otherwise

One by one pick the data from the table (temporary) then insert into
another table (which can be used for updation purpose becuase not all
records are going to be incorrect) now this table has trigger for
insert
Now each column is checked for the integrity constraints [ referential
,check etc ]
With present Solution if my upload has 18 fields and probable
combination of 60 exceptions [ referential ,check etc ] and my upload
excel file has 20000 records It will take nearly 16 Hours and still
going.

Can you suggest me a better method the present method is killing my
application

With Warm Regards
JatinderYou don't need to validate row by row. Two inserts should do it:

/* the valid rows... */

INSERT INTO TargetTable (author_name, foo_name, bar_name, ...)
SELECT DISTINCT S.author_name, S.foo_name, S.bar_name, ...
FROM StagingTable AS S
JOIN Authors AS A
ON S.author_name = A.author_name
JOIN foo AS F
ON S.foo_name = F.foo_name
JOIN bar AS B
ON S.foo_name = B.foo_name
WHERE ... / * domain checks go here */

/* ... and the invaid ones */

INSERT INTO ExceptionTable (author_name, foo_name, bar_name, ...)
SELECT DISTINCT S.author_name, S.foo_name, S.bar_name, ...
FROM StagingTable AS S
LEFT JOIN Authors AS A
ON S.author_name = A.author_name
LEFT JOIN foo AS F
ON S.foo_name = F.foo_name
LEFT JOIN bar AS B
ON S.foo_name = B.foo_name
WHERE A.author_name IS NULL
OR F.foo_name IS NULL
OR B.bar_name IS NULL
OR ... / * domain checks go here */

--
David Portas
SQL Server MVP
--|||Thanks David,

This will surely help me solve the problem.
I was thinking of the SET BASED Solution but could not figure out one
..Thanks again

It is not be related to the post but can you suggest some ways to
improve performance of an ASP page.

With warm regards
Jatinder

A problem when opening PDF and EXCEL report

Hi, All
This is really weird. Here is the full story:
My company has a test site to test the application we are developing,
and the Reporting Server is on the same machine. Each developer has the
full application on their own machine so that they can test other parts
individually and check in to the test site later. But we are all using
the same webservice server (that test site machine) and the same report
database.
Now, when I run the report part locally, by locally, I mean I am
running the application in my own machine, but that application is
still loading the remote webservice to generate the report from the
remote database. The report generation is working correctly, I can open
the report and view.
But, if I run that part in the test site, it has a problem. It still
generates a report, but it will prompt a download window, and the
report extension name is always ASPX, (for pdf and excel file). If I
click Open, it will use Visual Studio to open it. If I click Save, and
change the extension name, it's a correct report.
More interesting, for PDF report, if the client machine has Adobe
Reader 7.0, it won't even prompt, instead, it would just show an empty
window, you know those empty windows, you can see the background
through that window, and if you move the window, the background will
follow. Yup, a kind of normal window. It only has that prompt window if
the client has Adobe Reader 6.0
Anyone has a clue how to solve this? Any help is appreciated, thank you
very much!
--
Best Regards!
SowenI saw this during the beta for 2005, but it cleared up once I went with the
released version of 2005. Has this machine ever had the beta on it?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139930602.042085.322510@.f14g2000cwb.googlegroups.com...
> Hi, All
> This is really weird. Here is the full story:
> My company has a test site to test the application we are developing,
> and the Reporting Server is on the same machine. Each developer has the
> full application on their own machine so that they can test other parts
> individually and check in to the test site later. But we are all using
> the same webservice server (that test site machine) and the same report
> database.
> Now, when I run the report part locally, by locally, I mean I am
> running the application in my own machine, but that application is
> still loading the remote webservice to generate the report from the
> remote database. The report generation is working correctly, I can open
> the report and view.
> But, if I run that part in the test site, it has a problem. It still
> generates a report, but it will prompt a download window, and the
> report extension name is always ASPX, (for pdf and excel file). If I
> click Open, it will use Visual Studio to open it. If I click Save, and
> change the extension name, it's a correct report.
> More interesting, for PDF report, if the client machine has Adobe
> Reader 7.0, it won't even prompt, instead, it would just show an empty
> window, you know those empty windows, you can see the background
> through that window, and if you move the window, the background will
> follow. Yup, a kind of normal window. It only has that prompt window if
> the client has Adobe Reader 6.0
> Anyone has a clue how to solve this? Any help is appreciated, thank you
> very much!
> --
> Best Regards!
> Sowen
>|||hi, Bruce
thanks for your reply.
But we are still using VS 2003, SQL 2000 and .NET framework 1.1|||Ahh, OK. What Service Pack are you on?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139933336.752696.258260@.g44g2000cwa.googlegroups.com...
> hi, Bruce
> thanks for your reply.
> But we are still using VS 2003, SQL 2000 and .NET framework 1.1
>|||for reporting service, SP2
for 2003 server, SP1,|||How are your viewing the report? In your own app? Report Manager? If your
own app are you using URL integration or web services? If in your own app,
does it work properly when you use Report Manager?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139934523.097245.13230@.g44g2000cwa.googlegroups.com...
> for reporting service, SP2
> for 2003 server, SP1,
>|||via URL. sth like
http://localhost/mmis/ReportViewer/tabid/140/desktopdefault/+/Default.aspx,
(we are using DNN too.)
the code generating the report is correct.
It works properly if I run all application on my own machine's IIS.
It only has those problems if I run it on our test site (remotely)|||OK, I think what is happening is the Server 2003 internet security. Try
putting in the name of the machine instead of localhost and add the local
machine to the safezone.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139936364.847940.143500@.g44g2000cwa.googlegroups.com...
> via URL. sth like
> http://localhost/mmis/ReportViewer/tabid/140/desktopdefault/+/Default.aspx,
> (we are using DNN too.)
> the code generating the report is correct.
> It works properly if I run all application on my own machine's IIS.
> It only has those problems if I run it on our test site (remotely)
>|||do you mean adding our test site url in IE security trusted site?|||Here is what I suggest you do. Go to the test server. Open up IE and go to
report manager and make sure your reports and exports etc work as you they
should. Until you know it all works from report manager I wouldn't waste
much time wondering why it isn't working from your app. My guess is that you
will see some of the same issues there. Windows 2003 SP1 really protects
what web sites you can go to. You most likely need to add to trusted sites
in IE on the server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139937391.453464.226010@.z14g2000cwz.googlegroups.com...
> do you mean adding our test site url in IE security trusted site?
>|||it works on our test site.
It is frustrated that the same code actually works on our another
remote test site, I just found out.
Is there any security configuration issue I should pay attention to?
It's strange that the same thing working on one machine but not another
machine.|||I mean it works on the test site report manager. I can see everything
running properly. But I have the same problem if I run the ASP.NET
program to create the PDF or EXCEL report, the prompt window asks me to
download a ASPX file.
But if I run the same code on another test machine, there is no such a
problem.
strange...