i don't know how to use if...exists
i am new to asp.net & database as well
my requirement is to get 3 consecutive working days
declare @i int
declare @count int
while(@count <= 3)
1) select Holiday_Name from Holiday where Holiday_On = DATEADD(day,@i,CONVERT(date,GETDATE()))
the above statement is to check whether holiday exists
2) IF DATEPART(day, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 7 OR DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 1
the 2nd statement is to check whether weekend or not
set @i = @i + 1
if there no holiday and not weekend then only the following code is executed
set @count = @count + 1
here i have to retrieve the date...i don't know how to get the date...because i vil get 3 dates...
actually i no need to select holiday name in 1)statement...there i am checking for holiday..
I am using the below bcp command to export the binary files from the filestream db and all other files are seems to be exported fine (i.e. txt, pdf, rtf, image files & etc) except docx, xlsx & pptx files. I am able to export these files (i.e. docx, xlsx & pptx) but I am getting a warning/error message when opening those files and then it opens the file properly.
BCP "SELECT content FROM [dbo].[Contents] WHERE ID=1" queryout "C:\Temp\" -T -S (local) -f C:\Temp\files.fmt
I am getting the below message when opening docx, xlsx & pptx files:
XLSX - Excel found unreadable content and Do you want to recover the contents of this document? DOCX - The file test.docx cannot be opened bcos there are problems with the content and then Excel found unreadable content and Do you want to recover the contents of this document?
i have a requirement doc but i am unable to understand that one thats y can u tell me the basic tables which are required to achieve the e-commerce project..
as per my understand
1)user may signup using facebook,twitter,google+..
2)user interests and habits are need to be imported from social networking sites while signing up..
3)user may refer the friends, and stored in different groups
4)search, results will be in sorted as user wish(interest, buying habit, friends recommendations...)
5)reviews, user may write the reviews, like the others reviews.
these reviews are need to keep
7)all like wish list, shopping cart,payment, delivery..
You need a basic understanding of the following skills
- Reading and understanding requirements.
- Creating designs
- Creating data models
- Creating databases
And I expect that you are going to also need the following skills
- Internet Security
- Web design
- Basic SQL
- Intermediate Database management and SQL
- Basic user security
- Basic business retail knowledge
You could probably start by learning how to create databases, create tables and use SQL to access them. That would probably take a week.
Then learn about business retail and improve your requirement reading skills. No idea how long that will take you.
The two above together can then be used to create some tables that are at least something like what is needed for the project.
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.
asbegindeclare @i intdeclare @NewDate date
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()))
select @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