Format Alignment: files

Showing posts with label files. Show all posts

Tuesday, March 27, 2012

Filter before export

Hi,
I'm in the process of automating the export of a report to a PDF file.
The number of files is enormous and to prevent the query from executing over
and over again I was wondering whether the following scenario is possible.
- Get all records for a given report
- Filter by a specific value and export, repeat process for all necessary
values of that field.
Thanks for any input
Kind regardsYou could definitely do it programmatically from code that calls the RS Web
service...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> Hi,
> I'm in the process of automating the export of a report to a PDF file.
> The number of files is enormous and to prevent the query from executing
over
> and over again I was wondering whether the following scenario is possible.
> - Get all records for a given report
> - Filter by a specific value and export, repeat process for all necessary
> values of that field.
> Thanks for any input
> Kind regards
>|||Hi,
Thanks for the answer.
Do you have any idea in which Namespace I can find the Filter property?
Thanks
"Wayne Snyder" schreef:
> You could definitely do it programmatically from code that calls the RS Web
> service...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
> news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> > Hi,
> >
> > I'm in the process of automating the export of a report to a PDF file.
> > The number of files is enormous and to prevent the query from executing
> over
> > and over again I was wondering whether the following scenario is possible.
> >
> > - Get all records for a given report
> > - Filter by a specific value and export, repeat process for all necessary
> > values of that field.
> >
> > Thanks for any input
> >
> > Kind regards
> >
> >
>
>|||I would use a parameter, passed in via URL. THen use the parameter to do the
grouping...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> Hi,
> I'm in the process of automating the export of a report to a PDF file.
> The number of files is enormous and to prevent the query from executing
over
> and over again I was wondering whether the following scenario is possible.
> - Get all records for a given report
> - Filter by a specific value and export, repeat process for all necessary
> values of that field.
> Thanks for any input
> Kind regards
>|||Hi,
Wouldn't this trigger the query every time?
I'll try to give some more information.
We have about 1500 offices which all need a bunch of pre generated PDF
reports.
I want a report that returns only a couple rows per office to come back as 1
big Dataset that can be filtered clientside and then exported. So every
'filter' operation extracts the information for a specific office and then
exports it to a PDF. I do not want these queries to execute 1500 times
creating all the connection overhead.
eg.
Report returns 10 records per office
Returns 15000 records in the RS Dataset
The C# program (using Reporting Services WebService) filters out the 10
records and exports these to a PDF for every office
Kind regards,
Wesley
"Wayne Snyder" wrote:
> I would use a parameter, passed in via URL. THen use the parameter to do the
> grouping...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
> news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> > Hi,
> >
> > I'm in the process of automating the export of a report to a PDF file.
> > The number of files is enormous and to prevent the query from executing
> over
> > and over again I was wondering whether the following scenario is possible.
> >
> > - Get all records for a given report
> > - Filter by a specific value and export, repeat process for all necessary
> > values of that field.
> >
> > Thanks for any input
> >
> > Kind regards
> >
> >
>
>

Friday, March 23, 2012

FileSystem Deployment

I am still a little confused on how the file system deployment works. When I deploy my files they end up in {ROOT}/Microsoft SQL Server\90\DTS\Packages\{PKGBUNDLE}, but the connection managers still reference the original dev path. Why does it copy to a seperate directory but still reference the old path, what's the significance? Is using pkg_vars the only way to update conn mgrs to reflect the new path? (outside of using a grep util to search and replace)

I guess I am looking for design patterns, how are others handling the deployment process when deploying on the same server?

P.S. When we port this to the production server, we plan on using the same directories and steps as in staging...What you are probably after is configurations. These are designed such that you store all of the parameters which change between deployments in a configuration, or related store, and just pass the correct configuration into the package each time you use it. The location of the configuration is what may change, but the way you specify this would be chosen such that is common accross all machines.

The package storage location has no bearing on connections and neither should it. Just because my packages move the connection location an still be teh same, teh same SQL server, the same smpt server, and the same file path, if local to a machine or if using a UNC path, they can still all be the same. File system deployment vs any other deployment is just about the storage location really, not the package internals and settings.|||

Yah, I understand your point, I was more so wondering if I could have a seperate custom stored location other then under the default SQl Server\90\DTS....

|||Sorry, I get you. Not sure if it is supported, but playing with MsDtsSrvr.ini.xml may be what you want C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Some related links-

SSIS and SQL Server Instances
(http://www.sqlis.com/default.aspx?57)

Modification of MsDTSSrvr.ini.xml does not work in June CTP - Microsoft Technical Forums - It does work, see post.
(http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=61179)

Try a BOL search for that file as well.

Wednesday, March 21, 2012

files, filegroups

Can someone tell me if we can move files to different filegroups in SQL2K5? If we can, how do you do it?

Here is an excerpt from BOL.

REMOVE FILEGROUP filegroup_name
Removes a filegroup from the database. The filegroup cannot be removed unless it is empty. Remove all files from the filegroup first by either ***moving the files to another filegroup*** or, if the files are empty, by removing the files.

Is that a typo or what?

Thanks.I am hoping Microsoft folks will reply to this. Please let me know if the BOL has an error or you can actually move files among filegroups.
|||

This is a doc error. We can not move files between filegroups.

I've reported the problem to our doc team.

Thanks for reporting it here.

sql

Files sizes in a OLE bmp field

I have a database which stores word documents into tables as BLOB fields.
I need to work out what size each WORD document is.
Do you know how can I work out the individual size of each word document?
__________
'sp_spaceused worddoc_attached' only gives me the overall size of the data
in the table.
__________
Thank you for any help in advance
JAD
You can use the datalength function to do this. You can find more
information in books online under datalength.
-Sue
On Fri, 15 Oct 2004 16:05:27 +0100, "JAD" <listgrove@.yahoo.co.uk>
wrote:

>I have a database which stores word documents into tables as BLOB fields.
>I need to work out what size each WORD document is.
>Do you know how can I work out the individual size of each word document?
>__________
>'sp_spaceused worddoc_attached' only gives me the overall size of the data
>in the table.
>__________
>
>Thank you for any help in advance
>JAD
>

Files PDF into sql table.

I have 5 millions of file PDF (acrobat) to save into a sql table.
Wich type of field can i use into a table ?
Thank you, Checco.
You can use an IMAGE column. Why do you want to do this though?
Wouldn't it be easier just to store the file name? I don't think a PDF
could be very useful in a database.
This issue is discussed here:
http://www.aspfaq.com/show.asp?id=2149
David Portas
SQL Server MVP
|||There is a transform in DTS which will load the files into the database (
Search for Textcopy I think it is).
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Checco" <CheccoTogliLaForka@.Box.it> wrote in message
news:392pnnF5tn0h9U1@.individual.net...
>I have 5 millions of file PDF (acrobat) to save into a sql table.
> Wich type of field can i use into a table ?
> Thank you, Checco.
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ha scritto nel
messaggio news:1110195059.693757.152650@.g14g2000cwa.googlegr oups.com...
> You can use an IMAGE column. Why do you want to do this though?
> Wouldn't it be easier just to store the file name? I don't think a PDF
> could be very useful in a database.
>
I can save the files PDF in a file server... and access it by an URL like:
\\FileServer\Location\FileName but
i think that the access to a sqlserver is more fast than an access to a file
system, so, the files that i need is very much...
or not ?
Checco.
|||No. Keep your files in a filesystem instead, and save the path to them in a
table. Access to a filesystem is much faster. You might want to create
subfolders following some criteria, since keeping 5 million files in one
folder would be a little costly performance wise. You might want to create a
hash function for the subfolder name. That discussion is outside of the
scope of this group.
-Argenis
"Checco" <CheccoTogliLaForka@.Box.it> wrote in message
news:393299F5tg5j0U1@.individual.net...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ha scritto nel
> messaggio news:1110195059.693757.152650@.g14g2000cwa.googlegr oups.com...
> I can save the files PDF in a file server... and access it by an URL
like:
> \\FileServer\Location\FileName but
> i think that the access to a sqlserver is more fast than an access to a
file
> system, so, the files that i need is very much...
> or not ?
> Checco.
>
|||Thank you.
Checco.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> ha scritto nel messaggio
news:eZMNvqyIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> No. Keep your files in a filesystem instead, and save the path to them in
> a
> table. Access to a filesystem is much faster. You might want to create
> subfolders following some criteria, since keeping 5 million files in one
> folder would be a little costly performance wise. You might want to create
> a
> hash function for the subfolder name. That discussion is outside of the
> scope of this group.
> -Argenis
> "Checco" <CheccoTogliLaForka@.Box.it> wrote in message
> news:393299F5tg5j0U1@.individual.net...
> like:
> file
>

Files PDF into sql table.

I have 5 millions of file PDF (acrobat) to save into a sql table.
Wich type of field can i use into a table ?
Thank you, Checco.You can use an IMAGE column. Why do you want to do this though?
Wouldn't it be easier just to store the file name? I don't think a PDF
could be very useful in a database.
This issue is discussed here:
http://www.aspfaq.com/show.asp?id=2149
--
David Portas
SQL Server MVP
--|||There is a transform in DTS which will load the files into the database (
Search for Textcopy I think it is).
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Checco" <CheccoTogliLaForka@.Box.it> wrote in message
news:392pnnF5tn0h9U1@.individual.net...
>I have 5 millions of file PDF (acrobat) to save into a sql table.
> Wich type of field can i use into a table ?
> Thank you, Checco.
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ha scritto nel
messaggio news:1110195059.693757.152650@.g14g2000cwa.googlegroups.com...
> You can use an IMAGE column. Why do you want to do this though?
> Wouldn't it be easier just to store the file name? I don't think a PDF
> could be very useful in a database.
>
I can save the files PDF in a file server... and access it by an URL like:
\\FileServer\Location\FileName but
i think that the access to a sqlserver is more fast than an access to a file
system, so, the files that i need is very much...
or not ?
Checco.|||No. Keep your files in a filesystem instead, and save the path to them in a
table. Access to a filesystem is much faster. You might want to create
subfolders following some criteria, since keeping 5 million files in one
folder would be a little costly performance wise. You might want to create a
hash function for the subfolder name. That discussion is outside of the
scope of this group.
-Argenis
"Checco" <CheccoTogliLaForka@.Box.it> wrote in message
news:393299F5tg5j0U1@.individual.net...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ha scritto nel
> messaggio news:1110195059.693757.152650@.g14g2000cwa.googlegroups.com...
> > You can use an IMAGE column. Why do you want to do this though?
> > Wouldn't it be easier just to store the file name? I don't think a PDF
> > could be very useful in a database.
> >
> I can save the files PDF in a file server... and access it by an URL
like:
> \\FileServer\Location\FileName but
> i think that the access to a sqlserver is more fast than an access to a
file
> system, so, the files that i need is very much...
> or not ?
> Checco.
>|||Thank you.
Checco.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> ha scritto nel messaggio
news:eZMNvqyIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> No. Keep your files in a filesystem instead, and save the path to them in
> a
> table. Access to a filesystem is much faster. You might want to create
> subfolders following some criteria, since keeping 5 million files in one
> folder would be a little costly performance wise. You might want to create
> a
> hash function for the subfolder name. That discussion is outside of the
> scope of this group.
> -Argenis
> "Checco" <CheccoTogliLaForka@.Box.it> wrote in message
> news:393299F5tg5j0U1@.individual.net...
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ha scritto nel
>> messaggio news:1110195059.693757.152650@.g14g2000cwa.googlegroups.com...
>> > You can use an IMAGE column. Why do you want to do this though?
>> > Wouldn't it be easier just to store the file name? I don't think a PDF
>> > could be very useful in a database.
>> >
>> I can save the files PDF in a file server... and access it by an URL
> like:
>> \\FileServer\Location\FileName but
>> i think that the access to a sqlserver is more fast than an access to a
> file
>> system, so, the files that i need is very much...
>> or not ?
>> Checco.
>>
>

Files PDF into sql table.

I have 5 millions of file PDF (acrobat) to save into a sql table.
Wich type of field can i use into a table ?
Thank you, Checco.You can use an IMAGE column. Why do you want to do this though?
Wouldn't it be easier just to store the file name? I don't think a PDF
could be very useful in a database.
This issue is discussed here:
http://www.aspfaq.com/show.asp?id=2149
David Portas
SQL Server MVP
--|||There is a transform in DTS which will load the files into the database (
Search for Textcopy I think it is).
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Checco" <CheccoTogliLaForka@.Box.it> wrote in message
news:392pnnF5tn0h9U1@.individual.net...
>I have 5 millions of file PDF (acrobat) to save into a sql table.
> Wich type of field can i use into a table ?
> Thank you, Checco.
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ha scritto nel
messaggio news:1110195059.693757.152650@.g14g2000cwa.googlegroups.com...
> You can use an IMAGE column. Why do you want to do this though?
> Wouldn't it be easier just to store the file name? I don't think a PDF
> could be very useful in a database.
>
I can save the files PDF in a file server... and access it by an URL like:
\\FileServer\Location\FileName but
i think that the access to a sqlserver is more fast than an access to a file
system, so, the files that i need is very much...
or not ?
Checco.|||No. Keep your files in a filesystem instead, and save the path to them in a
table. Access to a filesystem is much faster. You might want to create
subfolders following some criteria, since keeping 5 million files in one
folder would be a little costly performance wise. You might want to create a
hash function for the subfolder name. That discussion is outside of the
scope of this group.
-Argenis
"Checco" <CheccoTogliLaForka@.Box.it> wrote in message
news:393299F5tg5j0U1@.individual.net...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ha scritto nel
> messaggio news:1110195059.693757.152650@.g14g2000cwa.googlegroups.com...
> I can save the files PDF in a file server... and access it by an URL
like:
> \\FileServer\Location\FileName but
> i think that the access to a sqlserver is more fast than an access to a
file
> system, so, the files that i need is very much...
> or not ?
> Checco.
>|||Thank you.
Checco.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> ha scritto nel messaggio
news:eZMNvqyIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> No. Keep your files in a filesystem instead, and save the path to them in
> a
> table. Access to a filesystem is much faster. You might want to create
> subfolders following some criteria, since keeping 5 million files in one
> folder would be a little costly performance wise. You might want to create
> a
> hash function for the subfolder name. That discussion is outside of the
> scope of this group.
> -Argenis
> "Checco" <CheccoTogliLaForka@.Box.it> wrote in message
> news:393299F5tg5j0U1@.individual.net...
> like:
> file
>

Files not filing with data

We have a quad sql server that runs OLTP transactions at the rate of
100's per second (read & Write).
We used to have all the tables on 1 file but started to notice high
contention on this file. We added 3 more files to match the processor
number. The problem is that the 3 additional files are not filling with
data. Does anyone know why this happens or can reccommend a fix?
--
willHi Will
Did you add the new files to the same file group or did you create a new
group?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> We have a quad sql server that runs OLTP transactions at the rate of
> 100's per second (read & Write).
> We used to have all the tables on 1 file but started to notice high
> contention on this file. We added 3 more files to match the processor
> number. The problem is that the 3 additional files are not filling with
> data. Does anyone know why this happens or can reccommend a fix?
> --
> will
>|||Hi
Since all your tables are in the 1st file group, SQL Server will continue to
use the 1st file group until it is full.
It does not balance over the files. You need to specifically move a table or
Index onto a file group for it to be used immediately.
Unless each file group is on a separate disk system (or LUN on a SAN), it
will not help adding file groups as the same IO contention continues to
exists.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> We have a quad sql server that runs OLTP transactions at the rate of
> 100's per second (read & Write).
> We used to have all the tables on 1 file but started to notice high
> contention on this file. We added 3 more files to match the processor
> number. The problem is that the 3 additional files are not filling with
> data. Does anyone know why this happens or can reccommend a fix?
> --
> will|||Let me correct myself:
We have a file group called 'Avail'.
In That file group we had all the tables running on 1 file.
We added 3 additional files to that filegroup and noticed they were not
filling with data. Has anyone seen this before?
--
will
"Kalen Delaney" wrote:
> Hi Will
> Did you add the new files to the same file group or did you create a new
> group?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "we7313" <we7313@.discussions.microsoft.com> wrote in message
> news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> > We have a quad sql server that runs OLTP transactions at the rate of
> > 100's per second (read & Write).
> >
> > We used to have all the tables on 1 file but started to notice high
> > contention on this file. We added 3 more files to match the processor
> > number. The problem is that the 3 additional files are not filling with
> > data. Does anyone know why this happens or can reccommend a fix?
> > --
> > will
> >
>
>|||If a table is created on a filegroup, and that filegroup has multiple files,
all the files should be used as more data is inserted into the table.
Are you seeing that existing tables are not seeming to use the new files?
How are you determining that?
Can you try creating a new table on the filegroup and see if its data is
spread around?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:286B7811-24A5-4323-8A4F-52384C44C3E3@.microsoft.com...
> Let me correct myself:
> We have a file group called 'Avail'.
> In That file group we had all the tables running on 1 file.
> We added 3 additional files to that filegroup and noticed they were not
> filling with data. Has anyone seen this before?
> --
> will
>
> "Kalen Delaney" wrote:
>> Hi Will
>> Did you add the new files to the same file group or did you create a new
>> group?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "we7313" <we7313@.discussions.microsoft.com> wrote in message
>> news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
>> > We have a quad sql server that runs OLTP transactions at the rate of
>> > 100's per second (read & Write).
>> >
>> > We used to have all the tables on 1 file but started to notice high
>> > contention on this file. We added 3 more files to match the processor
>> > number. The problem is that the 3 additional files are not filling
>> > with
>> > data. Does anyone know why this happens or can reccommend a fix?
>> > --
>> > will
>> >
>>
>>
>|||Yes the existing table is not using the new files in the file group.
If I go into enterprisemanager/view/taskpad I can see how big the data files
are and much data is actually in them. What I'm seeing is that 99% of the
data continues to go into the original file. I do see about 1% of data going
to the other 3 files combined.
--
will
"Kalen Delaney" wrote:
> If a table is created on a filegroup, and that filegroup has multiple files,
> all the files should be used as more data is inserted into the table.
> Are you seeing that existing tables are not seeming to use the new files?
> How are you determining that?
> Can you try creating a new table on the filegroup and see if its data is
> spread around?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "we7313" <we7313@.discussions.microsoft.com> wrote in message
> news:286B7811-24A5-4323-8A4F-52384C44C3E3@.microsoft.com...
> > Let me correct myself:
> > We have a file group called 'Avail'.
> > In That file group we had all the tables running on 1 file.
> > We added 3 additional files to that filegroup and noticed they were not
> > filling with data. Has anyone seen this before?
> > --
> > will
> >
> >
> > "Kalen Delaney" wrote:
> >
> >>
> >> Hi Will
> >>
> >> Did you add the new files to the same file group or did you create a new
> >> group?
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "we7313" <we7313@.discussions.microsoft.com> wrote in message
> >> news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> >> > We have a quad sql server that runs OLTP transactions at the rate of
> >> > 100's per second (read & Write).
> >> >
> >> > We used to have all the tables on 1 file but started to notice high
> >> > contention on this file. We added 3 more files to match the processor
> >> > number. The problem is that the 3 additional files are not filling
> >> > with
> >> > data. Does anyone know why this happens or can reccommend a fix?
> >> > --
> >> > will
> >> >
> >>
> >>
> >>
> >>
> >
>
>

Files not creating on Win2003

I have created a package which extracts data from a table and creates several files. This works fine on my PC (WinXP SP2), but when i deploy it to the server it doesn't work. I have also tried to create the package from scratch on the server as the administrator and it still has the same problem. Anyone have any ideas?"it doesn't work" is not much to diagnose the problem. Are you getting any error? Have you enabled SSIS logging - what logging information are you getting?|||You haven't provided a lot of information here, but the first thing to check is permissions on the folder you are writing to.|||Sorry Michael,

"it doesn't work" means that the files are not being created but the rest of it executes successfully. I'm not sure how to enable SSIS logging. Can you please point me to where i can find out how?

Thanks|||Sean,

The package is deployed on a Windows 2003 Standard server, SQL Server 2005 Standard. I am writing the files to a directory on C:\ and have tried giving Everyone full control on the directory and still no files are created.

Is there anywhere else i can check for the correct permissions?

Thanks|||

In the designer, click on the SSIS menu, and then choose Logging...

Check the Event Viewer in Administrative Tools to see if there are any Security exceptions being thrown. Sorry I can't be more specific here. It's tough to know exactly what your problem is. :)

|||Thanks Sean

After turning on the logging i have worked out what the problem was. It was a bug with my package.

Thanks everyone for your help.

Files Needed to Distribute CR 8.5 at run time

I package my Project using Deployment Wizard to create a set up file. I'm using VB6 and Crystal Report 8.5 and Access 2000 as my database. After installing my application in other computer I notice that all my Crytal Report 8.5 is not running. What other Files should I need?

Thanks

Noel
www.roadcs.comHi,

Refer the help file "Runtime.hlp", it's inbuild in crystal report setup.

Thanks & Regards,
K.Babu

Originally posted by Noel Rico
I package my Project using Deployment Wizard to create a set up file. I'm using VB6 and Crystal Report 8.5 and Access 2000 as my database. After installing my application in other computer I notice that all my Crytal Report 8.5 is not running. What other Files should I need?

Thanks

Noel
www.roadcs.com

Files in SQL 2005

Hello,
In my ASP.NET 2.0 web site I need to upload many files to various
folders and add the information in an SQL 2005 database.
I am creating a documents table which includes the fields:
[DocumentId], [DocumentAuthor] and [DocumentUrl]
This table will have records for many files.
Is there a standard way to name the files?
Maybe renaming the files to "doc" + DocumentId?
However my DocumentId is a Guid so it would be to big ... I think.
I would need to create the record, get the DocumentId and then access
the record again to add the DocumentUrl after renaming the file in
my .NET code.
Anyway, is there a standard way to make sure that every file name is
unique and that at the same time won't have an unreadable name?
Thanks,
Miguel
On Feb 14, 4:05 pm, "shapper" <mdmo...@.gmail.com> wrote:
> Hello,
> In my ASP.NET 2.0 web site I need to upload many files to various
> folders and add the information in an SQL 2005 database.
> I am creating a documents table which includes the fields:
> [DocumentId], [DocumentAuthor] and [DocumentUrl]
> This table will have records for many files.
> Is there a standard way to name the files?
> Maybe renaming the files to "doc" + DocumentId?
> However my DocumentId is a Guid so it would be to big ... I think.
> I would need to create the record, get the DocumentId and then access
> the record again to add the DocumentUrl after renaming the file in
> my .NET code.
> Anyway, is there a standard way to make sure that every file name is
> unique and that at the same time won't have an unreadable name?
> Thanks,
> Miguel
one possible solution make DocumentId as identity and store document
as documentid value
sql

Files in SQL 2005

Hello,
In my ASP.NET 2.0 web site I need to upload many files to various
folders and add the information in an SQL 2005 database.
I am creating a documents table which includes the fields:
[DocumentId], [DocumentAuthor] and [DocumentUrl]
This table will have records for many files.
Is there a standard way to name the files?
Maybe renaming the files to "doc" + DocumentId?
However my DocumentId is a Guid so it would be to big ... I think.
I would need to create the record, get the DocumentId and then access
the record again to add the DocumentUrl after renaming the file in
my .NET code.
Anyway, is there a standard way to make sure that every file name is
unique and that at the same time won't have an unreadable name?
Thanks,
MiguelOn Feb 14, 4:05 pm, "shapper" <mdmo...@.gmail.com> wrote:
> Hello,
> In my ASP.NET 2.0 web site I need to upload many files to various
> folders and add the information in an SQL 2005 database.
> I am creating a documents table which includes the fields:
> [DocumentId], [DocumentAuthor] and [DocumentUrl]
> This table will have records for many files.
> Is there a standard way to name the files?
> Maybe renaming the files to "doc" + DocumentId?
> However my DocumentId is a Guid so it would be to big ... I think.
> I would need to create the record, get the DocumentId and then access
> the record again to add the DocumentUrl after renaming the file in
> my .NET code.
> Anyway, is there a standard way to make sure that every file name is
> unique and that at the same time won't have an unreadable name?
> Thanks,
> Miguel
one possible solution make DocumentId as identity and store document
as documentid value

Files in SQL 2005

Hello,
In my ASP.NET 2.0 web site I need to upload many files to various
folders and add the information in an SQL 2005 database.
I am creating a documents table which includes the fields:
[DocumentId], [DocumentAuthor] and [DocumentUrl]
This table will have records for many files.
Is there a standard way to name the files?
Maybe renaming the files to "doc" + DocumentId?
However my DocumentId is a Guid so it would be to big ... I think.
I would need to create the record, get the DocumentId and then access
the record again to add the DocumentUrl after renaming the file in
my .NET code.
Anyway, is there a standard way to make sure that every file name is
unique and that at the same time won't have an unreadable name?
Thanks,
MiguelOn Feb 14, 4:05 pm, "shapper" <mdmo...@.gmail.com> wrote:
> Hello,
> In my ASP.NET 2.0 web site I need to upload many files to various
> folders and add the information in an SQL 2005 database.
> I am creating a documents table which includes the fields:
> [DocumentId], [DocumentAuthor] and [DocumentUrl]
> This table will have records for many files.
> Is there a standard way to name the files?
> Maybe renaming the files to "doc" + DocumentId?
> However my DocumentId is a Guid so it would be to big ... I think.
> I would need to create the record, get the DocumentId and then access
> the record again to add the DocumentUrl after renaming the file in
> my .NET code.
> Anyway, is there a standard way to make sure that every file name is
> unique and that at the same time won't have an unreadable name?
> Thanks,
> Miguel
one possible solution make DocumentId as identity and store document
as documentid value

Files corrupt when retrieving BLOB data

I am attempting (for the last several days) to save binary files to a SQL
Server 2005 database and then retrieve the files later. Have tried the new
varbinary(max) and image data types and several different techniques with no
luck. I can save 'most' file types and get something useful (will open,
compile, run etc.) but when I try to save and retrieve a .NET assembly
nothing works. The retrieved file is always the correct size but never
exactly the same as the file saved to the database.
The diff tool in SourceSafe shows a difference in the files but I cannot
visibly see the difference. Any help would be appreciated. This is do or die
for the project I am working on.
Code samples are below:
Writing the file:
using (TransactionScope oTranScope = new TransactionScope())
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
try
{
SqlCommand cmd = new SqlCommand("AddWorkZone", cn);
cmd.CommandType = CommandType.StoredProcedure;
System.IO.FileStream fs = new System.IO.FileStream(filepath,
System.IO.FileMode.Open, System.IO.FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
Byte[] b = br.ReadBytes((int)fs.Length);
fs.Close();
SqlParameter p1 = new SqlParameter("@.WorkZoneName",
SqlDbType.VarChar);
SqlParameter p2 = new SqlParameter("@.ShortDescription",
SqlDbType.VarChar);
SqlParameter p3 = new SqlParameter("@.LongDescription",
SqlDbType.VarChar);
SqlParameter p4 = new SqlParameter("@.Author", SqlDbType.VarChar);
SqlParameter p5 = new SqlParameter("@.AssemblyPath",
SqlDbType.VarChar);
SqlParameter p6 = new SqlParameter("@.SortOrder", SqlDbType.Int);
SqlParameter p7 = new SqlParameter("@.BinaryFile",
SqlDbType.Image, b.Length,
ParameterDirection.Input, false, 0, 0, null,
DataRowVersion.Current, b);
SqlParameter p8 = new SqlParameter("@.WZID", SqlDbType.Int);
p8.Direction = ParameterDirection.Output;
p1.Value = myWorkZone.WorkZoneName;
p2.Value = myWorkZone.ShortDescription;
p3.Value = myWorkZone.LongDescription;
p4.Value = myWorkZone.Author;
p5.Value = fName;
p6.Value = 0;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
cmd.Parameters.Add(p6);
cmd.Parameters.Add(p7);
cmd.Parameters.Add(p8);
cn.Open();
cmd.ExecuteNonQuery();
//We need the ID for later use.
WorkZoneID = (int)p8.Value;
}
Reading the file:
//No file exists so we must get a new one or abort.
SqlConnection cn = new
SqlConnection(currentConnectionStrings.GetConnectionString(LF.ConnectionStri
ngs.DatabaseTypes.LightningConfig));
cn.Open();
SqlCommand Cmd = new SqlCommand("SELECT BinaryFile FROM WorkZones WHERE
ID = " + myWorkZone.ID.ToString(), cn);
SqlDataReader Reader =
Cmd.ExecuteReader(CommandBehavior.SequentialAccess);
byte[] filebyte = new byte[0];
if (Reader.HasRows)
{
long startIndex = 0;
long retval;
int bufferSize = 100;
byte[] outByte = new byte[bufferSize];
BinaryWriter bw;
while (Reader.Read())
{
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate,
FileAccess.Write);
bw = new BinaryWriter(fs);
startIndex = 0;
retval = Reader.GetBytes(0, startIndex, outByte, 0, bufferSize);
while (retval == bufferSize)
{
bw.Write(outByte);
bw.Flush();
startIndex += bufferSize;
retval = Reader.GetBytes(0, startIndex, outByte, 0,
bufferSize);
}
bw.Write(outByte, 0, (int)retval - 1);
bw.Flush();
bw.Close();
fs.Close();
www.webtechone.com> nothing works. The retrieved file is always the correct size but never
> exactly the same as the file saved to the database.
It looks to me like the following statement is intentionally dropping the
last byte:
bw.Write(outByte, 0, (int)retval - 1);
I should think the code should be:
if(retval > 0)
{
bw.Write(outByte, 0, (int)retval);
}
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Pleasant" <PaulPleasant@.discussions.microsoft.com> wrote in message
news:4748FE17-5358-4CA0-8D8F-19DC9267B44F@.microsoft.com...
>I am attempting (for the last several days) to save binary files to a SQL
> Server 2005 database and then retrieve the files later. Have tried the new
> varbinary(max) and image data types and several different techniques with
> no
> luck. I can save 'most' file types and get something useful (will open,
> compile, run etc.) but when I try to save and retrieve a .NET assembly
> nothing works. The retrieved file is always the correct size but never
> exactly the same as the file saved to the database.
> The diff tool in SourceSafe shows a difference in the files but I cannot
> visibly see the difference. Any help would be appreciated. This is do or
> die
> for the project I am working on.
> Code samples are below:
> Writing the file:
> using (TransactionScope oTranScope = new TransactionScope())
> {
> using (SqlConnection cn = new SqlConnection(connectionString))
> {
> try
> {
> SqlCommand cmd = new SqlCommand("AddWorkZone", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> System.IO.FileStream fs = new System.IO.FileStream(filepath,
> System.IO.FileMode.Open, System.IO.FileAccess.Read);
> BinaryReader br = new BinaryReader(fs);
> Byte[] b = br.ReadBytes((int)fs.Length);
> fs.Close();
> SqlParameter p1 = new SqlParameter("@.WorkZoneName",
> SqlDbType.VarChar);
> SqlParameter p2 = new SqlParameter("@.ShortDescription",
> SqlDbType.VarChar);
> SqlParameter p3 = new SqlParameter("@.LongDescription",
> SqlDbType.VarChar);
> SqlParameter p4 = new SqlParameter("@.Author",
> SqlDbType.VarChar);
> SqlParameter p5 = new SqlParameter("@.AssemblyPath",
> SqlDbType.VarChar);
> SqlParameter p6 = new SqlParameter("@.SortOrder",
> SqlDbType.Int);
> SqlParameter p7 = new SqlParameter("@.BinaryFile",
> SqlDbType.Image, b.Length,
> ParameterDirection.Input, false, 0, 0, null,
> DataRowVersion.Current, b);
> SqlParameter p8 = new SqlParameter("@.WZID", SqlDbType.Int);
> p8.Direction = ParameterDirection.Output;
> p1.Value = myWorkZone.WorkZoneName;
> p2.Value = myWorkZone.ShortDescription;
> p3.Value = myWorkZone.LongDescription;
> p4.Value = myWorkZone.Author;
> p5.Value = fName;
> p6.Value = 0;
> cmd.Parameters.Add(p1);
> cmd.Parameters.Add(p2);
> cmd.Parameters.Add(p3);
> cmd.Parameters.Add(p4);
> cmd.Parameters.Add(p5);
> cmd.Parameters.Add(p6);
> cmd.Parameters.Add(p7);
> cmd.Parameters.Add(p8);
> cn.Open();
> cmd.ExecuteNonQuery();
> //We need the ID for later use.
> WorkZoneID = (int)p8.Value;
> }
> Reading the file:
> //No file exists so we must get a new one or abort.
> SqlConnection cn = new
> SqlConnection(currentConnectionStrings.GetConnectionString(LF.ConnectionSt
rings.DatabaseTypes.LightningConfig));
> cn.Open();
> SqlCommand Cmd = new SqlCommand("SELECT BinaryFile FROM WorkZones WHERE
> ID = " + myWorkZone.ID.ToString(), cn);
> SqlDataReader Reader =
> Cmd.ExecuteReader(CommandBehavior.SequentialAccess);
> byte[] filebyte = new byte[0];
> if (Reader.HasRows)
> {
> long startIndex = 0;
> long retval;
> int bufferSize = 100;
> byte[] outByte = new byte[bufferSize];
> BinaryWriter bw;
> while (Reader.Read())
> {
> FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate,
> FileAccess.Write);
> bw = new BinaryWriter(fs);
> startIndex = 0;
> retval = Reader.GetBytes(0, startIndex, outByte, 0,
> bufferSize);
> while (retval == bufferSize)
> {
> bw.Write(outByte);
> bw.Flush();
> startIndex += bufferSize;
> retval = Reader.GetBytes(0, startIndex, outByte, 0,
> bufferSize);
> }
> bw.Write(outByte, 0, (int)retval - 1);
> bw.Flush();
> bw.Close();
> fs.Close();
>
> --
> www.webtechone.com|||Good grief!
I copied this code from:
http://msdn.microsoft.com/library/d...romdatabase.asp
That's what I get for copying code and then not debugging it myself.
Thanks a bunch!
--
www.webtechone.com
"Dan Guzman" wrote:

> It looks to me like the following statement is intentionally dropping the
> last byte:
> bw.Write(outByte, 0, (int)retval - 1);
> I should think the code should be:
> if(retval > 0)
> {
> bw.Write(outByte, 0, (int)retval);
> }
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Pleasant" <PaulPleasant@.discussions.microsoft.com> wrote in message
> news:4748FE17-5358-4CA0-8D8F-19DC9267B44F@.microsoft.com...
>
>

Files associated with SQL server

Hi all,
Is there any any to find the files that are associated with a SQL server Instance ? Does SQL server provide any API for that ?

Hi,

Each instance installs in a different directory. If you look at you default installation directory [C:\Program Files\Microsoft SQL Server], you will find a dierctory for each instance and for each feature (e.g. SSIS, SSAS, etc). These directories are called MSSQL.x, the x separating out the features and instances. So e.g. You have installed 2 instances of SQL Server, one of SSAS and one of SSRS, you directory structure would look something like:

C:\Program Files\Microsoft SQL Server\90 - SQL Binaries (SQL 2005).

C:\Program Files\Microsoft SQL Server\MSSQL.1 - SQL Database Engine Instance 1

C:\Program Files\Microsoft SQL Server\MSSQL.2 - SQL SSAS

C:\Program Files\Microsoft SQL Server\MSSQL.3 - SQL SSRS

C:\Program Files\Microsoft SQL Server\MSSQL.4 - SQL Database Engine Instance 2

There is nothing stopping users from moving these database files around so to check that you have the correct path you could execute

Code Snippet

select * from sys.database_files

from the instance and that will confirm the location of the database files.

|||

You can run OSQL (sql 2000) or SQLCMD (2005) command to get files details against a instance.

select *from sysaltfiles -- SQL Server 2000

select *From sys.master_files -- SQL Server 2005

Eg. (for SQL 2005)

C:>sqlcmd -SYourInstanceName -UUserName -PPassword -dMaster -q"Select Physical_Name From Sys.Master_Files"

Read more about SQLCMD in BOL (OSQL if its sql 2000)

Madhu

|||

Are you looking for user databases files, if refer to the Madhu's method in this case.

For all associated binary files then they are installed under Program Files (default installation) or any directory you might have chosen when installing as Custom.

|||Hi all,
Thanks for the reply.
Sorry all. I think i didnt post the question correctly.I want to know the files associated with the SQL server programmatically. I want to know whether microsoft provides any API for that or is there any other method to do the same ?
|||

Ah, yes. Have a look at SQL Server Management Objects (SMO), and I quote from BOL:

"SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management."

|||Hi
Thanks !!!. I will look into it.
|||

The above post is correct - and you can use this link at any time for the default locations:

http://msdn2.microsoft.com/en-us/library/ms143547.aspx

The nice thing about the link is that it is kept up to date as things change.

- Buck Woody

Files associated with SQL server

Hi all,
Is there any any to find the files that are associated with a SQL server Instance ? Does SQL server provide any API for that ?

Hi,

C:\Program Files\Microsoft SQL Server\90 - SQL Binaries (SQL 2005).

C:\Program Files\Microsoft SQL Server\MSSQL.1 - SQL Database Engine Instance 1

C:\Program Files\Microsoft SQL Server\MSSQL.2 - SQL SSAS

C:\Program Files\Microsoft SQL Server\MSSQL.3 - SQL SSRS

C:\Program Files\Microsoft SQL Server\MSSQL.4 - SQL Database Engine Instance 2

There is nothing stopping users from moving these database files around so to check that you have the correct path you could execute

Code Snippet

select * from sys.database_files

from the instance and that will confirm the location of the database files.

|||

You can run OSQL (sql 2000) or SQLCMD (2005) command to get files details against a instance.

select *from sysaltfiles -- SQL Server 2000

select *From sys.master_files -- SQL Server 2005

Eg. (for SQL 2005)

C:>sqlcmd -SYourInstanceName -UUserName -PPassword -dMaster -q"Select Physical_Name From Sys.Master_Files"

Read more about SQLCMD in BOL (OSQL if its sql 2000)

Madhu

|||

Are you looking for user databases files, if refer to the Madhu's method in this case.

For all associated binary files then they are installed under Program Files (default installation) or any directory you might have chosen when installing as Custom.

Ah, yes. Have a look at SQL Server Management Objects (SMO), and I quote from BOL:

|||Hi
Thanks !!!. I will look into it.
|||

The above post is correct - and you can use this link at any time for the default locations:

http://msdn2.microsoft.com/en-us/library/ms143547.aspx

The nice thing about the link is that it is kept up to date as things change.

- Buck Woodysql

Files associated with SQL server

Hi all,
Is there any any to find the files that are associated with a SQL server Instance ? Does SQL server provide any API for that ?

Hi,

C:\Program Files\Microsoft SQL Server\90 - SQL Binaries (SQL 2005).

C:\Program Files\Microsoft SQL Server\MSSQL.1 - SQL Database Engine Instance 1

C:\Program Files\Microsoft SQL Server\MSSQL.2 - SQL SSAS

C:\Program Files\Microsoft SQL Server\MSSQL.3 - SQL SSRS

C:\Program Files\Microsoft SQL Server\MSSQL.4 - SQL Database Engine Instance 2

There is nothing stopping users from moving these database files around so to check that you have the correct path you could execute

Code Snippet

select * from sys.database_files

from the instance and that will confirm the location of the database files.

|||

You can run OSQL (sql 2000) or SQLCMD (2005) command to get files details against a instance.

select *from sysaltfiles -- SQL Server 2000

select *From sys.master_files -- SQL Server 2005

Eg. (for SQL 2005)

C:>sqlcmd -SYourInstanceName -UUserName -PPassword -dMaster -q"Select Physical_Name From Sys.Master_Files"

Read more about SQLCMD in BOL (OSQL if its sql 2000)

Madhu

|||

Are you looking for user databases files, if refer to the Madhu's method in this case.

For all associated binary files then they are installed under Program Files (default installation) or any directory you might have chosen when installing as Custom.

Ah, yes. Have a look at SQL Server Management Objects (SMO), and I quote from BOL:

|||Hi
Thanks !!!. I will look into it.
|||

The above post is correct - and you can use this link at any time for the default locations:

http://msdn2.microsoft.com/en-us/library/ms143547.aspx

The nice thing about the link is that it is kept up to date as things change.

- Buck Woody

Files associated with a DB

Hi all
Is there any way to find the files/filenames associated with the SQL server instance ?. Is there any way to find it programmatically ? Does Microsoft provide any API for that ?

Thanks in Adcance,
mani3727

Maybe you can use WMI.

See what you can list installed software

In another example you can List Specific Files Included in the Indexing Service

|||

Are you talking about binary files or database files?

If you are using SQL Server 2005 you can use the following code to get all databases related to the instance:

Code Snippet

SELECT * FROM sys.master_files

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi Wesley

Thanks for the reply.I want to know all the files associated with a specific SQL server instance.Could you tell me how it can be done programmaically ?

Thanks,
mani3727

Tuesday, March 27, 2012

Filter before export

Friday, March 23, 2012

FileSystem Deployment

Wednesday, March 21, 2012

files, filegroups

Files sizes in a OLE bmp field

Files PDF into sql table.

Files PDF into sql table.

Files PDF into sql table.

Files not filing with data

Files not filing with data

Files not filing with data

Files not creating on Win2003

Files Needed to Distribute CR 8.5 at run time

Files in SQL 2005

Files in SQL 2005

Files in SQL 2005

Files corrupt when retrieving BLOB data

Files associated with SQL server

Files associated with SQL server

Files associated with SQL server

Files associated with a DB

Format Alignment

Blog Archive

About Me