Exporting DB as SQL using SQL Server 2005

Hi all,

I have a MSSQL database running on my SQL 2005 server and I want to export the entire contents to a file as SQL statements with INSERT statements. How do I do this?

I've discovered how to export the contents as a flat comma deliminiated file (although only one table at a time) and how to export the CREATE statements in SQL for each table but so far I cannot find a way to just export the entire DB as SQL.

Thanks!

There are no SQL Server tools that will create the INSERT statements for you...but it should be fairly staightforward to create a C# program that would gererate the CREATE TABLE statements using SMO and then code to retrieve the data for generating the INSERT statements.

Thanks,

Peter Saddow

|||

Hi,

tzake a look at this one here: http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Just to give you an example SMO for creating table scripts (DDL) is quite easy, that a sample posted a few days ago:

string Script = new Microsoft.SqlServer.Management.Smo.Server(".").Databases["Adventureworks"].Tables["SomeTable"].Script();

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Exported PDF Security

Hi All
I am using SQL Reporting Services. I looked into the properties of the
generated .pdf file and it allows user to edit the file contents using a
full version Adobe Acrobat.
Is it possible to make the exported PDF file uneditable ? Any help on this
will be hisghly appreciated
Thanks
Vaibhav SharmaIf anyone says yes, well theyre wrong.
The issue here is not Reporting Services but rather the PDF Spec,
Using tools I will NOT mention here , I can edit any PDF , from any
source "AT-WILL"
The funny part is the tools I use are the same tools "I" use for allpdf
work and I have had developers/designers choke on their words many a
times over this exact issue.
I go by the xiom of , "If a client can read it, so can I" (client being
client tool, webbrowser, pdfviewer etc) but you can always take that a
step further and say "If I can read it I can change it"
NOW All that said is there a way to "Lock" a PDF Against edit for the
other 99% of people, yes, but I am unsure if RS has it implemented, I
have not seen it anywhere in the docs.|||RS does not support any type of locking of the PDF. You could write your own
extension to do this.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WertmanTheMad" <cwertman@.webchamps.com> wrote in message
news:1104179054.142883.151010@.f14g2000cwb.googlegroups.com...
> If anyone says yes, well theyre wrong.
> The issue here is not Reporting Services but rather the PDF Spec,
> Using tools I will NOT mention here , I can edit any PDF , from any
> source "AT-WILL"
> The funny part is the tools I use are the same tools "I" use for allpdf
> work and I have had developers/designers choke on their words many a
> times over this exact issue.
> I go by the xiom of , "If a client can read it, so can I" (client being
> client tool, webbrowser, pdfviewer etc) but you can always take that a
> step further and say "If I can read it I can change it"
> NOW All that said is there a way to "Lock" a PDF Against edit for the
> other 99% of people, yes, but I am unsure if RS has it implemented, I
> have not seen it anywhere in the docs.
>|||Any pointers on how to do that? I mean how to go about writing an extension.
TIA
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OZBq3bF7EHA.1260@.TK2MSFTNGP12.phx.gbl...
> RS does not support any type of locking of the PDF. You could write your
> own
> extension to do this.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "WertmanTheMad" <cwertman@.webchamps.com> wrote in message
> news:1104179054.142883.151010@.f14g2000cwb.googlegroups.com...
>> If anyone says yes, well theyre wrong.
>> The issue here is not Reporting Services but rather the PDF Spec,
>> Using tools I will NOT mention here , I can edit any PDF , from any
>> source "AT-WILL"
>> The funny part is the tools I use are the same tools "I" use for allpdf
>> work and I have had developers/designers choke on their words many a
>> times over this exact issue.
>> I go by the xiom of , "If a client can read it, so can I" (client being
>> client tool, webbrowser, pdfviewer etc) but you can always take that a
>> step further and say "If I can read it I can change it"
>> NOW All that said is there a way to "Lock" a PDF Against edit for the
>> other 99% of people, yes, but I am unsure if RS has it implemented, I
>> have not seen it anywhere in the docs.
>|||I know it is possible because 3rd parties have come up with additional
rendering extensions but looking at it another way (and definitely easier)
you could have a delivery extension (if you are sending this via
subscriptions). Your delivery extension could save the report as a pdf file,
then run it through whatever utility that locks the pdf and then you send it
on via the email. Look in the help on delivery extension.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:eC7tX7F7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Any pointers on how to do that? I mean how to go about writing an
extension.
> TIA
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OZBq3bF7EHA.1260@.TK2MSFTNGP12.phx.gbl...
> > RS does not support any type of locking of the PDF. You could write your
> > own
> > extension to do this.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "WertmanTheMad" <cwertman@.webchamps.com> wrote in message
> > news:1104179054.142883.151010@.f14g2000cwb.googlegroups.com...
> >> If anyone says yes, well theyre wrong.
> >>
> >> The issue here is not Reporting Services but rather the PDF Spec,
> >>
> >> Using tools I will NOT mention here , I can edit any PDF , from any
> >> source "AT-WILL"
> >>
> >> The funny part is the tools I use are the same tools "I" use for allpdf
> >> work and I have had developers/designers choke on their words many a
> >> times over this exact issue.
> >>
> >> I go by the xiom of , "If a client can read it, so can I" (client being
> >> client tool, webbrowser, pdfviewer etc) but you can always take that a
> >> step further and say "If I can read it I can change it"
> >>
> >> NOW All that said is there a way to "Lock" a PDF Against edit for the
> >> other 99% of people, yes, but I am unsure if RS has it implemented, I
> >> have not seen it anywhere in the docs.
> >>
> >
> >
>|||Thanks very much.
But in my case i am not sending file by susbscription, i am displaying
report as link and generating it on demand like an invoice pdf file.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ebWjTnG7EHA.2608@.TK2MSFTNGP10.phx.gbl...
>I know it is possible because 3rd parties have come up with additional
> rendering extensions but looking at it another way (and definitely easier)
> you could have a delivery extension (if you are sending this via
> subscriptions). Your delivery extension could save the report as a pdf
> file,
> then run it through whatever utility that locks the pdf and then you send
> it
> on via the email. Look in the help on delivery extension.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
> news:eC7tX7F7EHA.2196@.TK2MSFTNGP14.phx.gbl...
>> Any pointers on how to do that? I mean how to go about writing an
> extension.
>> TIA
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OZBq3bF7EHA.1260@.TK2MSFTNGP12.phx.gbl...
>> > RS does not support any type of locking of the PDF. You could write
>> > your
>> > own
>> > extension to do this.
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "WertmanTheMad" <cwertman@.webchamps.com> wrote in message
>> > news:1104179054.142883.151010@.f14g2000cwb.googlegroups.com...
>> >> If anyone says yes, well theyre wrong.
>> >>
>> >> The issue here is not Reporting Services but rather the PDF Spec,
>> >>
>> >> Using tools I will NOT mention here , I can edit any PDF , from any
>> >> source "AT-WILL"
>> >>
>> >> The funny part is the tools I use are the same tools "I" use for
>> >> allpdf
>> >> work and I have had developers/designers choke on their words many a
>> >> times over this exact issue.
>> >>
>> >> I go by the xiom of , "If a client can read it, so can I" (client
>> >> being
>> >> client tool, webbrowser, pdfviewer etc) but you can always take that a
>> >> step further and say "If I can read it I can change it"
>> >>
>> >> NOW All that said is there a way to "Lock" a PDF Against edit for the
>> >> other 99% of people, yes, but I am unsure if RS has it implemented, I
>> >> have not seen it anywhere in the docs.
>> >>
>> >
>> >
>>
>|||If you have your own UI (i.e. not using Report Manager but using your own
ASP.net application) then you could use the same technique by using web
services to first render PDF on your server, run the untility and then send
the resultant protected PDF to the user. If you want to use Report Manager
portal then there is not an easy solution that I can think of.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:Of5HKMP7EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Thanks very much.
> But in my case i am not sending file by susbscription, i am displaying
> report as link and generating it on demand like an invoice pdf file.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ebWjTnG7EHA.2608@.TK2MSFTNGP10.phx.gbl...
> >I know it is possible because 3rd parties have come up with additional
> > rendering extensions but looking at it another way (and definitely
easier)
> > you could have a delivery extension (if you are sending this via
> > subscriptions). Your delivery extension could save the report as a pdf
> > file,
> > then run it through whatever utility that locks the pdf and then you
send
> > it
> > on via the email. Look in the help on delivery extension.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
> > news:eC7tX7F7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> >> Any pointers on how to do that? I mean how to go about writing an
> > extension.
> >>
> >> TIA
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:OZBq3bF7EHA.1260@.TK2MSFTNGP12.phx.gbl...
> >> > RS does not support any type of locking of the PDF. You could write
> >> > your
> >> > own
> >> > extension to do this.
> >> >
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> >
> >> > "WertmanTheMad" <cwertman@.webchamps.com> wrote in message
> >> > news:1104179054.142883.151010@.f14g2000cwb.googlegroups.com...
> >> >> If anyone says yes, well theyre wrong.
> >> >>
> >> >> The issue here is not Reporting Services but rather the PDF Spec,
> >> >>
> >> >> Using tools I will NOT mention here , I can edit any PDF , from any
> >> >> source "AT-WILL"
> >> >>
> >> >> The funny part is the tools I use are the same tools "I" use for
> >> >> allpdf
> >> >> work and I have had developers/designers choke on their words many a
> >> >> times over this exact issue.
> >> >>
> >> >> I go by the xiom of , "If a client can read it, so can I" (client
> >> >> being
> >> >> client tool, webbrowser, pdfviewer etc) but you can always take that
a
> >> >> step further and say "If I can read it I can change it"
> >> >>
> >> >> NOW All that said is there a way to "Lock" a PDF Against edit for
the
> >> >> other 99% of people, yes, but I am unsure if RS has it implemented,
I
> >> >> have not seen it anywhere in the docs.
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

Export varbinary Data Types to Local PC from SQL Database

Could someone help me with writing the code to export the contents of a varbinary field in my database to make the contents be written to the local harddrive? I can do this in Visual Foxpro but my company wants it in C# and I have no clue about C#.
Thank you:eek:What do you need help doing? Are you looking for someone to write the C# program for you?

-PatP|||You know.......this is the 6th forum I have posted this question to. I must not be asking my question correctly.

I need help writing code to retrieve all my files out of an SQL IMAGE (varbinary) field and make the contents become files back on my hard drive again.|||There are 192 records. I need to retrieve the contents of all the varbinary fields and put the contents back on my hard drive. Some are .xls files, some .doc, some .txt. But, they need to come back out of SQL Server and go back to being files on my hard drive again.

Export valid XML files

Ladies and Gents,
I cannot seem to find an (easily) workable solution anywhere on the web.
All I need to do, is return the contents of some SQL server tables as XML
data sets.
I was able to form "proper" XML files by adding a top-level root element
(still unclear on why that is not built in). Now the issue that remains is
that SQL server will return the results with a ROWGUID and a full line
of undescores (_________________) before the actual XML.
I am trying to keep the process as simple as can get, avoiding SQLXML
and IIS -- I just want to find a solution where I can create a simple (!)
DTS package that will deliver a properly formatted XML file with a top
level element, one that IE will be able to display properly without
any further editing.
1) is this possible (anything is possible -- can this be done via some form
of "FOR XML" statement)
2) if there is no easy solution to 1, what methods would you recommend to
make the process
most maintanable? My requirements are simple -- I need to produce a
"feed" -- a set of properly formatted
XML files, based on many queries -- probably about 15-20 files
alltogether, so I need this to be
as simple as possible.
TIA,
EugeneCould you be more specific? Are you trying to export the database schema or
the data in your tables?
As simple as possible? A FOR XML query in a SQLXML template.
ML|||If you follow the "Microsoft" way, you set up a "web service" using the HTML
capabilities of SQL Server. The template supplies the formatting you need an
d
returns data in the proper format.
I do not have a link, but you can push a root tag into your FOR XML return.
The examples I have seen use the template, as well.
If the return is not valid, you can also throw the data through an XSLT
transform and get it to look however you desire. This is an extra step,
however.
NOTE: I am fairly fond of the whole SOA/web service concept, but it is not
for everyone. For SQL Server 2000, the link into IIS is not the best
implementation (HTTP endpoints in SQL 2005 are much better), but they provid
e
a means of moving towards SOA. SOA is not a silver bullet, but it makes a lo
t
of sense if you can make the paradigm change to working with messages instea
d
of CRUD.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Eugene" wrote:

> Ladies and Gents,
> I cannot seem to find an (easily) workable solution anywhere on the web.
> All I need to do, is return the contents of some SQL server tables as XML
> data sets.
> I was able to form "proper" XML files by adding a top-level root element
> (still unclear on why that is not built in). Now the issue that remains i
s
> that SQL server will return the results with a ROWGUID and a full line
> of undescores (_________________) before the actual XML.
> I am trying to keep the process as simple as can get, avoiding SQLXML
> and IIS -- I just want to find a solution where I can create a simple (!)
> DTS package that will deliver a properly formatted XML file with a top
> level element, one that IE will be able to display properly without
> any further editing.
> 1) is this possible (anything is possible -- can this be done via some for
m
> of "FOR XML" statement)
> 2) if there is no easy solution to 1, what methods would you recommend to
> make the process
> most maintanable? My requirements are simple -- I need to produce a
> "feed" -- a set of properly formatted
> XML files, based on many queries -- probably about 15-20 files
> alltogether, so I need this to be
> as simple as possible.
> TIA,
> Eugene
>
>|||I am trying to export the data.
Something along the lines of :
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||I am trying to export the data. Here's my query
SELECT
1 AS TAG
,NULL AS PARENT
,NULL AS [INSTITUTIONS!1]
,NULL AS [INSTITUTION!2!iconum]
,NULL AS [INSTITUTION!2!cusip]
,NULL AS [INSTITUTION!2!dlr]
,NULL AS [INSTITUTION!2!position]
,NULL AS [INSTITUTION!2!pct_held]
,NULL AS [INSTITUTION!2!position_change]
UNION
SELECT 2 AS TAG
,1 AS PARENT
,NULL AS [INSTITUTIONS!1]
,iconum AS [INSTITUTION!2!iconum]
,cusip AS [INSTITUTION!2!cusip]
,dlr AS [INSTITUTION!2!dlr]
,position AS [INSTITUTION!2!position]
,pct_held AS [INSTITUTION!2!pct_held]
,position_change AS [INSTITUTION!2!position_change]
FROM ##EUGENE_TEMP
ORDER BY [INSTITUTION!2!iconum]
FOR XML EXPLICIT
I get back something like :
XML_F52E2B61...
_____________________________
<INSTITUTIONS>
<INSTITUTION iconum="10134" cusip="151313103" dlr="2005-03-31T00:00:00"
position="22000" pct_held="0.002" position_change="22000"/>
</INSTITUTIONS>
I need to not have the top
XML_F52E2B61...
_____________________________.
SQLXML is not really an option now -- no ISS on the SQL box... Looks like
I'll just have to script a solution, unless
someone has another idea...
Thanks
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||If you're using BCP to write the results to a text file or using QA's
"Results to file" option, you'll have to remove the unwanted pieces of text
either manually or programatically (not in T-SQL).
Whether you like it or not, you will need a client application to read the
result.
Oh, and another thing - XML results might get split into several rows of
data if not read as an XML by an appropriate client.
If installing IIS creates a possible breach of security, you can still use
the IIS lock-down tool to keep your network secure. The power of SQLXML is
immense, so why not use it?
ML

export report server contents/items to xml file

how can I output the contents or my report server to an xml file? I
assume I would use some variation of ListChildren and the WriteXml
method.I was able to accomplish this using the following code:
Dim impersonationContext As
System.Security.Principal.WindowsImpersonationContext
Dim currentWindowsIdentity As
System.Security.Principal.WindowsIdentity
currentWindowsIdentity = CType(User.Identity,
System.Security.Principal.WindowsIdentity)
impersonationContext = currentWindowsIdentity.Impersonate()
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim items As CatalogItem() = Nothing
' Retrieve a list of all items from the report server database.
Try
items = rs.ListChildren("/", True)
Catch ex As Exception
response.write(ex.Message)
End Try
' Serialize the contents as an XML document and write the
contents to a file.
Try
Dim fs As New FileStream("C:\CatalogItems.xml",
FileMode.Create)
Dim writer As New XmlTextWriter(fs, Encoding.Unicode)
Dim serializer As New XmlSerializer(GetType(CatalogItem()))
serializer.Serialize(writer, items)
response.write("Contents successfully written to a file.")
Catch ex As Exception
response.write(ex.Message)
End Try
Stephen wrote:
> how can I output the contents or my report server to an xml file? I
> assume I would use some variation of ListChildren and the WriteXml
> method.