Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Thursday, March 1, 2007

Stored Procedure-Importing CSV Files into Table -SQL Server

Posted by  |   |   |  No comments   
Here is the Stored Procedure code for importing comma seperated CSV files to table.


CREATE PROCEDURE [dbo].[sp_ImportScript]
@filepath varchar(2000)
AS
BEGIN TRY
declare @tmpsql varchar(3000)
set @tmpsql='BULK INSERT table'
set @tmpsql=@tmpsql + ' FROM ''' + @filepath + ''''
set @tmpsql=@tmpsql + ' WITH (FIRSTROW=2,FIELDTERMINATOR ='
set @tmpsql=@tmpsql + ''','''
set @tmpsql=@tmpsql + ',ROWTERMINATOR ='
set @tmpsql=@tmpsql + ''','''
set @tmpsql=@tmpsql + ')'
EXEC(@tmpsql)
select @@rowcount
END TRY
BEGIN CATCH
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
Share:
Wednesday, February 28, 2007

Performance Tuning on SQL Server

Posted by  |   |   |  No comments   
Performance is one of most important factor at the time of the application developement. If your application or website is not responding fast to user queries then it's accepted by the users.

Here are the some tips to make fast retrival of data from sql server.

1) Create Index- Indexes are great way to improve your performance. It uniquely indetifys the each row in table.

2) Avoid Cursor- Cursors are time consuming so avoid cursors as much as you can do more work with queries.

3) Avoid Joins- Joins are also time consuming so avoid it.

4) Create Primary and Foreign keys in tables

5) Be sure your tables must have atleast one column that uniquely indetifys each rows
Share:
Saturday, February 24, 2007

SQL Server Index

Posted by  |   |   |  No comments   
Some Times we want to retrive data fastly without wasting time to retrive it. SQL Server Indexes can greatly help to make data retrival fast and provide quick access to tables.
Therer are four type of indexes in SQL Server 2000/2005.
1) Unique Key Index
2) Clustered Index
3) Non Clustered Index
4) Compsite Key Index
Unique Key Index:
As the name suggest this index does not allow duplicate values in rows. So it checks whether the data is unique or it Other wise it will give a error.
Clustered Index:
Clustered Index dictates the physical storage order of the data in the table. A table can have only one clustered index.
Non Clustered Index:
A non clustered index is a seperate index structure indepedent of the physical storage order of data. Sql Server 2000 alllowed 249 indexes per table.
Compsite Key Index:
As the name suggest in the composite key index two or more columns of tables to make a single unit. Sql Server 2000 allow 16 indexes per table.




Share:
Thursday, February 15, 2007

Capabilities Microsoft SQL Server,Microsoft ACCESS, MSDE

Posted by  |   |  ,  |  No comments   
I have found a very good site with provide the comparison of Microsoft sql server, Microsoft and mdse capabilities on the basis of following.
  1. Number of instances per server
  2. Number of databases per instance / server
  3. Number of objects per database
  4. Number of users per database
  5. Number of roles per database
  6. Overall size of database (excluding logs)
  7. Number of columns per table
  8. Number of rows per table
  9. Number of bytes per row
  10. Number of columns per query
  11. Number of tables per query Size of procedure / query
  12. Number of input params per procedure / query
  13. Size of SQL statement / batch Depth of sub query nesting
  14. Number of indexes per table
  15. Number of columns per index
  16. Number of characters per object name
  17. Number of concurrent user connections

here is the link for that site:

http://sqlserver2000.databases.aspfaq.com/what-are-the-capacities-of-access-sql-server-and-msde.html

Share:
Tuesday, February 13, 2007

SQL Server 2005 Reporting Service.

Posted by  |   |   |  1 comment   
There are lots tool available to develop reports. It has it's own advantage and disadvantage. One of most popular reporting tool is crystal report also has some limitation.

Microsoft has introduced a new way to develop the reports directly from sql server....SQL Reporting Service. It was there in sql server 2000 days but we have to add it as external service. Now with SQL Serve r2005 it is there with the sql server 2005 itself.

It supports all the features such as cross tab,sub reports etc...
Share:

SQL Server 2005 Express Edition User Instances.

Posted by  |   |   |  No comments   
Sql Server Express edition is a small version of a sql server 2005 and that provides data storage capability up to 4 gb. It is easily ships with the each application so many users are using this version of sql server instead Microsoft access as a database for windows base application.
One of the problem using sql server instances is whenever you compile it will overwrite the existing database.

For Example, if you perform a insert operations from application and add new row to the database then again after compile the application. It will over right that database with older
database.

I have found a simple solutions to get rid of this problem. In solution browse to the database and select the database (.mdf files) then go to the property window. There is a property called 'Copy to output' select property and select 'Do not copy'.

Now when you compile the application it will not copy your older database to the bin directory.
Share:
Thursday, February 1, 2007

Previous week startdate and enddate in SQL Server 2005,SQL Server 2000

Posted by  |   |   |  3 comments   
s have found the way to get start date and end date of previous week with the help of the date part function of SQL Server 2005. It also work on sql server 2000 also.

Following are the code for finding start date and end date of previous week.

Set @STimeStamp=GETDATE()
set @PStartDate=@STimeStamp-DATEPART(dw,@STimeStamp)-6
set @PEndDate=@STimeStamp-DATEPART(dw,@STimeStamp)


Happy Programming
Share:
Thursday, August 24, 2006

Sql Server Escape Sequence

Posted by  |   |   |  2 comments   
Some times we are required to put '(Quotation Mark) in our sql stored procedure query.But '(Quotation Mark) will use for operation in sql server stored procedure. You can use escape sequence here.
for example you want to put 'jalpesh' in stored procedure then you should give
print '''jalpesh'''
here first two quota ion mark counted as escape sequence and then the third ' are counted as character.
Share:
Monday, August 14, 2006

sql server 7.0/2000/2005 resources list

Posted by  |   |   |  No comments   
Share:

Support this blog-Buy me a coffee

Buy me a coffeeBuy me a coffee
Search This Blog
Subscribe to my blog

  

My Mvp Profile
My Mvp Profile
Follow us on facebook
Blog Archive
Total Pageviews