[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft SQL Server

162K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Share tech news, updates, or what's on your mind.

Sign up to Post

I need regular expression for following in Sql Server ( I hope it is same in vb.net or c#)
I am looking for a pattern  someletters somenumbers  some letters- someletters somenumbers  some letters     ( All capital letters)
  examples  AA111WW-B123SS
                      AB1DFG-S3ERT

   Another pattern I am looing is   somenumbers someletters somenumbers -someletters
     examples   11AAA123-SDFG
                          1AB23-AB
1
Why Diversity in Tech Matters
LVL 12
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

I have a sqlcmd command which I would like to not execute manually in the cmd window or in a Windows batch file, but to run as a SQL Server job, but putting the sqlcmd command line in the job results in an error on every single part of the command line, although the SQL Server doc gives an example of a sqlcmd call as a job step. So I'm a bit lost.

My SQL Server agent job looks like this: 1 step with the following content:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe" -S MyServerName -i "C:\temp\My T-SQL.sql" -v DWH = MyDBName -o "C:\temp\My T-SQL Log.txt"

When parsing this, the job window already complains about the -S bit. If I eliminate that, it complains about the -i bit, so clearly, something is completely wrong with the whole thing, but what ? Must be simple, but I can't figure it out. And, needless to say, the job runs but fails with the laconic error message "incorrect syntax near" and mentions the sqlcmd.exe part of the call.

Can someone help me ?

Thanks
Bernard
0
Hello All;

I have a table where the column is a varchar.
(If it needs to change to an INT or Date, please let me know and I will do so.
It is not too late to make that change.)
In the column named = Years
It has the following example.

1978
1965
1985
2017
1962
1998
2012
2002
1958
1978

OK, What I need to do is the following.
For the years in the table, they need to output as

1950
1960
1970
1980
1990
2000
2010
etc...

Thanks.
Wayne
0
I have the below query, which I've simplified as far as I can and still retain the warning:

SELECT LU.ID, CND.DataValue AS ClosedNotifiedDate
FROM  tblClientLoanDataDates AS CND
INNER JOIN tblLoanUnits AS LU 
ON LU.ClientLoanID = CND.ClientLoanID 
AND CND.ClientLoanID = 124880

Open in new window


The join between these two tables has a "No Join Predicate" warning:

NoJoinPredicate.png
These are both physical tables, the ClientLoanID is a basic int in both tables, there are no functions/UDF/views involved here.  I have to run this query in 2012 compatibility mode, as going past that is slowing down our database so much that it becomes unusable for the company after about 20 minutes.  I believe the new cardinality estimator is having issues with this for some reason.  I can offer up any more information that would help to solve this, but I can't think of anything else of value at the moment.
0
Hi Guys,
I have got the requirement to get the maximum from a table which needs to perform the calculation first and then get the maximum. for example

             DECLARE @table TABLE (TableValue VARCHAR (50))
             INSERT INTO @table VALUES ('(1+200)/100'),
									   ('150/100'),
									   ('34'),
									   ('(1+5)/2 + 7');

Open in new window

How do i do the calculation first and then get the maximum from this table. I am using SQL Server 2014.

Thanks in advance
0
I'm using Sql server 2008

I have this query:

SELECT [Column1]
      ,[Column2]      
      ,Column3 = REPLACE(CONVERT(VARCHAR(MAX), Column3), '<', '< ')
      ,[Column4]
FROM [DMS].[dbo].[TestTable]


Column3 is ntext datatype.
Column 3 holds notes that are typed into a textbox.

With the existing query above, if column 3 contains a value like this:

test notes <br/> this is a test

then it changes it to this(it adds a space after the less than sign):

test notes < br/> this is a test


How do i revise my existing query so that if the value in column 3 has a less than symbol followed by a space already,
then don't add a space after it.

Only add a space after then less than symbol, when it's followed by any character other than a space.
0
I'm working in SQL Server 2012 and I'm having difficulty casting varchar as int for comparison between two tables.  

Here's some example DDL/DML:

CREATE TABLE #tbl1 (
      col1 int,
      col2 int
      );
CREATE TABLE #tbl2 (
      col1 varchar(255),
      col2 varchar(255)
      );
INSERT INTO #tbl1 (col1, col2)
VALUES (1, 2);
INSERT INTO #tbl2 (col1, col2)
VALUES (1.0, 2.0);

SELECT * FROM #tbl1 as t1
inner join #tbl2 as t2
on t1.col1=CAST(t2.col1 AS int)
where t1.col2=CAST(t2.col2 AS int)

And the error I'm getting:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2.0' to data type int.

If I do this:
SELECT 1.0 AS Original, CAST(1.0 as int) Casted
I get this:
-----------------------------
| Original |Casted |
-----------------------------
|      1.0     |    1       |
------------------------------
which is what I would expect.

I think I'm missing some basic foundational concept here.  Any help will be greatly appreciated.

Thanks!
1
i'm using sql server 2008.

I have a table with a column that contains some text

So my column contains some text like this:

Column1
Test note 1
Test note 2 <br />
Test note 3
Test note 4 <br /> this is a test
Test note 5 another test
Test note 6 this is a test

In some of the records there is this html tag <br /> inside the notes.

With an update statement how do I update the records that contain the less than symbol and replace it with a less than symbol followed by a space.
So then after running the update command I want my table values to look like this. Notice how a space got added after the less than symbol.:

Column1
Test note 1
Test note 2 < br />
Test note 3
Test note 4 < br /> this is a test
Test note 5 another test
Test note 6 this is a test
0
Hello, All;

For SQL Server 2016.
I used to have this code lying around somewhere, but I am unable to find it.
OK.

table (SomeNames)
Johnny
Carr
Wayne
Ronda
Valerie
Tanya
Joey
Vin
Ron
Tony

OK, in the above, I would like to have a list of the Alphabets
Since there is NOT the whole list of letters to comprise the entire Alphabet, it would only show the letters for the names that are available.
For the example above, would be.


C
J
R
T
V
W

Once there are more names added which contain the other letters in the alphabet, then it will then display them letters.
And what I am going to do, is have each letter as a hyperlink on the page, to display all the names for that letter.

Hope this is clear.
I wish I knew where the code was at that I once have.
It is probably buried in a project script, of which I do not remember the name I gave it.
As it was about 5+ years ago, that I remember using it last, as it was at my old home.

Thanks all;
Wayne
0
If this works

        Dim strRootNodeName As String
        Dim dt As New DataTable

        Using cn As New SqlConnection(GlobalVariables.strConn)
            Using cmd As New SqlCommand(m_strNameLookupSQL, cn)
                cmd.Parameters.Add("@ItemId", SqlDbType.Int).Value = m_RootNodeId
                Using da As New SqlDataAdapter(cmd)
                    da.Fill(dt)
                    strRootNodeName = dt.Rows(0)("ItemName")
                End Using
            End Using
        End Using

Why would this not work?

        Dim strRootNodeName As String

        Using cn As New SqlConnection(GlobalVariables.strConn)
            Using cmd As New SqlCommand(m_strNameLookupSQL, cn)
                cmd.Parameters.Add("@ItemId", SqlDbType.Int).Value = m_RootNodeId
                Using rdr = cmd.ExecuteReader
                    rdr.Read()
                    strRootNodeName = rdr("ItemName")
                End Using
            End Using
        End Using
0
Acronis True Image 2019 just released!
LVL 1
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Experts,

**I work in MS Access. So I don't have access to query performance tools**.

Anyone know a best practice for dealing with joins on text fields?

If I have many data sets in the 500k to 1mil record range with a Cost Center field that is Text data type because the Cost Centers are 10 char with 4 numbers, a letter,  and 5 numbers in the format xxxx?xxxxx where x is a number and ? is a letter. I always both index and do joins on this field. My understanding is that joining fields with the numeric data type is more efficient than on text fields,  so I'm considering, to improve performance, creating an additional CostCenterID field with the letter removed so it would be a 9 length number of the Long data type. It does not appear that removing the letter will create any duplicate values.

My question is what is the best way to do this?
Lets say I have two tables:
Table A has 1mil records and a Cost Center Field of Text data type.
Table B has 25k records and a Cost Center Field of Text data type.

I could add a calculated CostCenterID field to my source tables with an Update Query using a formula such as Left([tA]![Cost Center],4) & Right([tA]![Cost Center],5) AS CostCenterID. The new field would be a 9 length number of Long data type.

I don't like altering my source tables but this seems like it would work . How do db admins typically handle this kind of problem?

Thanks
0
I don't know why I'm getting these rows.

I have a sql statement like this. I want to find the rows with datetime of 10/24/2018 at 12:04

declare @d datetime = '2018-10-24 12:00:16.350'
declare @d2 datetime = '2018-10-25 12:04:16.350'


SELECT * FROM dbo.Unit
WHERE LastModified >= CONVERT(DATE, @d, 101) AND
LastModified  <=  CONVERT(DATE, @d2, 101)

I run it and the data I get has "lastmodified" that looks like this. It has time of  08:35:46:640, for example. Why is this?

datetime.png
0
All,

If a SQL ldf file is set to grow by 1GB, what does that really mean?

Does it mean that it will allocate 1GB every time it writes to the file?

See attachment.SQL Auto growth
0
Hi All,

I have below script:

USE YSL_TRADING

DECLARE @NomorFkt VARCHAR(MAX) = 'YSL-2018-01-0001'
	DECLARE	@TahunBulan CHAR(6) = '201801'
	DECLARE	@LoginUserId CHAR(10)= 'ADMINIST'

	DECLARE @TipeJurnal VARCHAR(3) = 'JL'
	DECLARE @Year CHAR(4) = LEFT(@TahunBulan, 4)
	DECLARE @Month CHAR(2)  = RIGHT(@TahunBulan, 2)

 IF OBJECT_ID('tempdb..#THFKTPOJUAL') IS NOT NULL 
	 BEGIN
     DROP TABLE  #THFKTPOJUAL
     END

  
     CREATE TABLE  #THFKTPOJUAL (
                Nomor_Fkt CHAR(30)
				, Tgl_Fkt DATETIME
				, Nomor_OA CHAR(30)
				, MMCode VARCHAR(5)
				, CustCode VARCHAR(10)
				, Nilai_Fkt MONEY
				, Biaya_Angkut MONEY
				, Nilai_PPN MONEY
				, Uang_Muka MONEY
				, Nilai_Kurs MONEY
				, FakturUangMuka CHAR(1)
				, CrtId VARCHAR(10)
				, CrtDate DATETIME )


	IF  @NomorFkt = ''
	BEGIN

	    INSERT INTO  #THFKTPOJUAL
	    SELECT
		A.Nomor_Fkt
		, A.Tgl_Fkt
		, A.Nomor_OA
		, B.MMCode
		, B.CustCode
		, A.Nilai_Fkt
		, A.Biaya_Angkut
		, A.Nilai_PPN
		, A.Uang_Muka
		, A.Nilai_Kurs
		, A.FakturUangMuka
		, A.CrtId
		, A.CrtDate

		FROM THFKTPOJUAL A
		INNER JOIN THPOJUAL B
		ON A.Nomor_OA = B.Nomor_OA
	    WHERE 1=1
	 AND YEAR(A.Tgl_Fkt) = @Year
	 AND MONTH(A.Tgl_Fkt) = @Month
	 AND ISNULL(FakturUangMuka, '') <> 'Y' 
	 AND B.FromTipeTransaksi = 'SO'

	END
	ELSE
	BEGIN
	   
	   INSERT INTO  #THFKTPOJUAL

	    SELECT
		A.Nomor_Fkt
		, A.Tgl_Fkt
		, A.Nomor_OA
		, B.MMCode
		, B.CustCode
		, A.Nilai_Fkt
		, A.Biaya_Angkut
		, A.Nilai_PPN		
		, A.Uang_Muka
		

Open in new window

0
Hi All,

I have below script :

select
CostNoSeq 
, TipeTransaksi 
from TMTRANSACTION 
where appid = 'inv'
and TipeTransaksi is not null
and CostNoSeq is not null
ORDER BY COSTNOSEQ

Open in new window


CostNoSeq	TipeTransaksi
1	BS 
2	GR 
3	SR 
4	SIA
5	STI
11	SJ 
12	BRM
14	SOA
15	STO

Open in new window


How could I make the result into : BS GR SR SIA STI SJ BRM SOA STO ?

Thank you.
0
I am trying to convert this SELECT statement into an UPDATE statement. The premise of this statement is that it looks at lines on a purchase order and gets the quantity ordered(QTYORDER) and then looks at all the associated lines on sales orders that make up that quantity(QTYONPO).

SELECT * FROM
(SELECT PONUMBER,ORD,QTYORDER,QTYCMTBASE,QTYUNCMTBASE
FROM POWMT..POP10110) T
INNER JOIN
(SELECT T1.PONUMBER,T1.ORD,SUM(T2.QTYTBAOR) AS QTYTBAOR,SUM(T2.QTYONPO) AS QTYONPO
FROM POWMT..SOP60100 T1
INNER JOIN POWMT..SOP10200 T2 ON T1.SOPNUMBE=T2.SOPNUMBE AND T1.SOPTYPE=T2.SOPTYPE AND T1.LNITMSEQ=T2.LNITMSEQ
INNER JOIN POWMT..POP10110 T3 ON T1.PONUMBER=T3.PONUMBER AND T1.ORD=T3.ORD
GROUP BY T1.PONUMBER,T1.ORD) T2
on T.PONUMBER=t2.PONUMBER and T.ORD=t2.ORD
WHERE T.QTYCMTBASE<>T2.QTYONPO
ORDER BY T.PONUMBER,T.ORD

What I am trying to do is update the QTYCMTBASE on the PO line to the corresponding QTYONPO from all the associated lines on sales orders. I am also looking to set the uncommitted quantity column(QTYUNCMTBASE)  to the total quantity on the PO(QTYORDER) - committed quantity(QTYONPO).

UPDATE POWMT..POP10110
SET QTYCMTBASE=T2.QTYONPO,QTYUNCMTBASE=T.QTYORDER-QTYONPO
FROM
(SELECT PONUMBER,ORD,QTYORDER,QTYCMTBASE,QTYUNCMTBASE
FROM POWMT..POP10110) T
INNER JOIN
(SELECT T1.PONUMBER,T1.ORD,SUM(T2.QTYTBAOR) AS QTYTBAOR,SUM(T2.QTYONPO) AS QTYONPO
FROM POWMT..SOP60100 T1
INNER JOIN POWMT..SOP10200 T2 ON T1.SOPNUMBE=T2.SOPNUMBE AND T1.SOPTYPE=T2.SOPTYPE AND …
0
I am trying to convert height in inches to feet and inches to display on a SSRS 2012 report  like 5"8'  from 69.00 inches. I have the formula below that isn't showing up correctly the digits to the right of the decimal. For 69.00, it shows 5"0'.

HGT = concat(convert(int,min(HGTTO)/12),  '"', convert(int,min(HGTTO)/12) % 1,'''')

Thanks
0
Hi,

I got help from several EE. I need help again. Please see the file attached. This is what I have so far (I know) lol

select sum(case when balance between 0 and 100 then 1 else 0 end) as '<100',
       sum(case when balance between 101 and 1000 then 1 else 0 end) as '101-1000',
       sum(case when balance between 1001 and 10000 then 1 else 0 end) as '1001',
       sum(1) as total
from aaa

Open in new window


Please have a look. thanks
Book1.xlsx
0
In MS Access sql, is one of the following query methods more efficient the other? I'm joining two tables on a cost center field.
-Table names are tGFRS and tIQN.
-In the tIQN table only, the Cost Center field has multiple values for some employees separated by semicolon. For example, 5545Q7654; 4675Q2332; 9087Q2090

My business rule is to only use the right-most Cost Center

Is there any difference in query efficiency if I write the query to filter a Cartesian product? Version A:
SELECT qIQN.*, tGFRS.Rate
FROM qIQN, tGFRS
WHERE (((tGFRS.Bu)=Right([qIQN]![CostCenter],10)));

Open in new window

or version B, where use two queries in succession: first to parse out the right most cost center and then do an inner join
SELECT qIQN2.*, tGFRS.Rate, 
FROM tGFRS INNER JOIN qIQN2 ON tGFRS.Bu = qIQN2.CostCenter;

Open in new window


Thanks
0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Hi,

Please see the file attached. I have a table that contains Client name and their balances. I would like to show the client name, count, balance and % of total balance. I properly could do this with several nested select query. I was wondering if there is a better way? maybe pivot or some sort?

thanks
Book1.xlsx
0
The following query below returns one line of results as you can see from the code.  Sometimes all the output is NULL values because no records exist for the prescribed dates.  In the case where the resulting output is all NULL how can I have it put a "0" instead of NULL values?

With CTE_UNDUPLICATED as       
		(SELECT Distinct h.RegID, r.AgeCurrent, R.Race
		
        FROM tblOrgRegistrations AS r
        INNER JOIN tblOrgHours AS h 
        ON h.RegID = r.RegID 
			And ActivityDate >= '7/1/2018'
			And ActivityDate <= '09/30/2018'
			And RegDate >= '7/1/2018'
			And RegDate <= '9/30/2018'

		Where h.Agency = 'Administrator')

		SELECT
		SUM(CASE when R.AgeCurrent = 11 then 1 else 0 end) as [Ages 11]
       ,SUM(CASE when R.AgeCurrent = 12 then 1 else 0 end) as [Ages 12]
	   ,SUM(CASE when R.AgeCurrent = 13 then 1 else 0 end) as [Ages 13]
	   ,SUM(CASE when R.AgeCurrent = 14 then 1 else 0 end) as [Ages 14]
	   ,SUM(CASE when R.AgeCurrent = 15 then 1 else 0 end) as [Ages 15]
       ,SUM(CASE when R.AgeCurrent = 16 then 1 else 0 end) as [Ages 16]
	   ,SUM(CASE when R.AgeCurrent = 17 then 1 else 0 end) as [Ages 17]
	   ,SUM(CASE when R.AgeCurrent = 18 then 1 else 0 end) as [Ages 18]

	   From CTE_UNDUPLICATED H

	   inner join tblOrgRegistrations R on H.Regid = R.RegID

Open in new window

0
What is the best way to debug a Microsoft SQL Server 2014 table UPDATE trigger inside of Microsoft SQL Server 2014 Management Studio ?

https://stackoverflow.com/questions/22033109/how-to-debug-a-t-sql-trigger acts like maybe the below ?
 1. set a BREAKPOINT on insert into X(ID) values (1),(2)
 2. run insert into X(ID) values (1),(2) in DEBUG mode
 3. press F11 to "Step Into"
0
Can some please help me with a code snippet to extract specific text from a string Using T-SQL.  In the case of the strings below, I want to extract text between the '=' sign and '&' sign. Sometimes the '&hl=en' is missing from the combination

https://play.google.com/store/apps/details?id=com.barclays.android.barclaysmobilebanking&hl=en
https://play.google.com/store/apps/details?id=uk.co.hsbc.hsbcukmobilebanking
https://play.google.com/store/apps/details?id=org.coop.associatedschoolECU.coopmobile
https://play.google.com/store/apps/details?id=org.coop.associatedschoolECU.coopmobile&hl=en

Open in new window


Expected result
com.barclays.android.barclaysmobilebanking
uk.co.hsbc.hsbcukmobilebanking
org.coop.associatedschoolECU.coopmobile

Open in new window

0
Hi All,

In sales module, there are :

1. Sales
2. Sales Return

The scenario by sequence and at the same date:

First :
1. Stock In
2. Sales
3, Sales Return, Return from Sales at point 2.

Second :

1. Sales Return, Return from Sales at point 2.
2. Sales, stock from Sales Return at point 1.

Display format :

Seq   Date  Transaction No  

The problem, for the 2 scenarios above, how to query it ?

Thank you.
0
Looking for a suggestion. Trying create a view of a table to try show item usage. I have a table that shows item usage by period and year like so:

Item, Year, Per1, Per2, Per3...

Bike, 2016, 3, 2, 6
Bike, 2017, 5, 3, 1
Bike 2018, 3, 0, 0
Skateboard, 2016, 5, 3, 1
Skateboard, 2017, 5, 2, 1
Skateboard, 2018, 6, 8, 3

The production manager is looking to run a report monthly that shows usage ove rthe last 3 months (for this month it would be Per9,10,11). I can'd figure out how to filter so it only shows 2018, 9, 10, 11. I could just look at the month number and say current year, with month greater than 8 but that wont work for jan, feb, mar. This Jan would be 2018,11,12 and 2019 1...

Is there a way to create a view that shows the data differently so we can simply say today() - 90 days or something like that?
0

Microsoft SQL Server

162K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.