Drill down slow: setup

Showing posts with label setup. Show all posts

Thursday, March 29, 2012

Dreaded TEXT column

I need to setup repliaction between A and B. I have high transaction /min count. Some tables have TEXT columns. This is what i am planning to do:

Example: table name is CREDITS

vertical partition the table into two. First table (will be called CREDITS_PRI) holds PK column and non TEXT columns, second table (will be called CREDITS_SEC) holds PK column and TEXT columns.

Create a view called CREDITS with INSTEAD OF triggers for inserts, updates and deletes. So far so good. Then setup replication with immediate updating subscription between A and B for tables called ..._PRI and merge replication between A and B for tables called ..._SEC.

Would this work? How do other companies handle this? thank you in advance for pointing me into the right direction.

Lars

Yes, this would work, you could also leave your tables intact the way they were and implement bi-directional transactional replication.

However I assume you're on SQL 2000, correct? If you're on SQL 2005, immediate updating would work just fine if you replaced text datatype with varchar(max) datatype, it's also more optimal and efficient in terms of data storage.

|||

Thank you,

yes, we still are on SQL 2000 and won't upgrade until another 6 months. I have never heard of bi-directional replication since it wasn't mentioned anywhere. I found a great link on MS com and will take a look at it. Thanks again.

Tuesday, March 27, 2012

DR Question

We are planning for DR setup. Currently the database is located on shared disk storage. During DR, if we connect the database in the shared disk storage to the DR server. Will we be able to start up the SQL server at DR server? The IP address of the DR server will be different from the production since on different network segment. Is there any other things we need to take care of, such as user logins? Please comment.

If what is on the shared disk is your user(application) database, then you'll need to manually sync up all other data such as logins, stored procedures, maintenance jobs, etc.

They you would need to attach the database to the instance on the new node.

Downloading File located in SQL Server with ASP

I have a SQL 2000 Standard server setup and I have a table in one of my
databases to store files in. I am using asp code to retrieve the files from
the database. I can download txt, bmp files just fine but when it comes to
pdf documents it just hangs at the downloading the file screen and then gives
me a timeout error after awhile. It is not a problem with the file sizes. I
am testing it with a 20 and 60k pdf files and one of the txt files I used to
test with is a few mb. I am thinking it's either an IIS server config issue
or SQL Server config problem. The IIS server is for our intranet and is not
locked down. Both IIS and SQL are running on the same box which is a Windows
2003 Enterprise server. Any help would be great.
Thanks,
Brett Bishop
MCSE, MCP+I
US Bankruptcy Court"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:0880684A-A141-4D78-842A-041C501DBCF5@.microsoft.com...
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files
from
> the database. I can download txt, bmp files just fine but when it comes
to
> pdf documents it just hangs at the downloading the file screen and then
gives
> me a timeout error after awhile. It is not a problem with the file sizes.
I
> am testing it with a 20 and 60k pdf files and one of the txt files I used
to
> test with is a few mb. I am thinking it's either an IIS server config
issue
> or SQL Server config problem. The IIS server is for our intranet and is
not
> locked down. Both IIS and SQL are running on the same box which is a
Windows
> 2003 Enterprise server. Any help would be great.
Can you access PDF files via URL from the file system?|||fibreiv wrote:
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files from
> the database. I can download txt, bmp files just fine but when it comes to
> pdf documents it just hangs at the downloading the file screen and then gives
> me a timeout error after awhile. It is not a problem with the file sizes. I
> am testing it with a 20 and 60k pdf files and one of the txt files I used to
> test with is a few mb. I am thinking it's either an IIS server config issue
> or SQL Server config problem. The IIS server is for our intranet and is not
> locked down. Both IIS and SQL are running on the same box which is a Windows
> 2003 Enterprise server. Any help would be great.
Perhaps it has to do with the content type when the file is being sent
down. You may also want to set the content-disposition to indicate to
download the file, "Content-Disposition: Attachment" in the response header.
You aren't saying if you are trying to open up the file directly or save
it to disk first.
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Hi,
This is what i would do.
Try to download a PDF file from the file system (this rules out a problem
with IIS serving PDF-files).
Use WFetch[1] to request the PDF page (from the db), and see if IIS gives
a complete reply back (this will rule out a client-side problem).
Usually these kind of problems are caused by the programmers code. SQL
Server only stores the data, and IIS only "gives" the client the data.
Your code however do everything in between, and that is where it usually
goes wrong.
--
Regards,
Kristofer Gafvert
www.gafvert.info - My Articles and help
www.ilopia.com
fibreiv wrote:
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files
from
> the database. I can download txt, bmp files just fine but when it comes
to
> pdf documents it just hangs at the downloading the file screen and then
gives
> me a timeout error after awhile. It is not a problem with the file
sizes. I
> am testing it with a 20 and 60k pdf files and one of the txt files I
used to
> test with is a few mb. I am thinking it's either an IIS server config
issue
> or SQL Server config problem. The IIS server is for our intranet and is
not
> locked down. Both IIS and SQL are running on the same box which is a
Windows
> 2003 Enterprise server. Any help would be great.
> Thanks,
> Brett Bishop
> MCSE, MCP+I
> US Bankruptcy Court|||I have the Content type set to application/pdf and I can't open it or save
it. Just to clarify the data is stored in SQL Image field.
"Aaron Weiker" wrote:
> fibreiv wrote:
> > I have a SQL 2000 Standard server setup and I have a table in one of my
> > databases to store files in. I am using asp code to retrieve the files from
> > the database. I can download txt, bmp files just fine but when it comes to
> > pdf documents it just hangs at the downloading the file screen and then gives
> > me a timeout error after awhile. It is not a problem with the file sizes. I
> > am testing it with a 20 and 60k pdf files and one of the txt files I used to
> > test with is a few mb. I am thinking it's either an IIS server config issue
> > or SQL Server config problem. The IIS server is for our intranet and is not
> > locked down. Both IIS and SQL are running on the same box which is a Windows
> > 2003 Enterprise server. Any help would be great.
> Perhaps it has to do with the content type when the file is being sent
> down. You may also want to set the content-disposition to indicate to
> download the file, "Content-Disposition: Attachment" in the response header.
> You aren't saying if you are trying to open up the file directly or save
> it to disk first.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>|||The file is stored in the Database in an Image field so there is no file
system access to the file.
"Tom Kaminski [MVP]" wrote:
> "fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
> news:0880684A-A141-4D78-842A-041C501DBCF5@.microsoft.com...
> > I have a SQL 2000 Standard server setup and I have a table in one of my
> > databases to store files in. I am using asp code to retrieve the files
> from
> > the database. I can download txt, bmp files just fine but when it comes
> to
> > pdf documents it just hangs at the downloading the file screen and then
> gives
> > me a timeout error after awhile. It is not a problem with the file sizes.
> I
> > am testing it with a 20 and 60k pdf files and one of the txt files I used
> to
> > test with is a few mb. I am thinking it's either an IIS server config
> issue
> > or SQL Server config problem. The IIS server is for our intranet and is
> not
> > locked down. Both IIS and SQL are running on the same box which is a
> Windows
> > 2003 Enterprise server. Any help would be great.
> Can you access PDF files via URL from the file system?
>
>|||"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:9BEC1745-770E-47BF-8749-06E8EBB48601@.microsoft.com...
> The file is stored in the Database in an Image field so there is no file
> system access to the file.
Understood.
As part of the troubleshooting routine, I'm asking if you can access one
from the file system.|||I copied the same file thats in the database to the folder I have all the asp
files in on my server and put the url right to the pdf document and it opened
up acrobat and opened the file.
"Tom Kaminski [MVP]" wrote:
> "fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
> news:9BEC1745-770E-47BF-8749-06E8EBB48601@.microsoft.com...
> > The file is stored in the Database in an Image field so there is no file
> > system access to the file.
> Understood.
> As part of the troubleshooting routine, I'm asking if you can access one
> from the file system.
>
>|||fibreiv wrote:
> I have the Content type set to application/pdf and I can't open it or save
> it. Just to clarify the data is stored in SQL Image field.
> "Aaron Weiker" wrote:
>>Perhaps it has to do with the content type when the file is being sent
>>down. You may also want to set the content-disposition to indicate to
>>download the file, "Content-Disposition: Attachment" in the response header.
Are you setting "Content-Disposition" at all in the header?
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:AED1E8B2-C2EE-4E02-9A38-BA527F0C6B2B@.microsoft.com...
> I copied the same file thats in the database to the folder I have all the
asp
> files in on my server and put the url right to the pdf document and it
opened
> up acrobat and opened the file.
OK - at this point, we'll need to see code to get an idea of how you're
doing it. In that case, I would suggest adding the ASP newsgroup as well
...|||Here is what I am doing to test it. I have hardcoded the file I want to get
out of the database.
Connect = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=INNB;Data Source=pompom"
Set db = Server.CreateObject("ADODB.Connection")
db.Open Connect
SQL = "SELECT filesize, contenttype, filename, filedata FROM thefiles where
id = 2"
Set rs = db.Execute( SQL )
If rs.EOF Then Response.End
Response.ContentType = "application/pdf"
Response.AddHeader "Content-Disposition", "attachment;filename=agencies.pdf"
Response.AddHeader "Content-Length", "16863"
Response.BinaryWrite rs("filedata")
"Aaron Weiker" wrote:
> fibreiv wrote:
> > I have the Content type set to application/pdf and I can't open it or save
> > it. Just to clarify the data is stored in SQL Image field.
> >
> > "Aaron Weiker" wrote:
> >>Perhaps it has to do with the content type when the file is being sent
> >>down. You may also want to set the content-disposition to indicate to
> >>download the file, "Content-Disposition: Attachment" in the response header.
> Are you setting "Content-Disposition" at all in the header?
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>|||fibreiv wrote:
> Connect = "Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=INNB;Data Source=pompom"
> Set db = Server.CreateObject("ADODB.Connection")
> db.Open Connect
> SQL = "SELECT filesize, contenttype, filename, filedata FROM thefiles where
> id = 2"
> Set rs = db.Execute( SQL )
> If rs.EOF Then Response.End
> Response.ContentType = "application/pdf"
> Response.AddHeader "Content-Disposition", "attachment;filename=agencies.pdf"
> Response.AddHeader "Content-Length", "16863"
> Response.BinaryWrite rs("filedata")
Have you tried putting Response.End at the end? Someone else may be able
to help identify a missing piece as it's been a long time since I did this.
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/

Thursday, March 22, 2012

Download for redistributable SQL Server Express Edition?

Where do I find the setup files needed to redistribute sql server express installation files? I downloaded the SQLEXPR_ADV.EXE but the files disappear as soon as you cancel the installation.

I need the sql server setup files to package with my program fr distribution.

THANKS SO MUCH

First, this file is not SQL Express. In stead, it is for SQL Express Advanced. For SQL Express, you need SQLEXPR.EXE.

Second, when you doubl-click the file SQLEXPR_ADV.EXE, it starts to extract all packed files into a temporary directory. You can see a progress window and it will tell you the temporay directory where you can find unpacked files and folders. Then it will start to perform setup. After setup finishes, this temporary directory will be removed automatically.

|||

Thanks for prompt reply.

Yes, I know and that is exactly my question (Second, when you doubl-click the file SQLEXPR_ADV.EXE, it starts to extract all packed files into a temporary directory. You can see a progress window and it will tell you the temporay directory where you can find unpacked files and folders. Then it will start to perform setup. After setup finishes, this temporary directory will be removed automatically.)

How can I get these setup files so I can distribute them along witth my application?

If I try to copy once it is extracted it gives a copy error since the file is in use.

|||

Yes. You cannot copy the temporary folder with drag/drop. But you can open a console window with xcopy to copy the temporary to a new folder.

I do not know whether you can redistribute the content. You need to contact your attonary before you do that.

|||I think you should distribute SQLEXPR.EXE and call this EXE from your application setup.|||

the problem is I need to customize the upgrade by providing the template.ini with it.

template.ini is only available once the sqlexpr.exe has extracted.

if ms says it is redistributable and install can be customized using template.ini, how can i customize the SQLEXPR.EXE ?

i am lost.

|||

Then this is not a technical problem any more. You should ask your attonary instead.

|||I am not sure using template.ini is a supported configuration. Have you tried setting the same parameters on command line?

Sunday, March 11, 2012

Double left joins with conditions

I have a setup in an Access database with linked tables to Oracle (its a remote application database that is synced when a connection is available). I have to perform inserts, updates, and deletes based upon records that have changed since the last update. In the examples of inserting records into oracle, I find records in access that do not exist in oracle and meet the date condition. Where I run into problems are in tables like [events]: I need to only be updating, inserting, and deleting events for a specific county but the only place county is designated is in the location table that events are tied to through lctn_id. I just can't figure out how to involve the location table join with the events table join and the necessary conditions.

locations (and locations_ora)
lctn_id, cnty_nbr, lctn_nm..., rec_dt

events (and events_ora)
evnt_id, lctn_id, evnt_txt, ..., rec_dt

Example of format used for another table:
INSERT INTO locations_ora SELECT DISTINCTROW a.* FROM locations a LEFT JOIN locations o ON a.lctn_id=o.lctn_id WHERE (o.lctn_id IS NULL) AND a.rec_dt>#8/17/2000 1:11:11 AM# AND a.cnty_nbr=5

How I would like to get events working:
INSERT INTO events_ora SELECT DISTINCTROW a.* FROM events AS a LEFT JOIN events_ora AS o ON a.evnt_id=o.evnt_id WHERE (o.evnt_id IS NULL) AND a.rec_dt>#8/17/2000 1:11:11 AM# AND {some way to only get events tied locations in a specific county}
-- or even --
INSERT INTO events_ora SELECT DISTINCTROW a.* FROM (SELECT a.* FROM events a LEFT JOIN locations l ON b.lctn_id=l.lctn_id WHERE l.cnty_nbr=5 ) LEFT JOIN events_ora o ON a.evnt_id=o.evnt_id WHERE (o.evnt_id IS NULL) AND a.rec_dt>#1/17/1979 1:11:11 AM#
(I thought this one would work but Access blows up on the SELECT after the FROM.)

Can anyone point me in the right direction?INSERT
INTO events_ora
SELECT a.*
FROM (
events a
inner
JOIN locations l
ON (
l.lctn_id = a.lctn_id
and l.cnty_nbr = 5
)
)
LEFT
JOIN events_ora o
ON o.evnt_id = a.evnt_id
WHERE a.rec_dt>#1/17/1979|||The traffic signals of North Carolina thank you immensely!

Seeing how this is done correctly and in the reading I've done on your site (since I figured you would be the likely responder to my thread) has definitely increased my understanding of how to work with SQL. You will now be added to the Whiteboard of Fame in conference room C-011.|||thanks :)

that's the nicest kudos i've received in a while

Double inserts during snapshot posting of transactional replication

Hi,
So, I have setup transactional replication, uses the initial snapshot. When
the snapshot is being posted, it runs the same command, and causing it to
fail on the primary key constraint.
Has anyone seen this before?
Gary,
please can you post up the error message.
Also, are you doing a nosync subscription or is it a full initialization?
Regards,
Paul Ibison

Friday, March 9, 2012

Dos

I have a server that automatically updates itself every night. I have .bat files setup to detect any error messages in the log files that may be encountered during the process, and the job would abort. Furthermore, I need to have an alert e-mailed to me. The plan I came up with to do that is to somehow have another bat file chek my error log file. If the file is empty, the bat file would execute. Otherwise, it would simply fail. I need to have such bat file created, so I could schedule it using SQL Server 7 job scheduler. Now I don't know the dos command to check if the file is empty. Please, any suggestions would be greatly appreciated.Can't think of a dos command to help you but it sounds like a perfect candidate for DTS. You can use xp_sendmail and vbscript ... plus many others.

Wednesday, March 7, 2012

Don't send report if it has no data

Hi All,
I have the standard version of SQL Server installed.
I have setup a few reports that will be automatically sent to some out
clients on a daily basis via a normal email-based subscription in reporting
services.
On some days, these reports are blank i.e. no rows returned in the dataset.
I am wondering if it is possible for the report to not be sent if a
situation exists where the report is blank
Thanks
SanjeevIf you have access to the sql server agent and can write a quick query that
will let you know if the data is going to be available it is possible. Each
subscription creates a sql server job in the sql server agent. The job
basically fires a stored procedure that executes the event (get params->run
report->render->mail or save...). The sql server jobs are saved with GUID
names that link back to the report like below. So look in the catalog and
find the report link to the scheduleID or Subscription ID. That ID will
link with the sql server job. Edit the job and have it do some if logic.
IF (there are results in this quick query) THEN trigger the event ELSE
(return 0, dbsendmail an error or however you want to handle it). This is
the basic idea of it. Let me know if you need any more help/detail.
FROM dbo.Catalog INNER JOIN
dbo.ReportSchedule ON dbo.Catalog.ItemID = dbo.ReportSchedule.ReportID INNER
JOIN
dbo.Schedule ON dbo.ReportSchedule.ScheduleID = dbo.Schedule.ScheduleID
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Sanjeev Rampersad" <sanjeev@.sqr.com> wrote in message
news:O7miaQq9GHA.3960@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have the standard version of SQL Server installed.
> I have setup a few reports that will be automatically sent to some out
> clients on a daily basis via a normal email-based subscription in
> reporting services.
> On some days, these reports are blank i.e. no rows returned in the
> dataset.
> I am wondering if it is possible for the report to not be sent if a
> situation exists where the report is blank
> Thanks
> Sanjeev
>|||Hi Michael,
Works like a charm.
Thanks a million
Sanjeev
"Michael Abair" <michael.abair@.chicos.com> wrote in message
news:%23Xgxept9GHA.4708@.TK2MSFTNGP05.phx.gbl...
> If you have access to the sql server agent and can write a quick query
> that will let you know if the data is going to be available it is
> possible. Each subscription creates a sql server job in the sql server
> agent. The job basically fires a stored procedure that executes the event
> (get params->run report->render->mail or save...). The sql server jobs
> are saved with GUID names that link back to the report like below. So
> look in the catalog and find the report link to the scheduleID or
> Subscription ID. That ID will link with the sql server job. Edit the job
> and have it do some if logic. IF (there are results in this quick query)
> THEN trigger the event ELSE (return 0, dbsendmail an error or however you
> want to handle it). This is the basic idea of it. Let me know if you
> need any more help/detail.
>
> FROM dbo.Catalog INNER JOIN
> dbo.ReportSchedule ON dbo.Catalog.ItemID = dbo.ReportSchedule.ReportID
> INNER JOIN
> dbo.Schedule ON dbo.ReportSchedule.ScheduleID = dbo.Schedule.ScheduleID
>
> --
> Michael Abair
> Programmer / Analyst
> Chicos FAS Inc.
> "Sanjeev Rampersad" <sanjeev@.sqr.com> wrote in message
> news:O7miaQq9GHA.3960@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> I have the standard version of SQL Server installed.
>> I have setup a few reports that will be automatically sent to some out
>> clients on a daily basis via a normal email-based subscription in
>> reporting services.
>> On some days, these reports are blank i.e. no rows returned in the
>> dataset.
>> I am wondering if it is possible for the report to not be sent if a
>> situation exists where the report is blank
>> Thanks
>> Sanjeev
>

Sunday, February 26, 2012

Don't email report if no results from query

With an automated report (scheduling setup in report manager) is there a way to prevent the sending of an email if the report query has no results?Any ideas anyone?|||Sure. Rather than just creating a schedule, you'll need to do a data driven subscription which gets fired ON a schedule.

The data driven subscription will query another SQL Table which you will need to create and populate with data like who to send the report to (email), what report renderer to use (HTML? PDF? Excel?), what parameter values should be sent to the report for filtering purposes (someField = "SomeValue"), etc. You'll add one row per report which should get sent out.

When you create a data driven sub, the wizard will ask you WHERE it should get the info above from...you normally supply a basic SELECT statement that would select info from "your" table...In your scenario, you'd make the query a bit more complicated...Instead of JUST returning info from "your" table, you would also add some additional logic to check and see if the REPORT will actually return records. Here's a really, really ugly version of what I'm talkig about:

SELECT email,extensionType, myParameterValue from someTableICreated

WHERE exists(select * from a query that could be close to the one my report might use in it's data source..but this one should be quicker and return no rows if the report returns no rows )

Make sense?|||

FYI--This still e-mails a blank report if there are no records in the query, so I don't know what I'm missing.

Brian J. Matuschak
v-brmatu@.microsoft.com

Don't email report if no results from query

SELECT email,extensionType, myParameterValue from someTableICreated

WHERE exists(select * from a query that could be close to the one my report might use in it's data source..but this one should be quicker and return no rows if the report returns no rows )

Make sense?|||

FYI--This still e-mails a blank report if there are no records in the query, so I don't know what I'm missing.

Brian J. Matuschak
v-brmatu@.microsoft.com

Domain to DMZ Question

How do you setup replication without using window authentication when trying to replicate from a domain to a workgroup in the DMZ, without using trusts?
use sql authentication and FTP.
"JLS" <jlshoop@.hotmail.com> wrote in message news:Ot6P$$MHEHA.3196@.tk2msftngp13.phx.gbl...
How do you setup replication without using window authentication when trying to replicate from a domain to a workgroup in the DMZ, without using trusts?

Friday, February 17, 2012

Doing demos with SQL2K RS Developer

Hi there.
I need to know if there's a proper setup to have SQL 2k Developer Edition
and SQL 2k RS Developer Edition on top of Win XP Pro to demo some reports.
I have locally installed VS .Net 2003 professional, SQL 2K Developer, SQL 2K
RS Developer, SQL 2K AS and IIS on top of Windows XP and RS does not ran if
I'm not connected to corporate network.
I have no way to make RS work on the road or at customers networks to show
some nice reports we have built...
I even installed SQL 2K RS Sp1 and it has no effect
Do I need to format my notebook and Install Win 2000?
Is it a setup problem?
Is it a licensing restriction?
Thank you very much for your support
Michael PrendergastSearch this newsgroup for "loopback":
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/search?group=microsoft.public.sqlserver.reportingsvcs&q=loopback&qt_g=1&searchnow=Search+this+group
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MPS" <mps@.udd.cl> wrote in message
news:u$M4kf29EHA.2996@.TK2MSFTNGP10.phx.gbl...
> Hi there.
> I need to know if there's a proper setup to have SQL 2k Developer Edition
> and SQL 2k RS Developer Edition on top of Win XP Pro to demo some reports.
> I have locally installed VS .Net 2003 professional, SQL 2K Developer, SQL
> 2K RS Developer, SQL 2K AS and IIS on top of Windows XP and RS does not
> ran if I'm not connected to corporate network.
> I have no way to make RS work on the road or at customers networks to show
> some nice reports we have built...
> I even installed SQL 2K RS Sp1 and it has no effect
> Do I need to format my notebook and Install Win 2000?
> Is it a setup problem?
> Is it a licensing restriction?
> Thank you very much for your support
> Michael Prendergast
>

Doing away with like '%%'

Here's the setup

@.searchcondition varchar(50) --is a parameter passed into a stored procedure

IF(@.searchconditions is null)

BEGIN

SET @.searchcondition = '%'

END

ELSE

BEGIN

SET @.searchcondtion = '%' + @.searchcondition + '%'

END

select * from sometable

where somecolumn like @.searchcondition

Now the procedure is fine if the user passes a string, but if it's null, it seems really inefficient to search for '%'. Is there a generalized approach, other than say putting slightly different versions of the query in an if block?

Have you considering using the Fulltext features in SQL Server 2005 rather than LIKE?|||I'm trying to make minor optimizations to a setup I do not have full control of. Thanks for the pointer to the fulltext stuff though, I'll definitely be using that on my future projects.|||

Did you already try the following:

select * from sometable

where (somecolumn like @.searchcondition) or (@.searchcondition is null)

|||Nice, works like a charm. Thanks for the tip.

Thursday, March 29, 2012

Dreaded TEXT column

Tuesday, March 27, 2012

DR Question

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Thursday, March 22, 2012

Download for redistributable SQL Server Express Edition?

Sunday, March 11, 2012

Double left joins with conditions

Double inserts during snapshot posting of transactional replication

Friday, March 9, 2012

Dos

Wednesday, March 7, 2012

Don't send report if it has no data

Sunday, February 26, 2012

Don't email report if no results from query

Don't email report if no results from query

Domain to DMZ Question

Friday, February 17, 2012

Doing demos with SQL2K RS Developer

Doing away with like '%%'

Drill down slow

Blog Archive

About Me