Create Sample Table with Command GO [count] (T-SQL)

Posted: March 31, 2011 | Author: | Filed under: SQL Server 2005, SQL Server 2008 | Leave a comment

By adding a number (n) after GO, the batch will exexute n times. 

Here is a T-SQL snippet to create a sample table with 50 records in it:

<pre>

CREATE TABLE myNumTable   (id int identity(1,1),c1 datetime default getdate(), c2 varchar(50) default ‘test’)

GO

INSERT INTO myNumTable DEFAULT  
VALUES GO 50  

 

SELECT * FROM myNumTable 
</pre>

 

The GO is the signal to start a batch of T-SQL statement but itself is not a T-SQL statement. It is recognized by the sqlcmd and osql utilities.

Replace the Null Value With Previous Which Is Not Null

Posted: March 23, 2011 | Author: | Filed under: SQL Server | Leave a comment 
declare @t table (names varchar(5))
INSERT INTO @t SELECT 'sam'
UNION ALL SELECT null
UNION ALL SELECT null
UNION ALL SELECT null
UNION ALL SELECT 'jac'
UNION ALL SELECT null
UNION ALL SELECT null  

SELECT names FROM @t

declare @str VARCHAR(5)
UPDATE @t
SET @str=COALESCE(names,@str)
,names=@str

SELECT names FROM @t

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/9353f696-33fc-44c0-9dab-86b8b71ed40b

How to Determine What Fiscal Year of Today Datein ASP.NET?

Posted: March 21, 2011 | Author: | Filed under: ASP.NET 2 | Leave a comment

Here is a code snippet I posted early:

protected void Page_Load(object sender, EventArgs e)

{

int fYear;

if (DateTime.Today.Month < 7)

fYear = DateTime.Today.Year;

else

fYear = DateTime.Today.Year + 1;

Response.Write( “Current fisical year:”+fYear.ToString().Remove(0,2));

}

Format string with GridView

Posted: March 21, 2011 | Author: | Filed under: ASP.NET 2, SQL Server | Leave a comment

One user at ASP.NEt forum asked a question for how to format a 15 digit number(string) with two hyphen (-) inside at position 10 and 15.

It can be don easily in SQL:

SELECT  LEFT(@From,9) +’-‘+SUBSTRING(@From,10,5)+’-‘+RIGHT(@From,1)

The same can be done at the front end with string manipulation.

For the data column in GridView, we need to convert the BoundField to TemplateField first and apply the string.Insert to Eval at the designated position twice.

Text='<%# Eval(“rawCol”).ToString().Insert(9,”-“).Insert(15,”-“) %>’

We can see this in action below:

<asp:TemplateField HeaderText=”rawCol” SortExpression=”rawCol”>

<EditItemTemplate>

<asp:TextBox ID=”TextBox1″ runat=”server” Text='<%# Bind(“rawCol”) %>’></asp:TextBox>

</EditItemTemplate>

<ItemTemplate>

<asp:Label ID=”Label1″ runat=”server” Text='<%# Eval(“rawCol”).ToString().Insert(9,”-“).Insert(15,”-“) %>’></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField=”col” HeaderText=”col” ReadOnly=”True”

SortExpression=”col” />

 

DNN 6.0 CTP released

Posted: March 16, 2011 | Author: | Filed under: DotNetNuke (DNN) | Leave a comment

The release of DNN 6.0 CTP is spread at DotnetNuke Site. I have downloaded the copy and istalled a fresh copy easily.

After a few minutes click and I need to start over again to resinstall it again with the app_pool set to ASP.NET 4.

I tested a function to create child portal with content from the Default My Website. It seems there is a bug in the process of creating the template. It include a invalid timezon element in the setting part. I submit it to the DNN forum.

I’ll keep posting my findings for this CTP.

SQL Server Change Authentication Mode (From Windows to Mixed) after Installation

Posted: March 13, 2011 | Author: | Filed under: SQL Server 2005, SQL Server 2008 | Leave a comment

When yo install SQL Server, you have a choice the server authentication mode from either Windows Authentication mode ot SQL Server and Windowns Authentication mode (mixed). If  you choose the Windows Authentication mode during install and you want to change it to mixed mode, you can find a step by step guild from MSDN site:

http://msdn.microsoft.com/en-us/library/ms188670.aspx

The T-SQL used by MSDN article:

<code>

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = ‘<enterStrongPasswordHere>’ ;
GO

</code>

Redirect domain site root to another page with URLRewrite (IIS7)

Posted: March 11, 2011 | Author: | Filed under: IIS 7 URLRewrite | Leave a comment

There are two ways I know to do this:

1.Method1:

<rule name=”Redirect1_For_http://mysite.com/” stopProcessing=”true”>

<match url=”^\d*$” negate=”false” />

<action type=”Redirect” url=”redirect.aspx” />

</rule>

 

2. Method 2:

<rule name=”RedirectRootToAnotherPage” enabled=”true” stopProcessing=”true”>

<match url=”^$” />

<action type=”Redirect” url=”http://mysite.com/redirect.aspx&#8221; logRewrittenUrl=”true” />

</rule>

Read the rest of this entry »

T-SQL Sample: Insert to Self and Insert to Another Table from a Trigger

Posted: March 10, 2011 | Author: | Filed under: SQL Server, SQL Server 2005 | Leave a comment

–1. Insert into self
INSERT INTO dbo.Table_1 ([num])
select top (20)[num] from dbo.Table_1
select * from dbo.Table_1

–2.Use a trigger to insert table2
CREATE TRIGGER UpdatetbB ON [dbo].[Table_1]
FOR INSERT
AS

BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO [Table_2]([num])
SELECT num FROM inserted

END
GO

How can I truncate a table which has foreign key

Posted: March 9, 2011 | Author: | Filed under: SQL Server | Leave a comment

You can drop the Foreign Key before you do theTRUNCATE and add it back to the table after you are done. By the way, the TRUNCATE action is logged but it is just not the same way as the DELETE action. You can roll back a truncate in a transaction with the page logging.

Here is a simple sample for the question:

–Drop the foreign key

ALTER TABLE dbo.CategoryTable1Sub  

DROP CONSTRAINT FK_CategoryIDGO

truncate

table dbo.CategoryTable1truncate table dbo.CategoryTable1SubGO

–Add Foreign key back

ALTER

TABLE dbo.CategoryTable1Sub ADD CONSTRAINTFK_CategoryID

FOREIGN KEY(

CatID )REFERENCES dbo.CategoryTable1  

( Category_ID )GO

Copy Files with Powershell in XP as Scheduled Task

Posted: March 9, 2011 | Author: | Filed under: Powershell | Leave a comment

I have a requirement to copy pdf files on my web server to a folder for FTP and another location for archiving. I have searched a while and came up with a Powershell script on my developer XP machine .

First  Ineed to run this command under Powershell command:

set-executionpolicy RemoteSigned

and I responded with yes to allow the Powershell script to run.

The script I wrote to copy files (only pdf file)  from one folder to two destination folders and the file is saved as copyFilesTest.ps1 in my test folder. The file extension is ps1.

Here is the code in the file:

# source and destionation folder

$source = “C:\test\jingyang”
$destination1  = “C:\tes\jingyang_destination1”
$destination2  = “C:\tes\jingyang_destination2”

$files = Get-ChildItem -Filter *.pdf -Path $source -Recurse

Foreach($file in $files)

{

Copy-Item -Path $file.fullname -Destination $destination1  -Force
Copy-Item -Path $file.fullname -Destination $destination2  -Force
}

After the test run of the script in Powershell, I create a bat file to execute the file. The file with .bat extension looks like this in my case:

powershell -command “& ‘C:\test\Scripts\copyFilesTest.ps1′”

Last, from Scheduled Task GUI, I setup a new task to point to the bat file I just created and pick a schedule time to run this script.

References:

http://technet.microsoft.com/en-us/library/ee176949.aspx

http://blogs.technet.com/b/heyscriptingguy/archive/2011/01/28/install-powershell-on-windows-xp-and-copying-files.aspx

http://atakan.titiz.net/2008/01/schedule-powershell-script.html

← Older Entries