Ms Sql Server Technology Discussions

[deals2buy]

[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]

akada 255 kante ekuva char vunayi bhayya 500 echanu set iyindi

[Desamudhuru]

[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]

Please edit the title to : Sql Server Technology Discussions thanks

[deals2buy]

[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]

[quote name='GatisKandis' timestamp='1353524928' post='1302837037']
Please edit the title to : Sql Server Technology Discussions thanks
[/quote]

[deals2buy]

[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]

[b] Returning the Top X row for each group (SQL Spackle)[/b]


http://www.sqlservercentral.com/articles/T-SQL/71571/

[deals2buy]

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]

[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]

[quote name='GatisKandis' timestamp='1353524928' post='1302837037']
Please edit the title to : Sql Server Technology Discussions thanks
[/quote]
seyyar vaay

[9Pardhu]

changed....

[ravi87]

SSIS baga vachanavallu itu randi .. koddiga.. package sql agent tho run avtledu.. need help..

[Desamudhuru]

[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]

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.