OK, so as I understand it the db_accessadmin role got merged into db_securityadmin in recent versions of SQL server? This is fine, it's better this way.
My question is, when did it happen? I try to cater for all reasonable flavours of SQL server in my system, I recently took great joy in removing support for SQL 7 (The last client using it paid me to migrate their data to 2008r2 via 2005).
Now I'm trying to set up a login with both db_accessadmin & db_securityadmin rights. Or just db_securityadmin, if appropriate. So I need to know which release they merge in. Google is useless on this, so is MSDN.
I could just try to grant the right and suppress the error if it fails, On Error resume next anyone
One day we'll look back on this and plow into a parked car.
I have a SQL Server project that I'm currently adding code to using SSMSE2k8 and beside the fact that this is all TSQL, there's this creep that's occuring in the facility. Namely the .xml. Now, here's the more technical side of TSQL as related to XML, using a FOR XML PATH juncture. I can do a basic SELECT query and get output of xml (pseudo output really because ... this is the creep; in ssmse that's as far as development WENT) but I'm constrained by all these substitutions in he editor:
Could have knocked me over with a feather ... the reason that my editor (was) going south was because of the type I had used to reassign parsed lines of singleline .xml while striking it up as data in a table!.
[nvarchar](MAX) throws (bad use of an alliteration) the phantom formatter into overdrive, while [xml] is silently overlooked. Thus arrows are preserved.
SELECT [xmliform] AS'data()'FROM [database].[xml].[tblXMLAsTypeNvarchar] FOR XML PATH('')
So, as you can see, an editor will try to format anything from a table where the type is [nvarchar] using it's xml parser when I specify "FOR XML PATH" regardless of how bad I "want" it as xml. And that is with substituted control characters.
ok but this is when that you use COMMIT end of my related code.
but i dont use COMMIT tran in my code.
First, it's a lousy example; there's no way that the server can "guess" whether it should be rolled back or comitted automatically. Second, there is nothing to commit or rollback, since a SELECT statement doesn't change the data. Don't use a transaction when selecting. Also, set XACT ABORT[^] to ON. Also, don't lock an entire table, unless really, really required.
and if use this code you can see that this table is lock:
hi to all
im face with low performance by below query in sql server 2008 any one can help me.
my query is goon work when top has larg number but when top has less row (for example top 19) this query has low performance for table loaninstallment with amount of records(10000000 R).
any one can help me
thanks for any help
selecttop19 * from(select ROW_NUMBER()over (orderby ID ) as rowNumber,* from BML.LoanInstallment where total_amount like'%80%') tbl
select ID,ROW_NUMBER() over(orderby ID) RowNum from BML.LoanInstallment
selecttop19 * from(select payment_date,total_amount,BML.LoanInstallment.ID,RowNumber from BML.LoanInstallment
#tblTemp on #tblTemp.ID = BML.LoanInstallment.ID
where total_amount like'%80%') tbl
this run completed a large of second
i think the reason of this is my where clause that i use like in this clause and if i remove it my query is ok and response in reasonable time.
what you thinks ?
Find the problem for corrupted database and make it clean then convert the DB to another DB.If you are changing the corrupted database then there is an possibilities to affect in new database also.So clear and Shift.
I wanna ask if I can put just one table for both prices an cities.
In the beginning I created just table Product, but I have two different prices by cities(each city has a particular price).
After that I put two tables one for cities and one prices. but I think I don't need table cities, I'm asking if I can create one table prices with field city.
my data base NOW contains : Table Products: ProductID,ProductCode,ProductName,CityID,SubCategoryID... Table Cities: CityID,PriceID,City Name. Table Prices : PriceID,Price Table OrderDetails: OrderDetailsID, OrderID, FK ProductID, UnitPrice
Table order OrderID, CostomerID,...
The problem with prices : there are many prices for same product when they ordered (each city has a different price)
Well, there are multiple ways to solve a problem. It depends on whether you want to achieve speed or whether you want to have a normalized database. Here's what I would have done:
Have a table called Products: ProductID, ProductCode, ProductName,...
Have a table called Cities: CityID, City Name
Have a table called Prices: PriceID, Price
Have a table for mapping products with prices and cities, ProductPrice: ProductID, CityID, PriceID
Pro: This way your data remains in a normalized form which is considered to be a good design approach.
Con: You have to perform multiple joins while fetching data which will hamper performance a bit. But, I don't think you have millions of cities. So, I won't be bothered much with that.
set@i = 0while(@i<3)
beginIF DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 7OR DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 1print'weekend'elseprint'weekday'select Holiday_Name from Holiday where Holiday_On = DATEADD(day,@i,CONVERT(date,GETDATE()))
SELECT@NewDate = DATEADD(DAY,@i,CONVERT(date,GETDATE()))
RETURN@NewDateselect@i = @i+1
my task is if today is neither weekend nor public holiday ,i have to that date, increment loop...i have retrieve all the 3 working days...can anyone help me pls
if i want declare @NewDate as array[ ,how to declare that..pls help me
alterprocedure sp_pubhol asbeginselect Holiday_Name from Holiday where Holiday_On = CONVERT(date,GETDATE()) endwhen i execute sp_pubhol , i am facing the following issue... Invalid object name 'Holiday' pls suggest me what should i do
can anyone help me in writing stored procedure to Activate reminder for event to send email notification 3 days before event along with event description pls .i am using sqlserver2008R2.the list of events are in DB.
What have you tried so far? Where are you stuck and need help?
Here is what is expected of enquirers:
1. TRY first what you want to do! You may find that it's not that hard.
2. Formulate what was done by you that looks like an issue/not working.
The amount of 0 items will make a problem with Mycroft Holmes' answer - that line won't show up.
You need the numbers from 0-23 (somehow generated, or make a table "Hours" with those values, and then JOIN the query suggested by Mycroft Holmes to it, and replace NULL values with 0.
I have visits table for my patients database. I have created_date field which is timestamp with CURRENT_TIMESTAMP and closed_date with datetime datatypes. I have no problem with the created_date but when I close the file using this command: