Can I Force SQL to Accept INSERT List of Values Less Than Number of Columns?

I have a canned application that does INSERTs with lists of values without
column lists. The table has one additional (uniqueidentifier) column for merge
replication, so the application is inserting 7 values, but there are 8
columns. Is there a way to tell SQL to accept it anyway and just fill the
columns from left to right until it runs out of data? I hope so, because I
have no access to the source code.
--EricHello Eric. you could try with renaming that table (the one you insert in)
and creating a view with old name of the table you just renamed. In the view
definition specify all fields from the renamed table except the one that you
added for replication(uniqueidentifier) .
Hope this works,
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr
"Eric Robinson" <eric@._nospam_nvipa.com> wrote in message
news:CFN379450577312037@.news.microsoft.com...
> I have a canned application that does INSERTs with lists of values without
> column lists. The table has one additional (uniqueidentifier) column for
merge
> replication, so the application is inserting 7 values, but there are 8
> columns. Is there a way to tell SQL to accept it anyway and just fill the
> columns from left to right until it runs out of data? I hope so, because I
> have no access to the source code.
> --Eric
>|||If there isn't a column list specified in the INSERT then the number of
columns in the table must match the number of columns in the INSERT
statement (less the IDENTITY column, if any).
You could set a default for the uniqueidentifier column, rename the table
and then create a view under the original name containing all except the
extra column:
CREATE TABLE newname (a INTEGER PRIMARY KEY, b INTEGER NOT NULL, c
UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ...)
CREATE VIEW oldname
AS
SELECT a,b
FROM newname
Then find the programmer and make him fix his code.
--
David Portas
--
Please reply only to the newsgroup
--|||"Tomislav Kralj" <tomislav.kralj1@.zg.tel.hr> wrote in message
news:bpi4j6$vq0$1@.sunce.iskon.hr...
> Hello Eric. you could try with renaming that table (the one you insert in)
> and creating a view with old name of the table you just renamed. In the
view
> definition specify all fields from the renamed table except the one that
you
> added for replication(uniqueidentifier) .
Oh, and i forgot. create view with VIEW_METADATA option !!!
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hrsql

Can a Data Flow Task mimic Bulk Insert?

Hi Guys,

The way I understand a Data Flow Task is that it inserts the rows from the source to destination one by one. Is there a way to make it act like a bulk insert task? We have been experiencing performance issues when inserting a lot of rows from one table to another. If there's no way to actually do it, can a bulk insert task functionality be scripted? Coz what I need is a table to table insert, and the bulk insert task only accepts data files as sources.

Thanks!

Kervy

What destination object are you using? The OLEDB Destination and the SQL Server destination both enable bulk insert options for SQL Server.

How many rows are you inserting?

Donald

|||

Oh you mean to say, by default, an OLEDB source inserts to an OLEDB Destination in bulk? We are only inserting a couple of hundred thousand rows, but the source table is in the US. destinations are in Europe and Asia Pacific, so we need to optimize this as much as we can given the proximity of the servers.

We are using OLEDB source and OLEDB Destination

Thanks

|||

If you need to performance tune your data-flow then you should read a whitepaper that I have mentioned here: http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx

There's also more useful information here: http://blogs.conchango.com/jamiethomson/archive/2005/04/20/1319.aspx

Note that SSIS does not insert records one at a time - that would be a performance killer. The SSIS pipeline processes data in buffers and the contents of each buffer is inserted at the same time! A large part of performance tuning your application is finding the optimum buffer size.

Note that in your scenario the bottleneck is likely to be the georgraphic disparity of the source and destination, not how fast SSIS can process it. Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require.

-Jamie

|||

hi Jamie,

Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..

total number of rows to be inserted = 200,000

total number of columns = 167

|||

KervyChoa wrote:

hi Jamie,

Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..

total number of rows to be inserted = 200,000

total number of columns = 167

Exactly, hence my previous comment "Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require."

The point here is to reduce the amount of data coming over the wire because that is quite obviously the bottleneck in your scenario.

-Jamie

|||I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?|||

KervyChoa wrote:

I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?

As we have already determined (twice) the bottleneck is not within the SSIS pipeline so looking at this perf counter is not going to help you.

Unless you pick one of the machines up and move it to a different continent, or improve the speed of the link between them, you're not going to see any eprformance improvement.

-Jamie

Calling web service onclick of report column

We have a web service that reads a PDF file into binary and inserts the
binary text in a database, then returns an ID to the record. Is there a way
to call this web service from a Reporting Service Report column? The column
will have an Adobe image in the column and when clicked with call the web
service and read the record from the Database streaming the binary to a
browser window.
Thanks in advance!
RickOn Nov 27, 7:55 am, "Rick" <rfem...@.newsgroups.nospam> wrote:
> We have a web service that reads a PDF file into binary and inserts the
> binary text in a database, then returns an ID to the record. Is there a way
> to call this web service from a Reporting Service Report column? The column
> will have an Adobe image in the column and when clicked with call the web
> service and read the record from the Database streaming the binary to a
> browser window.
> Thanks in advance!
> Rick
You might try looking into using the Custom Code section of the report
(via: Layout tab >> Report drop-down tab >> Report Properties... >>
Code tab). Another long shot might be to use Jump to URL as part of
the Navigation Properties and pass the Web Service parameters as part
of an expression that calls the web service via URL. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Ok, I created a class library that makes a call to the web service and
referenced it in the report.
The web service uses system.web.httpresponse to stream the file to a
browser, so in order to make the class library work it required me to pass
in the httpresponse from the calling web page. So I tested that with a test
web site and it works as expected. I tried adding the same code to call the
class library using Custom Code and Navigation URL. The problem I have is
how to get the Httpresponse from the report to pass into the class library,
I made a reference to System.Web.Httpresponse in the report, but when I call
the code or use the navigation url it does not recognize HTTPResponse.
Any suggestions?
Error:
The Hyperlink expression for the image 'image1' contains an error: [BC30691]
'HttpResponse' is a type in 'Web' and cannot be used as an expression.
Navigation URL:
=webservicecall.webservicecall.getpdffile("filepath\filename.pdf",System.Web.HTTPResponse)
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:b5c663d9-9c91-4b50-8b56-bae9ef9f4788@.w34g2000hsg.googlegroups.com...
> On Nov 27, 7:55 am, "Rick" <rfem...@.newsgroups.nospam> wrote:
>> We have a web service that reads a PDF file into binary and inserts the
>> binary text in a database, then returns an ID to the record. Is there a
>> way
>> to call this web service from a Reporting Service Report column? The
>> column
>> will have an Adobe image in the column and when clicked with call the web
>> service and read the record from the Database streaming the binary to a
>> browser window.
>> Thanks in advance!
>> Rick
>
> You might try looking into using the Custom Code section of the report
> (via: Layout tab >> Report drop-down tab >> Report Properties... >>
> Code tab). Another long shot might be to use Jump to URL as part of
> the Navigation Properties and pass the Web Service parameters as part
> of an expression that calls the web service via URL. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I could use some help. Here is what I have, I created a Class Library that
makes a call to a web service and referenced the class library in the
report, I setup code in the report properties code, The Public function
calls CallWebService2, CallWebService2 makes a call to a the class library
with the web service passing in a pdffiname, an ID Parameter and the
HTTPResponse, the web service in return streams the PDF to the browser, I
put a column on the report that has an adobe icon image, in this column in
the Jump to URL, I put =code.CallWebService(): When I do this the Jump To
URL does nothing. I'm not sure if I'm getting the HTTPResponse correctly.
Report Properties Code:
Public Function CallWebService() as String
CallWebService2()
Return "True"
End Function
Private Function CallWebService2() as Boolean
Dim nservice As New WebServiceCall.WebServiceCall
Dim response1 As System.Web.HttpResponse
WebServiceCall.WebServiceCall.GetPDFFile("filepath\filename",
"parameter1", Response1)
Return True
End Function
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:Ks7X3AzMIHA.6940@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Rick,
You could not use the Code in the Jump to URL directly.
You may add a new report which is call the web service to show the
information. And you may use the Jump to URL to the report.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.