In SQL FULLTEXT, we can discards commonly occurring string from build into FULLTEXT INDEX.
Example : is, an, i, we. Thease discarded string s are called stopwords
In SQL 2005 and below, stoplist is called noise word and define in noise file under fulltext database.
Noise word is applied through SQL Instance
In SQL 2008, we can define STOPLIST in database instance
There is 3 option to select when we create STOPLIST
Create an empty stoplist
Create from the system stoplist
Create from an exisiting full-text stop list
System stoplist
select * from sys.fulltext_system_stopwords
Current database stoplist
select * from sys.fulltext_stoplists
I try create stoplist in adventurework
but sql return with error
CREATE FULLTEXT STOPLIST myStoplist;
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'STOPLIST'."
I just notice in "Stopwords and Stoplists", it did mention :
"Important CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes."
I change the database compatibility level from 90 to level 100 and execute the query again
CREATE FULLTEXT STOPLIST myStoplist;
Stoplist create successful
Reference :
Stopwords and Stoplists
http://msdn.microsoft.com/en-us/library/ms142551.aspx
Wednesday, December 22, 2010
Tuesday, December 21, 2010
SQL 2008 express : The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]
Install SQL Express Edition with Advanced Service (64-bit)
After install successful, SQL Server start but $SQL Server Agent not start
Check from SQL Server Configuration Manager -> SQL Server Agent (MSSQLSERVER)
State -> Stopped
Right click ->Property->Service->Start Mode change from "Disable" to "Automatic" and press "Apply"
SQL Server give the below message :
"The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]"
Google and found this from Microsoft Site.
SQL Server Features that Are Not Supported in SQL Server Express
One of the component is "SQL Server Agent and SQL Server Agent Service"
That is nothing wrong with installation but SQL Server Agent and SQL Server Agent Service by design is not supported in SQL Server Express 2008
After install successful, SQL Server start but $SQL Server Agent not start
Check from SQL Server Configuration Manager -> SQL Server Agent (MSSQLSERVER)
State -> Stopped
Right click ->Property->Service->Start Mode change from "Disable" to "Automatic" and press "Apply"
SQL Server give the below message :
"The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]"
Google and found this from Microsoft Site.
SQL Server Features that Are Not Supported in SQL Server Express
One of the component is "SQL Server Agent and SQL Server Agent Service"
That is nothing wrong with installation but SQL Server Agent and SQL Server Agent Service by design is not supported in SQL Server Express 2008
Monday, December 20, 2010
SQL 2008 Table data type
My developer ask me question about table data type.
Google and found this new features sql server 2008-user defined table type and table valued parameters and this Passing table valued parameters in SQL Server 2008
We have the same scenario but with additional step
When we execute the store procedure with pass in parameter, we want
1. Check whether the data already exists in table.
2. If exists, update the data with new value
3. If not exists, insert the data
In this store procedure, we have this :
CREATE procedure insertintocustomer(@Cust_ID int, @Cust_Name varchar(50),@Cust_Surname varchar(50),@Cust_Email varchar(50))
as
begin
If exists (select 1 from customers where cust_id=@Cust_ID)
Begin
update customers
set cust_surname=@Cust_Surname, Cust_Email=@Cust_Email
where cust_id=@Cust_id
END
else
BEGIN
insert into customers
values(
@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)
END
End
So we do this
alter procedure newcustomer(@Customer_details customertype READONLY)
as
begin
update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id
insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)
end
Google and found this new features sql server 2008-user defined table type and table valued parameters and this Passing table valued parameters in SQL Server 2008
We have the same scenario but with additional step
When we execute the store procedure with pass in parameter, we want
1. Check whether the data already exists in table.
2. If exists, update the data with new value
3. If not exists, insert the data
In this store procedure, we have this :
CREATE procedure insertintocustomer(@Cust_ID int, @Cust_Name varchar(50),@Cust_Surname varchar(50),@Cust_Email varchar(50))
as
begin
If exists (select 1 from customers where cust_id=@Cust_ID)
Begin
update customers
set cust_surname=@Cust_Surname, Cust_Email=@Cust_Email
where cust_id=@Cust_id
END
else
BEGIN
insert into customers
values(
@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)
END
End
So we do this
alter procedure newcustomer(@Customer_details customertype READONLY)
as
begin
update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id
insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)
end
Tuesday, December 14, 2010
Using Inner join or Function(Case)
One of the developer using function in query to return the result that he needed. The query and write using inner join without function
What is the different ?
Example :
In adventurework :
create table HumanResources.Gender
(id char(1),
Name varchar(20))
insert into HumanResources.Gender
values ('M','This is Male')
insert into HumanResources.Gender
values ('F','This is Female')
create FUNCTION dbo.GetGender (@what char(1))
RETURNS varchar(20)
AS
BEGIN
Declare @whatGender varchar(20)
SELECT @whatGender=Name from HumanResources.Gender where id=@what
RETURN @whatGender
End
Run Display Estimated Execution plan
together for the above 2 query
What Estimated Execution plan above show?
First query Query cost is 69% compare to second query Cost 31% (25%+6%)
Is that mean second query better that first query ?
Let set statistics time on, click on
and actual run the query
set statistics time on
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
set statistics time off
Actual execution plan
From the actual exection plan
First query still have high relative cost compare to second query
From the statistics
First query CPU time = 0 ms, elapsed time = 83 ms.
compare to Second query CPU time = 15 ms, elapsed time = 91 ms.
Second query elapsed time more slower compare to first query and use more CPU resource.
Let turn on Profiler to see what is running when we execute the query
Open SQL Profiler and select only TSQL:SQL:Batch Complete and click run
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 15, Read 19, Write 0, Duration 85
In Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 31, Read 893, Write 0, Duration 85
It show First query use less resource and run more faster compare to Second query.
Go back to SQL Server Profiler, pause the trace
From File->Properties->Event Selection->Check on Show all events-> Go to TSQL Category and check on SP:StmtCompleted (SQL statement within a stored procedure has completed).
Press run to run SQL profiler again.
From Menu bar, click on "Clear Trace Window" to clear the early trace
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
Go back to SQL Profiler and check the result
That is only 1 line in the SQL Profiler, SQL:BatchCompleted with CPU 31, Read 893, Write 0, Duration 85
Let clear Trace Window in SQL Proiler to clear to trace result and go back to Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Profiler and check the result
SP:stmtCompleted with
- SELECT @whatGender=Name from HumanResources.Gender where id=@what
- RETURN @whatGender
Last result is SQL:BatchCompleted with query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
When we execute Second query, SQL need to get Name value from HumanResources.Gender and return the value to GetGender function.
If we have 291 result return, SQL will execute (291*2)+1 time in SQL Server compare to first query which only execute one
From the result above, we can conclude first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
is better than second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
What is the different ?
Example :
In adventurework :
create table HumanResources.Gender
(id char(1),
Name varchar(20))
insert into HumanResources.Gender
values ('M','This is Male')
insert into HumanResources.Gender
values ('F','This is Female')
create FUNCTION dbo.GetGender (@what char(1))
RETURNS varchar(20)
AS
BEGIN
Declare @whatGender varchar(20)
SELECT @whatGender=Name from HumanResources.Gender where id=@what
RETURN @whatGender
End
Run Display Estimated Execution plan
together for the above 2 query
What Estimated Execution plan above show?
First query Query cost is 69% compare to second query Cost 31% (25%+6%)
Is that mean second query better that first query ?
Let set statistics time on, click on
and actual run the queryset statistics time on
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
set statistics time off
Actual execution plan
From the actual exection plan
First query still have high relative cost compare to second query
From the statistics
First query CPU time = 0 ms, elapsed time = 83 ms.
compare to Second query CPU time = 15 ms, elapsed time = 91 ms.
Second query elapsed time more slower compare to first query and use more CPU resource.
Let turn on Profiler to see what is running when we execute the query
Open SQL Profiler and select only TSQL:SQL:Batch Complete and click run
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 15, Read 19, Write 0, Duration 85
In Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 31, Read 893, Write 0, Duration 85
It show First query use less resource and run more faster compare to Second query.
Go back to SQL Server Profiler, pause the trace
From File->Properties->Event Selection->Check on Show all events-> Go to TSQL Category and check on SP:StmtCompleted (SQL statement within a stored procedure has completed).
Press run to run SQL profiler again.
From Menu bar, click on "Clear Trace Window" to clear the early trace
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
Go back to SQL Profiler and check the result
That is only 1 line in the SQL Profiler, SQL:BatchCompleted with CPU 31, Read 893, Write 0, Duration 85
Let clear Trace Window in SQL Proiler to clear to trace result and go back to Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Profiler and check the result
SP:stmtCompleted with
- SELECT @whatGender=Name from HumanResources.Gender where id=@what
- RETURN @whatGender
Last result is SQL:BatchCompleted with query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
When we execute Second query, SQL need to get Name value from HumanResources.Gender and return the value to GetGender function.
If we have 291 result return, SQL will execute (291*2)+1 time in SQL Server compare to first query which only execute one
From the result above, we can conclude first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
is better than second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
SQL Change column name and type
I want change existing table column name and data type
The faster way is drop and create
If table already have data inside and we don't wanna recreate table, we can do this :
Original table structure
create table HumanResources.Gender2
(id char(1),
lang varchar(20),
Name varchar(20))
Execute below query to change column name
sp_RENAME 'HumanResources.Gender2','lang', 'lang_id' , 'COLUMN'
Caution: Changing any part of an object name could break scripts and stored procedures.
Identify store procedure which reference to Gender2.lang table
Example :
create procedure GetGender2
@id int
as
select lang,name from Gender2 where
id =@id
To identify table, trigger, view reference to Gender2.lang table, execute this
sp_depends 'Gender2'-- Gender2 is the table name
In the current database, the specified object is referenced by the following:
name type
dbo.GetGender2 stored procedure
After change the column name and excute the above store procedure, SQL will give this error message:
Msg 207, Level 16, State 1, Procedure GetGender2, Line 4
Invalid column name 'lang'.
We should change all store procedure which reference to this table
In this case, we need change Store Procedure GetGender2, change lang to lang_id
alter procedure GetGender2
@id int
as
select lang_id,name from Gender2 where
id =@id
I execute the second query to change the data type
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
Data type change successful
If original table already have data and data as below:
insert into Gender2
values ('English','This is Male')
insert into Gender2
values ('English','This is Female')
When we run
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
SQL will give error :
- Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'English' to data type int.
The statement has been terminated.
We fail to change the data type because original data is character, we can't change the data type to integer.
Reference :
SQL SERVER – How to Rename a Column Name or
Table Name
Listing SQL Server Object Dependencies
The faster way is drop and create
If table already have data inside and we don't wanna recreate table, we can do this :
Original table structure
create table HumanResources.Gender2
(id char(1),
lang varchar(20),
Name varchar(20))
Execute below query to change column name
sp_RENAME 'HumanResources.Gender2','lang', 'lang_id' , 'COLUMN'
Caution: Changing any part of an object name could break scripts and stored procedures.
Identify store procedure which reference to Gender2.lang table
Example :
create procedure GetGender2
@id int
as
select lang,name from Gender2 where
id =@id
To identify table, trigger, view reference to Gender2.lang table, execute this
sp_depends 'Gender2'-- Gender2 is the table name
In the current database, the specified object is referenced by the following:
name type
dbo.GetGender2 stored procedure
After change the column name and excute the above store procedure, SQL will give this error message:
Msg 207, Level 16, State 1, Procedure GetGender2, Line 4
Invalid column name 'lang'.
We should change all store procedure which reference to this table
In this case, we need change Store Procedure GetGender2, change lang to lang_id
alter procedure GetGender2
@id int
as
select lang_id,name from Gender2 where
id =@id
I execute the second query to change the data type
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
Data type change successful
If original table already have data and data as below:
insert into Gender2
values ('English','This is Male')
insert into Gender2
values ('English','This is Female')
When we run
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
SQL will give error :
- Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'English' to data type int.
The statement has been terminated.
We fail to change the data type because original data is character, we can't change the data type to integer.
Reference :
SQL SERVER – How to Rename a Column Name or
Table Name
Listing SQL Server Object Dependencies
SQL 2008 Free Learning Resource Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability
Read Kimberly L. Tripp Blog, and found this
"When SQL Server 2008 was in beta, Paul and I worked on a project to create content that would "jumpstart" folks in learning and working with SQL Server 2008. We delivered these "new features" courses at various locations and finally, we recorded the content. All of this content is now available FOR FREE from Microsoft through their eLearning outlet"
Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability.
You need login into Microsoft Learning to start the Clinic. The good news is this is free and valid for one year!
"When SQL Server 2008 was in beta, Paul and I worked on a project to create content that would "jumpstart" folks in learning and working with SQL Server 2008. We delivered these "new features" courses at various locations and finally, we recorded the content. All of this content is now available FOR FREE from Microsoft through their eLearning outlet"
Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability.
You need login into Microsoft Learning to start the Clinic. The good news is this is free and valid for one year!
Thursday, December 09, 2010
You must use the Role Management Tool to install or configure Microsoft.NET Framework 3.5SP1
I want install SQL Express Version in Windows 2008 R2
When I Install SQL Server Express 2008, installation fail and give this error message
"need install Microsoft .NET Framework 3.5 SP1 Setup"
I Download and install Microsoft .NET Framework 3.5 SP1 Setup but fail
"You must use the Role Management Tool to install or configure Microsoft.NET Framework 3.5SP1."
From Server-> Start->Administrator tools->Server management ->Features -> Add Features->.NET Framework 3.5.1 Features
After installation, SQL intallation can contineous.
When I Install SQL Server Express 2008, installation fail and give this error message
"need install Microsoft .NET Framework 3.5 SP1 Setup"
I Download and install Microsoft .NET Framework 3.5 SP1 Setup but fail
"You must use the Role Management Tool to install or configure Microsoft.NET Framework 3.5SP1."
From Server-> Start->Administrator tools->Server management ->Features -> Add Features->.NET Framework 3.5.1 Features
After installation, SQL intallation can contineous.
Friday, November 26, 2010
SQL 2005 Self-Paced Lab from PSS
If you still using SQL 2005, or want to learn the below topic, go to download PSS Service Center Lab 2005
Blocking and Deadlocking
In these labs you will use SQLDiag, SQL Profiler, and various DMVs to solve blocking and deadlocking problems.
Performance
In these labs you will use SQLDiag, SQL Profiler, showplan, and ReadTrace to solve various query performance problems.
Debugging
In these labs you will learn more about debugging tools used by the PSS team such as SQLDumper and Windows Debugging Tools to solve problems like a crash of SQL Server.
Server and Data Recovery
In these labs you will learn to how to solve various server and database offline problems.
Blocking and Deadlocking
In these labs you will use SQLDiag, SQL Profiler, and various DMVs to solve blocking and deadlocking problems.
Performance
In these labs you will use SQLDiag, SQL Profiler, showplan, and ReadTrace to solve various query performance problems.
Debugging
In these labs you will learn more about debugging tools used by the PSS team such as SQLDumper and Windows Debugging Tools to solve problems like a crash of SQL Server.
Server and Data Recovery
In these labs you will learn to how to solve various server and database offline problems.
Friday, November 12, 2010
Netvault - Failed to Open file exclusions file
I using Netvault Backup to backup my database.
The Netvault backup has an option where we can exclude file with certain extention
I add a file with extention list want to exclude in backup
When Netvault start backup, it give this message :
Backup completed with warnings
I check Netvault log and found this
A problem occurred initializing items to exclude from the backup: "Failed to open file exclusions file 'c:\file\exclude.txt"
A problem occurred initializing items to exclude from the backup: "File exclusions file 'c:\file\exclude.txt" is not a valid Unicode file
Solve with :
Open and save exclude.txt as unicode file and backup successful without warning
The Netvault backup has an option where we can exclude file with certain extention
I add a file with extention list want to exclude in backup
When Netvault start backup, it give this message :
Backup completed with warnings
I check Netvault log and found this
A problem occurred initializing items to exclude from the backup: "Failed to open file exclusions file 'c:\file\exclude.txt"
A problem occurred initializing items to exclude from the backup: "File exclusions file 'c:\file\exclude.txt" is not a valid Unicode file
Solve with :
Open and save exclude.txt as unicode file and backup successful without warning
Friday, October 22, 2010
Query running in SQL Server
I use this sript to check what query is running in server
select r.blocking_session_id,r.session_id ,h.text,substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)
else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text , r.wait_type, r.wait_time , r.last_wait_type , r.wait_resource ,
r.command , r.database_id , r.granted_query_memory, r.reads , r.writes , r.row_count , s.[host_name] , s.program_name , s.login_name
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r on s.session_id =r.session_id and s.last_request_start_time=r.start_time
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h where is_user_process = 1 order by 2 desc
select r.blocking_session_id,r.session_id ,h.text,substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)
else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text , r.wait_type, r.wait_time , r.last_wait_type , r.wait_resource ,
r.command , r.database_id , r.granted_query_memory, r.reads , r.writes , r.row_count , s.[host_name] , s.program_name , s.login_name
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r on s.session_id =r.session_id and s.last_request_start_time=r.start_time
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h where is_user_process = 1 order by 2 desc
Thursday, October 21, 2010
SQL 2005 SSIS FAIL TO SAVE PACKAGE file error 0x80040155 "interface not registered"
When I create new project in SSIS, it fail and give this message :
"FAIL TO SAVE PACKAGE file error 0x80040155 "interface not registered"
Google and get this answer:
regsvr32 msxml6.dll
regsvr32 msxml3.dll
I run the above in the command prompt and It succesful create Project in SSIS with no error.
"FAIL TO SAVE PACKAGE file error 0x80040155 "interface not registered"
Google and get this answer:
regsvr32 msxml6.dll
regsvr32 msxml3.dll
I run the above in the command prompt and It succesful create Project in SSIS with no error.
Friday, July 02, 2010
Objects in SQL File Group
I want know which object sit in SQL filegroup.
Do some google and found this script to list objects per filegroup
Do some google and found this script to list objects per filegroup
Tuesday, May 18, 2010
SQL Server 2005 Patch Fails to install with an error "Unable to install Windows Installer MSP file"
I try to install SQL 2005 Service Patch 3, but it fail to install
After some Google, find this solution useful. My SQL 2005 SP3 successful install
SQL Server 2005 Patch Fails to install with an error "Unable to install Windows Installer MSP file"
Reference:
Converting a Regular GUID to a Compressed GUID
After some Google, find this solution useful. My SQL 2005 SP3 successful install
SQL Server 2005 Patch Fails to install with an error "Unable to install Windows Installer MSP file"
Reference:
Converting a Regular GUID to a Compressed GUID
SSIS - Exception from HRESULT: 0x8007007E
I try to add SSIS to SQL jobs, but it gives me this erorr:
The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)
and the SSIS UI no show
The step can be repeat with:
Open Microsoft SQL Server Management Studio ->SQLServerName-> SQL Server Agent-> Job- >right click "new job" -> In General ->keying Job name- > In steps -> Press "New"-> In Type -> Choose "SQL Server Integration Services Package" and the error message will pop out
solution
1. My computer- > right click
2. Advance -> environment Variables
3. System variables- > path
** Please change with careful **
let say you have this in path
C:\WINDOWS\system32;C:\WINDOWS
append this line at behind
C:\WINDOWS\system32;C:\WINDOWS;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\
c:\program files\Microsoft SQL Server\90\DTS\Binn\ is the location of DTS\Binn in your server
please replace with the correct path
4. close the UI
5. Close Mircosoft SQL Server Management Studio and reopen it
6. Repeat the step, and no more error.
The above solution work for my server and problem solve.
Reference
http://www.sqlservercentral.com/Forums/Topic278644-148-1.aspx
http://narfle.com/blog/2008/02/ssis-subsystem-failed-to-load.asp
The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)
and the SSIS UI no show
The step can be repeat with:
Open Microsoft SQL Server Management Studio ->SQLServerName-> SQL Server Agent-> Job- >right click "new job" -> In General ->keying Job name- > In steps -> Press "New"-> In Type -> Choose "SQL Server Integration Services Package" and the error message will pop out
solution
1. My computer- > right click
2. Advance -> environment Variables
3. System variables- > path
** Please change with careful **
let say you have this in path
C:\WINDOWS\system32;C:\WINDOWS
append this line at behind
C:\WINDOWS\system32;C:\WINDOWS;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\
c:\program files\Microsoft SQL Server\90\DTS\Binn\ is the location of DTS\Binn in your server
please replace with the correct path
4. close the UI
5. Close Mircosoft SQL Server Management Studio and reopen it
6. Repeat the step, and no more error.
The above solution work for my server and problem solve.
Reference
http://www.sqlservercentral.com/Forums/Topic278644-148-1.aspx
http://narfle.com/blog/2008/02/ssis-subsystem-failed-to-load.asp
Wednesday, May 12, 2010
SQL Server Build List
SQL Server 2005 Build list maintain by By Steve Jone in sqlservercentral
http://www.sqlservercentral.com/articles/Administration/2960/
SQL Server 2008 Build list maintain by By Steve Jone in sqlservercentral
http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/
Another Source from sqlsecurity
http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx
Microsoft Security -> Select SQL 2005
http://www.microsoft.com/technet/security/current.aspx
http://www.sqlservercentral.com/articles/Administration/2960/
SQL Server 2008 Build list maintain by By Steve Jone in sqlservercentral
http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/
Another Source from sqlsecurity
http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx
Microsoft Security -> Select SQL 2005
http://www.microsoft.com/technet/security/current.aspx
Tuesday, March 23, 2010
What SQL job in SQL Server
My x colleague ask me how to query SQL job in SQL Server
Normally I use the below script
use msdb
select name from sysjobs
where enabled=1
Normally I use the below script
use msdb
select name from sysjobs
where enabled=1
Thursday, March 04, 2010
Media - For DBA
From Microsoft TechNet
example of Title :
LINQ and Entity Framework, for the DBA
SQL Services - What does it mean for the DBA
and lot of Real World DBA Episode ...
Find all media here from Microsoft Technet
example of Title :
LINQ and Entity Framework, for the DBA
SQL Services - What does it mean for the DBA
and lot of Real World DBA Episode ...
Find all media here from Microsoft Technet
Thursday, February 18, 2010
SQL Server 2005 ranking functions - RANK(), DENSE_RANK(), NTILE()
http://msdn.microsoft.com/en-us/library/ms189798.aspx
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
Good explanation of ranking function
Good example of ranking function
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
Good explanation of ranking function
Good example of ranking function
Friday, February 12, 2010
SQL Free monitor tools
SQL HeartBeat, Monitor your sql server from SQLSolutions
http://www.sqlsolutions.com/products/Heartbeat/index.html
SQL Check, Monitor your sql server from Idera
http://www.idera.com/Products/Free-Tools/SQL-check/
http://www.sqlsolutions.com/products/Heartbeat/index.html
SQL Check, Monitor your sql server from Idera
http://www.idera.com/Products/Free-Tools/SQL-check/
Monday, January 25, 2010
Login is locked out when enforce password policy is on
MY SQL 2005 login is set with “Enforce password policy” turn on
Check what mean by Enforce password policy:
In Start->Administrative tools->Local Security Settings
Security Settings-> Account Policies -> Account Lockout Policy
Account Lockout threshold - 5 invalid login attempts
If user keying 5 invalid login attempts, account will be lock.
Account lockout duration - 30 minutes
account will be lock for 30 min before automatically becoming unlocked
Reset account lockout counter after 30 minutes
This security setting determines the number of minutes that must elapse after a failed logon attempt before the failed logon attempt counter is reset to 0 bad logon attempts
I use test as login id and keying 5 invalid password.
I try next login with correct password, I can’t login into this SQL 2005 server.
I check from Microsoft SQL Server Management Studio Server name->Security-> test
Double click on test (login name), select Status->Under
Status -> SQL Server authentication ->
"Login is locked" out is checked.
It mean my login account been locked.
I unchecked "account been locked" and press "OK".
I login with the correct password, the login still fail.
I double click the SQL login again, "account been locked" is still "checked"
I unchecked Enforce password policy, unchecked Login is locked out" and press OK.
I login with correct password, but login still fail.
Solution:
1. Change password with TSQL
-ALTER LOGIN test WITH PASSWORD = 'complexpassword',CHECK_POLICY = ON;
-I login with complexpassword, login successful
2. Change password from Microsoft SQL Server Management Studio
-Microsoft SQL Server Management Studio -> Server name->Security-> test
-Double click on test (login name)->General -> Keying new password in Password and Confirm Password and press "OK"
-Double click on test (login name)->Status ->SQL Server authentication: -> Login is locked out
-I login with complexpassword, login successful
Reference
Account lockout duration
Reset account lockout counter after
Check what mean by Enforce password policy:
In Start->Administrative tools->Local Security Settings
Security Settings-> Account Policies -> Account Lockout Policy
Account Lockout threshold - 5 invalid login attempts
If user keying 5 invalid login attempts, account will be lock.
Account lockout duration - 30 minutes
account will be lock for 30 min before automatically becoming unlocked
Reset account lockout counter after 30 minutes
This security setting determines the number of minutes that must elapse after a failed logon attempt before the failed logon attempt counter is reset to 0 bad logon attempts
I use test as login id and keying 5 invalid password.
I try next login with correct password, I can’t login into this SQL 2005 server.
I check from Microsoft SQL Server Management Studio Server name->Security-> test
Double click on test (login name), select Status->Under
Status -> SQL Server authentication ->
"Login is locked" out is checked.
It mean my login account been locked.
I unchecked "account been locked" and press "OK".
I login with the correct password, the login still fail.
I double click the SQL login again, "account been locked" is still "checked"
I unchecked Enforce password policy, unchecked Login is locked out" and press OK.
I login with correct password, but login still fail.
Solution:
1. Change password with TSQL
-ALTER LOGIN test WITH PASSWORD = 'complexpassword',CHECK_POLICY = ON;
-I login with complexpassword, login successful
2. Change password from Microsoft SQL Server Management Studio
-Microsoft SQL Server Management Studio -> Server name->Security-> test
-Double click on test (login name)->General -> Keying new password in Password and Confirm Password and press "OK"
-Double click on test (login name)->Status ->SQL Server authentication: -> Login is locked out
-I login with complexpassword, login successful
Reference
Account lockout duration
Reset account lockout counter after
Friday, January 15, 2010
MSOLAP Provider used in connection string
We move asp page from live server to new setup server.
When asp page connect to our olap server, it show the below message:
Microsoft® OLE DB Provider for OLAP Services
error '80004005' OLAP server error: The operation requested failed due to security problems (the user could not be authenticated).
Our connection string as below and same with live server
provider=msolap;data source=servername;initial catalog=customer;user id=user;password=xxx;sspi=anonymous
After some Google and found this article by my colleague
How to check the MSOLAP Provider used in connection string
Some extraction of article as below:
MSOLAP ---Default Provider
MSOLAP.1 ---OLAP 7.0 Provider (msolap.dll)
MSOLAP.1 ---Analysis Service 2000 Provider (msolap80.dll)
MSOLAP.1 ---SSAS provider (msolap90.dll)
HKCR\MSOLAP ---is the default and used when MSOLAP is selected in the connection string, the file msolap.dll, msolap80.dll or msolap90.dll that is latest registered will be the default.
HKCR\MSOLAP.1 ---is the registry key for OLAP version 7, file msolap.dll
HKCR\MSOLAP.2 ---is the registry key for AS2000, file msolap80.dll
HKCR\MSOLAP.3 ---is the registry key for AS2005, file msolap90.dl
We change the connection string to
provider=msolap.3;data source=servername;initial catalog=customer;user id=user;password=xxx;sspi=anonymous
and it work
When asp page connect to our olap server, it show the below message:
Microsoft® OLE DB Provider for OLAP Services
error '80004005' OLAP server error: The operation requested failed due to security problems (the user could not be authenticated).
Our connection string as below and same with live server
provider=msolap;data source=servername;initial catalog=customer;user id=user;password=xxx;sspi=anonymous
After some Google and found this article by my colleague
How to check the MSOLAP Provider used in connection string
Some extraction of article as below:
MSOLAP ---Default Provider
MSOLAP.1 ---OLAP 7.0 Provider (msolap.dll)
MSOLAP.1 ---Analysis Service 2000 Provider (msolap80.dll)
MSOLAP.1 ---SSAS provider (msolap90.dll)
HKCR\MSOLAP ---is the default and used when MSOLAP is selected in the connection string, the file msolap.dll, msolap80.dll or msolap90.dll that is latest registered will be the default.
HKCR\MSOLAP.1 ---is the registry key for OLAP version 7, file msolap.dll
HKCR\MSOLAP.2 ---is the registry key for AS2000, file msolap80.dll
HKCR\MSOLAP.3 ---is the registry key for AS2005, file msolap90.dl
We change the connection string to
provider=msolap.3;data source=servername;initial catalog=customer;user id=user;password=xxx;sspi=anonymous
and it work
SQL process, percentage of complete
I restore one database and want know how many percent the SQL process will be complete?
Step
1. Restore database test from disk='c:\test.mdf'
2. We can check which session belong to this process with running the below command
sp_who2 'active'
3. Check on command column, and look for "Restore database"
My SPID for this "Restore database" = 57
4. Run the below command and look for session_id=57
Select session_id,percent_complete,estimated_completion_time,total_elapsed_time
From sys.dm_exec_requests
Where session_id=57
session_id percent_complete estimated_completion_time total_elapsed_time
---------- ---------------- ------------------------- ------------------
57 26.47547 5800366 2090266
The above result show the SQL process already complete 26.47%, estimated_completion_time=5800366 and total_elapsed_time=2090266
This above example is use to show how to use sys.dm_exec_requests to show how many percent_complete of SQL process.
We can also run the below command to show percentage complete of restore process.
Restore database test from disk='c:\test.mdf' with stats
10 percent processed.
20 percent processed.
30 percent processed.
....
Step
1. Restore database test from disk='c:\test.mdf'
2. We can check which session belong to this process with running the below command
sp_who2 'active'
3. Check on command column, and look for "Restore database"
My SPID for this "Restore database" = 57
4. Run the below command and look for session_id=57
Select session_id,percent_complete,estimated_completion_time,total_elapsed_time
From sys.dm_exec_requests
Where session_id=57
session_id percent_complete estimated_completion_time total_elapsed_time
---------- ---------------- ------------------------- ------------------
57 26.47547 5800366 2090266
The above result show the SQL process already complete 26.47%, estimated_completion_time=5800366 and total_elapsed_time=2090266
This above example is use to show how to use sys.dm_exec_requests to show how many percent_complete of SQL process.
We can also run the below command to show percentage complete of restore process.
Restore database test from disk='c:\test.mdf' with stats
10 percent processed.
20 percent processed.
30 percent processed.
....
Subscribe to:
Posts (Atom)