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

Extract and compare hour

Hi all:
As I can extract the hour values and minute of a field of type datetime to compare it with the values of a field of type smalldatetime of another table
Thanks.:confused:Yes. Look at DatePart in BOL.

Extending RDL & rendering

How can I add new elements to an RDL report definition and then render those
elements? For example, I'd like to add a new type of report (i.e. not a
table, matrix, list, or chart), and write the code to generate the rendering
of that particular type of element.
A further complication: the implementation of this new element would also
need to do the database queries, since it does not fit into the Reporting
Services model (i.e. getting a flattened dataset back)
Is this possible within Reporting Services?You can extend with a new renderer but you have to render the whole thing
yourself, which is not what you want. There is no way that I know of to
extend by adding new elements. You can definitely create a data processing
extension, that is not a problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:AB288ABC-0055-47FC-A9C8-5AA151DC1791@.microsoft.com...
> How can I add new elements to an RDL report definition and then render
> those
> elements? For example, I'd like to add a new type of report (i.e. not a
> table, matrix, list, or chart), and write the code to generate the
> rendering
> of that particular type of element.
> A further complication: the implementation of this new element would also
> need to do the database queries, since it does not fit into the Reporting
> Services model (i.e. getting a flattened dataset back)
> Is this possible within Reporting Services?

Extended Stored Procedures 7.0 - 2000

I have a problem with passing parameters into an extended stored procedure in sql 2000 that was not present in sql 7.0.

I pass in a parameter of type varchar(8000) but sql 2000 truncates this value to 255 characters.

I was using srv_paramdata(sproc,1) for example to get the pertinent data. Microsoft says that srv_paramdata has been superceded by srv_paraminfo, but this function is crashing my dll when I run it....

BYTE bType;
unsigned long cbMaxLen;
unsigned long cbActualLen;
BOOL fNull;
BYTE data;

// Use srv_paraminfo to get data type and length information.
srv_paraminfo(sproc, 2, &bType, &cbMaxLen, &cbActualLen,&data, &fNull);

Any ideas?

This worked in sql 7.0.

wsprintf(string,"%s",(const char*) srv_paramdata(sproc,1));

Umm help appreciated.

WintermuteUmm actual guys... thanks to anyone who has looked to help me out, but I think I have (*this).sorted->muchos[gracias++];

umm in keeping with Open Source Software paradigms and stuff if anyone is curious

PBYTE*data;
BYTE type;
unsigned long Maxlen;
unsigned long Reallen;
int nParams;
BOOL boolnull;
char head_descriptor[24];
FILE*file;

nParams=srv_rpcparams(sproc);


data=new PBYTE[1];
if(data==NULL)
{
ServerErrorMsg(sproc,"Was unable to allocate the requisite memory for this data operation");
return -1;
};
memset(data,0,nParams*sizeof(PBYTE));


srv_paraminfo(sproc,2,&type,&Maxlen,&Reallen,NULL,&boolnull);

sprintf(head_descriptor,"Parameter 2: Input");
srv_describe(sproc,2,head_descriptor,SRV_NULLTERM, type,Reallen,type,Reallen,NULL);

if(boolnull==0)
{
data[0]=(unsigned char*)malloc(Reallen);
if(data[0]==NULL)
{
ServerErrorMsg(sproc,"Unable to allocate memory for this variable!");
delete data;
return -1;
};

srv_paraminfo(sproc,2,&type,&Maxlen,&Reallen,data[0],&boolnull);
}
else
{
ServerErrorMsg(sproc,"There Seems to be no data present for parameter 2");
return -1;
};

file=fopen("C:/bod.txt","a+");
if(file==NULL)
{
ServerErrorMsg(sproc,"Unable to access the specified filename for file input, Please check the filename and try again!");
return -1;
};

fwrite(data[0],Reallen,1,file);
fclose(file);


return 0;
};

Regards
Wintermute.sql

Ext Locks to tempdb db

Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
EXT type of lock in the tempdb database . The sproc does not seem to be
doing anything with temp tables or table variables,etc.. Theres some
aggregate functions only in a simple select statement
Any idea why the EXT type of lock ? Thanks
Aggregations often use tempdb and the nolock would have no bearing on that
aspect of it.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OTWI8WZ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
> EXT type of lock in the tempdb database . The sproc does not seem to be
> doing anything with temp tables or table variables,etc.. Theres some
> aggregate functions only in a simple select statement
> Any idea why the EXT type of lock ? Thanks
>
|||In addition to Andrew's answer:
These tempdb EXT locks are used internally by SQL Server to allocate or
deallocate pages and extents. The pages and extents are used as temporary
storage to process the query.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eNAu0Wa3EHA.924@.TK2MSFTNGP14.phx.gbl...
> Aggregations often use tempdb and the nolock would have no bearing on that
> aspect of it.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OTWI8WZ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
>
|||Hi Hassan,
I highly recommend reviewing this article
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default...b;en-us;328551
In my opinion, every server should implement the following suggestions
on how to reduce tempdb contention.
Increase the Number of Tempdb Data Files with Equal Sizing
If the data file size of tempdb is 5 GB, and the Log file size is 5 GB,
the recommendation is to increase the single datafile to 10 (each of 500
MB to maintain equal sizing), and leave the log file as is. Having the
different data files on separate disks would be good; however, this is
not required and they can co-exist on the same disk.
The optimal number of tempdb data files depends on the degree of
contention seen in tempdb. As a starting point, you can configure the
tempdb to be at least equal to the number of processors assigned for SQL
Server. For higher end systems (for example, 16 or 32 proc), the
starting number could be 10. If the contention is not reduced, you may
have to increase the number of data files more.
The equal sizing of data files is critical because the proportional fill
algorithm is based on the size of the files. If data files are created
with unequal sizes, the proportional fill algorithm tries to use the
largest file more for GAM allocations instead of spreading the
allocations between all the files, thereby defeating the purpose of
creating multiple data files.
The auto-grow of tempdb data files can also interfere with the
proportional fill algorithm. Therefore, it may be a good idea to turn
off the auto-grow feature for the tempdb data files. If the auto-grow
option is turned off, you must make sure to create the data files so
that they are large enough to prevent the server from experiencing a
lack of disk space with tempdb.
How Increasing the Number of Tempdb Data Files with Equal Sizing Reduces
Contention
Here is a list of how increasing the number of tempdb data files with
equal sizing reduces contention:
With one data file for the tempdb, you only have one GAM page, and one
SGAM page for each 4 GB of space.
Increasing the number of data files with the same sizes for tempdb
effectively creates one or more GAM and SGAM pages for each data file.
The allocation algorithm for GAM gives out one extent at a time (eight
contiguous pages) from the number of files in a round robin fashion
while honoring the proportional fill. Therefore, if you have 10 equal
sized files, the first allocation is from File1, the second from File2,
the third from File3, and so on.
The resource contention of the PFS page is reduced because eight pages
are marked as FULL at a time because GAM is allocating the pages.
Yih-Yoon Lee
Hassan wrote:
> Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
> EXT type of lock in the tempdb database . The sproc does not seem to be
> doing anything with temp tables or table variables,etc.. Theres some
> aggregate functions only in a simple select statement
> Any idea why the EXT type of lock ? Thanks
>

Ext Locks to tempdb db

Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
EXT type of lock in the tempdb database . The sproc does not seem to be
doing anything with temp tables or table variables,etc.. Theres some
aggregate functions only in a simple select statement
Any idea why the EXT type of lock ? ThanksAggregations often use tempdb and the nolock would have no bearing on that
aspect of it.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OTWI8WZ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
> EXT type of lock in the tempdb database . The sproc does not seem to be
> doing anything with temp tables or table variables,etc.. Theres some
> aggregate functions only in a simple select statement
> Any idea why the EXT type of lock ? Thanks
>|||In addition to Andrew's answer:
These tempdb EXT locks are used internally by SQL Server to allocate or
deallocate pages and extents. The pages and extents are used as temporary
storage to process the query.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eNAu0Wa3EHA.924@.TK2MSFTNGP14.phx.gbl...
> Aggregations often use tempdb and the nolock would have no bearing on that
> aspect of it.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OTWI8WZ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
>> Running sp_lock to a spid that runs a sproc and seems to have a lot of
>> 'X'
>> EXT type of lock in the tempdb database . The sproc does not seem to be
>> doing anything with temp tables or table variables,etc.. Theres some
>> aggregate functions only in a simple select statement
>> Any idea why the EXT type of lock ? Thanks
>>
>|||Hi Hassan,
I highly recommend reviewing this article
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
In my opinion, every server should implement the following suggestions
on how to reduce tempdb contention.
Increase the Number of Tempdb Data Files with Equal Sizing
If the data file size of tempdb is 5 GB, and the Log file size is 5 GB,
the recommendation is to increase the single datafile to 10 (each of 500
MB to maintain equal sizing), and leave the log file as is. Having the
different data files on separate disks would be good; however, this is
not required and they can co-exist on the same disk.
The optimal number of tempdb data files depends on the degree of
contention seen in tempdb. As a starting point, you can configure the
tempdb to be at least equal to the number of processors assigned for SQL
Server. For higher end systems (for example, 16 or 32 proc), the
starting number could be 10. If the contention is not reduced, you may
have to increase the number of data files more.
The equal sizing of data files is critical because the proportional fill
algorithm is based on the size of the files. If data files are created
with unequal sizes, the proportional fill algorithm tries to use the
largest file more for GAM allocations instead of spreading the
allocations between all the files, thereby defeating the purpose of
creating multiple data files.
The auto-grow of tempdb data files can also interfere with the
proportional fill algorithm. Therefore, it may be a good idea to turn
off the auto-grow feature for the tempdb data files. If the auto-grow
option is turned off, you must make sure to create the data files so
that they are large enough to prevent the server from experiencing a
lack of disk space with tempdb.
How Increasing the Number of Tempdb Data Files with Equal Sizing Reduces
Contention
Here is a list of how increasing the number of tempdb data files with
equal sizing reduces contention:
? With one data file for the tempdb, you only have one GAM page, and one
SGAM page for each 4 GB of space.
? Increasing the number of data files with the same sizes for tempdb
effectively creates one or more GAM and SGAM pages for each data file.
? The allocation algorithm for GAM gives out one extent at a time (eight
contiguous pages) from the number of files in a round robin fashion
while honoring the proportional fill. Therefore, if you have 10 equal
sized files, the first allocation is from File1, the second from File2,
the third from File3, and so on.
? The resource contention of the PFS page is reduced because eight pages
are marked as FULL at a time because GAM is allocating the pages.
Yih-Yoon Lee
Hassan wrote:
> Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
> EXT type of lock in the tempdb database . The sproc does not seem to be
> doing anything with temp tables or table variables,etc.. Theres some
> aggregate functions only in a simple select statement
> Any idea why the EXT type of lock ? Thanks
>

Expression Data Type Conversion

I am getting the following error in my report. I am tring to program the BackgroungColor Expression so the if the value is null display a blue color else leave it white.

Conversion from string "" to type 'Date' is not valid

Here is my code sample:

=Iif(Fields!ReturnTime.Value = "", "AliceBlue", "White")

Hi RayClark,

You cannot compare a string with a date.
You can do this in several ways here is one:

=IIF(Len(Fields!ReturnTime.Value)=0, "AliceBlue", "White")

Use the length function to check the length of return time. If it is 0 set it to alice blue else set it to white :).

http://jhermiz.googlepages.com|||Thanks that worked great!!!

Exporting to Excel Error : (rrRenderingError)

When exporting some reports to excel.
This error appears
-----
Exception of type
microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Index was out of range. Must be non-negative and less than the size of the
collection. Parameter name: index
----
The report can be exported as CSV without no issues.
Is there any explanation on what this error means...
Is there fix to the problem ....
Help...Have you installed RS sp1?
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Constantine" <Constantine@.discussions.microsoft.com> wrote in message
news:44A3E55F-80FD-4F27-BCFE-53573F67E2CA@.microsoft.com...
> When exporting some reports to excel.
> This error appears :
> -----
> Exception of type
> microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Index was out of range. Must be non-negative and less than the size of the
> collection. Parameter name: index
> ----
> The report can be exported as CSV without no issues.
> Is there any explanation on what this error means...
> Is there fix to the problem ....
> Help...|||"Constantine" <Constantine@.discussions.microsoft.com> wrote in message
news:44A3E55F-80FD-4F27-BCFE-53573F67E2CA@.microsoft.com...
> When exporting some reports to excel.
> This error appears :
> -----
> Exception of type
> microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Index was out of range. Must be non-negative and less than the size of the
> collection. Parameter name: index
> ----
> The report can be exported as CSV without no issues.
> Is there any explanation on what this error means...
> Is there fix to the problem ....
> Help...
I and two others have posted to this board with the same issue in the past
month. To my knowledge no fix or workaround has been posted. If you find
such a workaround please post back.
Good luck,
Bryan|||Hi Mary,
I have installed sp1. I will try to re-install it.
Thanks
"Mary Bray" wrote:
> Have you installed RS sp1?
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Constantine" <Constantine@.discussions.microsoft.com> wrote in message
> news:44A3E55F-80FD-4F27-BCFE-53573F67E2CA@.microsoft.com...
> > When exporting some reports to excel.
> > This error appears :
> > -----
> > Exception of type
> > microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown. (rrRenderingError) Get Online Help
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown.
> > Index was out of range. Must be non-negative and less than the size of the
> > collection. Parameter name: index
> >
> > ----
> >
> > The report can be exported as CSV without no issues.
> >
> > Is there any explanation on what this error means...
> > Is there fix to the problem ....
> >
> > Help...
>
>

Exporting to Excel

Hi,
I am having a problem exporting to excel for a particualr report. Based on
the parameters chosen then a different type of report shows up. However,
when I try to export a particular report it gives me all three reports (one
with valid data and two with data in the form of XXXXXXXXs). Does anyone
have any ideas on how to fix this?
Thanks, CaseyI have several reports where the table that is visible in the report is
determined by a parameter, which works fine until I try to export the report
to excel. When I try to export to excel all four tables are visible. Any
ideas on how to work around this behavior.
TIA,
Arly