Excuting distributed queries in parallel

Hi,

Is it possible to execute queries in parallel on multiple linked servers? I retrieve the checksum of a table on a linked servers like this:

SELECT * FROM OPENQUERY(Server1, 'SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*) FROM Table1')

I need to do this on multiple linked servers at the same time - is this possible? I tried the following but my workstation executes the queries sequentially:

SELECT ("Query Server1"), ("Query Server2"), ("Query Server3"),..

Any suggestions?

Rgds

Bob

TSQL statement execution is always serial in a batch or module. You can run the statements in parallel by doing one of the following:

1. Use on-demand SQL Agent jobs which contain TSQL task with each distributed query. You can then start each job in your TSQL code and wait for their completion

2. In SQL Server 2005, you can use service broker messaging infrastructure to activate multiple procs in parallel with each one executing a specific query

However, it is not clear if you are returning the results to client or processing on the server side itself. If you are returning the results to client then these techniques will not be efficient because you will have to dump the results into a table and then query it back. So you might as well run 3 different commands from the client code.

excell export doesn't work

I have a report with multiple tables, when I trie to export this report to
excell
it reporting services returns an error message:
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
can anybody help me solve this?"Dirkjan van Groeningen" <DirkjanvanGroeningen@.discussions.microsoft.com>
wrote in message news:F60A2429-8A44-4DE7-B162-D29FB36091E2@.microsoft.com...
>I have a report with multiple tables, when I trie to export this report to
> excell
> it reporting services returns an error message:
>
> 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
> can anybody help me solve this?
Hi, I have the same problem. I have post this issue here 3 times with no
meaningful help.
Please post back if you find a solution.
Good luck,
Bryan

Excel to SQl

Hi All

Thnks for the time

I was trying to figure out how to insert the data that I read from an excel sheet into multiple tables in SQL Database 2005.

// Connection string to the excel file

string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source= D:\\UserData.xls;" +

"Extended Properties=Excel 8.0;";

//Command to read data is

SELECT ID,ProductName,ProductDesc,CategoryName FROM [Products$]

//Connection string for sql 2005 database

string sqlConnectionString = "DataSource=MyServer\\MSSQLEXPRESS;Initial Catalog=TestExcel;Integrated Security=True";

And what is your question ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

excel source with optional columns

Hi:

I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col.

Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-)

Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns?

TIA

I recently solved this problem using a dynamically built select statement. Is it always just 1 column that's missing or do you need to load a dynamic number of columns? If it's a truly dynamic then the algorithm is a little more complex...|||

Thanks for your response. Request you tell me more aboout it.

I did the whole thing in BIDS in a SSIS project, using a ForEach container, a Excel Source and an OleDB destination. I was hoping to achieve my objectives with these objects and their settings :-).

|||I used a For Each Loop and then a For Loop to solve this problem.

The first For Each Loop iterates threw the columns names in the spreadsheet. It contains a script component that counts the columns storing the result in a variable. There might be a more efficient way to count columns but I couldn't figure out how.

The second For Loop container uses this counter variable to select and load each column one at a time. It contains 2 components; a script component that builds a select statement and a data flow task that actually moves the data using the select statement.

Here is the script code that dynamically builds each select statement:

Public Sub Main()
Dim SelectCommand As String
Dim WorksheetName As String
Dim ColumnLoopIndex As Integer
WorksheetName = Dts.Variables("WorksheetName").Value.ToString
ColumnLoopIndex = CInt(Dts.Variables("ColumnLoopIndex").Value)
SelectCommand = "Select F" & ColumnLoopIndex.ToString & " AS CurrentColumn from [" & WorksheetName & "]"
Dts.Variables("SelectCommand").Value = SelectCommand
Dts.TaskResult = Dts.Results.Success
End Sub

Please note: Depending on your data and how dynamic you want the

package to be you could skip the second For Loop and build a single select

statement that loads all of the columns. In this case your dynamically built select

statement would contain return fields like "SELECT F1, F2, NULL AS F3, NULL AS F4

FROM [myworksheetname]" to account for missing F3 and F4 columns.

Excel Source dt_ntext problems

Hi:

I import data from multiple excel files into SQL DB. I have trouble with fields that could contain >255 chars.

If I have the col type = DT_Ntext in my Data Flow, the package fails for files that do not have any values >255 chars.

If I have the external coltype=dt_wstr and the output coltype=dt_wstr(4000) the package fails if the file contains any value >255 chars.(Implicit conversion does not occur, as expected).

I worked around by adding a dummy first row with >255 chars.

Is there a way to use a cast function to solve this prob? I tried using Select dt_ntext(fieldname) from Sheet1$, but that does not work.

Is there some clean way to get around this problem?

TIA
Kar

Hi Karfast

For now this have to be done manually. I was able successfully execute getting advise from Bob Bojanic

Follw these instructions carefully:

1) Go to Mapping option at Destination, Point your mouse for each of row having your above problem and its corresponding destination (note the Datatypes types and Lengths)

2) Open Advanced Editor for the Excel Source, Under Amend the Types and Lengths accordingly for all the columns in destination to match your source Columns' type and length

Thanks

Subhash Subramanyam

|||

Metadata of Excel sheets is often hard to deal with. The dummy first row seems as a clever solution to me. It is not elegant but the entire JET provider is far from that.

Select dt_ntext(fieldname) is not going to work as dt_ntext can be recognized only by SSIS and not by the JET provider. The more adequate query would be select LongText(fieldname), but I have no idea if JET can deal with it (don't have a way to try it at the moment).

HTH.

|||

Thanks, Bob.

I tried:

Select LongText(Fieldname) , and also tried out Memo etc. All these give a Undefined Function error.

I cant even find any help on this. There is apparently something called a Jet SQL Reference, but I couldnt find it. Not even in Access 2000 Local Help.

So I guess the dirty workaround is the only way :-(

Kar

Excel Pivot Table

When I use Excel 2002 Sp3 to connect to the AS2000. I can select multiple value in the Page field. But when I install the OLAP AS 9.0 provider to connect to the AS2005. The multiple value selection is disapper. How can I fix this?There is a 'select multiple items' checkbox in the bottom area of the dropdown box.

If this is not visible - I'd almost think that your Excel platform at that specific time is Excel 2000.
Try recreating your pivot table from the as2005 cube|||I am using Excel 2002. It is funny sometime the muti value check box appear, sometime it disappear. I also try to reinstall the Excel, but it doesn't help|||

Did you upgrade MSXML to 6.0 as well as upgrading the AS Provider ?

Must say I don't get this behaviour in 2003 having added both the upgrades

|||Yes MSXML 6.0 Parser and MS OLAP Provide For AS 9.0 have been installed in my computer. Now I find the tempoary solution. If I run another application such as IE6 to overlap the Excel, then I can see the multi value check box. It is really @.$%...?|||

It's good that you're getting somewhere!

I remember that in some cases, I've had to have MDAC 2.8 installed if it wasn't already present. You shouldn't be getting that far in excel w/o it, but it's worth a try.

http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

Excel multiple sheets

Hi,
Is it possible to create a report in Crystal report from Excel that has multiple sheets. All sheets should be displayed in the report. Can this be accomplished?
ThanksYou need to make reports per sheet and design other report having these reports as subreports