|
Just in case anyone is interested, I solved the problem by replacing the SQL Express Server with the full version. All seems to be working correctly now.
|
|
|
|
|
Hi all,
I created a table in which there r n fields and in that two of them are date field i.e., startdate and enddate.
Now i want to write a query,to fetch the records from table which comes under startdate and enddate.
so,i need a help how to write the query.
Thanks in advance.
|
|
|
|
|
What you did so far? Have you try to write a query?
As I could understood you then query should be like:
select startdate,enddate from mytable where startdate >'2009.12.01' and enddate > '2009.12.22'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Try this
declare @t table(somevalue varchar(100),startdate datetime, enddate datetime)
insert into @t
select 'somevalue1','01/14/2009', '01/20/2009' union all
select 'somevalue2','01/15/2009', '01/22/2009' union all
select 'somevalue3','01/18/2009', '01/31/2009' union all
select 'somevalue4','02/01/2009', '02/10/2009' union all
select 'somevalue5','05/14/2009', '05/20/2009'
I want to get the records where between '01/14/2009' and '02/01/2009' (inclusive)
Query:
select * from @t
where startdate >='01/14/2009' and enddate <='02/01/2009'
Output:
somevalue startdate enddate
somevalue1 2009-01-14 00:00:00.000 2009-01-20 00:00:00.000
somevalue2 2009-01-15 00:00:00.000 2009-01-22 00:00:00.000
somevalue3 2009-01-18 00:00:00.000 2009-01-31 00:00:00.000
Niladri Biswas
|
|
|
|
|
i m using this stored procedure
CREATE PROCEDURE [dbo].[sp_db_restore]
@DBName varchar(60),
@BackName varchar(120),
@DataName varchar(60),
@DataFileName varchar(120),
@LogName varchar(60),
@LogFileName varchar(120)
AS
RESTORE DATABASE @DBName FROM
DISK = @BackName WITH MOVE @DataName TO
@DataFileName ,
MOVE @LogName TO @LogFileName,
REPLACE
it gives the following error
"RESTORE cannot process database 'databasename' because it is in use by this session. It is recommended that the master database be used when performing this operation.
RESTORE DATABASE is terminating abnormally."
plz help with any solution
Regards
Narendra Singh
(Jindal Tech Ventures)
|
|
|
|
|
I think the error says it all. It seems you are trying to run the stored proc in the database you are restoring, which obviously you can't
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
hey ashfield thanks for reply
i found the answer
before running restore command i have to use any other databse to make the restoring database free. After that when we run restore commands it works great.
Thanks for all ur replies
Regards
Narendra Singh
(Jindal Tech Ventures)
|
|
|
|
|
i usually have to Sp that Closes the Connection before i do a Back or Restore.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_ClearDatabaseConnections]
@DBNAME VARCHAR(255)
AS
SET NOCOUNT ON
DECLARE @SPID INT, @STR VARCHAR(255)
DECLARE USERS CURSOR FOR
SELECT SPID
FROM MASTER..SYSPROCESSES
WHERE DB_NAME(DBID) = @DBNAME
OPEN USERS
FETCH NEXT FROM USERS INTO @SPID
WHILE @@FETCH_STATUS <> -1
BEGIN
IF @@FETCH_STATUS = 0
BEGIN
SET @STR = 'KILL ' + CONVERT(VARCHAR, @SPID)
EXEC (@STR)
END
FETCH NEXT FROM USERS INTO @SPID
END
DEALLOCATE USERS
as you can see it accept the name of the DB that you want to Restore.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
thanks for your reply
good code
Regards
Narendra Singh
(Jindal Tech Ventures)
|
|
|
|
|
You are Welcome 
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
i have database of sql
in User table there is field which contain encrypted password
this database is using with an application. so i know the string using which these passwords are encrypt so please can any one help me how can i get that passwords.
Regrads
|
|
|
|
|
If it's properly done you can't get the unencrypted passwords from the database.
The normal procedure is that you encrypt the supplied password with the same algorithm as was used when storing it in the database, and then compare.
|
|
|
|
|
hi all
i have a .bak file created with a stored procedure which takes backup of a database. Now i want to restore that .bak file to the database can anybody help me with a stored procedure.
This .bak file is created with the help of
backup database databasename
to disk = "destination path"
with
thanks for ur replies in advance
Regards
Narendra Singh
(Jindal Tech Ventures)
|
|
|
|
|
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- Restore a backed up database
-- Given: the filename and path where the backedup database file is located and the name of the database to restore it as
-- The entry will be restored and a row placed into oDirect.dbo.tbl_dbref - which keeps track of the databases
-- The users for the database must also be restored!
ALTER PROCEDURE [dbo].[sp_RestoreDatabase]
@dbname char(32), -- the database name to restore as
@filename char(64), @path char(256) -- the location of the backuped up database file (on the SQL Server)
AS
set nocount on
declare @sql nvarchar(3000)
execute('sp_ClearDatabaseConnections ' + @dbname)
-- Restore the database
select @sql = ' RESTORE DATABASE ' + ltrim(rtrim( @dbname )) + ' FROM DISK = ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @filename )) + ''' '
select @sql = ltrim(rtrim(@sql)) + ' WITH '
select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + 'TNGoedit_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , ' -- logical file name to physical name
select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + 'TNGoedit_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' ' -- logical file name to physical name
select @sql = ltrim(rtrim(@sql)) + ' ,REPLACE,RECOVERY;'
--select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + ltrim(rtrim(@dbname)) + '_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , ' -- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + ltrim(rtrim(@dbname)) + '_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' ' -- logical file name to physical name
print @sql
execute ( @sql )
-- Was the command successful or was there a problem
if ( (select @@Error) = 0 ) begin
-- Put an entry into oDirect.dbo.tbl_dbRef
-- execute ( 'sp_DataSet_Save ''' + @xml + ''' ' )
-- TODO: restore the users
select 'Restore Successful' [Result]
end
else begin
select 'Restore Unsuccessful' [Result]
end
You can Simply change it to BAK file
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I want to build a trigger for updating a summary table in SQL Server.
I have a summary table with records listing all the individual tables in my database. When I add a new record to the individual tables I wish to for it to update the summary record for the latest data.
Example:
When adding a new record with the date, I want the date to be entered in my summary table as the last date entered for that table, the name of the table will be listed.
I could do this with a stored procedure, but I would rather this be automatic with a trigger.
Can anyone help me with this, thanks in advance.
Michael
modified on Monday, December 21, 2009 5:43 PM
|
|
|
|
|
This is a really dumb idea, doubling your transaction cost to support a summary table that you query how often. Putting in a trigger (triggers are evil, like cursors) for such a trivial, non essential operation is not good.
Create a view or a stored proc to get the information when and if you need it.
A trigger is exactly like a proc except it can reference the inserted/updated record it is called from. Read BOL for some examples.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
when i am executing the stored procedure by passing xml it is throwing transaction error
(1 row(s) affected)
Msg 266, Level 16, State 2, Procedure get_next_sequence, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
(1 row(s) affected)
(1 row(s) affected)
Msg 3902, Level 16, State 1, Procedure sp_ProcessExcelSheet, Line 58
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
(1 row(s) affected)
stored procedure is here.
ALTER PROCEDURE [dbo].[sp_ProcessExcelSheet]
@file varchar (MAX)
AS
BEGIN
DECLARE @ComplaintDetail TABLE
(
ID int identity(1,1),
FName Varchar(50) not null,
Email Varchar(50),
Address Varchar(100) not null,
Disclose bit not null,
Complaint text not null,
Location varchar(50) not null,
Area varchar(50) not null,
PlaceFound varchar(50) not null,
ComplaintMode varchar(50) not null
)
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @file
INSERT INTO @ComplaintDetail (FName,Email,Address,Disclose,Complaint,Location,Area,PlaceFound,ComplaintMode)
SELECT FName,Email,Address,Disclose,Complaint,Location,Area,PlaceFound,ComplaintMode
FROM OPENXML (@idoc, 'NewDataSet/Table',2)
WITH (FName Varchar(50),Email Varchar(50),Address Varchar(100),Disclose bit,Complaint text,
Location varchar(50),
Area varchar(50),
PlaceFound varchar(50),
ComplaintMode varchar(50))
declare @Count int
declare @IntCount int
set @IntCount=1
select @Count=count(*) from @ComplaintDetail
create table #Reff(FName varchar(50),Email varchar(50),ReffNo varchar(10) )
declare @FName Varchar(50)
declare @Email Varchar(50)
declare @Address Varchar(100)
declare @Disclose bit
declare @Complaint varchar(max)
declare @Location varchar(50)
declare @Area varchar(50)
declare @PlaceFound varchar(50)
declare @ComplaintMode varchar(50)
declare @seqno int
declare @sequentialNo varchar(10)
BEGIN TRAN T1
while(@IntCount<@Count+1)
begin
exec dbo.get_next_sequence @seqno output
set @sequentialNo= 'p'+convert(varchar(9),@seqno)
select @FName=FName,@Email=Email,@Address=Address,@Disclose=Disclose,@Complaint=Complaint,@Location=Location,@Area=Area,@PlaceFound=PlaceFound,@ComplaintMode=ComplaintMode from @ComplaintDetail where ID=@IntCount
Insert into HelpLine(ReferenceNo,FName,Email,Address,Disclose,Complaint,Location,Area,PlaceFound,ComplaintMode,ComplaintDate,ComplaintStatus)
values(@sequentialNo,@FName,@Email,@Address,@Disclose,@Complaint,@Location,@Area,@PlaceFound,@ComplaintMode,getdate(),'Open')
insert into #Reff values(@FName,@Email,@sequentialNo)
set @IntCount=@IntCount+1
end
IF @@ERROR = 0
COMMIT TRANSACTION T1
else
RollBack TRANSACTION T1
select * from #Reff
drop table #Reff
SET NOCOUNT ON;
END
when i changed the same same stored procedure it is throwing error as
Msg 2801, Level 16, State 1, Procedure sp_ProcessExcelSheet, Line 57
The definition of object 'sp_ProcessExcelSheet' has changed since it was compiled.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_ProcessExcelSheet]
@file varchar (MAX)
AS
BEGIN
DECLARE @ComplaintDetail TABLE
(
ID int identity(1,1),
FName Varchar(50) not null,
Email Varchar(50),
Address Varchar(100) not null,
Disclose bit not null,
Complaint text not null,
Location varchar(50) not null,
Area varchar(50) not null,
PlaceFound varchar(50) not null,
ComplaintMode varchar(50) not null
)
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @file
INSERT INTO @ComplaintDetail (FName,Email,Address,Disclose,Complaint,Location,Area,PlaceFound,ComplaintMode)
SELECT FName,Email,Address,Disclose,Complaint,Location,Area,PlaceFound,ComplaintMode
FROM OPENXML (@idoc, 'NewDataSet/Table',2)
WITH (FName Varchar(50),Email Varchar(50),Address Varchar(100),Disclose bit,Complaint text,
Location varchar(50),
Area varchar(50),
PlaceFound varchar(50),
ComplaintMode varchar(50))
declare @Count int
declare @IntCount int
set @IntCount=1
select @Count=count(*) from @ComplaintDetail
create table #Reff(FName varchar(50),Email varchar(50),ReffNo varchar(10) )
declare @FName Varchar(50)
declare @Email Varchar(50)
declare @Address Varchar(100)
declare @Disclose bit
declare @Complaint varchar(max)
declare @Location varchar(50)
declare @Area varchar(50)
declare @PlaceFound varchar(50)
declare @ComplaintMode varchar(50)
declare @seqno int
declare @sequentialNo varchar(10)
BEGIN TRAN T1
SET IMPLICIT_TRANSACTIONS ON
begin
while(@IntCount<@Count+1)
begin
exec dbo.get_next_sequence @seqno output
set @sequentialNo= 'p'+convert(varchar(9),@seqno)
select @FName=FName,@Email=Email,@Address=Address,@Disclose=Disclose,@Complaint=Complaint,@Location=Location,@Area=Area,@PlaceFound=PlaceFound,@ComplaintMode=ComplaintMode from @ComplaintDetail where ID=@IntCount
Insert into HelpLine(ReferenceNo,FName,Email,Address,Disclose,Complaint,Location,Area,PlaceFound,ComplaintMode,ComplaintDate,ComplaintStatus)
values(@sequentialNo,@FName,@Email,@Address,@Disclose,@Complaint,@Location,@Area,@PlaceFound,@ComplaintMode,getdate(),'Open')
insert into #Reff values(@FName,@Email,@sequentialNo)
IF @@ERROR <> 0
RollBack TRANSACTION T1
set @IntCount=@IntCount+1
end
select * from #Reff
END
COMMIT TRANSACTION T1
drop table #Reff
SET NOCOUNT ON;
END
can any body help me regarding this
hi
|
|
|
|
|
You have a begin tran on the outside of a while loop and have a rollback on the inside with an unconditional commit on the outside. If a rollback occurs, the loop continues transaction-less and the commit will execute anyway = your error 3902. I would not wrap a transaction around a while loop if there was another way (there is!).
Never issue a commit or rollback without checking to see if there is actually a tran.
if @@trancount > 0
rollback transaction
I would use a try-catch, without a doubt. Heres generally how a transaction could be handled:
begin try
begin transaction
--dml statement here
if @@trancount > 0
commit transaction
end try
begin catch
if @@trancount > 0
rollback transaction
print error_message() --(execute my error handler)
end catch
Some of the other wierd messages you are getting is because the proc failed to issue a commit or rollback at all leaving 'things' in an un-committed state. Until you have verified that the proc you are developing will always close the trans - issue either a commit or rollback after each 'test'.
|
|
|
|
|
Hi I am trying to create a stock scanner for my website. The user would choose and add certain criterias one by one, and a dynamic SQL statement would need to be created on the fly to retrieve the data.
Currently I am able to do this by appending pieces of SQL string after a long SQL string like the following:
Whenever "Add Criteria" button is pressed:
newQuery = "AND criteria > x"
userCriteria = userCriteria + newQuery
When "Retrieve Information" button is pressed:
Querystring = "Select * WHERE Date = date " & userCriteria
Is there a more efficient mechanism? If not that's also fine as it currently works well, but I just need some reassurance from experts. Thanks!
modified on Monday, December 21, 2009 12:07 AM
|
|
|
|
|
If it works then you can only improve the structure of the query otherwise it is an exercise in string concatenation.
If the where clause is the only dynamic piece and the elements are known you can use something like
Where (IsNull(@AttrID, -1) = -1 OR AttrID = @AttrID)
where the default value is null or -1. Caveat, too many of these (about 6 IIRC) MAY affect the query performance I believe. I use it all the time and have had up to 8 elements in the where clause and it worked fine. The only benefit is it is not dynamic, not a huge issue!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a stock time series data in which a numerical indicator fluctuates over time. If I want to find out for each stock that has its indicator now over 30, how many days since the last time this indicator is less than 30?
In other words, how many days since the last time the stock's indicator crosses above 30?
Just for your reference, currently I have the following:
SELECT ticker,
DATEDIFF(d,(SELECT MAX(DATE) FROM....),'12/21/2009')
WHERE Indicator > 30 AND Date = '12/21/2009'
I'd greatly appreciate any help! If possible please provide a sample code.
By the way, thank you Ashfield for your help on a similar question last time, later I was able to understand the code you gave me and utilized it in different ways. This is a bit more difficult for me though.
modified on Monday, December 21, 2009 12:06 AM
|
|
|
|
|
How about something like
select ticker,datediff(day,t.date,(select max(date) from table t1
where t1.ticker = t.ticker and t1.indicator < 30))
from table t
You may have to play about a bit, but it should be somewhere close
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thank you Ashfield, I'll test it out later and come back if I still have an issue.
|
|
|
|
|
i exported data in a table from an apache derby dbase to a sql script so i could run on MS SQL(Studio Express) to get the data in my table
but then there is this script for this table anytime i try running the script i get the following error msg.
before this the script would not even open the Studio when i try opening it and an err msg of "out of memmory would pop up"
please help me as to how i can solve this errror.
.
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Cannot execute script.
------------------------------
ADDITIONAL INFORMATION:
Exception of type 'System.OutOfMemoryException' was thrown. (mscorlib)
------------------------------
|
|
|
|
|
How big is that script? Have you considered loading and executing it piece by piece?
You can execute scripts from the commandline, without open the Management Studio. That's described here;
http://msdn.microsoft.com/en-us/library/ms165702.aspx
I are Troll 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
