Questions tagged [sql-server]

123243 questions
1

votes
1

answer
4.4k

Views

How to download SSIS Packages from SSISDB

I'm still fairly new to SSIS. I've figured out how to download packages from a server by connecting to Integration Services. How do I download packages from SSISDB? Background: We have a TON of packages that the previous people created. All packages SHOULD be kept in the same folder structure on...
Rich Uchytil
1

votes
2

answer
22

Views

Get last remaining characters if the field lenght is greater than 5

I was wondering if there is a way to select the remaining characters from the varchar field if the field length is greater than 5. For example: ID_Num 99984 99923GF 99100EFK 99341LM 99145RL4C What I am trying to get: ID_Num GF EFK LM RL4C
Milan Pk
-2

votes
0

answer
21

Views

MSSQL Server doesnt allow Insert of list of multiple values

I tried this query in MSSQL-Server: INSERT INTO Buchungsquelle (buchungsquelle) VALUES (34776),(35025),(35027),(35036),(35079),(35080),(35081),(35082),(35101); however, I get: 'Wrong syntax near ',' - what am I doing wrong? EDIT: Table Creation statement: USE [Doublettencheckui] GO /****** Object...
user2774480
1

votes
1

answer
282

Views

Why does SQL Server have multiple cached plans for the same query?

If you select * from syscacheobjects you can see some simple stats about your database's queries. I liked to use this table to find queries to tune. I especially zero in on queries where usecounts = 1. Sometimes I see two rows for the same query and it's baffled me. NOTE: The queries are parame...
010110110101
1

votes
1

answer
240

Views

Azure SQL: Update Compatibility Level of [master] database

We have a single production database [ProdDatabase] on an Azure SQL Server instance that was created back in 2013. At the time of creation the compatibility level of [ProdDatabase] and [master] was hence set to 100 (SQL Server 2008). Over the past few weeks we've successfully updated the compatibili...
sammy34
1

votes
2

answer
1.3k

Views

Could not connect to sql server: SQL server Instance failure. (System.Data)

I get this error message when I try to connect to Sql Server =================================== Cannot connect to **SQL_ServerName**. =================================== Instance failure. (System.Data) ------------------------------ Program Location: at System.Data.SqlClient.TdsParser.Connect(Serve...
Justin Lessard
0

votes
2

answer
32

Views

How to pass string with single quotes to Sql Stored Procedure

i'm using a stored procedure how can i pass a string like 'White','Black','Green' i'm trying to execute procedure like var data = new List(); string colorstr = ''White','Black','Green''; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string sql = $'exec G...
Faisal
0

votes
2

answer
19

Views

Is there a way to 'Update' existing table with new user list than running entire query to replace table everyday?

I am running query in a job that updates user list everyday in MS SQL. Query below is running everyday to update the data if object_id('report.dbo.data') is not null drop table report.dbo.data SELECT UserID, date into report.dbo.data from data a where date >= '2019-01-01' and date < getdate() O...
0

votes
1

answer
15

Views

how to retrieve the values that contains a part of value in the column

Suppose i have a column lastName and i have the data as kumar yadav kumar shah raghu. kumar Now i want to write a sql query that to get the values whose last name contains kumar.
Pankaj 4 U
0

votes
0

answer
15

Views

Update Column when date in other table is older then today (done) + Insert new row in other table with ID from first table

I update the Table dbo.Auftrag if all related columnes from the child table dbo.Positionen are outdated with this SQL: UPDATE dbo.Auftrag SET Status = 5 WHERE NOT EXISTS (SELECT * FROM dbo.Positionen WHERE dbo.Positionen.FremdID = dbo.Auftrag.ID AND dbo.Positionen.Lieferdatum >= convert(date, getdat...
Zam89
1

votes
3

answer
144

Views

Why SQL query with SHA2_256 returns NULL in WHERE clause

I need a hand on this, any comments will be highly appreciated. I have a table Users with field UserPassword with datatype varchar(60) I insert on the table users from this select SELECT HASHBYTES('SHA2_256','test') it insert its value (0x9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F...
1

votes
2

answer
452

Views

Can I use SQLCMD Commands in Visual Studio Database Project Stored Procedure?

I have a lot of SQL that is the same over multiple stored procedures. For example most procedures have the same variables declared, and is in the same try catch block to handle errors. I'd like to use the :r command so that I can write this code in one file and just import it into each sp. I can use...
Samuel O'Connor
1

votes
4

answer
321

Views

Laravel - whereHas checking latest record of a relationship without checking others

I have two tables that has One to Many relationship. Bookings - (id) booking_tasks - (id, booking_id,user_id) one booking has many task one task has one booking Booking Model: public function tasks() { return $this->hasMany('App\BookingTask'); } BookingTask Model public function booking() { retu...
ashanrupasinghe
1

votes
1

answer
176

Views

write unicode data to mssql with python?

I'm trying to write a table from a .csv file with Hebrew text in it to an sql server database. the table is valid and pandas reads the data correct (even displays the hebrew properly in pycharm), but when i try to write it to a table in the database i get question marks ('???') where the Hebrew shou...
Dror Bogin
1

votes
2

answer
249

Views

Azure SQL Database DTU metrics

I'm using Azure's metrics chart to see if it's feasible to merge two databases into one Pool. The two databases are currently in tier S3. The first chart is the result with 'DTU Used' metric using MAX() aggregation function in the last 30 days. It can be observed that during several intervals the 'D...
DuroForce
-1

votes
2

answer
28

Views

How to make a query to check the value of a field

I have this table: ----------------------------------------------- code intvalue checkrole result ----------------------------------------------- A01 14 A02-A03 true A02 24 A04 false A03 10 A04 false A...
Renzo
0

votes
0

answer
9

Views

Classic ASP connection works; .Net fails. Why?

This works in a Classic ASP Web site: sConnString = 'Provider=SQLOLEDB;Data Source=MySerer;Initial Catalog=MyDB;Integrated Security=SSPI;' In a .Net web site, the following connection string gets a 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'' error. Both run in the same Application Pool on...
Scott Pendleton
0

votes
1

answer
26

Views

Change “Allow access to Azure services” settings of Azure SQL Server using powershell

I want to modify (enable/disable) 'Allow access to Azure services' settings of Azure SQL Server using powershell, based on a value from my JSON configuration I do have referred couple of StackOverflow, all the answers are to create a firewall rule '0.0.0.0' to enable and '255.255.255.255' to disable...
Manjunath Rao
-1

votes
0

answer
13

Views

2 sql statements - Insert and Update with different tables ASP Classic

I have one table for an insert statement (table1) and a different table for an update statement (table2). Tried many versions of arranging the code and still can't get results. Table 1 does inserts but the update statement in table 2 doesn't. So to be clear, Table 1 inserts and Table 2 doesn't upda...
AspClassic-Guy
1

votes
2

answer
49

Views

In SQL Server can I implement the avg function manually? problem passing the name of the column to use

In SQL Server I want to create a function similar to the avg where I receive the column name as a parameter and query a table to get the values for the specified column. Maybe this is not possible as I saw that I cannot use exec for the common solution outside functions that is to concatenate the qu...
Afonso R.
1

votes
2

answer
49

Views

How to do this in SQL? (run a query over a window instead of just a single aggregate)

Let's say I have some data with timestamps yyyy/mm/dd hh:mm:ss and some error stages (1 meaning an error has occurred). I have the data loaded in a dataframe I call df and want to compute Time_To_Next_Error (measured in seconds) a new column Time_To_Error based on the timestamp and Error_State. Ti...
MrL
1

votes
2

answer
69

Views

Microsoft DTC not playing nice with a transaction in a sproc

I’m running into an issue where the DTC is not playing nice with me. I have two servers, who we'll refer to as Server A and Server B (different versions, different physical machines, same domain, and same network). I have two sprocs on Server A which do something identical, namely, get data from...
Eli
1

votes
2

answer
53

Views

Using ISNULL on date column

I’ve got a date column where some rows have got NULL values. I would like to use ISNULL or something like that to substitute those values with something like ‘N/A’, however, when I try to use ISNULL, I get an error due to two different data types. If I try to convert my Date column to VARCHAR...
Redraidas
1

votes
3

answer
32

Views

Calculate row difference within groups

I'm looking for help with calculating the difference between consecutive ordered rows within groups in SQL (Microsoft SQL server). I have a table like this: ID School_ID Enrollment_Start_Date Order 1 56 1/1/2018 10 1 56 5/5/2018 24 1...
Zoe
1

votes
2

answer
48

Views

Poor insert performance when inserting records into a table with with GUID non-clustered PK

I'm benchmarking the insert performance of SQL Server 2017 on a table with nonclustered primary key (GUID) and an additional clustered index (int). While I expected some performance degradation comparing to PK of autoincrementing ints, the metrics I'm getting don't seem reasonable - the insert to th...
Thinkhoop
1

votes
3

answer
44

Views

Select from multiple rows based on column value

I've got a table with rows of info on various units. This info is sourced from two locations, A and B. Source A contains info from every unit in the table, while source B contains more accurate info but only for some of the units. How would I go about selecting one row for each unit, but prioritizin...
Steven F
1

votes
2

answer
28

Views

SQL Query SUM and Divide by Distinct Date Count

I assistance, I am looking for the sum of a data field and then want to divide it by the number of distinct dates in that field. SUM(CASE WHEN dateResolved IS NOT NULL THEN 1 ELSE 0 END) / DISTINCT(dateResolved) AvgPerDay If there are 32 dates in dateResolved, with 5 distinct dates, I want it to r...
stacey528
1

votes
2

answer
43

Views

SQL Server: multiple select statements from the same table in one query

I'm working on two queries that pull data from one table. I can get them to run how I want individually, but I'd like to combine them into one if possible. I've tried the solution in [Multiple COUNT SELECTS from the same table in one query but wasn't able to get it figured out. Below is what I'm wor...
Jeremiah Williams
1

votes
2

answer
67

Views

Delete 130 million rows

In SQL Server 2012, I need to delete 100+ million rows from a table with 4-5 indices. How can I proceed? I guess that the transaction log will be full and thus cancel the transaction. Thanks for your help
Badr Erraji
1

votes
3

answer
51

Views

Join Lookup from 1 table to multiple columns

How do I link 1 table with multiple columns in another table without using mutiple JOIN query? Below is my scenario: I have table User with ID and Name User +---------+------------+ | Id | Name | +---------+------------+ | 1 | John | | 2 | Mike | | 3 | Cha...
Jacky
1

votes
3

answer
49

Views

Search by one query in prodecure

In my website I have one field for search by name, zip, guid etc. Now it works only for name. I wrote a simple query where (funeral_home_name like CONCAT('%', @searchFilter ,'%')) OR (funeral_home_zip5 like CONCAT('%', @searchFilter, '%')); and it works fine, but I need to insert this code into exis...
1

votes
2

answer
34

Views

Interview question help on relatively basic JOIN and subqueries

I was asked to: 'Print the following sequence of columns for each plant that only blooms in one type of weather. WEATHER_TYPE PLANT_NAME' Schema PLANTS (table name) PLANT_NAME, string, The name of the plant. This is the primary key. PLANT_SPECIES, sting, The species of the plant. SEED_DATE, dat...
1

votes
3

answer
55

Views

How to get the length one column in all tables in one database?

I am having trouble to get the max length of records in one column in all tables. I would only like to display the max length for each table for the specific column. Below is what I have tried, I already found the way to return the column I need, but now, i need to get the max len. I know this is no...
MichaelJ
1

votes
1

answer
31

Views

Merging dummy/placeholder data into a SQL query

In our SQL Server 2016 database we have a Payments table which records monthly payments made by our customers, but as they do not necessarily pay every month we may have missing month data. I now need to insert the missing monthly payment data (i.e a zero payment) for each customer for an SSRS rep...
nzmike
1

votes
2

answer
47

Views

Getting column information from system MS SQL Server

When I am using the below SQL statement to retrieve the column information of a SQL Server database. I am getting more columns than there actually are I presume this is because of the system columns that are there also. SELECT c.name Field, t.name Type, c.Precision, c.Scale, c.max_length, c.is_nu...
Dave
1

votes
2

answer
62

Views

Search list of values including range in SQL using WHERE IN clause with SQL variable?

I am trying to implement search functionality with list of values in SQL variable, including range. Appreciate any guidance/links pointing to correct approach for this. Below is the dataset: CREATE TABLE [dbo].[Books] ( [ID] [NCHAR](10) NOT NULL, [AUTHCODE] [NCHAR](10) NULL, [TITLE] [NCHAR](10) NULL...
variable
1

votes
2

answer
49

Views

How to prevent NHibernate SqlDateTime overflow when querying the database without using an IUserType?

Is it possible to prevent this exception when doing an NHibernate Linq query like the following: var dateFilter = DateTime.Parse('1500-01-01'); return _session.Query().Where(l => l.Date > dateFilter).ToList(); Result: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59...
Martin D.
1

votes
3

answer
27

Views

Removing part of string after specific character (from right to left)

I want this to happen: (left side is the full string. on the right side, after the '---->' is the output I want to get) 3.4.2.12 -----> 3.4.2 3.4.2.1-------? 3.4.2 I'm getting this: 3.4.2.12-----> 3.4.2. (note the '.' in the end) 3.4.2.1-----? 3.4.2 (this is good for me) I used this: select LEFT(...
Shawn Bitton
1

votes
1

answer
41

Views

Selecting separated data from string as singe rows in MSSQL

I have a table of a legacy system with structure and data like this: id data 1 some|data|I 2 need|to|get 3 out 4 of|the|database I need some way to get this data as single rows in a query like so: some data I need to get ... and so on. Anyone got an idea on how to do that? Thanks a...
Stefan Wolf
1

votes
2

answer
99

Views

What are possible mistakes when connecting to SQL Server from C#?

I created a login form in C# connecting to a SQL Server 2008. And the data reader has an error. What should I do? This is my project for finals SqlConnection con = new SqlConnection(@'Data Source=SAMSUNG-PC\SQLEXPRESS;Initial Catalog=LOGIN;Integrated Security=True');//this is my sql pc server name S...
Gabbierutt

View additional questions