Drill down slow: table

Showing posts with label table. Show all posts

Thursday, March 29, 2012

Drawing line between table rows and columns

how to insert line between rows and columns in reporting services 2005Couldn't you just set the borderstyle top, bottom, left, or right according to where you wanted the lines?

Drawing line at last record

Hi.
I have a table containing two groups and a detail area.My report has multiple pages.I want to draw line after the last record on every page.I think that expression will be written in record's bottom expression area.How can I choose the last record in every page.Or is there another way to do this.Could you help me please?why wont u draw a line on page footer? when u draw a line on page footer it will diplay on the bottom of each page.|||Thanks but it didn't work
I drawed horizontal line across the page footer.
But there is several blank rows between the last record and the line in page footer.And this is never looking good.If you have another idea about this please share with me.

Draw Horizontal Line at Threshold

I have a table in reporting services. I have a field that calcuates a
percentage and the report is sorted in ascending order by this
percentage. I want to draw a single red horizontal line at a
threshold, let's say 95% so that any record lower than 95% would be
above the line and any record higher would be below the line. How can
I do this?On Apr 4, 6:54 am, robertpet...@.hotmail.com wrote:
> I have a table in reporting services. I have a field that calcuates a
> percentage and the report is sorted in ascending order by this
> percentage. I want to draw a single red horizontal line at a
> threshold, let's say 95% so that any record lower than 95% would be
> above the line and any record higher would be below the line. How can
> I do this?
I would suggest adding an extra query/stored procedure return field/
column to include in the report that is used as a flag for the
percentages. Have the flag set for the minimum row at or above 95%.
Then in the report, set the table properties (F4) (BorderColor->Top
and BorderStyle->Top) according to that flag. Something like these
expressions should work:
For BorderColor:Top:
=iif(Fields!Flag.Value = 1, "Red", "White")
For BorderStyle:Top:
=iif(Fields!Flag.Value = 1, "Solid", "None")
Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Dragging multiple fields to a table?

Ok, there has to be a way of dragging multiple fields from a dataset
to a table like there is in Access. I have a dataset of about 30-40
columns and it will be excruciating to right click on a column in the
table 30-40 times to add more columns so that I can then drag one at a
time all the fields to each new column.
Please tell me that I don't have to do this one by one. Thanks for
any input.You can drag one field at a time from the field list and drop it in the
cell. You don't to right-click on the cell and add it through the
expression builder. you can select multiple fields and drag&drop them but I
don't think that will work unless your using a list.
"Stoma_Kalos" <fzuma@.yahoo.com> wrote in message
news:e876e3c8.0407211308.39c6da3a@.posting.google.com...
> Ok, there has to be a way of dragging multiple fields from a dataset
> to a table like there is in Access. I have a dataset of about 30-40
> columns and it will be excruciating to right click on a column in the
> table 30-40 times to add more columns so that I can then drag one at a
> time all the fields to each new column.
> Please tell me that I don't have to do this one by one. Thanks for
> any input.

Tuesday, March 27, 2012

Downloading File located in SQL Server with ASP

I have a SQL 2000 Standard server setup and I have a table in one of my
databases to store files in. I am using asp code to retrieve the files from
the database. I can download txt, bmp files just fine but when it comes to
pdf documents it just hangs at the downloading the file screen and then gives
me a timeout error after awhile. It is not a problem with the file sizes. I
am testing it with a 20 and 60k pdf files and one of the txt files I used to
test with is a few mb. I am thinking it's either an IIS server config issue
or SQL Server config problem. The IIS server is for our intranet and is not
locked down. Both IIS and SQL are running on the same box which is a Windows
2003 Enterprise server. Any help would be great.
Thanks,
Brett Bishop
MCSE, MCP+I
US Bankruptcy Court"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:0880684A-A141-4D78-842A-041C501DBCF5@.microsoft.com...
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files
from
> the database. I can download txt, bmp files just fine but when it comes
to
> pdf documents it just hangs at the downloading the file screen and then
gives
> me a timeout error after awhile. It is not a problem with the file sizes.
I
> am testing it with a 20 and 60k pdf files and one of the txt files I used
to
> test with is a few mb. I am thinking it's either an IIS server config
issue
> or SQL Server config problem. The IIS server is for our intranet and is
not
> locked down. Both IIS and SQL are running on the same box which is a
Windows
> 2003 Enterprise server. Any help would be great.
Can you access PDF files via URL from the file system?|||fibreiv wrote:
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files from
> the database. I can download txt, bmp files just fine but when it comes to
> pdf documents it just hangs at the downloading the file screen and then gives
> me a timeout error after awhile. It is not a problem with the file sizes. I
> am testing it with a 20 and 60k pdf files and one of the txt files I used to
> test with is a few mb. I am thinking it's either an IIS server config issue
> or SQL Server config problem. The IIS server is for our intranet and is not
> locked down. Both IIS and SQL are running on the same box which is a Windows
> 2003 Enterprise server. Any help would be great.
Perhaps it has to do with the content type when the file is being sent
down. You may also want to set the content-disposition to indicate to
download the file, "Content-Disposition: Attachment" in the response header.
You aren't saying if you are trying to open up the file directly or save
it to disk first.
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Hi,
This is what i would do.
Try to download a PDF file from the file system (this rules out a problem
with IIS serving PDF-files).
Use WFetch[1] to request the PDF page (from the db), and see if IIS gives
a complete reply back (this will rule out a client-side problem).
Usually these kind of problems are caused by the programmers code. SQL
Server only stores the data, and IIS only "gives" the client the data.
Your code however do everything in between, and that is where it usually
goes wrong.
--
Regards,
Kristofer Gafvert
www.gafvert.info - My Articles and help
www.ilopia.com
fibreiv wrote:
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files
from
> the database. I can download txt, bmp files just fine but when it comes
to
> pdf documents it just hangs at the downloading the file screen and then
gives
> me a timeout error after awhile. It is not a problem with the file
sizes. I
> am testing it with a 20 and 60k pdf files and one of the txt files I
used to
> test with is a few mb. I am thinking it's either an IIS server config
issue
> or SQL Server config problem. The IIS server is for our intranet and is
not
> locked down. Both IIS and SQL are running on the same box which is a
Windows
> 2003 Enterprise server. Any help would be great.
> Thanks,
> Brett Bishop
> MCSE, MCP+I
> US Bankruptcy Court|||I have the Content type set to application/pdf and I can't open it or save
it. Just to clarify the data is stored in SQL Image field.
"Aaron Weiker" wrote:
> fibreiv wrote:
> > I have a SQL 2000 Standard server setup and I have a table in one of my
> > databases to store files in. I am using asp code to retrieve the files from
> > the database. I can download txt, bmp files just fine but when it comes to
> > pdf documents it just hangs at the downloading the file screen and then gives
> > me a timeout error after awhile. It is not a problem with the file sizes. I
> > am testing it with a 20 and 60k pdf files and one of the txt files I used to
> > test with is a few mb. I am thinking it's either an IIS server config issue
> > or SQL Server config problem. The IIS server is for our intranet and is not
> > locked down. Both IIS and SQL are running on the same box which is a Windows
> > 2003 Enterprise server. Any help would be great.
> Perhaps it has to do with the content type when the file is being sent
> down. You may also want to set the content-disposition to indicate to
> download the file, "Content-Disposition: Attachment" in the response header.
> You aren't saying if you are trying to open up the file directly or save
> it to disk first.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>|||The file is stored in the Database in an Image field so there is no file
system access to the file.
"Tom Kaminski [MVP]" wrote:
> "fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
> news:0880684A-A141-4D78-842A-041C501DBCF5@.microsoft.com...
> > I have a SQL 2000 Standard server setup and I have a table in one of my
> > databases to store files in. I am using asp code to retrieve the files
> from
> > the database. I can download txt, bmp files just fine but when it comes
> to
> > pdf documents it just hangs at the downloading the file screen and then
> gives
> > me a timeout error after awhile. It is not a problem with the file sizes.
> I
> > am testing it with a 20 and 60k pdf files and one of the txt files I used
> to
> > test with is a few mb. I am thinking it's either an IIS server config
> issue
> > or SQL Server config problem. The IIS server is for our intranet and is
> not
> > locked down. Both IIS and SQL are running on the same box which is a
> Windows
> > 2003 Enterprise server. Any help would be great.
> Can you access PDF files via URL from the file system?
>
>|||"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:9BEC1745-770E-47BF-8749-06E8EBB48601@.microsoft.com...
> The file is stored in the Database in an Image field so there is no file
> system access to the file.
Understood.
As part of the troubleshooting routine, I'm asking if you can access one
from the file system.|||I copied the same file thats in the database to the folder I have all the asp
files in on my server and put the url right to the pdf document and it opened
up acrobat and opened the file.
"Tom Kaminski [MVP]" wrote:
> "fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
> news:9BEC1745-770E-47BF-8749-06E8EBB48601@.microsoft.com...
> > The file is stored in the Database in an Image field so there is no file
> > system access to the file.
> Understood.
> As part of the troubleshooting routine, I'm asking if you can access one
> from the file system.
>
>|||fibreiv wrote:
> I have the Content type set to application/pdf and I can't open it or save
> it. Just to clarify the data is stored in SQL Image field.
> "Aaron Weiker" wrote:
>>Perhaps it has to do with the content type when the file is being sent
>>down. You may also want to set the content-disposition to indicate to
>>download the file, "Content-Disposition: Attachment" in the response header.
Are you setting "Content-Disposition" at all in the header?
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:AED1E8B2-C2EE-4E02-9A38-BA527F0C6B2B@.microsoft.com...
> I copied the same file thats in the database to the folder I have all the
asp
> files in on my server and put the url right to the pdf document and it
opened
> up acrobat and opened the file.
OK - at this point, we'll need to see code to get an idea of how you're
doing it. In that case, I would suggest adding the ASP newsgroup as well
...|||Here is what I am doing to test it. I have hardcoded the file I want to get
out of the database.
Connect = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=INNB;Data Source=pompom"
Set db = Server.CreateObject("ADODB.Connection")
db.Open Connect
SQL = "SELECT filesize, contenttype, filename, filedata FROM thefiles where
id = 2"
Set rs = db.Execute( SQL )
If rs.EOF Then Response.End
Response.ContentType = "application/pdf"
Response.AddHeader "Content-Disposition", "attachment;filename=agencies.pdf"
Response.AddHeader "Content-Length", "16863"
Response.BinaryWrite rs("filedata")
"Aaron Weiker" wrote:
> fibreiv wrote:
> > I have the Content type set to application/pdf and I can't open it or save
> > it. Just to clarify the data is stored in SQL Image field.
> >
> > "Aaron Weiker" wrote:
> >>Perhaps it has to do with the content type when the file is being sent
> >>down. You may also want to set the content-disposition to indicate to
> >>download the file, "Content-Disposition: Attachment" in the response header.
> Are you setting "Content-Disposition" at all in the header?
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>|||fibreiv wrote:
> Connect = "Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=INNB;Data Source=pompom"
> Set db = Server.CreateObject("ADODB.Connection")
> db.Open Connect
> SQL = "SELECT filesize, contenttype, filename, filedata FROM thefiles where
> id = 2"
> Set rs = db.Execute( SQL )
> If rs.EOF Then Response.End
> Response.ContentType = "application/pdf"
> Response.AddHeader "Content-Disposition", "attachment;filename=agencies.pdf"
> Response.AddHeader "Content-Length", "16863"
> Response.BinaryWrite rs("filedata")
Have you tried putting Response.End at the end? Someone else may be able
to help identify a missing piece as it's been a long time since I did this.
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/

Sunday, March 25, 2012

downloading data from a table

I need to download all data in a table into a text file or an excel spreadsheet. What would be the easiest way to do this?
Hi,
Please see the reply to the same post in microsoft.public.sqlserver.server
group
Thanks
Hari
"Joel" <anonymous@.discussions.microsoft.com> wrote in message
news:4E7B6116-7F34-47B0-B8C7-055E2DCF1560@.microsoft.com...
> I need to download all data in a table into a text file or an excel
spreadsheet. What would be the easiest way to do this?
>

Download the SQL servere date to excel file using ASP.net

Hi EveryOne

I would like to download the SQL server databse to excel file using ASP.Net

I need to download the one table to excel file. If anyone knows please help me.

With Regards

Nibu Abraham

i found this link

http://www.theserverside.net/discussions/thread.tss?thread_id=29385

give a try

|||

Hi Nibu

Create a stored procedure to display the required columns from the give table. Grant execute permission to this stored procedure to the role that your ASP.NET account is assigned to. In your datalayer, run the stored procedure to output a dataset. Add a gridview control (or datagrid if still at ASP.NET 1.1) to an ASPX form.

Set the datasource for the control to the dataset produced by the read function in your data layer, call databind and the data will be displayed.

There is a simple modifier to make the displayed page OLE load Excel - I will find out the how of this at the weekend.

HTH

|||

You need to add the following to your Page_Load

Response.ContentType = "application/vnd.ms-excel";

Response.Charset = "";

You will need to turn off the sort option on the grid and any other hyperlinks on the form.

HTH

sql

Download the file stored in sqlserver table as image datatype using asp.net 2.0

I am using Asp.net 2.0 with C# and sql server 2000. I need to download the file which is stored in sql server database table as image datatype. So I need to download from cs page.

Pls reply,

Arun.

Here's a good article on storing and retrieving binary data with asp.net 2.0
http://www.beansoftware.com/ASP.NET-Tutorials/Binary-Files-To-Database.aspx

|||

Hi,

Consider, you have query like

select files,filename,filesize,fileext from filesintable where id=1

that file on dar[0] as image datatype.

while (dar.Read())
{
bFile = (byte[])dar[0];
Response.AddHeader("Content-Disposition", "attachment; filename=" +dar[1].ToString());

switch (dar[3].ToString())
{
case "ask":
Response.ContentType = "video/x-ms-asf";
break;
case "avi":
Response.ContentType = "video/avi";
break;
case "doc":
Response.ContentType = "application/msword";
break;
case "zip":
Response.ContentType = "application/zip";
break;
case "xls":
Response.ContentType = "application/vnd.ms-excel";
break;
case "ppt":
Response.ContentType = "application/vnd.ms-powerpoint";
break;
case "gif":
Response.ContentType = "image/gif";
break;
case "jpg":
case "jpeg":
Response.ContentType = "image/jpeg";
break;
case "wav":
Response.ContentType = "audio/wav";
break;
case "mp3":
Response.ContentType = "audio/mpeg3";
break;
case "mpg":
case "mpeg":
Response.ContentType = "video/mpeg";
break;
case "rtf":
Response.ContentType = "application/rtf";
break;
case "htm":
case "html":
Response.ContentType = "text/html";
break;
case "asp":
Response.ContentType = "text/asp";
break;
case "pdf":
Response.ContentType = "application/pdf";
break;
default:
Response.ContentType = "application/octet-stream";
break;
}
Response.BinaryWrite(bFile);

}

It works fine.

Thursday, March 22, 2012

download data into text file

I need to download all data in a table into a text file or an excel spreadsheet. What would be the easiest way to do this?
DTS would be the easiest way.
In Enterprise Manager, the shortcut is to right click on the table and choose export data.
|||Hi,
There are 2 options:-
1. DTS (A Graphical interface , you can just by clicking down load the
contents of a table to Text file.
2. BCP OUT (This is command line utility ) ... For more help execute BCP/?
from command prompt.
a. Using query to BCP OUT specific columns
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
b. BCP OUT entire table
BCP master..sysobjects OUT c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Ken Dutton" <anonymous@.discussions.microsoft.com> wrote in message
news:00E0DDBA-97BD-4DB0-B582-8D6863E7C6F2@.microsoft.com...
> DTS would be the easiest way.
> In Enterprise Manager, the shortcut is to right click on the table and
choose export data.
|||Hi,
I understand that you want to export the Data in a table to an excel work
sheet or a Text file.
You can achieve this by Import Export Wizard.
In the Enterprise Manager, select the Table from which you want to export
the data .
Right click on that table.
Select All Tasks
Select Export Data
Click on Next - On the Welcome Dialog of DTS Import/Export Wizard
You will get a Choose a Source Dialog. It would by default show the current
database. If that's the database from where you want to export the data,
click on Next
You will get a Choose a Destination Dialog
1. To save the data to Excel (2000 or above) worksheet
a. Select Excel 97 -2 000 in the Destination (from the pull down menu)
b. In the File name option, enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Source Tables and Views Dialog
Select the table from which you want to export the data and then click on
Next.
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
2. To save the data to Text File
a. Select Text File in the destination (from the pull down menu)
b. in the File Name option ,enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Destination File Format dialog
Select the appropriate format (or leave it to default).
You may want to select the option (First Row has Column Names), to include
the column names in the output
Then Click on NExt
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||How about going into EXCEL and DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL SERVER CONNECTION.
If the goal is to get SQL data into EXCEL, why use BCP or EM?
|||Yes, that worked very well
Thanks
Joel

download data into text file

I need to download all data in a table into a text file or an excel spreadsh
eet. What would be the easiest way to do this?DTS would be the easiest way.
In Enterprise Manager, the shortcut is to right click on the table and choos
e export data.|||Hi,
There are 2 options:-
1. DTS (A Graphical interface , you can just by clicking down load the
contents of a table to Text file.
2. BCP OUT (This is command line utility ) ... For more help execute BCP/?
from command prompt.
a. Using query to BCP OUT specific columns
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
b. BCP OUT entire table
BCP master..sysobjects OUT c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Ken Dutton" <anonymous@.discussions.microsoft.com> wrote in message
news:00E0DDBA-97BD-4DB0-B582-8D6863E7C6F2@.microsoft.com...
> DTS would be the easiest way.
> In Enterprise Manager, the shortcut is to right click on the table and
choose export data.|||Hi,
I understand that you want to export the Data in a table to an excel work
sheet or a Text file.
You can achieve this by Import Export Wizard.
In the Enterprise Manager, select the Table from which you want to export
the data .
Right click on that table.
Select All Tasks
Select Export Data
Click on Next - On the Welcome Dialog of DTS Import/Export Wizard
You will get a Choose a Source Dialog. It would by default show the current
database. If that's the database from where you want to export the data,
click on Next
You will get a Choose a Destination Dialog
1. To save the data to Excel (2000 or above) worksheet
a. Select Excel 97 -2 000 in the Destination (from the pull down menu)
b. In the File name option, enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Source Tables and Views Dialog
Select the table from which you want to export the data and then click on
Next.
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
2. To save the data to Text File
a. Select Text File in the destination (from the pull down menu)
b. in the File Name option ,enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Destination File Format dialog
Select the appropriate format (or leave it to default).
You may want to select the option (First Row has Column Names), to include
the column names in the output
Then Click on NExt
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||How about going into EXCEL and DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL
SERVER CONNECTION.
If the goal is to get SQL data into EXCEL, why use BCP or EM?|||Yes, that worked very well
Thanks
Joel

download data into text file

I need to download all data in a table into a text file or an excel spreadsheet. What would be the easiest way to do this?DTS would be the easiest way
In Enterprise Manager, the shortcut is to right click on the table and choose export data.|||Hi,
There are 2 options:-
1. DTS (A Graphical interface , you can just by clicking down load the
contents of a table to Text file.
2. BCP OUT (This is command line utility ) ... For more help execute BCP/?
from command prompt.
a. Using query to BCP OUT specific columns
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
b. BCP OUT entire table
BCP master..sysobjects OUT c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Ken Dutton" <anonymous@.discussions.microsoft.com> wrote in message
news:00E0DDBA-97BD-4DB0-B582-8D6863E7C6F2@.microsoft.com...
> DTS would be the easiest way.
> In Enterprise Manager, the shortcut is to right click on the table and
choose export data.|||Hi,
I understand that you want to export the Data in a table to an excel work
sheet or a Text file.
You can achieve this by Import Export Wizard.
In the Enterprise Manager, select the Table from which you want to export
the data .
Right click on that table.
Select All Tasks
Select Export Data
Click on Next - On the Welcome Dialog of DTS Import/Export Wizard
You will get a Choose a Source Dialog. It would by default show the current
database. If that's the database from where you want to export the data,
click on Next
You will get a Choose a Destination Dialog
1. To save the data to Excel (2000 or above) worksheet
a. Select Excel 97 -2 000 in the Destination (from the pull down menu)
b. In the File name option, enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Source Tables and Views Dialog
Select the table from which you want to export the data and then click on
Next.
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
2. To save the data to Text File
a. Select Text File in the destination (from the pull down menu)
b. in the File Name option ,enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Destination File Format dialog
Select the appropriate format (or leave it to default).
You may want to select the option (First Row has Column Names), to include
the column names in the output
Then Click on NExt
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Download Data from one SQL server to a local server

Hi I am trying to download data from one sql server to a local table. What are my options?

Thanks.What do you mean by local table? A table on your local Sql Server? If so the easiest thing to do is right click on the source table in Enterprise Manager and select "All Tasks->Export Data" and follow the wizard to copy the table to your local server.sql

Monday, March 19, 2012

Doubt regarding PFS

Does SQL Server use PFS to check whether a page is allocated or not if the
table has a clustered index.
Regards
BalajiPFS is used for determining if there is enough free space on the page for
the row but only on Heaps.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:C5E97806-A784-4B47-9746-EA01C5A3F84A@.microsoft.com...
> Does SQL Server use PFS to check whether a page is allocated or not if the
> table has a clustered index.
> Regards
> Balaji

Doubt on unique non clustered Index

Hi All,
I have a table with data:
CREATE TABLE [dbo].[Relation] (
[ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
[PersonID] [uniqueid] NOT NULL ,
[CurrentID] [uniqueid] NOT NULL ,
[RelativeName] [varchar] (50) NOT NULL ,
)
Here there are duplicate records for a combination of PersonID, CurrentID
and RelativeName.
I need to create a unique non clustered index on PersonID, CurrentID and
RelativeName.
But it throws an error saying duplicate values...
Why I want to create such an index is to stop inserting further duplicates
and the old data is important for me.
Is there any other way i can accomplish this?
Thanks,
PradYou ought to be able to get rid of the duplicates, otherwise your table
lacks integrity because of the redundant data. Why do you say that the old
data is important to you? In what way?
First, you'll have to fix any foreign key references:
UPDATE foo
SET id =
(SELECT MIN(R2.id)
FROM Relation AS R1
, Relation AS R2
WHERE R1.id = foo.id
AND R1.personid = R2.personid
AND R1.currentid = R2.currentid
AND R1.relativename = R2.relativename) ;
Then delete the duplicates:
DELETE FROM Relation
WHERE EXISTS
(SELECT *
FROM Relation AS R
WHERE R.personid = Relation.personid
AND R.currentid = Relation.currentid
AND R.relativename = Relation.relativename
AND R.id < Relation.id) ;
Now you can add the unique constraint.
Hope this helps.
David Portas
SQL Server MVP
--|||Hi
Maybe you should be changing/consolidating the data related that makes this
non-unique. This does not necessarily mean deleting it, for example having a
alternateids table would mean that only one id needs to exist in the "master
"
table.
John
"Pradeep Kutty" wrote:

> Hi All,
> I have a table with data:
> CREATE TABLE [dbo].[Relation] (
> [ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
> [PersonID] [uniqueid] NOT NULL ,
> [CurrentID] [uniqueid] NOT NULL ,
> [RelativeName] [varchar] (50) NOT NULL ,
> )
> Here there are duplicate records for a combination of PersonID, CurrentID
> and RelativeName.
> I need to create a unique non clustered index on PersonID, CurrentID and
> RelativeName.
> But it throws an error saying duplicate values...
> Why I want to create such an index is to stop inserting further duplicates
> and the old data is important for me.
> Is there any other way i can accomplish this?
> Thanks,
> Prad
>
>|||I prefer this one:
Delete from Relation
Where
[id] not in
(
SELECT MAX([id])
FROM Relation
GROUP BY [personid],[currentid],[relativename])
Regards,
"David Portas" wrote:

> You ought to be able to get rid of the duplicates, otherwise your table
> lacks integrity because of the redundant data. Why do you say that the old
> data is important to you? In what way?
> First, you'll have to fix any foreign key references:
> UPDATE foo
> SET id =
> (SELECT MIN(R2.id)
> FROM Relation AS R1
> , Relation AS R2
> WHERE R1.id = foo.id
> AND R1.personid = R2.personid
> AND R1.currentid = R2.currentid
> AND R1.relativename = R2.relativename) ;
> Then delete the duplicates:
> DELETE FROM Relation
> WHERE EXISTS
> (SELECT *
> FROM Relation AS R
> WHERE R.personid = Relation.personid
> AND R.currentid = Relation.currentid
> AND R.relativename = Relation.relativename
> AND R.id < Relation.id) ;
> Now you can add the unique constraint.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>

Doubt in select query

my table ha follow structure..
SalaryTable:
Value Salary
--
Jan 1000
Feb 2000
Mar 3000
Apr 4000
i want the o/p as..
Jan Feb Mar Apr
1000 2000 3000 4000
Pls give me a query for this
Regards,
SatheeshHi,
It is not straight forward in SQL 2000, you need to write logic. See the
below URL:-
http://www.sqlteam.com/item.asp?ItemID=2955
Thanks
hari
SQL Server MVP
"BabuLives" <satheeshbabu.b@.gmail.com> wrote in message
news:1130138243.780789.195160@.g43g2000cwa.googlegroups.com...
> my table ha follow structure..
>
> SalaryTable:
>
> Value Salary
> --
> Jan 1000
> Feb 2000
> Mar 3000
> Apr 4000
>
> i want the o/p as..
>
> Jan Feb Mar Apr
> 1000 2000 3000 4000
>
> Pls give me a query for this
>
> Regards,
> Satheesh
>

Doubt in Index

Hi EverB,

I have created a table say T1 with 5 columns (col1.....col5).
I have created an index on col4 and col5.

iam executing a select query say.

select col1,col2,col3 from t1 where col1 =0;

In the above query i hav not used the columns for which i hav created the index.My ques is-

Does the index really works when u execute the above query?

Any answer please.......
It wuld be better if u culd suggest some sites for this.

Thanks and Regds,
Arunno, for that query, the index is ignored

a good tutorial is Optimizing your MySQL Application (http://www.sitepoint.com/print/optimizing-mysql-application) even if you're using something other than mysql

Doubt aboult loop in trigger?

Hi all,
I have one doubt, if anyone can helpe-me.
I have one table that have one trigger (UPDATE), this trigger have one loop
with 12 interactions, for each interaction I have one INSERT in other table.
I did read that is not good put loop in trigger, this is true, if yes how I
should do this.
ThanksHi,
Anything done inside/called from the trigger will be in a transaction so can
cause blocking which can really quickly degrade the performance and
experience the user gets.
What loopnig are you trying to do, please post your trigger code.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I should do this.
> Thanks
>|||Generally, processing one row at a time inside a loop is not efficient,
compared to processing all rows using a single statement. In many cases,
loops and cursors can be avoided by using SET based queries.
Can you show us what is it that you are trying to do, and may be someone can
suggest an alternative for you.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
Hi all,
I have one doubt, if anyone can helpe-me.
I have one table that have one trigger (UPDATE), this trigger have one loop
with 12 interactions, for each interaction I have one INSERT in other table.
I did read that is not good put loop in trigger, this is true, if yes how I
should do this.
Thanks|||Hi,
I have one table that receive, request(purchase) of clients, these request
can be divided (for sample one purchase of 300,00 in 3x100,00), when this
occur I need schedule the payments (1th in cash , 2th in 30 days, 3th in 60
days), then I use LOOP to INSERT these in TABLE schedule.
I can have 1 to 12 INSERTS in this loop.
You have any other idea to do this, how? Thank you very much.
Thanks for help.
If you don't understand let-me know, because English is not my native
language, I can try explain in other way.
Retf
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escreveu na mensagem
news:ePof$nh3FHA.3900@.TK2MSFTNGP12.phx.gbl...
> Generally, processing one row at a time inside a loop is not efficient,
> compared to processing all rows using a single statement. In many cases,
> loops and cursors can be avoided by using SET based queries.
> Can you show us what is it that you are trying to do, and may be someone
> can
> suggest an alternative for you.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop
> with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I
> should do this.
> Thanks
>
>|||Here's a small example, that uses a numbers table, to replace the loop with
a singel INSERT statement inside your trigger.
CREATE TABLE Trn (TranID int, TotalSum money, TotalInstallments tinyint)
CREATE TABLE Pmts (TranID int, InstallmentNo tinyint, InstallmentAmt money)
CREATE TABLE Numbers (Number tinyint)
GO
SET NOCOUNT ON
GO
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 12
BEGIN
INSERT INTO Numbers (Number)
SELECT @.i
SET @.i = @.i + 1
END
GO
CREATE TRIGGER TrnInsTrg
ON Trn
FOR INSERT
AS
BEGIN
INSERT Pmts (TranID, InstallmentNo, InstallmentAmt)
SELECT i.TranID, n.Number, i.TotalSum/i.TotalInstallments
FROM inserted AS i
INNER JOIN
Numbers AS n
ON n.Number <= i.TotalInstallments
END
GO
INSERT Trn (TranID, TotalSum, TotalInstallments) VALUES (1, 30000, 3)
INSERT Trn (TranID, TotalSum, TotalInstallments) VALUES (1, 120000, 12)
GO
SELECT * FROM Trn
SELECT * FROM Pmts
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ucBkH1i3FHA.4076@.TK2MSFTNGP15.phx.gbl...
Hi,
I have one table that receive, request(purchase) of clients, these request
can be divided (for sample one purchase of 300,00 in 3x100,00), when this
occur I need schedule the payments (1th in cash , 2th in 30 days, 3th in 60
days), then I use LOOP to INSERT these in TABLE schedule.
I can have 1 to 12 INSERTS in this loop.
You have any other idea to do this, how? Thank you very much.
Thanks for help.
If you don't understand let-me know, because English is not my native
language, I can try explain in other way.
Retf
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escreveu na mensagem
news:ePof$nh3FHA.3900@.TK2MSFTNGP12.phx.gbl...
> Generally, processing one row at a time inside a loop is not efficient,
> compared to processing all rows using a single statement. In many cases,
> loops and cursors can be avoided by using SET based queries.
> Can you show us what is it that you are trying to do, and may be someone
> can
> suggest an alternative for you.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop
> with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I
> should do this.
> Thanks
>
>

Thursday, March 29, 2012

Drawing line between table rows and columns

Drawing line at last record

Draw Horizontal Line at Threshold

Dragging multiple fields to a table?

Tuesday, March 27, 2012

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Sunday, March 25, 2012

downloading data from a table

Download the SQL servere date to excel file using ASP.net

Download the file stored in sqlserver table as image datatype using asp.net 2.0

Thursday, March 22, 2012

download data into text file

download data into text file

download data into text file

Download Data from one SQL server to a local server

Monday, March 19, 2012

Doubt regarding PFS

Doubt on unique non clustered Index

Doubt in select query

Doubt in Index

Doubt aboult loop in trigger?

Drill down slow

Blog Archive

About Me