|
On a first look: too many subqueries.
Have a look here: How To: Optimize SQL Queries[^].
I would suggest you to ask Google with sentence: "query performance".
|
|
|
|
|
Maciej Los wrote: too many subqueries.
 I try to avoid subqueries; JOINs tend to work better in many situations.
|
|
|
|
|
Consider changing Top(1) for Max([Time]), but with a proper indexing you shouldn't notice a to big difference.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
I've built a RDL with SRSS 2008 (actually it's migrated from 2005 by opening and saving in SRSS 2008)
There's a group which its visibility is controlled by input parameters. (Static) rows inside the group sets RepeatOnNewPage as True and KeepWithGroup attribute is set to "Before" as footer also.
I found that the group keeps repeating on each page just only missing in the second last page.
I have tried to set the RepeatOnNewPage be either True or False for the group but the result still be same (missing the footer in 2nd last page).
I am not sure why this happening and hope if some advise can be provided. Thanks very much.
Source can be provided as request.
|
|
|
|
|
|
Marked as spam.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Greetings,
below command works fine. but everytime when i want to update another quess and buildingpoint .i need to change quess_2 ,q2buildingpoint.So i write all 12 ques and 12 builgpoints .So my question is i want output like this with stored procedures.Can anyone please suggest or modify my stored procedure.
output
exec 2,5,5 ---this out put effects in survey table (cid,ques_5 ,q5buildpoint)
exec 3,5,5 ---this out put effects in survey table (cid,ques_5 ,q5buildpoint)
UPDATE survey SET
ques_1 = (select response from surveyanswers t2 where id=@response),q1buildpoint=(select response from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
can i write procedure like this .it thows errors .can anyone please suggset something on this below procedure.How can i add if else conditions
ALTER PROCEDURE [dbo].[UpdateSurvey]
-- Add the parameters for the stored procedure here
@ID int,@response nvarchar(50),@buildingpoint nvarchar(50)
AS
BEGIN
if(@ID=1) then
Begin
UPDATE survey SET
ques_1 = (select response from surveyanswers t2 where id=@response),q1buildpoint=(select response from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID=2) then
Begin
UPDATE survey SET
ques_2a = (select response from surveyanswers t2 where id=@response),q2abuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_2b = (select response from surveyanswers t2 where id=@response),q2bbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=4) then
Begin
UPDATE survey SET
ques_2c = (select response from surveyanswers t2 where id=@response),q2cbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_3 = (select response from surveyanswers t2 where id=@response),q3buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_4 = (select response from surveyanswers t2 where id=@response),q4buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_5 = (select response from surveyanswers t2 where id=@response),q5buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_6 = (select response from surveyanswers t2 where id=@response),q6buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_7 = (select response from surveyanswers t2 where id=@response),q7buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_8 = (select response from surveyanswers t2 where id=@response),q8buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_9 = (select response from surveyanswers t2 where id=@response),q9buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_10 = (select response from surveyanswers t2 where id=@response),q10buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_11 = (select response from surveyanswers t2 where id=@response),q11buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_12= (select response from surveyanswers t2 where id=@response),q12buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
End
End
------
--exec UpdateSurvey 10,19,19
--select * from survey
--select * from surveyanswers
Errors
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 58
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 71
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 71
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 82
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 82
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 93
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 93
Incorrect syntax near the keyword 'then'.
|
|
|
|
|
"Then" is not part of the SQL-IF construction. See here[^].
If the error says "incorrect syntax", then there's an error in parsing the command; it means that the computer doesn't understand what you want. Easiest solution to those errors is to verify the syntax (the way the command is written) using MSDN.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
ALTER PROCEDURE [dbo].[UpdateSurvey]
@ID int,@response nvarchar(50),@buildingpoint nvarchar(50),@ID1 int
AS
BEGIN
SET NOCOUNT ON;
if(@ID1=1)
Begin
UPDATE survey SET
ques_1 = (select response from surveyanswers t2 where id=@response),q1buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID1=2)
Begin
UPDATE survey SET
ques_2a = (select response from surveyanswers t2 where id=@response),q2abuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID1=3)
Begin
UPDATE survey SET
ques_2b = (select response from surveyanswers t2 where id=@response),q2bbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=4)
Begin
UPDATE survey SET
ques_2c = (select response from surveyanswers t2 where id=@response),q2cbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=5)
Begin
UPDATE survey SET
ques_3 = (select response from surveyanswers t2 where id=@response),q3buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=6)
Begin
UPDATE survey SET
ques_4 = (select response from surveyanswers t2 where id=@response),q4buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=7)
Begin
UPDATE survey SET
ques_5 = (select response from surveyanswers t2 where id=@response),q5buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=8)
Begin
UPDATE survey SET
ques_6 = (select response from surveyanswers t2 where id=@response),q6buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=9)
Begin
UPDATE survey SET
ques_7 = (select response from surveyanswers t2 where id=@response),q7buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=10)
Begin
UPDATE survey SET
ques_8 = (select response from surveyanswers t2 where id=@response),q8buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
output
exec UpdateSurvey 5,5,5,2(id,response,buildingpoint,@ID1=2)
but instead of updating @ID1=2 ,it updates @ID1=5 .BUT I want to update @ID1=2 query .Is there any solution for this
|
|
|
|
|
"Debugging"; as far is I can see, it'd select the "Else If(@ID1=5)", and execute that query. Replace those queries with a "print" statement, and see if it switches to the right one.
|
|
|
|
|
Hi,
Well, this is kind of embarrassing, i always used the GUI for altering the database tables in sql server 2005. Now, in sql server 2008, I need to write sql statements to do this.
I need to alter the existing column, and set it to not null with default value 0.
I got the not null part working, get error on default value.
alter table dbo.mytable
alter column [VendorLevelId] INT Not NULL Default 0
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Default'.
|
|
|
|
|
Well, that's odd! BOL[^] says you should be able to use alter column ... set default x to set the default value, but it doesn't seem to work.
Try using three statements:
ALTER TABLE dbo.mytable
ADD CONSTRAINT DF_mytable_VendorLevelId DEFAULT (0) FOR VendorLevelId;
UPDATE dbo.mytable
SET VendorLevelId = 0
WHERE VendorLevelId Is Null;
ALTER TABLE dbo.mytable
ALTER COLUMN VendorLevelId int NOT NULL;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Here's an example from BOL (2k8):
CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
Straightforwardly:
SELECT * FROM dbo.doc_exy
Gets:
10
Now, doing the operation appears to suggest that no default value is ever needed:
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
GO
SELECT * FROM dbo.doc_exy
Shows what I mean:
10.00
DROP TABLE dbo.doc_exy ;
GO
|
|
|
|
|
Two of my OLAP databases disappeared in the last few days. Thankfully I had a backup.
However, I would want to investigate the root cause. How do I proceed?
|
|
|
|
|
Check who has access to the system, look at the audit logs etc.
Veni, vidi, abiit domum
|
|
|
|
|
There's also a chance that if you are not secured with regards to sql injection attacks - that you have just been the victim of one.
Dropping tables is a fairly classic symptom of an sql injection attack.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Does anyone have a code snippet that shows how to connect to a MariaDB database? Many of the examples I've found point to a MySql-compatible assembly. Is that the way you have to do it? I have no idea what assemblies to add to my references in a test project. I've not used MySql in donkey's years so I have absolutely no idea how to kick-start things using MariaDB.
Once I know what references to add and the syntax of a connection string particular to MariaDB's requirements I'll be okay from there.
TIA.
Edit: Forgot to mention I d/l the version 10.0 beta. Everything installed okay but I don't have any references visible to either MySql or MariaDB in a dotnet project.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
modified 12-Nov-13 10:07am.
|
|
|
|
|
the MySQLConenctor is normally a seperate installation from MySQL Connector[^]
once you download and install that usually you just add a reference to the project like you would do any normal installed .net library.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Thanks a lot Simon. I downloaded it and ran the install. I added a reference to the assembly in the installation folder and I can see a shed-load of MySql classes. I'll wire one up to my test database and see if it can connect to it. So, I'm a step on the road with a way to go...
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
I downloaded and installed the file that Simon provided at this link[^] (select the one called ADO.NET Driver for MySQL (Connector/NET). When you follow the download links the sly folks at Oracle/MySql try to make logging in to their website "mandatory" and it's easy to miss the tiny "No thanks, just start my download" link below it, so there's no need to register. When you've downloaded and installed it create a project and add an assembly reference to "C:\Program Files (x86)\MySQL\MySQL Connector Net 6.7.4\Assemblies\v4.0\MySql.Data.dll" (obviously, select your Program Files location and the framework version as appropriate).
If you have MariaDB installed chances are you have the IDE tool, HeidiSQL installed. I used that to create a new session, database and a table. In HeidiSQL, the menu Tools -> User manager is where I created a user registration and pointed it to "home", 127.0.0.1 and I set the access rights to full, the usual suspects.
Here's a code fragment I slapped into a console app (apologies if it doesn't format nicely):
string connStr = "server=127.0.0.1;uid=DogzBolx;pwd=password;database=imagecatalog;";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
string sql = "select * from data order by count desc;";
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataTable data = new DataTable();
da.Fill(data);
foreach(DataRow row in data.Rows)
{
string md5 = Convert.ToString(row[0]);
int count = Convert.ToInt32(row[1]);
Console.WriteLine(md5 + " " + count);
}
conn.Close();
Excuse my glib database name.  I ran the app and (bless my cotton socks) it worked! Anyway, it works fine, and it's rather jolly quick. Whether MariaDB can do what I'm familiar with using SQL Server I can't say but it seems to offer a lot of things. HeidiSQL itself is rather nice. I don't know if there are other tools to maintain MariaDB databases but seeing it's all part of the MariaDB-related family it's certainly works just fine. That's my $0.02c worth. 
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
modified 14-Nov-13 7:24am.
|
|
|
|
|
I need to use a function inside my database that need have the option Concat NULL yields NULL ON, however my database doesn't use that, so , I need to ensure that'll be ON when the function executes.
But seens not possible to define that inside the function ( Set .. On, is not allowed ).
There's any way to do that?
|
|
|
|
|
|
So code around it, ISULL(FieldName,'') will do for text fields
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Microsoft have already stated[^] that the option to turn CONCAT_NULL_YIELDS_NULL off will be removed in a future version. You should probably be looking to update the database so that it works with this setting turned on.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yeah, we should start think about it here, thank you. The problem is that we're using that on the return of a XML. The error are on the XML return. to solve, before call the function we got to use this.
SET CONCAT_NULL_YIELDS_NULL ON;
GO
This is our function
CREATE FUNCTION Function_StripHTML(@Text NVARCHAR(MAX), @BreakLine BIT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @XML AS XML
IF @BreakLine = 1
BEGIN
SET @Text= REPLACE(REPLACE(REPLACE(@Text,'<br>', CHAR(13)), '<br/>',CHAR(13)), '<br />',CHAR(13))
END
ELSE
BEGIN
SET @Text= REPLACE(REPLACE(@Text, '<br/>','<br>'), '<br />','<br>')
END
SET @XML = CAST(('<x>' + REPLACE(REPLACE(dbo.Function_RemoveInvalidCharacters(@Text), '&', '&'), '<', '</x><x>')+'</x>') AS XML)
SELECT
@Text=
(
SELECT
CASE WHEN LEFT(N.value('.', 'VARCHAR(MAX)'), 3) = 'br>' THEN
'<' + N.value('.', 'VARCHAR(MAX)')
ELSE
STUFF
(
N.value('.', 'VARCHAR(MAX)')
, 1
, CHARINDEX('>', N.value('.', 'VARCHAR(MAX)'))
, ''
)
END
FROM
@XML.nodes('x') AS T(N)
FOR XML PATH('')
, TYPE
).value('.', 'VARCHAR(MAX)')
RETURN LTRIM(RTRIM(@Text))
END
GO
I'm sorry if the code looks confuse, was made for a coworker here. 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
