Extract data from SQL Server 2005 by SMO or DMO

I am running an old script generator using SQL-DMO. Even on SQL Server 2005 it is working fine, but the new features like xml data type are not supported. So I switched to SMO. At the first view it looks pretty cool and easy. I changed the properties in the following source a thousand times but it doesn’t script any data to the file. Is it a bug or a stupid misunderstanding?

Transfer t = new Transfer(db);

t.CopyAllObjects = false;

t.CopyAllTables = true;

t.CopyData = true;

//t.Options.WithDependencies = true;

t.Options.ContinueScriptingOnError = true;

t.DestinationServer = "PC-E221\\SQLEXPRESS";

t.DestinationDatabase = "TestAgent";

t.DestinationLoginSecure = true;

t.CreateTargetDatabase = true;

t.Options.AllowSystemObjects = false;

t.Options.FileName = "testFile.sql";

t.Options.IncludeDatabaseContext = true;

t.Options.ToFileOnly = true;

Best regards

Wolfgang

Smo is a tool for generating scripts / manitaining the database not scripting the data out.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

thanks for your response.

1. If it is so, what does ".CopyData= true" mean, if it doesn't copy data?

2. How can I copy data, if DMO doesn't work either? As I said before, DMO doesn't copy xml data types.

br

Wolfgang

|||1. That is related to the TransferData method which will use DTS behind the scenes to transfer the data (read this somewhere sometime).

2. You could use a scripting utility like this here: http://vyaskn.tripod.com/code.htm to do the job. i don′t know if this is capable of using XMlL txypes, but its worth a try, because it can be really quick tested.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Extra Characters appended to entry

I'm encountering a strange problem in all the applications I'm working on and am totally dumbfounded as to why it's occuring:
From a standard web form I'm inserting a record using a stored procedure. (I'm writing this to a SQL 2000 db - where the column types and variables are all consistant) No matter what I do, the columns are padded with extra characters maxing out the field length (if it's nchar or nvarchar or char) after insert or update. I've tried Trim - ing the field.text values that I'm feeding to the @.variables used in my stored procedure. I've even RTRIM() - ed the @.variables within the stored procedure. No matter what I do I get extra spaces padding the end of the intended column input. Ideas anyone?
Thanks in advance.
- AbeDepending on your extra characters, trimmimg won't solve your problem if they are not whitespaces.
Could you supply more info and post your ASP and T-Sql code?
|||

Thanks. I'm pretty sure it's spaces because if I run a query "SELECT Education FROM tblCV WHERE (Education LIKE N'% %')" I get all records returned except the ones that I manually stripped out the trailing spaces.

privatevoid InsertCV()
{

SqlConnection conn =new SqlConnection(connectString);

SqlCommand cmd =new SqlCommand("sp_InsertCV",conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@.AAASS_ID",SqlDbType.NVarChar,5);

cmd.Parameters.Add("@.Education",SqlDbType.NChar,700);

cmd.Parameters.Add("@.Experience",SqlDbType.NChar,700);

cmd.Parameters.Add("@.Publication",SqlDbType.NChar,700);

cmd.Parameters.Add("@.ConferencePapers",SqlDbType.NChar,700);

cmd.Parameters.Add("@.OrganizationalMembership",SqlDbType.NChar,700);

cmd.Parameters.Add("@.EnteredBy",SqlDbType.NVarChar,10);

cmd.Parameters.Add("@.UpdatedBy",SqlDbType.NVarChar,10);

cmd.Parameters["@.AAASS_ID"].Value = _AAASSID;

cmd.Parameters["@.Education"].Value =this.education.Text;

cmd.Parameters["@.Experience"].Value =this.experience.Text;

cmd.Parameters["@.Publication"].Value =this.publications.Text;

cmd.Parameters["@.ConferencePapers"].Value =this.conferencePapers.Text;

cmd.Parameters["@.OrganizationalMembership"].Value =this.organizationalMembership.Text;

cmd.Parameters["@.EnteredBy"].Value = _AAASSID;

cmd.Parameters["@.UpdatedBy"].Value = _AAASSID;

conn.Open();

cmd.ExecuteNonQuery();

cmd.Dispose();

conn.Close();

}
CREATE PROCEDURE sp_InsertCV
(
@.AAASS_ID AS nvarchar(5),
@.Education AS nchar(700),
@.Experience AS nchar(700),
@.Publication AS nchar(700),
@.ConferencePapers AS nchar(700),
@.OrganizationalMembership AS nchar(700),
@.EnteredBy AS char(10),
@.UpdatedBy AS char(10)
)

AS
INSERT INTO [AAASSSQLData].[dbo].[tblCV]
(
[AAASS_ID], [Education], [Experience], [Publication], [ConferencePapers], [OrganizationalMembership], [DateEntered], [EnteredBy],
[DateUpdated], [UpdatedBy]
)
VALUES(
@.AAASS_ID,RTRIM(@.Education),RTRIM(@.Experience),
RTRIM(@.Publication),
RTRIM(@.ConferencePapers),
RTRIM(@.OrganizationalMembership),
GetDate(),
RTRIM(@.EnteredBy),
GetDate(),
RTRIM(@.UpdatedBy)
)
GO
tblCV
3 AAASS_ID nvarchar 5 0
0 Education nchar 700 1
0 Experience nchar 700 1
0 Publication nchar 700 1
0 ConferencePapers nchar 700 1
0 OrganizationalMembership nchar 700 1
0 DateEntered datetime 8 1
0 EnteredBy char 10 1
0 DateUpdated datetime 8 1
0 UpdatedBy char 10 1
Column contents: "SUNY Binghamton, B.A., Psychology, 1982
Pace U., MBA, Management Information Systems, 1986 "
Thank you for taking a look at this.
- Abe

|||Use varchar or nvarchar fields and they won't be padded. Char and nchar datatypes pad the data with spaces out to the field length.|||Thanks so much for the feedback. I'll give that a try.
Best,
- AbeR

Expressions not working if Matrix cell is not populated by dataset

Hi
I'm using the following expression for the BackGroundColor in a matrix
detail cell:
=IIF(Fields!ID_Quarter.Value = "Cumulative", "Gainsboro","Transparent")
This is checking the value of another cell before setting the
BackGroundColor value.
I'm also using an expression for the bottom border style:
=IIF(Fields!ID_Quarter.Value = "Cumulative", "Solid","Dotted")
This is checking the value of the same cell that the BackGroundColor
expression checks.
These expression ONLY work if the cell has been populated from the dataset
(in my matrix some cells don't have a value - this is expected).
My Cell has three expressions all up, the remaining expression checks it's
own value for nothing and then put's a "0" in the cell if true.
=IIF(Fields!Deep_SSI.Value = Nothing,"0",Sum(Fields!Deep_SSI.Value))
This expression always works.
Is there a problem with using more than one expression on a cell' I think
not because they all work fine if the cell is populated.
Any thoughts?
SimonBHello...
RE: Expressions not working if Matrix cell is not populated by dataset!
Has anyone got any clues on this problem?
Simon
"simonb" wrote:
> Hi
> I'm using the following expression for the BackGroundColor in a matrix
> detail cell:
> =IIF(Fields!ID_Quarter.Value = "Cumulative", "Gainsboro","Transparent")
> This is checking the value of another cell before setting the
> BackGroundColor value.
> I'm also using an expression for the bottom border style:
> =IIF(Fields!ID_Quarter.Value = "Cumulative", "Solid","Dotted")
> This is checking the value of the same cell that the BackGroundColor
> expression checks.
> These expression ONLY work if the cell has been populated from the dataset
> (in my matrix some cells don't have a value - this is expected).
> My Cell has three expressions all up, the remaining expression checks it's
> own value for nothing and then put's a "0" in the cell if true.
> =IIF(Fields!Deep_SSI.Value = Nothing,"0",Sum(Fields!Deep_SSI.Value))
> This expression always works.
> Is there a problem with using more than one expression on a cell' I think
> not because they all work fine if the cell is populated.
> Any thoughts?
> SimonB

Expression Question

I am working on an aging report and am trying to sum one of the columns that
is the 30-60 Day bucket. I have the expression written as the following;
"=sum(fields!extcost.value) where (fields!interval.value>30 and
Fields!Interval.Value<=60)"
What logic should I be using?
Thank you,
Ryan2 ways to deal with this. In report cell:
=Sum(IIf(Fields!Interval.Value=>30 AND
Fields!Interval.value<=60,Fields!extcost.value,0))
However, I normally try to do this in the SQL with a CASE statement for each
bucket.
SELECT X,Y,Z,Sum(CASE WHEN Interval BETWEEN 30 and 60 THEN extcost ELSE 0
END As Bucket2,A,B,C...
FROM....
Michael C
"Ryan Mcbee" wrote:
> I am working on an aging report and am trying to sum one of the columns that
> is the 30-60 Day bucket. I have the expression written as the following;
> "=sum(fields!extcost.value) where (fields!interval.value>30 and
> Fields!Interval.Value<=60)"
> What logic should I be using?
> Thank you,
> Ryan
>

Express Reporting Services

I've installed SQLEXPR_ADV.EXE and SQLEXPR_TOOLKIT.EXE. Was able to reattached my own DB's and everything seems to be working fine, but....

I used reporting services extensively in SQL 2000 and would love to play with the express version. My problem is, I don't know how to access. I tried opening ReportBuilder.exe but get an error indicating "Report Builder and Report Models are not supported in this version of Reporting Services"...

I have XP Pro and there is a Reports and ReportServer under the default web sites. Do you have to have Visual Studio to utilize?

Any help would be appreciated...

Thank you...

Danno

You need to use the Business Intellegence Developer's Studio, which is installed with the SQL Express Toolkit to design your reports. It should be listed on the Start menu with the other SQL tools.

Mike

|||

Thanks Mike, there is a line item called "SQL Server Business Intelligence Development Studio". When I try to access (double-click) I get the message "Windows is searching for devenv.ext. To locate the file yourself, click browse". I did a search on my entire hard drive for devenv.exe, but it is no where to be found. I tried reinstalling the Toolkit, but get a message that I didn't change anything so nothing was installed. I made sure the BI option was chosen.

I'm assuming that the BI Deve Studio is part of SQLEXPR_TOOLKIT.EXE, is this correct. Any ideas on what I can do to get the BI Dev Studio installed?

Thank you,

Dan

|||

We are facing exactly same problem as Dan has mensioned above. We have tried installing SQLexpress with advanced feature and the tool kit on three different machines and behaviour is same. We are not been able to use Reporting services with SQL2005express. Please help.

Prashant

exporting/importing tables

Hi there.
What is the best way to export/import a SQL table from one SQL server on a
computer to another computer.
I am a contractor, working on my laptop, with a company, that will not allow
me to hook my laptop up to their network.
I keep a 'copy' of what is on their SQL server, and am constantly updating,
back and forth between my laptop and their server.
Because I didn't know a better way, I have been exporting to an access file
on disk, then importing back into SQL on the other computer.
Generally this works okay, but lately I have been running into some problems
with datatypes being converted etc.
I'm assuming there is a much better way to do this.
Am I right?
Thanks in advance!
amberSimplest is to use a SQL Backup file... If this is production system, then
there are probably backup files already been done regularly that you should
be able to get acces to... If it's a development dataabse, then just do the
backup yourself, to a disk file. on a network UNC of your choice, where you
can copy the file to whatever medium you are using to get it to your laptop,
then just "restore" the backup to the SQL Server Database on your laptop.
Two gotchas to be aware of, When you select Restore in the SQL Enterprise
Mgr GUI, go to the options tab, and check the "Force Restore over existing
database" option checkbox, and type over the file locations on the far right
column, with the physical file locations on your laptop.
"amber" wrote:

> Hi there.
> What is the best way to export/import a SQL table from one SQL server on a
> computer to another computer.
> I am a contractor, working on my laptop, with a company, that will not all
ow
> me to hook my laptop up to their network.
> I keep a 'copy' of what is on their SQL server, and am constantly updating
,
> back and forth between my laptop and their server.
> Because I didn't know a better way, I have been exporting to an access fil
e
> on disk, then importing back into SQL on the other computer.
> Generally this works okay, but lately I have been running into some proble
ms
> with datatypes being converted etc.
> I'm assuming there is a much better way to do this.
> Am I right?
> Thanks in advance!
> amber
>|||If you are talking about data, then take a look to BCP utility or DTS, to
create csv files.
AMB
"amber" wrote:

> Hi there.
> What is the best way to export/import a SQL table from one SQL server on a
> computer to another computer.
> I am a contractor, working on my laptop, with a company, that will not all
ow
> me to hook my laptop up to their network.
> I keep a 'copy' of what is on their SQL server, and am constantly updating
,
> back and forth between my laptop and their server.
> Because I didn't know a better way, I have been exporting to an access fil
e
> on disk, then importing back into SQL on the other computer.
> Generally this works okay, but lately I have been running into some proble
ms
> with datatypes being converted etc.
> I'm assuming there is a much better way to do this.
> Am I right?
> Thanks in advance!
> amber
>|||You can script out the table. Create insert statements for the data using th
e
following script:
http://vyaskn.tripod.com/code.htm#inserts
Then run the table create script and data insert script on your destination
computer.
HTH
ZULFIQAR SYED
"Alejandro Mesa" wrote:
> If you are talking about data, then take a look to BCP utility or DTS, to
> create csv files.
>
> AMB
> "amber" wrote:
>|||Script your data with www.sqlscripter.com
There's an option like "Insert new, update existing" records ...
Thomas
"amber" wrote:

> Hi there.
> What is the best way to export/import a SQL table from one SQL server on a
> computer to another computer.
> I am a contractor, working on my laptop, with a company, that will not all
ow
> me to hook my laptop up to their network.
> I keep a 'copy' of what is on their SQL server, and am constantly updating
,
> back and forth between my laptop and their server.
> Because I didn't know a better way, I have been exporting to an access fil
e
> on disk, then importing back into SQL on the other computer.
> Generally this works okay, but lately I have been running into some proble
ms
> with datatypes being converted etc.
> I'm assuming there is a much better way to do this.
> Am I right?
> Thanks in advance!
> amber
>

Exporting the Report into Excel : Timeout Problem

Hi,

I have installed the SSRS 2005 beta version, when I am exporting the report into excel format it is working if my report has 1 to 3 pages. But the same report contains more than 10 pages then I will be getting the TimeOut error.

The operation has timed out

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Net.WebException: The operation has timed out

Any suggestions are welcome.

Thanks in Advance

Hello,

Can you export to other formats (HTML, PDF) without problems? How much information do you actually have on a page ? How many columns and rows in your excel spreadsheet?

Thank you,

Nico

|||

Hi,

I made the same experience, when exporting to XLS I got a timeout error message. This also happens when exporting to pdf, but not when exporting to csv, html. This only happens with larger reports (In our case approx. 700 pages). We would need some help here, too.

Thx

Michael

Exporting the Report into Excel : Timeout Problem

Hi,

I have installed the SSRS 2005 beta version, when I am exporting the report into excel format it is working if my report has 1 to 3 pages. But the same report contains more than 10 pages then I will be getting the TimeOut error.

The operation has timed out

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Net.WebException: The operation has timed out

Any suggestions are welcome.

Thanks in Advance

Hello,

Can you export to other formats (HTML, PDF) without problems? How much information do you actually have on a page ? How many columns and rows in your excel spreadsheet?

Thank you,

Nico

|||

Hi,

I made the same experience, when exporting to XLS I got a timeout error message. This also happens when exporting to pdf, but not when exporting to csv, html. This only happens with larger reports (In our case approx. 700 pages). We would need some help here, too.

Thx

Michael

|||Is this answered already?I almost have te same problem.I am using a custom code.When i am exporting my report to PDF, the operation time out.But why not when exporting to Excel or HTML.