hot backup: type

Showing posts with label type. Show all posts

Friday, March 30, 2012

How can I convert font in database

I have one field type ntext, I want to change font of this data. Can I do this.Please help me.

Thank you alot.

this is the duty of presentation layer. as such you should change the font in the FE or GUI not in the database and its not possilble and its not logcally correct also. And also please tell us why you want to change the font in DB?

Madhu

|||Do you mean change the font, change the encoding, or change the collation? The font isn't stored in the server, the encoding is set by the application, but the collation of a column can be changed for a specific language or ordering. You can find more information on SQL Server collations at:

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Hope that helps!

John

|||I mean encoding, before user use font VNI-Times (Vietnamese language ) and save to database, now, if we show it with Unicode, we can not read anything, so that I am finding solution to convert encoding to Unicode.I intend export to excel, and import with some option that can change font encoding (if have any ).

How can I convert font in database

I have one field type ntext, I want to change font of this data. Can I do this.Please help me.

Thank you alot.

Madhu

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Hope that helps!

John

How can I convert font in database

I have one field type ntext, I want to change font of this data. Can I do this.Please help me.

Thank you alot.

Madhu

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Hope that helps!

John

How can I convert DateTime to Date as Parameter?

Hi Guys! Need Help on this!! I am using a Datetime data type as my Parameter on my stored procedure in SQL Server 2005. I am also using Crystal Reports XI for my reporting using the stored procedure in SQL but my problem is that I want to use ONLY the DATE data type as my Parameter instead of using the datetime parameter in Crystal Reports! Since the SQL server does not have a Date data type, how can I convert this from DateTime to Only Date data type as my parameter?....Thanks!!

Use datatime data type and pass just date part from CR or strip off the time part wherever you are planning to use it.

declare @.d datetime

set @.d = getdate()

select dateadd(day, datediff(day, 0, @.d), 0)

AMB

|||

Thanks! but how do you pass just the date part from CR? Any idea would be greatly appreciated!! I can strip off the time part inside the stored procedure in SQL 2005 but CR is using the parameter which is datetime....

|||

Sorry about that, but I think that question could be answered better in a CR newsgroup. Try:

datetime(datepart("yyyy", {@.d}), datepart("m", {@.d}), datepart("d", {@.d}), 00, 00, 00)

AMB

|||Thanks AMB....that will work but that code is for the inside on the report...my problem lies in the parameter prompt window..how can I let the user only select the date without seeing the the time on the parameter prompt window?....|||

Sorry I have no idea. As I mentioned in my previos post, these questions would be better asked in a CR newsgroup.

AMB

Monday, March 26, 2012

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

I have Try to Create Stored Procedure in C# with the following structure

[Microsoft.SqlServer.Server.SqlProcedure]

public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)

{

}

But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature

@.ImageID uniqueidentifier

@.ImageFileName nvarchar(4000)

@.Image varbinary(8000)

But I don’t want that Data types .. I want it to be in the following format

@.ImageID uniqueidentifier

@.ImageFileName nText

@.Image Image

How Can I Control the data type for each parameter ?

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

How Can I defined the new Data type ?

What's the solution to this problem ?

Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP

ALTER PROCEDURE [dbo].[sp_AddImage]

@.ImageID [uniqueidentifier],

@.ImageFileName nText,

@.Image Image

WITH EXECUTE AS CALLER

EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]

And thanks with my best regarding

Fraas

Hi Fraas

You may change parameter types (from nvarchar(4000) to nvarchar(max), for example), but you can't pass values bigger than 8000 bytes directly...

If you need nText or Image handling inside your .NET sp's, you should use .NET wrappers for this SQL types. Consider following procedure definition:

public static void AddImage(Guid ImageID, SqlChars FileName, SqlBytes image)

{

SqlContext.Pipe.Send(String.Format("Image: id {0}, filename length: {1}, image size: {2}", ImageID.ToString(), FileName.Value.Length, image.Length));

}

After deploying this proc, parameter types are uniqueidentifier, nvarchar(max) and varbinary(max).
nvarchar(max) should be used instead of ntext, and varbinary(max) instead of image types accordingly. Ntext and image are deprecated.

simple test (values bigger than 8000 can pass! ;-)):

declare @.id uniqueidentifier, @.image varbinary(max), @.filename nvarchar(max)

select @.filename = (select * from AdventureWorks.Person.Contact for xml auto, elements)

--or select @.filename = (select convert(nvarchar(max), replicate(N'1', 8000)) + convert(nvarchar(max), replicate(N'2', 8000)) )

select @.image = convert(varbinary(max), @.filename)

select @.id = newid()

exec AddImage @.id, @.filename, @.image

WBR, Evergray

Words mean nothing...

P.S. Do you really need ntext for storing file name?

|||What you are seeing is Visual Studio's mapping of CLR types to SQL

types, where string/SqlString maps to nvarchar(4000) and byte[] maps to

varbinary(8000).

The ntext and image datatypes in SQL 2005 are now deprecated and you

should use nvarchar(max) and varbinary(max) instead. To automatically

get those types from your CLR code, you should use the SqlChars and SqlBinary types from the SqlTypes namespace instead.

Niels|||

Thanks for the replies

Surly I will not save the File Name in (nText) Data Type but this is only as example .. I need to use the nText Type to store large text Date in the filed

And I need the Image Data Type to store Large File In the Database

|||Let's just make sure we're all on the same page here. From your response above I'm not usre if you realize (if you do I apologize) that ntext/text and image in SQL 2005 are being deprecated and eventually will go away (they are there now only for backward compatibility). They are being replaced with the nvarchar(max)/varchar(max) and varbinary(max) datatypes. These new datatypes have the same storage capabilities as the old ntext and image, but are much easier to work with

So in SQL 2005 you would use the nvarchar(max) type to store larger text date and varbinary(max) to store a large file. Subsequently, when you use VS to create SQCLR assemblies and stored procs you can use the SqlChars and SqlBinary datatypes in order to get automatic mapping to ntext(max) and varbinary(max) during deployment.

Niels|||

Thanks for all replies .. as I can see it solve my problem when the nVarchar(Max) replace for nText

With my regarding

Fraas

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

I have Try to Create Stored Procedure in C# with the following structure

[Microsoft.SqlServer.Server.SqlProcedure]

public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)

{

}

But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature

@.ImageID uniqueidentifier

@.ImageFileName nvarchar(4000)

@.Image varbinary(8000)

But I don’t want that Data types .. I want it to be in the following format

@.ImageID uniqueidentifier

@.ImageFileName nText

@.Image Image

How Can I Control the data type for each parameter ?

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

How Can I defined the new Data type ?

What's the solution to this problem ?

Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP

ALTER PROCEDURE [dbo].[sp_AddImage]

@.ImageID [uniqueidentifier],

@.ImageFileName nText,

@.Image Image

WITH EXECUTE AS CALLER

EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]

And thanks with my best regarding

Fraas

Hi Fraas

You may change parameter types (from nvarchar(4000) to nvarchar(max), for example), but you can't pass values bigger than 8000 bytes directly...

If you need nText or Image handling inside your .NET sp's, you should use .NET wrappers for this SQL types. Consider following procedure definition:

public static void AddImage(Guid ImageID, SqlChars FileName, SqlBytes image)

{

SqlContext.Pipe.Send(String.Format("Image: id {0}, filename length: {1}, image size: {2}", ImageID.ToString(), FileName.Value.Length, image.Length));

}

simple test (values bigger than 8000 can pass! ;-)):

declare @.id uniqueidentifier, @.image varbinary(max), @.filename nvarchar(max)

select @.filename = (select * from AdventureWorks.Person.Contact for xml auto, elements)

--or select @.filename = (select convert(nvarchar(max), replicate(N'1', 8000)) + convert(nvarchar(max), replicate(N'2', 8000)) )

select @.image = convert(varbinary(max), @.filename)

select @.id = newid()

exec AddImage @.id, @.filename, @.image

WBR, Evergray

Words mean nothing...

P.S. Do you really need ntext for storing file name?

|||What you are seeing is Visual Studio's mapping of CLR types to SQL

types, where string/SqlString maps to nvarchar(4000) and byte[] maps to

varbinary(8000).

The ntext and image datatypes in SQL 2005 are now deprecated and you

should use nvarchar(max) and varbinary(max) instead. To automatically

get those types from your CLR code, you should use the SqlChars and SqlBinary types from the SqlTypes namespace instead.

Niels|||

Thanks for the replies

Surly I will not save the File Name in (nText) Data Type but this is only as example .. I need to use the nText Type to store large text Date in the filed

And I need the Image Data Type to store Large File In the Database

Niels|||

Thanks for all replies .. as I can see it solve my problem when the nVarchar(Max) replace for nText

With my regarding

Fraas

How can i change the chart type at runtime?

Hi friends,

I want to display my report with different chart.

The type of chart to be display will be choosen only on exection time.

Can any one help me by giving solution to my problem?

Thank you

Currently, SSRS doesn't support changing the report definition at runtime. To implement your requirement consider an application front end to change and deploy the report definition. If this is not an option, the other option is to have all supported chart types on the report and hide conditionally the ones you don't need. The later approach may have performance ramifications.

Wednesday, March 21, 2012

How can i add string type column as a measure

How can i add Fact table string type column as a measure.

What are you looking to do with the string?

Measures are usually numbers that can be summed (or some other additive/semi-additive function). If you have string datatypes in your fact table, and you're not just looking for a count, are those string values unique per row? If so, what you'll want to build is a degenerate dimension. If not, you'll want to pull that out into a separate dimension table and foreign key to it with an int surrogate key.

Search for the phrase "degenerate dimension" in the following paper:

http://msdn2.microsoft.com/en-us/library/ms345125(SQL.90).aspx

(Don't use ROLAP like he suggests unless MOLAP just doesn't work for you.)

How can I add pictures to my database filed in design view?

I have a database called 'Objects' which has many field. One of its fields is called 'Image' and has a data type image.

I want to add pictures to each one of my records offline, is this possible?

i.e. by copying the address from my C drive such as C:\Documents and Settings\fseyedarabi\My Documents\My Pictures

Hi,

From your description, it seems that you want to upload your local image file to the image field in your database, right?

If so, I suggest that you can refer the FileUpload class, The FileUpload class displays a text box control and a browse button that allow users to select a file on the client and upload it to the Web server. The user specifies the file to upload by entering the full path to the file on the local computer (for example, C:\MyFiles\FileName) in the text box of the control. Alternately, the user can select the file by clicking the Browse button, and then locating it in the Choose File dialog box.

After that, just read the file into binary format and save it into the corresponding data filed of your database.

For more information, see:
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.fileupload(VS.80).aspx

Thanks.

Monday, March 19, 2012

How can control Transactions for creating Stored Procedure ?

I create StringBuilder type for

concating string to create a lot of stored procedure at once

However When I use this command

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

------- This any command

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

on any command

If I use

Create a lot of Tables

such as

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

CREATE TABLE [dbo].[Table1](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table2](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table3](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

It correctly works.

But if I need create a lot of Stored procedure

as the following code :

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

CREATE PROCEDURE [dbo].[DeleteItem1]
@.ProcId Int,
@.RowVersion Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId AND
[RowVersion] = @.RowVersion
END

CREATE PROCEDURE [dbo].[DeleteItem2]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END

CREATE PROCEDURE [dbo].[DeleteItem3]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

It occurs Error ???

Please help me

How should I solve them ?

the stored procedure create

CREATE PROCEDURE ..

have to be first in T-SQL command batch. You can do what you need by inserting each single procedure code into varchar(max) variable and run this code using EXEC command like

DECLARE @.lcCommand as varchar(max)

SET @.lcCommand ='CREATE PROCEDURE PROC1 .....'

EXEC (@.lcCommand)

SET @.lcCommand ='CREATE PROCEDURE PROC2 .....'

EXEC (@.lcCommand)

remember if you procedure definition is longer than 8000 chars split it into chunks not longer than 8000 chars to prevent errors( for some reason string passed to varchar(max) in single assign is cut at 8000 position if longer than 8000 chars)

Friday, March 9, 2012

How Alter text column length?

How can I change the length of a Data Type text column? All I've found in BOL
is that you can't do it!
Thanks -Can you be more specific? Text can hold up to 2GB data, and you do not specify that length, as you
do with varchar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
> How can I change the length of a Data Type text column? All I've found in BOL
> is that you can't do it!
> Thanks -|||Tibor, Looks like Bil needs to see the entire contents of text column while
querying (SELECT).
Bil,
See the command SET TEXTSIZE in books online.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oq2JDiSmFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Can you be more specific? Text can hold up to 2GB data, and you do not
> specify that length, as you do with varchar.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
> news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
>> How can I change the length of a Data Type text column? All I've found in
>> BOL
>> is that you can't do it!
>> Thanks -
>|||The column was created with Length = 16. I am trying to store a text stream
which is being truncated at aprox 88 characters. Sample:
 *** TOP OF REPORT *** NORTH CAROLINA DIVISION OF MOTOR VEHICLES 
I don't get the correlation between 16 and 88, if I am counting those chars
correctly.
OBJECTPROPERTY TableTextInRowLimit = 0 indicates text in row option is not
set.
- Bil Click
"Tibor Karaszi" wrote:
> Can you be more specific? Text can hold up to 2GB data, and you do not specify that length, as you
> do with varchar.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
> news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
> > How can I change the length of a Data Type text column? All I've found in BOL
> > is that you can't do it!
> >
> > Thanks -
>|||The length16 is just Enterprise Manager's rather stupid way to tell you that the pointer used in the
internal handling of the text datatype is 16 bytes. It has nothing to do with what you can actually
store (which is 2GB, nothing that you configure in any way). The truncation either happens at input
or output of the data. Try checking the length using the DATALENGTH function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
news:57F1B4D3-9B8B-43F4-BDB9-F5D2057AEFD2@.microsoft.com...
> The column was created with Length = 16. I am trying to store a text stream
> which is being truncated at aprox 88 characters. Sample:
> *** TOP OF REPORT *** NORTH CAROLINA DIVISION OF MOTOR VEHICLES 
>
> I don't get the correlation between 16 and 88, if I am counting those chars
> correctly.
> OBJECTPROPERTY TableTextInRowLimit = 0 indicates text in row option is not
> set.
> - Bil Click
>
> "Tibor Karaszi" wrote:
>> Can you be more specific? Text can hold up to 2GB data, and you do not specify that length, as
>> you
>> do with varchar.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
>> news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
>> > How can I change the length of a Data Type text column? All I've found in BOL
>> > is that you can't do it!
>> >
>> > Thanks -
>>|||Turns out to be a Query Analyzer "gotcha", truncated the displayed field.
When I used the Datalength function I could see there really was more data
than being displayed in QA. EM only says <Long Text>. I finally opeded up
osql and see that the full text is being stored.
Thanks for the tips! Cheers!
- Bil
"Tibor Karaszi" wrote:
> The length16 is just Enterprise Manager's rather stupid way to tell you that the pointer used in the
> internal handling of the text datatype is 16 bytes. It has nothing to do with what you can actually
> store (which is 2GB, nothing that you configure in any way). The truncation either happens at input
> or output of the data. Try checking the length using the DATALENGTH function.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
> news:57F1B4D3-9B8B-43F4-BDB9-F5D2057AEFD2@.microsoft.com...
> > The column was created with Length = 16. I am trying to store a text stream
> > which is being truncated at aprox 88 characters. Sample:
> >
> > *** TOP OF REPORT *** NORTH CAROLINA DIVISION OF MOTOR VEHICLES 
> >
> >
> > I don't get the correlation between 16 and 88, if I am counting those chars
> > correctly.
> >
> > OBJECTPROPERTY TableTextInRowLimit = 0 indicates text in row option is not
> > set.
> >
> > - Bil Click
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Can you be more specific? Text can hold up to 2GB data, and you do not specify that length, as
> >> you
> >> do with varchar.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
> >> news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
> >> > How can I change the length of a Data Type text column? All I've found in BOL
> >> > is that you can't do it!
> >> >
> >> > Thanks -
> >>
> >>
>

Friday, March 30, 2012

How can I convert font in database

How can I convert font in database

How can I convert font in database

How can I convert DateTime to Date as Parameter?

Monday, March 26, 2012

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

How can i change the chart type at runtime?

Wednesday, March 21, 2012

How can i add string type column as a measure

How can I add pictures to my database filed in design view?

Monday, March 19, 2012

How can control Transactions for creating Stored Procedure ?

Friday, March 9, 2012

How Alter text column length?

How Alter text column length?

How Alter text column length?

hot backup

Blog Archive

About Me