deals2buy Posted November 21, 2012 Report Posted November 21, 2012 [quote name='Desamudhuru' timestamp='1353523246' post='1302836898'] Yeah akada length kuda change chesa baa iyina error esthondi [/quote] length kaadu...daani paina datatype emundo chudu
Desamudhuru Posted November 21, 2012 Report Posted November 21, 2012 akada 255 kante ekuva char vunayi bhayya 500 echanu set iyindi
Desamudhuru Posted November 21, 2012 Report Posted November 21, 2012 [quote name='deals2buy' timestamp='1353523276' post='1302836901'] length kaadu...daani paina datatype emundo chudu [/quote] Problem length vallane avi konni data 225 kante ekuva char vunyi so adi 500 ki set chesanu motham set iyindi. Along with that na DB lo varchar(max) chesanu aaa particular columns ki so set iyindi.
ChampakDas Posted November 21, 2012 Report Posted November 21, 2012 Please edit the title to : Sql Server Technology Discussions thanks
deals2buy Posted November 21, 2012 Report Posted November 21, 2012 [quote name='Desamudhuru' timestamp='1353524808' post='1302837029'] Problem length vallane avi konni data 225 kante ekuva char vunyi so adi 500 ki set chesanu motham set iyindi. Along with that na DB lo varchar(max) chesanu aaa particular columns ki so set iyindi. [/quote] cool...usually flat file nundi load chesetappudu problem akkade vastutundi or else with the delimiters
deals2buy Posted November 21, 2012 Report Posted November 21, 2012 [quote name='GatisKandis' timestamp='1353524928' post='1302837037'] Please edit the title to : Sql Server Technology Discussions thanks [/quote]
deals2buy Posted November 23, 2012 Report Posted November 23, 2012 [quote name='deals2buy' timestamp='1353335149' post='1302822473'] [b] Removing Leading Zeros From Column in Table[/b] [color=blue]USE [/color][color=black]tempdb GO[/color] [color=green]-- Create sample table[/color] [color=blue]CREATE TABLE [/color][color=black]Table1 [/color][color=gray]([/color][color=black]Col1 [/color][color=blue]VARCHAR[/color][color=gray]([/color][color=black]100[/color][color=gray]))[/color] [color=blue]INSERT INTO [/color][color=black]Table1 [/color][color=gray]([/color][color=black]Col1[/color][color=gray])[/color] [color=blue]SELECT [/color][color=red]'0001'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'000100'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'100100'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'000 0001'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'00.001'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'01.001'[/color] [color=black]GO[/color] [color=green]-- Original data[/color] [color=blue]SELECT [/color][color=gray]*[/color] [color=blue]FROM [/color][color=black]Table1 GO[/color] [color=green]-- Remove leading zeros[/color] [color=blue]SELECT[/color] [color=magenta]SUBSTRING[/color][color=gray]([/color][color=black]Col1[/color][color=gray], [/color][color=magenta]PATINDEX[/color][color=gray]([/color][color=red]'%[^0 ]%'[/color][color=gray], [/color][color=black]Col1 [/color][color=gray]+ [/color][color=red]' '[/color][color=gray]), [/color][color=magenta]LEN[/color][color=gray]([/color][color=black]Col1[/color][color=gray]))[/color] [color=blue]FROM [/color][color=black]Table1 GO[/color] [color=green]-- Clean up[/color] [color=blue]DROP TABLE [/color][color=black]Table1 GO[/color] Here is the resultset of above script. It will remove any leading zero or space and will display the number accordingly. [img]http://www.pinaldave.com/bimg/leadingzeroes.jpg[/img] [u][b]Alternate Solutions:[/b][/u] SELECT SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + 'a'), LEN(Col1)) FROM Table1 SELECT RIGHT(Col1, LEN(Col1)+1 -PATINDEX('%[^0 ]%', Col1 + 'a' )) FROM Table1 [/quote] [b][u]More Solutions[/u]:[/b] [color=blue]USE [/color][color=black]tempdb GO[/color] [color=green]-- Create sample table[/color] [color=blue]CREATE TABLE [/color][color=black]Table1 [/color][color=gray]([/color][color=black]Col1 [/color][color=blue]VARCHAR[/color][color=gray]([/color][color=black]100[/color][color=gray]))[/color] [color=blue]INSERT INTO [/color][color=black]Table1 [/color][color=gray]([/color][color=black]Col1[/color][color=gray])[/color] [color=blue]SELECT [/color][color=red]'0001'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'000100'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'100100'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'000 0001'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'00.001'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'01.001'[/color] [color=blue]UNION [/color][color=gray]ALL[/color] [color=blue]SELECT [/color][color=red]'0000'[/color] [color=black]GO[/color] [color=black][u]Solution -1[/u]:[/color] [color=blue]SELECT[/color] [color=magenta]CASE PATINDEX[/color][color=gray]([/color][color=red]'%[^0 ]%'[/color][color=gray], [/color][color=black]Col1 [/color][color=gray]+ [/color][color=red]' ‘'[/color][color=gray])[/color] [color=blue]WHEN [/color][color=black]0 [/color][color=blue]THEN [/color][color=red]''[/color] [color=blue]ELSE [/color][color=magenta]SUBSTRING[/color][color=gray]([/color][color=black]Col1[/color][color=gray], [/color][color=magenta]PATINDEX[/color][color=gray]([/color][color=red]'%[^0 ]%'[/color][color=gray], [/color][color=black]Col1 [/color][color=gray]+ [/color][color=red]' '[/color][color=gray]), [/color][color=magenta]LEN[/color][color=gray]([/color][color=black]Col1[/color][color=gray]))[/color] [color=blue]END FROM [/color][color=black]Table1[/color] [color=black][u]Solution - 2[/u]:[/color] [color=blue]SELECT[/color] [color=magenta]RIGHT[/color][color=gray]([/color][color=black]Col1[/color][color=gray], [/color][color=magenta]LEN[/color][color=gray]([/color][color=black]Col1[/color][color=gray])+[/color][color=black]1 [/color][color=gray]-[/color][color=magenta]PATINDEX[/color][color=gray]([/color][color=red]'%[^0 ]%'[/color][color=gray], [/color][color=black]Col1 [/color][color=gray]+ [/color][color=red]'a' [/color][color=gray]))[/color] [color=blue]FROM [/color][color=black]Table1[/color] [u]Solution-3[/u]: [color=blue]SELECT[/color] [color=black]T.Col1[/color] [color=gray], [/color][color=black]label [/color][color=blue]= [/color][color=magenta]CAST[/color][color=gray]([/color] [color=magenta]CAST[/color][color=gray]([/color][color=magenta]REPLACE[/color][color=gray]([/color][color=black]T.Col1[/color][color=gray], [/color][color=red]' '[/color][color=gray], [/color][color=red]''[/color][color=gray]) [/color][color=blue]AS FLOAT[/color][color=gray])[/color] [color=blue]AS VARCHAR[/color][color=gray]([/color][color=black]10[/color][color=gray]))[/color] [color=blue]FROM [/color][color=black]Table1 [/color][color=blue]AS [/color][color=black]T[/color] [color=black][u]Solution - 4[/u]:[/color] [color=blue]SELECT [/color][color=magenta]REPLACE[/color][color=gray]([/color][color=magenta]LTRIM[/color][color=gray]([/color][color=magenta]REPLACE[/color][color=gray]([/color][color=black]col1[/color][color=gray],[/color][color=red]'0'[/color][color=gray],[/color][color=red]' '[/color][color=gray])),[/color][color=red]' '[/color][color=gray],[/color][color=red]'0'[/color][color=gray]) [/color][color=blue]FROM [/color][color=black]table1[/color]
deals2buy Posted November 23, 2012 Report Posted November 23, 2012 [b] Returning the Top X row for each group (SQL Spackle)[/b] http://www.sqlservercentral.com/articles/T-SQL/71571/
deals2buy Posted November 23, 2012 Report Posted November 23, 2012 DECLARE @string1 VARCHAR(16) SET @string1 = 'Microsoft Server' DECLARE @Stuffing VARCHAR(30) SET @Stuffing = '******************************' SELECT DATALENGTH(@string1),DATALENGTH(@Stuffing) --Execution of the above returns 16 and 30 as expected. I then execute : (Second SELECT statement) SELECT STUFF(@string1, 11,0,@Stuffing) , DATALENGTH(STUFF(@string1, 11,0,@Stuffing)) --The question is: what is returned as the DATALENGTH by the Second SELECT statement?
deals2buy Posted November 23, 2012 Report Posted November 23, 2012 [b] Script for no of Index in a table[/b] SELECT t.name AS TableName, t.[object_id], SUM ( CASE WHEN i.is_primary_key = 1 THEN 1 ELSE 0 END ) AS Primarykey, SUM ( CASE WHEN i.[type] = 1 THEN 1 ELSE 0 END ) AS ClusteredIndex, SUM ( CASE WHEN i.[type] = 2 THEN 1 ELSE 0 END ) AS NonClusteredIndex, SUM ( CASE WHEN i.[type] = 0 THEN 1 ELSE 0 END ) AS HeapIndex, COUNT ( * ) TotalNoofIndex FROM sys.tables t LEFT OUTER JOIN sys.indexes i ON i.[object_id] = t.[object_id] GROUP BY t.name, t.[object_id]
manmadudhu Posted November 23, 2012 Report Posted November 23, 2012 [quote name='GatisKandis' timestamp='1353524928' post='1302837037'] Please edit the title to : Sql Server Technology Discussions thanks [/quote] seyyar vaay
ravi87 Posted November 26, 2012 Report Posted November 26, 2012 SSIS baga vachanavallu itu randi .. koddiga.. package sql agent tho run avtledu.. need help..
Desamudhuru Posted November 26, 2012 Report Posted November 26, 2012 [quote name='ravi87' timestamp='1353954681' post='1302855066'] SSIS baga vachanavallu itu randi .. koddiga.. package sql agent tho run avtledu.. need help.. [/quote] Problem ento exact ga post cheyu baa
ravi87 Posted November 26, 2012 Report Posted November 26, 2012 Joblu runuuuu avtledduuu baa sql agent thooo Message Executed as user: xxx\sqlserviced. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:55:58 AM Error: 2012-11-26 11:55:58.22 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2012-11-26 11:55:58.56 Code: 0xC0202009 Source: Pkg Connection manager "SQLServerConnection" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'X'.".---> edi main anipistundhi baaaaaa prob End Error Error: 2012-11-26 11:55:58.56 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SQLServerConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2012-11-26 11:55:58.56 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Error: 2012-11-26 11:55:58.56 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-11-26 11:55:58.56 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:55:58 AM Finished: 11:55:58 AM Elapsed: 0.469 seconds. The package execution failed. The step failed.
Recommended Posts