Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1200 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia.
I am proficient in Corporate Training. I have designed and implemented complex database architecture, and have also implemented strategies for database high availability and scalability. Furthermore, my core expertise lies in query tuning and performance optimization.
If you want to seek my expertise then drop me a line and tell me about your requirements by using the form below or send me email pinal “at” sqlauthority.com. I value development community and will be happy to help you at any stage of project development, from design to deployment.
Copyright violation and Reproduction of blog:
SQLAuthority.com is trademark of Pinal Dave. Exact work “SQLAuthority” or “SQL Authority” in any form or medium without explicit written permission of Pinal Dave.
If any article published on this blog violates copyright please contact me, I will remove it right away.
Linking to this blog is allowed. It is allowed to reproduce maximum of 160 words or 8 lines, which ever is maximum in event of linking to blog (Link is must).
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.
Community Rules
- Do not post obscene, indecent, hateful, offensive, defamatory, abusive, harassing or profane material. They will removed.
- Do not post junk mail, pyramid schemes, chain letters or advertisements.
- Do not engage in personal attacks. We have zero tolerance for such incidents.
- Vulgar and insulting nicknames will be removed. Nicknames attempting to impersonate other users will be removed.
- Do not post anyone’s telephone number, street address, email address. or any other personal information.
- Do not post copyrighted material.
- Lively debate and opposing opinions are welcome, but please behave courteously.
pinal “at” SQLAuthority.com
pinaldave “at” yahoo.com
Please search at http://search.sqlauthority.com before posting any question here.
HI,
What is the best way to use unique value (for PKs) across the tables (must be unique in whole database) other than GUID/Uniqueidentifier. In the problem, I need Bigint as PK for all the tables and there is also a requirement to set it as default value for some columns (as like we use NewID()), so how it can be used in default values also.
Please suggest,
Thanks
Dear Pinal!
Firstly, I just would like to say thank you so very much for this wonderful website! The tips and code segments I here found here helped me very often.
I have a question: I want to get the drop code of a database object programmatically. This is not an option for me, since I have to do it programmatically.
I’m aware that one can do this by right-clicking on the database object and selecting Script as -> DROP and CREATE To -> New Query Editor Window.
Another way of doing it is to right-click on the database one is working on and then selecting Tasks -> Generate Scripts, and then selecting which database object you want to create scripts for. Once again, this is not an option, since it requires manual interaction.
I know that when you use
select object_definition(object_id(‘DBObjectName’)),
you’ll get the actual code that creates the object, but it does not include the if exists(‘DBObjectName’)…drop(‘DBObjectName’) part.
1. How do I get this extra if exists(‘DBObjectName’)…drop(‘DBObjectName’) part?
2. Also, I want to get the definition query that creates tables (programmatically since the query
select object_definition(object_id(‘DBObjectName’))
cannot be used on tables, there must be another way that be used to get the table definition query. What query should I use?
Any reply/replies will be very much appreciated.
Thanks so much in advance!
Kind regards
Ian
Why do you want to do this programmatically as there are easy options already available?
I have a production DB of 950 GB. It has been reporting I/O’s taking more than 15 seconds error. I tried everything . The drives are set up perfectly log and data different drives and temp is on its own RAID. It has 32 GB RAM, 2.5 TB disk space, 4 quad core processor sitting on a G5.
Microsoft tracked it down to cluster size of the disk leading to misalignment in the HDD. Is there a comprehensive way to determine the stripe unit size and determine the appropriate cluster size of the HDD for sql data and log file.
a row from a table in sql is deleted. I want to get the datas of that row through sql quries
If you used transaction you can rollback it
Otherwise depends on the recovery model of the database you can get it from a log file
If you have latest back, you can get from it too
Hi Pinal,
When we creating a cluster index and drop that index significantly reduced the unused space in tables .After doing this similar to all tables, database performance will increase or decrease ?.
please explain me
We are using SQL Server 2008 in our company, and facing a problem with GROUP BY GROUPING SETS
The data are like,
CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)
Now when I execute following query,
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId), (Yr))
It gives me following error,
Incorrect syntax near ‘SETS’.
I know I can use UNION ALL, but curious to know what is the problem with GROUP BY GROUPING SETS
Will appriciate any help, thanks….
Hi Ekta…
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr
Hello Ekta,
Make sure that the compatibility level of database is 100. Please let us know if this is not the issue.
Regards,
Pinal Dave
Ekta, this is working absolutely perfect with me.
It wont work in versions prior to 2008, OP must have tried it with earler version or compatibility below 100
i am facing a problem with join and max(lenght). Need a help to get a good perfomance in sql server .
I have a table with phone calls logs to different countries and cities and i want to add a column with country name and city name depending in the called number.
For example:
Table 1
Time_stamp—Called_number
00:12:56 346783049056
01:45:23 344556578865
02:12:45 3469I5354435
04:34:34 336765757575
05:23:23 3312369O3404
06:34:34 3244123131335
08:34:43 3220O4245453
table 2
Code—– Country
34 Spain
346 Spain mobile
33 France
336 France mobile
32 Belgium
324 Belgium mobile
i need a join table that will give the calls with the exact country name, for example for the number 346783049056 the country name can be Spain and Spain mobile but 346 is correct.
I try using self join and correlation using the length of the country code but performance is not good because my table1 have more than 1 million called numbers and table2 have the codes of all the world
This the tjoin that i need from my example
Time_stamp—Called_number— Country
00:12:56 346783049056 Spain-mobile
01:45:23 344556578865 Spain
02:12:45 3469I5354435 Spain-mobile
04:34:34 336765757575 France-mobile
05:23:23 3312369O3404 France
06:34:34 3244123131335 Belgium-mobile
08:34:43 3220O4245453 Belgium
i will appreciate any help
Regards
Fangu
Fangu,
Dont’ you have fixed characters that represent Country code ? In your case, you have CountryCode varying in length for Countries. E.g, 2 chars for Spain, 3 chars for Spain Mobile..
Please clarify your business requirement
Hi,
I want to add a days into date by excluding saturday & sunday in a single query, I know it can be done by function but i want in a single query whether it would be possible…
Kindly confirm
consider daye is : 15-Dec-2009
days :10
output : 29-Dec-2009
vivekanand
Use this
declare @input_date datetime
set @input_date='15-Dec-2009'
select max(dates) as date from
(
select top 10 dateadd(day,number,@input_date) as dates from master..spt_values
where type='p' and number >0 and
datename(weekday,dateadd(day,number,'15-Dec-2009')) not in ('saturday','sunday')
order by 1
) as t
Hi
Hi Pinal,
I am ashok i want mirror concept through wizard(i configured through wizard sucessfully but when i stat mirroring its getting error) pls give me some suggestions
Can u please send the best solution
HOW TO MovE the all user databases .MDF & LDF files from (Default location )C:\ PFiles\Mssqlserver\ ….drive to D:drive in the tstsqlserver
tHANKS IN ADVANCE
Hi Siva,
Stop the SQL Server service and then you can move any .ldf or .mdf file from any where to any location.
Thanks
Manish
A very nice example is there in BOL. Pasting the same below -
USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
NAME = Test1dat2,
FILENAME = N’D:\t1dat2.ndf’
);
Stop SQL Server, copy the files to the location specied. Start SQL
Another way of doing this is by right-clicking on the database you want to move (in SQL Server Management Studio) and then selecting “Tasks”->”Detach”.
After moving the database files to the desired location, right-click on “Databases” (also in SQL Server Management Studio) and then selecting “Attach”.
This way, you can move the database without the need for stopping and starting the SQL database engine.
Have a great day! :)
Hi Pinal,
I am unable send attachemts from dataserver but without attacment can send mails.The problem arises only when there is an attachment. Same works fine in all the other servers with same configuration including the dbmail config.I am able access the same path with XP_CMDShell dir command.Any help would be greatly appreciated.
sql server error:
\\Shared\softusers\Gautham\att.txt
Msg 22051, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Event Log:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:18456, OS: 18456 [Microsoft][SQL Native Client][SQL Server]Login failed for user ‘ABC-SQL\sqlaccount’..
—————————————————————————
I learnt lot of DBA stuff from your blog, it is amazing that you have experstise in almost all the areas of sql server.
You do a great job helping DBAs like me and almost for all the issues i could find soultions from your blog.
Thanks in Advance.
Regards,
Gautham
Hi Pinal,
I need to monitor a lock issue and is required to monitor blocked sentence, blocking sentence and duration as well, to realize this I try to use profiler but our hosting provider deny all remote access to the database, when I start profiler on the database server general immediately performance falls to an unacceptable level, any advice would be highly appreciated.
Thanks & Regards,
Gustavo
Hello Gustavo,
If you want to log all the details of deadlocks then best is use trace flag 1205 & 3604. Otherwise you can manually or by job, execute the below script to know blocking & blocked spid and statements:
select t1.request_session_id as [Waiter spid], — spid of waiter
t2.blocking_session_id [Blocker spid],
db_name(resource_database_id) as [Database],
t1.resource_type as [Resource type],
case t1.resource_type when ‘OBJECT’ then object_name(t1.resource_associated_entity_id)
else t1.resource_associated_entity_id end as [Blocking object],
t1.request_mode as [Lock req mode],
t2.wait_duration_ms as [Wait time],
r.text as Waiter_batch,
substring(r.text,exr.statement_start_offset/2,
(case when exr.statement_end_offset = -1
then len(convert(nvarchar(max), r.text)) * 2
else exr.statement_end_offset end – exr.statement_start_offset)/2) as Waiter_stmt,
p.text as Blocker_batch,
substring(p.text,blk_exr.statement_start_offset/2,
(case when blk_exr.statement_end_offset = -1
then len(convert(nvarchar(max), p.text)) * 2
else blk_exr.statement_end_offset end – blk_exr.statement_start_offset)/2) as Blocker_stmt
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2,
sys.dm_exec_requests exr
cross apply sys.dm_exec_sql_text(exr.sql_handle) as r,
sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) as p,
sys.dm_exec_requests blk_exr
where
t1.lock_owner_address = t2.resource_address
and exr.session_id = t1.request_session_id
and sp.spid = t2.blocking_session_id
and blk_exr.session_id = t2.blocking_session_id
Regards,
Pinal Dave
Hi Pinal,
it wud b very helpful for me if give information about how to do Microsoft certfication.
I have 4 yr Exp of ASP.net (C#). Pls guide me .
Regards,
Hi Pinal,
I am creating an SSIS package to import CSV file to a SQL table. The main problem is that the number of columns in this CSV file varies ( so i need to create table every time i need to import the file ) i.e. today there can be 5 columns tomorrow the number of columns may be 10. Is it possible to create the table as per the CSV file. Please help.
Thanks,
Rupesh
Follow one of the methods described here (creating a view, format file or using OPENROWSET)
It exactly does what you wanted to do
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
Hello Rupesh,
Instead of desiging a complex package to load a table of varing number of columns and alter table according to the source data, you can create a simple package to load all data from csv into a single column of a temporary table. Then split the data usgin substring or other customized functions and load into destination table. In t-sql this can be done by much less effort.
Regards,
Pinal Dave
Hellow Sir,
I want to use index.How can i get max(value) of column- Transno which is present in both tables :- table1 and table2
Thanks in Advance
Yours
Kishan
@kishan
What’s wrong with the MAX(). Can you give a sample to help explain.
Hi Kishan,
Use MAX() with UNION as below:
SELECT MAX(value) FROM
(SELECT value FROM table1
UNION
SELECT value FROM table2
) AS tmp
Regards,
Pinal Dave
Please let me know
how to write a function to take input from user as integer and give an output as the number is even or odd
eg:
Input 4 Output Even
Input 5 Output Odd
@Ajay
DROP FUNCTION Is_Even;
GO
CREATE FUNCTION Is_Even(@A INT)
RETURNS CHAR(4)
BEGIN
RETURN CASE @A % 2 WHEN 0 THEN ‘Even’ ELSE ‘Odd’ END
END
GO
SELECT 4, dbo.Is_Even(4) UNION ALL
SELECT 5, dbo.Is_Even(5);
Hi Pinal.
I have one question please clarify this. Is it wrong to declare a variable like DECLARE @@Sample AS NVARCHAR(50).
I tried this in MSSQL 2005. I didn’t get any error. But i hope this is wrong but don’t know how ?
Thanks & Regards
Mohan Dass.
@Mohan
I will start by saying, i don’t know. I just noticed that anything works:
DECLARE @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@A int
SET @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@A = 1
SELECT @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@A
I think it’s just another variable.
This works too
declare @ as int
Hi, i am new to sql server. i have just installed sql server 2005 and its giving
” TITLE: Connect to Server
——————————
Cannot connect to MSSQLSERVER.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
——————————
BUTTONS:
OK
——————————
i AM USING WINDOWS 7
I have installed default instance and if i run same settings on mindows xp it works fine. but on windows 7 they show error. now i along with 2005 i have also installed sql 2008, its showing same error but if i use server name: (local), then its connecting to database engine in sql 2008
Hi Sumit,
Please check the below article:
http://blog.sqlauthority.com/2007/04/23/sql-server-fix-error-40-could-not-open-a-connection-to-sql-server/
Regards,
Pinal Dave
Dear Sir
First, I want to say u as “Thanks a lot”. I downloaded SQL’s questions and answers from your web site. Sir, may I contact and to get guideline from you for a good SQL professional. I ever enter and see with u by online if you free time.
HELLO Mr.DEv
I have a Question
I have 3 tables TECTA,Elearning and Curriculum
I have a PERSON table in TECTA
PersonID is a primary key in this table.
My Boss said this primary key is used as a foreign key all these 3 databases
I want to update the foreogn key tables whers Primary is equal to foreign jey.
I can update the foreign key tabkles in tects by using your advice. thank u so much for that.
COuld u help me like how can I update in other databases also at the same time.
WITH CTE (updatePersonId,persondupeId,duplicateCount)AS
(
SELECT personID AS updatePersonId,MIN(personID) OVER(PARTITION BY FirstName,LastName,MIddleName)AS persondupeId,
ROW_NUMBER() OVER(PARTITION BY FirstName,LastName,MIddleName ORDER BY FirstName,LastName,MIddleName)AS duplicateCount
From V_DuplicatePerson
)
select updatePersonId,persondupeId into tempdupedata from CTE Where duplicateCount>1 AND updatePersonId!=persondupeId
Please provide solution for this . thank u om much for ur help.
I apprciate it.
After a power outage, our SQL Server will allow Windows authentication but not SQL authentication. The server’s property is set to Mixed Auth mode and the registry reflects Mixed Auth … but the system administrator login does not work (or any other SQL authentications)… so the question is why? … and how can this be fixed?
hi,
We have MSSQL2000 STD ED server having 175 databases for MIS purpose, we need to migrate all these databases to MSSQL2005 STD ED, what is the best approach, besides manual backup restore?
Thanks.
Cleto.
Hi Pinal,
I was implementing the Data Auditing Feature in SQL Server 2008 and have a very Basic doubt “What does a Database Principal Name mean and how is it decided for a Server Principal Name” .
If you see a row in the Auditing Log ,It has Database Principal Name, Session Principal Name and Database Principal Name. What does these mean and what is the clear distinction between them.
Thanks Pinal in anticipation
Hey Pinal
I am waiting for your views sir!!!!!!
Hi Pinal,
Can u pls tell me how to find number of saturday and sunday in a Month.
Thnx,
Arun
This is the example code
declare @input_date datetime
set @input_date='01-Dec-2009'
select count(dateadd(day,number,@input_date)) as no_of_sat_sun from master..spt_values
where type='p' and number >=1 and number<day(dateadd(month,datediff(month,0,@input_date)+1,-1)) and
datename(weekday,dateadd(day,number,@input_date)) in ('saturday','sunday')
Pinal,
I did a few different searches but couldn’t find anything associated with my question I have. Currently we have a few SQL 2005 servers that we are going to retire. We are getting new servers that will have SQL 2008 Enterprise installed on them. My question is this. Is it better to backup on the 2005 servers and then restore on the 2008 servers, or to script all the database objects and then use SSIS to copy all the data from all databases\tables over? We’re looking at about 35 total databases with only a few having more than 2gb of data.
Any direction would be greatly appreciated.
Hi Mike,
To migrate database, backup-restore is the best option with least overhead. Copy Database task in SSIS is also good option that also not need scripting of obejcts.
Regards,
Pinal Dave
Hello Mike,
Just to add 2 cents to what have been said, I would recommend you to first run Database Upgrade Advisor before you move your database to higher version.
This comment has nothing to do with your question directly, but indirectly it is still connected to your question. In any of the method you do, either by backup restore or inplace upgrade, I doubt if you will be prompted of any upgrade / compatibility issue during migration process.
So better check the compatibility issues before you actually migrating databases.
~ IM.
I want to Implement Live Database like if Someone Use My Sql Server or Any Entry inserted or Deleted or Any transation done on My Server Same Time it Will be Mail Me isn’t it Possible then Guide me How to Configure?????
Hi kalpesh,
Which Sql Server are you using????If its Sql Server 2008 you can implement a data Auditing Feature that will log all actions by anyone and then you can create a job on the log file created to notify you by mail based on the frequency you decide.
This is just an idea of implementing it, may be someone else can propose a better idea.
Hi Kalpesh,
On a live database where many processes run that performs many update and changes. Firing mails on each n every change not seems practicle. If you explain your requirement in more details than we would be able to provide you better resolution.
Regards,
Pinal Dave
Why do you think this is needed to you?
If there are 10000 transactions per day, do you want to get as many mails?
Hey Dave,
You are Really Great,No Words to Express your Service.
and i’m just wondering if you can give me the Link where i can access to some Video Tutorials or any other Presentations so it will be Great help full to us.
As i knew many people got benefited by you.
you created lifes to many people.
Keep it up
and Heartful thanks!
I am new to SQL and trying to figure out how the “nn%19759+1″
is changing the result returned from the select statements listed below.
Will appreciate any clue that help me understand the logic behind the query returning 3231 & 12542
Regards
Jay
Queries
————–
select GETDATE()
= 2009-12-26 10:10:02.323
select DATEPART(ms, getdate())
= 323
select DATEPART(ms, getdate())*10%19759+1
= 3231
select DATEPART(ms, getdate())*100%19759+1
= 12542
Hello Jay,
In your experession, all operators are applied from left to right and % is modulo operator.
Regards,
Pinal Dave
Hi Dave,
Thanks for the explanantion, so what about the 19759, does it represent any thing special that you know of, (the number is used in reference material and code examples on Technet)
Regards
Jay
Hello Sir,
I have a situation where i need to create a batch file which will run at every 5:00 Pm and take a backup of my sql database and will keep it in my local hard disk.
Please help me with the query that is to be run on command prompt which will take the backeup from say
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
and paste it into
D:\SqlBackups folder
Thanks & Regards
Type these two lines in Notepad and save it as:
fileName.bat
ECHO This Script duplicates the Database
COPY C:\LogData.mdb C:\Dup_LogData.mdb
The first line is ignorable.
I have used Scheduled Tasks utility found in Windows. Follow this:
Start -> Programs – > Accessories -> System Tools -> Scheduled Tasks
You can schedule a task / prgoram and it will execute at whatever time you choose. Choose Daily and then at the end of the Wizard you select Advance Settings from where you can select the exact time that you want your scrtipt / batch file to be executed. This batch file will copy the database file with the named duplicate file.
Apply it in your case!
Hi Sumit,
Do one thing, just paste the following in notepad and save as a batch file….
Copy from here……………….
————————————————————————-
@echo off
Rem Taking the backup of your database
set var=%date:~10,4%-%date:~4,2%-%date:~7,2%_%time:~0,2%-%time:~3,2%
rem echo The variable is “%var%”
rem md D:\Backup\%var%\
osql -e -S Manish\SQLEXPRESS -U SA -P manishpassword -Q “Backup DATABASE Yourdatabase to DISK = ‘D:\Backup\DB%var%.bak’
————————————————————————-
till Here ……………….
Please note
1. Save the above script in a batch file and simple execute once from command prompt, you can find the backup in D:\Backup\ folder. if this works properly you can schedule it by Start -> Programs – > Accessories -> System Tools -> Scheduled Tasks
2. Manish\SQLEXPRESS is my sql server name and instance.
3. manishpassword is my password, you can use yours.
4. Yourdatabase is the name of your database.
Please let me know if you face any more issue.
Follow the post. It will take backup by appending current date value
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/backup-database.aspx
Hello Sumit,
Use the dos command MOVE at command prompt or using xp_cmdshell in sql script.
Regards,
Pinal Dave
Dear Sir,
I am actually new to sql server. I ‘ll be gratefull if you guide with me the full cmd prompt or xp_cmdshell query.
Thanks & Regards
Sumit Thapar
Hi,
Is there any way to find out
The list of tables including their create date, row count, column names and keys. I can get these info by looking at each table and its property. But I want them all on one screen.
Thanks
AbdulVahab
Hi Abdul,
you can use this query in SQL 2008, SQL 2008
select * From sys.tables
and for the previous version you can do by
select * From sys.objects where type=’u’
Thanks
Manish
Hi Dave!
I just came across your blog and found it very useful. I have created a Windows Forms Application in VB.Net (using Visual Studio Pro 2008). It my project which I have to submit for my M.Sc. IT final semester.
Well, to come to the main problem, I have created a database and a table in SQL Server 2005. I need to create a Setup of the project which must be able to set up the database on any machine along with the application itself. (The setup is running fine though on my system.)
I think I need to attach the database programatically or create it on the fly when the application is installed. I don’t know how to either task.
Can you help me with it? However, somewhere on your blog I have read an article on it and will try it. But I need a clear-cut solution which I can apply to my present problem.
One more thing, will a user need to install SQL Server Express Ed. 2005? Will it alone do (along with the .Net Framework and Windows Installer)?
Hello Dev,
SQL Server is backend for any client-server application and installation of SQL Server instance can not be included into an application setup. You will have to install the server and create database saperately.
Regards,
Pinal Dave
Dear Sir,
I have a situation where i need to give a user multiple privileges. Privileges are stored in asp List box control and every privilege has a value associated with it. admin selects multiple privilege values for a user.
Now in the database there is a table which has structure like UserId,PrivilegeId.
for a single user there can be multiple Privileges stored.
Now what i want to do is i want to send all the privileges separated by any character Value, create it into a string and then send this string as parameter to sql stored procedure. the idea was to use like the SPLIT function in asp.net that returns an array after splitting the string and then one by one insrt the valuesinto that table. i cant find any split function in sql server. nor i want to send an xml file to the database.
Please Help
Hi Pinal,
I need to use sp_addlinkedserver to link two servers for fetching data from two different databases on diff. servers.
So, that can design the report in reporting services.
Could you please throw some light on the syntax and arguments as well.
Thanks.
hi madhuri,
–sp_addlinkedserver ‘OrcleBaan’, ‘Oracle’, ‘MSDAORA’, ‘BAAN’
–OracleBaan is linkedServername
–Oracle is ProductName
–MSDAORA is Providername
–BAAN is SQL*Net
–sp_addlinkedsrvlogin ‘OrcleBaan’, false, null, ‘baandb’, ‘baandb’
–OracleBaan is LinkedServerName
–baandb userid
–baandb Password
sql for fetching data
SELECT * from openquery([ashish\sql2005],
‘SELECT [eno]
,[ename]
FROM [Replication].[dbo].[emp]‘)
—shish\sql2005 is a logical name linked server .
Arun,
Many thanks for your reply.
I am using MS SQL, so which syntac will help me to pull the arguments required :
@srvproduct = ] ‘product_name’ ]
[ , [ @provider = ] ‘provider_name’ ]
[ , [ @datasrc = ] ‘data_source’ ]
[ , [ @location = ] ‘location’ ]
[ , [ @provstr = ] ‘provider_string’ ]
[ , [ @catalog = ] ‘catalog’ ].
I have the server name its “psswarehouse”, but how to get the other details ?
Madhuri,
Try this,
Use master
EXEC master.dbo.sp_addlinkedserver @server = N’LinkARUN’,
@srvproduct=N”, @provider=N’SQLOLEDB’,
@datasrc=N’Arun’, @catalog=N’tabreed1′
datasrc is the name of sql server,catalog is the name of data base .
dont forget to add
sp_addlinkedsrvlogin @rmtsrvname =
, @useself =
, @locallogin =
, @rmtuser =
, @rmtpassword =
u can do this by sql server management after adding link server
Arun
Madhuri,
Use master
EXEC master.dbo.sp_addlinkedserver @server = N’LinkARUN’,
@srvproduct=N”, @provider=N’SQLOLEDB’,
@datasrc=N’Arun’, @catalog=N’tabreed1′
@datasrc = N’psswarehouse’
@catalog = Name of database which u want to connect.
dont forget to sp_addlinkedsrvlogin after connecting.
U can do it from Sql Server management
Arun
I have database A with multiple tables, and I want to copy the content of Table T1 into Table T2 in Database B. There are million records. From T2 to Table T3 on Database C there is transaction replication.
So its T1-T2 – Insert into select * ……… 4 hours
T2 – T3 – Transaction replicaiton. – 4 hours..
What is the best option to reduce the time…
Thanks in advance for your help.
Hi Reddy,
I guess taking backup of table T1 from database A and restoring on database B will save your time.
Thanks
Manish
Hi,
I am trying to set up a network Access DB on a mapped drive as a linked server in SQL 2008 and am getting the ‘Microsoft.Jet.OLEDB.4.0 for linked server…… is not a valid path’ message when I perform a ‘Test Connection’.
Steps I have taken – set the path variable on the SQL Server to C:\Temp and set security to allow everyone full access, set security on the access database and containing folders (at the share level) to everyone with full access. Set the SQL Server service to run using my login (full network admin). Set SQL Server to use SQL Server & Windows authentication. Made a copy of the access db in C:\ on the SQL Server and set up a test linked server to this mdb.
When I use SSIS on the SQL Server I can access both the local mdb on C: and the network version. This is logged on to the server using my own login and logging in to SSIS using Windows authentication.
When I try this on my workstation using SSIS I can access the version on the SQL Server C: drive but not the network version.
I need help with any steps I might be missing or what else I could try.
Thanks in advance for any help.
Regards
Jim
SELECT hirer.file_no,hirer.hirer_name,Starting,Closing,Mode,hirer.instalment as Due
FROM hirer inner join employee on hirer.emp_code=employee.emp_code
where(mode=’m'and
day(starting)in(select day(demand) from dbo.demand_range(’12/12/2007′,’12/20/2009′)))
here i have used a function demand_range which only gives all dates between two dates
day(starting)in(select day(demand) from
dbo.demand_range(’12/12/2007′,’12/20/2009′)))
the above compares if the day of starting date is the day of date generated by the function
Now i want to select that date where the ist day matches with the date generated by function
Please help me itz urgent
@feroz
It might be easier to change the statement to use BETWEEN:
WHERE starting BETWEEN ’12/12/2007′ AND ’12/20/2007′
Though, this is different than what you have because it also checks month and year. To only check the day:
WHERE DAY(starting) BETWEEN 12 AND 20
From there it should be easier to match the new request.
Dear Sir,
I want to export data from SQLSERVER 2005 to Excel at some destination point as below, but when I pass parameter in @query variable, it is not working and showing message near to this line “where region=”’+ @region+”
Pls solve this thing
delcare @region char(5)
select @region=’abcd’
EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance.xls’,
@query = ‘select *
from cust_tmp
where region=”’+@region+”’ /*problematic part as */
order by region,cust_name’
end
@NikhilChaturvedi
After @region put the single-quote before the double-quote.
create table cust_tmp
( cust_no char(5),
cust_name varchar(50),
region char(5)
)
insert into cust_tmp values(‘c1′,’abcd’,'abcd’)
insert into cust_tmp values(‘c2′,’abcd’,'abcd’)
select *
from cust_tmp
declare @region char(5)
select @region=’abcd’
EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance1.xls’,
@query = ‘select *
from cust_tmp
where region=”’+@region+”’/*problematic part */
order by region,cust_name’
–It Shows error message “Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ‘+’.
/* If I used the following scripts it Works, I used variable @a and assign select statement into @a and after that
I put it in front of @query. It work . but when I write select statement into @query. it doesn’t work */
declare @region char(5),
@a varchar(200)
select @region=’abcd’
select @a=’select *
from cust_tmp
where region=”’+@region+”’/*problematic part */
order by region,cust_name’
select @a
EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance1.xls’,
@query = @a
–It works
–Pls solve this issue.
Sir,
How can I compare uniqueidentifier in where clause of select statement.
I am using this select statement in sp so I get an error.
select Name From Area where ID = tablename.uniqueid
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier “tablename.uniqueid” could not be bound.
Thank you.
@Hello
Are you comparing two different TABLEs? To do that, the other TABLE must be joined, that is, added in the FROM clause.
Dear Sir
I have client application that use Sql Server as Database and use C# for Interface.
my problem is that I dont want to see any person my database data and i want to install it on the one client and no have server.
in Sql server Enterprise and developer edition has TDE or Encryption database but Express edition no have.
my qusetion is :
1- can i create encryption database in my computer and use it in clien in express edition (in client just have SELECT and no INSERT no UPDATE no DELETE) ???
2- can i use developer edition in client in my package as database ?????
my perpose just no want can any one see my data
Hello Ali,
TDE is to encrypt backup only, not to encrypt data in online database.
To encrypt the data you will have to incorporate the code for encrypt (while storing) and decrypt (while retrieving) in C# application.
The best method is through controlled authentication and authorization. Allow access only to valid users and grant permission only for limited objects and activities.
Regards,
Pinal Dave
hi
i want to retrieve the last 20 rows from the result set
i can not use top 20 order by desc
order by has to be asc.
my query
$sql2 = “select top 20 employeeid,employeename from hr_employee where isthere = 1 and dateofleaving >= ‘”.$month.’/01/’.$year.”‘ and dateofjoining <=
'".$month.'/'.daysinmonth1($month,$year).'/'.$year."'".$department." and Employeeid < ".$prevempid." order by employeeid asc";
Hi Harish,
First get the top 20 records order by desc and then reorder the retreived resultset of 20 records in ascending order.
Regards,
Pinal Dave
thanks
for u r help
regards
Harish
Hi Pinal!
I’m dba junior and i have problems when i want restrict permissions and access to our production dabatabases.
This restriction will be to the developers, because then spoil the server configuration.
I created a rol where i give to them only view definition and execute, but i have many problems when the want make some changes to the aplication’s sp.
Plz help me with the restrict the permissions to the developers.
I’ll be very thankful.
Pd. SOrry wheter my english is bad.
Saludos!
@Jim
Please explain what issue you having with SPs. The only thing i think of just now is the EXECUTE AS clause, which is explained in the help file.
Hi Brian, the issue is how control to the user (specifically developer), i can give to them execute and view definition, in each sp, view table, funtion; for each user and object in the database (With the Datatipe user access problem ).
I want avoid this using a group.
I do it this process making a rol, and i granted permissions(exec and view def.) for each object through a script (Type ) to this rol, all this under a schema created for this rol, later i added to the developers to this rol.
I want know wheter this metod than i created is correctly, or if exist a correct metod, because the objects and the users quantity in the database is very highly, because i has and have problem with my metod.
Regards
Jim
Giving rights to a ROLE and putting developers in that ROLE, is a good method. It is probably the best way to do it.
Have you had any issues?
@Jim,
Roles with Higher permissions will over ride lower permissions.
What I would suggest is the following
1. See if the users attached to this new role are also member of any other role that has high privileges, if this is the case then you need to review this again.
2. Make use of impersonation account,
Execute as login = ‘developer_login_name’ and see what privileges does that user have.
3. check permissions for the users,
sp_helprotect @username = ‘name of the user’
~ IM.
Please let me know how we can partion on day basis. And delete partition on daily basis.
1) We have one current old data base which has 59 un-partitioned Tables with 300 days of data.
Now we need write a script to make a new Database with same table definition but Portioned on txn_date(one partition a day) and load the data from current DB.
We have txn_date field as date field in all tables.
We are starting from txn_date from 01 Aug 2009 to create partitions on each day and name the partition as partition_MMDDYY to till today.
So that we will have almost 180 partitions by the end of January 2010.
2) After that we need to create 4 weeks of empty partitions i.e. 28 partitions from February 1st 2010 onwards.
Same name standard: partition_MMDDYY.
3) After one week we need to delete and drop all the partitions that are older than 180 days. (partion_MMDDYY) and REUSE the table space
And we need to create one week of daily partitions.
Thanks
Hi Ramesh,
You can partition your table on daily basis. For that you need to create a partition function and a parition scheme. This partition function can be used for your all tables.
To automatically create one new parition and delete the oldedst parition on daily basis, you need to implement the Sliding Window Partitioning that is explained with example here:
http://msdn.microsoft.com/en-us/library/aa964122(SQL.90).aspx
But one table can be partitioned on the basis of one partition scheme. So you need to decide one partitioning period either daily or weekly.
Regards,
Pinal Dave
Hi Pinal
you say :
Hello Ali,
TDE is to encrypt backup only, not to encrypt data in online database.
To encrypt the data you will have to incorporate the code for encrypt (while storing) and decrypt (while retrieving) in C# application.
The best method is through controlled authentication and authorization. Allow access only to valid users and grant permission only for limited objects and activities.
Regards,
Pinal Dave
//———————————-
First Thank you
//———————————–
but I after sales my application (in package with sql server)
i dont have any control over sql server
because sql server instal in destination Computer (not on network)
and i remember that sql server has sa User and any one can create user with all facilities and can use this user to modify or see my database !!!
//———————————-
every application sales to one person and not support network and just instal in one pc or computer i want that no body can use my database
can i do that???
//————————-
thank mr Pinal
Hi Pinal,
In SQL server error log only spid’s with datetime and event occured are maintain as history. How TO get loginname of that spid’s so dba can know which login user has done that event.
Also, If Deadlock occur in night and if DBA was not present at that time, How can DBA know that Deadlock as occured and from which table/stored procedure/query ?
Thanks,
Parikshit
Helo Parkshit,
More details are recorded by default trace. The path of log files created by default trace is “C:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Log\”
To get the details of all deadlocks start the trace 1204 and 3605. After switching on these traces complete details of all deadlock will be recorded in notepad file.
Regards,
Pinal Dave
Hello Sir,
I got this error when i m creaing sdatabase on my laptop.
i m using vindow vista on my laptop …
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database ‘master’.
Plz help me short out this problem…
Thanks,
Ashwani
Hello Sir,
I got this error when i m creating database on my sql server 2005.
i m using Window’s vista ultimate.
i dont have any service pack, is that required ??
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database ‘master’.
Plz help me short out this problem…
Thanks,
Ashwani
Hi Pinal,
In many books (even in your SQL Server interview question guide) , I have seen that it is mentioned
“Delete is DML Command and can be rolled back. Truncate is DDL command and cannot be rolled back”.
I know that for Oracle, the statement holds true but i feel that this is not case with MS Sql server 2000 or higher versions.
My observation with MS Sql Server is:
In SQL Server, by default AutoCommit is On.
So without explicit transaction neither delete can be rolled back nor Truncate command.
Within a transaction both Delete and Truncate can be rolled back.
So where is the difference between the two commands?
Request you to correct me if my understanding is wrong.
Thanks in advance,
Pramod
Hi Pramod,
You are correct that:
without explicit transaction neither delete can be rolled back nor Truncate command.
Within a transaction both Delete and Truncate can be rolled back.
The difference are in functionality like:
we can use WHERE clause with delete but not with truncate.
Truncate reset the identity but Delete not.
Truncate does not fire the trigger (on delete event) but Delete does.
Truncate can be used if table is referenced by other foreign key tables but Delete can be used.
Regards,
Pinal Dave
Hi Pinal,
I agree with all other.
The only reason for asking the question “Delete is DML Command and can be rolled back. Truncate is DDL command and cannot be rolled back” is that I have practically seen that this is not the case with SQL Server.
But when an expert (yourself) is saying that, it made me think that I am missing some information and i should post a question and enhance my knowledge.
Thanks,
Pramod
Truncate can be rollback if used within a transaction. But many people still that it is not rollbackable
Dear Pinal!
We need to maintain history of changes made in data and that too applying some business logic and it is module specific also, At present this is done by comparing before and after values (two rows) in while loop. Please tell if we can use databse logs or is there any way to read those log files and generate a readable report like
Or suggest if there is any other way to acive above kind of report without mannualy comapring tow rows (before and after),Thanks in advance.
Best Regards
sach
Report is as follows
FieldName : OldValue : NewValue : ModifiedDate: ModifiedBy
Hi Sach,
For such customozed auditing trigger is best. In SQL Server 2008 a new feature CDC can b used to audit the changes.
Regards,
Pinal Dave
Thanks a lot Pinal.
Hello Sach,
Just to add 2 more cents to what SQL Genuis (Pinal) said, I would suggest you to look at slowly changing dimensions tasks available in SQL Server 2005 SSIS (BI Tool).
I do not know, what your requirement is, if it is a feed, or real time update, if it is a feed, then I would definitely recommend using Slowly Changing dimension tasks, its very handy tasks, easy to configure it and it gives what you want.
I have only used it for testing, I do not know, if it comes with any problems.
~ IM.
Dear sir:
I have a query Plz help me to go tru with tis query..
I have a major as table name. tat table having QualificationsIDs fields, tis QualificationsIDs column contains many ids such as 12,20,48,23,
I need to reterivew these table by selected categoriIDs
eg:
If im selecting 20
Rows having 20 as Qualification id should display.
What should i do for this..
help me to solve tis problem
Thanks & Regards
Rajesh.k
@Rajesh
Do you mean the one field has many ids in it? Is each id enclosed in two commas?
FROM major WHERE QualificationsIDs LIKE ‘%,20,%’
If not everyone is enclosed:
FROM major WHERE
QualificationsIDs LIKE ’20′
OR QualificationsIDs LIKE ’20,%’
OR QualificationsIDs LIKE ‘%,20′
OR QualificationsIDs LIKE ‘%,20,%’
Hi Pinal,
I found this article and asked a question, but no reply yet. May be the discussion is old now.
http://blog.sqlauthority.com/2008/02/12/sql-server-get-current-database-name/#comment-59495
Anyway: Vishnu replied to one question:
Milan you can use current databse name in a script..
try like this..
declare @DBName varchar(50)
set @DBNAme=(select db_name())
use @DBName
go
Then on January 9, 2010 at 2:48 am I replied and asked the question below:
“I have the same problem, I wanted to use current database name in a variable and then use that variable in USE command. I tried your commands but it didn’t work, I got an error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘@DBName’.
What would be the reason, or is there any other way to do that.
I want to run a script in all the databases in a server. ”
I hope you will be able to reply with a solution.
Thanks a lot and appreciated your work.
Shoaib,
DBA from Canada.
@Shoaib.
How is Canada ( Freezing ????)
Answering your question, one way of doing this is to use dynamic sql…
declare @DBName varchar(50)
set @DBNAme=(select db_name())
– add these lines
declare @sqlcmd1 nvarchar(4000)
set @sqlcmd1 = ‘use ‘+@DBName
Exec (@sqlcmd1)
– continue your script
~ IM.
Hi Imran,
We can not use “USE ” with dynamic query.
It will not execute and we remain at same database as we get same DB by “DB_NAME()”
USE statement will not work with dynamic query.
Try to give to use different database from the same server.
Thanks,
Tejas
SQLYoga.com
Hello Tejas,
“Use” can be used with dynamic SQL. All you have to do is just append your script after “Use” and database name as below,
use master
declare @DBName varchar(50)
set @DBNAme= ‘msdb’
declare @sqlcmd1 nvarchar(4000)
set @sqlcmd1 = ‘use ‘+@DBName +’ select Db_name()’
–print @sqlcmd1
Exec (@sqlcmd1)
~IM.
Hi Imran,
That is true, then why don’t we use just
DatabaseName.Schema.Tablename in dynamic SQL.
What I mean is: after executing USE statement with dynamic SQL, Query analyzer will still stays in master database.
So if I execute Dynamic SQL with USE statement and then in next statement I try to exeute from that database, it would not work.
So, we can execute it by USE statement, but I think it is not of any use.
Please suggest your ideas.
Thanks,
Tejas
Hi Imran,
Yes it is freezing, last week we had -20 C and lots of snow.
Thanks for your reply, however Tejas is right, it didn’t work, I used even in a cursor. Eventhough the value changed for every database (database name is being printed) but it doesn’t change the value of DB_name or current database name, i mean my script return same value for all databases on the server. If I run the script against all database individually then it gives different result for each DB.
Pinal suggested to use SP_msforeachdb, but the problem is same, i have a big script and it is returning same result.
Thanks again for your time guys.
Shoaib
I like your blog a lot. I have gone through lot of your articles. Your examples are really simple and superb.
I have one request. I could not find any queries anywhere with tables and data. I either see just solutions but the want questions for it so that i can do it by myself.
To practice more queries i need a list of queries and their solutions. Could you help me in this?. I want to practice more and more but all i see is one simple example for each of the queries.
Plz it would be a great help for many of new starters and will be a revision for many experienced.
I like your blog very much. I just like your degrees. If I will have any query regarding SQL, I will definitely contact you.
Thank You…
Hi,
Can you tell me how to fine the store procedure execution history. means i need to know which user has executed the storeprocedure and wht time it has been executed with some more information. Can it be possible to get the details.
Regards
Ahmed
hi Tejas shah And Imran,
we can use The “use’ clause in Dyanamic sql.
Try this,
use Database
–select Name from sys.databases
–select * from sys.database_files
Declare
@databaseName varchar(50),
@sqlqry nvarchar(max) ,
@sqlqry1 nvarchar(max)
Begin
Create table #temp( Db varchar(250) , Name nvarchar(225) ,size int,FileType int)
set @sqlqry = ”
Set @sqlqry1 = ”
Declare c Cursor for select Name from sys.databases
open c
Fetch Next from c into @databaseName
While @@fetch_status =0
Begin
Set @sqlqry = ‘ Use ‘ + @databaseName + ‘ ‘
Set @sqlqry1 = ‘ Insert into #temp (Db, name,size,FileType)
select ”’ + @databaseName + ”’ Db ,name,size,Type from sys.database_files’
Print @sqlqry1
Set @sqlqry = @sqlqry + @sqlqry1
Print @sqlqry
Exec sp_executesql @sqlqry
Fetch Next from c into @databaseName
End
close c
Deallocate c
End
select * from #temp
Drop table #temp
Hi Arun,
Thanks a lot for your response and time.
It worked, I had to modify your script though. Because there were some single quotes and double quotes so I had to change. But it worked.
I really appreciated all of you guys:
Imran, Arun, Tejas and best of all Pinal.
Take care.
Regards.
Shoaib
DBA from Canada
Hi, my name is Ruben, im a computer engineer, from dec. of 2008, and i like to be part of your blog, recently im working a lot with sql server 2000 and sql server 2005 and im learning by myself microsoft visual studio 2008, i hope you like to let me join of your team, by the way im from baja california, mexico. see you later. thanks for the attention.
Hello All,
I am working on a project where we are migrating from SQL Server 2005 on Win2k3 To SQL Server 2008 on Win2k8.
We are noticing significant increase in latency/ response time from the databases in SQL Server 2008.
We have not changed the code at all.
We have confirmed that we do not have any fragmentation
General SQL Performance counters between 2k5 and 2k8 seem to be comparable.
We ensured we used the same servers/hardware for the tests and hence have eliminated any hardware or network issues.
Tests involve calling into SQL Server from C# using simple ADO.Net connections.
Has anybody seen these kind of performance issues and do you have any suggestions or fixes for them?
Thanks
Hi pinal,
How can i get the number of data pages used by table
Thanks
Hello Zafar,
Use the SP_SPACEUSED [schema.table] system stored procedure to know space used by a table. Here you get size in KB. To know the page number divide this value by 8 as size of a page is 8 kb.
Regards,
Pinal Dave
Pinal Thanks for replying
i have an image of size 3801K, when i save the image, it gives me the size 3904 (which i get using the SP_SPACEUSED [schema.table].
that means that 3904/8 = 488 pages
but the actual size of the image is 3801 and size returned from table using SP_SPACEUSED is 3904 (which means 96 bytes overhead is also there)
Total overhead = 488 * 96 bytes = 46848 bytes / 1024 = 46KB total overhead by 488 pages
which 3904 (actual size returned using SP_SPACEUSED) – 46KB (overhead of every page) = 3858KB
less the actual size of the image was 3801 , that gives me 57KB.
Now my question is
1. Why the (size returned+overhead of the page) is not equal to size of the image
2. Where the remaining 57KB has been used
3. If overhead is already included in every page (in my case 488 * 96 bytes) then the difference is of 103KB
Regards
Zafar Iqbal
I have a query
begin try
insert into table1
select * from table2
End try
Begin catch
End catch
The above insert query throwing an error of duplicate key voilation.
can i Log the record details on which the error occured
ie like on 50th record from table there is a voilation.?
insert into logs(columns)
select columns from table1 as t1
where exists(select * from table2 as t2 where t1.pkcol=t2.pkcol)
Hi Pinal,
We have a database with tables, foreign keys and indexes.
I need to develop 3 scripts which generates the create scripts for foreign keys, Non Clustered indexes and tables (with clustered index definition) separately when run on the existing database.
The DML scripts can be generated with GUI by SSMS but we don’t want to go by that.
Please let me know how this can be done, possibly an example. Thanks for the help.
Rahul
Hi Pinal dave, im a begginer in sql server, im migrating an access 97 data base to a sql server 2000, and some queries have the access functions FIRST(), PIVOT, TRANSFORM, OWNERACCESS, those access functions gives me a lot of problems, i dont know how to do the same functions in sql server 2000, thanks for your time, and your help will be very apreciated, regards.
There are no equivalents in version 2000
Post the code
May be some people help you in convertion
Hi Pinal,
In my company, We are developing one interface engine which will store data from HL7 messaging in Oracle or SQL Server 2005 DB (Depending upon the configuration by user).
I was assigned job for handling the SQL Server side. I feel that in SQL Server 2008, we have array handling mechanism directly but not in SQL Server 2005.
I want to handle the multi dimensional array objects received from messaging using CLR (Creating assembly in C# and registering in SQL).
Some sites says that usage of CLR should be avoided as it hurts performance and some site says that it don’t have performance issue.
I request you to please give your opinion on the usage of CLR for handling Multi-dimensional array.
Thanks,
Pramod
Hi Pinal,
Request you to please tell me whether CLR usage handling multi-dimensional array is acceptable or it really have any performance drawback.
Thanks,
Pramod
Hi Pinal,
I am a devloper in a software company we are using sql server 2005 and we devlopers have client of sql server is installed
is there any method to restore database without using wizard can we restore database .
Backup files and ldf and mdf file’s are also on server.
Hello Rahul,
In SQL Server, almost all work that we can do using SSMS can also do using t-sql script.
To restore backup we have RESTORE DATABASE command.
Regards,
Pinal Dave
Dear Pinal,
First, I like to thank you for this wonderful website!!!!! The tips and code segments helped me very often. As i am new to sql server database, and developing database driven website. So to make things fast i am doing work at home and office, but as i try to take database to my office PC it first gave me a lot of trouble, some errors related to “PIPE” and the “debugger” related errors croup up… any how i managed to do it. Now i am in a fix.. how to manage on daily basis the synchronization of database at home PC and Office PC… right now i am managing by remembering the changes done and just duplicating those changes at other location PC…but this is time consuming and now a good practice… Please Help.
hey pinal
i am a software developer in a company, i have just joined the company as a fresher. I have to make a search engine page for a table so that i can use it to find out any word .
so can you please explain me to make the processure for that?
Thank you
hey Pinal
Can you please help to create the search engine page to find out the rows for a particular word in the table in sql 2008
Start with applying like operator
where col like ‘%serach value%’
hi pinal,
i have a problem with calling a user defined function which has been defined in ‘user’ database from ‘masters’ database. i am using SQL 2000 server.can u suggest any way to call????
thanks in advance
Hello asu,
What error are you getting? It could be access permission or other syntactical issue. If you can share the details of error we could help you better.
Regards,
Pinal Dave
You need to use
user.dbo.function_name
Pinal You are right but when I tried with Restore command it prompts error when i gives backup file path (network path) which is located on server.
Thanks
Rahul
What was the erro you got?
Hi Pinal,
We have a database with tables, foreign keys and indexes.
I need to develop 3 scripts which generates the create scripts for foreign keys, Non Clustered indexes and tables (with clustered index definition) separately when run on the existing database.
The DML scripts can be generated with GUI by SSMS but we don’t want to go by that.
Please let me know how this can be done, possibly an example. Thanks for the help.
Rahul
Hello
I have a question
how can we create foreign key relationship across the databses without replicating the table.
thank you
It is not possible. You may need to do it via your application or force it via a trigger
Hi all,
am getting mail alerts only after I mutually run datamail.exe in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
not automatimatic.
OS – windows Server2003
Environment – sql 2005
SMTP status= enabled
kinldy give the solution regaring this issue
hi Pinal, a few days i posted a question about some access functions (FIRST(), PIVOT, TRANSFORM, OWNERACCESS) but i need to do the same things in SQL SERVER 2000, i dont know if you can help me with that or something, i appreciate your time, by the way, excelent blog. regards.
What i want to do is that there is table which has age and zip code. I am writing a code that takes four records through loop then copy them to temporary table (calculate average over there of the four records ) then store that avg value in a variable. After that want to replicate the avg value in the original table. I have done all the above steps only have problem in replicating the avg value only against the four record only that were first selected and for whom average was calculated.
DECLARE @count3 INT
SET @count3 = 0
WHILE (@count3 <@count2 )
BEGIN
INSERT INTO #MyTempTable VALUES (1)
SET @count3 = (@count3 + 1)
END
CREATE TABLE #MyTempTable (cola INT)
Select * from #MyTempTable
Delete #MyTempTable
Create table majeedwaheed(agee int, zipee int)
DECLARE cursor_first CURSOR FOR
SELECT Zip_code , age FROM Rt_md_rd_II
OPEN cursor_first
Declare @count5 INT
Set @count5 = 0
Declare @v_zip_code INT
Declare @v_age INT
Fetch NEXT from cursor_first into @v_zip_code,@v_age
While (@count5 < 4)
BEGIN
Select @v_zip_code as zip_code,@v_age as age insert into majeedwaheed values(@v_zip_code, @v_age)
Set @count5 = (@count5 + 1)
Fetch NEXT from cursor_first into @v_zip_code,@v_age
END
Declare @avg_age int
set @avg_age = (select avg(zipee) from majeedwaheed)
select @avg_age
Declare @count9 INT
Set @count9 = 4
While (@count9 < = 0)
begin
update Rt_md_rd_II set zip_code = @avg_age
set @count5 = (@count9 – 1)
END
the output of my code should be:
original table
Zip code Age
1305 21
1306 22
1309 23
1400 30
Output table
Zip code Age
1330 24
1330 24
1330 24
1300 24
What is the logic behid your expected result?
SELECT SERVERPROPERTY(‘BuildClrVersion’) BuildClrVersion
,SERVERPROPERTY(‘Collation’) Collation
,SERVERPROPERTY(‘CollationID’) CollationID
,SERVERPROPERTY(‘ComparisonStyle’) ComparisonStyle
,SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) ComputerNamePhysicalNetBIOS
,SERVERPROPERTY(‘Edition’) Edition
,SERVERPROPERTY(‘EditionID’) EditionID
,SERVERPROPERTY(‘EngineEdition’) EngineEdition
,SERVERPROPERTY(‘InstanceName’) InstanceName
,SERVERPROPERTY(‘IsClustered’) IsClustered
,SERVERPROPERTY(‘IsFullTextInstalled’) IsFullTextInstalled
,SERVERPROPERTY(‘IsIntegratedSecurityOnly’) IsIntegratedSecurityOnly
,SERVERPROPERTY(‘IsSingleUser’) IsSingleUser
,SERVERPROPERTY(‘LCID’) LCID
,SERVERPROPERTY(‘LicenseType’) LicenseType
,SERVERPROPERTY(‘MachineName’) MachineName
,SERVERPROPERTY(‘NumLicenses’) NumLicenses
,SERVERPROPERTY(‘ProcessID’) ProcessID
,SERVERPROPERTY(‘ProductVersion’) ProductVersion
,SERVERPROPERTY(‘ProductLevel’) ProductLevel
,SERVERPROPERTY(‘ResourceLastUpdateDateTime’) ResourceLastUpdateDateTime
,SERVERPROPERTY(‘ResourceVersion’) ResourceVersion
,SERVERPROPERTY(‘ServerName’) ServerName
,SERVERPROPERTY(‘SqlCharSet’) SqlCharSet
,SERVERPROPERTY(‘SqlCharSetName’) SqlCharSetName
,SERVERPROPERTY(‘SqlSortOrder’) SqlSortOrder
,SERVERPROPERTY(‘SqlSortOrderName’) SqlSortOrderName;
Hi Penal,
I’m a beginner in SQL Server, could u please guide me to some good hands on lab exercises to practise SQL/T-SQL on Adventureworks db??
Your help is appreciated :)
Best Regards,
Amit
Dear Pinal,
I am a junior DBA and have been following your blogs for last 2 years.I find it really useful for any newbie.I was supposed to attend your BI session in Pune but I got transferred and missed this oppurtunity:-(
I have a question which I am not sure how to ask.Will gve it a try:-
In one of my projects we had to migrate from sql 2000 to sql 2008.Both were enterprise edition.My senior DBA did not run the upgrade wizard.He just installed SQL 2008,detached sql 2000 .mdf and .ldf files and just attached them to sql 2008.I have a gut feeling that this was one of the smart ways to do it but it might not be able to utilize the sql 2008 performance gains properly.Can you please throw some light on this.
Hello Chandan,
Database will start using new features when the Compatibility level changes to 100.
Before convertign the compatibility level you should run the upgrade advisor to find out any possible issue.
Regards,
Pinal Dave
Thanks Pinal.I assume that you too stress on upgrading a server rather copying files manually to a higher server then using Attch/Detach
Also see if any code breaks due to behaviral changes
Hi,
I am working on a Restaurant Web Site with Online Ordering. Now I am making an admin console to manage and print orders. and for that i need differential data. So I only need recent orders not all orders. so can you suggest me something on that?
You need to define the term recent
Generally
select t1.* from table1 as t2 inner join
(
select pkcol, max(date_col) as date_col from table group by pkcol
) as t2
on t1.pkcol=t2.pkcol and t1.date_col=t2.date_col
Hi All
I want to update a table having the same name as day part of a particular column for example i want to store the value to 5\12\2009 in days5 and 15\12\2009 in days15 etc
update #wrk set #wrk.days+cast(day(Col_name)))=colum_name
from #wrk inner join #tem2 on #wrk.file_no= #tem2.file_no
where @c=day(Col_name)and #wrk.file_no=#tem2.file_no
i get error in above
Hello Firoz,
write query to create a dynamic statement and then execute the dynamic statement. First create statement in a varchar variable as below:
declare @strSQL varchar(2000)
set @strSQL = ‘update wrk set wrk.days’+cast(day(Col_name)) + ‘=colum_name
from wrk inner join tem2 on wrk.file_no= tem2.file_no
where @c=day’ + (Col_name) + ‘and wrk.file_no=tem2.file_no’
Make sure that you use permanent tables instead of temporary tables when using dynamic statement.
Regards,
Pinal Dave
is there any alternative using temporary tables in order to get the desired result
to store the value to 5\12\2009 in days5 and 15\12\2009 in days15 etc
Actually i have to convert column value to column name
having 2 temporary tables
file_no demand
1 12/1/2009
1 12/2/2009
1 12/3/2009
.
.
1 12/31/2009
2 12/1/2009
2 12/15/2009
2 12/30/2009
etc
have to show report like
file_no day1 day2 day3…….day31
1 100 0 500 200
display report in above format
You need to make use of the PIVOT operator
If you use version 2005 or above, refer the method
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
i am Not explained How it useful to the people.Because all of the Guys Knows About This One.
It really Helpful me to alottttt
i have Problem In My Professional .I need regularly works on Pdf Files To Sql tables? Please give me the Solution for this Problem?
Probalbly you may need to convert pdf to excel and load excel data to SQL Server table
Hi Pinal,
I have query regarding database performance.
1.
As i have sp and p file in oracle what are the parameter file in SQL where i can set the maximum num of session and cursors or some other parameter.
2.
What are the basic parameter i hav to set for a high end database.(200 gb)
3.
I hav a Database Server of 32 gb ram and quad core processor and my cpu utilization is 100%where could be the problem.As the system is utilizing only 17 gb and cpu utilization is 100%.
4.What are the key parameters related to performance.
Dear Pinal,
I want to un-subscribe from this bolg, whats the procedure for that.
I am using sql studio express and am trying to have the primary key of table 1 automatically be inserted into table 2 and table 3 where it is declared as foreign keys.
any suggestions please?
You can make use a AFTER INSERT trigger defined on the parent table
hello Tejas ,
how can it possible to get the pivot result without using pivot? pls. tell me.
Chirag
Hi Chirag,
We can get PIVOT result without using PIVOT keyword by making CASE WHEN statements.
Before SQL 2005, we need to get results by that way only.
Thanks,
Tejas
Hello Pranav,
You will have to write extra code to add records automatically in foreign key table.
Delete and update can be performed automatically by using the ON DELETE and ON UPDATE cascade option while writing the foreign key constraints.
Regards,
Pinal Dave
Hi Pinal,
Thank you for publishing this website, it has been very helpful to me in many occasions. My question is, what would be the best way to insert rows to Teradata via a linked server in SQL. I use the following syntax and it takes approx. 30minutes to do an insert of less than 15K records. Thank you.
INSERT INTO OPENQUERY(, ‘SELECT * FROM LinkedServerTableName’) SELECT * FROM
Hi
I need some help, I need to create a Foreign key constraint that references to a table in a external database which resides in the same server, I’m using the next sintax:
ALTER TABLE WorkOrderTable
ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ElearnerPID)REFERENCES TECTA_BAK.dbo.Person(PersonID)
GO
SQL returns the next message:
Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key ‘TECTA_BAK.dbo.Person’.
Could any one help me regarding this
Thank you
As error says, cross-database reference is not allowed. You need to do it vai your application
Pinal,
my req is i need to connect to other database which is on other computer(Which is Created Locally on his Machine) but within same network.
ENvi: Sql Server 2008.
i Apreciate your Help.
Hello Sudarshan,
You can access database from another server by creating a linkedserver and then using 4 part naming.
You can also access using OPENQUERY and OPENDATASOURCE functions.
Regards,
Pinal Dave
In SQL Server 2005, Is there a way to configure the path of a .ndf file/
Thanks in advance
@ Kishore~~ Can you please explain in detail as to what do you mean by configuring the path?
If you wish to change the location of files,you can use alter database commands(see books online) or you can use detach and attach method after you change the location.But I prefer the first method
Hello Kishore,
If you can stop the database for once than use the detach and attach method. You can also use backup restore method.
Otherwise
Using ALTER DATABASE add a new file
Using DBCC SHRINKFILE empty the previous file
Using ALTER DATABASE remove the previous file.
Regards,
Pinal Dave
Hi,
Let me begin by saying that I’m a total newb.
I’m trying to extract various columns from a fixed width text file, however since the file exceeds 65000+ rows I can not simply accomplish my task using MS Excel 2003.
I’d like to use SQL to accomplish my task but I’m not certain how I can code a statement to only pull say column 2-5, 13-25, etc.. into discrete fields.
I don’t have a CS or programming background, I’m just scouring the web to look for tutorials or articles to help accomplis my goal.
Thank you.
Dear Pinal Sir,
I have a problem in following queries :-
create table cust_tmp
( cust_no char(5),
cust_name varchar(50),
region char(5)
)
insert into cust_tmp values(‘c1′,’abcd’,’abcd’)
insert into cust_tmp values(‘c2′,’abcd’,’abcd’)
select *
from cust_tmp
declare @region char(5)
select @region=’abcd’
EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance1.xls’,
@query = ’select *
from cust_tmp
where region=”’+@region+”’/*problematic part */
order by region,cust_name’
–It Shows error message “Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ‘+’.
/* If I used the following scripts it Works, I used variable @a and assign select statement into @a and after that
I put it in front of @query. It work . but when I write select statement into @query. it doesn’t work */
declare @region char(5),
@a varchar(200)
select @region=’abcd’
select @a=’select *
from cust_tmp
where region=”’+@region+”’/*problematic part */
order by region,cust_name’
select @a
EXECUTE sp_makewebtask @outputfile = ‘\\devdb\ramcoerpdocuments\reports\AR_CustomerTrialBalance1.xls’,
@query = @a
–It works
–Pls solve this issue.
Hello Jitesh,
Use the SUBSTRING function. The systax is as below:
SUBSTRING(‘yourstring’,startPosition, length)
Regards,
Pinal Dave
Hi Pinal,
I have a issue while running a query in a view. This query is failing because the tempdb is growing and occupying the entire memory on the drive and resulting in query to fail due to insufficient space. But I can’t shrink the db when my query is running.Can we shirkin the tempdb on the fly.Please advise me on this issue.
Regards,
Venkat
@Venkat~ Tempdb is used as a temporary workspace.I would suggest you to keep the tempdb files on a separate drive and it should be given ample space to grow which depends upon your needs.when the tempdb is in use,shrinking it ‘on the fly’ does not help much.
http://support.microsoft.com/kb/307487
We also used to have such issues so once a while we needed to restart sql service to clear our tempdb.
@Pinal- Over to you Sir
you may consider adding extra datafiles to tempdb.This way if a datafile in one drive is full,some other datafile will be utilized.
But its all the same thing.You need sufficient space as per your needs.
i want u r articles or blogs how can i login into u r site
You can subscribe to the blog
Hi Pinal,
I have scenario to load values from CSV file into SQL table. I can do this using bulk insert and it works fine, but if I remove identity column from csv and if I try to load it is not working.
Could you please suggest what is the best way to load specific columns value into SQL table.
Cheers,
Ramu
Hello Ramu,
You can import selected column using the Import/Export wizard. To use repeatedly save the wizard as SSIS package and execute the package.
OPENDATASOURCE function can also be used to perform this task.
Regards,
Pinal Dave
The following post deals with the case you faced
You can use many methods
Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
Hello
I have a question
Is there any possibility to enter multiple dates into a single column
I would like to enter all work order contacting dates respective that order wants to enter into a single column.
Is there any chance to enter like that or I need to enter different rows for each date
please give me suggestion regarding this
Thank you
Hello Usha,
The best method is to use XML datatype column for this purpose.
The another way is to use create a foreign key table containing order contacting dates.
Otherwise convert the dates into a comma saperated string.
Regards,
Pinal Dave
Hi Pinal,
I have one question.
I have three tables that are getting updated 24*7*365 and there are number of stored procedures that fires select on these tables.
The table is having primary key but that is not used in where condition in any of the Select from these tables, they have one field called Product name and on based of that where condition evaluated.
My question is what are the best options for creating the indexes on these three tables?
P.S (I Dont have any Index right now and willing to improve performance using Indexes only.)
@Jigar- If an index gives you a high ‘SELECTIVITY’ and reduces your table scan then it is good for you.
In your case you mention that your search condition is based on ‘Product name’.Hence you can create a non-clustered key on this field.It will improve your performance surely.
PS: Indices get fragmented over the time.So ask your DBA to take care of that.(hint. alter index rebuild/reorganize commands)
—>Over to Pinal sir
You could try Database Engine Tuning Advisor or just read the execution plan (insert your sql-statement into SQL Server Management Studio and press CTRL+L) and try to figure out where indexes are needed. Google for these and you find a lot of information on MSDN.
But my bet would be just to add nonclustered index to the ‘Product name’ field:
CREATE NONCLUSTERED INDEX [TableName_ProductName] ON [TableName]
(
[ProductName]
)
You say you have primary keys in tables. That usually means that you have also clustered indexes on those tables. So if you just JOIN tables using primary keys that should do fine.
Now if you are still facing performance problems then possibly there’s something else that’s holding back the DB (possibly write locks). That is harder to fix without actually seeing the database.
What is that primary key?
If it is just an identity column, you can dummily add it in the where clause for better performance
where conditions and ok_col>0
Hi All,
I want to merge 2 columns between Newline/Carriage return.
For Example..
My table
EmpID | First Name | Last Name
——————————
1 | First | Second
2 | chris | cintrella
…
i want Output for the following format…
EmpID | Name
————–
1 | First
Second
2 | chris
cintrella
…
Please give solution….
warm regards,
Ms.Hema
Where do you want to show data?
This feature is known as “Suppress if duplicated” that should be done in the front end application
Hi Pinal,
One of my DB got in to recovery mode, the log file is a monstrous 150gb… Is there way I can stop the recovery and delete the data and log files? I do not need the db as I have the bkp else where…
This is a client server and I do not want to risk it as there are other production dbs there.
Regards,
Sriram Hariharan
If you have recent back , drop the database
drop database your_db
Hi,
I have a requirement where using a single select statement I need to get the following output in sql
Col 1 Col 2
Row1 1 2
Row2 3 4
That is basically the output has to be just a display in above format.
Please suggest as to how can we achieve it.
Hi,
i have a huge database with approx. 30.000.000 rows…
i need to search 1 varchar-field per row with p.ex. \directory1\directory2\directory3\testfile_100121_sqlserver.bak
lets say i want to search for the phrase “sql”
What is the fastest way to get this done?
With a simple select * from table where varcharthing LIKE ‘%sql%’ it nearly kills the Server…
With an full-text-catalog the search is very fast but not exact and not showing all entries with “sql” in it….
Help :-). Any ideas?
Regards,
Patrick
hi, im migrating an access database and it have some access functions (FIRST(), PIVOT, TRANSFORM, OWNERACCESS) but i need to do the same things in SQL SERVER 2000, i already search in internet but nothing, i dont know if someone can help me or where can i find a solution, really thanks.
regards
Hi Ruben,
SQL Server does not have functions for exact functionality of these MS Access functions. But FIRST() can be replaced by TOP 1, PIVOT is a function in SQL Server 2005 but in SQL Server 2000 you will have to write queries to perform it. Functionality of other functions also have to be written in T-sql.
Regards,
Pinal Dave
thanks a lot it will be very helpful, im learning a lot in this blog, thanks again pinal.
regards
Hi ,
I am jaffer a B.E fresher ,want to build career in databases
please give solution for this…
-> consider any student table …
select student_name, subject wise maximum marks ?
please reply me soon.
select student_name, subject ,max(marks) as marks
from student
group by student_name, subject
and also i have no job but want master in writing sql
quries beyond query optimization levels , how can i
achieve that…
reply me soon
hi sir,
I need Query for this situation,Please give
==============================
Vehiclename Drivers
===========|=========
|
Car | 2
Car | 2,3
Car | 2,5,8
Car |
Scooter | 4
Scooter |
Scooter 4,6
I want Result like this
=================
Vehiclename Drivers
Car 2,3,8
Scooter 4,6
Without using Cursor ,Please give query
@Chris
You might want to separate the values, find the unique one, then put them back together. Pinal has some articles on these matters, perhaps they will help you:
http://blog.sqlauthority.com/2007/05/06/sql-server-udf-function-to-convert-list-to-table/
http://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/
http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/
i have a problem wi th this sum is not working ,
pls suggest alternate.
select *,Fromdate,Todate,datediff(day,Fromdate,Todate) as leavedays,sum(leavedays)
from leavepermission1
where datepart(month,AppliedONdate) = ’1′ and Employeeid = ’125′
Harish,
Aggregate Function is used with the help of ” group by” and following query may help you…
declare @leavepermission1 table
(
sno int,
employeeid int,
address char(10),
Fromdate datetime,
Todate datetime,
AppliedONdate datetime
)
insert into @leavepermission1 values (’1′,’111′,’Agra’,’2008-08-01′,’2008-08-02′,’2008-08-05′)
insert into @leavepermission1 values (’1′,’112′,’CHD’,’2008-08-11′,’2008-08-22′,’2008-08-25′)
insert into @leavepermission1 values (’1′,’111′,’PKL’,’2008-09-01′,’2008-09-22′,’2008-09-05′)
insert into @leavepermission1 values (’1′,’113′,’Agra’,’2008-09-01′,’2008-09-02′,’2008-09-07′)
insert into @leavepermission1 values (’1′,’114′,’CHD’,’2008-11-01′,’2008-11-08′,’2008-11-25′)
insert into @leavepermission1 values (’1′,’114′,’CHD’,’2008-12-01′,’2008-12-12′,’2008-12-12′)
insert into @leavepermission1 values (’1′,’111′,’PKL’,’2009-01-01′,’2009-01-12′,’2009-01-05′)
–select * from @leavepermission1
select *,Fromdate,Todate,datediff(day,Fromdate,Todate) as leavedays–,sum(datediff(day,Fromdate,Todate) )
from @leavepermission1
where datepart(month,AppliedONdate) = ’1′ and Employeeid = ’111′
– Used Group by
select datepart(month,AppliedONdate),sum(datediff(day,Fromdate,Todate) )
from @leavepermission1
group by datepart(month,AppliedONdate)
Thank you Mr.Pinal for your reply
could you help me to write the query :
how to convert the dates into a comma saperated string.
Thank you
It may be helpful….
select getdate()
output
———————–
2010-01-25 12:51:37.997
select replace((convert(varchar(20),getdate(),102)),’.',’,')
output
————————-
2010,01,25
Trying to replicate from SQL 2005 to 2 subscribers on 2008. one server is succeeded and another one is failing. Getting error “Agent message code 20084. Process could not connect to subscriber “. Using managment studio 2008 I could connect to all servers. Ping is working fine in all direction. Any ideas pls?
Hello Christopher
I did go through almost similar one
Try to use thsi queru
I Hope it will be useful for u
IF OBJECT_ID(‘Table1′, ‘U’)
IS NOT NULL
DROP TABLE
Table1 CREATE TABLE Table1( ColumnA INT, ColumnB VARCHAR(1))
GO
INSERT INTO Table1 (ColumnA, ColumnB) VALUES (1, ‘a’)
INSERT INTO Table1 (ColumnA, ColumnB) VALUES (1, ‘b’)
INSERT INTO Table1 (ColumnA, ColumnB) VALUES (2, ‘c’)
INSERT INTO Table1 (ColumnA, ColumnB) VALUES (2, ‘d’)
GO
ColumnA ColumnB
1 a
1 b
2 c
2 d
IF OBJECT_ID(‘dbo.JoinString’, ‘FN’) IS NOT NULL DROP FUNCTION dbo.JoinString
GO
CREATE FUNCTION dbo.JoinString (@ColumnA INT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @result VARCHAR(100)
SET @result = ” SELECT @result = @result + ‘ ‘ + IsNull(ColumnB, ”)
FROM Table1
WHERE ColumnA = @ColumnA
ORDER BY ColumnB
return LTRIM(@result)
END
GO
DECLARE @Tab Table( ColumnA INT, ColumnB VARCHAR(100))
INSERT INTO @Tab (ColumnA)
SELECT DISTINCT(ColumnA)
FROM Table1 (NOLOCK)
UPDATE @Tab SET ColumnB = dbo.JoinString(ColumnA)
SELECT *FROM @Tab
ColumnA ColumnB
1 a b
2 c d
I am jaffer a B.E fresher ,want to build career in databases
please give solution for this…
-> consider any student table …
select student_name, subject wise maximum marks ?
please reply me soon.
select student,subject,max(marks) as marks from student
group by student,subject
and also i have no job but want master in writing sql
quries beyond query optimization levels , how can i
achieve that…
reply me soon
very goodevening sir,
could u please tell me
“
Hello Pinal.
I’m looking for a SQL script that is output will be the SQL Server startup parameters.
If I want to create this script witch system table should I address to find this information?
Thanks
Oren
i want to count total number of days for a particular month like below.
for example:
Date:23/01/2010
i want how many sundays,mondays,tuesdays,wednesdays,thursdays,fridays in this month
like this:
sundays(5)
mondays(4)
tuesdays(4)
i want to count total number of days for a particular month like below.
for example:
Date:23/01/2010
i want how many sundays,mondays,tuesdays,wednesdays,thursdays,fridays in this month
like this:
sundays(5)
mondays(4)
tuesdays(4)
thanks in advance.kindly rly plz very urgent..
declare @date datetime
set @date='20100123'
select week_day,count(*) as counting from
(
select datename(weekday,dateadd(day,number,dateadd(month,datediff(month,0,@date),0))) as week_day from master..spt_values
where type=’p’ and number between 0 and day(dateadd(month,datediff(month,0,@date)+1,-1))-1
) as t
group by week_day
Hi Pinal,
I’m using My SQL, I’m facing on problem. In my table, there is a details for Products, sales,quarter and year. the details are as follows.
ProdID Quarter Sales Year
1 1 1 2010
1 4 2 2009
1 1 5 2009
1 4 5 2008
2 1 1 2009
Now i want the sum of Sales as per the quarter.
the output should be as follows
ProdID Sales
1 3
1 10
2 6
I’ve written a following query to get the results, I’m getting the output for the prodID=1 but not for the prodID=2.
the query is as follows
SELECT (s.Sales+s1.Sales)as totalsales,s.year,s.quarter
FROM
Sales s
LEFT JOIN Sales s1 ON (s.ProdID = s1.ProdID)
WHERE s1.year=s.year-1 and s.Quarter = 1 and (s1.quarter = 4 )
Group by s.ProdID,s.year
ORDER BY s.year;
Please modify the query to get the output.
hi, Pinal
there r 2 tables master : username, GSID
details:username,SID.
primary foreign key is username
in SID, one default value goes in master tbls “GSID” n all other values for SID goes into details tbl under same username.
for e.g. username : test user
GSID: CometMotors,Goblin,AutoWay
here CometMotors vl go into master n other 2 in detail under test user.
nw, I want result as combined with comma seperated in query result llike : “CometMotors,Goblin,AutoWay”, can u provide such query?
Hello
I want to backup my database in the external harddrive
I don’t know how to backup in external hard drive
could any one help me regarding this
Thank you in advance
While taking the backup,it asks for the location where you want to place your backup file.Just change the default path and put the path which you desire.I am assuming here that you are taking native backup.
If I could not understand your qustion well,please elaborate a bit more:-)
Hello chandan
Thank you so much for your reply
Actually I wnat to backup from remote desktop
So it is not showing my external harddrive over there to backingup my database
Could u explan me What I need to do in that situation
thank you
If the server has access to your external drive, you can use UNC path like
\\syste_name\drive_name\file_name.bak
Our ‘sa’ password has been changed by some one and linked servers are failing. Is there a way to find who has made changes? If I get clue asap would reduce my search around.
Advance thanks.
hello
can any one help me ?
how to decrypt the ssn and how to get the details based on that SSN from SQL table
please help me
thank you
@Usha.
I dont know which version of SQLServer are you using.
From SQL Server 2005 you can use certificates, Symmetric and ASymmetric keys to Encrypt and Decrypt data in database.
Same key /certificate has to be used to decrypt data that was used to encrypt data. I don;t have hands on experience, to know more about Symmetric and Asymmetric Key please follow this link:
http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/
Its a very easy tutorial, easy to understand and implement, written by none other than our SQL Maestro (Pinal Dave)
~ IM.
Thank u so much
Article helped me to resolve my issue
nice article
Hi,
I am surprised with the strange behavior of SSIS “Lookup transformaton” control. Actually, i was checking the duplicate entries for one field “Emp_ID”(which is unique). but due to some reasons it was not able to send all the “Emo_ID” to destination. in the same table i had one more field “EMP_no”(which is also unique ).
Now something happened, which is hard to understand. What happend that the package was taking around 1~2 hours for completion with “Emp_ID”. But it is taking only 5~6 minutes afte adding the “Emp_no” to the “Columns” part of the “Lookup Transformaton” control. I used the same approach of “Composite key” in SQL. Afte this change, i got the solution and the package is working as expected, but couldnot able to understand why the performance got increase many folds.
Any clue in this regard will help me out.
Thanks in advance.
Thanks,
Loknath
Hello Loknath,
One thing about the “Lookup Transformation” task that we must know is that its value comparision is case sensitive.
The reason of variant execution time could be index or data statistics. I think there would be an index on Emp_no column.
Regards,
Pinal Dave
Thanks Pinel,
Actually, the indexes are as given:
In Source DB:
i) nonclustered located on PRIMARY index on “EMP_ID”
ii) clustered, unique, primary key located on PRIMARY “EMP_NO”
In Destination DB:
i) nonclustered located on PRIMARY on “EMP_ID” and ii) clustered, unique, primary key located on PRIMARY for “EMP_NO and EMP_ID”
Ok, i could understand that the index could play a role in performance. But i am not getting the case-sensivity of the “Lookup Transformation” control.
The data types are as:
EMP_ID -> int
EMP_no -> bigint
will it do any difference for pulling the records. My records are in the range of 25573200 for EMP_ID and 161664991 for EMP_No.
Again thanks for you reply…
Thanks,
Loknath
CREATE TRIGGER trig_addEmployee
ON staffcust
FOR INSERT
AS
DECLARE @newName VARCHAR(100)
SELECT @newName = (SELECT staff_id + ‘ ‘ FROM Inserted)
PRINT ‘New employee “‘ + @newName + ‘” added.’
INSERT INTO staffcust values(s1)
The name “s1″ is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
As erro says, what is s1?
sorry i sent post above before i got chance to add comment…
getting that error was just wondering if anyone could help me out
You need to tell us what s1 means in your code
How much space does an empty string use on SQL SERVER 2008? On an integer, varchar does it matter?
I know that a SPARSE NULL column takes up no space but I cannot find the documentation that states how much a non sparse NULL value takes up.
Hi pinal i have question
SELECT username + ‘ من ‘ + location + ‘ يقول ‘+ comment + ‘ عن ‘ + CAST(Date as varchar) as ‘تعليقات القراء ‘ FROM tblComments
this is my query i am concating arabic in the query
my database supports arabic since i have got arabic data
but this thing gives me question marks
may be u can help me
rgds
Nadir Firfire
Which datatype did you use?
You should use nchar or nvarchar datatypes
Hello pinal
I am regular reader your blog last one year it so interesting and increase my knowledge of SQL server.
Right now Please to know what is Master data Services in sql server 2008, Please give me more information about it.
Rgds
vishnu dalwadi
Master Data Services (MDS) helps organizations standardize and streamline the business data customers use across their organization to make critical business decisions. MDS is a Master Data Management (MDM) application built from platform components which may be deployed as an application or extended by use of the platform components to consistently define and manage the critical data entities of an organization. MDS is an any-domain hub that supports but is not limited to domains such as product, customer, location, cost center, equipment, employee, and vendor.
Using MDS, customers can manage critical data assets by enabling proactive stewardship, enforcing data quality rules, defining workflows around data changes, notifying impacted parties, managing hierarchies, and sharing the authoritative source with all impacted systems.
refer : http://www.microsoft.com/sqlserver/2008/en/us/mds.aspx
hi Pinal
First many thanks to your great help..
my problem is i want reset IDENT_CURRENT(‘tablename”)
value to -1
explanation: hi i’m using two table 1st i ll insert one table value after that i ll select identity value from that table to insert 2nd table value. my problem is i want to reset the 1st table ident_current value to -1. plz tel me how to do??
eg: insert into master(name,email) 1st table with id as autoincrement value
insert into child(IDENT_CURRENT(‘master’), marks, school)
set IDENT_CURRENT(‘master’)=-1; // is it possible??
plz help me i need it very urgently
many thanks in advance :)
IDENT_CURRENT is used to returns the last identity value generated for a specified table, it is not used to set anything.
and you want to continue the rows in your first table by having -1,-2,-3 ….. or -1,0,1,2……
Try using
DBCC CHECKIDENT ( table_name
[ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
more info is on http://msdn.microsoft.com/en-us/library/ms176057%28v=SQL.100%29.aspx
Thanks
Manish
For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID
sir,
installed 2008 r2 in my PC
in SSRS configuration tool when i click on report manager url i found an error like
User ‘DEEPTHI-PC\DEEPTHI’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
and when i click on web service URL
The permissions granted to user ‘DEEPTHI-PC\DEEPTHI’ are insufficient for performing this operation. (rsAccessDenied) Get Online Help
but i am logged on as administrator only
is any installation problem…?
or any configurations i need to do..?
please help me..
good articles
Hi pinal,
How to use date dimension table while writing queries on datawarehousing
Thanks
ramesh.m