Tag: sql-server-2008-r2

Show zero value when no data available in date sequence

I have a table with two columns sale_date and amount: sale_date amount ——————- 05/01/2014 400 05/02/2014 350 05/04/2014 430 . . I want the output to include a value of zero for amount if there are no sales for a particular date. Desired output: sale_date amount ——————- 05/01/2014 400 05/02/2014 350 05/03/2014 0 05/04/2014 430 […]

Displaying results based on different selection in sql server stored procedure

I am currently working on Microsoft SQL Server 2008. My question is: I have 5 conditions represented by parameters @rep, @name, @work, @year, @seg. How do I write a query that will display the result if I only fill up certain conditions=? And then I have a query that will count total number of visit. […]

Issue in complicated join

I have 4 tables tbLicenceTypesX (2 Fields) LicenceTypes LicenceTypesX tbLicenceTypesX (Contains data like) 1 – Medical Licence 2 – Property 3 – Casualty 4 – Trainning Licence tbProduct (3 feilds) Product ProductX CompanyId (F.K) LicenceTypes(F.K) tbProduct (Contains data like) 1 – T.V – 10 – 2 2 – A.C – 30 – 3 3 – […]

SQL Server log file over 5GB in size

I notice that my database size is over 5GB in size around 98% of this is taken by log file while .mdf file size is around 10MB. I tried shrinking database that reduced space by 2MB Then I tried shrinking data file both .mbf & .ldf which hardly reduced space by 10-15mb. When I take […]

Count total when grouping by two columns

I have a table with the following information: ————————————– | client_name | supplier | completed | ————————————– | Acme_1 | Sup_1 | 0 | | Acme_1 | Sup_1 | 0 | | Acme_1 | Sup_1 | 1 | | Acme_1 | Sup_2 | 0 | | Acme_1 | Sup_1 | 1 | | Acme_1 | […]

MultiDatabase based architecture for Web application

If we have an ASP MVC4 web application and we have to provide it to multiple companies with their logins, what should be the best way with respect to database architecture. Should we make separate dbs for each company and on url bases we will connect with respective database? Or should we make one db […]

SQL Migration of Databases

I’m running SQL Server 2008 R2, I have a test database that I’ve modified to have new foreign keys and columns etc. I was wondering what is the best way to map all of the changes to my live db? Do I have to run a drop and create script individually on each table? I […]

SQL Service Broker – communication scenario – migration from SQL 2008 R2 to SQL 2014

Summary: Is there anything new in SQL Server 2014 (versus 2008 R2) that does not allow the following scenario? Can the same approach be transfered to the new server? The application uses SQL Service Broker to collect data from satellite SQL Express machines (technology computers, now two, soon 4, and possibly more) to the central […]

Use sql server Convert Function to convert hijri to gregorian date

I have simple table on my sql server and in my table have a date field,and into date field save a hijri date,i want use the sql server convert function to convert hijri date to gregorian date. how can i do this? i use this query in sql server: update k set time=CONVERT(datetime,GETDATE(),101) and i […]

Transpose Column to row on SQL Server 2008 R2

I write a query that need to use to export from asp.net SQL Command to Excel file but firstly i just want transpose column to row i tried to use pivot but it’s not working i write a query like this SELECT CATAGORY_NAME,GROUP_NAME,FUNCTION_NAME,’G’+TEST_CASE.GID + ‘-TC’+TEST_CASE.CASE_ID CASE_ID,CASE_NAME,CASE_NAME_TH,EXPECT_RESULT,EXPECT_DETAIL,INTERFACE,RESULT,TEST_DATE,TEST_BY,REMARK FROM TEST_CASE LEFT OUTER JOIN TEST_CATAGORY ON TEST_CASE.CID = […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.