Saving a SQL Query to a database table.

I've been troubleshooting a problem I've had for a couple of hours now and
am getting nowhere and hope someone here can help.
I have a car website under development that allows registered users to enter
search criteria when looking for cars. I want to allow users to save their
search and thought the easiest way would be to save the sql query string
directly to the database.
I store the sql string in an ASP session variable and then try to add this
session variable to the database but have not succeeded. A typical query
string looks like this:
select * from hk_car WHERE active_status = 1 AND (sold_date = '' or
sold_date is null) AND number_doors >= 2 AND (car_town LIKE '%br7%' OR
car_postcode LIKE '%br7%')
The field in the database is called search_sql and allow's NULL values.
The error message I am getting is:
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
After some testing, I have found the field does not like any spaces yet I
have other fields working fine with spaces so don't understand this...
Any help is greatly appreciated!!
Thanks,
Robin.1) Make sure that you use: replace(sqlString, "'", "''")
2) You should be able to add any text to the field as long as your length is
<= to max.

> I store the sql string in an ASP session variable and then try to add this
> session variable to the database but have not succeeded. A typical query
> string looks like this:
> select * from hk_car WHERE active_status = 1 AND (sold_date = '' or
> sold_date is null) AND number_doors >= 2 AND (car_town LIKE '%br7%' OR
> car_postcode LIKE '%br7%')
> The field in the database is called search_sql and allow's NULL values.
> The error message I am getting is:
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
> After some testing, I have found the field does not like any spaces yet I
> have other fields working fine with spaces so don't understand this...|||..inetserver.asp.db would have been a better group to post this question.
Robin Lawrie wrote:
> I've been troubleshooting a problem I've had for a couple of hours
> now and am getting nowhere and hope someone here can help.
> I have a car website under development that allows registered users
> to enter search criteria when looking for cars. I want to allow users
> to save their search and thought the easiest way would be to save the
> sql query string directly to the database.
> I store the sql string in an ASP session variable and then try to add
> this session variable to the database but have not succeeded. A
> typical query string looks like this:
> select * from hk_car WHERE active_status = 1 AND (sold_date = '' or
> sold_date is null) AND number_doors >= 2 AND (car_town LIKE '%br7%' OR
> car_postcode LIKE '%br7%')
> The field in the database is called search_sql and allow's NULL
> values.
> The error message I am getting is:
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done.
> After some testing, I have found the field does not like any spaces
> yet I have other fields working fine with spaces so don't understand
> this...
>
I doubt that the spaces are the problem. It sounds as if you are using
dynamic sql to pass this string to the database so it is more likely that
the embedded apostrophes are the problem.
A better technique woulc be to use parameters to pass this string, either
via a stored procedure (recommended):
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl
or using an explicit Command object to pass parameters to a string
containing parameter markers:
http://groups-beta.google.com/group...r />
2fee7804e
Dynamic sql should be avoided due to the problems with handling embedded
delimiters. The larger problem however is the danger of sql injection:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/adv...l_injection.pdf
HTH,
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi Bob,
Thanks for your excellent and prompt reply.
I'm new to SQL and haven't used Stored Procedures yet (I'm a bit scared of
them actually!) but followed your second suggestion of using an explicit
Command object and am really please to say that this worked!!
Thank you very much and in future I will post to the inetserver.asp.db
Thanks again.
Robin.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:Og95YJh6FHA.1036@.tk2msftngp13.phx.gbl...
> .inetserver.asp.db would have been a better group to post this question.
> Robin Lawrie wrote:
> I doubt that the spaces are the problem. It sounds as if you are using
> dynamic sql to pass this string to the database so it is more likely that
> the embedded apostrophes are the problem.
> A better technique woulc be to use parameters to pass this string, either
> via a stored procedure (recommended):
>
http://groups.google.com/groups?hl=...phx.gbl

> or using an explicit Command object to pass parameters to a string
> containing parameter markers:
>
http://groups-beta.google.com/group...2e36562fee7804e

>
> Dynamic sql should be avoided due to the problems with handling embedded
> delimiters. The larger problem however is the danger of sql injection:
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
> http://www.nextgenss.com/papers/adv...l_injection.pdf
> HTH,
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Robin Lawrie wrote:
> Hi Bob,
> Thanks for your excellent and prompt reply.
> I'm new to SQL and haven't used Stored Procedures yet (I'm a bit
> scared of them actually!)
Don't be. Here's a good starting point:
http://www.aspfaq.com/show.asp?id=2201
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Save Report .rdl including Data

Where is the option in SSRS 2005 to save a report .rdl with data so I can send it to others for troubleshooting?

http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/msg/0d2569d2e55e392e

>>

microsoft.public.sqlserver.reportingsvcs > what is the .rdl.data file for?
From: Lev Semenets [MSFT] - view profile
Date: Thurs, Aug 19 2004 2:35 pm
Email: "Lev Semenets [MSFT]" <l...@.microsoft.com>
Groups: microsoft.public.sqlserver.reportingsvcs

It is cached data used by preview

--
This posting is provided "AS IS" with no warranties, and confers no rights.
>>

|||ok, so then you're saying that cache file can be used somehow or is the cach lost after preview? There has to be a way other than this just like in Crystal where you can save the data. I know you can do this in XML format from an .rdl but I don't know how to then use that xml file back into my report later..I tried setting up an XML dataset to try to use the xml file but not sure how to|||

You should see an .rdl.data along with the .rd file after previewing the report. This entry in Russell Christopher's blog explains how others can use this file:

http://blogs.msdn.com/bimusings/archive/2005/06/22/431489.aspx

>>

How to preview a report without direct access to its data source in SQL Server Reporting Services 2000

You may have noticed that starting with SSRS SP1, when you preview a report which contains data in the designer, a ReportName.RDL.Data file appears on your file system. We store the data that was just displayed by your preview action in this file so we don't have to round-trip to the server over and over again each time you preview the report (assuming you don't change any parameter values, etc.)

If you want, you can use this data file to make your report more "portable" -- Basically taking the report.rdl and report.rdl.data files to another machine that doesn't have access to the "real" data store. You can also use this method to send the report/data to a buddy if you need some help troubleshooting the report. Cool hack!

Close Visual Studio and add the following lines to Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE\devenv.exe.config file:

<system.diagnostics>
<switches>
<add name="Microsoft.ReportDesigner.ReportPreviewStore.ForceCache" value="1" />
</switches>
</system.diagnostics>

What this entry does is force the preview pane to bypass checking that the cache (data file) is up-to-date and that any report parameter values selected in the report match those used to generate the cached data in the local file.

Next, Create a new VS Report Project and add the existing (rdl) report to it. Drop your rdl.data file in the same folder.

(This is the strange part) Add a new shared datasource to the project, giving it the SAME name as the old datasource your report used in the other project it lived in previously. You don't need even need to specify connection string in his data source -- it'll be using the cached data in your rdl.data file.

Finally (if necessary), modify the report so that any parameters in it don't use queried values (which you no longer have data for) , and that all parameters have valid default values. You also need to remove any other datasets that use data not coming from your .rdl.data file.

Preview!

|||thanks a lot!|||Hey, I'm using SSRS 2005, not 2000! I hope this still works the same|||I have a problem with his technique. I have several datasets in my report and so when I go through his steps, my datasets are still trying to reference stored procs and my preview errors out. What do I do in this situation?|||

here's my .rdl, it's pretty high end and a lot is going on. I am not sure what to keep or tweak here to stop the errors and get it to just use that file like the article says because I have so much going on in this report:

http://photopizzaz.biz/myreport.zip

|||It appears to work for me with SRS 2005. I ran a report the first time and it took about 20 seconds, the second time using the same parameter with a change to a label it took about 2 seconds.