Database

Time out only the first time

4-Dec-08 3:54

You can use for example sp_helprole[^] to list all roles in a database.

The need to optimize rises from a bad design.My articles[^]

samerh3-Dec-08 21:02

samerh

3-Dec-08 21:02

Iam using a table variable inside a stored procedure to search for a name. When executing the stored procedure for the first time in my asp.net web application it times out, while the second time it takes 3 seconds and less.

How can i getrid of this time out the first time?

Here is my SP:

ALTER PROCEDURE [dbo].[SearhBYFullName]
@FullName nvarchar(250)=null,
@Language varchar(10)=null,
@TableName varchar(50)=null,
@BadInfo bit=null,
@Client bit=null,
@Source nvarchar(50)=null,
@top int=1000,
@Filter varchar(10)=null
AS
BEGIN
Declare @Tmp_SearchByName table(
pk int identity(1,1),
RefId int ,
FullName nvarchar(200),
TableName varchar(50),
TableOrder varchar(50))

insert into @Tmp_SearchByName(RefId,FullName, TableName, TableOrder)
(
SELECT DISTINCT RefId,FullName, TableName, TableOrder from table1 where FullName=@FullName
union
SELECT DISTINCT RefId,FullName, TableName, TableOrder from table2 where FullName=@FullName
union
SELECT DISTINCT RefId,FullName, TableName, TableOrder from table3 where FullName=@FullName
union
SELECT DISTINCT RefId,FullName, TableName, TableOrderc from table4 where FullName=@FullName
union
SELECT DISTINCT RefId,FullName, TableName, TableOrder from table5 where FullName=@FullName
)

SELECT distinct top(@top) tmp.RefId, tmp.TableName, tmp.TableOrder, tmp.FullName,
Reference.RefSourceId, Reference.RefNumber, StRefSource.Code as source,
StRefSource.Description
FROM @Tmp_SearchByName tmp
INNER JOIN Reference ON tmp.RefId = Reference.RefID
INNER JOIN StRefSource ON Reference.RefSourceId = StRefSource.Id
left outer join vw_SelectClients as BA on BA.Refid=tmp.RefId
where ((tmp.TableName=@TableName and @TableName is not null) or @TableName is null)
and ((BA.Code='BA' and @Client is not null) OR @Client is null)
order by tmp.TableOrder,StRefSource.Code,tmp.FullName,tmp.RefId

Re: Time out only the first time

Wendelius4-Dec-08 4:03

Need to execute a query when postgres service starts

4-Dec-08 4:03

Without any information about the data amounts etc I would guess that the operation just takes too long.

Why this happens only for the first time sounds like that second time you execute this, buffer manager has enough data to execute the whole thing in acceptable time. This isn't the case when executing the procedure first time (most likely because of high physical I/O amount).

Based on the statements I would say that you need to optimize your statements. Investigate the execution plan for all of the statements and try to eliminate the bottlenecks. To achieve good performance results you may even have to rethink the e/r-model.

The need to optimize rises from a bad design.My articles[^]

krishnan.s3-Dec-08 20:45

krishnan.s

3-Dec-08 20:45

Hi,
I need to exceute a query in my DB when the postgres service starts .
Is there any means to achieve this.
I tried calling an exe when system starts it worked but need to exceute as soon as the service starts .

Thanks

Re: Need to execute a query when postgres service starts

Wendelius4-Dec-08 7:06

To apply a search on every table in a database

4-Dec-08 7:06

I don't know if Postgres supports executing statements at startup, but one way to do this is to create a simple wrapper which:
- starts up the database
- gets a connection
- executes the statement.

In this case you would set the postgres service startup option to manual and start it using the wrapper. If you want, you can create the wrapper as a service which then again is set to start automatically.

The need to optimize rises from a bad design.My articles[^]

rahuladya3-Dec-08 19:49

rahuladya

3-Dec-08 19:49

hello forum

i want to give an option of search on frontend (asp.net) that will find the given keyword in selected columns of each table present in database
how can i do that perfectly and without effecting the performance.

How to create a stored procedure to apply a search on every table in a database

can anybody help me.

regards
rahul

Ashfield3-Dec-08 21:09

Ashfield

3-Dec-08 21:09

Assuming SQL Server 2005/2008 there is a built in command called sp_MSForEachTable

exec sp_MSForEachTable @command1 = "select col1 from ? where x = y"

Note the use of double quotes around the command. When the query runs it replaces the question mark with the table name. I have a feeling you made need single quotes around the question mark, but I don't have sql server on this pc to check.

Hopefully this will get you in the right direction

Bob
Ashfield Consultants Ltd

rahuladya4-Dec-08 6:29

rahuladya

4-Dec-08 6:29

Thanx Friend

I m working on that....

regards
Rahul

Ashfield4-Dec-08 9:18

Ashfield

4-Dec-08 9:18

You're welcome, I hope you get the result you need

Bob
Ashfield Consultants Ltd

rahuladya4-Dec-08 20:55

rahuladya

4-Dec-08 20:55

hello Bob

I did some job on that but the output is not according to my desire.
Let me explain
the Demo tables are
txnNews
NewsId Title News

txnWallpapers
WallpaperID Name Path

txnHoroscope
SunSignId Horoscope

Now i want to do search on the News and title columns of txnNews on Name column of txnWallpapers and Horoscope column of txnHoroscope

the code that i used is

declare @search varchar(128)
declare @mycommand varchar(128)
declare @mywhereand varchar(128)
declare @returnvalue varchar(156)
declare @colname varchar (50)
set @search = '%India%'


set @mycommand = 'select * from ? where news like '''+ @search+''''
exec @returnvalue= sp_MSforeachtable 
                @command1 = @mycommand


print @returnvalue

that gives an error for the other two tables of invalid column

what should i do

regards
rahul

Reporting Services and multiple connection strings

Mycroft Holmes4-Dec-08 21:54

Mycroft Holmes

4-Dec-08 21:54

Think, if that is the error you are getting what do you THINK may be causing the problem. The columns are different in each table, you expect SQL to be telepathic. Also you don't need to search EVERY table in the database which this will do. looking for the same column in EVERY table.

Your requirement is to search different columns in 3 tables, write 3 select statements and deal with the result sets.

Never underestimate the power of human stupidity
RAH

gantww3-Dec-08 11:20

gantww

3-Dec-08 11:20

Hello all,
We're about to start using reporting services. It looks like it will do most things we want, however, we are having some trouble figuring out how to do things in a way that doesn't create more work for us long term. We would like to set things up so that our ASP.NET applications that call reporting services can specify which connection the reports actually run against. In other words, in dev, we want to make sure it is using the dev connection, while in live, we obviously want the real data. The suggestions I've seen so far are as follows:

1) Use multiple instances of reporting services, and use shared connections with different connection strings on each, then alter the URL you are calling to retrieve reports.
2) Have a dynamic connection string which uses data passed in as a parameter to determine the host and database to use for building the report.

Item #2 is pretty much out, since it would involve manually going back to a static connection string any time we want to edit a report. I can see potential for that breaking all over the place. So, is #1 the only way to do this or is there something easier and more obvious that we are missing? We currently have automated deployments for our other code, and we'd like to do this in a way that will eventually allow the same approach.

Any thoughts?

Will

Re: Reporting Services and multiple connection strings

moon_stick3-Dec-08 22:48

moon_stick

3-Dec-08 22:48

I think SSRS does this automatically - as long as you configure the Data Source through the web front end and make sure it's named the same in each environment it should be okay.

I've got projects that I deploy between different environments and don't have to make any changes to the data source. It might be worth pointing out that when deploying to the remote environment I upload the RDL files manually rather than deploying from the VS Solution (as VS isn't installed on the UAT and Production machines) though you can probably get the same end result by changing your project properties so that the datasource isn't overwritten on deployment.

At any rate, I don't remember having to do anything particularly clever when I installed it last (though that was over a year ago...!)

It definitely isn't definatley

Re: Reporting Services and multiple connection strings

Mycroft Holmes4-Dec-08 21:58

Mycroft Holmes

4-Dec-08 21:58

We use #2, the dynamic connection string and pass in the server as a parameter. Works fine for us, the developer is so used to the process of changing servers when modifying a report that it is now automatic. A total PITA for the rest of us who rarely have to get into the reports. B/C as report can run against any of 4-5 servers this work perfectly.

Never underestimate the power of human stupidity
RAH

Query

CodingYoshi3-Dec-08 9:15

CodingYoshi

3-Dec-08 9:15

I have a table with three columns: EmployeeID, Target, Date

Every employee is assigned a target (amount in dollars) to try to hit on every date. How do I get the latest target each employee has been assigned ordered by the employeeID. I tried the following but it is not accurate:

SELECT EmployeeID, MAX(Date), Target
FROM Target
GROUP BY EmployeeID, Target
ORDER BY EmployeeID

This query does not only return the latest target date.

Re: Query

Wendelius3-Dec-08 9:32

Accommodating an apostrophe in SQL... [modified]

3-Dec-08 9:32

CodingYoshi wrote:
How do I get the latest target each employee

You can either add HAVING clause after GROUP BY or eliminate previous dates like:

SELECT EmployeeID, Date, Target
FROM Target
WHERE Date = (SELECT MAX(DATE) FROM Target)                   
ORDER BY EmployeeID

The previous example is assuming that the date is the same for all. If it's not, use correlated subquery like

...
WHERE Date  = (SELECT MAX(DATE) FROM Target t2 WHERE t2.EmployeeID = target.EmployeeID)
...

The need to optimize rises from a bad design.My articles[^]

new_phoenix3-Dec-08 5:24

new_phoenix

3-Dec-08 5:24

I need some assistance in accommodating an apostrophe in an SQL statement using VBA in MS Access. Please help.

<br />
strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br />
   "[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br />
   "[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br />
   "WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _<br />
   "AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _<br />
   "AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _<br />
   "AND [ALT_GROUPING]='" & UCase(rstInputFile![ALT_GROUPING]) & "' " & _<br />
   "AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _<br />
   "AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _<br />
   "AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _<br />
   "AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'"<br />

What do I need to do to prevent it from hanging up on the apostrophe? The apostrophe needs to be accepted in:

<br />
"AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'" <br />

modified on Wednesday, December 3, 2008 11:31 AM

Jay Royall3-Dec-08 5:41

Jay Royall

3-Dec-08 5:41

You need to replace the apostrophy with 2 apostrophies. Best way is probably through a function (although I'm not sure of the exact syntax for VBA) - something like :

Public Function MakeSqlSafe(strData) as string
Return strData.Replace("'", "''")
end function

and then pass each one of your inputs through the function, e.g.

'" & MakeSqlSafe(UCase(rstInputFile![REGION])) & "'

Hope this helps.

new_phoenix3-Dec-08 6:03

new_phoenix

3-Dec-08 6:03

Thank you, liqz for your assistance.

The item that I have encountered in the implementation in the temporary copy of the application is: "Return without GoSub".

The code is as follows in VBA:

<br />
Public Function MakeSqlSafe(strData) As String<br />
    strNewStrData = Replace(strData, "'", "''")<br />
    Return<br />
End Function<br />
<br />
strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br />
  "[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br />
  "[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br />
  "WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _<br />
  "AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _<br />
  "AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _<br />
  "AND IsNull([ALT_GROUPING]) " & _<br />
  "AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _<br />
  "AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _<br />
  "AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _<br />
  "AND [JOB_FUNCTION]='" & MakeSqlSafe(UCase(rstInputFile![JOB_FUNCTION])) & "'"<br />

Any assistance in resolving this item would be greatly appreciated!!!

Jay Royall3-Dec-08 6:12

Jay Royall

3-Dec-08 6:12

Ah, try changing the function to :

Function MakeSqlsafe(strData)
     MakeSqlSafe = Replace(strData, "'", "''")
end Function

new_phoenix3-Dec-08 6:30

new_phoenix

3-Dec-08 6:30

It worked!!! Woo Hoo!!!

Thank you liqz, for your assistance in resolving this item!!!

Wendelius3-Dec-08 6:51