Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob Boles
This isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:
> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in
a
> long text field (defined as ntext (16)). When I run the query and export t
o
> Excel the text is truncated at 255 characters. How do I get all the text t
o
> export?
> Thanks,
> Bob Boles

Exporting Jobs in SQL Server Agent

Hello,
We have about 20 Jobs in the SQL Server Agent, under the Management section
of the Enterprise Manager tree that run on different schedules daily. We're
moving to a new server and I can't seem to find a way or a tool that will
let me copy the jobs to the new server. Does anyone know if there is a way
or a tool for such a task? We can redo them manually but thought I would ask
first before we started. We're on SQL2K. Thanks for any information you may
be able to give me.
RGPyou can right click on the job and choose Generate SQL Script...
btw, jobs are saved in msdb. :-)
"riley" <riley3@.microtek-sales.com> wrote in message
news:%23lSae4BxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We have about 20 Jobs in the SQL Server Agent, under the Management
section
> of the Enterprise Manager tree that run on different schedules daily.
We're
> moving to a new server and I can't seem to find a way or a tool that will
> let me copy the jobs to the new server. Does anyone know if there is a way
> or a tool for such a task? We can redo them manually but thought I would
ask
> first before we started. We're on SQL2K. Thanks for any information you
may
> be able to give me.
> RGP
>|||Right click the Jobs under Management | SQL Server Agent
for that SQL Server instance, select All Tasks -> Generate
SQL Scripts ..., and save the SQL script for all the local
jobs.
You can then run this generated script on a differen
server to re-create the jobs. You may need to edit the
script somewhat if the script contains references that are
not valid on this other server.
Linchi
>--Original Message--
>Hello,
>We have about 20 Jobs in the SQL Server Agent, under the
Management section
>of the Enterprise Manager tree that run on different
schedules daily. We're
>moving to a new server and I can't seem to find a way or
a tool that will
>let me copy the jobs to the new server. Does anyone know
if there is a way
>or a tool for such a task? We can redo them manually but
thought I would ask
>first before we started. We're on SQL2K. Thanks for any
information you may
>be able to give me.
>RGP
>
>.
>|||Thank you very much -I didn't realize the Generate SQL Scripts was for that
purpose. Appreciate the help.
RGP
"NEW NAME, OLD GUY" <PLEASE!> wrote in message
news:O4vnJFCxDHA.3436@.tk2msftngp13.phx.gbl...
> you can right click on the job and choose Generate SQL Script...
> btw, jobs are saved in msdb. :-)
> "riley" <riley3@.microtek-sales.com> wrote in message
> news:%23lSae4BxDHA.1060@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> > We have about 20 Jobs in the SQL Server Agent, under the Management
> section
> > of the Enterprise Manager tree that run on different schedules daily.
> We're
> > moving to a new server and I can't seem to find a way or a tool that
will
> > let me copy the jobs to the new server. Does anyone know if there is a
way
> > or a tool for such a task? We can redo them manually but thought I would
> ask
> > first before we started. We're on SQL2K. Thanks for any information you
> may
> > be able to give me.
> > RGP
> >
> >
>

Exporting DTS jobs?

SQL Server 2000.

Hey there,

I'm trying to set up a test environment. My projuction environemtn has a bunch of Scheduled Jobs which run either stored procedures or DTS jobs.

I've exported the SP's and the Scheduled jobs but I'm wondering if there is any way to migrate the DTS jobs?

Cheers...open DTS, select "save as" from "Package" menu. Select "structured storage file" in Location drop down. provide name etc. save.

in the destination machine, right-click on Data-Transformation-Services of EM select "open package". save it with Location = SQL Server.|||Me being the lazy bum that I am, I'd use DTSBackup (http://www.sqldts.com/default.aspx?242). I've got develoeprs that really like DTS though, so I get an infernal number of packages on some of my servers.

-PatP|||Me being the lazy bum that I am, ...
-PatP

I think I'm maybe lazier. I created a script that saves my DTS packages every night to a remote (off-server) location:

' ************************************************** ********************
' * SaveDTS.vbs
' * Hugh Scott
' * 2005/06/28
' *
' * This script logs into a SQL Server, locates all DTS packages and Then
' * saves each pacakge as a file to a specified location
' *
' * Parameters:
' * IN: None
' * OUT:
' * None
' *
' * Usage:
' *
' *
' * Modification History:
' * Date Initials Modification
' * 2005/06/28 HMS Original
' *
' ************************************************** ********************
Dim oDTS
Dim oConn
Dim oRS
Dim sSQL
Dim iPackages
Dim sServer
Dim sUser
Dim sPassword
Dim bTrusted
Dim sTarget
Const DTSSQLStgFlag_UseTrustedConnection = 256

bTrusted = True
sServer = <set server name here>
sTarget = <set backup destination here>

Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")

If bTrusted Then
oConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" & sServer
Else
oConn.ConnectionString = "Provider=SQLOLEDB.1;UID=" & sUser & ";Password=" & sPassword & ";Persist Security Info=False;Initial Catalog=msdb;Data Source=" & sServer
End if
oConn.Open

sSQL = "SELECT DISTINCT name FROM sysdtspackages"

Set oRS = oConn.Execute(sSQL, iPackages)

While Not oRS.EOF

Set oDTS = CreateObject("DTS.Package")
sName = oRS("name")
oDTS.LoadFromSQLServer sServer,,,DTSSQLStgFlag_UseTrustedConnection,,,,sN ame
sFileName = Replace(sName, ":", "")
oDTS.SaveToStorageFile sTarget & sFileName & ".dts"
Set oDTS = Nothing
oRS.MoveNext
Wend

Set oRS = Nothing
oConn.Close
Set oConn = Nothing

Schedule it as a job (set the task type to VB Script). It ensures that you always have a backup of your DTS packages when it comes time to restore the db server.

Regards,

hmscott|||Nice idea. I may implement this.|||Awesome help guys thankyou all so much! :D|||open DTS, select "save as" from "Package" menu. Select "structured storage file" in Location drop down. provide name etc. save.

in the destination machine, right-click on Data-Transformation-Services of EM select "open package". save it with Location = SQL Server.

I tried HMSCOTT's code, and it yielded the same results as everything else

it is unable to backup my packages correctly, they get restored with too many incorrect settings

the only option I have gotten to work is doing a manual "save as" which is explained above|||... they get restored with too many incorrect settings ...

Can you explain/elaborate? What settings are incorrect?

Be aware that DTS is very environment dependent. You would have to make certain that:

1. Any drivers that a DTS package would use would be the same on the new server as the old server.

2. Any custom DTS Tasks would have to be the same on the new server as the old server.

3. External paths, links and certain files must be identical (for example, an Oracle TNSNames.ora file, or a UNC path)

4. In some cases security settings will have an impact.

Regards,

hmscott|||here is a description of the problem:

if i open this particular dts package and save it to file it is 511B, but if i use any backup program or script i.e. your script, DTS backup, a script i wrote, etc... it is 472B.

when i open both files in enterprise manager, the 511B file yields the correct package (obviously for migration i would have to change settings but no big deal as long as its the same structure)

the 472B package is compeltley different, I don't even know how it thinks the translation even comes close. it messes up some success/failure criterias and creates additional connection ??|||hmm, okay, I can't address most of your specific issues. I can only comment on my own experiences and environment.

I just opened up all of the package "structured" files that I have. I opened them from a different server than the one on which they normally reside.

I found the following:
1. In all but one case, the packages restored just fine with no errors.
2. The comments that I had created using the textbox feature were all missing (ugh). Also, in some cases, the objects were shifted on the designer window (moving from the left side of the screen to the right and vice versa).
3. In one case, only two VBScript tasks showed in the designer window (out of maybe 40 tasks). I checked the disconnected edit properties, though, and found everything to be present.
4. When I executed the package (the one with the clear designer window), everything executed in the order in which I would expect it.

I am running SQL Server 2000 with SP4 (patched with hotfix 2040).

I will do some more research at work tomorrow. I'm glad you pointed this out; I excercised the recovery process once last year, but I haven't looked at this in a while.

Regards,

hmscott|||one thing to check would be to look at the size of your structured files for your backup program versus a file that you saved manually

maybe do this for the most complicated packages to see if there is an issue

it might just be that my packages are too complicated for a backup program to account for|||See, 1 night, long, long ago, all of the SQL Server developers fell asleep, and the Access developers snuck in...viola...DTS

Here's a pleasant thought...there is no DTS in 2k5|||hahaha... that is hilarious

what does 05 use to automate external data flows?|||http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx

Look for Intergration Services...they don't even mention DTS, nor do they mention how (or if) it's possible to migrate existing packages.|||See, 1 night, long, long ago, all of the SQL Server developers fell asleep, and the Access developers snuck in...viola...DTS

Here's a pleasant thought...there is no DTS in 2k5

Yep, with 2k5, they shot all of the Access developers and brought in the BizTalk developers. Arrrrgh!!!

On the serious side, I figured what I had done wrong in the earlier example (the one package that would not restore properly): I had a custom task in the package and had not registered the custom task on the server to which I restored the package. Once, I took care of that little task, then the package opened up just fine. My file sizes are also different between the programmatic save versus the manual save. I think that is because the programmatic method is saving version information in addition to the structure. When I go to open the package, I see a list of possible entries to open, starting with the most recently saved version.

Again, I can't comment or help on your specific scenario; I'm glad for the opportunity to have checked out the script and verified the restore process. It seems to work fine for me.

:shrug:

Regards,

hmscott

Exporting Data+Structure

Hello E'body,
I have to export a SQL Server Table (Structure+Data) to an access mdb file at run time. How can i do it? please help.
Lax.You can use DTS wizard in SQL Server to accomplish the task.
For information review topics available in SQL books online.

ONce you've designed the package, can schedule it to run as a job at run time.

Exporting Data from excel to SQL Server express

Hi

I have managed to add the DTS wizard as a tool and saved the routine. However, I do not know how to schedule my PC to automatically run this DTS procedure. I have tried to use scheduled tasks, but it does not seem to want to start.

Pls help.

It was absolutely amazing.

Just by typing in "SQL Server" and Excel in the search box on this page in the forums, I found these previous posting on the same topic...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1083746&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295895&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=970723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304674&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=985930&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1083317&SiteID=1

There were more...

|||

Hi Arnie,

Thanks for the suggestion, I discovered a a previous post you made. You suggested that you get scheduled tasks to run dtsrun.exe. I do not have DTSrun.exe and surely you would have to make the executable run only your specified saved DTS 'routines'. How would you do this?

Regards

George

|||

Perhaps these will help guide you.

DTS - How to Run a DTS Package as a Scheduled Job
http://www.support.microsoft.com/?id=269074

DTSWizard.exe
http://go.microsoft.com/fwlink/?LinkId=65111

|||

Hi

Thank you for your help, but unfortunately the above solutions do not seem to work. Let me explain my situation more completely:

I have installed Microsoft SQL Server Express Edition with Advanced Services SP2 and the Toolkit. I have made the DTSWizard a tool in SQL management studio express I have transferred sample data into the database using the DTS tool. I have 'saved' the DTS transfer|||

For command line details about executing a 'package', refer to Books Online, Topic: dtutil

Exporting Data

I have a requirement to run .rdl reports and also export the data for the
report into EXCEL. My quest is how can I export the data, is there an
dataelement in the RDL definition language that I can use.
ThankYouThe user can run the report and then use the export function. OR
you may call the report via HTML passing the parameters to render to Excel..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"kulwant" <kulwant@.discussions.microsoft.com> wrote in message
news:52A6BA63-B984-4131-B86E-12EF4D56AC24@.microsoft.com...
>I have a requirement to run .rdl reports and also export the data for the
> report into EXCEL. My quest is how can I export the data, is there an
> dataelement in the RDL definition language that I can use.
> ThankYou|||I am sorry but I have not made myself clear, what The users want is the
actual underline data that is used to generate the report.
"Wayne Snyder" wrote:
> The user can run the report and then use the export function. OR
> you may call the report via HTML passing the parameters to render to Excel..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "kulwant" <kulwant@.discussions.microsoft.com> wrote in message
> news:52A6BA63-B984-4131-B86E-12EF4D56AC24@.microsoft.com...
> >I have a requirement to run .rdl reports and also export the data for the
> > report into EXCEL. My quest is how can I export the data, is there an
> > dataelement in the RDL definition language that I can use.
> >
> > ThankYou
>
>

exporting crystal report to text format

Hi there! Can anyone help me with this: what dll's do i need in order to export a crystal report to text format during runtime.

When i run my program in visual basic 6, the exporting is done successfully. but when im using it in .exe, errors are generated.

i already used U2FTXT.DLL

what could be the problem?

pls help me... thanks a lot!Look at this thread, you might need one more dll:

http://www.dev-archive.com/forum/showthread.php?t=287692&highlight=export+text

JeffB

Export to PDF problem.

I have a report that is to be exported to PDF when it is run. However, this report can have about 180 pages if certain combinations of parameters are supplied (Yes I know, but the spec from the client wants it to show so much data). Now, when the report is this big and they try to export it to PDF, ReportServer will either just hang or crash with an error without even creating the PDF. The report consists of about 4 subreports, one of which calls subreports of its own, which then calls its own subreports (it goes on with this for about 5 levels worth of data). I think this could be a limitation of SSRS, but does anyone maybe know of a way around it? The other developers seem to think it would be a solution to re-design the report from scratch without all the sub-reports, but this would take at least a week and the client needs it back ASAP.

Thanks in advance.

Hi,

Im also having a problem in RS2005 exporting to pdf. im exporting my chart to pdf format but the file size is too big.. about 10mb.

Im using windows 2003 server 64 bit.

regards

Anton

Export to PDF problem.

I have a report that is to be exported to PDF when it is run. However, this report can have about 180 pages if certain combinations of parameters are supplied (Yes I know, but the spec from the client wants it to show so much data). Now, when the report is this big and they try to export it to PDF, ReportServer will either just hang or crash with an error without even creating the PDF. The report consists of about 4 subreports, one of which calls subreports of its own, which then calls its own subreports (it goes on with this for about 5 levels worth of data). I think this could be a limitation of SSRS, but does anyone maybe know of a way around it? The other developers seem to think it would be a solution to re-design the report from scratch without all the sub-reports, but this would take at least a week and the client needs it back ASAP.

Thanks in advance.

Hi,

Im also having a problem in RS2005 exporting to pdf. im exporting my chart to pdf format but the file size is too big.. about 10mb.

Im using windows 2003 server 64 bit.

regards

Anton

Export to PDF Different!?

I am having problems with my matrix that is embedded in a list. I used a
workaround to create a horizontal table (matrix + list). Now, when i run my
report, the row sizes of the matrix always shrink, even if i put CanGrow and
CanShrink to false.
So now, i haev set CanGrow to true, and have aligned other fields
(textboxes) to line up with their corresponding rows. It looks "decent" when
i run the report on the report server, but when i click export to pdf, all
the textboxes are shifted up and are no longer lined up. Does this happen to
anyone else, and is this a known issue (differences in rendering between the
report server and exporting) ?
Any help is GREATLY appreciated. Thanks!
--whitOn Apr 25, 3:14 pm, Whitney Yiu <Whitney...@.discussions.microsoft.com>
wrote:
> I am having problems with my matrix that is embedded in a list. I used a
> workaround to create a horizontal table (matrix + list). Now, when i run my
> report, the row sizes of the matrix always shrink, even if i put CanGrow and
> CanShrink to false.
> So now, i haev set CanGrow to true, and have aligned other fields
> (textboxes) to line up with their corresponding rows. It looks "decent" when
> i run the report on the report server, but when i click export to pdf, all
> the textboxes are shifted up and are no longer lined up. Does this happen to
> anyone else, and is this a known issue (differences in rendering between the
> report server and exporting) ?
> Any help is GREATLY appreciated. Thanks!
> --whit
You might want to check the properties for each cell in the matrix
control. Check the Format tab and make sure that: 'Can decrease to
accommodate contents' is not selected. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

export to cvs/excel with comments attached

i'm building a web page to help our support staff run and export their sql queries faster and more easily than going through the sql server manager. i found an article at codeproject [1] that i was able to customize to be more flexible, but i'm not sure how to get it to export the results of a query along with the comments that preceed the query.

for example, i have the following sql file:

-- this query does a certain thing, and the comment can be pretty long at times, depending on how complicated the query isselect *from table where columnid > 100

would anyone be able to tell me how to edit Kolluri's export function to allow for a comment section? alternatively if his way of doing an export is out of date, i'd be interested in a newer way. most importantly, though, i need to be able to export the comments along with query results.

my ultimate goal is to have a drop down list of databases on the server, a list of query files (from a certain consolodated folder) for them to run on the chosen database, a "test query" function, and an export function that exports the comments and results.

[1] http://www.codeproject.com/aspnet/ExportClassLibrary.asp

if i'm going about this all wrong, i don't mind changing directions. :)

what i'm really trying to do is allow our support staff to speed up their query running. currently they have a folder of txt and sql files that they need to run on a certain database (which is different for different support staff). right now they open one of these txt/sql files, open sql studio, copy the query from the txt/sql, paste it into sql studio, run the query, export the results, open the results, and paste the query into the exported results file. rinse and repeat for every txt/sql file in the folder, which is time consuming and ridiculous.

this seems very repetitive and i was hoping to help them out by allowing them to go to a web page, select one or more queries from a list, pick which database they need to connect to, and i'd loop through all those queries and run them with the support staff having to only click one button to start that process. i've got almost every part of this plan working except being able to put the query in comments at the top of the exported file, but if i need to change my entire game plan to allow that, i'm willing. having the query at the top of the exported results is very important, and if i can't get this working, my whole web page is a bust.