Questions tagged [sql-server]

88157 questions
0

votes
1

answer
25

Views

Must declare the scalar variable “@tabvar”

declare @departmentid int; set @departmentid = 1; declare @tabvar table (id int, name nvarchar(100)) begin insert into @tabvar select DepartmentID, Name from HumanResources.Department where DepartmentID = @departmentid; print @tabvar end kindly assist on this and do the needful. It shows that Must...
Shinu Raju
0

votes
2

answer
40

Views

How can I make this stored procedure (SQL Server) easier to read?

Below is the pseudo code of my current stored procedure. First, I have a HUGE query that creates a temp table. Then, I have other queries inside the stored procedure that are querying that temp table. My goal is to make my stored procedure more readable. The HUGE query is making it difficult becaus...
Cooler
0

votes
1

answer
31

Views

SQL Server : remove duplicates and add columns

I have a table which has duplicate record this is how the table looks like. ID Date Status ModifiedBy ------------------------------------------ 1 1/2/2019 10:29 Assigned(0) xyz 1 1/2/2019 12:21 Pending(1) abc 1 1/4/2019 11:42 Completed(5)abc 1 1/20/2019 2:45 Closed(8)...
Abdul Shaikh
-1

votes
1

answer
16

Views

Add a 0 to a number given it has not already a leading 0

How do I write in SQL server Add a 0 to a number provided it has not already a leading 0 and has a length of 9 or 10 in total- ideally within a select statement I have something like this, can someone please help to correct this select [mobile] from tab WHERE ( RIGHT ('0' + ([mobile] (1)), 1) AND...
Anna
0

votes
0

answer
18

Views

Class cast exception with MSSQL drivers

I am trying to modify an existing legacy application by porting few queries and making them compatible with MSSQL server. Some part is already written and I am actually integrating TVP with Java code. But when I try to execute my code I get a weird exception java.lang.ClassCastException: com.micro...
Anand Vaidya
-1

votes
1

answer
19

Views

How to track changes in development environment in SQL Server

Our development team works on SQL Server and writes stored procedures for our product. We need something like a version control system for those procedures or any other objects. Sometimes I change a stored procedure, and someone else in my team changes it and I don't know any thing about it. Is...
Mili
0

votes
0

answer
17

Views

newline character display as space in sql server

I have table with a field of type varchar. I entered data to that field with a new line character. When i select i am not seeing newline.I am seeing space there. But for my friend i could see new line. Please help me find a solution to make it consistent. entered data - abc abc1 abc2 my output abc...
vinton
2

votes
1

answer
47

Views

Querying a view after creating a clustered index on it still yields the same query plan

I have the following view SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO ALTER VIEW web.vGridHotelBooking WITH SCHEMABINDING AS SELECT HBK_ID, COF_ID, COF_CST_ID, HTL_Name, COF_Data FROM web.HotelBooking INNER JOIN web.CustomerOfferBundle ON COF_ID = HBK_COF_ID INNER JOIN web.Hotel ON COF_HTL_ID...
Mihail Shishkov
1

votes
1

answer
9.1k

Views

SQL Server : error on converting varchar to decimal / float

I am getting an error while trying to convert the varchar to decimal. I found the place of error but unable to rectify it. The code I use is: SELECT CONVERT(DATETIME, dbo.PAYMENTS.recdate, 3) AS rdate, CONVERT(DECIMAL, dbo.PAYMENTS.amount), dbo.PAYMENTS.balance FROM dbo.PAYMENTS I got an error mes...
Bishu
1

votes
2

answer
2.2k

Views

How to use Function VB.NET Insert To Database?

i using function with vb.net. i'm query sql to datagridview and insert data from datagridview to Databse By function. But Error in function: The name 'EXHBK13004' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. i wa...
nettoon493
1

votes
1

answer
4k

Views

Incorrect syntax parsing :setvar in SQLCMD mode

I downloaded an AdventureWorks 2012 LT Script from CodeProject, but when I try to run it it says: Incorrect syntax was encountered while parsing :setvar. There's nothing more than this: :setvar SqlSamplesDatabasePath "C:\Program Files\...\DATA\"; :setvar SqlSamplesSourceDataPath "C:\Samples\...\";...
pangular
1

votes
1

answer
119

Views

T-SQL Select, ensure manager row comes before employee

Here is my problem. I have a table of employee data, which contains the username of the employee and their manager's username like so ______________________________________________ | employee_username | employee_manager_username| ------------------------------------------------ tom01 |...
Black Dynamite
1

votes
2

answer
1.9k

Views

How do I Copy Results With Headers from a 'View' in SQL Server 2008 R2 Management Studio

I'm having difficulties determining how to copy/export the headers for a 'View' I created. With my tables if I right click on the query results, I see an option to Copy with Headers as in this picture: But when I try the same for results from a custom view, I don't see the option to Copy with Header...
Rob G
1

votes
2

answer
1.3k

Views

Update or Insert value if a field is NULL

I know this is a very simple question but I just wanted to be sure. I have a Null value in a field, and I want to change that Null value. To change the Null Value Do I use Insert or update? For Example: Insert Into dataTable (Column 1) Values (1) WHERE 'Column 1' = NULL OR UPDATE dataTable SET 'C...
Hotlansy Httlandy
1

votes
2

answer
1.2k

Views

sql server stored procedure spatial data

I am new to Spatial data types and SQL server as well, I have the following tables: Table (A) ZipCodesUSA Columns: zipcodes| latitude| longitude| city| state Table (B) US_Cities_longitude_Latitude Columns: ID int| City nvarchar(40)| Coordinates GEOGRAPHY| What I am trying to do is to create an SPR...
simon
1

votes
2

answer
1.9k

Views

Date parameter in SQL Server stored procedure from ASP.NET

I've written an SQL Server stored procedure that, amongst other things, accepts a date parameter: CREATE PROCEDURE data.addScore ... (some parameters), @testDate date AS On an ASP.NET page I have a textbox with a Calender Extender control (from the AJAX Toolkit) that allows the user to select a date...
Katstevens
1

votes
2

answer
538

Views

Transpose SQL result set

I have data in the following format: Machine | week_end | widgets A | 05-26-2013 | 5 B | 05-26-2013 | 4 A | 04-01-2013 | 6 B | 04-01-2013 | 0 I want my result set to look as follows: Machine | 05-26-2013 | 04-01+2013 | ... A | 5 | 6 | B | 4...
BenDundee
1

votes
2

answer
61

Views

How to avoid doing two separate passes while querying through a table?

The context of this problem are hospital visits. The logic is a bit convoluted but these are the requirements. Table Visits contains the names and visitIDs for the patients. Table FilteredVisits contains certain visits that meet some criteria The report is run under a certain date range - this is im...
Gabe
1

votes
2

answer
3.4k

Views

Cannot see Databases In Visual Studio SQL Server Object Explorer after previous database dropped

I used to be able to see all databases in the SQL Server Object Explorer in Visual studio 2012. I dropped one of the main databases that I used to work with in VS, now I can no longer see any of the other databases. I can connect to the Server (SQL Server 2012) but the database tree is empty. Screen...
Asynchronous
0

votes
4

answer
39

Views

Selecting multiple values into single row - SQL server

I need to merge a table with ID and various bit flags like this ----------------- a1 | x | | x | ----------------- a1 | | x | | ----------------- a1 | | | | ----------------- b2 | x | | | ----------------- b2 | | | | ----------------- c3 | x | x | x | into such form --------...
Astroc
0

votes
1

answer
36

Views

Getting exact day values per month, between 2 dates

I am trying to get the amount of days in a month, between 2 dates. So this would ideally look like Month | Days Jan | 5 Feb | 28 March | 5 The dates need to come from a table that looks like this - Name | Age | Address | AddressStartDate| AddressEndDate Steve| 19 | randomAdd | 2018-01-26...
sam smith
-2

votes
0

answer
34

Views

MS SQL calculate storage per Day and Month

Need help, a lot of help actually. Our company charges storage to customers per pallet. so we will charge base on days or Months, depending on the client. for example i have a table Pallet. ID, PalletNumber, qty a table palletlog. PalletNumber, qty, action, date. when a new pallet is made or r...
Juan G
0

votes
1

answer
23

Views

Trailing spaces cant be removed in WHERE clause?

I need to list out the records with same records in column_A & column_B both are of varchar(50) type. But the issue is Column_A has trailing spaces in it, Even though both the columns having same data but it couldn't match. I tried to LTRIM(RTRIM(column_A)), REPLACE(column_A, CHAR(32), '') etc., bu...
Adithya Alapati
0

votes
0

answer
9

Views

How do I set a boolean value on a column that can be used in my MVC Web Application?

In my MVC web application I have a model class that talks to a SQL database view that is stored in my Azure SQL Database. The model looks like this: namespace GanttTest.Models { [Table("GanttTest")] public class Task { public int Id { get; set; } public string Text { get; set; } public DateTime? St...
Yanayaya
11

votes
3

answer
14.6k

Views

Using column name when using SqlDataReader.IsDBNull

Hello I have got this code which reads data from SQL DB. I don't know how should I edit it so I can use original column name and not column index. string query = "SELECT * FROM zajezd WHERE event='" + thisrow+ "' AND year='" + klientClass.Year() + "'"; SqlCommand cmd= new SqlCommand(query, spojeni);...
Marek
1

votes
3

answer
585

Views

Store results of SQL Server query for pagination

In my database I have a table with a rather large data set that users can perform searches on. So for the following table structure for the Person table that contains about 250,000 records: firstName|lastName|age ---------|--------|--- John | Doe |25 ---------|--------|--- John | Sams |...
kshep92
1

votes
3

answer
1.7k

Views

Accumulate rows in SQL: summarizing previous values and new grouped by date

In SQL Server 2008, If I have a table like this. Date NoOfCases -------------------- 2013-11-27 1 2013-11-28 2 2013-11-29 1 2013-11-30 3 And I want the previous sum to be added to the next row's value. That means that the output should be like this: Date AccumulatedNoOf...
guitarzero
1

votes
3

answer
92

Views

Optimization of an MSSQL Database

Briefly I would like to explain my project. I have already built an eCommerce website (ASP.NET C# MSSQL) where users are asked to register before payment (in case if they are not registered). Whenever a user register, 1 table is created automatically using C#. The purpose of this table is to store d...
Facbed
1

votes
2

answer
88

Views

Counting up a column incorrectly SQL

I am having a problem working out why a query I am writing is counting up the overall views for a page and not for each organisation, it should come out as 19 and 8 but when I run the query it comes out as 27 page view each the rest of the query seems to work fine. anyone got an idea why? Thanks Tho...
Thomas James
1

votes
1

answer
46

Views

Sql 2008R2 Max()

I have a function in which I have written the following lines. DECLARE @MaxLetterID BigInt; DECLARE @MaxObserverLetterID BigInt; DECLARE @MaxBillLetterID bigInt; SET @MaxLetterID = (SELECT MAX(LetterNO) AS Expr1 FROM Lab WHERE (LetterNO BETWEEN @Min AND @Max)); SET @MaxObserverLetter...
Mehrdad Kamelzadeh
1

votes
1

answer
1.1k

Views

how to load Text file Thorn(þ) delimited into sql-server 2012

I have text file something like this col1þcol2þcol3þcol4 aaaþbbbþcccþddd xxxþyyyþzzzþabs 123þtyehþyetþethrr i tried loading into sql-server using ssis package after following steps from here but i couldn't find solution,could somebody guide me in my execution The Package fails : here's...
TeamDataViz.
1

votes
1

answer
2.3k

Views

Keeping the size of ldf file under certain limit

I have scenario where ldf file on SQL Server grew to more than 30GB in course of 4-5 days. As this presents potential problem with space (the HDD is a bit more than 250GB) I searched few solutions but came baffled as anything that I find on shrinking of ldf file says that it will grew again. One sol...
Matija Kapić
1

votes
2

answer
150

Views

build an Sql query in c#

I need to pass a sql query to c#. Here is the query: select T010.A010_nom_ent A010_nom_ent from T010_ENTIDADE T010, T016_USUARIO T016 left outer join T307_CELULA_USUARIO T307 ON T307.A306_seq_celula = 0 and T307.A016_cod_usuario = T016.A016_cod_usuario where T010.A010_cod_entidade = T016.A016_cod_u...
user3107374
1

votes
1

answer
411

Views

Connectionstring issue. c# sql express 2012.

Here is my connection string. string connectionString = @"Data Source=.\SQLEXPRESS;Database=dbo.Employee; User Id=user1; Password=Password11"; I have a database called Employee with a table called [EVUSERS] in the dbo schema. I have created a database login under sql logins for this username an...
1

votes
1

answer
2.8k

Views

How can I perform DML operations in SQL Server?

I want to know how I can perform DML operations on a view composed of more than 1 table. Ie. if I have a view that joins three tables, is there a way I can can perform DML operations like UPDATE, DELETE, etc. on this view?
NoviceToDotNet
1

votes
3

answer
84

Views

Running total query in select statement without views

I have to query a set of running total data by month. e.g. Month Amount Total 2014-01-01 100 100 2014-01-02 100 200 2014-01-03 100 300 The application does not allow to create a view or SP. It is a...
guneverdie
-2

votes
1

answer
40

Views

how to find all strings in between commas in sql server

I want to display a string in a table format as shown below: For a string like 'hi,is,1,question,thanks,.,.,n' I need this result: column1 column2 column3 column4 ..... column hi is 1 question ..... n
manoj
1

votes
1

answer
17

Views

accept phone numbers of specific characters

In SQL Server, I want a code that allows to accept phone numbers starting with 056 or 078 and have a total of 9-10 digits. I tried: WHERE (SUBSTRACT([mobile], 1 , 3 ) in (056, 078) ) and (len([mobile]) between 9 and 10)) Why does this not work?
Anna
1

votes
3

answer
138

Views

How to delete a row with primary key id?

I have a SQL table user. It has 3 columns id(set as primary and Is Identity as Yes), name and password. When I enter data to the table the id became incremented. But on delete query only name and password will be deleted. I need to delete a particular row including the id. For example: id:1 name...
1

votes
2

answer
668

Views

How to get an email from SQL Server using C#

This is bugging me. Something so simple and it does not work. What could I be doing wrong? Here is the code I am using ... string strSQL = "SELECT ac.ContactEmail AS fldEmail FROM vwApprenticeshipContactDetails ac WHERE (ac.ApprenticeshipID = @ApprenticeshipID)"; SqlCommand cmd2 = new SqlCommand(str...
Fandango68

View additional questions