Jump to content

Ms Sql Server Technology Discussions


9Pardhu

Recommended Posts

[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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

[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

Link to comment
Share on other sites

[quote name='GatisKandis' timestamp='1353524928' post='1302837037']
Please edit the title to : Sql Server Technology Discussions thanks
[/quote]
@gr33d CITI_$D#

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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


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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

[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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

×
×
  • Create New...