SSIS Package error

SSIS Package error:

I have tried to work with Script task but while running the application I have got some errors in executing the same. Below is the error.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Script Task: The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.
(Microsoft.DataTransformationServices.VsIntegration)

Procedure followed:
1. I have created a variable “Var1” in the variables window with the scope as “Script task”.
2. Dragged the script task and put the same in the designer window.
3. Double clicking the script task and click “script”, we can see the Script details in the right pane. 4. In Readwritevariables, I have specified “User::Var1”
5. Clicked “Design Script” and written a small VB.net code snippet in the script main function.

Dts.Variables("Var1").Value = 20
MsgBox(Dts.Variables("Var1").Value)

Saved the application and tried to execute it. I got the above error.




Solution to resolve the problem:
1. The problem is due to binary code generation from the VB.net code. Double click script task, script and check the “precompileScriptintoBinarycode” should be true.
2. May be the problem is with the VB.net code. Please check the syntax of the code.
3. Else, the problem is with the Service pack. If you have older version of SQL Server 2005, you can’t have that option of converting into binary codes. In this case, you need to download the SQL server FIX from Microsoft site.
http://www.microsoft.com/downloadS/details.aspx?FamilyID=2ba43893-ef6b-4019-9e7d-d8cc6c7b905d&displaylang=en

I have installed x86 version, now its working fine.
Happy Learning!!!.
Regards,
Venkat

SQL Server 2005 connection error

SQL Server 2005 connection error:
Once Sql server 2005 – Database (SQLExpress) is installed in our machine. If we try to connect to the local or remote server, we usually get an error in connecting the database. Below is the error string,

TITLE: Connect to Server
------------------------------

Cannot connect to Server.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

I have searched in web for a proper solution. But, I didn’t find any proper solution and wish to log the solution in my blog.

As per the error, the problems are due to,

v Server’s remote connection is disabled.
v Protocol disabled for remote connection (TCP/IP).
v Alias creation or alias mismatch.

Solution:
Enabling Remote connection and protocol:

v Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.

v On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.

v On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

v On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Check the screen shot for the reference,

Resolving Alias Problem:

v Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, Click SQL Server configuration manager.
v Click “Aliases” you can see your aliases in the right pane.
v If your server’s name aliases is there just double click it and check your “server”, “port number” and “alias name”
v If there is no alias you need to create an alias. Provide your server name (machine name). Right click you’re my computer and you can get the name.
v You need to specify the port number check with 5000 and 20000 port numbers. Most probably it will work and it’s specific to your client.
v Now just click “Ok” button.
v Try to connect you SQL server database engine.
v Now it will work… cool… No worries.

Regarding creation of exceptions for your newly installed SQL Server 2005. You can check this KB, http://support.microsoft.com/kb/914277
Happy learning!!!
Regards,
Venkatesan Prabu . J

Finding subdirectories and directory depth in SQL Server

SqlServer provides an extended stored procedure “sys.xp_dirtree” to retrieve the folder structure for a particular drive.
EXEC master.sys.xp_dirtree 'E:\Test'
On executingh the query above, we will get below output,

The extended stored procedure will retrieve two columns,
1. Subdirectory – Shows all subdirectories inside the given path or directory.
2. Depth – Depth of the subdirectory from the given path or directory.

Regards,

Venkatesan Prabu .J

Foreign key relationship in sql server

This constraint is used to map a particular column in the child table with a primary key column in the parent table. Consider my scenario, am having an employee table and it will be having empid as primary key (Unique reference to identify an employee) , am having empProjects which will hold project id as the primary key.
I need to check, In which project an employee is allocated? Or what are the projects assigned for this particular employee?
The relation ship between these two tables is One to Many relationship (An employee can work in more than one project).
To retrieve the result set we need to have a foreign key relation ship between the employee table and empProjects table, a column empid should be created in empProjects table and it should refer the primary table “Employee”.
Let’s see a small example; I am trying to create two tables VenkatA, VenkatB. Id in the second table is referencing the ‘id’ column in the primary table.
create table venkatA(id int,[name] varchar(100))
create table venkatB(id int references venkatA(id),designation varchar(100))
OOPS, On executing the query above, we will get an exception.
There are no primary or candidate keys in the referenced table 'venkatA' that match the referencing column list in the foreign key 'FK__venkatB__id__7BB05806'.
It’s because of the column ‘id’ in the primary table. It’s mandatory to declare that particular columns as primary or candidate key.
create table venkatA(id int primary key,[name] varchar(100))
create table venkatB(id int references venkatA(id),designation varchar(100))
On executing the query above, you will get the second table referencing the first one.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

Retrieving physical location of your MDF and LDF file in sql server

I have started questioning myself like,
Is it possible to retrieve the physical location of my data file and log file?
I am struck up with retrieving details for data and log file location of our existing databases and found that the key table is located in the master databases. Below is the query to retrieve that information,
select * from [master].[sys].[master_files]
On executing the query above, we will get the below result set,

Now, lets tune up the query further, I am trying to retrieve data files physical path for my master database.

DECLARE @sql_path NVARCHAR(256) ;

SELECT @sql_path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1)
FROM [master].[sys].[master_files]
WHERE [database_id] = 1
AND [file_id] = 1 ;
print @sql_path

The output is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\”

Happy Learning!!!

Regards,

Venkatesan Prabu .J

My travel to Sydney, Australia

I travelled to Sydney for a short business trip. On 14 June 2008, I've started in Malaysian airlines from chennai at 10PM and reached Malaysia at 4PM.

OOPS, after waiting for 6 hours in malaysian airport, i have got my next flight to sydney.

It's really a cool place to work.
Endrum,
Venkat

Error message "Interface not registered"

Problem faced in SSIS package:

Today, I've found a peculiar problem in opening my SSIS package. I've tried to create a new SSIS package. OOPS, I found a message box showing "Error message "Interface not registered" ". After clicking Ok button, i didn't got a new package instead the IDE stood mum :-)

Reason for the problem:

1. Your previous installation files were creating some issues. It's because of the improper cleaning of your machine registry for your new installation.

2. Improper installation of your MSXML files.

Solution:

Try registering MSXML using the following commands in the command prompt (CMD)

a. regsvr32 msxml3.dll

b. regsvr32 msxml6.dll

Happy Learning!!!

Regards,

Venkatesan Prabu . J

T-SQL to fetch the tables with out primary key in the database

Today, i've tried to fetch table details from sysobjects. I have started digging this table to retrieve the table information about primary key checking, foreign key and other constraints details and arrived with a small code snippet to retrieve the tables which doesn't have primary key.

Code Snippet to retrieve the table without primary key

select name as TableWithoutPrimaryKey from sys.sysobjects where type='U' and objectproperty(id,'TableHasPrimaryKey') = 0

I've used Object property to fetch this information.

Happy Learning!!!

Regards,

Venkatesan prabu. J

SQL Teaser 2 - More magics in SQL Server

After getting the @ value, i've tried with the below code,

Code Snippet

SET ROWCOUNT 0 DECLARE @ int SET @ =6 IF @@ROWCOUNT = 1 PRINT 'yes' ELSE PRINT 'no' PRINT @@rowcount

The output is Yes and 0. I've dig for the answer and found it. The reason for "Yes" is, in the previous statement i have set the value SET @ =6 and after that am checking the @@rowcount. Obviously, it's value is 1. Thats why we got the output as "Yes" and in the last print statement we dont have any updation of rows. So its result shows 0.

Happy learning!!!

Regards,

Venkatesan prabu . J

SQL Teaser - Some magics in SQL Server

I found a superb question from one of the sql server expert and wish to write in my blog,
What could be the output?

Code Snippet

SET ROWCOUNT 0 DECLARE @ int SET @ =6 print @

I thought the @ symbol should come in the default declaration part and the above snippet should throw an error. OOPS, its not the case @ is considered as a variable.

Happy Learning!!!

Regards,

Venkatesan Prabu . J