Getting 0 padded values in the columns.

Getting 0 padded values in the columns.

Hi All,

I have a requirement to convert a integer to string and display it in
Sql server with fixed length say 3 chars. (in c, we wud use %03d in
printf)

If the number is small say, 9 then it has to be displayed as 009,
56 -> 056, 897-> 897, 6786 -> xxx

Checked through STR and CAST functions, couldn't find any relevant
paramters.

if you have any ideas, please mail me.

Thanks & Regards,
Chandra MohanDo:

SELECT CASE WHEN LEN(@.n) <= 3
THEN RIGHT('000' + CAST(@.n AS VARCHAR), 3)
ELSE 'xxx'
END ;

--
- Anith
( Please reply to newsgroups only )|||bschandramohan@.yahoo.com (Chandra Mohan) wrote in message news:<bb0ef6.0308200036.236c3321@.posting.google.com>...
> Getting 0 padded values in the columns.
> Hi All,
> I have a requirement to convert a integer to string and display it in
> Sql server with fixed length say 3 chars. (in c, we wud use %03d in
> printf)
> If the number is small say, 9 then it has to be displayed as 009,
> 56 -> 056, 897-> 897, 6786 -> xxx
> Checked through STR and CAST functions, couldn't find any relevant
> paramters.
> if you have any ideas, please mail me.
> Thanks & Regards,
> Chandra Mohan

Hi ,

You could use this :

select replicate('0', 3-datalength(cast(column as varchar(10)))) +
cast (column as varchar(10)) from table

Replace the column and table with the right values and here the
assumption is the column is of int datatype.

Regards,
-Manoj Rajshekar

Getting "marshaler restriction: excessively long string" in browser control when viewing r

Getting "marshaler restriction: excessively long string" in browser control when viewing rDid you figure this out yet? I am having the same problem.

GetScheduleProperties()

I create a report subscription in code. Everything works fine:
string id = service.CreateSubscription(reportName, extensionSettings,
description, eventType,matchDataParam, parameters );
I get the id back and store it in the database. When I try to pass the id
to GetScheduleProperties() I get the following error:
"The schedule '7acaf6e8-8cb5-499b-b052-c57d3f0b20a6' cannot be found. The
schedule identifier that is provided to an operation cannot be located in
the report server database. "
What's going on? I looked in the Subscriptions table in the ReportServer
database and I see the subscription ID there...You want to call GetSubscriptionProperties, not GetScheduleProperties.
GetScheduleProperties returns information about shared schedules only.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"FNDS" <absolutelynospam@.nodomain_.com> wrote in message
news:%23lBbzT65EHA.2180@.TK2MSFTNGP12.phx.gbl...
>I create a report subscription in code. Everything works fine:
> string id = service.CreateSubscription(reportName, extensionSettings,
> description, eventType,matchDataParam, parameters );
> I get the id back and store it in the database. When I try to pass the id
> to GetScheduleProperties() I get the following error:
> "The schedule '7acaf6e8-8cb5-499b-b052-c57d3f0b20a6' cannot be found. The
> schedule identifier that is provided to an operation cannot be located in
> the report server database. "
> What's going on? I looked in the Subscriptions table in the ReportServer
> database and I see the subscription ID there...
>

geting the UNIQUENAME fom a string

Does anyone have an idea how to get the UNIQUENAME of a member from ther dimesion date given a determined value.

For example in Adventure Works

Val: 2004

UNIQUENAME: [Date].[Calendar].[Calendar Year].&[2004]

Thanks!!

Here is an example showing how to retrieve the unique member name or the key value associated with the current member:

WITH

MEMBER MEASURES.[UniqueName] AS

[Date].Calendar.CurrentMember.UniqueName

MEMBER MEASURES.[KeyValue] AS

[Date].Calendar.CurrentMember.Properties("Key")

SELECT

{[Date].[Calendar].[Calendar Year].&[2004]} ON COLUMNS,

{MEASURES.[UniqueName],MEASURES.[KeyValue]} ON ROWS

FROM [Adventure Works]

HTH,

- Steve

Get XML from SQL Server 2000

I have a stored procedure, that returns a customer record from the customers table in the northwind database.
how can i return back an xml string of the row?
I mean, when the aspx page calls that procudure, I want to have somehting like:
<customers>
<customer>
<customerid>xxx</customerid>
<companyname>rrr</companyname>
</customer>
</customers>
can a stored procedure return such a string in xml form?
thanks alot

Read up on the FOR XML clause. Here's an article:http://www.sqljunkies.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk and there's quite a bit of good info in BOL.

Get the Highest value.

Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!select max(number) as m from daTable|||thanks a bunch!!!!|||select max(number) as m from daTable
Truly, an elegant piece of coding. Sheer genious for its blend of brevity and functionality. I shall have to use this in my next project.

get string

hello!

I have a code folow:

public

string GetLink()

{

string ID=Request.QueryString["mabaihat"].ToString();

stringget;

SqlConnection con =newSqlConnection(strconnection);

DataSet ds =newDataSet();

con.Open();

SqlCommand

cmd =newSqlCommand("select link from song where songid="+ID+" ",con);

//

how can I get string of comlumn link?

//

con.Colse();

return get;//string link

}

Hi try this changed code

string ID = Request.QueryString["mabaihat"].ToString();string get;SqlConnection con =newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);DataSet ds =newDataSet();

con.Open();

SqlCommand cmd =newSqlCommand("select link from song where songid=" + ID , con);object obj = cmd.ExecuteScalar();if (Convert.IsDBNull(obj) || obj==null)

get =

"";else

get = obj.ToString();

con.Close();

return get;

I have retrived the link through execute scalar method.This will solve your problem

|||thank you very much. i'm will try

get SQL connection for SQL config from XML

Hi

In toder to make my SSIS packages portable, I need to be able to set connection string to sql package configuration. I thought I can do that via XML package, problem is, at execution time SQL server package configuration is queried first, then variables from XML.
This way, I have no chance to set connection via XML.
Any ideas on how to aproach this ?
(I want to have as much configuration on SQL server so I can modify it easily from future GUI application.

best regards

For this scenario what you need is an indirect configuration, where the connection string to the SQL Server database where your "real" configurations are stored is in turn stored in an environment variable:

http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

I do not know of any way to perform this indirection through an intermediary XML config file - only environment variables are supported.

|||

One more method:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

|||You should be able to set your initial SQL Server connection from an XML configuration file. In the Configurations dialog, make sure that the XML configuration is at the top of the list, so that it is executed first.

Get Processor Id

Hi All
How Can I Convert This Code
Dim cimv2, PInfo, PItem ' no idea what to declare these as
Dim PubStrComputer As String
PubStrComputer = "."
Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
For Each PItem In PInfo
MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
PItem.ProcessorId)
Next PItem
From Sql query Analyzer To Get Processor Id
ThanksHi
"Taha" wrote:
> Hi All
> How Can I Convert This Code
> Dim cimv2, PInfo, PItem ' no idea what to declare these as
> Dim PubStrComputer As String
> PubStrComputer = "."
> Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
> Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
> For Each PItem In PInfo
> MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
> PItem.ProcessorId)
> Next PItem
> From Sql query Analyzer To Get Processor Id
> Thanks
Check out http://www.sqldbatips.com/displaycode.asp?ID=6 and
http://www.sqlservercentral.com/columnists/aloera/sqlserverscriptingandwmi.asp on how to run WMI scripts.
John

Get only the number from a string, T-SQL??

I have astring in form "abcdefg 12355 ijklmn"
Now I want get only thenumber 12355 within thestring !!
Is there any function available in T-SQL of Sql server 2K??
Thanks
for any helpI dont think there is any off the shelf function. You would need towrite your own custom function. SQL Server is not really good at stringmanipulations. You could do this very easily using regular expressionsat the front end.
|||

CREATE FUNCTION dbo.fFilterNumeric
(
@.Src nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN
declare @.Res nvarchar(255)
declare @.i int, @.l int, @.c char
select @.i=1, @.l=len(@.Src)
SET @.Res = ''
while @.i<=@.l
begin
set @.c=upper(substring(@.Src,@.i,1))
IF isnumeric(@.c)=1
SET @.Res = @.Res + @.c
set @.i=@.i+1
end

return(@.res)
END

|||The issue I have with IndianScorpion's suggestion is the use of theISNUMERIC function. This function will also return a 1 for somecharacters you'd might not expect, such as a comma (,), dollar sign($), and a period (.).
Try this little script in Query Analyzer to see the ASCII characters between 1 and 254 that SQL Server considers to be numeric:

DECLARE @.myTest char(10), @.a int
SELECT @.a = 1
WHILE @.a < 255
BEGIN
SELECT @.myTest = CHAR(@.a)
IF ISNUMERIC(@.myTest) = 1
PRINT CAST(@.a AS char(3)) + ' -- ' + @.myTest
SELECT @.a = @.a + 1
END

I would do something like this instead:

IF @.c IN ('0','1','2','3','4','5','6','7','8','9')

|||dont like long cycles,
if needs cut just one number without dot (by Ukrainian - крапки),
and number always exists,
try example below, this just example, may this example transform like one select
--
Declare @.X varchar(100)
Select @.X= 'Here is where15234Numbers'
--
Select @.X= SubString(@.X,PATINDEX('%[0-9]%',@.X),Len(@.X))
Select @.X= SubString(@.X,0,PATINDEX('%[^0-9]%',@.X))
--// show result
Select @.X

|||

pmz0178 wrote:

Declare @.X varchar(100)
Select @.X= 'Here is where15234Numbers'
--
Select @.X= SubString(@.X,PATINDEX('%[0-9]%',@.X),Len(@.X))
Select @.X= SubString(@.X,0,PATINDEX('%[^0-9]%',@.X))
--// show result
Select @.X

The limitation of this solution is that a value of @.X like this:

Select @.X= 'Here is where15234Numbers987'

will result in 15234, and not 15234987, as IndianScorpion'swould. This could be a positive thing or a negative thing,depending on the situation.
|||I suggest that TSQL is the wrong tool for the job, at least until you can use the CLR. IMO much better if you can use a client to do this work before it gets to the database, a simple (well when are they) regular expression would sort this kind of problem out.

Get only a certain piece of a string

I have a table that holds file paths for reports. Let's say it looks like this:

C:\Jeremy\Testing\JCScoobyRS\Testing.txt

Let's assume that none of the files are in the same directory and the directory is not known, as I'm running a report to get only file names. How can I get only file names from the string listed above? Thanks, JeremyBetter to store PATH & FILENAME in seperate variables.

Refer to this link (http://www.nigelrivett.net/CheckIfFileExists.html) for XP_FILEEXISTS which searches for the specified file, where you can use it for your task.

HTH|||That's my (lazy) way to do it:

declare @.FP varchar(255)
select @.FP='C:\Jeremy\Testing\JCScoobyRS\Testing.txt'
select reverse(substring(reverse(@.FP),1,charindex('\',rev erse(@.FP))-1))|||Valid tip Kukuk, keep it up.