date and time diffs: 2012

Thursday, March 29, 2012

Datatype question

What is the difference between decimal(9) and decimal(9,0)?
Thanks!There is no difference, because the default scale is 0.
For more informations, see:
http://msdn.microsoft.com/library/e...des_04_82ic.asp
Razvan|||thanks!
"Razvan Socol" wrote:

> There is no difference, because the default scale is 0.
> For more informations, see:
> http://msdn.microsoft.com/library/e...des_04_82ic.asp
> Razvan
>

Datatype Question

I've got numbers stored in my table as varchar which i need to cast to a
datatype so that i can perform an arithmetic operation on.
34.2348905444367
45.08070345435
34.6546456354354353
43.6540697929
the problem is, I need them to be cast so that i can perform calculations on
them. I need the specific value back, and not something that is rounded up
or down.
If I use float I get extra numbers appearing at the end, (ie. there is a
rounding issue)
If I try to cast as decimal(12, 16)
The scale must be less than or equal to the precision.
How can i simply get the true value of the record ?
Many thanks.decimal(28, 16)
28 = total length
16 = positions to the right of the decimal point.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Douglas Adams" wrote:

> I've got numbers stored in my table as varchar which i need to cast to a
> datatype so that i can perform an arithmetic operation on.
> 34.2348905444367
> 45.08070345435
> 34.6546456354354353
> 43.6540697929
> the problem is, I need them to be cast so that i can perform calculations
on
> them. I need the specific value back, and not something that is rounded u
p
> or down.
> If I use float I get extra numbers appearing at the end, (ie. there is a
> rounding issue)
>
> If I try to cast as decimal(12, 16)
> The scale must be less than or equal to the precision.
>
> How can i simply get the true value of the record ?
> Many thanks.
>
>|||The following works for me. But don't confuse how the number is
*displayed* with the way it is stored
CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO T1 (x)
SELECT 34.2348905444367 UNION ALL
SELECT 45.08070345435 UNION ALL
SELECT 34.6546456354354353 UNION ALL
SELECT 43.6540697929 ;
SELECT CAST(x AS DECIMAL(20,16)) FROM T1
Result:
34.2348905444367000
34.6546456354354353
43.6540697929000000
45.0807034543500000
(4 row(s) affected)
David Portas
SQL Server MVP
--|||Thanks guys
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121852998.202064.66570@.g43g2000cwa.googlegroups.com...
> The following works for me. But don't confuse how the number is
> *displayed* with the way it is stored
> CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
> INSERT INTO T1 (x)
> SELECT 34.2348905444367 UNION ALL
> SELECT 45.08070345435 UNION ALL
> SELECT 34.6546456354354353 UNION ALL
> SELECT 43.6540697929 ;
> SELECT CAST(x AS DECIMAL(20,16)) FROM T1
> Result:
> --
> 34.2348905444367000
> 34.6546456354354353
> 43.6540697929000000
> 45.0807034543500000
> (4 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --
>

Datatype problem?

Hello
We ahve a problem with datatypes. I the cube whe have 2 mesasures (Item_Sold and Sales). Item_sold is Numeric datatype and Sales is Currency. In the virtual cube whe divide the to in to a average price (Sales/Item_sold).
We user Excel show the cube.
For integer numbers we get no problem but for item_sold with decimal values (like 1,43) the division get currupt. It seems that it only devide the integer so average measures of:
Item_sold=2,5
Sales=500
...will be 250 (500/2). And if the item_sold is smaller than 1 (for units smaller than 1 kilo) the average value will even more strange.

I think that the datatyp is the error. What datatyp shall i use?

I am also getting this strange problem

What is happening is that when something is being divided by 0.XX its producing those wrong figures where decimal is at a wrong place. But if we divide the numerator and denominator by 100, we get the correct figures.

Something *100 / 0.XX *100 = good

Something / 0.XX = wrong

|||

OK,

I found the solution. Its related to data types. Make sure that both the denominator and the numenator are of same data type (may be numberic)

It solved my problem

sql

Datatype problem?

I think that the datatyp is the error. What datatyp shall i use?

I am also getting this strange problem

Something *100 / 0.XX *100 = good

Something / 0.XX = wrong

|||

OK,

I found the solution. Its related to data types. Make sure that both the denominator and the numenator are of same data type (may be numberic)

It solved my problem

Datatype problem?

I think that the datatyp is the error. What datatyp shall i use?

I am also getting this strange problem

Something *100 / 0.XX *100 = good

Something / 0.XX = wrong

|||

OK,

I found the solution. Its related to data types. Make sure that both the denominator and the numenator are of same data type (may be numberic)

It solved my problem

Datatype problem: development vs production servers

This is driving me nuts: On my development machine the code runs finebut generates an error on the production server. Both are running SQLServer 2000 and ASP.NET 1.1

The datatype of the field in question isdatetime.
The webform has a calendar for a user to select and automaticallyinsert the date into the textbox. The update command in the webform is:
cmdInsert.Parameters.Add("@.citation_date", CDate(txtDate.Text))

This works without a hitch on my development system, but on the production server it generates the following error:
Cast from string "19-12-1997" to type 'Date' is not valid.

WHY? Sad [:(]
It has to do with the locale information (country, language, etc.) for the computer. Check to make sure the server is set to whatever you're using on your local development PC. I'm not familiar with setting/changing these since I only use U.S. format and English.|||

jcasp wrote:
It has to do with the locale information (country,language, etc.) for the computer. Check to make sure the serveris set to whatever you're using on your local development PC. I'mnot familiar with setting/changing these since I only use U.S. formatand English.

You are right. I'm inputting U.S format of date for the time being until I've figured a way around it. Thanks!|||Use YYYY-MM-DD format, then it doesn't matter what culture you are in.

DATATYPE PROBLEM(cross)

I have a column in my table BizdekiFiyat . The datatype = float length =8
(to save money values).. It is impossible to change these attributes for
some reasons.
It has records like This
BizdekiFiyat
110
24
29.5
31.35
I use Vb.Net . I use ExecuteReader To select values from my db..
After first attemp
Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
IT returns
110
24
295
3135
Dim BizdekiFiyat As String
It returns right results.
110
24
29.5
31.35
There is a problem with decimal records when i want to evaluate this
values..
For example
Dim BizdekiFiyat As String
BizdekiFiyat = BizdekiFiyat * 1.05
It is supposed to be
29.5 * 1.05 =30.975
31.35*1.05=32.9175
but it returns
309,75
3291,75
How can i solve this problem ?"Savas Ates" <in da club> wrote in message
news:OEhAfpwLGHA.648@.TK2MSFTNGP14.phx.gbl...
>I have a column in my table BizdekiFiyat . The datatype = float length =8
>(to save money values).. It is impossible to change these attributes for
>some reasons.
>
> Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
> IT returns
> 110
> 24
> 295
> 3135
Integer datatype will always truncate your decimal fraction values.
I've had data dimension problems trying to use the Decimal datatype for
holding (SQL) decimal data returned through parameters using MS's EntLib
DAAB. I resolved this by using .NET's Double datatype (though I'd prefer to
know why .NET's decimal gave me the problem in the first place).

> Dim BizdekiFiyat As String
> It returns right results.
Because the value is being represented and stored as a string (just like
typing into a textbox), not a numeric type, so...

> There is a problem with decimal records when i want to evaluate this
> values..
> For example
> Dim BizdekiFiyat As String
> BizdekiFiyat = BizdekiFiyat * 1.05
> How can i solve this problem ?
You're expecting .NET to intelligently convert your datatypes for you, which
it is valiantly trying to do. You should consider setting Option Strict on
(Tools | Options | Projects | VB Defaults) to prevent loose data typing and
late binding. You should strongly type your datatypes as a matter of god
practice. When you need to convert datatypes, dothis explicitely using
CType(sourceObj, targetType), or the shorthand versions such as Cint(value),
CDbl(value), etc.
As for your calculations: e.g. using Double to store your values, create a
function which you'll call when necessary to do your calculations:
private function MultiplyBizdekiFiyat(Byval origValue as double, Byval
MultiplyBy as double) As Double
'Perform the calculation
MultiplyBizdekiFiyat = origValue * MultiplyBy
'Return the value to the calling method.
return MultiplyBizdekiFiyat
end function
Hope that helps
Al

datatype problem

how can i transfer incoming data from flat file which would be a string to my sql table of column int...

i have a problem with datatype can i conver string to int how should i do it...new to it

please help!!

This is what the Data Conversion Task is for. U could also use the Derived Column Task. Place it between source and destination, convert your column there.

What more can I say?

Pipo1

|||Use a derived column transformation.

This is one example of an expression you could use: (DT_I4)[Your_Column]sql

DataType Problem

Hi,

I want to pass data to the stored procedure by vb code.One of the input parameters of the SP has Text Data Type.whats the equivalent of this datatype in vb?

Thanks in Advance.

You can use byte array (byte[]) for BLOB data type (text/ntext/image) in SQL Server:)

datatype problem

Hi Everyone,

In C# file:

bool abc; (it is working)

But in sql file: (bool is not working. What datatype should use for bool in the sql file?)

CREATE TABLEsb_payment_history (

sb_dispensing bool NULL <================ not working because bool is not known.

)

Thanks,

May

You should use the bit datatype in your SQL table.

Datatype Problem

Hello
I am new to SQL Server and have migrated an Access DB to SQL Server
I can connect to the database, pull content, etc but a strange thing happen
when I use the "text" datatype. I have a table called "tbl_news
where a field is called "news_story" this is a large text block that my clien
can cut and paste text from MS word. Since it needs to hold large amounts ot tex
I used the MEMO field-type in Access. When I used the TEXT field in SQL serve
some weird stuff happens. When I query the record in a recordset and display
the results on a standard ASP page - one or another fields will not print out
No matter what I try - it seems that one field won't display and there is no rhyme or reason.
When I look in the DB I see the DATA but it wont print on the screen like response.write rs("news_story"
What would cause a field not to print? somewhat hap-hazardly
SHould I use the text datatype? For ex. when I use vchar it doesnt happen. But my tex
gets truncated. Please Help - I am ready to pull my hair ouTo read from TEXT column you should use READTEXT statement,
but you can try to play with SET TEXTSIZE.
Please read "Retrieving ntext, text, or image Values" topics in BOL.
HTH
Igor Raytsin
"Padpros" <anonymous@.discussions.microsoft.com> wrote in message
news:892D6AA1-FCF8-4051-9586-B0FFAA1ED99D@.microsoft.com...
> Hello,
> I am new to SQL Server and have migrated an Access DB to SQL Server.
> I can connect to the database, pull content, etc but a strange thing
happens
> when I use the "text" datatype. I have a table called "tbl_news"
> where a field is called "news_story" this is a large text block that my
client
> can cut and paste text from MS word. Since it needs to hold large amounts
ot text
> I used the MEMO field-type in Access. When I used the TEXT field in SQL
server
> some weird stuff happens. When I query the record in a recordset and
display
> the results on a standard ASP page - one or another fields will not print
out.
> No matter what I try - it seems that one field won't display and there is
no rhyme or reason.
> When I look in the DB I see the DATA but it wont print on the screen like
response.write rs("news_story")
> What would cause a field not to print? somewhat hap-hazardly?
> SHould I use the text datatype? For ex. when I use vchar it doesnt happen.
But my text
> gets truncated. Please Help - I am ready to pull my hair out
>

Datatype Problem

Hello,
I am new to SQL Server and have migrated an Access DB to SQL Server.
I can connect to the database, pull content, etc but a strange thing happens
when I use the "text" datatype. I have a table called "tbl_news"
where a field is called "news_story" this is a large text block that my clie
nt
can cut and paste text from MS word. Since it needs to hold large amounts ot
text
I used the MEMO field-type in Access. When I used the TEXT field in SQL serv
er
some weird stuff happens. When I query the record in a recordset and display
the results on a standard ASP page - one or another fields will not print ou
t.
No matter what I try - it seems that one field won't display and there is n
o rhyme or reason.
When I look in the DB I see the DATA but it wont print on the screen like re
sponse.write rs("news_story")
What would cause a field not to print? somewhat hap-hazardly?
SHould I use the text datatype? For ex. when I use vchar it doesnt happen. B
ut my text
gets truncated. Please Help - I am ready to pull my hair outTo read from TEXT column you should use READTEXT statement,
but you can try to play with SET TEXTSIZE.
Please read "Retrieving ntext, text, or image Values" topics in BOL.
HTH
Igor Raytsin
"Padpros" <anonymous@.discussions.microsoft.com> wrote in message
news:892D6AA1-FCF8-4051-9586-B0FFAA1ED99D@.microsoft.com...
> Hello,
> I am new to SQL Server and have migrated an Access DB to SQL Server.
> I can connect to the database, pull content, etc but a strange thing
happens
> when I use the "text" datatype. I have a table called "tbl_news"
> where a field is called "news_story" this is a large text block that my
client
> can cut and paste text from MS word. Since it needs to hold large amounts
ot text
> I used the MEMO field-type in Access. When I used the TEXT field in SQL
server
> some weird stuff happens. When I query the record in a recordset and
display
> the results on a standard ASP page - one or another fields will not print
out.
> No matter what I try - it seems that one field won't display and there is
no rhyme or reason.
> When I look in the DB I see the DATA but it wont print on the screen like
response.write rs("news_story")
> What would cause a field not to print? somewhat hap-hazardly?
> SHould I use the text datatype? For ex. when I use vchar it doesnt happen.
But my text
> gets truncated. Please Help - I am ready to pull my hair out
>

datatype performance

Hi, I'm a webmaster of http://www.jivejewelry.com. Somehow the website seems slows. The developer told me that a datatype in the database design could be causing the problem. I don't believe it that is possible. Is this actually possible? Please help.

You need to be more specific. Could a database datatype cause a problem? Sure, if you store all integers as strings, that could slow thngs down. That would not be the most likely scenario.|||

Any suggestion on how I can find the specific cause?

|||

Find a page that is slow. Debug the page, using F10 to stp over statements until you find one or more that are really slow. Step into that until you find what is slowing things down.

Alternately, sprinkle code with Trace statements and turn on tracing to determine what is taking time.

|||Easiest way to see if it's the database: Turn on SQL ServerProfiler. Default options should be fine. Connect it to theproduction server and let it run for a while. Now look at querieswith high values in the Duration column. Finally, go fix them. Big Smile [:D]

Note, running Profiler against a production databasecan causeperformance degredation if your server is especially hammered. Ifyou're not maxing out the server though, this probably will not be anissue.
|||A good example of Datatype for an online store is MONEY and NUMERIC, money may give you rounding problems, numeric will not but it is bigger than money. Hope this helps.|||

Ok. I found the query that got high duration. Reading the execution plan for the query, it does not makes sense. I don't know what to do. Or, how to fix it.

|||Run it in the Query Analyzer and click on execution plan which will show what you need to cut out of the query. You could also post it so someone can run it and fix it for you. Hope this helps,sql

Datatype of column dynamically

Hi All,
How can I get the datatype of a column using a query?
Thanks,
SanjeevUse view information_schema.columns.
Example:
use northwind
go
select
data_type
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 'orders'
and column_name = 'orderid';
AMB
"mahajan.sanjeev@.gmail.com" wrote:

> Hi All,
> How can I get the datatype of a column using a query?
> Thanks,
> Sanjeev
>|||Thanks!
I was going to use a join on sysobjects, syscolumns and systypes to get
it but this looks better!

Datatype nvarchar(max) not accessible

Hi,

trying to input

create table T (c1 nvarchar(max));

in MS SQL Server Manangement Studio Express results in an error :

Fehler beim Analysieren der Abfrage. [ Token line number = 1,Token line offset = 30,Token in error = max ]

create table T (c1 nvarchar(4000));

is processed w/o errors.

I′ve installed SQL Server 2005 Express Ed. SP2

Microsoft SQL Server Management Studio Express 9.00.3042.00

Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)

Microsoft MSXML 2.6 3.0 4.0 5.0 6.0

Microsoft .NET Framework 2.0.50727.42

Betriebssystem 5.1.2600

Any ideas?

Thanks in advance

Werner

This command should work without problems, and did on my computer. Could you check the error log to see if there is more informaiton about the failure there?

Mike

|||

Hi,

thanks for your reply. I think I found the reason.

Being a newcomer I accidently selected "SQL Server Everywhere" and generated a compact sdf-DB. This type of server doesn′t support nvarchar(max) (which is not mentioned in any documentation available in the net).

Setting up a new db (conversion of the sdf to mdf format is not possible (?)) fixed the problem.

btw, I can imagine using large datafields even on a PDA, so what′s the reason for not supporting this datatype (I do see a parallel to those guys of TI in the late 70s who invented the datetime format for PCs, saving 2 byte...)

Thanks anyway for your fast reaction and your help offered

Werner

DataType Money

Please i need to display the money column in DataBase in an asp.net page but i get something like this 786.0000 how can i format it so that i get something like 786.00

Thanx

That kind of formatting is best done at the application level.|||

You could use the ToString("c") to display as currency on your page.

Double TotalCost = 786.0000;

lblCost.Text = TotalCost.ToString("c");

Your label should now be set to $786.00

|||

If you are binding it, you can use <%# Bind("YourColumnName","{0:c}") %> and will display $786.00
If you dont want to display the currency, repace c with your own format like #0.00 at it should display 786.00

Datatype mismatch in export

I am exporting my sql table to the access database. My text fileds in SQL are stored as varchar(50) in SQL server 2005. but when I export the table to Access, These fileds get converted to 'memo' fields. I tried converting these memo fields in access to text field but Access does do that, throws an error message saying not enough disk space.

When I try to convert these 'varchar' fields in sql to text fileds, sql throws a 'time out ' message nad does not convert it to text.

What do I do?

Thanks

As per the guidelines of Microsoft, the equivalent of 'VARCHAR' in Sql server is 'TEXT' in MS-Access. How are you exporting Sql server data to Access? Are you using SSIS? On a side note, equivalent of 'MEMO' is 'TEXT' in Sql Server. I wouldn't recommend you to change the data type in Sql Server to change to 'TEXT'.

Text data type in Sql Server is used to store large texts that go beyond 8KB in size. Usually, sql server keeps a 16 byte pointer to the text data in the page. It can contain upto 2 GB size data. In Sql Server 2005, usage of Text data type is not recommended and varchar(max) is recommended.

http://articles.techrepublic.com.com/5100-6345-5033381.html
https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0561.mspx?mfr=true

datatype map - Native ODBC driver

Hi,

Our software uses native ODBC driver and Visual C++ 2003 to communicate with SQL Server 2005.

SELECT xml_schema_namespace(SCHEMA_NAME(schema_id) ,name) FROM sys.xml_schema_collections.

SQLDescribeColW returns -152 as the datatype for the column in the above select statement. However, I’m unable to find a preprocessor definition for -152. The datatype looks like an nvarchar. Is it an nvarchar? Where can I find the definition for this datatype?

I have discovered similar problems while bringing back SQL_VARIANT types. The returned subtype of a SQLVariant comes up with -16 and -25. I cannot find definitions for these sub types either.

Any help is appreciated.

Regards,

Venkat

-152 is the XML data type and is defined in SQLNCLI.H as SQL_SS_XML. SQLNCLI.H is the header file for the SQL Native Client ODBC driver and OLEDB provider for SQL Server 2005.

I would advice you use the SQL Native Client ODBC driver for the best performance and functionality with SQL Server 2005. You can download the redistributable from http://www.microsoft.com/downloads/details.aspx?FamilyId=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&DisplayLang=en

Regards,

Uwa.

|||

Thank you very much!

Do you know where i can find information about SQL variant sub types -16 & -25? Subtype -16 looks like an integer (4bytes) while subtype -25 looks like a bigint (8Bytes)

Regards,

|||

-16 = "SQL_INTEGER + SQL_SIGNED_OFFSET" ( = 4 + (-20) )

-25 = "SQL_BIGINT + SQL_SIGNED_OFFSET" ( = -5 + (-20) )

So, basically they're SIGNED LONG and SIGNED BIGINT respectively, which explains their sizes of 4bytes and 8bytes.

You can find these defined in the header file SQLEXT.H:

#define SQL_BIGINT (-5)
...

#define SQL_SIGNED_OFFSET (-20)
#define SQL_UNSIGNED_OFFSET (-22)

....

#define SQL_C_SBIGINT (SQL_BIGINT+SQL_SIGNED_OFFSET) /* SIGNED BIGINT */
...

#define SQL_C_SLONG (SQL_C_LONG+SQL_SIGNED_OFFSET) /* SIGNED INTEGER */

As Uwa mentioned above, you'll need to search SQLNCLI.H for the SQL Server 2005 types. Here you can find (-152) defined:

#define SQL_SS_VARIANT (-150)
#define SQL_SS_UDT (-151)
#define SQL_SS_XML (-152)

|||Thanks a lot!sql

DataType Issue

Using SQLserver 2000 I have to enter a huge data (nearly 8000 charcters) into a field, which data type is suitable for that and what is the method to store data.

You might need to use a VARCHAR(8000) or a text datatype; however, there is not enough information.

Is your data divisible into different definite pieces? Do you need to support unicode? Is your data numeric? binary? Is your data XML Is there additional data that needs to be stored with the 8000 characters?

Datatype for Primary key fields ...

Hi,
As far as my understanding goes, normally PK would be set on fields whose
datatype is INT. But in one of the project I saw 99% of the tables they have
used Varchar datatype for PK fields.
This internally means that it would string comparisons. I was arguing that
SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
something? Any pointers on this topic would be of great help to me.
Best Regards
Vadivel
http://vadivel.blogspot.comVadivel wrote:

> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they ha
ve
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
This is the wrong question because the datatype for keys is determined
by the data you need to model in the table. For example how are you
going to represent names using an INTEGER?
If you meant to ask "what should I use for an artiificial surrogate
key?" then you can search the archives of this group for many previous
discussions on that topic.
David Portas
SQL Server MVP
--|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
If you don't use surrogates, I'd say that most natural primary keys are
strings.
Even telephone numbers, license plate numbers, zip codes, serial codes,
credit card numbers, bar codes, invoice numbers, bank account numbers... are
in reality, strings.|||Are you speaking of a natural or surrogate primary key?
http://www.aspfaq.com/show.asp?id=2504
Natural keys can be a combination of most any data type. However, if the key
in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) then
do try to define it as integer, becuase it's storage will be smaller than
character, and it will thus result in less memory usage and fewer index
pages to traverse.
If you plan to use an identity column as a surrogate primary key, then I
don't see a reason to use anything but an integer.
http://www.windowsitpro.com/Article...ArticleID=23449
Data Type Performance Tuning Tips for Microsoft SQL Server
http://www.sql-server-performance.com/datatypes.asp
In general integer based comparisons are more efficient than Char or VarChar
comparisons, but I don't know of SQL Server specifically not being not good
at character comparisons relative to any other DBMS system such as Oracle or
DB2.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com|||I know that telephone numbers, Credit card nos, Zipcode all would be varchar
fields only. As we won't be doing any mathematical calculation based on that
data there isn't a need for us to go for INT datatype. Even though u would
have those fields as Varchar in ur DB won't you have a ID field in that
table? Won't that ID field be of INT datatype?
Best Regards
Vadivel
http://vadivel.blogspot.com
"Raymond D'Anjou" wrote:

> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> If you don't use surrogates, I'd say that most natural primary keys are
> strings.
> Even telephone numbers, license plate numbers, zip codes, serial codes,
> credit card numbers, bar codes, invoice numbers, bank account numbers... a
re
> in reality, strings.
>
>|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
>I know that telephone numbers, Credit card nos, Zipcode all would be
>varchar
> fields only. As we won't be doing any mathematical calculation based on
> that
> data there isn't a need for us to go for INT datatype. Even though u would
> have those fields as Varchar in ur DB won't you have a ID field in that
> table? Won't that ID field be of INT datatype?
> Best Regards
> Vadivel
Read JTs response including the links.
Your ID field (column) is a surrogate.
The use of surrogates (including Identity) has been discussed in this
newsgroup "ad nauseum".
Even though you can use surrogates as keys, there should always exist a
"natural" primary key in your tables.|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:u8lYzCMAGHA.3936@.TK2MSFTNGP12.phx.gbl...
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
> Read JTs response including the links.
> Your ID field (column) is a surrogate.
> The use of surrogates (including Identity) has been discussed in this
> newsgroup "ad nauseum".
> Even though you can use surrogates as keys, there should always exist a
> "natural" primary key in your tables.
One other thing:
Concerning your "performance hit" using INTs versus VARCHARs.
From what I've read, especially in today's databases, if there is one it's
negligable.|||Thanks for the links JT.
Lets assume that I have a table with just two fields,
CityID Varchar(20) -- PK
CityName Varchar(50)
Now I would be using this field CityID in some other table as a FK field. Is
that ok? Or is it advisable / mandatory to have another column with INT
datatype and use it in other tbls as FK?
Best Regards
Vadivel
http://vadivel.blogspot.com
"JT" wrote:

> Are you speaking of a natural or surrogate primary key?
> http://www.aspfaq.com/show.asp?id=2504
> Natural keys can be a combination of most any data type. However, if the k
ey
> in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) th
en
> do try to define it as integer, becuase it's storage will be smaller than
> character, and it will thus result in less memory usage and fewer index
> pages to traverse.
> If you plan to use an identity column as a surrogate primary key, then I
> don't see a reason to use anything but an integer.
> http://www.windowsitpro.com/Article...ArticleID=23449
> Data Type Performance Tuning Tips for Microsoft SQL Server
> http://www.sql-server-performance.com/datatypes.asp
> In general integer based comparisons are more efficient than Char or VarCh
ar
> comparisons, but I don't know of SQL Server specifically not being not goo
d
> at character comparisons relative to any other DBMS system such as Oracle
or
> DB2.
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
>
>|||If CityID is the primary key, then that is the column you want to use as the
foreign key when joining referencing tables. However, I don't understand why
CityID would be 20 characters long. Is this something like an ISO code
assigned to every city on the planet? If CityID contains embedded attributes
like geographic coordinates or nation, state, county codes, then split those
attributes out as seperate columns.
Provide more details about what CityID means and how it's values are
assigned.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:67DAA04A-A277-49AB-8745-521699D2F06F@.microsoft.com...
> Thanks for the links JT.
> Lets assume that I have a table with just two fields,
> CityID Varchar(20) -- PK
> CityName Varchar(50)
> Now I would be using this field CityID in some other table as a FK field.
> Is
> that ok? Or is it advisable / mandatory to have another column with INT
> datatype and use it in other tbls as FK?
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "JT" wrote:
>

Datatype for Primary Key

I'd like to use uniqueidentifier in my database as Primary Key
Is this a good idea?
How does it effect performance?
Plz help
ApogeeDo you mean a GUID? If so I would think this is quite a long field for a
primary key to be based on.
If you do use it, and you are generating a random one every time, I would
make sure the index isn't clustered, because you won't be inserting to the
bottom of the table.
"Apogee" <developer@.bitefish.net> wrote in message
news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> I'd like to use uniqueidentifier in my database as Primary Key
> Is this a good idea?
> How does it effect performance?
> Plz help
> Apogee
>|||What disadvantages would this have? (not clustered)
Apogee
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:eeHKXxbUDHA.1692@.TK2MSFTNGP11.phx.gbl...
> Do you mean a GUID? If so I would think this is quite a long field for a
> primary key to be based on.
> If you do use it, and you are generating a random one every time, I would
> make sure the index isn't clustered, because you won't be inserting to the
> bottom of the table.
> "Apogee" <developer@.bitefish.net> wrote in message
> news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> > I'd like to use uniqueidentifier in my database as Primary Key
> >
> > Is this a good idea?
> > How does it effect performance?
> >
> > Plz help
> >
> > Apogee
> >
> >
>|||I don't know a huge amount about this subject but I believe using a
clustered index, the data is actually stored in the order of the index. A
clustered index is therefore the fastest type. A non clustered is a normal
index, which contains the information you have included in your index, and
also a link to where the actual record is stored.
If you insert lots of records in the middle of a clustered index, the server
will have to do a certain amount or re-jigging of the data to keep it in the
actual order of the primary key.
I would suggest using a clustered index if you are using an auto increment
primary key, and a non clustered index if you are inserting random values.
If the tables are small, or without much activity, this is largely
irrelevant though.
Ryan
"Apogee" <developer@.bitefish.net> wrote in message
news:Oi88I%23bUDHA.2200@.TK2MSFTNGP11.phx.gbl...
> What disadvantages would this have? (not clustered)
> Apogee
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:eeHKXxbUDHA.1692@.TK2MSFTNGP11.phx.gbl...
> > Do you mean a GUID? If so I would think this is quite a long field for
a
> > primary key to be based on.
> >
> > If you do use it, and you are generating a random one every time, I
would
> > make sure the index isn't clustered, because you won't be inserting to
the
> > bottom of the table.
> >
> > "Apogee" <developer@.bitefish.net> wrote in message
> > news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> > > I'd like to use uniqueidentifier in my database as Primary Key
> > >
> > > Is this a good idea?
> > > How does it effect performance?
> > >
> > > Plz help
> > >
> > > Apogee
> > >
> > >
> >
> >
>|||I don't like using a GUID for an artificial primary key. It takes up 4 times
as much space as an int (16 bytes vs 4 bytes), and as the primary key is
referenced in other tables and indexes, this can add up to a large amount of
unnecessary space in your database, negatively impacting performance. You
can store more than 2 billion rows in a table when you have a IDENTITY
column starting at 1 with an INT datatype, and that is enough for most
applications. If it isn't you can always use a BIGINT (8 bytes) datatype.
Using GUIDs also makes debugging more difficult than using identity, because
humans are better at remembering 1-2-3 than at remembering 32 character
hexadecimal strings. And with IDENTITY the inserts are generated in order,
which can help debugging as well.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Apogee" <developer@.bitefish.net> wrote in message
news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> I'd like to use uniqueidentifier in my database as Primary Key
> Is this a good idea?
> How does it effect performance?
> Plz help
> Apogee
>

Datatype for JPEG image

Hi Group,
What SQL Datatype would I use to hold a JPEG file?
Kind RegardsIMAGE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_5f1d.asp).

-PatP

DataType converting, char to float

I need to convert Data Types in a table where all fields are char, so that the ones containing numeric values is decimal or float.
I've tried this query:
SELECT Ansilumens, CAST(RTRIM(Ansilumens) AS FLOAT) FROM dbo.projector
And I get the error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float.

Does anyone how I can convert chars to decimals or floats? When the tabel already contains lots of data, without loosing it..

Thanks.--Find bad values
select YourCol
from YourTable
where isnumeric(YourCol)=0

/*
Then choose, what numeric type do you want.
Float is usually used in science for storing inaccurate very high range values. Stored as single/double real binary.
Int-like datatype for integers (without decimal places). Stored as sign fixed binary.
Numeric for precise calculations, large nums (10^36), fixed decimals. Stored as sign nibble (2 decimal digits in one byte)
rounded up to 1+4n bytes.
Money is fast predefined numeric with special rounding and 4 decimal places. Stored as sign fixed binary.
*/

--if your nums are really large, try FLOAT(53) or NUMERIC(38) or ballanced NUMERIC(32,16)

--Sending test values would be your benefit.|||If you plan on using numeric or decimal data types you really have to know what is the largest numeric value you will use - not just to the left of the decimal but to the right as well (your precision and scale) otherwise you will receive an arithmetic overflow error. Float has the same issues with precision.

The following post discusses this problem:

post (http://dbforums.com/showthread.php?threadid=554550)|||Well, my problem (after some more testing) seems that everything I try to insert from VB6 is impossible to get into the db unless the datatype in the db is char, varchar, timestamp or text that is.
This is the code:

Dim InsertQuery As String
Set oConn = New Connection

Set oRec = New Recordset
oRec.Open InsertQuery, oConn

is my problem related to that everything in InsertQuery, is of course, a string when the SQL query executes?

-jr|||You need to post an actual insert statement and the data types as defined by the table.|||InsertQuery = "INSERT INTO black (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', '" & Now & "', '432.95');"

Is a test-query I use

And the table is defined as follows:
Part - char - size 11 - Do not allow nulls
Serial - char - size 8 - Do not allow nulls
[Time] - datetime - Do not allow nulls
S0 - decimal - Precision: 2 - Do not allow nulls|||Your post has 2 main problems
1. You do not specify scale for decimal. Minimum numeric(5,2) for this insert.
2. You use VB Now() function, which is setting-specific. Use getdate() on server.

create table testNum(
Part char (11) not null
,Serial char(8) not null
,[Time] datetime not null
,S0 decimal(15,2) not null
)
GO
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), '432.95')
--faster
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), 432.95)|||What is the most numbers to the left of the decimal and to the right for the column S0 ? Once you know this, create that field with a precision of the 2 maximums combined and the scale of the maximum of the length to the right of the decimal.|||Thanks rnealejr and ispaleny... it was clearly my SQL knowledge (or the lack of it) that was the problem. Works great now, though. Thanks again.|||Just one more thing..
When the value is (e.g) 2300.00 the .00 is not showing in the db, how to make the decimals show even though they are only zeroes (0)?|||This will happen in enterprise manager - run a query in query analyzer and you will see them.

Datatype convert char to numeric

Hi,

I read the topic from JROdden and this case is similiar but...

I got several varchar fields with
values like
1.2
1.3
... these I can covert with
select CONVERT(dec(5,2), fieldname) as fieldname

In fact I also solved undefined- and NULL-values with.
CONVERT(decimal(12, 2), CASE WHEN GESCHKOSTMAX IS NULL OR
GESCHKOSTMAX < '0' THEN '0' ELSE GESCHKOSTMAX END) as GESCHKOSTMAX,

But now there are values like
1,4 and these ones neither CONVERT nor CAST will handle.

I tried the
SELECT DISTINCT KMPAUSCHALE
FROM extr_INTFIRMA
WHERE (isnumeric(KMPAUSCHALE) = 1)

and get
0,40
0.25
0.30 and so on...

The error is:
[Microsoft][ODBC SQL Driver][SQL Server]Error converting datatype varchar to decimal. (or float or numeric (whatever I tried))

I think the easiest way would be to insist on higher data quality but
I also would like to solve this interesting challenge.

Thanks for any hints

By the way, I followed rudys link to
http://rudy.ca/afdb.html
and now I know how I could protect myself !!!!

There must be a voice in my head saying:
Try the db-forum, try it and stay happy... ;-)

best regards and have fun with new year eve.

MichaelWhy are you worried about < 0? And how do you now they will translate to 5,2?

You should be more worried about other chars that don't translate...

SELECT CONVERT(float,ISNULL(REPLACE(GESCHKOSTMAX,',',',') ,0))
FROM extr_INTFIRMA
WHERE ISNUMERIC(REPLACE(GESCHKOSTMAX,',',','))=1

My question to you is...what do you do with the data that doesn't fit this profile?

You're exclusing an entire population of potentially valid data...

Yes you should more tightly define the columns datatype...

It's probably going to require data cleaning though...|||Originally posted by Michael Kaiser
By the way, I followed rudys link to
http://rudy.ca/afdb.html
and now I know how I could protect myself !!!!

:cool:|||Originally posted by r937
:cool:

Hi Brett,

thank you very much for that SQL-command.
I tried
SELECT CONVERT(decimal(15, 4), ISNULL(REPLACE(HERUMSATZWELT, ',', '.'), 0)) AS Expr1
FROM extr_INTFIRMA
WHERE (ISNUMERIC(REPLACE(HERUMSATZWELT, ',', '.')) = 1)

(please note the point I replaced) and it worked well.

Nevertheless there are some field which do not contain NULLS.
The ASCII(fieldname) gives me NULL as result.
So I guess this field is "not defined".

This is the reason why I' m worried about < 0.
All "undefined" went to "0".
Not very elegant but it worked...

best regards

Michael|||Originally posted by Michael Kaiser
Nevertheless there are some field which do not contain NULLS.
The ASCII(fieldname) gives me NULL as result.
So I guess this field is "not defined".

This is the reason why I' m worried about < 0.
All "undefined" went to "0".
Not very elegant but it worked...

best regards

Michael

I don't understand...ISNULL() Will only give you a 0 if the value is NULL

And what is ASCII(fieldname) for?

And I don't understand "undefined"

But hey, as long as it work

Ihr Willkommen|||Hi Brett,

the data are coming from a web-frontend into an access-db.
Obviosly the user don't have to fillout all questions and therefore
some fields remain empty.
Others change to "NULL".
I don't know why.
I imported the data to SQL server and
checked them with select distinct
I get two "empty" fields in the result.
One contains "NULL" the other "nothing" ?
I tried ASCII(fieldname) to search for "nonvisible" data but
this ASCII-command results in NULL.

When trying your command it fails because of this empty fields.

I checked the SQL online help and found the
NonEmpty function but that will not work within a sql-command
(in my view)

Whatsoever...
now it is time to go to a new years eve party...

I will try it again next year!!!

By the way...

your welcome = Ihr Willkommen
is very, very strange - hihi -

your welcome best fits in german - gern geschehen -

But I get the idea.

Thanks for your help and tons of fun the next hours...

Michael|||und einen guten rutsch ins neue jahr !!

rudy|||Hey, that's what I get for a google translation...

anyway...happy new year!

And what you're describing is an empty string, which is not null

SELECT COUNT(*)
FROM extr_INTFIRMA
WHERE GESCHKOSTMAX = ''

Should show you how many

Have a liter or 2 for me!|||Rudy - I tried selling my version of that device but no luck. I have been wearing mine for several years now - and I feel so much safer today - no aliens talking in my head anymore ... :-)

Thanks for posting that - I have not seen that for a couple of years now and it always makes me laugh.

DataType Conversion using WHERE IN ( )

I am getting a "Syntax error converting the varchar value '10,90' to a column of data type int." error when I run the following procedure:

@.myList varchar(200)

SELECT column1
FROM table1
WHERE table1.ID IN (@.myList)

When @.myList is a single value, I get no errors. However, when @.myList is a comma separated list like in the message above, I error out. I am using SQL Server 2000.

How else can I build this list of IDs? Thank you in advance for your comments.

--ColonelYou cannot do what you are trying to do. YOu need to use dynamic SQL, or send in a string and use a function to create a table variable and do the operation based upon a select on that table variable.|||I found that my varchar parameter was being sent in with single quotes around it. I removed these, and now my WHERE clause looks like this:

WHERE table1.ID IN (REPLACE(@.myList,'''',NULL))

and it works just fine.

I did not add those quotes to the list of values. I believe that SQL Server adds them to delimit the text. Thank you for your comments.

Datatype conversion strangeness

Hi all,

After more headbanging and cursing than an entire Metallica audience, I have finally deduced how to store and calculate the values of a stack storing RPN calculations using a recursive stored procedure. But there is a new conundrum. Originally, to test the above, I was using fairly simple values -- 10, 20, +, 3, /, etc. But the real data is likely to include values with at least a couple of decimal places -- these have been configured using the money data type. Nevertheless, in my actual stack table the value needs to be specified as a varchar. However, as soon as I start sending in 0.68 as a varchar to my stored procedure, it gives an error.

Can anyone offer any light on this?

(The error message number is 245, data type conversion error. But this is inexplicable as I have commented out any conversion code in order to get to the root of the problem. As far as I am aware this value "0.68" is always being passed around as a varchar.)

Thanks.

But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors. It is hard to tell what is wrong without looking at some repro. You should however avoid doing these type of operations since the potential for misinterpretation or run-time error is large. If you are dealing with numeric values then specify the data type of the variables accordingly.|||Umachandar,

Many thanks for your reply. The reason I'm doing it this way is that I'm modelling a stack. Also some values are actually the tablename.objectname id of values in a read-only section of the database, so the <value> part of this stack has to be flexible -- hence varchar. Surely the potential for runtime error is not that great, as I only want to be able to handle values such as the following:-

table1.id1 (this gets parsed by the stored procedure into values such as follow...)
0.68
0.43
10
20
+
*

|||Again, it is hard to tell what is wrong without looking at some piece of code that reproes the error. Alternatively, you could run profiler trace with statement level events to see which statement is failing (you can even get this from the error message header which will point to the line that generated the error).|||OK let me try to explain as fully as possible.

I have a table NEO_FORMULA_STACK (f_id(int), position(int), val(varchar(100)), valname(varchar(100)), units (nvarchar(12).

I have a stored proc, which accepts f_id (as above) as a parameter. Based on this f_id it then loads the 'stack' of values into table TEMP_STACK (pos(int),val(varchar(100).

What I mean is that there are multiple formulas in the original table each with their own 'stack' of values (arranged in Reverse Polish Notation format, i.e. 10,20,+,3,/ [a way of writing ((10+20)/3).

Now, in the course of the load from NEO_FORMULA_STACK TO TEMP_STACK conversions such as tablename.objectname being evaluated to e.g. 0.098 might very well occur. But my impression is that if NEO_FORMULA_STACK has a val column of VARCHAR(100) datatype, and so does TEMP_STACK, then there should be absolutely no problem going from 'tablename.objectname' to '0.000918'. These are BOTH completely valid strings. Bear in mind that NO calculation has taken place at this point, I have simply loaded NEO_FORMULA_STACK for a particular f_id into TEMP_STACK. Attempting to pop off a value like '0.0098' into a temporary variable of varchar(100) is throwing error: 245, converting from varchar to int at line 77 of the stored proc:-

I can't give you the exact line, because one sp is calling another so the line it cites as being in the wrong is a comment.

Please let me know if you need more detail, or if you have any clues based on this information. Many thanks!
|||"But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors."

I assure you I am doing no concatenation or anything like that. I removed all such code in order to try and attack the problem. I am loading one varchar into another varchar, then popping that varchar off into a temporary variable of type varchar -- and it is that pop which is throwing the error, saying illegal conversion, when as far as I can see they are ALL varchar(100).
|||Your explanation doesn't really help to clarify the exact operations being done in the SP. As I said, this error happens due to implicit conversions of values of different types or initialization of variables/parameters using value of different data type. Add additional debug statements to see the results before the input so you can identify the issue. Or create a simpler repro that will help you identify the problem due to implicit conversion.

Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.Not meaning to be thick here, but why are you changing a varchar to a binary? Did you want instead to change it to nvarchar?

Regards,

hmscott|||In this case, you are implying that you want to convert varchar data to binary. I don't think that can be done automatically. The error might be misleading.

If that's the only field, it shouldn't give that error, but a table consisiting of only a binary field seems like it's not very useful. Is a blob out of the question? it only takes up 16bytes of the page. Yould definetely need to export/import then.

You should be able to add a binary column, or export the data, recreate the table with a binary field, and then import the data, with suitable massaging.|||You could use varbinary, but if the amount of data in the row exceeds 8060, you will get errors, instead of warnings.

Datatype Conversion during insert

I am performing an insert inside a stored procedure. In the values list , I am doing some data converision. I am getting an compile error like:

Server: Msg 170, Level 15, State 1, Procedure premiumstage_to_fact, Line 303
Line 303: Incorrect syntax near '='.
The code is:

INSERT INTO table-name( c1,c2,c3,c4)

VALUES
(@.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3)

Where am I going wrong? Where do I do the conversion? The comma after END AS @.variable, Is that syntax right?

Please advise.

ThanksUse SELECT instead of VALUES.

INSERT INTO table-name( c1,c2,c3,c4)

SELECT @.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3|||snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.|||Originally posted by ms_sql_dba
snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.

to ms_sql_dba:

You are right - it works for 2000. I am not sure about 7. May somebody test it and reply.

create table test13(id int,code varchar(10))
go
insert test13 values(1,case when 1=1 then 1 else 0 end)
insert test13 values(1,'4'+'5')

Datatype COnversion chart

Do you have a SQL Datataype conversion chart, ie a chart that represents if for eg an INT datatype can be converted to Datetime... etc...?

I dont need the one on SQL Books online. But need some other chart that lists out the datatype covnersions in SQL

If you are using T-SQL to do the 'conversion', the chart in Books Online is the definitive source.

And since it is the most complete source, I guess I don't understand what you are hoping to find.

|||

What does this mean? "lists out the datatype covnersions in SQL"

There are a couple of things here. Are you talking about implicit conversions? Or what types you can convert using cast and convert? Both are included in the chart in BOL in the CAST and CONVERT section.

Can you give us a sample of what you are looking for?

|||Btw, if you don't find the information in the Books Online topic useful or not satisfactory then please use the "Send Feedback" link in the topic. This will help you to provide feedback directly to the topic owners and will help improve the quality of the documentation.|||

I tried to convert an INT to a datetime, it gave an error "Arithmetic overflow error converting expression to data type datetime."

So I first converted it to varchar, and then to datetime, then it worked fine. eg

select convert(datetime,convert(varchar,xxx)),xxx,* from tblq

However, when I looked up, the BOL, it shows That Conversion from INT TO DATETIME, AND VARCHAR TO DATETIME Are both IMPLICT... Then how come the convert from Int to datetime errored out....?

I guess I am not able to Interpret the BOL Doc, for Implied and Explict conversions.. If it says impied conversion for both Varchar to datetime, and Int to datetime-- then why does 1 work and not the other.

|||

Sounds like a typo in the conversion -number too big, or perhaps, not a number ...

The following works as expected:

select convert( datetime, 2958463 )

9999-12-31 00:00:00.000

The following fails with the error you received -only 1 digit larger...

select convert( datetime, 2958464 )

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

|||hi,
what's the integer you're trying to convert as datetime? if it's greater than 2,958,463, it will produce that error because i think sqlserver supports datetime from 01/01/17? [convert(datetime, -53690)] to 12/31/9999 [convert(datetime, 2958463)].

- clintz|||

The Integer I am trying to convert is a large one, ie

select convert(datetime,20060404)
It is has a date value.

But I still need to know what is the diff between the Implicit and explicit conversions. BOL was not clear enough.

|||

Is 20060404 an integer, or a date? (It looks like a date to me...)

There is a really, really, big difference.

(The interger value 38809 would convert to 2006/04/04.)

As I, and others indicated in other responses to this post, the largest integer value that will convert to a datetime is 2958463. Your number, 20060404, greatly exceeds that value and WILL always cause an overflow error.

|||

If your data looks like 20060404 and is stored as interger values, and you wish to convert them to datetime values, try something like this:

First convert (or cast) to char(8), and then cast that to a datetime.

Code Snippet

SELECT cast( cast( 20060404 AS char(8)) AS datetime )

2006-04-04 00:00:00.000

Datatype change INT to BIGINT on a large table

Hi folks,
I have a table which is of 500 GB in size. I need to change the datatype
of a column from INT to BIGINT.
When I tried making this change from Enterprise Manager, it was throwing
log space is full. I also truncated the log and tried again, eventhen I
face the same problem.
Also I have limited space available on data drive. After some research I
found that SQL Server interally creates a Tmp table with the new
datatype, populates that table with orginal table data, drops the
original table and then renames the Tmp table.
So I must need atleast 500 GB additional freespace on data drive, but I
do not have 500 GB free space on data drive.
I am just thinking the below alternate way to do this task.
1. BCP out the data to a temporary mapped network drive which has 500 GB
free space.
2. Drop the table.
3. Recreate the table with BIGINT datatype on the required column.
4. BCP in the data.
5. Recreate the Keys and constraints.
Can someone suggest me whether this is the best way, any possibility of
loosing the data if I follow this way. Please suggest me if there is a
better approach.
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***Another option is to use ALTER TABLE ... ALTER COLUMN ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Tibor,
If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
file?
*** Sent via Developersdex http://www.examnotes.net ***|||You need to test first. Create a similar table in a smaller database, copy o
ver a subset of the rows
and do a test. Sometimes, these changes can go without touching the data (im
mediately), sometimes,
all data is changed immediately, and changes has to be logged. I haven't see
n any document
describing the exact rules for when a change is immediate or not. So, do a t
est first to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OkPRGnDyFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor,
> If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
> file?
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||I think your bulk copy solution is the best way to go. To reduce the size of
the exported file, use native format instead of fixed width or tab delimited
text columns. To reduce transaction logging while importing the data, set
the database recovery model to "bulk insert" or "simple", set the database
to single user / dbo use only mode, and re-create indexes only after the
import has completed.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks JT for your excellent suggestions/comments.
I tried Tibor's suggestion (ALTER TABLE...ALTER COLUMN), it logs to
transaction log even when the recovery model is set to simple.
Internally it updates all the rows. But I do not have enough space on
log drive. So I will have to go with BCP option.
*** Sent via Developersdex http://www.examnotes.net ***|||I tried BCPing with Native format and Char format option. It seems that
the the file unloaded using Char format is smaller than the one created
with Native format. Any ideas..
--
*** Sent via Developersdex http://www.examnotes.net ***|||I don't recall offhand the specifics, but there are cases (perhaps with
decimal data types) where exporting to char format and then re-importing
will cause loss of data resolution. If you have space for exporting to
native format, then go ahead and use that.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:%235R%23ikNyFHA.3420@.TK2MSFTNGP10.phx.gbl...
>I tried BCPing with Native format and Char format option. It seems that
> the the file unloaded using Char format is smaller than the one created
> with Native format. Any ideas..
> --
> *** Sent via Developersdex http://www.examnotes.net ***sql

DataType change

I recieve a text file with a field that has a text format of 20010910. I import this data into sql server 7.0 utilizing the import wizard. (The wizard will not convert the data to datetime format) I need to know how to convert this data into date format of 2001/09/10. How do I do this in sql.

Thank You,
David A. FullertonYou can add a string column to your table and CONVERT in a post-importing execution or use Visual Basic CDATE in Transformation tab in DTS just before click OK in usual IMPORT... dialog|||CDATE will not work in this example. However, I agree with Cesar's recommendations. In this case, since using vbscript will be more complicated than using cdate you can try the following after the data is loaded:

select convert(varchar(10), cast('20010920' as datetime), 111)|||Thank You That worked..Have a great day!|||Happy to help and good luck.

Datatype change

Hello

I'am trying to change a datatype from nvarchar(10) to nvarchar(50).

My database is a merge replication.

Is this possilbe to do without any major workload.

If you're on SQL 2005, you shoudl be able to do ALTER TABLE command.

datatype casting in derived column

Here is my expression in a derived component:

"Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO

PROP_NO comes from ms sql server , and the derived component datatype for this column is DT_WSTR.

The destination will be ms sql server, and i have a data conversion after the derived component to cast from DT_WSTR to DT_STR.

However, the derived component failed everytime giving me

Error: 0xC0049064 at Load Ponl_Well, Derived Column [1342]: An error occurred while attempting to perform a type cast.

Anyone know how i can eliminate the data conversion component and just do my string and column concatenation in the derived column and have it output as DT_STR?

Sub-expressions and literals in a derived column expression are always DT_WSTR, but you can cast the expression result to DT_STR by wrapping the whole expression with a cast:

(DT_STR,<length>,<codepage>)("Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO)

That should eliminate the need for the data convert transform after the derived column.

However, the error you listed looks like it was coming from the derived column, and I'm not sure why that would be the case if PROP_NO is of type DT_WSTR...

|||thanks for trying, but i'm still getting the same error msg, anyhow, i create a script component and concatenate the strings together, not the solution i wanted but it should work for now

Datatype <uniqueidentifier>

When creating table, now we usually include the colunm(Id) whose datatype is uniqueidentifier.

My first question is why choosing datatype as uniqueidentifier instead of int[identity(1,1)] if no replication needed.

My 2nd question is for the uniqueidentifier column, which index is appropriate for the column, cluster or non-cluster?

And the third question is how to set the default for the uniqueidentifier.which function is better, NewID() or NEWSEQUENTIALID()?

Thanks in advance.

uniqueidentifier is a type of GUID which means that it is unique. It is larger than int style fields and unless you are using NEWSEQUENTIALID() the new values are going to be random (so don't cluster on it unless you use that - or your insertion point will be random in your index). Even with NEWSEQUENTIALID() the values will not normally be consecutive, just increasing.

Generally I would use an identity column for a single table with no multi-source issues (uniqueidentifier is good if you are taking records from various places and merging them). It is smaller (4bytes for int, 8bytes for bigint as opposed to 16bytes for uniqueidentifier), and it is much easier to type in a SQL statement. It is also easier for the processor and memory access to deal with as a value.

As noted it is not a good idea to cluster on an random uniqueidentifier. Even if it increasing why do you want to cluster on it. Generally you do not query by a range of GUIDs. As clustering determines the grouping of the records on disk it is normally better to cluster by something that matches your common querying - to minimise the amount of disk access to return the record. Single record access (if you do retrieve by GUID) is not really affected by clustering as you are only after one record on one datapage.

|||

Here is some additional information that will help you resolve your question. (Generally, avoid GUIDs in situations where Replication is not involved.

GUID -Identity and Primary Keys
http://sqlteam.com/item.asp?ItemID=2599

GUID -Is not Always GOOD
http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx

GUID -The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.asp?p=25862&rl=1

GUID -Uniqueidentifier vs. IDENTITY
http://sqlteam.com/item.asp?ItemID=283

|||

1. Identity is better to use if you never planned for replication. It is one of the biggest datatype (16 Bytes) in the sql server. If you are going to use to identitfy your row then better use it Identity (int/smallint/bigint,1,1) is more enough. But GUID allocates 16 Bytes on each row.

2. Creating a index on GUID is really bad idea unless its required. Since the datatype is huge the indexes will occupy more space and the manipulation also slow.

3. Setting default value ColumnName UniqueIdentifier Default NEWID()

Datatype

I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
insert the date and time into a SQL database by using hour(now). I am
having a hard time trying to figure out which datatype to use in SQL to
store this value. I have tried using datetime, char, nchar, text and
nothing seems to work. Anyone have any ideas? Thanks!

Regards, :)

Christopher BowenDo you mean that you want to store a time without a date? This isn't
possible in MSSQL, since there is only a single datetime data type.

http://www.aspfaq.com/show.asp?id=2206
http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||Use a DATETIME or SMALLDATETIME column.

INSERT INTO YourTable (dt_col) VALUES (CURRENT_TIMESTAMP)

--
David Portas
SQL Server MVP
--|||Christopher,

the function call Hour(Now) will return the current hour, which is an
integer. I would expect this information to be of little use. However,
if you want to store this in an SQL-Server database, then a column of
type tinyint would suffice.

HTH,
Gert-Jan

c_bowen@.earthlink.net wrote:
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
> having a hard time trying to figure out which datatype to use in SQL to
> store this value. I have tried using datetime, char, nchar, text and
> nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen|||It would probably be helpful to see your code and it's not 100% clear what
you're trying to do. I'm guessing hour(now) is a .NET function? Does it
simply return the number of the current hour? That would be some sort of
INT, which would be a datatype mismatch with the datatypes you say you've
tried. Your note mentioned "insert the date and time", which wouldn't
simply be the current hour, anyway.

When I want to insert the date and time, I usually use SQL Server's
getdate() function to supply the value. A T-SQL example would be:

create table foo (col1 char(10),col2 datetime)
go
insert foo values ('abc',getdate())
go
select * from foo
go

[results]
(1 row(s) affected)

col1 col2
---- ----------------
abc 2005-02-25 15:16:38.367

(1 row(s) affected)

Use the datetime datatype if you can. In my experience, using character or
numeric types for storing dates and times usually ends up in grief.

By the way, I usually set things up so that SQL Server is responsible for
supplying the time, rather than the application. That way, if the various
workstation's or web server's clocks are a little bit off, the time values
of rows inserted/updated will still be synchronized across your
applications.

<c_bowen@.earthlink.net> wrote in message
news:1109141117.808195.36560@.l41g2000cwc.googlegro ups.com...
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
> having a hard time trying to figure out which datatype to use in SQL to
> store this value. I have tried using datetime, char, nchar, text and
> nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowensql

datatype

what is the diffrent between nchar and varchar in ms sql server 2005Char is an American system where each bite is a letter.
Nchar is international and takes 2 bites per letter and takes more space.

Good Luck.

Datatype

I am trying to convert a NVCHAR datatype to a DATETIME data type is that possible?Only if it's a valid date. Post a simple example of failure along with associated code and data values and let's see what we can do.|||DECLARE @.x TABLE (Col1 nvarchar(25))
INSERT INTO @.x(Col1)
SELECT '12/31/2004 11:59:59' UNION ALL
SELECT 0 UNION ALL
SELECT 'Happy New Year'

SELECT CONVERT(datetime,Col1) FROM @.x WHERE ISDATE(Col1) = 1|||Here is the select statement I am using but when I use it it doesnt just give me the 2004 only it gives me everything in the table
SELECT TM#, LastName, FirstName, [Date]
FROM Revocations_Tbl
WHERE ([Date] BETWEEN '01/01/2004' AND '12/31/2004')

this is what the tables design looks like all the other tables have DATETIME for their datatypes and I have no problems, when I tried to convert the datatype I got alot of error messages|||To be honest...I'd fix the table...

ALSO! Anyone figure out why I'm getting the conversion error in the following...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myRevocations_Tbl99(TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))
GO

INSERT INTO myRevocations_Tbl99 (TM#, LastName, FirstName, [Date])
SELECT 1, 'Kaiser', 'Brett', '10/24/2004' UNION ALL
SELECT 2, 'Kaiser', 'Brett', '01/01/2005' UNION ALL
SELECT 3, 'Kaiser', 'Brett', '12/31/2003' UNION ALL
SELECT 4, 'Desiree', 'A', '01/01/2004' UNION ALL
SELECT 5, 'Desiree', 'Whos', '10/19/2004' UNION ALL
SELECT 6, 'Desiree', 'Your', '10/19/200a' UNION ALL
SELECT 7, 'Desiree', 'Their', '12/31/2004' UNION ALL
SELECT 8, 'A Row', 'Of Data 1', 'aaaaaaaaaa' UNION ALL
SELECT 9, 'A Row', 'Of Data 2', '0' UNION ALL
SELECT 0, 'A Row', 'Of Data 3', '123456789'
GO

-- Your's

DECLARE @.x varchar(10), @.y varchar(10)
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE [Date] BETWEEN @.x AND @.y
GO

-- Mine

DECLARE @.x datetime, @.y datetime
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM (SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE ISDATE([Date])=1) AS XXX
WHERE DATEDIFF(yy,CONVERT(datetime,[Date]),@.x) = 0
AND DATEDIFF(yy,CONVERT(datetime,[Date]),@.y) = 0
GO

SET NOCOUNT OFF
DROP TABLE myRevocations_Tbl99
GO|||ALSO! Anyone figure out why I'm getting the conversion error in the following...

It looks like the optimizer is flattening out your query into a single table scan, eliminating the ISDATE, then choking on the strings that aren't dates. At least that's what the estimated plan indicates.
Seems like an odd thing to do.|||Put the row for [TM#] 8 first, then it will work.

-PatP|||Put the row for [TM#] 8 first, then it will work.

-PatP

Like I'd have any control over the data...|||Well actually Pat, It blows up right away...|||This works...

DECLARE @.xxx table (TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))

INSERT INTO @.xxx (TM#, LastName, FirstName, [Date])
SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE ISDATE([Date])=1

DECLARE @.x datetime, @.y datetime
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM @.xxx
WHERE DATEDIFF(yy,CONVERT(datetime,[Date]),@.x) = 0
AND DATEDIFF(yy,CONVERT(datetime,[Date]),@.y) = 0
GO

Datatype

Is it possible to modify Datatipe using ALTER TABLE?:confused:Yes, it is.

Example:

ALTER TABLE customers
ALTER COLUMN street_address
varchar(35)

If this doesn't help you, search Books Online for a more comprehensive help regarding ALTER TABLE.|||Thanks a lot, it works perfect!!

datatype

does any know how sqlserver 2000 'text' datatype is mapped in sqlserver JDBC driver? where can i find documentation of for data mapping between sqlserver datatypes and jdbc datatypes?
neo wrote:

> does any know how sqlserver 2000 'text' datatype is mapped in sqlserver JDBC driver? where can i find documentation of for data mapping between sqlserver datatypes and jdbc datatypes?
Hi. The simple test would be to query a text column and do a getObject() and see
what sort of object you got. However, it is simple just to tell the driver what
sort of object you want, by doing a getString() if you want a string, getAsciiStream()
if you want a stream, etc.
Joe Weinstein at BEA
|||The document from "SQLServer 2000 driver for JDBC User's Guide and
Reference" - 3 SQL Server 2000 Driver for JDBC - Data Types have this
information.
"neo" <anonymous@.discussions.microsoft.com> bl
news:19024BF8-645E-45E9-B014-DD475462204D@.microsoft.com g...
> does any know how sqlserver 2000 'text' datatype is mapped in sqlserver
JDBC driver? where can i find documentation of for data mapping between
sqlserver datatypes and jdbc datatypes?

Datatransfer speed

Hi,
I have two datatransfer speed problems with inserts,delets and updates of a
replicated database. The replicated database is a mergepublisher with two
subscribers.
When I import data with dts, I have a transfer speed of 5 rows/sec.
How can I increase this speed ?
When the import is ready the merge-agents have a transfer speed of 5
rows/sec, too.
How can I increase this speed, too ?
Best regards
Axel Lanser
Hi,
it isn't a network problem, because the network traffic is 3%.
Best regards
Axel Lanser
|||Probably not - while with merge you can in some situations get your merge
agent to work within a few seconds, you will likely see it take about a
minute or more to complete its tasks. However this is a highly variable
number dependent on the nature of your database activity, number of
subscribers and hardware.
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:7B0D19A9-E4F3-4854-8280-692553454FB5@.microsoft.com...
> Hi,
> I have two datatransfer speed problems with inserts,delets and updates of
> a
> replicated database. The replicated database is a mergepublisher with two
> subscribers.
> When I import data with dts, I have a transfer speed of 5 rows/sec.
> How can I increase this speed ?
> When the import is ready the merge-agents have a transfer speed of 5
> rows/sec, too.
> How can I increase this speed, too ?
> Best regards
> Axel Lanser
sql

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

Tuesday, March 27, 2012

Datatime order by Problem

Hi everybody,
I have onde table called (TB) with two fields : F1(int) and F2(varchar:10)
In this table the field F2 is used to store dates in the format
dd/mm/yyyy (27/12/2002). Its is record as varchar and undesired.
I need to select all records from a single id from the field F1 and
then order by the result set by date to result set be this way:

F1 F2
------------
01 15/12/1975
01 15/12/1980
01 16/12/1998
01 27/12/2003
------------

To do this Im using the follow syntax :

SELECT * FROM TB
WHERE (F1 = '01')
ORDER BY CAST(F2 AS datetime(103))

But it gives the follow message erro trying to select data :

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

How can I convert the format used in the select to order by date type in a SQL Server range datetype to get a result set as above?

Thansk for attention.

Leonardo AlmeidaUse convert(datetime, f2, 103) instead of the cast.sql

DataTime for linked Server

I have a SQL server in Country A (Server A) and another Server in Country B (Server B) Server A has Server B as linked server, I run a stored procedure in Server A and getdate()gives me current date/time for Server A, how can I do getdate() for Server B?

The Getdate() on ServerB might not be the same as ServerA and can cause conflicts for time sensitive data/logic.

Generally for applications spanning countries and timezones it is advised to use UTC time getUTCDate() instead of getdate(). Any localized information to the user can be done by converting the UTC time into local timezone.

|||

Ok. Thanks, how can I convert UTC to local time?

|||

you can use any of the date functions - DateAdd, DateDiff etc..

Thursday, March 29, 2012

Tuesday, March 27, 2012

Blog Archive

About Me