...it's me...eRiCk

hi..it's me :D

2021-03-24T15:42:00.004+07:00

it's been a long time since my last post

i will try to blog again in my journey

the journey paths will be in: Blazor, IIS, Crystal Report, SAP Business One, ClickOnce, Nuget, SQL Server Query.

most will be using MS tech of course

until then

SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

2017-08-02T09:49:00.002+07:00

Hi all,

Found another repeating issue regarding unable to connect to network SQL Server
So here is the step by step i made

Enable TCP /IP in SQL Server Configuration Manager
Open Port in Window Firewall
Enable Allow remote connections to this server in SQL Server
Enable SQL Server Browser Service

for step by step with Image please check this link. Credit goes to Pinal Dave

Value cannot be null when trying to get WebAPI Token using Postman

2017-06-26T16:30:00.003+07:00

Well,

Server Error in '/' Application.

Value cannot be null.
Parameter name: value

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentNullException: Value cannot be null.
Parameter name: value

Source Error:
Line 431:        {
Line 432:            AuthenticationManager.SignOut(DefaultAuthenticationTypes.ExternalCookie);
Line 433:            var identity = await UserManager.CreateIdentityAsync(user, DefaultAuthenticationTypes.ApplicationCookie);
Line 434:            AuthenticationManager.SignIn(new AuthenticationProperties() { IsPersistent = isPersistent }, identity);
Line 435: 

It happened because when i created the user using Configuration Seed (i'm using EF Code First), i'm not filled the Security Stamp that is why the error occured because when the Security Stamp is null, it can not generate Claims Identity

After i change the Seed to fill the Security Stamp then it works like a charm :D

until then

Session timeout problem in IIS

2017-06-20T10:27:00.002+07:00

Hi,

Long time no post, i will try to post again since i came out searching a typical solution over and over again :D

Now i'm gonna post about session. Since i got this from other link so here it is

So I started looking into the IIS settings and came to know that i missed two things to change :

Application pool’s Idle Time-out(minutes)

Session state – State server’s Time-out (seconds)

Once I have changed above settings than after Session timeout worked.

So after that I have made one checklist that can help us to validate Session timeout settings, here i am sharing it with you as well so that you will also get some benefits from it :

Application Pool – Advanced Settings Menu – Process Model – Idle Time-out (minutes)

Sites – Session State – Cookie Settings – Time-out (minutes)

If you are using State Server or SQL Server to manage your session (instead of InProcess), Here is the steps to follow : Sites – Session State – Session State Mode Settings – Time-out (seconds)

Under Web.config – system.web – authentication mode – forms – timeout (for form authentication)

That is all , no more things you need to set in order to increase session timeout from its default value.

Simple but very useful tips.

thanks for sharing it. Credit goes to Jay Ganesh

Dropdown List from object List in MVC View

2015-10-23T09:38:00.001+07:00

In previous months, i have opportunity to play along with MVC after a while using EF Code First and MVC 5.

So today the snippet about creating DropdownList from object List rather than List of SelectListItem

let's say u have this Company Class

[Key]
public int CompanyId { get; set; }
public string Name { get; set; }
public string Code { get; set; }

and u want to display dropdown list based on Code as Text and Id as Value
in the View u create a SelectList based on that field

@Html.LabelFor(model => model.Brand.Company, htmlAttributes: new { @class = "control-label col-md-2" })

@Html.DropDownList("selectedCompany", Model.Companies.Select(_ => new SelectListItem
{
Text = _.Code,
Value = _.CompanyId.ToString(),
Selected = _.CompanyId.ToString() == Model.Brand.Company.CompanyId.ToString()
})

)

the Model is a simple ViewModel that have Brands and Brand object

sorry for the format, can not make it right :D

sp_msforeachtable - Quick counting table rows

2014-12-19T12:54:00.002+07:00

i just starting to play along with SSIS and SSAS
and i came up with this trick to show wether my data has been populate into staging or data warehouse table

so here is the script

EXEC sp_MSForEachTable "
DECLARE @count AS INT SET @count=0
SELECT @count = ISNULL(SUM(1), 0) FROM ?
PRINT ""?: "" + CONVERT(VARCHAR, @Count)
, @whereand = "ORDER BY 1" "

until then

sp_msforeachtable - Delete data in all tables in SQL Server database

2014-12-05T15:52:00.001+07:00

hi all, just remember to post this useful tips

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--if some of the tables have identity columns we may want to reseed them
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

see u then

sp_MsForEachTable - compare data between tables

2014-06-23T09:31:00.002+07:00

usually i will using temporary table to build dynamic query to compare the value for two indentical database between two servers

but few weeks ago i know undocumented sp_msforeachtable stored procedure and gain some advantage for using it

exec sp_MSforeachtable

@command1 = '

IF EXISTS(

SELECT top 1 * FROM [server2].db_name.?

EXCEPT

SELECT top 1 * FROM ?

)

PRINT ''?''

'

just make sure u use "." in first syntax because sp_msforeachtable return "dbo.table_name"

for parameters reference please see this link

see u

Scheduled ClickOnce Application in Windows Task Scheduler

2014-06-20T09:16:00.001+07:00

it can :D
u have to publish it to iis i think, not testing when publish it using ftp

just add this line

"C:\Program Files\Internet Explorer\iexplore.exe" http://yoururltoapplication/applicationname.application

just make sure u put the red word at behind

until then

create ClickOnce shortcut at Startup

2014-06-18T16:38:00.001+07:00

currently there are needs to add some shortcut based on ClickOnce Application that being deployed,
it's obvious we can't create it manually because of the installation path

here is some remedy :D, u need to add reference to System.Deployment, System.IO, System.Reflection

if (ApplicationDeployment.IsNetworkDeployed)
{
string publisherName = ((AssemblyCompanyAttribute)Attribute.GetCustomAttribute(Assembly.GetExecutingAssembly(),
typeof(AssemblyCompanyAttribute), false)).Company;
string productName = System.Reflection.Assembly.GetExecutingAssembly().GetName().Name;
string startupPath = Environment.GetFolderPath(Environment.SpecialFolder.Startup);
startupPath = Path.Combine(startupPath, productName) + ".appref-ms";

if (!File.Exists(startupPath))
{
string allProgramsPath = Environment.GetFolderPath(Environment.SpecialFolder.Programs);
string shortcutPath = Path.Combine(allProgramsPath, publisherName);
shortcutPath = Path.Combine(shortcutPath, productName) + ".appref-ms";

File.Copy(shortcutPath, startupPath);
}
}

until then

Monitoring Table Size Growth in SQL Server

2014-06-17T09:13:00.003+07:00

if u plan to check how much your database growth then u might need this script from Yaniv Etrogi

it writes the growth to database so u can check it againts database threshold that u define
best of it: all the scripts already provided :D

until then

Find all Database size in SQL Server

2014-06-17T08:16:00.003+07:00

hi, just came across a very good script from Pinal Dave

u can see all database information in one place
here the script

SELECT database_id,

CONVERT(VARCHAR(25), DB.name) AS dbName,

CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],

state_desc,

(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,

(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],

(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,

(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],

user_access_desc AS [User access],

recovery_model_desc AS [Recovery model],

CASE compatibility_level

WHEN 60 THEN '60 (SQL Server 6.0)'

WHEN 65 THEN '65 (SQL Server 6.5)'

WHEN 70 THEN '70 (SQL Server 7.0)'

WHEN 80 THEN '80 (SQL Server 2000)'

WHEN 90 THEN '90 (SQL Server 2005)'

WHEN 100 THEN '100 (SQL Server 2008)'

END AS [compatibility level],

CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],

-- last backup

ISNULL((SELECT TOP 1

CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +

LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +

CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +

CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +

' (' + CAST(DATEDIFF(second, BK.backup_start_date,

BK.backup_finish_date) AS VARCHAR(4)) + ' '

+ 'seconds)'

FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],

CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],

CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],

page_verify_option_desc AS [page verify option],

CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],

CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],

CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],

CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],

CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],

CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]

FROM sys.databases DB

ORDER BY dbName, [Last backup] DESC, NAME

until then

Force Application update in ClickOnce deployment

2014-05-06T10:15:00.001+07:00

the key is to set minimum requirement version to be exactly like the one u published right now to remove the update popup dialog when starting clickonce program

just make sure the version is the same between Publish version and Update the Project properties - Publish

until then

WCF: The underlying connection was closed

2014-04-30T08:27:00.000+07:00

hi,

just got an error for couple of days when try to return a model contain child record using Entity Framework
and DBContext

i have this Role class that contain list of User within that Role
so when i try to get specific Role along with the User it give me an error "The underlying connection was closed "

after searching alot i came up with this great solution disabled proxy creation and lazy loading

or manually in Entity DBContext constructor

it run succesfully and can return UserInRole but if i filled up the table then the error comes, so we need to Preserving Object Reference in WCF but i came with this very useful Andrey Chabster Blog that using custom attribute that needed for the Service contract and Operation contract

until then

The version of SQL Server in use does not support datatype datetime2

2014-04-29T10:27:00.002+07:00

hi again,

just came up with this error in Entity Framework
the reason is because my development using SQL2008 express edition but the preproduction using SQL2005

and the solution is fairly simple

Open your EDMX in a file editor (or “open with…” in Visual Studio and select XML Editor). At the top you will find the storage model and it has an attribute ProviderManifestToken. This has should have the value 2008. Change that to 2005, recompile and everything works

after trying up for 2 hours :D

until then

Renaming SQL Server Default Instance

2014-03-03T08:38:00.000+07:00

hi all, found another intresting stuff to post

scenario: u change windows computer name and suddenly the query that contains SQL Server link server or anything that currently connect to ur "changed computer" is not working

try using this query in SSMS

-– Get the current name of the SQL Server instance for later comparison.
SELECT @@servername
–- Remove server from the list of known remote and linked servers on the local instance of SQL Server.
EXEC master.dbo.sp_dropserver ‘[SERVER NAME]‘
–- Define the name of the local instance of SQL Server.
EXEC master.dbo.sp_addserver ‘[NEW SERVER NAME]‘, ‘local’
–- Get the new name of the SQL Server instance for comparison.
SELECT @@servername

after try, u must restart sql server services...then check if the query is working now

see u

Get Child and Parent from hierarchy Table using Common Table Expression (CTE) - SQL 2005 and above

2013-12-05T15:46:00.001+07:00

hi, just want to post something that might be useful

DECLARE @a AS TABLE

(

autoid INT

, name VARCHAR(20)

, parentid INT

)

INSERT INTO @a (autoid, name, parentid)

SELECT 1, '1', 0

UNION ALL

SELECT 2, '1.1', 1

UNION ALL

SELECT 3, '1.2', 1

UNION ALL

SELECT 4, '1.2.1', 3

UNION ALL

SELECT 5, '2.1', 2

UNION ALL

SELECT 6, '3.1', 3

UNION ALL

SELECT 7, '3.2', 3

/*

1 1 0

2 1.1 1

3 1.2 1

4 1.2.1 3

5 2.1 2

6 3.1 3

7 3.2 3

*/

/*

Get Parent Records from child '1.2.1'

return:

1.2 (parent from '1.2.1')

1 (parent from '1.2')

*/

;WITH employeeMaster

AS ( SELECT p.autoid ,

p.parentid ,

p.[name]

FROM @a p

WHERE p.[NAME] = '1.2.1'

UNION ALL

SELECT c.autoid ,

c.parentid ,

c.[name]

FROM employeeMaster cte

INNER JOIN @a c ON c.autoid = cte.parentid

)

SELECT *

FROM employeeMaster m

WHERE m.[name] <> '1.2.1'

/*

Get Child Records from parent '1.2'

return:

1.2.1

3.1

3.2

*/

;WITH employeeMaster

AS ( SELECT p.autoid ,

p.parentid ,

p.[name]

FROM @a p

WHERE p.[NAME] = '1.2'

UNION ALL

SELECT c.autoid ,

c.parentid ,

c.[name]

FROM employeeMaster cte

INNER JOIN @a c ON c.parentid = cte.autoid

)

SELECT *

FROM employeeMaster m

WHERE m.[name] <> '1.2'

see u

Convert DATETIME to SMALLDATETIME

2012-12-27T11:09:00.000+07:00

here is the script from my colleague if u want to convert DATETIME data type to SMALLDATETIME because simple CONVERT is not working

SELECT CONVERT (SMALLDATETIME, CONVERT (VARCHAR, GETDATE(), 103), 103)

until then

Find size of SQL Server tables and other objects with stored procedure

2012-11-30T14:23:00.002+07:00

my colleague needs a query to seek for all tables in certain Database then use it to display informati on such as: Created Date, Rows in that tables, etc

at first i look over sp_spaceused but it end up wrapping query which is hard because undocumented and it failed :(
also considerations can be used for SQL 2000

so i google it and found this link, it works for SQL 2000 and above
~~i tweak a bit to meet my requirement but~~ credit goes to Richard Ding

here is the query that i used

USE master;
GO

IF OBJECT_ID(N'dbo.sp_SOS', N'P') IS NOT NULL
DROP PROCEDURE dbo.sp_SOS;
GO

CREATE PROCEDURE dbo.sp_SOS
@DbName sysname = NULL,
@SchemaName sysname = NULL,
@ObjectName sysname = N'%',
@TopClause nvarchar(20) = NULL,
@ObjectType nvarchar(50) = NULL,
@ShowInternalTable nvarchar(3) = NULL,
@OrderBy nvarchar(100) = NULL,
@UpdateUsage bit = 0
AS

/*=================================================================================================

Author: Richard Ding

Created: Mar. 03, 2008

Modified: Mar. 17, 2008

Purpose: Manipulate object size calculation and display for SS 2000/2005/2008

Parameters:
@DbName: default is the current database
@SchemaName: default is null showing all schemas
@ObjectName: default is "%" including all objects in "LIKE" clause
@TopClause: default is null showing all objects. Can be "TOP N" or "TOP N PERCENT"
@ObjectType: default is "S", "U", "V", "SQ" and "IT". All objects that can be sized
@ShowInternalTable: default is "Yes", when listing IT, the Parent excludes it in size
@OrderBy: default is by object name, can be any size related column
@UpdateUsage: default is 0, meaning "do not run DBCC UPDATEUSAGE"

Note: SS 2000/2005/2008 portable using dynamic SQL to bypass validation error;
Use ISNULL to allow prefilled default parameter values;
Use "DBCC UPDATEUSAGE" with caution as it can hold up large databases;
Unicode compatible and case insensitive;

Sample codes:

EXEC dbo.sp_SOS;
EXEC dbo.sp_SOS 'AdventureWorks', NULL, '%', NULL, 'U', 'No', 'T', 1;
sp_SOS 'TRACE', NULL, NULL, Null, ' ,,, ,;SQ,; u ;;;,, v ,,;iT , ;', 'No', N'N', 0;
sp_SOS NULL, NULL, NULL, NULL, 'U', 'Yes', N'U', 1;
sp_SOS 'AdventureWorks', 'Person%', 'Contact%', NULL, 'U', 'no', 'N', 0;
sp_SOS 'AdventureWorks', NULL, NULL, N'Top 100 Percent', 'S', 'yes', N'N', 1;
sp_SOS 'AdventureWorks', NULL, 'xml_index_nodes_309576141_32000', NULL, 'IT', 'yes', 'N', 1;
sp_SOS 'TRACE', NULL, 'Vw_DARS_217_overnight_activity_11142007', ' top 10 ', 'v', 'yes', 'N', 0;
sp_SOS 'AdventureWorks', NULL, 'xml%', ' top 10 ', null, 'yes', 'N', 1;
sp_SOS 'AdventureWorks2008', NULL, 'sales%', NULL, ' ,,; u ;;;,, v ', 'No', N'N', 1;
sp_SOS NULL, NULL, NULL, N'Top 100 Percent', ' ;;Q, U;V,', N'Y', 1;

=================================================================================================*/

SET NOCOUNT ON;

-- Input parameter validity checking
DECLARE @SELECT nvarchar(2500),
@WHERE_Schema nvarchar(200),
@WHERE_Object nvarchar(200),
@WHERE_Type nvarchar(200),
@WHERE_Final nvarchar(1000),
@ID int,
@Version nchar(2),
@String nvarchar(4000),
@Count bigint,
@GroupBy nvarchar(450);

IF ISNULL(@OrderBy, N'N') NOT IN (N'', N'N', N'R', N'T', N'U', N'I', N'D', N'F', N'Y')
BEGIN
RAISERROR (N'Incorrect value for @OrderBy. Valid parameters are:
''N'' --> Listing by object name
''R'' --> Listing by number of records
''T'' --> Listing by total size
''U'' --> Listing by used portion (excluding free space)
''I'' --> Listing by index size
''D'' --> Listing by data size
''F'' --> Listing by unused (free) space
''Y'' --> Listing by object type ', 16, 1)
RETURN (-1)
END;

-- Object Type Validation and Clean up
DECLARE @OTV nvarchar(10), @OTC nvarchar(10);
SELECT @OTV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,
N'S, U, V, SQ, IT'), N' ', N''), N',', N''), N';', N''), N'SQ', N''), N'U', N''),
N'V', N''), N'IT', N''), N'S', N'');
IF LEN(@OTV) <> 0 -- only allow comma, semi colon and space around S,U,V,SQ,IT
BEGIN
RAISERROR (N'Parameter error. Choose ''S'', ''U'', ''V'', ''SQ'', ''IT'' or any combination of them,
separated by space, comma or semicolon.

S -> System table;
U -> User table;
V -> Indexed view;
SQ -> Service Queue;
IT -> Internal Table', 16, 1)
RETURN (-1)
END
ELSE -- passed validation
BEGIN
SET @OTC = UPPER(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,N'S,U,V,SQ,IT'),N' ',N''),N',',N''),N';',N''))
SELECT @ObjectType = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL
(@ObjectType, N'S,U,V,SQ,IT'),N',',N''),N';',N''),N'SQ',N'''QQ'''),N'IT',N'''IT'''),N'S',
N'''S'''),N'U',N'''U'''),N'V',N'''V'''),N'QQ',N'SQ'),N' ',N''),N'''''',N''',''')
END

---- common ----
SELECT @DbName = ISNULL(@DbName, DB_NAME()),
@Version = SUBSTRING(CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')), 1,
CHARINDEX(N'.', CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')))-1),
@OrderBy = N'ORDER BY [' +
CASE ISNULL(@OrderBy, N'N')
WHEN N'N' THEN N'Object Name] ASC '
WHEN N'R' THEN N'Rows] DESC, [Object Name] ASC '
WHEN N'T' THEN N'Total(MB)] DESC, [Object Name] ASC '
WHEN N'U' THEN N'Used(MB)] DESC, [Object Name] ASC '
WHEN N'I' THEN N'Index(MB)] DESC, [Object Name] ASC '
WHEN N'D' THEN N'Data(MB)] DESC, [Object Name] ASC '
WHEN N'F' THEN N'Unused(MB)] DESC, [Object Name] ASC '
WHEN N'Y' THEN N'Type] ASC, [Object Name] ASC '
END;

---------------------- SS 2000 -----------------------------------
IF @Version = N'8'
BEGIN
SELECT @SELECT = N'USE ' + @DbName + N' SELECT ' + ISNULL(@TopClause, N' ') +
N''''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''' AS ''Object Name'',
o.type AS ''Type'',
MAX(i.[rows]) AS ''Rows'',
CONVERT(dec(10,3), SUM(i.reserved * 8.000/1024)) AS ''Total(MB)'',
CONVERT(dec(10,3), SUM((i.reserved - i.used) * 8.000/1024)) AS ''Unused(MB)'',
CONVERT(dec(10,3), SUM(i.used * 8.000/1024)) AS ''Used(MB)'',
CONVERT(dec(10,3), SUM((i.used - CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END)
* 8.000/1024)) AS ''Index(MB)'',
CONVERT(dec(10,3), SUM(CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END
* 8.000/1024)) AS ''Data(MB)''
FROM dbo.sysindexes i WITH (NOLOCK)
JOIN dbo.sysobjects o WITH (NOLOCK)
ON i.id = o.id
WHERE i.name NOT LIKE ''_WA_Sys_%''
AND i.indid IN (0, 1, 255) AND USER_NAME(o.uid) LIKE ''' + ISNULL(@SchemaName, N'%') + N''' ',
-- SS 2000 calculation as below:
-- "reserved" = total size;
-- "dpages" = data used;
-- "used" = used portion (contains data and index);
-- text or image column: use "used" for data size
-- Nonclustered index take tiny space, somehow it is not counted (see sp_spaceused).
@WHERE_Final = N' AND OBJECT_NAME(i.id) LIKE ''' + ISNULL(@ObjectName, N'%')
+ N''' AND o.type IN (' + @ObjectType + N') ',
@GroupBy = N' GROUP BY '''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''', o.type ',
@String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy
END

------------------- ss 2k5 ------------------------------------------------------
IF @Version IN (N'9', N'10')
BEGIN
SELECT @String = N'
IF OBJECT_ID (''tempdb.dbo.##BO'', ''U'') IS NOT NULL
DROP TABLE dbo.##BO

CREATE TABLE dbo.##BO (
ID int identity,
DOI bigint null, -- Daughter Object Id
DON sysname null, -- Daughter Object Name
DSI int null, -- Daughter Schema Id
DSN sysname null, -- Daughter Schema Name
DOT varchar(10) null, -- Daughter Object Type
DFN sysname null, -- Daughter Full Name
POI bigint null, -- Parent Object Id
PON sysname null, -- Parent Object Name
PSI bigint null, -- Parent Schema Id
PSN sysname null, -- Parent Schema Name
POT varchar(10) null, -- Parent Object Type
PFN sysname null -- Parent Full Name
)

INSERT INTO dbo.##BO (DOI, DSI, DOT, POI)
SELECT object_id, schema_id, type, Parent_object_id
FROM ' + @DbName + N'.sys.objects o WHERE type IN (''S'',''U'',''V'',''SQ'',''IT'')
USE ' + @DbName + N'
UPDATE dbo.##BO SET DON = object_name(DOI), DSN = schema_name(DSI), POI = CASE POI WHEN 0 THEN DOI ELSE POI END
UPDATE dbo.##BO SET PSI = o.schema_id, POT = o.type FROM sys.objects o JOIN dbo.##BO t ON o.object_id = t.POI
UPDATE dbo.##BO SET PON = object_name(POI), PSN = schema_name(PSI), DFN = DSN + ''.'' + DON,
PFN = schema_name(PSI)+ ''.'' + object_name(POI)
'
EXEC (@String)

SELECT
@WHERE_Type = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N't.DOT ' ELSE N't.POT ' END,
@SELECT = N'USE ' + @DbName + N'
SELECT ' + ISNULL(@TopClause, N'TOP 100 PERCENT ') +
N' CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN THEN t.PFN
ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END AS ''Object Name'',
' + @WHERE_Type + N' AS ''Type'',
SUM (CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN
CASE WHEN (ps.index_id < 2 ) THEN ps.row_count ELSE 0 END
ELSE CASE WHEN (ps.index_id < 2 and t.DON = t.PON) THEN ps.row_count ELSE 0 END END) AS ''Rows'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.reserved_page_count ELSE 0 END)* 8.000/1024 AS ''Total(MB)'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.reserved_page_count ELSE 0 END
- CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN
ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Unused(MB)'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Used(MB)'',
SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''
THEN ps.used_page_count ELSE 0 END
- CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes')
+ N''' = ''No'' THEN 0 ELSE CASE WHEN (ps.index_id<2 nbsp="" p=""> THEN (ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count)
ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count END END) * 8.000/1024 AS ''Index(MB)'',
SUM (CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes')
+ N''' = ''No'' THEN 0 ELSE CASE WHEN (ps.index_id<2 nbsp="" p=""> THEN (ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count)
ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count END END) * 8.000/1024 AS ''Data(MB)''
FROM sys.dm_db_partition_stats ps INNER JOIN dbo.##BO t
ON ps.object_id = t.DOI
',
@ObjectType = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N'''IT'',' + ISNULL(@ObjectType, N'''S'',''U'',
''V'', ''SQ'', ''IT''') ELSE ISNULL(@ObjectType, N'''S'', ''U'', ''V'', ''SQ'', ''IT''') END,
@WHERE_Schema = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DSN ' ELSE N' t.PSN ' END, -- DSN or PSN
@WHERE_Object = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DON LIKE ''' + ISNULL(@ObjectName, N'%')
+ ''' OR t.PON LIKE ''' + ISNULL(@ObjectName, N'%') + N''' '
ELSE N' t.pon LIKE ''' + ISNULL(@ObjectName, N'%') + N''' ' END, -- DON or PON
@WHERE_Final = N' WHERE (' + @WHERE_Schema + N' LIKE ''' + ISNULL(@SchemaName, N'%') + N''' OR ' + @WHERE_Schema +
N' = ''sys'') AND (' + @WHERE_Object + N' ) AND ' + @WHERE_Type + N' IN (' + @ObjectType + N') ',
@GroupBy = N'GROUP BY CASE WHEN ''' + ISNULL(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN
THEN t.PFN ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END, ' + @WHERE_Type + N''
SELECT @String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy
-- SELECT @String AS 'STRING'
END

----- common ------
IF OBJECT_ID(N'tempdb.dbo.##FO', N'U') IS NOT NULL
DROP TABLE dbo.##FO;

CREATE TABLE dbo.##FO (
ID int identity,
[Object Name] sysname,
[Type] varchar(2),
[Rows] bigint,
[Total(MB)] dec(10,3),
[-] nchar(1),
[Unused(MB)] dec(10,3),
[==] nchar(2),
[Used(MB)] dec(10,3),
[=] nchar(1),
[Index(MB)] dec(10,3),
[+] nchar(1),
[Data(MB)] dec(10,3) );

INSERT INTO dbo.##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],[Used(MB)],[Index(MB)],[Data(MB)])
EXEC (@String);

SELECT @Count = COUNT(*) FROM dbo.##FO;

IF @Count = 0
BEGIN
RAISERROR (N'No records were found macthcing your criteria.', 16, 1)
RETURN (-1)
END
ELSE -- There're at least one records
BEGIN
-- Run DBCC UPDATEUSAGE to correct wrong values
IF ISNULL(@UpdateUsage, 0) = 1
BEGIN
SELECT @ObjectName = N'', @ID = 0
WHILE 1 = 1
BEGIN
SELECT TOP 1 @ObjectName = CASE WHEN [Object Name] LIKE N'%(%' THEN
SUBSTRING([Object Name], 1, CHARINDEX(N'(', [Object Name])-2) ELSE [Object Name] END
, @ID = ID FROM dbo.##FO WHERE ID > @ID ORDER BY ID ASC
IF @@ROWCOUNT = 0
BREAK
PRINT N'==> DBCC UPDATEUSAGE (' + @DbName + N', ''' + @ObjectName + N''') WITH COUNT_ROWS'
DBCC UPDATEUSAGE (@DbName, @ObjectName) WITH COUNT_ROWS
PRINT N''
END

PRINT N''
TRUNCATE TABLE dbo.##FO
INSERT INTO dbo.##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],
[Used(MB)],[Index(MB)],[Data(MB)]) EXEC (@String)
END
ELSE
PRINT N'(Warning: Run "DBCC UPDATEUSAGE" on suspicious objects. It may incur overhead on big databases.)'
PRINT N''

UPDATE dbo.##FO SET [-] = N'-', [==] = N'==', [=] = N'=', [+] = N'+'

IF @Count = 1 -- when only 1 row, no need to sum up total
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM dbo.##FO ORDER BY ID ASC
ELSE
BEGIN
SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
[Index(MB)],[+],[Data(MB)]
FROM dbo.##FO ORDER BY ID ASC
COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])
END
END

RETURN (0)

GO

--exec sp_SOS @DbName = 'myERP', @ObjectType ='U'

until then

PS: something went wrong when try to copy it so i revert to the original one

Get Computer name from IP

2012-11-22T11:18:00.000+07:00

i know it's "old" one but sometimes i need to use it so instead i search over and over again, i'll write it down here :D
the magic word are (at command prompt)

nbtstat -a [ip_address_here]

until then

Print Escape Character for printer Barcode in .NET

2012-10-08T16:43:00.001+07:00

fiuhh, this case is one of the most annoying 'simple' approach

requirement is to send 'barcode' to printer directly, because often we using Image print (creating image on the fly then send it to barcode printer)..this one althought works but often the image become blur / discarded

so we need to 'communicate' to barcode printer using its 'language'
the 'language' depends on the printer itself..in my case, we using SATO barcode printer so the 'language' is
SBPL (visit the link for more info)

first we need to design the label
we can use SBPL and design it 'on the fly' make us a little bit crazy

or we can use SATO LABEL Gallery Free to do the job and generate SBPL equivalent for us so we can use it in our code

after we design it, we use "Print to file" method for the printer then print it so we can see what its send to the printer

the generated SBPL will contain 'unseen' character in visual studio IDE (i'm using VS 2010) so be careful with it, u must open it using 3rd party like Notepad++

just replace the 'dynamic' content (ie: productCode, productName, etc) then start printing it

caution:
1. if u need to add , just for readiness of the code then u need to replace it from the SBPL using notepad++ then paste it in code
before printing don't forget to Replace it again to so it can recognized using this method:

.Replace("", ((char)02).ToString());
.Replace("", ((char)03).ToString());
.Replace("", ((char)27).ToString());

2. don't forget to copy it "as is" from generated File in, must include [STX][ETX] thing

become

and using step 1. to replace it (tooks 2 days to realize it and search for the .NET equivalent way) :(

PS: stilll not sure how to display the Escape Character inside VS IDE ( maybe u'll see 'bad thing inside' :D )

until then

Reset Identity Column Value in SQL Server

2012-09-27T08:53:00.000+07:00

if u have identity column and of course before deployed it to production, u'll need to test the value when u done, u probably want to reset it to initial value (0 for example) so u have 'fresh' data for production u'll be need to use this syntax

DBCC CHECKIDENT (orders, RESEED, 0)

PS: the next value will be always +1, so in my case next value will be 1 until then

Date and Time format in SQL

2012-09-24T16:49:00.000+07:00

this are some DATE and TIME format to use in SQL that i found when i'm googling a little

YY-MM-DD	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]	99-01-24
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]	1999-01-24
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]	08/99
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	12/2005
YY/MM	SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	99/08
YYYY/MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2005/12
Month DD, YYYY ¹	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 2006¹
Mon YYYY¹	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006 ¹
Month YYYY ¹	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2006 ¹
DD Month¹	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September ¹
Month DD¹	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11 ¹
DD Month YY ¹	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 72 ¹
DD Month YYYY ¹	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002 ¹
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]	12/92
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	05-2006
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]	92/12
YYYY-MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2006-05
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252006
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072002
Mon-YY ¹	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02 ¹
Mon-YYYY¹	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002 ¹
DD-Mon-YY ¹	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05 ¹
DD-Mon-YYYY ¹	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-2005¹

source until then,

Search specific string inside Stored Procedure, View, Function, Trigger, Table Name, Table Field in SQL Server

2012-08-28T11:20:00.001+07:00

this query can be used to search specific string inside, no need to right click - modify - and search manually from Query designer / SSMS

DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)
DECLARE @Query nvarchar(2000)
DECLARE @WHERE nvarchar(1000)

DECLARE @Query2 nvarchar(2000)
DECLARE @Where2 nvarchar(1000)

SET @Query =
'SELECT DISTINCT so.name, ' +
'CASE so.xtype ' +
'WHEN ''P'' THEN ''SPROC'' ' +
'WHEN ''IF'' THEN ''FUNCTION'' ' +
'WHEN ''FN'' THEN ''FUNCTION'' ' +
'WHEN ''V'' THEN ''VIEW'' ' +
'WHEN ''U'' THEN ''TABLE'' ' +
'WHEN ''TR'' THEN ''TRIGGER'' ' +
'END AS [TYPE] ' +
'FROM syscomments sc ' +
'RIGHT JOIN sysobjects so ON sc.id = so.id ' +
'WHERE '
SET @String = 'cab'
SET @WHERE = ''
SET @Where2 = ''
SET @Delimiter = ','
SET @String = 'cab' + @Delimiter
SET @Pos = CHARINDEX(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = LTRIM(RTRIM(SUBSTRING(@String, 1, @Pos - 1)))
PRINT 'next: ' + @NextString

IF LEN(@WHERE) > 0
BEGIN
SET @WHERE = @WHERE + ' OR sc.[TEXT] LIKE ''%' + @NextString + '%''' +
' OR so.[name] LIKE ''%' + @NextString + '%'''

print '42: ' + @where2
SET @Where2 = @where2 + ' OR T0.Name LIKE ''%' + @NextString + '%'' '
print '42: ' + @where2
END
ELSE
BEGIN
SET @WHERE = @WHERE + ' sc.[TEXT] LIKE ''%' + @NextString + '%''' +
' OR so.[name] LIKE ''%' + @NextString + '%'''

print '51: ' + @where2
SET @Where2 = @Where2 + ' t0.Name LIKE ''%' + @NextString + '%'' '
print '51: ' + @where2
END

SET @String = SUBSTRING(@String, @pos + 1, LEN(@String))
SET @pos = CHARINDEX(@Delimiter, @String)
END

SET @WHERE = 'so.xtype IN (''P'', ''IF'', ''FN'', ''V'', ''U'', ''TR'') AND (' + @WHERE + ')'

SET @Query2 =
'UNION ALL ' +
'SELECT t1.NAME, ''TABLE FIELD'' AS [TYPE] ' +
'FROM syscolumns t0 ' +
'INNER JOIN sysobjects t1 ON t0.ID = t1.ID ' +
'WHERE '
SELECT @Where2 = 't1.xType = ''U'' ' +
CASE WHEN LEN(@Where2) > 0 THEN ' AND (' + @Where2 + ')' ELSE '' END

PRINT @Query + @WHERE + @Query2 + @Where2
EXEC (@Query + @WHERE + @Query2 + @Where2)

until then

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server [linked_server_name]

2012-08-14T14:25:00.001+07:00

just got this error when try to execute query against LinkServer (SQL 2000) from SQL 2005

eventhough the link can create but i can't do 4 parts query
select * from LINKED.DB.DBO.TABLE

the problem because SQL 2000 needs to run certain SP that not exists in master so the LINK query can run

create procedure sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = nullasdeclare @Result int set @Result = 0exec @Result = sp_tables_info_rowset @table_name,@table_schema, @table_type

this solution originally POST by Marek Adamczuk

until then

...it's me...eRiCk

hi..it's me :D

SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

Value cannot be null when trying to get WebAPI Token using Postman

Server Error in '/' Application.

Value cannot be null.Parameter name: value

Session timeout problem in IIS

Dropdown List from object List in MVC View

sp_msforeachtable - Quick counting table rows

sp_msforeachtable - Delete data in all tables in SQL Server database

sp_MsForEachTable - compare data between tables

Scheduled ClickOnce Application in Windows Task Scheduler

create ClickOnce shortcut at Startup

Monitoring Table Size Growth in SQL Server

Find all Database size in SQL Server

Force Application update in ClickOnce deployment

WCF: The underlying connection was closed

The version of SQL Server in use does not support datatype datetime2

Renaming SQL Server Default Instance

Get Child and Parent from hierarchy Table using Common Table Expression (CTE) - SQL 2005 and above

Convert DATETIME to SMALLDATETIME

Find size of SQL Server tables and other objects with stored procedure

Get Computer name from IP

Print Escape Character for printer Barcode in .NET

Reset Identity Column Value in SQL Server

Date and Time format in SQL

Search specific string inside Stored Procedure, View, Function, Trigger, Table Name, Table Field in SQL Server

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server [linked_server_name]

Value cannot be null.
Parameter name: value