About Pinal Dave
Pinal Dave is a common man who enjoys listening music, reading books, travelling places, watching movies and writing blogs. Pinal has over 6 years industrial experience gained through his work within the IT industry and SQL Server Community.
He is a highly respected and leading figure in Indian IT field. He is recognized through his work as founder of the highly useful SQL Server Site SQLAuthority.com. Microsoft has presented him SQL – MVP award for his extraordinary contribution as a SQL Server Expert.
Contact Pinal Dave
Email is always welcome, whether you’re interested in talking about projects, SQL Server 2008 and SQL Server 2005 technologies, NET(C#), ColdFusion, Web Architecture, have taken an online tutorial class at SQLAuthority.com, or have an event where you believe Pinal Dave would be a great speaker or panelist.
If you want Pinal Dave to review your product, book, website or code, please do not hesitate to contact him. Or, feel free to say “hi” anytime. All comments are welcome.
Search SQLAuthority.com
If you have any questions for faster response, Search SQLAuthority.com. It is possible that your question is already answered in one of the hundreds articles.
Child Rights and You:
If this blog has been helpful to you and if you want to help me. Please stand up for the child rights. Donate money to Child Rights and You by visiting their site directly.
pinaldave “at” yahoo.com
pinal “at” SQLAuthority.com
Dear Readers,
I have received many comments on this page.
I do my best to answer almost all the queries and questions.
If possible I answer them right here or if necessary I send email as well.
Due to length of current page, I have moved previous comments on archive page here : Contact Me – Archive 1.
Fee free to ask your doubts here.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
I need some help with performance issues.
we have sql server 2005 running;
kindly suggest me some scripts that I can run
and also a place I can get some scripts that I use.
or
pl suggest me some books.
thanks
sri
I am trying to restore a database that was provided by a vendor. I am running in a SQL 2005 environment and get the following message when trying to restore.
Msg 3241, Level 16, State 0, Line 1
The media family on device ‘C:\eMason Backup Files\eMasonClarifire_BOA_backup_200807110015.bak’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
Please advise
Hi Dave:
I am having a serious issue with merge replication on an SQL Server 2005 publisher/subscriber setup. The problem is on inserting a row into a table I get the following error:
523: A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding resultset was active.
There are only merge replication triggers on this table.
Have you ever seen this and/or know of a possible reason/solution for the problem?
Thanks for any assistance you can provide.
Hi Pinal,
I am in serious issue with writing a procedure for Slowly Changing Dimension Type 1 and Type 2 and Fixed Attribute. I have two table new table (i.e reference table) and old table. I want to compare them and if a column is changing attribute it should append the data. If it Type 2, then it should insert a new row with Statusflag column with Current and the old record with a Statusflag ‘Expired’. and if for the fixed attribute column the value is changing, it should not change.
Can you please help me guiding me with the logic for solving this issue.
Thanks a lot.
Zee
Could you please get me the Table definition, also the SCD columns – column name with what type ?
Hi Pinal,
I want to know that how we can find maximum no. from char field in sql Express 2005.
I am using stored procedure to find max. value which works well for int value but it not work for char value.
Thanks.
If it is for varchar value,
select max(varchar_col*1) from your_table
Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx
Select max(cast(char_field as int)) from your_table
Pinal,
Can you tell me how do i convert data from excel sheet into a web page by using ASP.net, I need solid solution for this please help me yaar
Hi Pinal,
Could you please give some notes on replication in SQLserver & Transact sQL made easy … would be very helpful…
Thanks.
hi,
i read this site and learn too concept in sql server.
i have 3 + exp. in .net with sql server exp.
i want to good job in good it company,
if any oportunity like me , please info me
thanks
hi sir,
this is anil yadav from mumbai.Actually i m doing a coures of SOFTWARE ENG. which all kind of software knowledge.I want to which topic will be help for me in future i mean to say that whether SQL will be better or .NET.Which one more favourable and profitable in future for me.On which subject should i concentrate more.
thank you
@Naresh,
I have no idea how one can pick up a maximum value from characters,
I guess you are trying to find out a record which is very long in length and that column has a char or varchar or nvarchar field,
I would use this,
CREATE TABLE EXAMPLE11 ( EID INT, ENAME VARCHAR(100))
INSERT INTO EXAMPLE11 VALUES ( 2, ‘THIS IS SMALL’)
INSERT INTO EXAMPLE11 VALUES ( 1, ‘THIS IS VERY VERY BIG’)
SELECT * FROM EXAMPLE11
WHERE LEN(ENAME)= (SELECT MAX ( LEN(ENAME))FROM EXAMPLE11)
I am using len function, which calculates the len of the character and I am findingout which record has maximum characters.
Hope this helps,
Thanks,
Imran.
how set null row value when they more than one time in row ?
I dont unserstand your question
Can you give us more informations?
Pinal,
Can you tell me how do i import the data of single excel sheet into a multiple tables in sql server.
For example if i have a sheet with fields branch name, district name ,state name,branch address …
I need this sheet to be converted to 3 tables(sql server) of branch , state, district .. and branch table should include ids of state and district
I need solid solution for this please help me..
Hi Pinal.. one question..
who is the best for performance?
If exists (select * from where )
statement1
else
statement2
or
If exists (select 1 from where )
statement1
else
statement2
or
If exists (select from where )
statement1
else
statement2
or
If exists (select top 1 <anyone from previous (*, 1 or from where )
statement1
else
statement2
Thanks in advance!
regards.
Adrian
When you use exists in IF clause, you wont find differences
SELECT *, SELECT 1, SELECT TOP 1 all perform same
Hi Pinal,
Could you please give me some notes on comparison with transactions written in TSQL & the transactions written in ADO.NET.
Thanks in advance
Divz
Hi Anoo,
I dont know what exactly you need to do here. But still if it is just importing of Excel Spread sheet into tables, I think DTS package will be the best option for you. Specially if you are going to do it regularly.
Let me know if you have questions on this.
Thanks!!
Hardeep Singh.
Hi Pinal,
Nowadays I am working on SQL 2000 DTS to SSIS packages. During some of the transformations in SQL 2005 I am receiving following error:
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at Data Flow Task, OLE DB Destination [76]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “Communication link failure”.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “TCP Provider: An existing connection was forcibly closed by the remote host.
Error: 0xC0209029 at Data Flow Task, OLE DB Destination [76]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “OLE DB Destination Input” (89)” failed because error code 0xC020907B occurred, and the error row disposition on “input “OLE DB Destination Input” (89)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “OLE DB Destination” (76) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
During development of SSIS packages we are using the SQL 2000 DTS packages for transferring data in SQL 2005 server and it’s giving the same error during transformations. So I have tried the same transformation by using SSIS package and it’s also giving same error.
I have read different articles on the sites but not yet fixed my problem. Can you please help me out for the solution to fix this problem ?
Thanks
Vijay
Hi Pinal,
I got error related to pool connections max limit in SQL server 2005,
would you please help me in SQL server restart scheduling automatically to release unused pool connections.
Regards and Thanks,
Priyank
Hi Pinal,
I am trying to execute a Stored procedure using vb 6.0 & database being Sql Server 2005, wherein a Field is been inserted into selected number of database from the list box…
DB_LIST = DB_LIST + “‘” & LST_DBNAME.Text & “‘, ”
db_list holds database name seperated by commas & passing a single quote to da Database name.
its like
exec UPDATEFIELDS ‘temptable’, ‘databasename’, ”’databasename1”, ”databasename2”, ”databasename3”’
here databasename1,2,3 is selected from listbox
This runs very well in Sql Server 2000 but in Sql server 2005, it does not get executed & holds itself at start inside declare cursor where it searches for Database Name from master -> sysdatabases
when i run da same in Management Studio it Runs smoothly
really confused…
please help me fix the problem
Thanx anyways…..
Good Day
Hi Dave,
I’m using SQL with the Northwind database.
I’m trying to do a query that displays the top 2 invoices for each vendor, in descending order, by invoice total.
The trouble is, for the result set I’m only getting 1 vendor with 2 invoices and that’s it.
How would you suggest I write the query that would show each vendor, with their Top 2 invoices?
A reply to my e-mail would be a huge help! Many thanks in advance.
KK
Refer this post
You can find various methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
HI Pinal,
I’m new to writing queries in mssql. Need some help in writing a cursor. There are 2 cols in my table 1) CaseDate and 2) CaseId. CaseId have multiple entries with a different timestamp in the CaseDate col. i’m trying to create another col which will contain the min(CaseDate) of that CaseId.
Eg: below is the existing data
CaseDate CaseID
2008-04-16 11:07:36.000 1600195687
2008-04-16 11:07:58.000 1600195687
2008-07-21 15:15:55.000 1600331971
2008-04-05 16:16:06.000 1600331971
NULL 1600331971
What i’m lookin @ is
CaseDate CaseID CaseDate2
2008-04-16 11:07:36.000 1600195687 2008-04-16 11:07:36.000
2008-04-16 11:07:58.000 1600195687 2008-04-16 11:07:36.000
2008-07-21 15:15:55.000 1600331971 2008-04-05 16:16:06.000
2008-04-05 16:16:06.000 1600331971 2008-04-05 16:16:06.000
NULL 1600331971 2008-04-05 16:16:06.000
Can u please guide me to write a cursor (or whatever performs good)
Best wishes,
Vallabh
Hi Vallabh,
Please find below code snippet for your answer. Also note that, example below create a temporary table. You can replace this with the actual table.
create table #temp (
CaseDate datetime,
CaseID int,
CaseDate2 datetime )
insert into #temp(CaseDate, CaseID)
select ’2008-04-16 11:07:36.000′, 1600195687
union all
select ’2008-04-16 11:07:58.000′, 1600195687
union all
select ’2008-07-21 15:15:55.000′, 1600331971
union all
select ’2008-04-05 16:16:06.000′, 1600331971
union all
select NULL, 1600331971
update t1
set t1.CaseDate2 = t2.Min_CaseDate
from #temp t1
inner join (select CaseID, min(CaseDate) as Min_CaseDate
from #temp
group by CaseID) as t2
on t1.CaseID = t2.CaseID
Hi Pinal,
First of all, thanks for all knowledge and articles and most important for sharing with us. I always look forward to new post and always learn something from them. I did not find much on SQLDiag. Could you share some tips on how to start using SQLDiag?
Thanks again,
Will T
Hi Pinal,
I need a SP which inserts IP Addresses which are not found in the table. When we supply the range the SP should insert the IPs found with in the range. With all the combinations.
Example: 192.168.0.0 – 192.168.255.255
Please reply soon. Your help would be of much use.
Thanking you!
Regards
Titus
Hi Pinal. Just checking to see if this is the same Pinal that worked with me on Eva’s MS Access Anesthesiology database about 5 yrs ago.
If it is, HELLO !!!
Hello Pinal,
I am working on a Financial Project which deals with rural branches .The database size is incresing day by day and it takes about 30 min to do the SOD 2 months back it used to take only 5 min.I think do we have to rebuild all the indexes.Can u tell me how perfomance and Tuning is done in this scenario.
Actually the whole project is set up on merge pull replication which is running with 6 branches live on Production.The whole end of day and start of day is done from the Haedoffice.Plz revert me if I mislead u somewhere.
Thanx,
Amit
Hello Mike Weltman,
Yes, I am the same Pinal Dave. You were one of the first person to introduce me to database systems. I thank you for the same.
Kind Regards,
Pinal
Hello Pinal,
I have read your blog and it’s very useful for my knowledge. I am very interest with your personal profile and track record. I have problem with my SQL Syntax. How can i copy the data from one table to another table but the structure a both of table are different. Some field are same.
Please help my syntax..
Thanks,
-BS-
Insert into target_table(column_list)
select required_column_list from source_table
Pinal, I am a beginner and just wanting to get my foot wet in learning SQL and wanting to learning more programming. I was looking for some quick tips on how to start learning SQL. How or what steps do I need to take to download a SQL server database to start practicing. I also emailed you on this question. Thanks!
hi
I want to generate a script,should contains both the structure and the data existing in the database.Is ii available in sql server 2005
thanks in advance ?
It is not available in version 2005 but available in version 2008
One option is take script of the tables
Copy the data to text file (using bcp)
Hello
Can I insert multipla language rows in their language syntax or charset in a table
For example I have A table with 1 field varchar(50)
Rows are
Example ( in English)
Örnek ( in Turkish)
BeiSpiel ( in German)
??????? (in Russian)
Hello! I have read your site and have seen some very nice suggestions and tips! One thing I was hoping to see was how would you suggest importing a text file that has no column delimiters (its fixed columns, which I know the definitions of) but the record LENGTH is variable. Some records are 127 bytes and some are 323 bytes.
DTS, Bulk Import, SSIS, nothing seems to be able to handle this sort of scenario. Variable record length files are normal and common but with SSIS having a problem loading them, how would you recommend getting around it?
Thanks!
Just curious , why this name SQL authority?
Is this some organization or company?
Hello Pinal,
I searched your website for CDC (change data capture), to learn what SQL server has to offer in this respect. I haven’t found any articles. Do you have any in pipeline regarding this topic?
Thanks,
Mahi
Hi Pinal,
I was wondering how in SQL we will select the top 10 salary people from a database table.
Thanks in Advance
Daniel
Refer this to know hot to get it using different methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
hi Pinalkumar,
i need some clarifications for how to access two different database table in a Quey
Another one Question
i wrote the split function for the sql2005 that function i need to call query its possible or not some more details for that
1 SELECT columns from DB2..table
2 select * from dbo.your_split(‘some values’)
Pinal Dave,
Thanks for providing the valuable Articles. I am new to SQLServer2005, I worked as a Oracle DBA. How to change the TIMEZONE to UTC TIMEZONE in SQLServer2005.
In Oracle we can issues the ALTER DATABASE SET TIME_ZONE = ‘ ‘ …is there any commands to change in SQLServer2005.
Thanks in Advance.
DBAtor
Pinal Sir,
You are true god of SQL. I wish to meet you once. You and your site has helped me so much that if I can just see you once, my whole life will be worth of it.
I can only manage my job and feed my family because of you. I read your site and teach developers SQL.
You are true Hero!
Joe
I want to start your fan site.
Luve u boss.
I am trying to make a query
tarih=datetime;
exp ’01.01.2005 22:00:00′
“select tarih from th_randevu”
ı only wantto select date like ’01.01.2005′ from table”
ı dont want to select time
please help ,thx.
select tarih from th_randevu
where
tarih>='20050101' and
tarih<'20050102'
Hi Adrian Galende
See following link for select 1 vs select *
http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/
If exists (select from where )
statement1
else
statement2
or
If exists (select top 1 <anyone from previous (*, 1 or from where )
statement1
else
statement2
Above syntax not clear.
Hi Pinal
Please can you help!
I have an SQL Query which exports records, using ODBC, from a business package called ‘De Facto into a FileMaker 6 database.
Part of the query says :-
- where sltr_date >= ’01 jun 08′ and sltr_date = ‘&start’ and sltr_date <= ‘&end’
(‘start’ and ‘end’ being the FileMaker databse fields)
Thanks in advance!
Duncan
hi all,
how can i get field name and type etc. in MS-SQL server 2005. is there any query available???
Select the table and hit Alt+F1 in query window. You would get complete details on columns, datatypes, indexes, constraints etc
These are the diffrent methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/28/different-ways-to-know-structure-of-a-table.aspx
@ugur oral
create table example ( cola datetime)
insert into example select ’01.01.2005 22:00:00′
select convert(varchar(10), cola , 104) from example
Result: 01.01.2005
Hope this helps,
Thanks,
Imran.
Alsways use unambigious date formats
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
@mohan
use database_name
Sp_help table_name
This stored procedure gives all the details of column, their types, any indexes, any constraints, any identity columns and some good information for that particular table.
Second method:
select column_name ‘Column Name’, data_type ‘Data Type’, character_maximum_length ‘Maximum Length’ from information_schema.columns where table_name = ‘table_name’
Hope this helps,
Imran.
or
EXEC sp_columns 'table name'
Very nice Banner.
Sir, In Bangalore we have created fan club of yours.
We meet twice a week. Currently we have 46 members in our group. We pick any of your article and discuss in depth.
We are learning group so we are going to stay away from fancy websites or sponsers or funding. We just meet and learn. We use your website as our base.
Would it be possible for you to visit us once? Please please sir, it will be our dream come true.
Maya Thanki
(Member – SQLAuthority Fan Club, Bangalore)
Dear Pinal Dave,
My Hearty Greeting to you!
I would like to learn some sql server cmd in DOS prompt which book is good?
Please advice me the same.
Thanks,
JSaraboji.
Dear Sir,
Thank you for sending us email. I do understand that you are skeptical about SQLAuthority Fan Club. Believe me it exist and I will send you photos of it next time when we meet this week.
Maya Thanki
(Member – SQLAuthority Fan Club, Bangalore)
[...] 5, 2008 by pinaldave In today’s article we will see question of one of reader Mohan and answer from expert Imran Mohammed. Imran thank you for answering question of [...]
Help please with a dynamic data source in Reporting Services/SQL2005.
1. I have a completed report that works with the following connection string:
Data Source=BUTTERFINGERS\SQLEXPRESS;Initial Catalog=VC
2. I am trying to replace the database name, in this case VC with a variable so the user can select which DB to run the report against as per the instructions from MSDN and a few other sites.
3. When I change to:
Data Source=BUTTERFINGERS\SQLEXPRESS;initial catalog=Parameters!DatabaseName.Value and add a report parameter in VS2005 and run I get the following error:
Cannot open database “Parameters!DatabaseName.Value” requested by the login. The login failed. Login failed for user ‘ADPRO.COM.AU\steveha’.
4. What I find interesting is that of course it cannot login with “Parameters!DatabaseName.Value” as this is not a valid DB, it is the variable name. It never seemed to riun the report and prompt me the report parameter to enter a valid DB name.
5. I would have thought that when the report was run it would ask for a DB name via the report parameter, replacing the variable name with the actual string or name of the DB and then run so in essence the connection string would be the same as my original working string? Credentials set to Use Windows Authentication, Type: Microsoft SQL Server, Use shared data source checkbox off.
Note I am a beginner.
Have I lost the plot here:)
Pinal,
I want to combine two colums from two different tables where is no unique identifier.
Eg:
Table 1 :pen
item description Qty
Table 2: Pencil
Product supplier orderdate
I want to combine item from table 1 and orderdate from table 2.
Thank you
select * from pen
union all
select * from pencil
Select Pen.Item, Pencil.orderdate
from Pen, Pencil
But note that, as u say there is no joining column, this gives u cardinality…
Hi Pinal,
SQL HELP
We have some issue regarding sql server on our website due to which our site is down since morning.
Could you please mail me your contact number so that I can brief you about the same .
Thanks ,
Vishal
i want to Set first day of week to Monday as oppose to default Sunday.
Is it possible through any sql setting.
(for permanent setting according to user)
example:- if i change in windows registry start date 0 to 7 . then our calender show according to my setting. i want same thing in sql server 2005??
Read about Datefirst option in SQL Server help file
Hi,
I want to create new copy of database on same server with data and all elements i.e. views, stored procedure etc.
It is SQL server 2000.
Is there any way to achieve this using SQL that I can run from VBScript?
Thanks
Chetan
Take a backup of the existing database and restore it as new database
Hi,
I want to create a temp table and wants to add columns dynamically with autogenerated columns names.
example: i have given StartTime and EndTime for a day
@StartTime = 9
@EndTime = 6
now i want to create table with autogenerated columns from 9 am to 6 pm [ columns names should be 9, 10, 11, 12, 1, 2, 3, 4, 5, 6 else it can be 9 am, 10 am, 11 am, 12 pm, 1pm, 2pm, 3pm, 4pm, 5pm, 6pm]
how can i achieve this, its urgent
Thanks in advance
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Using that procedure
select dateadd(hour,number,'9:00:00') as dates into #t from master..spt_values
where type='p' and number between 0 and 9
EXEC dynamic_pivot
'select dates from #t',
'convert(varchar(10),dates,108)',
'Count(dates)'
Hi Sir,
i want to Set first day of week to Monday as oppose to default Sunday.
Is it possible through any sql setting.
(for permanent setting according to user)
example:- if i change in windows registry start date 0 to 7 . then our calender show according to my setting. i want same thing in sql server 2005??
hello pinal sir,
i am really thankful of yours as whenever SQL server have problem with me your posts work as a guide for me.
But since last 2 week i am continuously fighting with the SSIS package.
As i have made SSIS Package for Traansfering data from .CSV file to table in my database.
While creating these SSIS PAckages I have tried with all the Package Protection Level,but still while executing these SSIS packages i m not getting my password for the User in connection string.every time i have to update it manually.
Bcoz of this i m also not able to make SP pr JOb for automation of Package Execution.
So can YOu plzplzplz help me in this.
i will be relally a grate thank full of urs.
as i m trying since two week i not successed.
any kind of help from any one should be appriciated.
Thanks in advance.
Regards,
Jigar
Jigar,
When you are referring passwords w.r.t packages, these are meant for security purposes to prevent others to modify the package. Package Passwords comes handy. Once you set this, every time you open the package, it asks. However, the password for User in Connection string is something to be configured dynamically. Refer Package Configuration in SSIS
I want to generete the time with 30min gap and add that column to #temp table
like this 09:00am, 09:30am, 10:00 am ….. 04:30 pm, 05:00 pm
I wrote this SP but its adding @test as column name but i want to value inside the variable should be column name. if i set @test = 10 then the column name should 10 and if i set @test = 11 then the column name should be 11. how can i achieve this
—————————————————————–
CREATE PROCEDURE [dbo].[TestingAvailability]
AS
BEGIN
DECLARE @VisitingHoursStartTime AS INT
DECLARE @VisitingHoursEndTime AS INT
SET @VisitingHoursStartTime = 9 — morning 9 am
SET @VisitingHoursEndTime = 18 — evening 6 pm
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[t1]‘) AND TYPE IN (N’U'))
DROP TABLE t1
ELSE
CREATE TABLE t1(Id NVARCHAR(50))
DECLARE @test NVARCHAR(100)
– Loop to add columns to temp table
WHILE (@VisitingHoursStartTime < @VisitingHoursEndTime)
BEGIN
SET @test = @VisitingHoursStartTime
IF NOT EXISTS ( SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(‘[dbo].[t1]‘) AND NAME = ‘@test’ )
ALTER TABLE t1 ADD [@test] NVARCHAR(100) NULL
SET @VisitingHoursStartTime = @VisitingHoursStartTime + 1;
IF @VisitingHoursStartTime = 19
BREAK;
END
SELECT * FROM t1
DROP TABLE t1
END
===================
its very urgent
I changed my code and created dynamic sql, it worked
————–
SET @DynamicSQL = ‘ALTER TABLE #Temp ADD ['+ CAST(@VisitingHoursStartTime AS NVARCHAR(100)) +'] NVARCHAR(100) NULL’
EXECUTE (@DynamicSQL)
I have a problem in writing a query for the following logic.
For the same custID and transactionNO there are few records matching as shown below.
In a such case i have to get data to Prd Column from other lookup table and update into Dest Column based on the following condition
1) If the value in Indicator coloumn is 1 and RelKey column is having 4005 then i need to set the value in the dest column for the respective row with Data from lookup table
2) If the value in Indicator coloumn is 0 and RelKey column is having 4005 then i need to set the value in the dest column for the respective row with 0
3) If the value in indicator column is 0 and relkey column is having Null then also i need to set the value in the dest column for the respective row with Data from lookup table
I have millions of records in this table so without using cursor, how do i write a qurey for this logic.
CUSTID TransactionNO Indicator RelKey Prd Dest
123456 Trans12345 1 4005 A1 X
123456 Trans12345 0 4005 A2 X
123456 Trans12345 0 4005 A3 X
123456 Trans12345 0 NULL A4 X
98765 Trans98765 1 5675 A5 X
98765 Trans98765 0 5675 A6 X
98765 Trans98765 0 5675 A7 X
98765 Trans98765 0 NULL A8 X
98765 Trans98765 0 NULL A9 X
98765 Trans98765 0 NULL A10 X
Hi Pinal,
Thank you very much for an awesome website. It has certainly helped me out of many a jam.
I am trying to get this stored procedure to execute but am having some difficulty.
I call the stored procedure from an ASP page using the ADODB.COMMAND and passing two parameters to the sproc.
The code works fine but the sp does not execute.
Here is the sp:
******************************
USE [xxxxxx]
GO
/****** Object: StoredProcedure [dbo].[xxxxxxx] Script Date: 08/08/2008 07:55:25 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[xxxxxx]
@inputfile VARCHAR (500),
@outputfile VARCHAR (500)
AS
DECLARE @ifile VARCHAR(500)
DECLARE @ofile VARCHAR(500)
DECLARE @sqlcmd VARCHAR(8000)
SET @ifile = @inputfile
SET @ofile = @outputfile
SET @sqlcmd = ‘SQLCMD -Sxxxxx -d”xxxx” -Usa -P”xxxxx” -i”‘ + @ifile + ‘” -o”‘ + @ofile + ‘” -h 2000000 -k1 -W -s”,”‘
EXEC master..xp_cmdshell @sqlcmd
**************************
Any comments will be greatly appreciated
Hi,
is there a stored procedure to display the records of all queries executed in an sql server
if not, can anyone help with a script to display the history of logins and the queries executed by the login
One option is to run
EXEC sp_who
Hi
I am in here big probleam my database is replicated and my log size is 55gb how i can shrink.please Help me As soon as possible i am in here denger position about this issue
please Help me
Regards
Tito
Hello,
I’d like to have on the same PC :
- SQL Server 2005 Standard
- SQL Server 2008 Standard
At this time, I have “only” the 2005 version. Could you tell me, if it’s possible, how to install the 2008 version ? As .NET developer, I’d like make some tests (with VS2008) on both database. I know to use SQL2008 we have too wait the VS2008 SP1.
Regards,
Hi Pinal,
Could you please give some notes on SQL profiler..
as i am new to this..
Thanks
Prajin
Refer SQL Server help file
It has more details
Is it possible to take Backup and update using table at the same time ms sql 2005? If it is yes please let me know the updated rows in the table also available in backup file.
Thanks
Hi Pinal
Well you doing great job by helping others here ……
i wanted some information like how to run sql server from sqlcmd i mean in 2005 and how to run sql server in a single user mode and muti user mode and one more thing there is a option to disable graphival view when sql server starts in sql cmd what is that….
please show me with examples…..
thanks a ton
Vaibhav Mathur
9227900650
Hi my name is sushma,
I am very new to SQL SERVER. How can i approve for best result. please help me for programs and i need some notes . Please help me on this . I am very much interested to do job so please help me.
Thanks ,
Sushma
I have a problem. I had SQL 2000 set up a database maintenance to back up the mdf and ldf files every night. It was backing up but not truncating. Now, our data does not have current data. We do have the log file and I did manually truncate it, thus committing the transactions so going forward we are ok.
Here is the problem. We need the february 29th database. Our back up software has it but the LDF file is current and the MDF file never updated. How do I selectively update a database from the log file of only the transactions up to February 28th? I have a test server with SQL installed. I just dont know what to do.
To recap.
Until Yesterday our production database has only been updated by the log through the end of the year. The LDF file just grew. I made copies of these files (backups) then I manually truncated the log file and commited those transactions.
Now, how do I commit transaction from the log file to the database only through Feb 29th. Does that make sense?
Any help would be great.
Thanks!
_Chris
If you are wondering why, it’s because the software does a monthly export and we think something was wrong with that month but need to look at it before the March 1′st export of GL transactions.
@vaibhav
By default sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
If you still have that file located at the same location then you can run this command in command prompt.
1. For named instance :
sqlservr.exe -m -s
2. For default Instance :
sqlservr.exe -m
If you changed the location of the file, I dont know how to do this, I usually do it through services,
Click start, run, type services.msc
select the sql server you want to run in single user mode, double click server name, click stop and then in parameters give -m value and then start, sql server will start in single user mode.
if you want to remove sql server from single user mode, stop sql server and this time dont give any parameter. sql server will start in normal mode.
Hope this helps,
Imran.
I have 4 indexes (1 clustered and 3 non-clustered) on a table which is 105 GB. I want to run the following code to rebuild all the indexes on this table:
ALTER INDEX ALL ON Product REBUILD
Question is – During this operation will other users be able to access the product table? I know SQL 2005 supports online index operations. But wasn’t sure if it supports the following data types:
7 columns and the data types for them are as follows:
3 – uniqueidentifier
1 – varbinary(16)
1 – int
1 – image
1 – tinyint
Can index be created with online option for this table? My SQL version is 2005-64 bit.
Appreciated any input
Hi Pinal,
I want to discuss about indexes
what are the best techniques to make indexes because sometime performance increase or sometimes decreases if index will not make properly
kindly comments on this
thanks,
SJ
Hello Pinal
I am Using SQL SERVER 2005 and I have made 2 instances
1.Default(MSSQLSERVER)
2.SQL05
Default instance is working fine with remote connections but
named instance is not working.It uses another port i have checked with SQL Surface Area configuration and i have checked also TCP/IP dynamic port 0 and TCP/IP port is a copy of below defined port through Configuration Manager
All in one
I have a problem with remote connection on named instance
thanks,
SJ
@SJ
1. Make sure you have sql server broswer service running ( but if you can connect to default instance that means your browser service is on ).
2. IN SQL Server 2005, when you create a names instance, the named instance will be the name given inluding the machine name,
lets say the machine name is PROD and you named the instance as SVR1 , then your server name will be
PROD\SVR1. try using this method.
3. can you please post the error you got when you tried connecting to named instance, remotely.
Thanks,
Imran.
Hi Pinal,
Interview questions are really useful.Could you please provide examples for joins and keys(primary,foreign and unique).In most of the interviews they give tables and as us o join those tables.Please provide examples.
Thank You.
Raji.
Hello Sir,
when i am working the below task it gives result as below
please advise the
task : to find the size of a table?
query : exec sp_spaceused [tablename]
result :
name rows reserved data index_size unused
Tablename 145 888 KB 248 KB 16 KB 624 KB
here
name means — tablename
rows means — no of rows in that table
i have little confusion on “reserved,data,index_size,unused”
please advice how it calculated.
Thanks,
venkat
FROM SQL Server help file
reserved
varchar(18)
Total amount of reserved space for objname.
data
varchar(18)
Total amount of space used by data in objname.
index_size
varchar(18)
Total amount of space used by indexes in objname.
unused
varchar(18)
Total amount of space reserved for objname but no yet used
Hi Pinal,
I have 3 tables in one server database and want to copy that data in to my Local database (b’cos I want to work with that local data with out effect that original data)
Please help me do this.
Take a backup and restore in your system
Hi
I have an issue i have a table that has many rows
for example
table A
a b
xfactor 20
xfactor 20
media 30
media 30
how can i sum the individual eg i wana add the value of all the xfactor in a table how can i get that
thanks Namita
select a,sum(b) as b from table
group by a
Sub : MSSQL DBA vs. MYSQL DBA
Dear Pinal ,
I would like to thanks & appreciate from the bottom of my heart for your blog.
Currently I am working as a System Administrator on Windows Platform
I am very interest & Passion to start to work as a DBA.
As some of my colleagues are said that go for MYSQL DBA
I am in bit confuse to choose
MSSQL DBA vs. MYSQL DBA which will have sound bright carrier and future?
I am kindly requesting to give you advice
Thanks in advance
Regards
Kalyan.
Hi pinal,
i have a table with pvn_no, alert_name, and email_addr.
pvn_no alert_name email_addr
10 AL1 AKS@C.C0M
10 AL1 LAK@V.COM
10 AL1 HSJ@B.COM
10 AL2 YET@J.COM
10 AL2 KOI@D.COM
20 AL3 POL@M.COM
20 AL3 WGE@S.COM
NOW I WANNA A RESULT SET LIKE
pnm_no alert_name email_addr
10 AL1 AKS@C.COM,LAK@V.COM,HSJ@B.COM
10 AL2 YET@J.COM,KOI@D.COM
20 AL3 POL@M.COM,WGE@S.COM
for each combination of pvn_no & alert_name we have 8 to 10 email. how can i concatenate them in a single row.
Thanks in advance
Aks
Hi,
Please find below code snippet for your question. Run the same and experience the solution
create table tab_example (
pvn_no int,
alert_name varchar(10),
email_addr varchar(100))
insert into tab_example
select 10, ‘AL1′, ‘AKS@C.C0M’
union all select 10, ‘AL1′, ‘LAK@V.COM’
union all select 10,’AL1′, ‘HSJ@B.COM’
union all select 10,’AL2′, ‘YET@J.COM’
union all select 10,’AL2′, ‘KOI@D.COM’
union all select 20, ‘AL3′, ‘POL@M.COM’
union all select 20, ‘AL3′, ‘WGE@S.COM’
create function udf_csv_data(@pvn_no int, @alert_name varchar(10))
returns varchar(1000)
as begin
declare @sql varchar(1000)
select @sql = coalesce(@sql + ‘, ‘, ”) + email_addr
from tab_example
where pvn_no = @pvn_no and alert_name = @alert_name
return @sql
end
select pvn_no, alert_name, dbo.udf_csv_data (pvn_no, alert_name) as csv_email_addr
from tab_example
group by pvn_no, alert_name
Dear Sir,
Thank you for visiting our organization and teaching our kids SQL. We are ready to pay any fee if you can visit us one more time.
I think you have inspired people here.
Thanks,
Roger Ben Silva
.NET Learning Institute of India
I have to create maintenance plan on sql 2005 to check the log and mdf file size ,I want sql script to chk this and notify admin
Pinal,
Thank you for the great site. Here is my query question:
How do I add an extra row to a query restult set that is not part of the table being queried?
i.e.
I have a query that returns this:
2008
2007
2006
2005
2004
2003
I would like the query to return this:
2008
2007
2006
2005
2004
2003
All
So I want to add the string ‘All’ to my query result. ‘All’ is not, and should not be, part of the table I am querying.
Here is my current T-SQL code:
SELECT DISTINCT [Program_Year] FROM [DB1].[dbo].[Projects_All]
How can I modify that query to add ‘All’ into the results?
I could have sworn I knew how to do this in Access, but am having trouble getting it to work in SQL Server 2005.
Thanks,
Scott
Why do you want to do this?
Note that you should do this in your front end application.
If there is no option
SELECT DISTINCT cast([Program_Year] as varchar(10)) as [Program_Year] FROM [DB1].[dbo].[Projects_All]
SELECT 'All'
Hi Pinal
Good to see MVP with your name.
i have 3 question.
I want to do some data migration , for that purpose
1- i want to disable all relationships present in my database. so i can do my migration. ?
2- and also after complete migration , how i can enable the relationship again ?
3- I have a database which have more than 50 tables , and fist colum of everytable contains a serial number, but serial number is varchar field,
I want access maximum amount of number which is present in all table’s first colums.
Waiting for your earliest reply.
Best Regards
Sharjeel
hai sir
i want to know Sub queries in detail
@Scott
select date from mytable
union all
select ‘ALL’
hope this helps
If date is not varchar, you may need to convert it to varchar
@Daniel Ashton
select top 10 salary from mytable
order by salary desc.
Hope this helps
Also, below is one more method which is much faster
set rowcount 10
select salary from mytable
order by salary desc
set rowcount 0
We are mainly a SQL Server 2005 shop. Most SQL installations are on Windows 2003 and we are using Active Directory for SQL Logins.
Our problem comes with the way SQL Server Integration Services work. It requires Local Admin rights on all SQL Servers for users to be able to view running packages or depoly them.
Is there away to grant these rights (view/deploy packages) without having these users as local admins on the servers?
Thanks for any suggestions or ideas.
Dear Pinal,
I’m using SQL Server 2000. how to convert the following sample 1 to sample 2 ??
Sample 1:
Id Tag
1 a
1 b
1 c
2 a
3 a
3 c
4 d
4 e
4 a
(No null values in Id/Tag)
Sample 2:
Id Tag
1 a,b,c
2 a
3 a,c
4 d,e,a
Kindly advice me
Thanx in advance
Nandha,
Please find below code snippet
create table tab_example (
id int,
tag varchar(10))
insert into tab_example
select 1, ‘a’ union all select 1,’b’
union all select 1, ‘c’
union all select 2, ‘a’
union all select 3, ‘a’
union all select 3, ‘a’
union all select 3, ‘c’
union all select 4, ‘a’
union all select 4, ‘d’
union all select 4, ‘e’
alter function udf_csv_data(@id int)
returns varchar(1000)
as begin
declare @sql varchar(1000)
select @sql = coalesce(@sql + ‘, ‘, ”) + tag
from tab_example
where id = @id
return @sql
end
select id, dbo.udf_csv_data (id) as csv_email_addr
from tab_example
group by id
Dear,
I have faced some problem.
In my code I used unicode for type Bangla. When I save it into Access Database,
show error: Syntax error in INSERT INTO statement.
I used Visual Studio.Net, C#.Net.
Please, help me how can I solve this proble.
Zahid.
Hi.
Database Mirrioring Problems
I am getting the problem for database mirrroring, after the configration of the mirrioring
the error code is : 1498
configuration of principle endpoint is failed
configuration of mirror endpont is failes
Hi Pina,
I just migrate my database from sql 2000 to sql 2005 and ran a test for writing data to dbf file (ms foxpro). The data in sql is ‘float’
for ex. 4000. but when this data is writen to dbf the format change to 4,000.00. this is a problem because now my dbf database see it as 4 instead of 4000. This is a major concern since we are dealing with inventory.
Please help me on how/what setting I need to make
Thank you in advance for your help.
Dat
Hello Dave!!!
I’ve been meaning to write to you for a while now!
me and my partner are working on our thesis and everytime we encounter a problem with some query I google it and There you are! with the solution or at least most of the times!!
so Really apreciate it that you have this really really nice website!!!
I mean it ! it’s like 4 out of 5 times that your site comes to solve our doubts!!!
So Thanks a lot!
Regards From Caracas – Venezuela
Corina
iam using sqlserver 2005 .can u tell me how to get the first letter capital in a word using T-sql.for eg: good morning as Goodmorning
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2010/07/19/initcap-function.aspx
@Siju,
I am sure there must be a good way of doing this, I would use something like this,
create table example ( ename varchar(100))
insert into example values ( ‘good morning’)
insert into example values ( ‘hello’)
select upper(substring (ename ,1, 1))+substring(ename,2,len(ename)) from example
Result:
Good morning
Hello
Hope this helps.
Imran.
Check this out.
declare @str varchar(100)
set @str = ‘good morning’
select stuff(@str, 1,1, upper(left(@str, 1)))
Hello,
I am new to SQL Server2005 and am in the midst of importing 12 separate Access Tables into one database SQL Table.
I have created a SSIS package to loop through my Access Tables, but instead of it looping through EACH Access Table…it is looping through only the first Access Table 12 separate times, thus multiplying the data in this table 12 times!?
Please help…I don’t know where I am going wrong.
Thanks!
Zin
Hello
I want to apply search feature on my website.How to write stored procedure for the same.
Main problem is that i can easily select data from one table but when i have to select data from multiple tables and these tables are not linked with each other.How can it be possible.
Please help me or drop me mail at above mentioned addreess.
Thanks
Can you give more informationson how you want to search as there is no connection between the tables?
Hello Pinal,
Need help with one query.
This is my input table
Item—————–Qty
xyz —————— 0
xy1 —————— 0
xy2 —————— 0
abc —————— 1
ddd—————— 1
eee—————— 3
xys—————— 4
yyy—————— 4
nnn—————— 4
ppp—————— 4
ggg—————— 6
kkk—————— 6
lllllll—————— 7
I want the 3 least ordered Qtys,
This should be the output
Item—————–Qty
xyz —————— 0
xy1 —————— 0
xy2 —————— 0
abc —————— 1
ddd—————— 1
eee—————— 3
Help much appreciated.
Thanks
@tibya
I am sure there must be a good way for the same,
Ex:
create table example11 ( item varchar(100), qty int )
go
insert into example11 values (‘xyz’, 0)
insert into example11 values (‘xy1′, 0 )
insert into example11 values (‘xy2′, 0 )
insert into example11 values (‘abc’, 1 )
insert into example11 values (‘ddd’, 1 )
insert into example11 values (‘eee’, 3 )
insert into example11 values (‘xys’, 4 )
insert into example11 values (‘yyy’, 4 )
insert into example11 values (‘nnn’, 4 )
insert into example11 values (‘ppp’, 4 )
insert into example11 values (‘ggg’, 6 )
insert into example11 values (‘kkk’, 6 )
insert into example11 values (‘lllllll’,7 )
go
select * from example11 where qty in ( select distinct top 3 qty from example11 )
Result:
xyz 0
xy1 0
xy2 0
abc 1
ddd 1
eee 3
Hope this helps.
Imran.
Hi,
I have create a linkserver to read a vfp file with a photo field, the name and file STRUCTURE of vfp as follows:-
/*
ITEMPHOTO TABLE STRUCTURE
VFP: (ITEM_NO CHAR(10),ITEMPHOTO GENERAL)
SQL: (ITEM_NO CHAR(10),ITEMPHOTO IMAGE)
*/
The link server as follows:-
–EXEC SP_ADDLINKEDSERVER
– @SERVER = N’FOX2′, — YOUR LINKED SERVER NAME HERE
– @SRVPRODUCT=N’VISUAL FOXPRO 9′, — CAN BE ANYTHING
– @PROVIDER=N’VFPOLEDB’,
– @DATASRC=N’”C:\BTM\123\DATA1.DBC”‘
–EXEC SP_ADDLINKEDSRVLOGIN ‘FOX2′
After the server is created i checked to read the file without the photo field it is he read the file with no error, but with the photo field i have the below error :-
SELECT item_no,photo FROM OPENQUERY(FOXPRO,’SELECT item_no,photo FROM ITEMPHOTO’)
the error message as follows:-
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “VFPOLEDB” for linked server “FOXPRO” reported an error. The provider ran out of memory.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “VFPOLEDB” for linked server “FOXPRO”.
best regards
Hi All,
Has any one got a template of sql server build documentation/Disasater recovery plan that he/she would like to share.
I just need a tempplate I can use as a base for my own documentation.
Hi all,
Im using SQL 2005, when runninig a trace using Profiler and using the ‘tunning’ template the duration column is displayed in microseconds. I am running the trace to determine how long it takes for stored procedures to complete. I want to convert the duration column to seconds, how do i do that?
Thanks
Hi Pinal,
I have a problem about migration from SQL-2000 to SQL-2005.
Could you please give some idea about what are the changes are required (code/server level) after migrating the database from 2000 to 2005 with compatibility level 80? This would be really appreciated.
@ Hitesh,
If you migrate sql server 2000 database into SQL Server 2005, I am quite sure the compatibility of that database will still be set to 80.
70 – SQL Server 7.0
80 – SQL Server 2000
90 – SQL Server 2005
100 – SQL Server 2008
IF Compatibility is set to 80, NO matter if you are using SQL Server 2005, your database will still act as if, it is in SQL Server 2000.
To change compatibility, Right click database -> properties -> On left side of the dialog box, select option -> right side set compatibility to 90 ( select from drop down list) -> click ok …. now your database will act as if it is in SQL Server 2005.
Just to confirm, right click database -> click reports->standard reports – > disk usage, If you see a report then it is confirmed that your database is in SQL Server 2005 compatibility level.
Just do the reverse, set the compatibility to 80 and try to run a report, you will see an error saying, database is in compatibility 80.
Hope this helps.
Imran.
hi iam new to sql server and want to know how solve this can u give me the procedure for this
i have two tables A and B i have to join these tables in to
a another table C (append) and delete the duplicates and there are no primary key, can some on e give me the query for this and explain how it works
thanks in advance
Is there a way to move the data from the secondary database file to the primary database file? I asked this because we have a database were the secondary database was created by mistake and that has caused the full backups to fail.
Hi,
I have a problem with connection microsoft sql server 2005 and php, i need to retrive some data from mssql 2005 tables by using php the problem when i save the php file into htdocs file and open a browser by local server the browser display empty page or give a fatal error:
Fatal error: Call to undefined function mssql_connect() in C:\AppServ\www\gggg\adminloginz.php on line 12
could you please help me to solve this problem as i need to finish my project urgently.
note: i am not understand microsoft sql server 2005.
hi Mr.Dave,
How to compare the rows to columns.
Eg:
Table1
ColumnId
Column1
Column2
Column3
Column4
Column5
Column6
Table2
Column1 Column2 Column3 Column4 Column5
I want to know which are the Columns are not there in the Table2 when comparing with the Table1 rows
plz give me Query for this.
Regards,
Malik
@ Malik,
I am sure there must be a good way to do this, I might try something like this,
create table example1 ( columnid varchar(max))
go
insert into example1 values ( ‘column1′)
insert into example1 values ( ‘column2′)
insert into example1 values ( ‘column3′)
insert into example1 values ( ‘column4′)
insert into example1 values ( ‘column5′)
insert into example1 values ( ‘column6′)
go
create table example2 ( column1 varchar(max),column2 varchar(max),column3 varchar(max),column4 varchar(max),column5 varchar(max))
go
create table #result ( columnid varchar(max))
go
insert into #result select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = ‘example2′
go
select columnid As ‘Column Missing in Example2′ from example1
EXCEPT
select columnid from #result
go
drop table #result, example1 , example2
Result:
Column Missing in Example2
column6
Hope this helps,
Imran.
Check this out :
select t1.ColumnId from Table1 t1
where not exists (select 1 from sys.columns s
where s.name = t1.ColumnId
and object_id = object_id(‘Table2′))
or
select t1.column_name from information_schema.columns where table_name=’table1′
where not exists(select * from information_schema.columns where table_name=’table2′ and column_name=t1.column_name)
hi,
I need a help or suggestion from you.
I have a table, in that a column is encrypted(SYMMETRIC KEY encryption) in binary data format in the database. I have a SP to encrypt the plain text which receive an input and gives the encrypted data.
I have to copy the whole table to same structured table in the different database as a batch update. I created a SP which receive the source table in XML. Everything working fine except the encrypted data.If try to decrypt the data after the copy, I am not getting the original data.
So have plan to encrypt the plain text once again before insert into the destination table.But stuck in calling the SP in the batch insert(which provide the required encrypted by receiving the plain text). I am not able to convert that procedure to function also.
I tried with cursor by looping each record, encrypting and insert into destination table.Its working but take so much of time.
my questions:
how to maintain the format of encrypted data while passing thru XML, or any way to convert the binary encrypted data to XML string and in the SP again convert it into binary data in batch insert and update.
Is there any possibility to call the stored procedure within the batch insert statement like user defined function
Insert into table_1 values(number, sec_data)
(@row_num,sp_encryp(@plain,@encrpy))
Please advice me
Hello Imran Mohammed, Pinal Dave,
Imran has already give me a solution to my previous query and this one is the similar to it (actually, this is the one I was looking for), so addressing this to Imran.
I am a Intern and trying to learn sql as much as I can in this project. My queries may seem very simple to you all, but for me this is the first step.
Anyways, here I go:
This is the output I need:
BOTTOM 3 Items ——————————–QTY
xyz- ———————————————— 3
xyz1————————————————-3
xyz2————————————————-3
xyz3————————————————-6
xyz4————————————————-6
xyz5————————————————-10
xyz6————————————————-10
xyz7————————————————-10
xyz8————————————————-10
xyz9————————————————-10
I have 3 tables, Order_Master , Order_Detail and Stock
Order_Master – has fields client_id, order_id
Order_Detail – has order_master_id (linked to order_id of Order_Master), Qty
NOTE : Qty shown in desired output is sum(Order_Detail.Qty) for multiple orders.
I can get all the data using this query below, but I am unable to derive the Bottom 3 Qtys (Note: its not the bottom 3 rows that is needed, but all the Qtys that are the least 3).
Select Sum(Order_Detail.Qty) As Qty, Stock.Item_Description As
All_Item_Description
From Order_Master Inner Join
(Order_Detail Inner Join
Stock On Order_Detail.SKU = Stock.SKU) On Order_Master.Order_ID =
Order_Detail.Order_Master_ID
Where Order_Master.Client_ID =999
Group By Stock.Item_Description
Order By Qty ASC
This gives the output as:
xyz- ———————————————— 3
xyz1————————————————-3
xyz2————————————————-3
xyz3————————————————-6
xyz4————————————————-6
xyz5————————————————-10
xyz6————————————————-10
xyz7————————————————-10
xyz8————————————————-10
xyz9————————————————-10
xyz10————————————————70
xyz11————————————————70
xyz11————————————————70
xyz11————————————————70
xyz11————————————————70
xyz11————————————————70
xyz11————————————————70
xyz12————————————————100
xyz13————————————————100
xyz14————————————————100
xyz15————————————————278
xyz16————————————————300
xyz17————————————————300
xyz18————————————————300
xyz19————————————————300
Thanks for all your help.
Hello
I am not SQL Expert either,
Add one line to your query and you should get desired output.
Select Sum(Order_Detail.Qty) As Qty, Stock.Item_Description As
All_Item_Description
From Order_Master Inner Join
(Order_Detail Inner Join
Stock On Order_Detail.SKU = Stock.SKU) On Order_Master.Order_ID =
Order_Detail.Order_Master_ID
Where Order_Master.Client_ID =999 and Order_Details.Qty in (select distinct top 3 Qty from Order_Detail order by Qty ASC)
Group By Stock.Item_Description
Order By Qty ASC
Hope this helps.
Imran.
Hi,
I’ve got database with me which was created in MSDE initially and by mistake MS SQL 2005 Express edition was loaded on same machine. now when i am trying to attach same database with MSDE. It is not getting attached. Please help how to attach the database with MSDE again. Is there any way of downgrading database for MSDE. I am not expert on scripting.
Its little critical please help out.
@Narender
MSDE is free edition of SQL Server 7.0 and SQL Server 2000. And SQL Server Express is free edition if SQL Server 2005.
By default I dont think there is anyway to restore 2005 database to SQL Server 2000.
But this article gives a very good details about how to do this… follow these instructions they are very easy and simple, and everything is through wizard, so you just have to follow the wizard.
Hopefully your issue will be solved.
Here is the link : http://www.cmsdev.com/node/20
Starting few lines just ignore it….
Hope this helps.
Hi
i have copied a db from one server to another long with all the log on and jobs. but alot of the jobs are getting the error mesg below – any suggestion welcomed.
“Executed as user: SIVLTD\SQLSERVICE. SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.”
thanks, amanda
@111
7th line in my code, by mistake I wrote Order_details, instead of Order_Detail table name. so query might need to be changes. If you figured out that fine, if not please change 7th line of script.
Apologies if I gave you a hard time in figuring out….
Thanks,
Imran.
@Amanda
To enabble stored procs, triggers and Application to reference custom OLE Automation objects,
you have to Enable OLE Automation if Application or T-SQL uses OLE Automation.
TO do so, follow this step,
Select Start,–> All programs –> Microsoft SQL Server 2005 –> configuration tools –>
Surface Area Configuration for features
The click on the instance you wnat to enable OLe Automation.
Select OLE Automation, on the right pane, Check enable OLE Automation and Click OK.
To this, you must be a memeber of sysadmin server role
I need to create a diagnostic package using cursors using this logic
1.Loop through and load clean data from source table destination table i.e while the @@fetch_status =0
2.if the @@Fetch_status 0,add the current row to Error table
3.Restart the cursor at the next record to avoid reloading the same records
My brain is blowing out about this :( .I’ll probably add as the responses pour in
Thank you
You can use bulk insert with Errorfile option
Dont use cursor
Hi Dave,
Is there a way to get details like login/UID/User Name etc of who created a table (not owner). By the way, I am using SQL 2005 server. Thanks
Hello Imraan,
@111
Thanks for the solution. I tried the query. It does not return the top 3 qtys. It gives me some different result. I am not sure what change is needed – cannot think of any, that’s why I am here again. Please help!!!
Thnx
Tibya
Hi,
I am working with SQL server 2000 database,
I was assigned unrestricted file growth in data file, but some how it does not growth.
As a result it will stuck the database and giving me the time out expire error in my programs.
How should I fixed this error
Thanks
Hi pinal
i want to retrieve the last inserted value of an identity column how can i do that without using max/top clause plz help me out
@Arvinder
This could give you a start….
create table example1 ( id int identity, ename varchar(100))
go
insert into example1 (ename) values (‘value1′)
insert into example1 (ename) values (‘value2′)
insert into example1 (ename) values (‘value3′)
insert into example1 (ename) values (‘value4′)
insert into example1 (ename) values (‘value5′)
insert into example1 (ename) values (‘value6′)
go
select * from example1 where id = @@identity
go
drop table example1
go
Hope this helps,
Imran.
@Tibya
I also wrote a patch for @111 comment in @115 comment.
Please read @111 and make changes according discussed in @115 comment.
That should help, if not please let me know I can try something for you again.
Thanks,
Imran.
This is in SQL 2000. I need to select top (some) records from a view into a variable. I use:
declare @lastzip int
–@lastzip value obtained from another ‘select’ stmt
select @zip = top (@lastzip) zipcode + ‘,’ From vwSalesTeam v
where v.userid = @userid order by zipcode asc
I get an error
Msg 170, Level 15, State 1, Procedure usp_search_ver5, Line 386
Line 386: Incorrect syntax near ‘=’.
I was working in SQL 2005. I would have used the above code in SQL 2005. But now I’m using 2000 and I’m getting this error.
Could you tell me what I’m doing wrong here???
Any ideas/Suggestions are highly appreciated.
Thanks
Vijeya Shobana
@Vijeya,
Correct me if I am wrong.
The above code will not work because of the following things,
1. @zip is a variable and a variable can store only one value, but if you see passing more than one values into @zip, for example if your @lastzip value is 3, then you are trying to insert 3 values into a single variable.
2. I think zipcode is a int datatype and you are performing a concatination action which is only possible with varchar datatype.
I wrote something like this and I assumed some fake values, just to show you some output.
create table vwsalesTeam ( zipcode int, userid int )
go
insert into vwsalesTeam values ( 11111, 1234)
insert into vwsalesTeam values ( 22222, 3456)
insert into vwsalesTeam values ( 22223, 3456)
insert into vwsalesTeam values ( 22224, 3456)
insert into vwsalesTeam values ( 22225, 3456)
insert into vwsalesTeam values ( 33333, 2345)
go
create table #zip ( zipcode1 varchar(10))
go
declare @lastzip int
declare @userid int
set @lastzip = 3
set @userid = 3456
insert into #zip select top 3 convert( varchar(10), zipcode) + ‘,’ From vwsalesTeam v where v.userid = @userid order by zipcode asc
go
select * from #zip
drop table #zip
This works in SQL Server 2000, I did three changes, first I changed @zip to #zip ( i.e. create a temporary table) and second I changed zipcode to varchar datatype. Third I dont know what is wrong with “select @zip = top”, it never worked for me, so I changed it to, insert into #zip select top…….
I know this is not the answer you are looking for, may be this would give you an idea where to start …..
Hope this helps,
Imran.
@Arvinder
@121
USE Database;
GO
DBCC CHECKIDENT (‘MyTable’);
GO
Hope this helps
Help me, please.
I have any a problem. I don’t know, how do I do convert (downgrade) from SQL Server 2005 to SQL Server 2000.
I tried with detach in SQL Server 2005 and sequentially attach in SQL Server 2000 data file (*.mdf).
Then I tried with full backup in SQL Server 2005 and sequentially restore in SQL Server 2000.
And NOTHING :(
I know.
May I create new database, then turn off relationships, constraints and … , then copy dates between old and new database, then turn on relationships, constraints and …, but
I seek anything intelligent working out.
Help me, PLEASE.
PS: I’m sorry, in my English. :((((
Pinal,
Is there a function in Sql server which is similar to the BITAND function of Oracle.
Thanks
-Noms
Tell us what BITAND does in ORACLE so that we will be able to give solution in SQL Server
Hi,
LOG shipping between a 2000 and 2005 console but the databses are in compactible mode 8
I would like to do a logshipping from a 2000 enterprise console on a server to a 2005 management studio console on another server . The databse that I want to do log shipping on is a 2000 sql databse on both the server. I have not upgraded the databse to 2005 .
My problem is I can’t connect the 2005 destination server on to my 2000 management studio (naturally) so I tried to open my 2000 databse on the 2005 management studio in the other server but I can’t find any log shipping option .
Would you be able to help me out .
Thank you so much ,
Anitha
Looking for advice on purchasing a new server for SQL databases and I’m at a complete loss as to what I’m looking for.
I have a Dell 1850, which has worked well for years and works pretty good still. I have 16 gigs of ram, and pleanty of hard drive space.
Issue is that I have a program that runs up against my SQL database and when I try to run the program multiple times at once, I get i/o errors. I can run 3,000 queries per minute, (program does that) but I need to get to 60,000 queries per minute. They are very small queries, primarily append and updates, but none the less, they run constantly.
Knowing that I need to get the speed on the queries, without getting i/o errors, I’m at a loss for what features I should be looking for in a new server.
Obviously raid 10 capabilities, but not sure if I should be looking for a disk array, or a completely new quad core server with more ram.
Any input would be greatly appreciated.
Hi Pinal,
in a replicated database, want to insert values.In a table, Identity specification is set to yes for a field n so can’t able to insert values.
Tried this,
First identity is set to on,inserted values again set to off..It works great for individual database but not for replicated database..
how to overcome this problem..
Thanks,
Jeevika
Hello sir,
I am a beginner for SQL server , and i want to learn the SQL commands, stored procedures and all such related stuffs, so can you guide me on how should i start.
Thanks,
Abhishek
Hello sir,
I need help on SQL 2005, is it possible to use VBscript or SQL script to create new logins, is so, how should i do it?
@ Eric,
In SQL Server 2000 and 2005 you can create logins using scripts,
This is a sample script.
create login login1 with password = ‘H512kIHn’
by default check_policy option will be on and Check_expiration policy will be off, what this means is,
all windows server 2003 and later version has security policy which can be applied to SQL Server logins,
Check_policy will ensure that your passwords,
1. meet password complexity,( any 3 of below 4.)
a) atleast 8 characters, ( what ever you have set at OS level 8 or 6)
b) atleast one character
c) atleast one digit (1-9)
d) atleast one special character
Any 3 out of 4.
2. Account lock out policy : what ever limit you have set at OS level, after that many failure attempts windows account will locked, this logic is also applied to SQL Server logins,
3. Lock out expiration policy. same as OS level.
But for Check_expiration which is by default off, you need to mention explicitly if you want turn it ON, this policy keeps record of date when login was created and when user has to change password next time… , same concept as in OS ( Server 2003)
script1: (SQL Server Logins)
create login example1 with password = ‘H512kIHn’
Check_policy ON, Check_exipration OFF ( by default)
script2:(SQL Server Logins)
create login example2 with password = ‘H512kIHn’, check_policy = on, check_expiration = on
Check_policy ON, Check_expiration ON , you cannot keep Check_expiration ON, untill you have Check_policy ON.
script3: ( Windows Logins in SQL Server)
create login [domain\example] from windows
This is when you want to create a windows login. you have to use square brackets in order to create a windows logins, here you dont provide any information for check_policy or check_expiration because all this will be taken care by active directory and OS.
We also have something called, must change option, ( for SQL Server Logins) when users connect to SQL Serverfirst time, they are prompted to change their password ( on first login) , this is similar to user must change their password option in windows.
Check_Policy and check_expiration both should be ON, in order to keep MUST_Change option, this can easily be done by using GUI.
Check_policy and Check_password works with Windows Server 2003 and later versions.
Books online is best source.
Hope this helps,
Imran.
Hi pinal ,
Am having some doubts ,
Can we call stored procedures within a function?
Plz give me some help notes for stored procedure wiithin stored procedure…
thanks
hello,
could you please tell me which is the better option to use
‘index rebuild’ or ‘index defrag’ at production environment
dear pinal,
i am working on a finacial project in which we r using BI (SSAS) enviroment.we don’t have any knowledge how to create cubes and mining structure
will u help me to how we can create a cube in ssas
or will u provide some refrence to help us …
dileep singhal
Hi Pinal
I believe you might have answered a similar question in the past, if so could you please forward the same, or if have not answered please reply back.
I have two SQL SERVER databases ,one in 2000 and another in 2005 on two different serveres
And the need for me is to contantly access data across the two versions and also preform DML operations between the two databases.
What is the best way for me access the databases across different versions. In the past I have been using linked servers, I would like to know if I could a similar approach, and how do we create a linked server across different versions?
Thanks
Bala
I am curious if you are interested in working on a CFMX7 / SQL05 project as a development consultant.
Please contact me and let me know if you have interest.
Sir
Please solve my problem. I want to create storeprocedure in which i want to use another database. How is it possible.
Please give me reply.
Thanks in advance.
Refer other db’s object by
DBname.dbo.object_name
Dear Pinal, thank you for giving this chance, first of all.
My problem which I should resolve it urgently, is about decimal symbol of tables in my databases. Now, the decimal part of the numbers is separated by “.” But I need to change it as “,” Because it is not compatible for my other programs which I use sql tables by linking..
If you would give me some clue about how to make this change, I will appreciate..
Thank you in advance..
Hi Dave,
I have currency fields stored as text data in an access database. where the last position of the field is in zoned decimal format. (f.i. J = -1, K = -2 ) .
How do I extract numeric values with two decimal places from the access database ?
Thanks for your advice.
hi pinal,
i have 2 simple questions…
1. can a foregin key in any table can be a foregin key in any other table…i think the question is clear…i will explain in other words…..Primary key of table A, which is foregin key in table B. can it be a foregin key of table C ? here there is no relation between table A and table C.
2. In junction table, the primary key is made up of composite key, can any one the composite key be a foreign key in any other table….
thanks for your website…..its really very helpful…….
Hi Pinal,
I have a request to move the mdf/ldf from one drive to another using xcopy. Can you help me in using this in SQL Server script or tell me any other command to move the files? This is kinda urgent. Any support will be much appreciated!
Thanks,
Sagar
Hi,
I want a help in sql query. i am developing a jobsite portal so i want to put search for jobs in jobseeker panel. my problem is that..
i made city master for job posting.
when a recruiter post a job then i store job cities in another table with cityid and jobid.
i want that when an jobseeker search like jobs in jaipur
then all the jobs which are available in city jaipur comes in search results.
so plz help me in written query..
its urgent..
Hey Pinal,
Just to let you know that there is a mispelled word in your resume(Webpage resume not the pdf version)
Under Software Outsourcing Manager (3rd last point)
interption should be interruption.
cheers!
Hi Pinal,
I just started working as a Junior DBA for 2 years. I want to know what kind of inteview questions they will ask in a interview because I’m changing company. Are the questions mid level or senior level dba questions?
Thanks
Ben
Hi Pinal,
I have been assigned task to find what service accounts are being used on all SQL servers . This would be for every SQL Server service that is running (SQL Server, Agent, Notification Services, etc.).
It would be a lot of work and I’m not sure there is anything that could be done to automate it so that we can pull that information without manually checking each server instance. Any thoughts? Please assist if there is any automated script to fetch account info. for all SQL servers.
With best regards,
Vivek
Hi Pinal Dave,
Thanks for all your great input. I am new to SQL and data importing and was wondering if you had any feedback for me on this issue.
I need to import data from .csv files into a SQL database. (SQL 2000)
A script was provided to me that set up all the tables and set the column names in the SQL DB. The .csv files that I need to import have the identical names as the table names in SQL and the column names in the .csv relate to the column names in the SQL tables. That said, I was hoping to use the steps outlined in
http://www.databasejournal.com/features/mssql/article.php/10894_3325731_1
However in my project each .csv has different column names to each other, but they do relate directly to the column names in the SQL DB (as elaborated above) – How would I go about importing this data, or is there a better method for me to implement to achieve the result?
Thanks in advance,
Jonathan
hi Pinal ,
i have one query regarding index, for example i have one table on which have 1 cluster index and 1 non-cluster index , cluster index is based on the id column and non cluster index based on id column and location code column , when i will fire select query and in the where clause of this query both id and location code column include , so in that case which type of index will be used to return the result set .
I have created a function in sql which accepts a string removes all the special characters and returns string without special characters….i have used while loop in it….Normally when i run the code it gives me right results but when i call the function it returns the first character of the string passed…Issue here is why the while loop doesn’t work when i call the function. I have used the word continue also in it….
Post the code used in the function
hi! Pinal,
I am looking for a sql backup solution.
I have a 30gb db in 2005 sql ent version. Currently it was configured db mirroring and log shipping.
I need to backup the db regularily as I think if the db having problem..like coruption. This info will replicated to the mirror server as well as to the log shipping server. This is the reason why I need to back.
But when I try to setup a backup job on principal server from maintenance plan and I cannot select the actual db. I think this is due to the db was in mirroring configuration. Backup plan not available in mirror server as well…
your reply is greatly appreciate
thanks….Metthew
Some examples re item 149
The first csv file which is named abntemp.csv has the following:
FNMASTER_CODE,ABN_NUMBER
102276,22002072104,
102863,89008921926,
etc.
The table in SQL that needs to be populated with the data from abntemp.csv is named dbo.abntemp and has the following columns defined:
fnmaster_code abn_number
The second csv file which is named algra.csv has the following:
ALLIG_CODE,COAUTH_CODE,COUPDATE_DATE,COUPDATE_TIME,COUPDATE_USER,COUPDATE_TERM,ALGRA_RSN_ENDED,COEND_DATE_BY,COEND_DATE_TRMNL
ESS,AD,28/04/2000,12:01:13,gems_live,WCCNTS5,,,,
ESS,PRA,25/01/2000,14:07:09,gems_live,WCCNTS5,,,,
CLT,PRA,19/08/1999,13:49:54,gems_live,WCCNTS5,,,,
FPR,PRA,19/08/1999,13:50:16,gems_live,WCCNTS5,,,,
etc.
the table in SQL that needs to be populated with the data from algra.csv is named dbo.algra and has the following columns defined:
allig_code coauth_code coupdate_date coupdate_time coupdate_user coupdate_term algra_rsn_ended coend_date_by coend_date_trmnl
thanks, Jonathan
Hi Pinal,
Thanks for SQL Server 2008 Intv Q & A.
I need a help in SQL Server 2005.
I am trying to create new maintenance plan. But when I right clicking on Maintenance Plan folder (SQL Server Management Studio -> Management ->Maintenance Plan), no option of creating a new maintenance plan from scratch or creating one via the wizard.
Actually I am very novice in SQL Server 2005. Please help me.
Thanks & Regards
Koushik
Koushik,
If I am not wrong, you are trying to create maintenance plan in SQL Server 2000 using SQL Server 2005 client tools, which is not possible.
If you are using SQL Server 2005 client tools and you are connected to SQL Server 2000 then there is no way that I know to create new maintenance plans.
The only way to create new maintenance plan in SQL Server 2000 is using SQL Server 2000 client tools ( Enterprise manager).
Please correct me if I am wrong.
Thanks,
Imran.
hi pinal,
i am raghuram, working as dba in pune. i am reading all of your interview questions and answers. it is helping me in learning sql in detail. like that do u have any kind question bank for MCDBA examination. i want to write that exam.can you give me any tips for that exam preparation.
expecting reply soon
with regards
raghuram
Please forward me if you have any partition maintenece script you use in your environment for 2005
I just meant to say that we just rebooted the db server last week.
According to Imran Mohammed, Imran you are wrong, because when execute
“Select @@Version” in my query window , result is
” Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) ”
And other details are
Microsoft SQL Server Management Studio Express-> 9.00.3042.00
Microsoft Data Access Components (MDAC)-> 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML->
2.6 3.0 5.0 6.0
Microsoft Internet Explorer-> 6.0.2900.2180
Microsoft .NET Framework-> 2.0.50727.42
Operating System->
5.1.2600
When I am right click on Maintenance Plan,only two menu are appear.one is “Report” and other is “Refresh”.
Hi Koushik,
I have not tried it myself but may be your management studio is of express and your SQL is developer (Acts like enterprise) that could be the reason. Express edition management studio can have limitation.
Regards,
Pinal
hi pinal
how r u . I hope ur fine with busy work with enjoyment .
i ve query that ”
i want to insert a complete row between the rows . i mean to say that
Empid Empname
———- —————–
1. 111 pinal
2. 112 imran
3. 113 Raju
4. 115 Ravi
now i want to insert a row between(113 and 115) the row . output sholud be like this
Empid Empname
———- —————–
1. 111 pinal
2. 112 imran
3. 113 Raju
4. 114 David
5. 115 Ravi
can it be possible.
Plz give me response .
————” Drive into d ocean of SQL SERVER “————-
Dont worry abouting how to save ordered data
When you use SELECT statement use ORDER BY clause
Also, if you have Clustered Index created on EmpID, the ordering is taken care
You should never rely on the default ordering although you use clustered index
Not to worry, have a solution:
use GEMS
GO
DECLARE tnames_cursor CURSOR
FOR
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS -1)
BEGIN
IF (@@FETCH_STATUS -2)
BEGIN
PRINT @tablename
EXEC(‘BULK INSERT ‘ + @tablename +
‘ FROM ”D:\GEMS_IMPORT_FILES\’ + @tablename + ‘.csv”’ +
‘ WITH (fieldterminator = ”,”, rowterminator = ”\n”, FIRSTROW = 2)’)
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
Hi, Pinnale,
I would like to learn sql server 2005 online with help or support of you
my table
————————————————————————
ID | tpic1 | tpic2 | ……. | tpic20 | s_pic |
————————————————————————
1 | sun.jpg | gun.jpg | ……. | win.jpg | 2 |
————————————————————————
2 | rat.jpg | fun.jpg | ……. | run.jpg | 1 |
————————————————————————
3 | see.jpg | row.jpg | ……. | hill.jpg | 3 |
————————————————————————
4 | ice.jpg | tea.jpg | ……. | bill.jpg | 20 |
————————————————————————
. | ……….. | ………….. | ……..| …………………|…………|
. | ……….. | ………….. | ……..| …………………|…………|
. | ……….. | ………….. | ……..| …………………|…………|
————————————————————————
n |rose.jpg | leaf.jpg | ……. | root.jpg | 18 |
————————————————————————
i need query for like this selection. pls help me
————————————————————————
ID | s_pic | main_pic
————————————————————————
1 | 2 | gun.jpg — ( tpic2)
————————————————————————
2 | 1 | rat.jpg–( tpic1)
————————————————————————
3 | 3 | sss.jpg–(tpic3)
————————————————————————
…. | ………. | ……………………
…. | ………. | ……………………
…. | ………. | ……………………
etc
what is the query for above one..
Thanking you.
D.vediyappan.
Hi Pinal,
I have written following stored procedure:
**************************************************
CREATE PROCEDURE sp_OpenItemBrkDwn
AS
BEGIN
SET NOCOUNT ON;
if exists (select * from dbo.sysobjects where id =
object_id(N’[dbo].data1′) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[data1]
if exists (select * from dbo.sysobjects where id =
object_id(N’[dbo].data’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[data]
declare @sql1 varchar(max)
declare @sql2 varchar(max)
declare @sql3 varchar(max)
CREATE TABLE data (CLT_SITE nvarchar(50), CLT_CD nvarchar(50), TOTAL INT,
IMG_STAT nvarchar(50))
select @sql1 = ‘insert into data SELECT CLT_SITE,CLT_CD,COUNT(IMG_ST) AS TOTAL, ”P”
as IMG_STAT ‘ + ‘FROM IMG_INFO WHERE IMG_DT < GETDATE() AND IMG_ST = ”P”’ +
‘ GROUP BY CLT_SITE, CLT_CD’
select @sql2 = ‘insert into data SELECT CLT_SITE,CLT_CD,COUNT(IMG_ST),”R” AS IMG_STAT ‘ +
‘FROM IMG_INFO WHERE IMG_DT < GETDATE() AND IMG_ST = ”R”’ +
‘ GROUP BY CLT_SITE, CLT_CD’
select @sql3 = ‘insert into data SELECT CLT_SITE,CLT_CD,COUNT(IMG_ST),”Y” AS IMG_STAT ‘ +
‘FROM IMG_INFO WHERE IMG_DT < GETDATE() AND QA = ”Y”’ +
‘ GROUP BY CLT_SITE, CLT_CD’
exec (@sql1)
exec (@sql2)
exec (@sql3)
;with cte
as
(
select *, row_number() over(partition by CLT_CD order by CLT_SITE,CLT_CD) rid from data
)
select
isnull([CLT_SITE],”) as CLT_SITE
,isnull([CLT_CD],”) as CLT_CD
,isnull([P],”) as P
,isnull([R],”) as R
,isnull([Y],”) as Y
into data1
from
(select CLT_SITE, CLT_CD,IMG_STAT, rid, TOTAL from cte) as data
pivot
(
max(TOTAL)
for IMG_STAT in
([P],[R],[Y])
)as pvt
select clt_site as ‘CLIENT SITE’,clt_cd AS ‘CLIENT CODE’,max(p) AS ‘PENDING’,
max(r) AS ‘REVIEW’,max(y) AS ‘QA’, max(p) + max(r) + max(y) as ‘TotalWReview’,
max(p) + max(y) as ‘TotalWOReview’
from data1
group by clt_site,clt_cd
end
***********************************************
When I execute it independantly from SQL Server 2005 it is giving me correct output in about 2 minutes but when I execute it from a VB.net 2005 form I am getting following error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
I have given connection timeout = 0 in the connection string and also set sqlcommands command Timeout to 0.
Please provide me a solution to the above problem.
Regards,
Pranjal
Hi Pinal
Thanx alot for ur kind response .
i’ll getback to u again.
( SHAIK ).
Hi Pinal
I need a clarification on indexing. Recently i got this question from my interview. I’m having two tables.
One is #table and another one is @table.
In both, which table can i set the index?
If you want to set index by altering the table, it should be #table
I have to design a script which would go thru the DB and extract a flat file of each pertinent table’s data(1 file/table).I have used DTS to export data from databases.I have never written a script for extraction.Could you please guide me on this and help me in getting started on how to write a script with a small example.Could you also please help me on how to run the script against the database so that it could generate a data file
Thanks
Shveta
Read about bcp in SQL Server help file
Hi Pinal
I have created strored procedure in SQL Server 2005.It would execute other stored procedure for it….
It takes 4min to execute in SQL Express Management Studio.Is there any way in Managemet studio so that I can do step by step debugging….or reson why its taking so long …..
Otherwise when i execute it from my frontend it would give me timeout session error…
Thanks
Regards
Montu
Pinal,
I have two questions for you.
I have one view Table having 1000 + records.
1. Can you tell which is the proficient way which consumes less time to fetch all the records.
If the table has primary key you can dummily add a condition like
where pk_col>”
I am continuing my 2nd question.
In the same view table having 4 columns. I want to display the records based on two column restriction such that,
Right know this view table is ordered by “Name” Column.
Here is the sample example of my query output.
id Name tickername
5 Diya 2.rt
4 Kiran
3 Manu er.t
2 Rahul
1 sachin p1.st
I want to display the records from the following priorities.
priority 1: records should be displyed : order by NAME column [asc or desc]
Priority 2: If the column “TICKER NAME” is blank that record should come at the bottom.
Like
id Name tickername
5 Diya 2.rt
3 Manu er.t
1 sachin p1.st
4 Kiran
2 Rahul
Can you help me out how can write the query to get this output?
I appreciate ur reply.
Regards
Sachin K
Select * from table
order by case when tickername=” then 1 else 0 end,name
Amazing stuff. Thanks Madhi
Hi Sir,
I would like to ask your help regarding SQL queries:
My current code:
declare @W nvarchar(50)
declare @S nvarchar(50)
Set @W =’Week 27′
Set @S =’SESTW056′
– Weekly Reports Summary per day
Select ServerName,DateName(weekday,[datetime]) as ‘Day’, count(*)as ‘No. of Unschedule Restart’ from LogServerrestart
where weekcovered = @W and outageinterruption =’IISRESTART’ and servername =@S and [action]=’1′
Group By ServerName,DateName(weekday,[datetime]),DateName(dayofyear,[datetime])
order by ServerName,DateName(dayofyear,[datetime])
—————-
Select DateName(weekday,EventStartTime) as ‘Day’, count(*)as ‘Nagios Logs’ from Nagioslogs
where weekcovered = @W and eventtype = ‘SERVICE CRITICAL’ and servername =@S
Group By DateName(weekday,EventStartTime),DateName(dayofyear,EventStartTime)
order by DateName(dayofyear,EventStartTime)
—-
My problem is how to combine this queries.. thanks in advance
Hi Pinal
Thanx alot for Maintenance Plan related issue,ur response.
Regards,
Koushik
Hi Pinal,
I am wondering if u can post some pre-defined stored procedures. I searched alot but didn’t find all the important one’s.
Thanks,
Rajesh.
What did you mean by pre-defined sps?
Dear sir,
i have a table named table it has two fields empno is a primary key it is automatically generated starting from 1, 2,3 etc.and also a empname fields. if there is10 records with empno starting from 1 to 10 there. i have deleted the 5 th record. now i want to insert exactly at the fifth position .what i have to do this is interview question asked me pleas tell me the answer
second question if there is error in a trigger how u can correct it , i said alter trigger but interviewer it is not the correct answer
please send me the answers
yours faithfull
ragesh
SET IDENTITY_INSERT ON
insert into table(empno , empname )
select 5, ‘fifth_emp_name’
SET IDENTITY_INSERT OFF
Please note that, empno should be IDENTITY column
Hi Pinal,
Following is my scenario,
Suppose Table 1 and Table 2 has same column e.g. Column1
Following is the query,
1. Select column1,column2 From Table1
2. Select column1 From Table2
I want to find common records from these tables, but i don’t want to use Join clause bcoz for that i need to specify the column name for Join condition,
Will you help me to get common records without using Join condition.
I am using SQL Server 2005
Thanx in advance
Sachin.
Unlimited freedom, unlimited storage. Get it now
Select colum1 from table1
intersect
Select colum2 from table1
Hi Pinal
I have a question regarding finding data difference between two identical tables.
I have two identical tables. One table(Table1) contains redundant data , another one contains exact data(Table 2). I need to select only rows from Table1 which is not present in Table 2 and load the selected data in Table 2. How can this be acheived in sqlserver 2005. I want to write a Stored Procedure for this.
Thanks in Advance.
Debarati
insert into table2(cols)
select cols from table1
except
select cols from table2
Hi Dave sir,
With due respect I want to state that your answer to each questions are satisfactory.Sir i need an ebook by u so that i can become as smart as u
@Sachin,
If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records.
select column1 from table1
intersect
select column1 from table2
if you want in the output both column1 and column2 from table1 which has common columns1 in both tables.
select column1, column2 from table1 where column1 in
(
select column1 from table1
intersect
select column1 from table2
)
To do this, make sure your column1 is unique and do not have duplicate records.
Hello Mr. Pinal,
I have a small query in Sql Server 2005 Express edition that is, i have a database back up named gharabhada.bak which was created in a server named ESELSRV and now am trying to restore in my system named HOME but i was unable to as it is giving error as i can restore in the server named ESELSRV only. So how can i create a SERVER WITH ESLESRV name as most of my .Net projects i have created in that server only. So, culd you help in creating a server. If possible please mail me the solution.
Hi,
Iam using SQL Server 2005
SELECT
Col1,
col2,
ROW_NUMBER OVER(PARTION BY Col3 ORDER BY Col2) AS RowNumber
GROUP BY Col1, Col2
ORDER BY RowNumber
Iam getting the exception
Column “RowNumber” is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Help me to solve this.
ROW_NUMBER should be ROW_NUMBER()
What do you want to do?
Remove group by clause
Hello Mr. Pinal,
I was hoping you could answer a question for me. In SQL2005, can you lock only certain records within a sql server table?
Thank You for your time,
~Jessica
Hello Pinal,
I saw this entry in your older thread and we have the same issue at a client site. Can you shed some light on it and how to avoid or rectify it if possible.
Thanks
Steve
————————–
We are getting errors in our production database when we restarting the sql services
Could not find database ID 2, name ‘tempdb’. The database may be offline. Wait a few minutes and try again.
can you please tell me why this occuring
——————–
Dave,
How to get File Attributes, like File Size, of a file directory? I need to work this from TSQL for a report (SSRS). I know there is an undocumented Stored Proc “xp_getfiledetails”; but that is not what I need. I need to get the file size of the whole directory!
Try
exec xp_cmdshell ‘Dir C:\temp’
Hello
I am facing one problem while bulk inserting the data.
my text file looks like this.
START-OF-FILE
DATEFORMAT=20081003
DATARECORDS=93
FIELDSEPARATOR=TAB
id|name
START-OF-DATA
1 AGB
2 ANB
3 ARHUS
4 AITOV
…….
END-OF-DATA
DATARECORDS=93
END-OF-FILE
FIELDTERMINATOR = ”\t”,ROWTERMINATOR = ”\n”, KEEPNULLS, FIRSTROW = 2 ,
I am using bulk insert method to upload the data by specifying . It is working fine but my first record is always getting skipped.Its loading data from 2nd row (2 ANB). Can you pls suggest why this is happening.
Thanks Debarati
Remove this
FIRSTROW = 2
or
change it to
FIRSTROW = 1
Dave,
How do you feel about installing SQL Server 2005 and 2008 on Virtual Servers; both Microsoft’s and third parties.
Thanks,
Hi Pinal,
I am Nagashree. I want to do certifiaction on SQL server. I need complete info regarding this..
please guide me… (I mean how many exams, can I learn without going to learning center?)..etc etc..
I am waiting for your reply…
Sorry about this.
I fear my spelling errors might make it difficult to understand the problem.
Corrected version below.
—————————————————————————-
Dear Pinal,
I am trying to assess the best method for copying a client record and all its related dependencies from a database on one server to an identical database on another server using SQL 2005.
The database consists of about 12 tables.
A client record in the table at the top of the hierarchy has numerous related records throughout the remaining tables.
I wanted to use a stored procedure to be able to copy a client and all of its related data from all tables on the one server to an identical set of tables on another server.
All constraints and referential integrity must be maintained.
I am new to SQL and simply wanted pointing in the right direction.
Thanks,
Dawn
@Dawn,
What I understood from the information you provided is,
you have a database with 12 tables, which has client information and all the tables are interlinked with each other. Table also have some dependencies ( triggers, views, other tables).
And you also have one more sql server 2005 to which you want to copy this data with all dependencies,
The solution might with var with your requirements.
1. Database Mirroring : Copy database to destination server but you cannot access database on destination as it will be in recovery mode.
2. LogShipping : copy database to destination server, but you can only read data from destination database as it can be in recovery or read only mode.
With the above two process you can only send data to destination database, you cannot recieve data from destination to source.
But with replication ( Transactional replication with updatable subscription ) you can have data flow in both direction, but there are some restrictions.
1. Identity columns
2. Table with out primary keys.
3. No replication of stored procedures.
And few more. If you could tell us more, how you want your destination database to be. that would give us more idea…
Thanks,
Imran.
Imran,
Thanks for your response.
The database does have about twelve tables with information about individual clients spread over the various tables and linked via primary-foreign key relationships.
As an example the dbo.Client table contains two columns, ClientID (PK) and ClientName. This is more or less the top-level table.
The dbo.ClientSchemes Table contains four columns including ClientID (FK) and SchemeID (PK).
The dbo.ClientSchemeExercises Table contains nine columns including ClientID (FK), SchemeID (FK) and ExerciseID (PK).
What I want to do is this;
Given a particular ClientID I want to be able to obtain all records in all tables (maintaining referential integrity) that pertain to the particular ClientID and copy them into the table with the same name on a destination server which contains an identical database.
In essence a copy of all the data for the specific ClientID will be created on the destination server.
I had tried to do some research on replication. However it seemed to be more relevant to the case of copying an entire database from one location to another as opposed to copying a few related records into individual tables on the destination database.
Thanks,
Dawn
I recently began working with SQL Server and have a little experience and training with 2005. Now, I have a few 2000 databases to maintain, until I get rid of them. I have one quick question:
In 2005 I am able to clear history as part of a maintenance plan. How do I clear the history in 2000?
Thanks,
Donna
Hi Pinal,
I want to set timeout.
i am having a SQL Server 2005 stored proc in which i am using OpenQuery to insert data into sql 2005 table from sybase table. I am using Linked Server.
I am calling this stored proc from SSIS using Execute SQL Task.
sometimes the process hangs.
I want to error out (timeout) the process if the SP (or openquery) takes more than a given time.
how to do?
Thanks a lot in advance.
Hi!
How can i get that, how many recordsets are returned by a query/stored procedure?
Thanks a lot in advance.
If you use front end application like VB6, you can get it by Rs.nextRecordSet where rs is recordset object
add the following statement immediately after a query. this will give you total records affected by the query.
print ‘Total records returned ‘ + convert(varchar(100),@@rowcount)
Regarding 191 above – question about history setting. I found it in the options. :)
Hi Pinal
first of all ……Thanks for creating very supporting website for SQL guys. somemany times I had gone through your website ………its really helpful ………..
could I get details about included option in index in SQL Server 2005 and CTE (say new features of SQL 2005) and would like to know more depth about DBCC UPDATE STATISTICS
could I get your office address……..bcoz i m also from Ahmedabad….
Hi Dave,
Thanks for your tips and tricks with regards to sql server. really enlighting.
I have a question though. Its about when restoring a back up database to a sql server 2005.
Do you know what are the scripts being executed after a database has been restored? I placed a database trigger on my database so that if anyone who alters the same sp without any changes it will throw an exception. the problem is that when i restore a database, somehow a script is being executed and an exception is being thrown. Do you know what that script is?
Thanks Dave, more power!
@ Dawn.
Apologies for late reply.
Thanks for describing the situation, When you decided to copying the database. I would suggest you try one of the following. Dont go with Replication, Its good when you are doing specific tables. but in your case you want all database to copy at destination, so try one of the following.
1. Logshipping: Easy to set up, less maintenance. Works very smooth, Data latency ( data gets updated every 15 minutes, depends how much time interval you specify when configuring Logshipping ).
Here is a pictorial step by step procedure, how to configure log Shippping
2. Database Mirroring: Little bit tough concept. Easy to configure , little bit tough to solve errors, difficult to manage.
Latency will be less than 3 seconds
So I would suggest you to go with Log Shipping. As it is hassle free.
But you did mention that you will insert this data into a table. well if you are doing Either logShipping or Database Mirroring, you CANNOT perform write operation on Destination database. What you can do, you can create one more database and then copy data from destination database into your main database.
Or you can also do this,
write a stored procedure on source server inserts data to destination server after joining s all the tables on which you have build primary keys and foriegn keys according to your business needs , place it in a job and schedule that job to run every 5 minutes , Before you create thsi stored procedure you have to create a linked server between source and destination servers.
Also in stored procedure you have to use four partname,
servername.databasename.ownername.objectname
There are many ways to perform this task. depends which one you feel is easy for you.
Thanks.
Imran
Hi Pinal,
I have been following you blog for quite some time now and appreciate our work towards SQL Server Community.
I am looking for answers for three inter related questions
1) what is the advantage of SQL Server 2005 over SQL Server 2000? ( in respect of migrating server from 2000 to 2005)
2) now as SQl Server 2008 is about to be released.. why one should move its database from sql server 2005 to sql server 2008. i mean advantages SQL server 2008 database will have.
3) SQL Server 2005 was considerable swift from sql server 2000. SO which migration will worth more a) 2000 to 2005 or
b) 2005 to 2008
thanks
Rahul Kumar
PS: mail ur reply to my email also.
Hi,Pinal i want to Backup my database with security and while Restoring in the Client place the database must restore only and i must deny the permissions for accessing the database…?
How is it possible pls reply as soon as poss…?
Hello Subu,
You question is not clear, I just assuming your question to be ” After you take a backup of any database, you want to restore a database but you dont want any one to access this database”
Yes This can be done, this is very easy. we call this mode of the database as single user only.
First you restore this database, follow the normal procedure and once you are done, then do the following.
IN SQL SERVER 2005:
1. Right click database- click properties- click options from left side of the dialog box – scroll down right side of the box to the last point, under state subcategory, you will see an option :
Restrict User: whose value will be set to MULTI_USER ( By default) , click on MULTI_USER and select SINGLE_USER from the drop down list, click OK. On Message Box, click YES.
Now in this state only one user can connect to this database, so when you are connected to this database no one else can connect to this database as this is in single user mode.
IN SQL SERVER 2000:
1. Right click database- click properties- click options from top tabs of the dialog box -Under Access session you will see Restrict User: check this option and then check Single – user , click ok.
By this method you can restrict users to connect to this database. but one user can connect to this database. No matter if other users have more privileges than you ( SA), SQL Server will not allow other users to use untill you come out of this database.
If you want your database to be in multi_user mode how it was previously, follow the same process as above,
IN SQL SERVER 2005.
1. This time instead of SINGLE_USER select MULTI_USER.
IN SQL SERVER 2000.
1. Uncheck Restrict Access.
This would be a temporary solution to your problem, if you dont want any user to access this, remove gues account ( In SQL Server 2000) or Disable Guest Account ( In SQL Server 2005) and drop all users.
By doing this only dbo;s ( Logins with SA privileges) will have access to this database.
If you dont wany any user to connect to this database, not even dbo’s not even SA’s, not even a single user mode, then the best solution is take that database offline.
to take database offline, do the following.
IN SQL SERVER 2000.
1. Right click database – All Task- Take offline.
IN SQL SERVER 2005.
1. Right Click database- Task- Take offline.
Note: Dont stop the process when the database is going offline, if you stop the process in the middle, you will loose your database for ever, be careful.
Hope this helps,
Imran.
Hi Pinal,
Please provide me some good links so that i can read the execution plans effectively which will help me a lot in solving performance related issues.
Balaji.J
Imran,
Thanks for all your help.
Working on a solution will post my solution when I arrive at it.
Dawn
Microsoft OLE DB Provider for ODBC Drivers error ‘80004005′
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
This keeps coming up when I try to host my ASP pages on Godaddy.com I used dreamweaver to create them and I uploaded the db to the host . Can you please help me
I am very much impressed with your blog. I have small requirement in query.
In one of my table schema like ApprovalID,RFA Number Approved Status
Data lilke
Apporval ID ,RFA Number, Status
124 100 4
125 100 3
126 100 2
Status is numeric field while displaying the content of Status in numeric values I want to display related status names like Approver for 4 , Rejected for 3 like this. I don’t have any table to represent the status names for those values.
how can i do this
select Apporval ID ,RFA Number,
case
when Status=4 then ‘Approver’
when Status=3 then ‘Rejected’
.
.
end
Hi Pinal
Actually would like to more about in sQL SERVER 2005, in index say cluser index Included column ….
more depth about included column…
did typo mistk
say cluster index n in tht included columns….in SQL 2005
Hello,
Can we get the table names in which records just inserted/updated/deleted?
For example, the employee information is added in two tables (PersonalInformationTable and EmployerHistoryTable).
Is there any way to find out the tables’ names in which data is inserted in last 10 minutes?
Bye,
[...] Sachin Asked: [...]
Dear sir,
I am in the middle of restoring… state of my database (which I initiated through one application).,….now it does not terminate…after waiting for ages! :(
I tried restarting server and all…but still is shows the same status….help me get out of this ASAP.
I am using SQL Express 2005.
thanks
Jeet
Hi pinal,
I need some help in Updating the tablw which has both primary key and Unique key constraint on diffrent coulmns.
In the source we have records with diffrent value for primary and same value for uniqu key when we try to update the target using these records using primary key it is violating unique key constraints.
how to update the target in this scenario.
hi pinal
i want to know how can i use like statements with dates ….i want to search stock between two dates and i have to use like nothing else and i am using sql server 2000 ..plz help me its urgent…….
i am very thankful to u…plz make me help…….
i am using (RecievingDate between like ‘%10/14/2008%’ and like ‘%11/14/2008%’). but it doesnt work…..again i m very thankful to u….
nitin
where
date_col>=’20081014′ and
date_col<'20081115'
Hi, can you add me to IM? I need to talk to you.
ibarghash [at] yahoo (d0t) com
thanks :)
@Ram kumar,
You can use case function to display the same. I am writing script for the example which you discussed.
CREATE TABLE EXAMPLE1 ( [APPORVAL ID] INT ,[RFA NUMBER] INT, STATUS NUMERIC )
GO
INSERT INTO EXAMPLE1 VALUES ( 124 ,100, 4)
INSERT INTO EXAMPLE1 VALUES ( 125 ,100, 3)
INSERT INTO EXAMPLE1 VALUES (126 ,100, 2)
GO
SELECT [APPORVAL ID],
[RFA NUMBER],
[STATUS],
CASE WHEN STATUS = 4 THEN ‘APPROVED’
WHEN STATUS = 3 THEN ‘REJECTED’
WHEN STATUS = 2 THEN ‘PENDING’
END AS STATUS_NAME
FROM EXAMPLE1
GO
DROP TABLE EXAMPLE1
Thanks,
Imran.
Hi Pinal,
Here is my question. I have one table A with 52 columns and based on the other table B, I need to populate table1 and table 2 with table A data.
Table A:
client_id
Table B:
client_id, client_type
where Table A.client_id = Table B.client_id then get the client_type
if client_type = 8 then populate Table 1 with Table A
else populate Table 2
I am new to the SQL server. Can you please reply me as soon as possible.
Thank you in advance.
Hi Dave
I want to export my query from sql directly to excel (Excel SpreadSheet). i can do it with .Net (Export content of a datagridview to excel OR export a generic list with reflection and microsoft.office.intropt.excel) but it doesn t have good performance with many records.
So Thanks Inadvance.
Dear sir,
I want to create a stored procedure which will insert one record into master table and multiple records into other table.Master tables insert statement i want to call once and other table insert statement i want to insert multiple times.
Thank you.
How do you want to pass parameter values to the procedure?
Hi Pinal,
I am trying to execute an SSIS package. This ssis package has only one step Execute Sql 2000 DTS task and it has a DTS in it. This DTS is working in SQL 2000 & 2005 both if executed as DTS. But when tried to execute the SSIS package I am getting the following error message:
Error: 0×0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0×80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
Task failed: Execute DTS 2000 Package Task
Warning: 0×80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package “Package.dtsx” finished: Failure.
The program ‘[4836] Package.dtsx: DTS’ has exited with code 0 (0×0).
Please help me.
Thanks & Regards
Sudha
1. whar are the configuration you do before and after installation of sql server service pack?
2. what is the reason behind log file size is increasing even after configured Log shipping?
3. what is .TUF file in sql server?
4. If querey execution time increases …what will u do?system id idle..no blocking issues…what is the reason behind it??
5.Diff b/w mirroring,Log shipping,Replication?
6. clustering in sql server .
7.How to monitor sql server performance without using GUI?
8.where do we monitor log shipping??how to trouble shoot Log shipping?
Dear Pinal,
Sometime I see following error while processing any webpage.
***************[Error]*********************
Microsoft OLE DB Provider for SQL Server error ’80004005′
Transaction (Process ID 102) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
***************[/Error]*********************
I would really appreciate if you would be able to let me know, what should I be doing in order to fix the error.
Thanks in advance.
Su
Dear sir
Why is a complete snapshot being generated when a new article is added for Transcation Replication (SQL 2005) .
i need query to prevent from this only run query it add it.
This is my first post hope for good and quick replay
Hi Pinal, I’ve an apllication that was installed with sql server 2005 trial version, and it expired, so i copy the data and log file i install the retail version and attach the data base using data file i backup the data base, and from another pc i restore the data base using that backup, but now, i’m not able to login to the the application, it says: “problem connecting to data base, please try logging in again”
what should i have to do??
Please help me :)
I’m trying to do 2 things in sql server that i know can be done in Oracle:
1. SQL Server 2008 won’t let me run a windowed function with a distinct clause. for example:
select count(distinct customer) over(partition by store)
from table
I’ve read that SQL Server 2008 was supposed to have this feature added – I see that i can do Cubes and Rollups with count(distinct), but i still can’t run the query above.
2. Is there a way to see how much time is left for a query to run as it’s running. In Oracle Enterprise Manager there was a window that counted down the time for the query to stop running and it was super accurate. Is there such a feature in SQL Server 2008?
Thanks,
Mike
1
select count(customer) over(partition by store)
from (select distinct customer from table) as t
2
I dont think there is a way to simulate this
Hi Pinal,
Is there any chance to create a script for each “UPDATE” process in each table?..
Example:
Case1:
if new data saved in table “Suppliers” we need to create a script for that new entry…
Case 2:
If any editing occured in table “Suppliers” we need to generate script for that edited entry..
Actually our purpose is to transfer the new/edited datas to our Branches which have the same DB structure.
So if we can get only the New/edited data scripts from each table in source server; we can run these scripts in target server.
thanx…
hi
iam new to this website.i want to learn sqlserver.2005 or 2008 which one is better.can any one give an idea how to start and where to start.plz help me
thanks in advance
Hai GUDMNG,
Hai i am anil.Pls send interviews questions,tips & how face to interviews.pls send my mail as soon as possible..
Thanks u
u’s
ANIL….
Hai Latha,
You should learn what is SQL SERVER…then you will know what are the diff b/w 2000,2005,2008….The functionality is same in every verion…but it is upgrading…thats it…ok
All the best for ur future,,
Im surprised that I cannot find an article about Change Data Capture (CDC) on your website. I think this is a great new function that has been added to SQL Server 2008. It would be great to get your insight into this and possibly learn more about all the different components behind CDC. Thanks!
-Theresa
Hi pinal
How are u , i hope so fine , This is shaik. i ve query that consider there 2 tables A and B.
A is having 10 records , b is having 5 records , now i want to append 10 records of table A to exhactly(means after all the records of table B) after the 5 records of table b , can it be possible , if it is plz show me . plz kindly forward a mail to my email id .
Thankx alot
SHAIK .
Hi Pinal,
I gained lot of knowledge on SQL Server by studying your articles.I am very thanks full to you.
Can send me some FAQS on SSIS and SSRS please please..
actually i have interview so can you do it please…………or any body can send me please…………….
Hi dave
I do have server problem when i make a dbcc checkdb allow data lose script. the log of the database was increasing currently 114gig it started with I think 5MB log, then it has
server error message “could not continue scan with no lock due to data movement”
server message 601.
Now its been 3days running, Im running out of space..your help would very much appreciated..thanks
Regards,
Vincent
please haw can i copi the data from sql database to CSV File i need realy your help thanks ……..
Hi Pinal Wish U the same.
I have one query. I want to find out which databases has been recently restored from which databases in SQL SERVER 2005.
Thanks,
rajesh
select top 10 destination_database_name from msdb..restorehistory order by 1 desc
My previous reply should be
select top 10 destination_database_name from msdb..restorehistory order by restore_history_id desc
I’m using the ‘Group By’ to group by month. The values I am selecting are ‘high’, ‘low’ and ‘close’. When I group, I want the highest ‘high’ (MAX), the lowest low (MIN), and the last close (date wise) in the month. Obviously I have the ‘high’ and ‘low’ figured out, MAX() and MIN() respectively, but I’m completely baffled by the ‘close’ value. Any help would be appreciated; I’ve spent too many hours on it already, with no solution coming to me.
Thanks,
Ken
What did you mean by close?
Can you post some sample data with expected result
Hi,
How do i check a record before updating.
i want to check if field has a value in it, if not than go ahead do the update. If the value already exist than stop the update.
I have one table CMR, and 5 columns with the following name.
Cmrid
Name
Owner
A.Date
B.Date
Update table col=somevalue
where col is null
Hi Pinal,
I’ve been dealing with this issue for a while!
I have backup files (.bak) with postfix of year-month-day-hours at the end of files.
e.i. backup_file_200810311230.bak
The backup scripts have been generated by MP, that’s why they all have date at the end.
I need to load my database in the target server with the latest dump file.
This is a server to server loading…I’ve already automated a script to bring the backup files to my target server.
In order to load my database in the target, I need to add some coding before the load SQL-script so it goes and grab the latest backup file.
I’m very new to sql server and need more direction with the solution.
I really appreciate your help!
Clue
select top 1 backup_file from
(
select 'backup_file_200810311230.bak' as backup_file union all
select 'backup_file_200811316230.bak' union all
select 'backup_file_200801321230.bak' union all
select 'backup_file_200810322430.bak'
) as t
order by substring(backup_file,patindex('%[0-9]%',backup_file),patindex('%[.]%',backup_file)-1) desc
Hello Pinal ,
This is shaik . I written the query twice or thrice , till now there is no response from ur side , i dont know whether ur out of city or some other busy works .
plz kindly could u refer my comment no 229 and answer me .
waiting for ur response ……..
Thanking u very much
Shaik .
Hi Pinal,
I would like to know the performance impact of using temp tables in stored procedures. I believe temp table cause SP recompilation.
I would like to why and when this causes the stored procudre to recompile? I would also like to know the improvement in SQL 2005.
Thank you.
@Imran Mohammed
@Pinal Dave
Imran,
I have managed to write a stored procedure to copy paraent and child records as detailed in my previous query.
It is an amateur attempt.
I wondered if you could have a look at the code and grant me an opinion more expert than my own.
The problem and solution can be looked at here;
http://teenylookatcode.blogspot.com/2008/11/stored-procedure-for-copying-parent-and_03.html.
Thanks,
Dawn
Hi Dear Pinal,
How are you?
I want to congratulate on your all success in life.
after success most people forget forever who helped them .
when i saw your site i found u exaclty opposite of it. wants to congrats you on this.Please be this way only
I m a new visitor to your site and very eagar to get a Job of sql dba currently working as a soft developer.
i have no source where i can get a good knowlege of sql and was so scared if i can accomplish my goad of being dba
ur site is as a personal trainer to me from today.
i just wish hard that i reach my goal with your help. i have given myself this november and december month for this.
hope that you wud help me get it
lots of wishes
swati.
do you have any idea how do i get last access date for a user on sql database ?
hello sir,
my problem is that i have to make matrimonial website in which i need to store all the saved searches of user in single column using sqlserver 2005 and asp.net .
plaese help me
Hi Dear Pinal,
How are you?
I have a big problem with full text search of SQL Server 2005,
We using CONTAINSTABLE with TOP_N_BY_RANK to get result with best performance.
It’s return right data with high rank, but i need it return last record that added to DB (newest first , not by relevance)
my DB has over 7,000,000 record, and we must using full text search and top n by rank,
could you please help me.
Thanks
Vahedi
Hi Pinal,
Can we refresh a database (like we do by right clicking database node in object explorer and clicking on refresh) thru SQL Query?
If yes, can you please tell me the query?
Thanks,
Ashish Agarwal
No. It is not possible
Hi,
I am here as a Database designer. I have to convert existing databse which is in MYSQl into SQL Server 2008.
I am not getting any relevat info regarding that.
The major challenge is converting data from mysql to ms sql server2008.
Will you please help me?
Thanks
Shailesh
Hi Pinal,
I have a doubt. My company database is in MYSQL.
Now we want to switch all data into SQL Server 2008.
I am not getting any answer that how will I convert this?
I can do this but that a long procedure (First create table exactly same as in MYSQL then by ssis or dts we can do this)
CAn you help me or tell me any other method?
Waiting for your reply.
Thanks
Shailesh
[...] 7, 2008 by pinaldave Yesterday I received following questions on blog. Ashish Agarwal asked following [...]
Hi Pinal,
I am new to databae programming.
I gained lot of knowledge on SQL Server by studying your articles.I am very thankfull to you.
I have a requirement to use SQL Jobs,i am not having any idea about it.
can u please tell how to work with SQL Jobs in sqlserver 2005.
Waiting for your reply.
Thanks
Pushpa.
Hi Pinal,
I have a Hp Proliant server with 8 processors(amd) and 120 GB ram with windows 2003 active passive 64 bit cluster .
I Installed sql 2005 on it.I noticed an error message..
The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
I read a few blogs and I would like to set the affinity mask and affinity I/O mask option using sp_configure..What would be the setting value for affinity mask and affinity I/O mask considering that i have 8 processors.Please suggest if there are any other options to eliminate the errors.
Thanks a lot
Purushotham
Reply for the question 248
Hi Pushpa,
Jobs are available on Object Explorer. You have to expand SQL Server Agent, under which you will find Jobs. Roght click there and you will find new JOB , click on the new job and you will find New Job window.
There are several pages available on left side.
First on general page type the name and description.
Second on step page you have to create a new step.
Just go through different pages , its very easy to schedule a job from the earlier version of SQL server.
Thanks
Shailesh
Hi Pinal,
Is there a way to query the SQL Server to get the date time format – I would like to know whether the date format is mdy,ymd or dmy?
Thanks,
Kshitij.
Run this and see the result
DBCC Useroptions
Hi Pinal Dave,
i have a table ‘emp’ with fields empno,empname, sal and bonus.. my requirement is to add the sal and bonus for individual record..
ex: empno empname salary(sal+bonus)
how can i get it? plz help me
with regards,
pavan
select empno, empname, sal+coalesce(bonus,0) as salary from your_table
Hope you are doing good. You have done a wonderful job and all the articles in your blog are purely knowledge based.
I want to send data in pdf files to database (SQL Server) directly. I am looking for code in C#.Net VS 2005.
Please can you help me in getting the code.I tried searching in internet but i couldn’t find any.
It will be helpful for me if you can also guide me in converting data in PDF files to Excel in C#.
What are the plugins that are needed to be used for pdf files.
Waiting for your reply.
Hi Pinal Dave,
I am facing some problem with data types defined for temporary table.
e.g.
(Original Table)
Table name: tblTask
Column name: TaskTitle Varchar(200)
I have created one temporary table, which is used in a stored procedure.
(Temporary Table)
Table name: TmpTable
Column name: TempTitle Varchar(200)
Now problem is.
I want to change Size of column “TaskTitle” (from Original table “tblTask”) to 400.
I forgot to change the size of column “TempTitle” (From Temporary table “TmpTable”), and while executing the stored procedure it throws an error.
So can we set the datatype of column “TaskTitle” (from Original table “tblTask”) to column “TempTitle” (From Temporary table “TmpTable”) at runtime?
If above thing is possible, so we will just have to change size of column in original table and do not have to worry about temporary table, temporary table will take size of column from original table.
Can you please suggest the solution or alternative for this problem?
Best Regards,
Jayraj Todkar
One option is
select * into #temp from table where 1=0
How do i round 1.0847 to 1.09 using round function in sql server 2005
wating for a reply
thanks in advance .
Hi Pinal,
This is a wonderful site for aspiring DBA’s.
You’ve got almost all topics covered.
I was looking for some article on SQL Data scrambling.
But i could not find anything.
Would be pleased if some article is published on Data Scrambling.
Regards,
Sarang.
Hello Pinal,
The site is really helpful to all the people , who works on SqlServer(as a DBA or programmer). Well I am working on MS.Net , and I am looking for a stored procedure or function that generates auto generate number. (Eg.) Suppose the first prefix always GRV Like GRV0001, the next number should GRV0002. I have made a function which is not generating number after GRV9999,the actual result should be GRV10000 and so on. But the function that i ve used generates GRV1000(after GRV9999). Please help me. Thanks in advance
Post the code used in the function
You may need to increase the length of the output
Hi Pinal
I am looking for good training center in Gujarat/ india for SQL SERVER 2005-2008 ….could i get inforn regarding this
Thanks
JAY
I am very new to SQL and would like help on the following.
Below is the table I am using.
person Table Structure
person ID (primary key)
person Name
City Id (foreign key)
State Id (foreign key)
District Id (foreign key)
City table structure
City ID(primary key)
City name
State table structure
State Id(primary key)
State Name
District Table structure
District Id
District Name
I want to write a SQL statement where I can get the person name, name of the city, name of the state and name of the district.
select p.person_name, c.city_name, s.state_name, d.district_name
from person p inner join city c on p.city_id = c.city_id inner join state s on p.state_id = s.state_id inner join district d on p.district_id = d.district_id
Hi Pinal,
I have a big problem with our Database Server. Database Server machine around 2 pm, memory paging / sec is too much high. After 3 / 4 hour later, memory paging/ sec again normal state. Last one month, each an every day in same process going on. All Application level transactions in a day ( 9 am to 7 pm ) are identical. No special activity done for this (2 pm to 6 pm) time.
All other SQL Server / OS level Performance Counter are normal in a day, except Disk Queue Length.
Disk Queue Length always high in a day.
Can u give a suggestion / Solution to resolve this problem?
We are using
MS SQL Server 2000 Enterprise Edition with SP 4 (included major hot fixes).
Windows Server 2003 Enterprise Edition with SP 2.
Physical RAM 16GB, Allocating 12 GB for SQL Server.
Virtual Memory = Physical RAM * 1.5
Normal – memory paging/sec (6 pm to next day 2 pm) = less than 50 / sec
High – memory paging/sec (2 pm to 6 pm) = more than 1400 / sec
Thanks
Koushik
hi
could you give the solution regardiing this error;
eventid 12291
source SQLISPackage
or suggest any book for troubleshooting.
thanks in advance
Hi Pinal
I need a script that can/will delete schemas in the various dbs
(Security -> Schemas) as I have problems deleting user accounts using Great Plains front end
Deleting the Security -> Schemas works as I can now delete the user account in Great Plains without any issues, the problem is that I have 250 such accounts to delete.
I will appreciate it if you can email me a solution and or script
Kind regards
Colin
another one…..(Great Plains V 9) can the default Account Format Setup be changed without having to do a re install
Kind Regards
Colin
Hi Pinal,
I am trying to get results wihout printing the column header using T-sql. Is there any command which we can use?
As in osq there -h-1 or by unchecking option “Prints Column header(*)”.
Many Thanks
Aj
Why do you want to do this?
Seem you want to export result to files (text,csv etc)
In that case use bcp
Hi Pinal,
i need to learn Mdx
if you have any tutorials or useful links for beginners
please advice
thanks in advance
Makarov
HI PINAL,
There is a very little Query i want to launch SQL Profiler from batch file.
the command i use is
@ECHO OFF
profiler90 /Sserver /Uuser /Ppassword /Tx
When ever i pass values & run it from batch file it launches the profiler successfully. But i need to open more than one servers in same profiler window is it possible to open multiple traces in single profiler window by the same command.
Kunal
Hi,
Pinal, Imran
I just love to read your blog.
The information provide in this is really intresting & useful.
I did so many of practices using your examples.
Thanks.
Regards,
Neetu
Hi,
I’m new to Sql server, but task given to me is not a small one.
My task is to check a table, whether it is inserted/deleted/updated. if any DML action performed i want to send mail to admin or particular person.
I decided to do this through “Triggers”. i got a sample code from net. i’ve given the code below. when i run that code i received an error
“Server: Msg 170, Level 15, State 1, Procedure CustomerUpdateMail, Line 48
Line 48: Incorrect syntax near ‘@CustomerID’.”
Code is :
CREATE TRIGGER CustomerUpdateMail
ON CustomerInfo
FOR UPDATE
AS
declare @CustomerID varchar(10)
declare @CustomerEMailID varchar(2000)
declare @body varchar(2000)
declare @CustomerName varchar(10)
declare @CustomerNewName varchar(10)
SELECT @CustomerID = Customer_id,
@CustomerName = d.First_Name,
@CustomerEMailID=d.Email_id
FROM deleted d
SELECT @CustomerNewName = First_Name
FROM inserted
SET @body = ‘Customer with ID=’ @CustomerID ‘ has been updated
with previous First Name is ‘ @CustomerName ‘
and the new First Name is ‘ @CustomerNewName
–xp_sendmail is the extended sproc used to send the mail
EXEC master..xp_sendmail
@recipients = @CustomerEMailID,
@subject = ‘Customer Information Updated’,
@message = @body
GO
please help me to solve this problem
Thanks & Regards,
S. Ramkumar
While taking append backup, when every 6th backup is completed, i want 1st backup to be removed from that backup file. when 7th backup is completed, 2 nd backup to be removed, when 8th backup……. How to do/set it for append backup.
I have multiple database i think more than 40-50 dbs, I have to backup some dbs name LIKE ED% and delete tables LIKE REL% from each db.
Anybody can solve this code?
USE Master
GO
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
DECLARE @sql NVARCHAR(4000)
SET @path = ‘D:\Db_Backup\’
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT [name]
FROM sys.Databases
Where [name] LIKE ‘ED%’
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5
print ‘Db Name is : ‘ + @name
–I can not get sys.tables from @name db on following lines
–First i have to backup db name ED% than drop the tables REL%
WHILE EXISTS (SELECT TOP 1 * FROM @name .sys.tables WHERE [name] LIKE ‘REL%’)
BEGIN
SET @sql = ‘DROP TABLE EDBO.[' + (SELECT TOP 1 [name] FROM @name .sys.tables WHERE [name] LIKE ‘REL%’) + ‘]’
EXEC (@sql)
END
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
I need help with partitioning large sql server 2005 tables with Referntial Integrity with 6 other tables. Any thoughts or help?
hi Pinal
i’m a newbie to sql server. i’m having two versions sql server 2000 and sql server 2005. i want to find out the installed versions of sql server.
i tried this “select @@version” but the result was only MS sql Server2000 not 2005!!
i tried this also in command window
c:\>osql -Q “select @@version”
i need ur help!!
thanx in advance!!!
Dear Sir,
I am using SQL Server 2005 and I want to have periodical backup (based on condition) of a particular table with appending mode. And how can I do the following using script/SP or any other solution?
Table1 data should be transferred/appended to Table2
before truncating them from Table1
could you please assist me in this?
thanks
Jeet
I’m not sure what are you trying to achieve when you say periodical backup. If you would like to go for job scheduling then embed the code in SP and schedule in SQL Server Agent specifying the timeline.
simple solution :
insert into table2
select * from table1
go
truncate table table1
make sure columns, data types etc are proper…
Alsways specify the column names. If a column is added in any of the table, the code will break
Hello,
I want to create an sql database from vb.net 2005 through windows application. In connection string i need to specify a default database name first but i dont want to include the database name the first time i create the database. After creating my database i want to use it in connection string on the basis of which all the other project queries are executed.
What do i do to create the database for the first time without passing the database name in the connection string.
Do i have some other way out to create database from vb.net 2005.
Pls, reply me.
Rachana
sri
i need your help. we found following errors in running qury thru VB 6 exe. we use SQL Server 2000 with service pack 4 and OS 2003
error
“query processor could not produce a query plan because of the hints define in this query. Resubmit the query without specifying any hints. and without specifying any hints and without using SET FORCEPLAN ”
waiting for your reply
Regards
Samip Shah
I need your help regarding an issue on IDENTITY column. I have a Order table with an identity column. When ever a new record is created in order table new identity column value is generated written in a stored procedure. I also have a product table updation written in a separate stored procedure which requires the identity column generated in Order table. Both these stored procedures are running in a single transaction( Order will be saved first and then product will be saved). So i am not able to retrieve identity col value req for product table till the whole transaction is committed.
HI PINAL,
I am big fan of your blog.
But I am sorry to say that You did not even mention about Mumbai terrorist attacks.
As a citizen of India at least we can do console .
I expect article /note from your blog .
Thanks & Regards
Kalyan
HI PINAL,
I am big fan of your blog.
But I am sorry to say that You did not even mention about Mumbai terrorist attacks.
As a citizen of India at least we can do console .
I expect a article /note from your blog .
Thanks & Regards
Kalyan
Hi,
From past 3 months I am visiting your site regularly. I learn lot of things from your site. I need your help and guidance for learning SSRS, SSIS and SSAS. Can you help me in this.
Thanks
Nag
Hi Pinal,
I am trying to implement the transactions in ssis packages.The problem i am facing is when i run the package which uses bulk inserts tasks(3) and a bunch of execute sql tasks.When i run the package from visual studio the package fails in the last step sothat i can test the transaction rollbacks.as long as i am in the debug mode and package is in running mode, in sql server 2005 management studio the database to which the ssis is operating on is not accisable and it gives lock time out error 1222. But as soon as i end the the package the database is browsable(meaning the table list,sp list…)
is there any way i can access the db while ssis pacage is still running mode?
Thanks,
Madhu
hi…
This is vjayabharathi.i was downloaded Sql srver 2005 express edition after downloading i was instaleed in my personal system but i didn’t find out Query Analyzer to do Sql &T-Sql queries. before installation of Sql server 2005 express edition i was installed DOTNEt Frame work as a support file but i didn’t find out query Analyzer…but i didn’t understand what’s the problem…plz send me some queries ASAP. i need Sql server 2005 Express Edition software r different edition & what is tha support file plz send me details…at the same time plz send how to install….
thanks & Regards
Vijayabharathi.
Hi Pinal,
I was asked this in an interview, I have only 1 column in a table and i need to break it into two columns..
Plz give your ideas in this
Thanks in advance
Jayanth
Did you mean breaking data into two columns?
Hi Dave!
SQL SERVER – Clear Drop Down List of Recent Connection From SQL Server Management Studio
… Mru.dat …
Observation:
This does not work (for SQL Server Express 2008)? But if you erase
C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\100\Tools\SqlStudio.bin
- the list is empty!
- with regards, Predrag
Dear Sir Pinal,
Please help me to solve my problem. I can’t continue my program because of this problem which I know you can solve or can suggest the best possible solution.
I have a project in my study doing simple accounting program. To facilitate the discussion, I will just
show you the two tables where I want to insert records – the TransactionTable and the TransactionDetailsTable.
I am very confused of how to create a stored procedures to insert a single row of record in TransactionDetailsTable
and multiple rows of record in TransactionTable. Can you please help me to create the perfect one for my problem?
Below is a sample transaction
Transaction Reference Number: CR12345
Transaction Date: 12/06/2008
GLCode GL Description Debit Credit
———– ——————— ———– ———–
1010010000 CASH 1,500.00
1050020000 Sales Discount 30.00
1050010000 SALES 1,500.00
Particulars: To record cash payment from customers on previous sales.
TansactionTable Rows are as follows:
TransNum BigInt PK AutoIncrement- This will store
transaction identity
TransRef VarChar(16) FK NOT NULL- This will store
transaction details as entered by the
user such as CR12345 based on the
above sample transaction
GLCode Char(10) NOT NULL – This is the Code of the
General Ledger Account stored in the
GLTable such as 1010010000 for
CASH, 1050020000 for SalesDiscount,
etc based on the above sample
transaction.
SLCode Char(10) NULLABLE- This is where I will store the
clients Code if there is a transaction
related to them
TransAmount Money- This is the amount of debit(+) or
credit(-) transaction
TransactionDetailsTable rows are as follows:
TransRef VarChar(16) PK - Handler of Transaction Details
TransDate SmallDateTime – Date of Transaction such as
12/06/2008 based on sample transaction
TransParticulars – to handle particulars of the transaction
such as “To record cash payment
from customers on previous sales” on
sample transaction.
I created the transactionDetails table to handle Transaction Date and Particulars of the Transaction so that
it will not be recorded repeatedly on TransactionTable.
I created a function under the Class as follows
Public Sub AddTransaction(ByVal myTransTable As DataTable, ByVal iret As Integer)
I have strConnectionString declared.
Dim myConnection As sqlConnection = New SqlConnection(strConnectionString)
Dim cmdAddTransEntry, cmdAddTransRef As SqlCommand
Dim ParamTransRef, ParamRef, ParamTransDate, ParamParticulars, ParamUserID, ParamGLCode, ParamSLCode, ParamTransAmount, ParamTransCat As SqlParameter
Dim myTrans As SqlTransaction
Dim strAddTrans As String
myConnection.Open()
myTrans = myConnection.BeginTransaction
Try
strAddTrans = “INSERT INTO TransactionDetailsTable(TransRef, TransDate, TransParticulars)” & _
“VALUES(@TransRef, @TransDate, @TransParticulars)”
cmdAddTransRef = New SqlCommand(strAddTrans, myConnection)
cmdAddTransRef.Transaction = myTrans
ParamTransRef = cmdAddTransRef.Parameters.Add(“@TransRef”, SqlDbType.VarChar, 16)
ParamTransDate = cmdAddTransRef.Parameters.Add(“@TransDate”, SqlDbType.SmallDateTime, 8)
ParamParticulars = cmdAddTransRef.Parameters.Add(“@TransParticulars”, SqlDbType.VarChar, 300)
ParamTransRef.Value = VoucherNo
ParamTransDate.Value = dtSystemDate ‘ stSystemDate is given under Global Module
ParamParticulars.Value = Particulars
cmdAddTransRef.ExecuteNonQuery()
strAddTrans = “INSERT INTO TransEntryTable(TransRef, GLCode, SLCode, TransAmount)” & _
“VALUES(@TransRef, @GLCode, @SLCode, @TransAmount)”
cmdAddTransEntry = New SqlCommand(strAddTrans, myConnection)
cmdAddTransEntry.Transaction = myTrans
Dim TAmount As Double = 0
For i = 0 To myTransTable.Rows.Count – 1 ‘ Came from Dataset
‘to determine if debit or credit and if credit make it negative(-)
If Not IsNumeric(myTransTable.Rows(i)(2)) Then
TAmount = myTable.Rows(i)(3) * (-1)
Else
TAmount = myTable.Rows(i)(2)
End If
ParamRef = cmdAddTransEntry.Parameters.Add(“@TransRef”, SqlDbType.VarChar, 16)
ParamGLCode = cmdAddTransEntry.Parameters.Add(“@GLCode”, SqlDbType.Char, 10)
ParamSLCode = cmdAddTransEntry.Parameters.Add(“@SLCode”, SqlDbType.Char, 10)
ParamTransAmount = cmdAddTransEntry.Parameters.Add(“@TransAmount”, SqlDbType.Money, 10)
ParamTransCat = cmdAddTransEntry.Parameters.Add(“@TransCat”, SqlDbType.Char, 1)
ParamRef.Value = VoucherNo
ParamGLCode.Value = myTable.Rows(i)(0)
ParamSLCode.Value = SLCode
If ParamSLCode.Value = “” Then
ParamSLCode.Value = String.Empty
End If
ParamTransAmount.Value = TAmount
ParamTransCat.Value = PrivTransCat
cmdAddTransEntry.ExecuteNonQuery()
Next
myTrans.Commit()
Catch ex As Exception
myTrans.Rollback()
MsgBox(“Transaction Not Added”)
Finally
myConnection.Close()
End Try
End Sub
executing this will return error as follows under cmdAddTransEntry.ExecuteQuery()
The variable name ‘@TransRef’ has already been declared. Variable names must be
unique within a query batch or stored procedure.
Please help me solve this problem.
Thanks in advance and more power
Ariel
Hi All,
This thread is closed for comments. If you have any question or need my help. Please go to following link and post comment.
http://blog.sqlauthority.com/contact-me-contact-pinaldave/
Kind Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi,
I am developing a education portal. We are taking tests of the student whoever logs in for the first time and then save the details of the student and the related test in a table. This tests are useful in analysis for giving practice sheets for his learning purpose. So according to his sore we would give him practice sheets would be given which will also be stored in the DB. So should I store the practice sheets in the same DB or create new DBs or new table for each distinct student. Please send your contact no on my mail ID for my further reference.
Thanking You.
I have problem with this qurey, result is ok but i have seen multiple duplicate records in brows mode please tell me about my problem the query is blow there:
select c.batch_no,a.ship_date,b.custname,c.contract,c.style,c.quantity,c.quantity/c.qty_perctn as ctn_qty,d.ctn_size;
from ex_paper a,customer b, ex_pap1 c, p_style d into cursor cebat where A.CUST_CODE=B.CUSTCODE AND c.batch_no=a.batch_no and c.style=d.style;
AND BETWEEN(a.ship_date,date1,date2)
please tell me about above this query
regards,
Zahid Khan
Dear Pinal,
Not sure if this is the right way to contact you. I am new to SQL 2005. Read your instructions to set up a remote oracle publisher from sql 2005. I have a evaluation version of sql 2005 enterprise edition.
For some reason I am unable to launch the new oracle publisher wizard from the replication folder in SSMS. I installed the oracle software and can connect to the remote oracle server and also created an Administrative account in the database.
I am trying to set up replication from oracle to sql so that the tables have updated data if new rows are added or modified.
I have set up a test environment at home and have installed oracle 10g r2.
Is there something I am missing. Can you please help.
Regards
Deepak
Hi Dave,
Since everyone has the problem of converting UFT8 to latin charset.I am also having the same problem when i migrate the data from oracle to sql server.I have gone through most of the forums ans still I havent come up with a proper solution.
Kindly provide us a solution.
Thanks,
Arun RS
Hi Pinal,
I have just installed Sql Server 2008 server. When I am trying to create Maintenance plan from management studio, when I click on save butoon getting error catastropic failure message.
How I can resolve this error, I am able to too every singal function like backup running query everything working fine.
I am not DBA beginner only.
Thanks,
Hi pinal, will you please assist me i would like to build a database that will be storing music but i am not winning even if you can give me tutorial link i will be glad.
Thanx
Hi syndney,
Would you please explain your problem in detail? I will sure be able to help you.
Regards,
Pinal Dave ( http://blog.SQLAuthority.com )
Hi, I thank for the reply, the music database that i would like to create has one of the table that has the following columns:
Artist_ID
Track_ID
Track_Title
Track_FileName
Track_Sequence
So i would the design of this table, more focus on the “Track_FileName” the data type and will need for me to have file for each album on my database.
thank you
have a table called test with 2 columns like jobid & empid.this is my table….
jobid empid
1 12345
1 13421
1 24131
2 25243
2 52312
2 54572
2 87921
3 10001
3 51024
4 87610
4 87622
now i have to display the result as like this
jobid count
2 4
How can i write the sql query for this……….
select jobid, count(empid) from test
group by jobid
@ss
Use Group By,
here is an example.
create table test (Jobid int, empid int )
insert into test values ( 1 ,12345)
insert into test values (1, 13421)
insert into test values (1 ,24131)
insert into test values (2, 25243)
insert into test values (2 ,52312)
insert into test values (2 ,54572)
insert into test values (2 ,87921)
insert into test values (3 ,10001)
insert into test values (3, 51024)
insert into test values (4, 87610)
insert into test values (4 ,87622)
select Jobid, count(*) [Count] from test
group by Jobid
– drop table test
Hope this helps,
IM
Could you recommend some advanced training sessions/workshops in Mumbai,India on SSIS SSRS SSAS?
sir
i need how to connection to sql server 2005 to visual basic 6.0 . than i put in sql server 2005 user is sa put password but i change my password in sql server not work in vb pl reply me sir
I want to select First value and Last value for the particular date and Id
Table Ex.
ID DATE VALUE
001 23:04:2009 APPLE
001 23:04:2009 GRAPHE
001 23:04:2009 ROSE
001 24:04:2009 BERRY
001 24:04:2009 TIFFANY
001 24:04:2009 ORGANE
001 24:04:2009 SILVER
FOR 001 VALUE NEEDED –
APPLE AND ROSE FOR THE DATE OF 23:04:2009
BERRY AND SILVER FOR THE DATE OF 24:04:2009
Result Expected:
ID DATE VALUE
001 23:04:2009 APPLE
001 23:04:2009 ROSE
001 24:04:2009 BERRY
001 24:04:2009 SILVER
I tried Min, max, top condition but I am not getting a proper answer
SQL QUERY HELP urgent
check out the below code snippet :
create table #temp(
ID varchar(10), DATE datetime, [VALUE] varchar(100))
go
insert into #temp
select ’001′, ’2009-04-23′, ‘APPLE’
union all select ’001′, ’2009-04-23′, ‘GRAPHE’
union all select ’001′, ’2009-04-23′, ‘ROSE’
union all select ’001′, ’2009-04-24′, ‘BERRY’
union all select ’001′, ’2009-04-24′, ‘TIFFANY’
union all select ’001′, ’2009-04-24′, ‘ORGANE’
union all select ’001′, ’2009-04-24′, ‘SILVER’
– method 1
select v1.id, v1.date, v1.[value]
from (select id, date,[value],
row_number() over(order by id, date) as row_num from #temp) as v1
where v1.row_num in ( select row_num from (
select v.id, v.date,
min(v.row_num) as row_num from (
select id, date, row_number() over(order by id, date) as row_num from #temp) as v
group by v.id, v.date
union
select v2.id, v2.date,max(v2.row_num) from (
select id, date, row_number() over(order by id, date) as row_num from #temp) as v2
group by v2.id, v2.date) as v5)
– method 2 :
with cte(id, date,[value],row_num) as
(select id, date,[value],row_number() over(order by id, date) as row_num from #temp
)
select c1.id, c1.date,c1.[value] from cte c1, (
select min(row_num) as min_row_num, max(row_num) as max_row_num
from cte
group by id, date) as c2
where c1.row_num = c2.min_row_num
or c1.row_num = c2.max_row_num
Also refer these methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
Hi All,
This is archive page. Posting your question here may not get quicker answer.
Please contact me here : http://blog.sqlauthority.com/contact-me-contact-pinaldave/contact-me-contact-pinaldave-archive-1/
Find solution to your questions : http://search.sqlauthority.com
Kind Regards,
Pinal
Hi i have report (SSRS 2008 ) which look like this…………..
Business Area MTD Month Estract X Y Z
Segment Sales Plan Sales
Category %
Brand
——————————————————————————————————————————————–
+LL Builder 34567 678 111 888
+Decorative 43727 457 2453 6574
+ xyz 3587 8764 1134 36572
+pqrs 6154 7676 8699 58585
I need to sort the column that is if some one clicks on MTD Sales %
all the values in the column need to be sorted even the drill down
values in +LL Builder and others also need to be sorted
I have tried different ways using interactive sorting
In interactive sorting
i have selected Group By and in Group By i have selected Business_Area
and in Sort By ………i have selected MTD Sales (=Sum (Fields! MTD
Sales.Value)
and in also sort group i have selected Tablix ………
its working fine with normal values…………….and for drill down
values its not working.
Query:
SELECT NON EMPTY { [Measures].[EST YTD NET SALES % of PLAN],
[Measures].[EST YTD NET SALES % of PREV YR],
[Measures].[MaxERAEOperTimeSales], [Measures].[YTD PLAN],
[Measures].[TOTAL SNB GROSS], [Measures].[YTD DP@STD], [Measures].[YTD
DP@STLF], [Measures].[CURR BACKLOG GROSS], [Measures].[TOTAL BACKLOG
GROSS], [Measures].[EST MTD NET SALES], [Measures].[MTD NET SALES],
[Measures].[EST YTD NET SALES], [Measures].[YTD NET SALES],
[Measures].[EST MTD NET SALES % of PLAN], [Measures].[MONTH PLAN] } ON
COLUMNS, NON EMPTY { ([Product].[By Business Area].[Business
Area].ALLMEMBERS * [Product].[By Segment Category
Brand].[Brand].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (
STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@ByYearMonth, CONSTRAINED) ) ON COLUMNS FROM
[SALES])) WHERE ( IIF( STRTOSET(@ByYearMonth, CONSTRAINED).Count = 1,
STRTOSET(@ByYearMonth, CONSTRAINED), [By Year Month].currentmember ),
IIF( STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED).Count = 1,
STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED), [Selling
Company].[By Selling Company].currentmember ) ) CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS
Hi Pinal,
i have a column with positive and negative numeric values in it, how will i sum positive and negative values separately using aggregate function….Can anybody guide me….Thanks in advance….
Shah,
If i understand your Qstn correctly, the column is integer type. Find the below code.
select sum(column_name) from table_name
where column_name > = 0 — for positive
union
select sum(column_name) from table_name
where column_name < 0 — for negative
If it should in the same row,
select sum(case when col_name>=0 then 1 else 0 end) as positive_count,sum(case when col_name<0 then 1 else 0 end) as negative_count from table
@Shah
SUM(CASE WHEN col1 = 0 THEN col1 END) positive,
[looks like my last comment was trimmed]
@Shah
1) SUM(CASE WHEN col1 = 0 THEN col1 END) positive
[still being trimmed. Here's the second one:
SUM(CASE WHEN col1 < 0 THEN col1 END) negative
Hi All,
This is archive page. Posting your question here may not get quicker answer.
Please contact me here : http://blog.sqlauthority.com/contact-me-contact-pinaldave/
Find solution to your questions : http://search.sqlauthority.com
Kind Regards,
Pinal
Hi Pinal,
would you please assist me to get the resolution in the following issue.I wrote this in the vbscript
Set CnnSQL=CreateObject(“ADODB.Connection”)
CnnSQL.Open “Provider=SQLOLEDB;” & _
“Data Source=datasourcename;” & _
“Initial Catalog=databasename;” & _
“User Id=user;” & _
“Password=password”
my probelm is i want to pass the dynamic variable to initial catalog i.e. ” i ” is the value of database name reading from the text file and have to pass the different database name value to initial catlog for each loop
i.e Initial Catalog=i (Parameterization)
But when i executed that vb script i m getting the error i.e can not read the ” i “value
Can you please provide me the solution.
Thanks,
Veera
Hi,
when i am restoring a database in sql server 2005. In compatability level sql server 2005 is not showing, if i select sqlserver 2000 it was giving me a error that “too many backup specified for backup or restore only 64 allowed”.
It was giving me this error even though i m using sqlserver 2005.hw can i get sqlserver 2005 in compatibility dropdown list.
Regards,
S.Inayat Basha.
Hi Inayat,
What statement you are using to restore the database. Compatibility level is not specified at restore time. Let us know the statement and error in details.
Regards,
Pinal Dave
Hi Pinal,
Wt is sqlserver injection.can u plz tell me about this topic and the usage.
Regards,
S.Inayat Basha.
Hello Inayat,
SQL injection is inputing a sql command to violate security and steal or corrupt data or even server.
Most common way of SQL injection is changing the statement by passing specific values in parameters.
This can be avoided by strongly typed parameter, using sp_executeSQL with parameter instead of dynamic query, replace ‘ (single quote) with ”(two single quote) in input paramter values and type check before using in statement.
For more details please visit: http://msdn.microsoft.com/en-us/library/ms998271.aspx
Regards,
Pinal Dave
HI
i Have 3 tables one to many relation
1 . tblStudent
studintID pk
studentName
2 . tblStatus
statusID pk
statusName
3 . tblStudentStatus
srNo pk
studintID fk
statusID fk
How can i select latest( top ) status of All Student ?
when i use TOP(i) it selects only i Student please help me
Try this
select studentid,max(statusid) as statusid from tblStudentStatus group by statusid
Hi,
When i going to debbug SP in VS 2008 its giving error “canceled by user”
SQL Server Edition: SQL Server 2005 SP3 Enterprise Edition,
Win server 2003 64 bit,
Please help me.
Just wanted to extend my thanks and appreciation for devoting your quality time and energy for the betterment of SQL Server and IT as a whole.
Hello pinal,
I just know about the .bak file.
I lost my .mdf file so how it possible restore my database with .bak file.
I created database same as previous name.
and restore with my backup file.
but I got error.
————————————————————
Restore failed for Server ‘MYPCNAME’. (Microsoft.SqlServer.Express.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Directory lookup for the file “D:\SQL2005DataBases\TestDB.mdf” failed with the operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Express.Smo)
————————————————————
For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID
How to maintain more databases say 1000 databases in .net?
Hi,
I am using CRM 2011 application and sql 2008 R2 Enterprise.
Now, i need to migrate crm to different server which is having sql 2008 R2 Standard.when i am migrating database i from sql enterprise to sql standard i am getting the following error.Kindly guide me to resolve this issue:
http://social.microsoft.com/Forums/getfile/12795/
Thanks in advance
Jeriesh
I have two tables tbl1 and tbl2.
in tbl1 one field is there.
FirstName
a
b
c
d……..
in tbl2 i have Menu field
menu
x
y
z….
Now i would like to display Like… (Menu Fields are dynamic creation i.e x,y,z)
FirstName x y z
a
b
c
d
Hi Pinal,
I am sort of novice as a DBA. I just started learning about log shipping. I did a test on my own computer that I have created a database called ‘Test’, then I restored to a new database ‘Test2′. FYI, I used with norecovery while doing the restoring the database.
While restoring process is on going. I create a log shipping, Database Test as primary database and Test2 as secondary database. Test2 is set to Stand By Mode.
Then I tried to run the related jobs manually and it looks successfully done. But,there is one thing seems to be strange. I got Skipped log backup file log shipping. Secondary DB: Test2. Could not find a log backup file that could be applied to secondary database Test2.
What’s the main problem that makes this issue raised up?
Thanks in advance.
hii pinal,
I have a doubt regarding ssis,in that how the for each loop gets success even one of the inner tasks get failure.