Sql Create error: file

Showing posts with label file. Show all posts

Thursday, March 29, 2012

Create table,fields come from csv file

I want to create a table automatically,and fields come from a csv file

any idea? TIA

One way is using two packages and a configuration file.

I'm assuming that you are constructing the name of the table on the fly. Note that the table MUST always have the same format.

First create a sample of what you want your table to look like.

Package 1:
Create an SSIS package that loads data from a CSV file into that table.
Make the tablename come from a variable.
Put the variable in a configuration table

Package 2:
Create a variable that will contain the table name.
Create a variable expressions that has the sql to create the table using the variable previously defined.
Create a variable expression that has the sql to update the variable in the configuration table from Package 1
Create a SQL Task to create the table
Create a SQL Task to update the configuration table variable
Create an Execute SSIS package task to execute Package 1

Similarly you can extend this to the source by scanning directories for files and loading different CSV files into different tables.

Hope this helps,

Larry

CREATE TABLE scripts

I have a bunch of CREATE TABLE scripts I need to run.

In Visual Studio 2003 you could right-click on a .SQL file and choose Run from the shortcut menu... and it would run the script. Visual Studio 2005 there is not a Run on the right-click short cut menu.

What gives?

hi,

why not just downloading the free official management tool, SQL Server Management Studio Express, from http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en ..

after installing it, the .Sql extension will be associated to this tool... very easy to use..

regards

|||

You should consider asking this question of the VS folks, they hang out over in the VS forums.

Mike

Tuesday, March 27, 2012

create table question

Hi all,
I bring data from 15 different files into several SQL Server tables every
night. I first bring data from CSV file and store them into 15 different
intermediary tables. Then I execute stored procedure that imports data from
intermediary tables into the tables that our users use.
My question is about clearing the intermediary tables. Right now I am
just truncating the tables before importing new data from CSV files. So I
use Insert Into...Select query. But this can create a problem if any of the
columns in the CSV files are removed. So I am thinking if it is worthwhile
droping the tables and then import data using Select...Into query. This way
even if a column is removed, the data will still be imported from the CSV
files. My question is are there any drawbacks in droping and recreating 15
tables everynight instead of truncating them?
Thanks.Actually, if you're database recovery model is not FULL, a SELECT INTO is
going to run significantly faster than an INSERT SELECT, because it's
considered a BULK operation.
Can't think of any drawbacks.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Nikhil Patel" <nikhil0100@.aol.com> wrote in message
news:O9DvE8GNFHA.3512@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I bring data from 15 different files into several SQL Server tables
> every
> night. I first bring data from CSV file and store them into 15 different
> intermediary tables. Then I execute stored procedure that imports data
> from
> intermediary tables into the tables that our users use.
> My question is about clearing the intermediary tables. Right now I am
> just truncating the tables before importing new data from CSV files. So I
> use Insert Into...Select query. But this can create a problem if any of
> the
> columns in the CSV files are removed. So I am thinking if it is worthwhile
> droping the tables and then import data using Select...Into query. This
> way
> even if a column is removed, the data will still be imported from the CSV
> files. My question is are there any drawbacks in droping and recreating 15
> tables everynight instead of truncating them?
> Thanks.
>|||I can see some drawbacks to creating the tables each time. If columns
are missing or renamed then won't your SPs that access them fail
anyway? You should avoid using SELECT * in production code. Plus,
you'll have to give DDL admin rights to whatever process performs the
load.
One possible approach is to create views that only expose certain
columns in the base table(s) then use a Dynamic Properties task in DTS
to load the data to the appropriate view each time. This does assume
you can read some metadata that determines what columns should be
present. If that metadata isn't available then I would have thought it
desirable to fail the load whenever some data was missing. What if the
file contains zero columns - just empty rows?
David Portas
SQL Server MVP
--sql

create table from xml schema

Hi everybody!
I need to create a table in SQL server from the xml schema of a file. Is
it possible?
Thanks a lot.
Anahi
Download SQLXML 3.0 from the MS SQL Server Website.
Check out the SQLXML Bulk Load component.
I think you can write a script in a DTS package and load data.
check out this article:
http://support.microsoft.com/default...b;en-us;316005
until MS Support replies.
"Anahi Luduea" <anahi@.optience.com> wrote in message
news:%23ANr9j0cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi everybody!
> I need to create a table in SQL server from the xml schema of a file. Is
> it possible?
> Thanks a lot.
> Anahi
>

Create table from text file, extract data, create new table from extracted data.

Hello all,

Please help...

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem...

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.select *
INTO ExtractedDataFile
from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%'|||Thanks so much Brett!

I have more question though......

I will be needing to do the importation of text file & extraction of data at least once a month. Then after I import & extract data I will need to append the extracted data into the table ExtractedDataFile. But I will only need to append the data if there is no duplicate DF_SC_Case_Nbr.

Can all this be done in just one stored procedure? How will I do this?

Thanks again.|||What's a duplicate?

Do you have books online?

Open it up, and leave it open...

Look into bcp

Create table from Text

Hi, all. I'm fairly new to SQL, and I have been trying to create a table
from a text file. I have been looking at this for days, and can't find the
problem. I get a syntax error " Line 55: Incorrect syntax near
'DateUpdated'." Here is the query. Any suggestions would be appreciated,
as I am trying to learn and improve.

Use ACH
go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[ImportFiles]
GO

CREATE Procedure ImportFiles
@.FilePath varchar(1000),
@.MergeProc varchar(128) = 'MergeData'
AS
DECLARE @.cmd varchar(2000),
@.Command_String varchar(3000)

DECLARE @.FileName varchar(1000),
@.File varchar(1000)

CREATE table ##Import (datarow varchar(200))
CREATE table #Dir (datarow varchar(200))

DROP TABLE ACHParticipants

select @.cmd = 'dir /B' + @.FilePath
delete #Dir
insert #Dir exec master..xp_cmdshell @.cmd

delete #Dir where datarow is null or datarow like '%not found%'

while exists (select * from #Dir)

BEGIN
select @.FileName = min(datarow) from #Dir
select @.file= @.FilePath + @.FileName
select @.cmd = 'bulk insert'
select @.cmd = @.cmd + ' ##Import'
select @.cmd = @.cmd + ' from'
select @.cmd = @.cmd + ' @.File,'
select @.cmd = @.cmd + ' with (FIELDTERMINATOR=''\n'''
select @.cmd = @.cmd + ',ROWTERMINATOR = '':\n'')'

truncate table ##Import

-- import the data
exec (@.cmd)

-- remove filename just imported
delete #Dir where datarow = @.FileName

exec @.MergeProc
END

drop table ##Import
drop table #Dir
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MergeData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MergeData]
GO

CREATE PROCEDURE MergeData
AS
CREATE table ACHParticipants
(RoutingNum varchar(9),
OfficeCode varchar(1),
ServicingFRBNum varchar(9),
RecordType varchar(1),
ChangeDate varchar(8),
NewRoutingNum varchar(9),
BankName varchar(36),
BankAddress varchar(36),
City varchar(20),
State varchar(2),
Zipcode varchar(10),
Phone varchar(14),
StatusCode varchar(1),
DataView varchar(1),
Filler varchar(5),
DateUpdated datetime)

INSERT INTO ACHParticipants
(Routing_Number
, Office_Code
, Servicing_FRB_Number
, Record_Type_Code
, Change_Date
, New_Routing_Number
, Customer_Name
, Address
, City
, State_Code
, Zipcode
, Telephone
, Institution_Status_Code
, Data_View_Code
, Filler
, DateUpdated)

SELECT Substring(DataRow,1,9) AS RoutingNum,
Substring(DataRow,10,1) AS OfficeCode,
Substring(DataRow,11,9) AS ServicingFRBNum,
Substring(DataRow,20,1) AS RecordType,
convert(datetime,Substring(DataRow,21,6)) AS ChangeDate,
Substring(DataRow,27,9) AS NewRoutingNum,
Substring(DataRow,36,36) AS BankName,
Substring(DataRow,72,36) AS BankAddress,
Substring(DataRow,108,20) AS City,
Substring(DataRow,128,2) AS State,
Substring(DataRow,130,5) + '-' + Substring(DataRow,135,4) AS Zipcode,
Substring(DataRow,139,3) + '-' + Substring(DataRow,142,3) + '-' +
Substring(DataRow,145,4) AS Phone,
Substring(DataRow,149,1) AS StatusCode,
Substring(DataRow,150,1) AS DataView,
Substring(DataRow,151,5) AS Filler
DateUpdated datetime AS DateUpdated
FROM ##Import
GO

Thanks,
KarenThe error is probably because you are missing a comma after "AS
Filler". In general, you should avoid creating permanent tables from
within stored procedures, as it makes it very hard to control your data
model correctly, and if the proc is run multiple times you may have
problems.

A common approach is to create a permanent staging table (instead of
using a temporary one as you are), and bulk load your files into that.
A stored proc can then do the final INSERT into ACHParticipants, after
making any other data changes that might be needed.

You might also want to consider loading the data using bcp.exe instead
of BULK INSERT - it can often be easier to deal with file names etc.
outside the database, in a batch file or a script of some other sort.

Simon|||Thank you, Simon. I have put the comma in, and I am still getting the
error. I am going to try setting up a staging table - thanks again for the
suggestion.

Thank you for the good advice.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1117006217.229228.129070@.g14g2000cwa.googlegr oups.com...
> The error is probably because you are missing a comma after "AS
> Filler". In general, you should avoid creating permanent tables from
> within stored procedures, as it makes it very hard to control your data
> model correctly, and if the proc is run multiple times you may have
> problems.
> A common approach is to create a permanent staging table (instead of
> using a temporary one as you are), and bulk load your files into that.
> A stored proc can then do the final INSERT into ACHParticipants, after
> making any other data changes that might be needed.
> You might also want to consider loading the data using bcp.exe instead
> of BULK INSERT - it can often be easier to deal with file names etc.
> outside the database, in a batch file or a script of some other sort.
> Simon

Thursday, March 22, 2012

Create stored procedure

Hi!
I have a problem. I would like to create a stored procedure from a script file. I must use inparameters as well. I'm using ms Access 2000.
Please help me!
Mike.I'm not sure that I understand what you mean by inparameters, but if you just read the script file into a string variable, then execute that string variable as a command, then you should be "good to go".

-PatP|||Why would you want to do backend application development from access..

I would imagine it would be severe hoop jumping...

Get the sql server client side tools...

unless we're really talking about MSDE...|||Originally posted by Brett Kaiser
Why would you want to do backend application development from access.. Why do some folks like leather undies? There is no accounting for taste.

I'd suggest using OSQL or better yet Visual Studio, but that's just me!

-PatP|||Originally posted by Pat Phelan
Why do some folks like leather undies?

I have no response|||We are currently (trying) to create an application with Access forms and SQL server database with stored procedures. If you can get out of it, please do. Certainly the part with the stored procedures parameters is a hell. I would also suggest MSDE with osql.

But, probably, you can't drop the Access, so if you can supply some more info and i'll look into it.sql

Sunday, March 11, 2012

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to view
the error message. Some protocols or warnings may finish after a few minutes,
especially when using performance indicators."
MarkusMarkus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few minutes,
> especially when using performance indicators."
> Markus|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
--
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus|||Nice Workaround :)
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - then
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
--
Markus PÃ¶hler
netpoint-edv gmbh
Germany
"chinn" wrote:
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:
> > Hi.
> >
> > I want to create a perfmon Logfile and store the Log Data into SQL Database
> > as offered option instead of CSV or Binary File. I want to use those Data
> > with Reporting services lateron. But I can't realize it. I have looked for
> > any articles - without success.
> > I have proceed as follows:
> >
> > 1. Created a database on test Server to retrieve the log data
> > 2. Created an SQL USER to use this DB
> > 3. Created an ODBC Connection to this SQL Database
> > 4. Created the Log File and defined the ODBC Connection as target for data
> >
> > After starting I am getting an error message saying sth like: "The Protocol
> > [name] or warnings have not been started. Refresh the Logfile list to view
> > the error message. Some protocols or warnings may finish after a few minutes,
> > especially when using performance indicators."
> >
> > Markus

Create PerfMon Log and store into SQL DB

I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - then
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
Markus P?hler
netpoint-edv gmbh
Germany
"chinn" wrote:
[vbcol=seagreen]
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:

Create PerfMon Log and store into SQL DB

I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - the
n
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
Markus P?hler
netpoint-edv gmbh
Germany
"chinn" wrote:
[vbcol=seagreen]
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:
>

Create PDF with SQL Reporting Services

I am getting the following error when trying to export a
PDF file in SQL Reporting Services. Does anyone know what
the problem might be?
Reporting Services Error
Exception of type
Microsoft.ReportingServices.ReportRendering.Report Rendering
Exception was thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.Report Rendering
Exception was thrown.
Cannot find font '?'.
Microsoft Reporting Services
There is a reporting services forum Microsoft.public.sqlserver.reportingsvcs
that can help you better with reporting services but it sounds like you do
not have a font installed on the server that is running reporting services.
Most server admins do not like applications installed willy nilly but you
may try installing MS Office (less Outlook) to get the font.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Bill D" <anonymous@.discussions.microsoft.com> wrote in message
news:fd9901c43e93$ddbf8f90$a001280a@.phx.gbl...
> I am getting the following error when trying to export a
> PDF file in SQL Reporting Services. Does anyone know what
> the problem might be?
> Reporting Services Error
> --
> Exception of type
> Microsoft.ReportingServices.ReportRendering.Report Rendering
> Exception was thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.Report Rendering
> Exception was thrown.
> Cannot find font '?'.
> --
> Microsoft Reporting Services
|||Why install an entire application to get a font?
How about finding out what font is needed and putting that font on the
server?
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OFbf87pPEHA.832@.TK2MSFTNGP09.phx.gbl...
> There is a reporting services forum
Microsoft.public.sqlserver.reportingsvcs
> that can help you better with reporting services but it sounds like you do
> not have a font installed on the server that is running reporting
services.
> Most server admins do not like applications installed willy nilly but you
> may try installing MS Office (less Outlook) to get the font.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "Bill D" <anonymous@.discussions.microsoft.com> wrote in message
> news:fd9901c43e93$ddbf8f90$a001280a@.phx.gbl...
>
|||That works until a developer decides they want another on that is thee and
the app bombs.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:%23ikZX2sPEHA.3524@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Why install an entire application to get a font?
> How about finding out what font is needed and putting that font on the
> server?
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:OFbf87pPEHA.832@.TK2MSFTNGP09.phx.gbl...
> Microsoft.public.sqlserver.reportingsvcs
do[vbcol=seagreen]
> services.
you
>

Create PDF with SQL Reporting Services

I am getting the following error when trying to export a
PDF file in SQL Reporting Services. Does anyone know what
the problem might be?
Reporting Services Error
---
--
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRendering
Exception was thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRendering
Exception was thrown.
Cannot find font '?'.
---
--
Microsoft Reporting ServicesThere is a reporting services forum Microsoft.public.sqlserver.reportingsvcs
that can help you better with reporting services but it sounds like you do
not have a font installed on the server that is running reporting services.
Most server admins do not like applications installed willy nilly but you
may try installing MS Office (less Outlook) to get the font.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Bill D" <anonymous@.discussions.microsoft.com> wrote in message
news:fd9901c43e93$ddbf8f90$a001280a@.phx.gbl...
> I am getting the following error when trying to export a
> PDF file in SQL Reporting Services. Does anyone know what
> the problem might be?
> Reporting Services Error
> ---
> --
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRendering
> Exception was thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRendering
> Exception was thrown.
> Cannot find font '?'.
> ---
> --
> Microsoft Reporting Services|||Why install an entire application to get a font?
How about finding out what font is needed and putting that font on the
server'
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OFbf87pPEHA.832@.TK2MSFTNGP09.phx.gbl...
> There is a reporting services forum
Microsoft.public.sqlserver.reportingsvcs
> that can help you better with reporting services but it sounds like you do
> not have a font installed on the server that is running reporting
services.
> Most server admins do not like applications installed willy nilly but you
> may try installing MS Office (less Outlook) to get the font.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "Bill D" <anonymous@.discussions.microsoft.com> wrote in message
> news:fd9901c43e93$ddbf8f90$a001280a@.phx.gbl...
> > I am getting the following error when trying to export a
> > PDF file in SQL Reporting Services. Does anyone know what
> > the problem might be?
> >
> > Reporting Services Error
> > ---
> > --
> >
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRendering
> > Exception was thrown. (rrRenderingError) Get Online Help
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRendering
> > Exception was thrown.
> > Cannot find font '?'.
> >
> > ---
> > --
> > Microsoft Reporting Services
>|||That works until a developer decides they want another on that is thee and
the app bombs.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:%23ikZX2sPEHA.3524@.TK2MSFTNGP10.phx.gbl...
> Why install an entire application to get a font?
> How about finding out what font is needed and putting that font on the
> server'
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:OFbf87pPEHA.832@.TK2MSFTNGP09.phx.gbl...
> > There is a reporting services forum
> Microsoft.public.sqlserver.reportingsvcs
> > that can help you better with reporting services but it sounds like you
do
> > not have a font installed on the server that is running reporting
> services.
> > Most server admins do not like applications installed willy nilly but
you
> > may try installing MS Office (less Outlook) to get the font.
> >
> > --
> > Andrew C. Madsen
> > Information Architect
> > Harley-Davidson Motor Company
> > "Bill D" <anonymous@.discussions.microsoft.com> wrote in message
> > news:fd9901c43e93$ddbf8f90$a001280a@.phx.gbl...
> > > I am getting the following error when trying to export a
> > > PDF file in SQL Reporting Services. Does anyone know what
> > > the problem might be?
> > >
> > > Reporting Services Error
> > > ---
> > > --
> > >
> > > Exception of type
> > > Microsoft.ReportingServices.ReportRendering.ReportRendering
> > > Exception was thrown. (rrRenderingError) Get Online Help
> > > Exception of type
> > > Microsoft.ReportingServices.ReportRendering.ReportRendering
> > > Exception was thrown.
> > > Cannot find font '?'.
> > >
> > > ---
> > > --
> > > Microsoft Reporting Services
> >
> >
>

Create PDF with SQL Reporting Services

Create PDF File

I have an application in Power Builder 6.5 and I use SQL Server 2000.
How Can I create pdf files?
I want to create pdf files using Power or SQL.
Is it possible?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I guess you cannot create PDF files using SQL Server 2000. I don't know about
Powerbuilder. Adobe has a PDF file creation services. Please take a look at
http://www.adobe.com/products/server/docgen.html
Did you mean storing PDF files in SQL Server 2000?
"Karla Gomez" wrote:

> I have an application in Power Builder 6.5 and I use SQL Server 2000.
> How Can I create pdf files?
> I want to create pdf files using Power or SQL.
> Is it possible?
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Vikas,
While you may not be able to directly create Adobe PDF files directly with
T-SQL (and I'm still researching this), you can use a 3rd party tool and a
T-SQL query and generate a PDF file as the output. Specificly, see
ceTesoftware at http://www.dynamicpdf.com/ and their product DynamicPDF
ReportWriter and code example at:
http://www.dynamicpdf.com/Products/R...r/Examples.csp
The basic program flow for a script using DynamicPDF? ReportWriter is as
follows:
Create a report object.
Define the database connection and query.
Add report elements (i.e. RecordBox) to the header, section header, body,
section footer and/or footer.
Generate (Draw) the PDF report.
ASP Example Script: 
<%
Dim objReport
Set objReport = Server.CreateObject("DPDF_RptWtr.Report")
With objReport.Command
'An ADO Connection String (Northwind DB in this example)
.ActiveConnection = "File Name = " & Server.MapPath("Northwind.udl")
'A SQL Query
.CommandText = "SELECT CompanyName FROM Customers"
End With
With objReport.Header
.Height = 24
.AddLabel "Header goes here", 0, 0, 504, 12
End With
With objReport.Body
.Height = 12
.AddRecordBox "CompanyName", 0, 0, 504, 12
End With
objReport.DrawToASP
Set objReport = Nothing
%>
And the following link demostrates a dynamiclly built PDF file:
http://www.dynamicpdf.com/Products/R...t_Verdana.aspx
I'm sure there are other such products as SQL Server 2000 Reporting Services
supports writing the output to the PDF format.
Regards,
John
"Vikas Ahuja [MSFT]" wrote:
[vbcol=seagreen]
> I guess you cannot create PDF files using SQL Server 2000. I don't know about
> Powerbuilder. Adobe has a PDF file creation services. Please take a look at
> http://www.adobe.com/products/server/docgen.html
> Did you mean storing PDF files in SQL Server 2000?
> "Karla Gomez" wrote:
|||Karla & Vikas,
I knew I had seen how to do this somewhere...
Creating a PDF from a Stored Procedure
http://www.sqlservercentral.com/colu...dprocedure.asp
Regards,
John
"John Kane" <JohnKane@.discussions.microsoft.com> wrote in message
news:DC2A1EB6-DEF1-47DA-919C-22A7336C5638@.microsoft.com...
> Vikas,
> While you may not be able to directly create Adobe PDF files directly with
> T-SQL (and I'm still researching this), you can use a 3rd party tool and a
> T-SQL query and generate a PDF file as the output. Specificly, see
> ceTesoftware at http://www.dynamicpdf.com/ and their product DynamicPDF
> ReportWriter and code example at:
> http://www.dynamicpdf.com/Products/R...r/Examples.csp
> The basic program flow for a script using DynamicPDFT ReportWriter is as
> follows:
> Create a report object.
> Define the database connection and query.
> Add report elements (i.e. RecordBox) to the header, section header, body,
> section footer and/or footer.
> Generate (Draw) the PDF report.
> ASP Example Script: 
> <%
> Dim objReport
> Set objReport = Server.CreateObject("DPDF_RptWtr.Report")
> With objReport.Command
> 'An ADO Connection String (Northwind DB in this example)
> .ActiveConnection = "File Name = " & Server.MapPath("Northwind.udl")
> 'A SQL Query
> .CommandText = "SELECT CompanyName FROM Customers"
> End With
> With objReport.Header
> .Height = 24
> .AddLabel "Header goes here", 0, 0, 504, 12
> End With
> With objReport.Body
> .Height = 12
> .AddRecordBox "CompanyName", 0, 0, 504, 12
> End With
> objReport.DrawToASP
> Set objReport = Nothing
> %>
> And the following link demostrates a dynamiclly built PDF file:
>
http://www.dynamicpdf.com/Products/R...t_Verdana.aspx
> I'm sure there are other such products as SQL Server 2000 Reporting
Services[vbcol=seagreen]
> supports writing the output to the PDF format.
> Regards,
> John
>
>
>
> "Vikas Ahuja [MSFT]" wrote:
about[vbcol=seagreen]
at[vbcol=seagreen]
|||hello Karla,
did you find a solution to this problem? Did you use the acrobat writer or stored procedure? how difficult is it to create pdf's on the fly?
bev
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi Bev,
Yes, I did find a solution to this problem. You can use T-SQL and therefore
stored procedures via the methods documented at:
Creating a PDF from a Stored Procedure
http://www.sqlservercentral.com/colu...dprocedure.asp
Regards,
John
"bev harris" wrote:

> hello Karla,
> did you find a solution to this problem? Did you use the acrobat writer or stored procedure? how difficult is it to create pdf's on the fly?
> bev
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>
|||Karla
This company provide COM and .NET components.
http://websupergoo.com/products.htm
I have used version 3 which was a COM component with .NET 1.0 and it worked
very well. Tickets generated on www.easyCinema.com use this technology.
I have never used this product...
http://www.tallcomponents.com/
"Karla Gomez" wrote:

> I have an application in Power Builder 6.5 and I use SQL Server 2000.
> How Can I create pdf files?
> I want to create pdf files using Power or SQL.
> Is it possible?
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Thursday, March 8, 2012

Create objects from a configuration file

Anyone think this is a good idea?
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=42c20106-de27-4fb8-88e4-1bf13598c4e1

-Jamie

Hello Jamie,

How far would you go?

If I choose to export a whole package worth of configurations are you saying

that I should be able to take this to a blank package, point it at the config

file and it would at "Just Before Runtime" create everything for me?

You can imagine the hit associated with this, validation would be costly.

What about Connection Managers? sensitive data?

How would you design workflow in a configuration file?

Allan

> Anyone think this is a good idea?

> http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedba

> ck id=42c20106-de27-4fb8-88e4-1bf13598c4e1

> -Jamie

|||Nope, I'm not saying that at all. This isn't a runtime thing - just an aid to developer productivity.

e.g. All my packages will be inserting into the same database. I have a configuration set up for a connection manager for that database. Whenever I start to build a new package I can just point at an existing configuration and say "create the object that this configuration references in my current package". Then the developer continues as normal building his/her package with whatever functionality it requires.

-Jamie|||Would this approach be extended to putting standard code for error handling, logging, event handling, etc...?|||

Not really. This is talking about something alot more specific than that.

Create new database from backup file?

Hi all,
I have a ploblem...
i have ful backup file, diffrential backup file, transaction log backup file.
i want to create a new database form backup file.
i do it follow :
Using Enterprise manager
1. Rrestore database from full backup file. it is ok.
2. affter that i restore different database differential backup file. it does not work, the error is :' Theproceding operationdid not specify WITH NORECOVERY or WITH STANDBY. Resatr the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. RESTORE DATABASE is terminating abnormally'
please show me,
nest regard.If you will be applying a differential backup or a log backup you need to restore the full backup in a recoverable mode. See BOL for syntax

HTH

Wednesday, March 7, 2012

Create My Own Log (for functionality)

I'm calling stored procedures in a DTS package. The log file only shows that of DTS. In stored procedures I use "Print" to print out error messages the program encouters. I can see the errors when I execute a procedure in Query Analyzer, but they go nowhere when I run a DTS package.

How do I generate a myfunctionality.log with these lines?

Thanks,

LiliHi,

The best way to add Own Log functionality , is to create a log table and use insert statement to log message instead of print. The advantage will be that log will be available for future reference whereas filelog will be over written every time you run package.

best of luck

create multiple stored proc thru script

Hi,

I am using an MSDE database.
During installation, i intend to use a a script file which creates all tables and stored procedures using ADODB in InstallShield.
But i get the error
"CREATE PROCEDURE must be the first statement in a batch update". Moreover "GO" is not recognized by ADODB.
How can i create the stored procedures ?

regards,
henryCall osql to execute the script?|||Hi Brett,

I am using osql to execute the script successfully. But i am still wondering how to do this through ADODB.

Thanks and regards,
henry

Create mdf file?

Scenario:
I have a fully licensed SQL server 2005 database that my production
application uses. I have several contract sales people who take my web
application, visit prospective clients and perform a demonstration of our
product (by connecting to the internet to access the database).
I would like to have the application run completely on the laptops my sales
team uses, so I am wondering how to move my existing SQL 2005 database to a
..mdf file that can be places in the app_data folder so the application will
not have to access the Internet for the demonstration).
I don't know what the steps are to do this, or how to create the conection
string to point to a local database in the app_data folder.
Thanks in advance!
Brian
Brian,
Of course, the laptop will need to have SQL Server installed on it. If your
database is not too big (<4 GB) you can use SQL Server Express. BACKUP your
database and RESTORE the backup to the laptops. (This will place an MDF and
an LDF on the laptop. You need both.)
Since logins will not come over with the restore, you will also need to set
up a login for the person demonstrating your product. But that should not
be a problem to do.
I would suggest that you do this once on a single laptop. Once you have it
just the way you want it, take backups of all the databases on the laptop.
Then for the other laptops, install SQL Server Express and then restore all
the backups to each traveling laptop.
RLF
..
"AutoTrackerPlus" <brian.cesafsky@.autotrackerplus.com> wrote in message
news:Oz5QIkTzHHA.4652@.TK2MSFTNGP05.phx.gbl...
> Scenario:
> I have a fully licensed SQL server 2005 database that my production
> application uses. I have several contract sales people who take my web
> application, visit prospective clients and perform a demonstration of our
> product (by connecting to the internet to access the database).
> I would like to have the application run completely on the laptops my
> sales team uses, so I am wondering how to move my existing SQL 2005
> database to a .mdf file that can be places in the app_data folder so the
> application will not have to access the Internet for the demonstration).
> I don't know what the steps are to do this, or how to create the conection
> string to point to a local database in the app_data folder.
> Thanks in advance!
>
> Brian
>
|||It is not as simple as including the data files. Your application
does not touch the files, and would not know what to do with them if
it did. Your application talks to the SQL Server service running on
the server, and SQL Server deals with the database files.
To run the application on the laptops you will need SQL Server
installed and running on the laptops. In this case you can probably
get away with SQL Express, which is free. That assumes the database
is no more than 4GB, the SQL Express limit. You will probably want
reasonably robust laptops, particularly when it comes to memory where
1GB is probably a minimum. I suggest thorough testing to confirm that
performance is good enough to show customers
Roy Harvey
Beacon Falls, CT
On Mon, 23 Jul 2007 09:51:57 -0500, "AutoTrackerPlus"
<brian.cesafsky@.autotrackerplus.com> wrote:

>Scenario:
>I have a fully licensed SQL server 2005 database that my production
>application uses. I have several contract sales people who take my web
>application, visit prospective clients and perform a demonstration of our
>product (by connecting to the internet to access the database).
>I would like to have the application run completely on the laptops my sales
>team uses, so I am wondering how to move my existing SQL 2005 database to a
>.mdf file that can be places in the app_data folder so the application will
>not have to access the Internet for the demonstration).
>I don't know what the steps are to do this, or how to create the conection
>string to point to a local database in the app_data folder.
>Thanks in advance!
>
>Brian
>

Create mdf file?

Scenario:
I have a fully licensed SQL server 2005 database that my production
application uses. I have several contract sales people who take my web
application, visit prospective clients and perform a demonstration of our
product (by connecting to the internet to access the database).
I would like to have the application run completely on the laptops my sales
team uses, so I am wondering how to move my existing SQL 2005 database to a
.mdf file that can be places in the app_data folder so the application will
not have to access the Internet for the demonstration).
I don't know what the steps are to do this, or how to create the conection
string to point to a local database in the app_data folder.
Thanks in advance!
BrianBrian,
Of course, the laptop will need to have SQL Server installed on it. If your
database is not too big (<4 GB) you can use SQL Server Express. BACKUP your
database and RESTORE the backup to the laptops. (This will place an MDF and
an LDF on the laptop. You need both.)
Since logins will not come over with the restore, you will also need to set
up a login for the person demonstrating your product. But that should not
be a problem to do.
I would suggest that you do this once on a single laptop. Once you have it
just the way you want it, take backups of all the databases on the laptop.
Then for the other laptops, install SQL Server Express and then restore all
the backups to each traveling laptop.
RLF
.
"AutoTrackerPlus" <brian.cesafsky@.autotrackerplus.com> wrote in message
news:Oz5QIkTzHHA.4652@.TK2MSFTNGP05.phx.gbl...
> Scenario:
> I have a fully licensed SQL server 2005 database that my production
> application uses. I have several contract sales people who take my web
> application, visit prospective clients and perform a demonstration of our
> product (by connecting to the internet to access the database).
> I would like to have the application run completely on the laptops my
> sales team uses, so I am wondering how to move my existing SQL 2005
> database to a .mdf file that can be places in the app_data folder so the
> application will not have to access the Internet for the demonstration).
> I don't know what the steps are to do this, or how to create the conection
> string to point to a local database in the app_data folder.
> Thanks in advance!
>
> Brian
>|||It is not as simple as including the data files. Your application
does not touch the files, and would not know what to do with them if
it did. Your application talks to the SQL Server service running on
the server, and SQL Server deals with the database files.
To run the application on the laptops you will need SQL Server
installed and running on the laptops. In this case you can probably
get away with SQL Express, which is free. That assumes the database
is no more than 4GB, the SQL Express limit. You will probably want
reasonably robust laptops, particularly when it comes to memory where
1GB is probably a minimum. I suggest thorough testing to confirm that
performance is good enough to show customers
Roy Harvey
Beacon Falls, CT
On Mon, 23 Jul 2007 09:51:57 -0500, "AutoTrackerPlus"
<brian.cesafsky@.autotrackerplus.com> wrote:

>Scenario:
>I have a fully licensed SQL server 2005 database that my production
>application uses. I have several contract sales people who take my web
>application, visit prospective clients and perform a demonstration of our
>product (by connecting to the internet to access the database).
>I would like to have the application run completely on the laptops my sales
>team uses, so I am wondering how to move my existing SQL 2005 database to a
>.mdf file that can be places in the app_data folder so the application will
>not have to access the Internet for the demonstration).
>I don't know what the steps are to do this, or how to create the conection
>string to point to a local database in the app_data folder.
>Thanks in advance!
>
>Brian
>

Thursday, March 29, 2012

Create table,fields come from csv file

CREATE TABLE scripts

Tuesday, March 27, 2012

create table question

create table from xml schema

Create table from text file, extract data, create new table from extracted data.

Create table from Text

Thursday, March 22, 2012

Create stored procedure

Sunday, March 11, 2012

Create PerfMon Log and store into SQL DB

Create PerfMon Log and store into SQL DB

Create PerfMon Log and store into SQL DB

Create PDF with SQL Reporting Services

Create PDF with SQL Reporting Services

Create PDF with SQL Reporting Services

Create PDF File

Thursday, March 8, 2012

Create objects from a configuration file

Create new database from backup file?

Wednesday, March 7, 2012

Create My Own Log (for functionality)

create multiple stored proc thru script

Create mdf file?

Create mdf file?

Sql Create error

Blog Archive

About Me