[SQL Server 2008 issues] Strange File issue > "CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file"

Strange File issue > "CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file"
Identify string or integer
Disable/Enable Account
maintain unmtched reocrds by source in delete table
merge script issuse in sql server .

Strange File issue > "CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file"

Posted: 08 Aug 2013 02:26 AM PDT

Recently I have had to move and restructure my SQL farms and environment. I had detached a SolarWinds database called NetPerfMon that consisted of three data (mdf) files and one log(ldf) file. The database detached without issue. Today one of my engineers needed to retrieve some data and when I went to restore it I found the files still there. That's great I thought. Just re-attach. Then I received this error:"CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'd:\userdbs\pathToFile.mdf'"I listed the files and database names from sys.databases and this database is DEFINITELY not loaded in to SQL. When I looked at all the open file handles with Sysinternals "Handle.exe", it lists the netperfmon mdf files as being open by the SQL server process. This server has been rebooted several times during the move since I dropped the original database. Why (WHY!?!?) would SQL still have these files open? I already worked around the issue by restoring it somewhere else but I'm dying of curiosity....why would SQL have these files open when the database is not currently attached to the system and hasn't been for several starts? Also why when attaching to an EXISTING file would it be trying to create the data file as it indicates in the error?

Identify string or integer

Posted: 13 Aug 2013 11:20 PM PDT

Hi Team,am passing a parameter to a stored procedure, i want to know whether the parameter is string or integeri want a print statement in stored procedure to print the parameter is string or intEg : EXEC Stored_procedure1 ('abc')output : stringEXEC Stored_procedure1 ('123')output : integerEXEC Stored_procedure1 ('abc112')output : string

Disable/Enable Account

Posted: 18 Aug 2013 09:24 AM PDT

Is there any way I can enable/disable a sql/nt account by running a stored poc?

maintain unmtched reocrds by source in delete table

Posted: 18 Aug 2013 03:30 PM PDT

Hi friend i have small doubt in sql server .plese tell me how to solve this issusei have 3 tables 1 for source table and 2nd for destination table 3rd for soure table records not matched bytarget that records maintain in deletes tablesource table structure (here (id,sal)as int and (name,dname,databasenam) as varchar id , name , sal ,dname ,databasename 1 , a ,100 ,hr ,db1 2 , b ,200 ,j ,db2 3 , c ,300 ,l ,db2target table structure also same id , name , sal ,dname ,databasename 1 , a ,100 ,hr ,db1 2 , b ,200 ,j ,db2 3 , c ,300 ,l ,db2 1 , d ,400 ,kal ,db2delete table structure also same id , name , sal ,dname ,databasenamehere mainaly source record not matched target table records that reocrds i want maintained into delete table . i deleted one reocrds at source table that recordis 1 , a ,100 ,hr ,db1 in target table also there that record. i want delete that record in target table and that record maintain into delete tablei run below query insert into cdc_source.dbo.cdc_deletes select * from cdc_target.dbo.emp where [id] not in (select [id] from cdc_source.dbo.emp)here i need to compare id and databasename fields source reocrds not matched by target then we maintain separetely in delete table.in the above query i compare only id based but i nedd to write id and databasename based.to maintain new reocrds and updated record i used merge script.its work fine.in above query i want to write same way to write above query based on id and databasename filed. how we achive this one. plese tell me that query.

merge script issuse in sql server .

Posted: 17 Aug 2013 08:32 PM PDT

hi friends i have small doubt in sql server plese tell me how to solve this issuse in sql server i have 3 tables 1 for source table and 2nd for target table and 3rd for maintain deletes records table andtable name is cdc_source and id & sal are int datatypes and (name,dname,databasename )are varchar datatypes and data like belowid name sal dname databasename1 veu 15 gr db12 eu 1 jr db23 jd 60 vr db24 pd 80 tr db2target table is cdc_targetid name sal dname databasenamemaintain delete table is cdc_deletesid name sal dname databasenameHere i implement query useing merge script in sql server to maintain delete records in other table(cdc_deletes) as well as to maintain and source records changed (insert/updated/deleted)that would be effected target table(cdc_target). first time i ran query that time what ever records in source table that records goes to target table.and next time i deletedone record in source table .and again i run query that time what ever i deleted record in source that records also deleted in target table.here i want maintain what ever i deleted records that records maintain to deleted tables.i tried query like belowmerge cdc_target.dbo.emp tusing cdc_source.dbo.emp son t.id=s.id and t.databasename=s.databasenamewhen matched thenupdate set t.name=s.name, t.sal=s.sal , t.dname=s.dnamewhen not matched by target theninsert (id,name,sal,dname,databasename)values(s.id,s.name,s.sal,s.dname,s.databasename)when not matched by target theninsert into cdc_source.dbo.cdc_deletesselect * from cdc_target.dbo.emp where id not in(select id from cdc_source.dbo.emp);when not matched by target then--when not matched by source thendelete ; but its not given exacetly.plese tell me how to solve this issuse useing merge script in sql server .

Sql samples, sql tips

Monday, August 19, 2013