Tag: sql-server

Conditional Count On Row_Number

I have a query that calculates the number working days within a month based on a table which stores all our public holidays. The current output would show all working days, excluding public holidays and Saturday and Sunday, I would like to show each day of the month, but don’t increment on a public holiday […]

Parameters issue with Insert Statement

I get this error message from my Try catch: The parameterized query ‘(@UserID int, @date date, @BreakfastLevels float, @LunchLevels floa’ expects the parameter ‘@UserID’, which was not supplied The code is designed to take values put into a data grid view from a form and insert them to the table in the database however I […]

How to avoid 'number stored as text' error when exporting data from SQL Server to Excel using SSIS DatFlowTask

I am using a SSIS Data Flow Task to export data from SQL Server to an Excel destination, but while exporting it converts int values to “Numbers Stored As Text” Excel cells and every cell gets this error “number stored as text” with a green tag. Can you please guide me how I can export […]

I have created a generic database class to call database (SQL Server) from C#

I am new to .net. Please help me what the things should I add here? How to add stored procedure? My aim is to keep DataAccess class in one project and use it in other project as .dll. In other project I just want to call dbclass and to add parameter, execute method only. Please […]

.NET C#: How to dislay foreign key property?

I have this model called Station and ServiceLevel. Station contains a foreign key called ServiceLevelId which refers to a primary key in the ServiceLevel table. But I can’t show the ServiceLevel properties which is a part of the Station. I can present the station properties in the view by writing: @Html.DisplayFor(model => model.Name) But it […]

Enforce naming conventions on sql server

Is there any way to enforce naming conventions on sql server regarding Primary Keys /Forign Keys / Default Values .. etc’ for example : PrimaryKey – PK_TableName_FieldName ForignKey – FK_TableName_FieldName Default Value – DF_TableName_FieldName … in addition if possible to prevent adding default value on table field without creatring a defined constaint ( prevent auto […]

Trigger MSSQL – Output to File (XML)

we have a very old ERP system which is badly supported. Now the warehouse want´s to buy a new “store system” for our goods. It´s a fully automatic store system which need´s data from our ERP system. The support of your ERP system can´t help us, so we have to build a solution of our […]

Records not shown while selecting from table in mssql

I’m Getting total tables in whole database and its row-count from following query: SELECT SCHEMA_NAME(A.schema_id) + ‘.’ + A.Name, SUM(B.rows) AS ‘RowCount’ FROM sys.objects A INNER JOIN sys.partitions B ON A.object_id = B.object_id WHERE A.type = ‘U’ GROUP BY A.schema_id, A.Name Order By ‘RowCount’ desc After that i’m getting below result: And then finally when […]

SSIS – Filter duplicate rows

I have a table (Id, ArticleCode, StoreCode, Adress, Number) that contains duplicate entries based on only these columns [ArticleCode, StoreCode]. Currently I can filter duplicate rows using Aggregate transformation, but the problem is in the output rows I have only two columns [Article, StoreCode] and I need the other columns as well.

SQL Query – Get distinct count of two tables

Table 1(student_details) Is having 532 datas with different advisor each data is assigned to one advisor like id advisor — ——- 1 Bala 2 Shanmugam 3 Priya 4 Bala etc.. Similaly in table 2:training_details id advisor — ——- 1 Bala etc.. I tried this query this will return count of people assigned to each advisor […]

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