Recently I had a requirement to generate/convert varbinary column data to a csv file. Below is the way I achieved this.
select ‘bcp.exe “select [column(s)] from <DB Name>.<Schema>.<Table> where <Condition> ‘” queryout <filename>.csv -n -T -S “‘ + @@SERVERNAME + ‘”‘
Happy coding!!
Hi All,
Recently I had to write the following script on MS SQL Server to create a proc that would search through all the procedures in the database for a given search phrase. Guess it would be helpful for someone else as well.
create proc adm_SearchProcs
@searchText varchar(max)
as
begin
select ‘exec sp_helptext ‘ +””+ specific_schema +’.’ + routine_name +””
from information_schema.routines
where routine_definition like ‘%’+@searchText+’%’
order by 1
end
Happy Coding!!
Rasika
Hi All,
I recently came across the following errors on trying to do an integrity check on one of my DBs
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘LIVE.CBNHEA’ (ID 1652916960). Data row does not have a matching index row in the index ‘CBNHEA_ROWID’ (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3726817:1) identified by (HEAP RID = (1:3726817:1)) with index values ‘ROWID = 5679187 and HEAP RID = (1:3726817:1)’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘LIVE.CBNHEA’ (ID 1652916960). Data row does not have a matching index row in the index ‘CBNHEA_CBH0’ (ID 3). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3726817:1) identified by (HEAP RID = (1:3726817:1)) with index values ‘STOFCY_0 = ‘HLT’ and ITMREF_0 = ‘ and HEAP RID = (1:3726817:1)’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘LIVE.CBNHEA’ (ID 1652916960). Data row does not have a matching index row in the index ‘CBNHEA_CBH1’ (ID 4). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3726817:1) identified by (HEAP RID = (1:3726817:1)) with index values ‘STOFCY_0 = ‘HLT’ and LLC_0 = 99 and ITMREF_0 = ‘ and HEAP RID = (1:3726817:1)’.
CHECKDB found 0 allocation errors and 3 consistency errors in table ‘LIVE.CBNHEA’ (object ID 1652916960).
CHECKDB found 0 allocation errors and 3 consistency errors in database ‘xxxxx’.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (xxxxx).
Then I ran the dbcc CHECKDB with ‘repair_rebuild’ on single user mode and it fixed the issue straight away.
This is what I did;
1. Put the DB on single user mode – In order to do this, right-click on the DB and select properties and then go to options, in the options select “Restrict Access” and then select “Single_User” and click “Yes”. You will have to close the management studio and reconnect again before proceeding to the next step.
2. After login on to management studio open up a query window that points to the relevant DB. Type the following
dbcc CHECKDB(‘dbname’,‘repair_rebuild’)
3. run the dbcc CHECKDB(‘dbname’)
This time all the inconsistency errors are gone 🙂
Happy Coding 🙂
The following query filters the results set based on the time value only
Select * From <Table Name>
Where Convert(Varchar(8),<DateTime Field Name>,8) >= ‘<Time Value>’
In the above query it will compare a time value with the format of,
hh:mm:ss
If you don’t want the seconds portion of it simply reduce the length of the converted field
ie. Convert(varchar(5),<DateTime Field Name>,8) this will only return a time value with the format of,
hh:mm
Happy coding!!
Create Function GetNumberOfDaysInTheMonth(@PDate DATETIME) Returns INT
AS
BEGIN
RETURN CASE WHEN Month(@PDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN Month(@PDate) IN (4, 6, 9, 11) THEN 30
WHEN Month(@PDate)=2 THEN
CASE WHEN (Year(@PDate)%4 = 0) AND (Year(@PDate)%100 != 0 OR Year(@PDate)%400 = 0)
THEN 29
ELSE 28
END
END
END
Select * from <Table Name>
Where Convert(varchar(<length>),<DateTime Field Name>,<Date/Time Format Number>) = ‘<DateTime Value>’
Rasika's Blog 