SQL: Temp Tables vs View

Folks...

I have created a DTSX package for a requirement which fetched data from MS access and store it in SQL Server.For business calculations there we some Views required. The final stored procedure will insert data into a table which will be utilized by a ERP system. The insert query was a bit complex like it called fields in a View which was created from a view (can call it a nested view?) & so on... The execution time was more than 30 mins :( So we need to optimize things for better performance....

now my next attempt will be to try it out with temp tables. [drafting post...]

Yes... In place of the views I used two temporary tables and one view i kept it as it is because, It was only displaying distinct values from a table.

This time to complete execution the Stored procedure took only 34.031 seconds...

Wow... that's a huge difference.... I believe using Temp tables optimized the stored procedure than views. The point to be noted here is that the decision to go for View & Temporary tables is based on the scenario... In my case Temp tables did the trick :)

Best Regards
Fauzi

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Hi...

Came across this error today when i run a simple query in query analyzer...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

It was when i gave a test date with dd/mm/yyyy format. Seems we need to give in mm/dd/yyyy format... now it works fine....

people discussed on the same here

correct query is:

(SELECT
TEMP.Sre,
TEMP.Tket,
TEMP.RDate,
TEMP.SKU,
TEMP.Qty,
TEMP.Esion,
SAM.AverageCost*TEMP.Qty AS Expr2
FROM
TEMP INNER JOIN SAM ON (TEMP.SKU = SAM.SKU) AND (TEMP.Store = SAM.Sre)
WHERE
TEMP.RDate >= '07/13/2009'
And TEMP.RDate <= '07/19/2009'
) ORDER BY TEMP.Sre, TEMP.Tket

Regards
Fauzi

C# : float to currency format

Usually programmers need to format their data type to presentable currency format. Following the simple c# code to present your data in currency format like (##,###.##)

Code:

Decimal f = Convert.ToDecimal({AnyDataType});

lblValue.Text = f.ToString("C").Replace("$", "");

Regards

4Z

Mail code not working in Godaddy hosting server

Hi

In one of the site i hosted there is a functionality to submit a business directory form. As soon as the for is submitted there should be a mail send. I used the following C# code which was working fine in my local setup but gave error when i hosted the files.

Code:
MailMessage message = new MailMessage("fauzi@fauzi.com", "fauzi@fauzi.com", "Add Memeber Request", strbody.ToString());
message.IsBodyHtml = true;
SmtpClient emailClient = new SmtpClient("SmtpClientName");
emailClient.UseDefaultCredentials = true;
emailClient.Send(message);

Later I tried with the Code as follows:

Code:
MailMessage message = new MailMessage("FromEmail", "ToEmail", strSubject, strbody.ToString());
message.IsBodyHtml = true;
SmtpClient emailClient = new SmtpClient("smtp.gmail.com",587); //use this PORT!
emailClient.UseDefaultCredentials = true;
message.DeliveryNotificationOptions = DeliveryNotificationOptions.OnSuccess;
emailClient.EnableSsl = true;
emailClient.DeliveryMethod = SmtpDeliveryMethod.Network;
emailClient.Credentials = new NetworkCredential("UserName", "Password");
emailClient.Send(message);

And the error show was:

Description: The application attempted to perform an operation not allowed by the security policy. To grant this application the required permission please contact your system administrator or change the application's trust level in the configuration file.

Exception Details: System.Security.SecurityException: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

This means that your hosting account is set to "Medium Trust, " whereas the script you are using requires "Full Trust," which cannot be granted by the provider.

Solution:

The solution is we need to change the code as recommended by Godaddy support team as follows.

Code:
MailMessage message = new MailMessage("fauzi@fauzi.com", "fauzi@fauzi.com", "Add Memeber Request", strbody.ToString());

message.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "relay-hosting.secureserver.net";
smtp.Credentials = new System.Net.NetworkCredential("Username", "Password");
smtp.Send(message);

Reference

Note: Assemblies Needed are: System.Net.Mail; System.Net; And this code may not work in your local machine, saying...

Mailbox name not allowed. The server response was: sorry, relaying denied from your location [IPAddress] (#5.7.1)

Just ignore this because your local anti virus firewall may block this code to run.

It works fine Now on live site :)

Hope It Helps

Regards
Fauzi

SQL Server: How to generate script for tables WITH data

Hi,

If you have to generate script for your tables in database with data, you need to install Database Publishing Wizard for SQL Server. It is a free download from Microsoft website.

Link to Download

Localized version

After installation you need to run the following syntax in command prompt.

Syntax:

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing>sqlpubwiz script -d YOURDB "C:\YOURDB.sql" -dataonly

The Following acknowledge will be displayed if the syntax is right...

Microsoft (R) SQL Server Database Publishing Wizard
Version 1.1.1.0
Copyright (c) Microsoft Corporation. All rights reserved.

Generating script for database YOURDB
- Generating only data script
- Generating script targeted for SQL Server 2005
Gathering list of objects to script
Scripting objects
..
Writing script to disk
Writing table dbo.Members
Writing table dbo.authors

Scripting completed for database YOURDB.

Thus your Script is generated & ready for use :)

Hope it help...

Regards
Fauzi

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, err

Hi,

If you get this error when you run a asp.net application:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Please don't waste time, check whether the connection string is right. I forgot to give appropriate initial Catalog ! :)

<add name="pubsConnectionString" Initial Catalog=YOURDB;Persist Security Info=True;User ID=user;Password=pawssword" providerName="System.Data.SqlClient"/>

Regards
Fauzi

ASP.NET IIS - Error: 0x80070005 Access is denied.

Hey...

I was trying to run a website from my IIS. The same website is working very well when i run through my Visual studio. The error shown when i browse the site through IIS is as follows:

Server Application Unavailable

The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.

Administrator Note: An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.

So I checked the event log and there were three logs created on the same time when i run browsed the site:

1.
aspnet_wp.exe (PID: 6024) stopped unexpectedly.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

2.
Failed to execute the request because the ASP.NET process identity does not have read permissions to the global assembly cache. Error: 0x80070005 Access is denied.

3.
Failed to initialize the AppDomain:/LM/W3SVC/1/Root/Bbsbd

Exception: System.IO.FileLoadException

Message: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Access is denied.

StackTrace: at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Activator.CreateInstance(String assemblyName, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, Evidence securityInfo, StackCrawlMark& stackMark)
at System.Activator.CreateInstance(String assemblyName, String typeName)
at System.AppDomain.CreateInstance(String assemblyName, String typeName)
at System.AppDomain.CreateInstance(String assemblyName, String typeName)
at System.Web.Hosting.ApplicationManager.CreateAppDomainWithHostingEnvironment(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters)
at System.Web.Hosting.ApplicationManager.CreateAppDomainWithHostingEnvironmentAndReportErrors(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters)

For more information, see Help and Support Center at

Solution:
I read the forums and the solution worked for me is I gave permission for ASPNET account for GAC folder with the following syntax in command prompt:

Syntax: CACLS c:\windows\assembly /e /t /p {MachineName}\ASPNET:R

to be on safer side, For ASPNET account, i have given read permission for the project folder too.

now the issue is fixed.

Regards
Fauzi

Javascript Pops

Came across this site which has lot of working sample to play with javascript pop ups

http://www.sivamdesign.com/scripts/popups.html

Read MS Access through .Net

Hi,

I had a requirement to create a application to retrieve data from legacy MS-Access application.
I was looking for a sample in google. could'nt find one. So posting it so that it could be useful for people who is looking for the same.

I have created a DNS for the mdb file. used System.Data.Odbc; and rest are similar to how we do for SQL. following is the sample code.

static void Main(string[] args)

{

//Read Data from Access

OdbcConnection ODBCConnection = new System.Data.Odbc.OdbcConnection("DSN=DNS_Test_MSACCESS;UID=sam;PWD=mam;");

// ODBCConnection= new OdbcConnection(ConnString);

ODBCConnection.Open();

OdbcCommand ODBCCommand = new OdbcCommand("select * from Table1", ODBCConnection);

OdbcDataReader ODBCDataReader = ODBCCommand.ExecuteReader();

string s = "";

while (ODBCDataReader.Read())

{

s = s + "- " + ODBCDataReader.GetString(1);

}

Console.WriteLine(s);

ODBCConnection.Close();

}

Failed to save package file --- with error 0x8002802B "Element not found"

Hey Folks,

Earlier it was my friend who witnessed a similar issue. now it's my turn. when i tried to Create a SSIS package after new installation. The error shown this time was little different but the solution is same.

Error: "Failed to save package file "C:\TEMP\tmp18D.tmp" with error 0x8002802B "Element not found"

solution is we need to call "regsvr32 msxml6.dll" from Start -> Run.

Googled for What is regsvr32.exe? and found the following:

regsvr32.exe is a process belonging to the Windows OS and is used to register dynamic-link libraries and ActiveX controls in the registry. This program is important for the stable and secure running of your computer and should not be terminated.

Hope it helps :)
Fauzi

Keyword 'this' is not valid in a static property, static method, or static field initializer

Hi

I was writing a console application, I wrote a common method to reuse it like this.. But when i build the solution, i get the following message:

"Keyword 'this' is not valid in a static property, static method, or static field initializer"

I googled and found the reason from MSDN, which says static methods are independent of any instance of the containing class - Reference

The work around i did was to create a class and place this method inside. Create a object of the class and call this method.

Hope it helps...

Regards
Fauzi

MOSS: SharePoint_Config LDF file grows drastically

Hey Folks...

In our MOSS server database, the SharePoint_Config_log.LDF file of database SharePoint_Config grows drastically. surprisingly it reaches more than 100 GB in months time... due to this there occurs lack of disk space. Currently I have scheduled to run a SQL query given by my friend to shrink the LDF file.

SQL Query:

BACKUP LOG SharePoint_Config WITH TRUNCATE_ONLY
USE SharePoint_Config

GO
DBCC SHRINKFILE (SharePoint_Config_Log, EMPTYFILE)
GO

For time being this is the solution, If you are aware of any other permenant solution, please do let us know :)

Regards
Fauzi

MOSS: Install IT helpdesk Template

Hey,

Following are commands to be executed to install a template in MOSS Server. I have installed a site template for IT help desk with the following command. There are two steps to be basically.

1. Add solution:
Command:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsadm -o addsolution -filename HelpDesk.wsp

Message shown execution is "Operation completed successfully."

2. Deploy solution:
While deploying we need to specify either the deployment is '-immediate' or '-local' or '-time' as parameter .
Command:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsadm -o deploysolution -name HelpDesk.wsp -immediate -allowgacdeployment

Message shown execution is "Timer job successfully created."

Thats it :)

Regards
Fauzi

Google Analytics for dotnetnuke CMS

Hi,

I was looking forward to implement Google Analytics for a dotnetnuke CMS site. Obviously i tried to place the script some where in master page like any normal web applications. Then figured out that in dotnetnuke it is skins which are used in common. As i don't have direct server access i have to only download the skin.ascx, and add the tracking script provided by Google & upload it back through the cms. but hard luck this time as the CMS did not allow .ascx file to be uploaded. And even renaming the file after uploading it as a text file is also was restricted. Then I googled for Analytics in Dotnetnuke and found the article below. I followed the steps & Google Analytics is setup for my site :)

http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/53/adding-google-analytics-to-dotnetnuke.aspx

[update: Checked statistics report today morning, It works fine...]

Regards
Fauzi