Kategori arşivi: SQL

SQL de türkçe karakter yüzünden select sorgusunda convert fonksiyonu hata veriyorsa:

Yayın tarihi
Cevapla

Hata: Arithmetic overflow error converting numeric to data type numeric.

Hatanın çözümü COLLATE .

SELECT S.FACTOR,S.*
, CONVERT(NUMERIC(3,1),S.FACTOR) AS PS
FROM IASBOMVARASSIGN S , IASBOMITEM I
WHERE S.CLIENT = ’00′ AND S.COMPANY = ’01′ AND S.MATERIAL = ‘HLB222K’ AND S.ATTRIBUTEKEY = ’02′
AND S.OPTIONKEY = ‘HĞH’
AND I.BOMITEM = S.BOMITEM AND I.CLIENT = S.CLIENT AND I.COMPANY = S.COMPANY
AND I.PLANT = S.PLANT AND I.MATERIAL = S.MATERIAL AND I.ITEMTYPE = ‘Z’ AND I.COMPONENT = ‘PS’
COLLATE SQL_Latin1_General_CP1_CI_AS

MS SQL UNION ALL Deyimi

Yayın tarihi
Cevapla

Aynı kolonlara sahip farklı sunucu, farklı veritabanı, farklı tablo veya farklı sorgular sonucu gelen verileri tek bir sonuçta birleştirmek için UNION ALL kullanabilirsiniz.

SELECT * FROM
(SELECT VW_ORGU_ONAY_CONF.MATERIAL, VW_ORGU_ONAY_CONF.VOPTIONS, VW_ORGU_ONAY_CONF.PROJECT,
VW_ORGU_ONAY_CONF.OUTPUT, VW_ORGU_ONAY_CONF.POTYPE, VW_ORGU_ONAY_CONF.PRDORDER,
VW_ORGU_ONAY_CONF.CONFIRMATION, VW_ORGU_ONAY_CONF.CONFIRMPOS, VW_ORGU_ONAY_CONF.CONFIRMDATE,
VW_ORGU_ONAY_CONF.WORKCENTER, VW_ORGU_ONAY_CONF.CREATEDBY
FROM MACELLAN.dbo.VW_ORGU_ONAY_CONF VW_ORGU_ONAY_CONF WHERE CONFIRMDATE>=’2011-01-01′
UNION ALL
SELECT VW_ORGU_ONAY_CONF.MATERIAL, VW_ORGU_ONAY_CONF.VOPTIONS, VW_ORGU_ONAY_CONF.PROJECT,
VW_ORGU_ONAY_CONF.OUTPUT, VW_ORGU_ONAY_CONF.POTYPE, VW_ORGU_ONAY_CONF.PRDORDER,
VW_ORGU_ONAY_CONF.CONFIRMATION, VW_ORGU_ONAY_CONF.CONFIRMPOS, VW_ORGU_ONAY_CONF.CONFIRMDATE,
VW_ORGU_ONAY_CONF.WORKCENTER, VW_ORGU_ONAY_CONF.CREATEDBY
FROM MAC2011.dbo.VW_ORGU_ONAY_CONF VW_ORGU_ONAY_CONF WHERE CONFIRMDATE>=’2011-01-01′
UNION ALL
SELECT VW_ORGU_ONAY_CONF.MATERIAL, VW_ORGU_ONAY_CONF.VOPTIONS, VW_ORGU_ONAY_CONF.PROJECT,
VW_ORGU_ONAY_CONF.OUTPUT, VW_ORGU_ONAY_CONF.POTYPE, VW_ORGU_ONAY_CONF.PRDORDER,
VW_ORGU_ONAY_CONF.CONFIRMATION, VW_ORGU_ONAY_CONF.CONFIRMPOS, VW_ORGU_ONAY_CONF.CONFIRMDATE,
VW_ORGU_ONAY_CONF.WORKCENTER, VW_ORGU_ONAY_CONF.CREATEDBY
FROM MAC2010.dbo.VW_ORGU_ONAY_CONF VW_ORGU_ONAY_CONF) AS BIRTLESIKTABLO

Sql server a excel dosyasını linked server olarak bağlamak

Yayın tarihi
Cevapla 
EXEC master.dbo.sp_addlinkedserver @server = N’UTUMALIYET’
, @srvproduct=N’excel’, @provider=N’Microsoft.ACE.OLEDB.12.0′
, @datasrc=N’E:\Company\Utu Ambalaj\Utu Ambalaj Ortak\YEDEKMALIYETCIZELGESI.xlsx’
, @provstr=N’Excel 12.0′
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’UTUMALIYET’
,@useself=N’False’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL


SELECT *
 FROM [UTUMALIYET]…[Sayfa1$]

					

		
		
	


				
					
	

		

						
SQL de bir tablodaki duplicate kayıtları bulma

			
						

				Yayın tarihi    			

			
						

				Cevapla			

					


				

			
SELECT A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR

FROM IASMATVENDORS A

GROUP BY A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR

HAVING COUNT(*)>1;


SELECT A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR

INTO TMPIASMATVENDORS

FROM IASMATVENDORS A

GROUP BY A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR

HAVING COUNT(*)>1;


SELECT A.* FROM IASMATVENDORS A,TMPIASMATVENDORS B

WHERE A.CLIENT=B.CLIENT AND A.COMPANY=B.COMPANY AND A.MATERIAL=B.MATERIAL AND A.VENDOR=B.VENDOR

AND VALIDFROM >’2009-01-01′;


DELETE A FROM IASMATVENDORS A,TMPIASMATVENDORS B

WHERE A.CLIENT=B.CLIENT AND A.COMPANY=B.COMPANY AND A.MATERIAL=B.MATERIAL AND A.VENDOR=B.VENDOR

AND VALIDFROM >’2009-01-01′;


UPDATE IASMATVENDORS SET VALIDFROM =’2005-01-01′ WHERE MATERIAL LIKE ‘%’ AND VALIDFROM <>’2005-01-01′;


DROP TABLE TMPIASMATVENDORS;