is there any tool / utility , online service / websites that will help me convert a MSSQL 7 database to be compatible to MSSQL2k ?
i have enterprise manager for 7 , but when i try to connect to server which has sql2000 ... i cant .
thanx .You need to uninstall Client Tools for SQL 7.0 and install Client Tools for 2K.
Showing posts with label conversion. Show all posts
Showing posts with label conversion. Show all posts
Tuesday, March 27, 2012
Conversion Tools
Are there any tools to convert Crystal Reports to SSRS out there besides KTL's Crystal Converter?
http://www.microsoft.com/sql/technologies/reporting/partners/crystal-migration.mspx|||
I went through that list and most of those are services. I am looking for tools like Crystal Converter.|||
To my understanding, Crystal (Business Objects) disapprove commercial converters. For this reason, most vendors offer this as a service.
|||Thank you for your help.
conversion to date
HI everyne,
I have a varchar field in one table, which contains data in the form '010706' and I want to convert this to date datatype to 01/07/2006 (Jan 07, 2006). When I just import the data to the other table it gets converted to 7/6/2001, how can I convert it right? Please help.
CAST(RIGHT(datafield,2)+LEFT(datafield,4) AS datetime)
|||Thanks Motley. It works!conversion sql from oracle to SQL Server
Hi Guys, I have this statement that I am converting from Oracle to SQL. Help pls:-) PP_PRICEPOINT_ID is a decimal. What is the appropriate usage..
Oracle
----
update pricepoint set pp_type = decode(substr(pp_pricepoint_id,1,1),7,0,2),
pp_qtybreakindex =substr(pp_pricepoint_id,3,1) where pp_type is null and pp_qtybreakIndex is null;
Here is its SQL
------
UPDATE pricepoint
SET pp_type =
CASE SUBSTRING(pp_pricepoint_id, 1, 1)
WHEN 7 THEN 0
ELSE 2
END,
pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null
------
I am getting the error
The data type decimal is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
-- What's with this?
-- , pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null|||Try converting your pp_pricepoint variable to varchar before using the substring function
Change :
CASE SUBSTRING(pp_pricepoint_id, 1, 1)
For :
CASE SUBSTRING(Cast(pp_pricepoint_id As VarChar) , 1, 1)
Pls Note i didnt check ur substring use for the sintax.
Hope it can help|||Thanks Brett, this worked..
UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
, pp_qtybreakindex = SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null
I am updating 2 values here..sqlsql
Oracle
----
update pricepoint set pp_type = decode(substr(pp_pricepoint_id,1,1),7,0,2),
pp_qtybreakindex =substr(pp_pricepoint_id,3,1) where pp_type is null and pp_qtybreakIndex is null;
Here is its SQL
------
UPDATE pricepoint
SET pp_type =
CASE SUBSTRING(pp_pricepoint_id, 1, 1)
WHEN 7 THEN 0
ELSE 2
END,
pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null
------
I am getting the error
The data type decimal is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
-- What's with this?
-- , pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null|||Try converting your pp_pricepoint variable to varchar before using the substring function
Change :
CASE SUBSTRING(pp_pricepoint_id, 1, 1)
For :
CASE SUBSTRING(Cast(pp_pricepoint_id As VarChar) , 1, 1)
Pls Note i didnt check ur substring use for the sintax.
Hope it can help|||Thanks Brett, this worked..
UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
, pp_qtybreakindex = SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null
I am updating 2 values here..sqlsql
Conversion query
I have 2 date variables passed into my store procedure as follows
@.StartDate datetime,
@.EndDate datetime
however I am getting a conversion error when running the command below which
says "Syntax error converting datetime from character string"
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
') AND (DateEntered <= ' + @.EndDate + ')')
Any suggestions would be welcome.
ThanksYou have to explictly cast the datetime values to characters like so
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= '
+ Cast(@.StartDate As VarChar(20))
+ ') AND (DateEntered <= ' + Cast(@.EndDate As VarChar(20)) + ')')
Thomas
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:E78EE524-27E2-47AF-96E4-60B250AFA884@.microsoft.com...
>I have 2 date variables passed into my store procedure as follows
> @.StartDate datetime,
> @.EndDate datetime
> however I am getting a conversion error when running the command below whi
ch
> says "Syntax error converting datetime from character string"
> PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
> ') AND (DateEntered <= ' + @.EndDate + ')')
> Any suggestions would be welcome.
> Thanks
@.StartDate datetime,
@.EndDate datetime
however I am getting a conversion error when running the command below which
says "Syntax error converting datetime from character string"
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
') AND (DateEntered <= ' + @.EndDate + ')')
Any suggestions would be welcome.
ThanksYou have to explictly cast the datetime values to characters like so
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= '
+ Cast(@.StartDate As VarChar(20))
+ ') AND (DateEntered <= ' + Cast(@.EndDate As VarChar(20)) + ')')
Thomas
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:E78EE524-27E2-47AF-96E4-60B250AFA884@.microsoft.com...
>I have 2 date variables passed into my store procedure as follows
> @.StartDate datetime,
> @.EndDate datetime
> however I am getting a conversion error when running the command below whi
ch
> says "Syntax error converting datetime from character string"
> PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
> ') AND (DateEntered <= ' + @.EndDate + ')')
> Any suggestions would be welcome.
> Thanks
Conversion Probs..
Hi Group,
I am trying to display the multiplication through this way
-------
select 1163436036*100
-------
Getting the error
============================
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
============================
For that reason I was tried to convert that to nvarchar
--------
select convert(numeric(36,2),1163436036*100)
--------
But still getting the error
=============================
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
=============================
Please help me to solve it out..
Thanks and Regards
Arijit Chatterjee(arijitchatterjee123@.yahoo.co.in) writes:
> Hi Group,
> I am trying to display the multiplication through this way
> -------
> select 1163436036*100
> -------
> Getting the error
>============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>============================
> For that reason I was tried to convert that to nvarchar
> --------
> select convert(numeric(36,2),1163436036*100)
> --------
> But still getting the error
>=============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>=============================
> Please help me to solve it out..
I am trying to display the multiplication through this way
-------
select 1163436036*100
-------
Getting the error
============================
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
============================
For that reason I was tried to convert that to nvarchar
--------
select convert(numeric(36,2),1163436036*100)
--------
But still getting the error
=============================
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
=============================
Please help me to solve it out..
Thanks and Regards
Arijit Chatterjee(arijitchatterjee123@.yahoo.co.in) writes:
> Hi Group,
> I am trying to display the multiplication through this way
> -------
> select 1163436036*100
> -------
> Getting the error
>============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>============================
> For that reason I was tried to convert that to nvarchar
> --------
> select convert(numeric(36,2),1163436036*100)
> --------
> But still getting the error
>=============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>=============================
> Please help me to solve it out..
You need to convert one of the numbers in the expression to the
target type you want, for instance:
select 1163436036*convert(bigint, 100)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Arithmetic overflow error converting expression to data type int.
This error message provides the clue as to the underlying cause. You get an
integer result when you multiply 2 integers (1163436036*100) before the
CONVERT. You'll get a numeric(36, 2) result if you CONVERT or CAST at least
one of the values to numeric(36, 2):
SELECT CONVERT(numeric(36,2), 1163436036)*100
SELECT CAST(1163436036 AS numeric(36,2))*100
Since both of the values are integers, you might consider using bigint
instead of numeric if your are using SQL Server 2000:
SELECT CONVERT(bigint, 1163436036)*100
SELECT CAST(1163436036 AS bigint)*100
--
Hope this helps.
Dan Guzman
SQL Server MVP
<arijitchatterjee123@.yahoo.co.in> wrote in message
news:1118148077.784344.140590@.g43g2000cwa.googlegr oups.com...
> Hi Group,
> I am trying to display the multiplication through this way
> -------
> select 1163436036*100
> -------
> Getting the error
> ============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> ============================
> For that reason I was tried to convert that to nvarchar
> --------
> select convert(numeric(36,2),1163436036*100)
> --------
> But still getting the error
> =============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> =============================
> Please help me to solve it out..
> Thanks and Regards
> Arijit Chatterjee|||Thanks,
Thanks for your great support.
Regards
Arijit Chatterjee
Conversion problems between mssql and access
Hello
When I trying to insert data with datatype datetime or smalldatetime from SQL Server into a table in a linked access database I get this error :
Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type smalldatetime to float is not allowed. Use the CONVERT function to run this query.
I dont understand why it try to insert it as a float?!Because from a SQL Server point of view DateTime values ARE float values.
So you cannot do an implict cast but you have to do explictly by using the T-SQL statement "CONVERT"
Look in Books On Line for better help on conversions.|||Hello
I have tried CONVERT. But I dont know to which datatype I should convert my source value.
I have converted to varchar and nvarchar but I still got the same error.|||can you post your code, and specify from what kind of data type you what convert to?|||INSERT INTO LINKEDACCESS...ContactTarget
Select ChangedBy, ChangedDate, PersonIdNo,IdNo,TargetCode,TargetCodeproductCode,T argetCodePotentialCode
from tblContactTarget
WHERE Id NOT IN(select Id from LINKEDACCESS...ContactTarget CT
WHERE CT.Target = tblContactTarget.TargetCode)
ChangedDate is of datatype Datetime in SQL and Date/Time in Access.
This INSERT will trigger the error I wrote about.|||Try to convert it to a varchar. use the CONVERT function so that you can even specify the dateformat you need to have
When I trying to insert data with datatype datetime or smalldatetime from SQL Server into a table in a linked access database I get this error :
Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type smalldatetime to float is not allowed. Use the CONVERT function to run this query.
I dont understand why it try to insert it as a float?!Because from a SQL Server point of view DateTime values ARE float values.
So you cannot do an implict cast but you have to do explictly by using the T-SQL statement "CONVERT"
Look in Books On Line for better help on conversions.|||Hello
I have tried CONVERT. But I dont know to which datatype I should convert my source value.
I have converted to varchar and nvarchar but I still got the same error.|||can you post your code, and specify from what kind of data type you what convert to?|||INSERT INTO LINKEDACCESS...ContactTarget
Select ChangedBy, ChangedDate, PersonIdNo,IdNo,TargetCode,TargetCodeproductCode,T argetCodePotentialCode
from tblContactTarget
WHERE Id NOT IN(select Id from LINKEDACCESS...ContactTarget CT
WHERE CT.Target = tblContactTarget.TargetCode)
ChangedDate is of datatype Datetime in SQL and Date/Time in Access.
This INSERT will trigger the error I wrote about.|||Try to convert it to a varchar. use the CONVERT function so that you can even specify the dateformat you need to have
Sunday, March 25, 2012
Conversion Problem
I want convert a mysql database in SQL Server 2000. SQL Server forecastes
the possibility to view graphically the relationship between all tables
containing in my database with relative structure by graphic.
About mysql i don't know as actes.
Thanks and i'm sorry for my probable encorrected english but i'm italian boyernix (@.*.it) writes:
> I want convert a mysql database in SQL Server 2000. SQL Server forecastes
> the possibility to view graphically the relationship between all tables
> containing in my database with relative structure by graphic.
> About mysql i don't know as actes.
the possibility to view graphically the relationship between all tables
containing in my database with relative structure by graphic.
About mysql i don't know as actes.
Thanks and i'm sorry for my probable encorrected english but i'm italian boyernix (@.*.it) writes:
> I want convert a mysql database in SQL Server 2000. SQL Server forecastes
> the possibility to view graphically the relationship between all tables
> containing in my database with relative structure by graphic.
> About mysql i don't know as actes.
I'm a little uncertain what you are really asking about. But it is true
that in Enterprise Manager includes a simple diagram feature. However,
this is not an integral part of SQL Server, and there are third power
modelling tools which are much more powerful in this regard. These tools
covers all major RDBMS's, although I don't know about how well MySQL is
supported. The major tools in this area are ErWin (Computer Associaties),
PowerDesigner (Sybase) and Embrocadero (I think their product is called
DataArtisan.) There are also freeware tools in this area, although I don't
have any names.
If you are considering a move only because of the graphic diagram in
Enterprise Manager, frankly, I don't consider this worth the effort.
There might be other and better reasons to make the change, but switching
from one DB engine to another is nothing you do lightly, because of
difference in language and mindset.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Conversion of Varchar into float
We convert a varchar column into float so that the data is ordered logically
like (1,2,10,11) instead of (1,10,11,2).
We have noticed one peculiar issue. When this query runs for a specific
range of inputs for the float values, it selects records which are outide
the inputs ranges
For example, if the query is run for float values between 1 and 10 then
values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
We tried using the float conversion part of the where clause error, but it
gives a data type conversion error . Is there any other method to order the
varchar values logically besides converting into float.
Need forum members help on this
Soura.Could you perhaps post the query so that we know what steps you are
taking to accomplish your goal?|||Hi
Check out http://www.sommarskog.se/arrays-in-sql.html to convert it into an
orderable format, you would then need to reconstitute the string.
John
"SouRa" wrote:
> We convert a varchar column into float so that the data is ordered logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
> We tried using the float conversion part of the where clause error, but it
> gives a data type conversion error . Is there any other method to order the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||Do your numbers have decimals? If not, then you should be using int and not
float.
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> We convert a varchar column into float so that the data is ordered
> logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> ?
> We tried using the float conversion part of the where clause error, but
> it
> gives a data type conversion error . Is there any other method to order
> the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||We do have decmials .I forgot to mention about this in my original post
"Michael D'Angelo" wrote:
> Do your numbers have decimals? If not, then you should be using int and not
> float.
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> > We convert a varchar column into float so that the data is ordered
> > logically
> > like (1,2,10,11) instead of (1,10,11,2).
> >
> > We have noticed one peculiar issue. When this query runs for a specific
> > range of inputs for the float values, it selects records which are outide
> > the inputs ranges
> >
> > For example, if the query is run for float values between 1 and 10 then
> > values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> > ?
> >
> > We tried using the float conversion part of the where clause error, but
> > it
> > gives a data type conversion error . Is there any other method to order
> > the
> > varchar values logically besides converting into float.
> >
> > Need forum members help on this
> >
> > Soura.
> >
> >
> >
>
>|||We have solved this issue by using money instead of float
"nate.vu@.gmail.com" wrote:
> Could you perhaps post the query so that we know what steps you are
> taking to accomplish your goal?
>|||Hi
You may want to use decimal or numeric instead of money.
John
"SouRa" wrote:
> We have solved this issue by using money instead of float
> "nate.vu@.gmail.com" wrote:
> > Could you perhaps post the query so that we know what steps you are
> > taking to accomplish your goal?
> >
> >sqlsql
like (1,2,10,11) instead of (1,10,11,2).
We have noticed one peculiar issue. When this query runs for a specific
range of inputs for the float values, it selects records which are outide
the inputs ranges
For example, if the query is run for float values between 1 and 10 then
values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
We tried using the float conversion part of the where clause error, but it
gives a data type conversion error . Is there any other method to order the
varchar values logically besides converting into float.
Need forum members help on this
Soura.Could you perhaps post the query so that we know what steps you are
taking to accomplish your goal?|||Hi
Check out http://www.sommarskog.se/arrays-in-sql.html to convert it into an
orderable format, you would then need to reconstitute the string.
John
"SouRa" wrote:
> We convert a varchar column into float so that the data is ordered logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
> We tried using the float conversion part of the where clause error, but it
> gives a data type conversion error . Is there any other method to order the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||Do your numbers have decimals? If not, then you should be using int and not
float.
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> We convert a varchar column into float so that the data is ordered
> logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> ?
> We tried using the float conversion part of the where clause error, but
> it
> gives a data type conversion error . Is there any other method to order
> the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||We do have decmials .I forgot to mention about this in my original post
"Michael D'Angelo" wrote:
> Do your numbers have decimals? If not, then you should be using int and not
> float.
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> > We convert a varchar column into float so that the data is ordered
> > logically
> > like (1,2,10,11) instead of (1,10,11,2).
> >
> > We have noticed one peculiar issue. When this query runs for a specific
> > range of inputs for the float values, it selects records which are outide
> > the inputs ranges
> >
> > For example, if the query is run for float values between 1 and 10 then
> > values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> > ?
> >
> > We tried using the float conversion part of the where clause error, but
> > it
> > gives a data type conversion error . Is there any other method to order
> > the
> > varchar values logically besides converting into float.
> >
> > Need forum members help on this
> >
> > Soura.
> >
> >
> >
>
>|||We have solved this issue by using money instead of float
"nate.vu@.gmail.com" wrote:
> Could you perhaps post the query so that we know what steps you are
> taking to accomplish your goal?
>|||Hi
You may want to use decimal or numeric instead of money.
John
"SouRa" wrote:
> We have solved this issue by using money instead of float
> "nate.vu@.gmail.com" wrote:
> > Could you perhaps post the query so that we know what steps you are
> > taking to accomplish your goal?
> >
> >sqlsql
Conversion of Varchar into float
We convert a varchar column into float so that the data is ordered logically
like (1,2,10,11) instead of (1,10,11,2).
We have noticed one peculiar issue. When this query runs for a specific
range of inputs for the float values, it selects records which are outide
the inputs ranges
For example, if the query is run for float values between 1 and 10 then
values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
We tried using the float conversion part of the where clause error, but it
gives a data type conversion error . Is there any other method to order the
varchar values logically besides converting into float.
Need forum members help on this
Soura.Could you perhaps post the query so that we know what steps you are
taking to accomplish your goal?|||Hi
Check out http://www.sommarskog.se/arrays-in-sql.html to convert it into an
orderable format, you would then need to reconstitute the string.
John
"SouRa" wrote:
> We convert a varchar column into float so that the data is ordered logical
ly
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
?
> We tried using the float conversion part of the where clause error, but
it
> gives a data type conversion error . Is there any other method to order th
e
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||Do your numbers have decimals? If not, then you should be using int and not
float.
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> We convert a varchar column into float so that the data is ordered
> logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> ?
> We tried using the float conversion part of the where clause error, but
> it
> gives a data type conversion error . Is there any other method to order
> the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||We do have decmials .I forgot to mention about this in my original post
"Michael D'Angelo" wrote:
> Do your numbers have decimals? If not, then you should be using int and n
ot
> float.
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
>
>|||We have solved this issue by using money instead of float
"nate.vu@.gmail.com" wrote:
> Could you perhaps post the query so that we know what steps you are
> taking to accomplish your goal?
>|||Hi
You may want to use decimal or numeric instead of money.
John
"SouRa" wrote:
[vbcol=seagreen]
> We have solved this issue by using money instead of float
> "nate.vu@.gmail.com" wrote:
>
like (1,2,10,11) instead of (1,10,11,2).
We have noticed one peculiar issue. When this query runs for a specific
range of inputs for the float values, it selects records which are outide
the inputs ranges
For example, if the query is run for float values between 1 and 10 then
values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
We tried using the float conversion part of the where clause error, but it
gives a data type conversion error . Is there any other method to order the
varchar values logically besides converting into float.
Need forum members help on this
Soura.Could you perhaps post the query so that we know what steps you are
taking to accomplish your goal?|||Hi
Check out http://www.sommarskog.se/arrays-in-sql.html to convert it into an
orderable format, you would then need to reconstitute the string.
John
"SouRa" wrote:
> We convert a varchar column into float so that the data is ordered logical
ly
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
?
> We tried using the float conversion part of the where clause error, but
it
> gives a data type conversion error . Is there any other method to order th
e
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||Do your numbers have decimals? If not, then you should be using int and not
float.
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> We convert a varchar column into float so that the data is ordered
> logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> ?
> We tried using the float conversion part of the where clause error, but
> it
> gives a data type conversion error . Is there any other method to order
> the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||We do have decmials .I forgot to mention about this in my original post
"Michael D'Angelo" wrote:
> Do your numbers have decimals? If not, then you should be using int and n
ot
> float.
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
>
>|||We have solved this issue by using money instead of float
"nate.vu@.gmail.com" wrote:
> Could you perhaps post the query so that we know what steps you are
> taking to accomplish your goal?
>|||Hi
You may want to use decimal or numeric instead of money.
John
"SouRa" wrote:
[vbcol=seagreen]
> We have solved this issue by using money instead of float
> "nate.vu@.gmail.com" wrote:
>
Conversion of query from Oracle to SQL Server
Hi Friends,
Need ur help desperately. I am stuck with one of the queries which i had written in Oracle and need the same in SQL Server.Please have a look at the following query :
select * from r_tin_1099_info where instr(translate( nm_ctrl_cd , '~!@.#$%^&*()_+}{":?><`-=]['''';/., ', '*******************************' ),'*') > 0;
Basically my purpose is to replace the values in column NM_CTRL_CD having wild card characters with '*' and then select this rows to display.
However i am not able to run the same query in SQL Server since TRANSLATE is not a built in func. I have tried a lot to replace it but could only one func : REPLACE . But the same will not replace any one of the above wild characters but will replace the entire pattern.Please note that it should be able replace even if one of the wild card characters are present in the string and not necessarily the entire pattern shown above.
please reply ASAP since i am working and need this query to fix a defect.
Thanks in advance.Hi all,
Can somebody please reply to my query mentioned above !!!|||I'd suggest using:SELECT *
FROM r_tin_1099_info
WHERE nm_ctrl_cd LIKE '%[]~!@.#$%^&*()_+}{":?><`-=[]%'
-PatP|||Hey thanks a ton... I will try this out and let you know about the results !!!|||Hi,
The query u have sent does not return any result even though the data is there in the table.Can you please help us out with the query !!
Thanks|||Sorry, I was trying to avoid using escape characters and that got me into trouble. A better solution is:SELECT *
FROM r_tin_1099_info
WHERE nm_ctrl_cd LIKE '%[~!@.#$%^&*()_+}{":?><`x-=x]['';/., ]%' ESCAPE 'x'-PatP|||hey Pat... thanks a lot.. this is working fine... just another question... do u knw any equivalent func for TRANSLATE(in DB2)... bcoz i have a query in DB2 which needs to be translated in SQL Server and since TRANSLATE is not a built in func.. i am not able to execute the same query. the query is as follows:
update r_tin_1099_info set nm_ctrl_cd = substr(replace(translate( coalesce(last_nm,tin_nm_1), '', '~!@.#$%^&*()_+}{":?><`-=]['''';/., ' ),' ',''),1,4) where locate('*',translate( nm_ctrl_cd , '*******************************', '~!@.#$%^&*()_+}{":?><`-=]['''';/., ' )) > 0
I know i mite be asking to much from you.. but it will gr88 if u can guide me with this query as well !!
Thanks.|||Because the second argument to the Translate() call is an empty string, the Translate function will do nothing, it is meaningless so it can be discarded. That leaves you with:UPDATE r_tin_1099_info
SET nm_ctrl_cd = substr(replace(coalesce(last_nm, tin_nm_1), ' ', ''), 1, 4)
WHERE nm_ctrl_cd LIKE '%[~!@.#$%^&*()_+}{":?><`x-=x]['';/., ]%' ESCAPE 'x'-PatP|||hey pat... i tried ur query... its executing without any errors but doesnt seem to update the records... the values having wild characters are not updated and still contain the wild characters... Can you please help me out with this
thanks.|||If you can tell me what you want, I can probably help. The code that you posted in your last question ought to have exactly the same effect as the code that I posted in response to it, but that doesn't appear to be what you actually want.
As I've given you several examples to work from, you ought to be able to get pretty close to what you want on your own. If not, please post:
1) whatever DML you have working
2) A DDL script to build your schema
3) At least a few sample rows of data (BCP native format would be preferred)
4) An example of the output that you'd like from your query
I'll help you, but I can't read your mind and I won't actually do your job for you.
-PatP
Need ur help desperately. I am stuck with one of the queries which i had written in Oracle and need the same in SQL Server.Please have a look at the following query :
select * from r_tin_1099_info where instr(translate( nm_ctrl_cd , '~!@.#$%^&*()_+}{":?><`-=]['''';/., ', '*******************************' ),'*') > 0;
Basically my purpose is to replace the values in column NM_CTRL_CD having wild card characters with '*' and then select this rows to display.
However i am not able to run the same query in SQL Server since TRANSLATE is not a built in func. I have tried a lot to replace it but could only one func : REPLACE . But the same will not replace any one of the above wild characters but will replace the entire pattern.Please note that it should be able replace even if one of the wild card characters are present in the string and not necessarily the entire pattern shown above.
please reply ASAP since i am working and need this query to fix a defect.
Thanks in advance.Hi all,
Can somebody please reply to my query mentioned above !!!|||I'd suggest using:SELECT *
FROM r_tin_1099_info
WHERE nm_ctrl_cd LIKE '%[]~!@.#$%^&*()_+}{":?><`-=[]%'
-PatP|||Hey thanks a ton... I will try this out and let you know about the results !!!|||Hi,
The query u have sent does not return any result even though the data is there in the table.Can you please help us out with the query !!
Thanks|||Sorry, I was trying to avoid using escape characters and that got me into trouble. A better solution is:SELECT *
FROM r_tin_1099_info
WHERE nm_ctrl_cd LIKE '%[~!@.#$%^&*()_+}{":?><`x-=x]['';/., ]%' ESCAPE 'x'-PatP|||hey Pat... thanks a lot.. this is working fine... just another question... do u knw any equivalent func for TRANSLATE(in DB2)... bcoz i have a query in DB2 which needs to be translated in SQL Server and since TRANSLATE is not a built in func.. i am not able to execute the same query. the query is as follows:
update r_tin_1099_info set nm_ctrl_cd = substr(replace(translate( coalesce(last_nm,tin_nm_1), '', '~!@.#$%^&*()_+}{":?><`-=]['''';/., ' ),' ',''),1,4) where locate('*',translate( nm_ctrl_cd , '*******************************', '~!@.#$%^&*()_+}{":?><`-=]['''';/., ' )) > 0
I know i mite be asking to much from you.. but it will gr88 if u can guide me with this query as well !!
Thanks.|||Because the second argument to the Translate() call is an empty string, the Translate function will do nothing, it is meaningless so it can be discarded. That leaves you with:UPDATE r_tin_1099_info
SET nm_ctrl_cd = substr(replace(coalesce(last_nm, tin_nm_1), ' ', ''), 1, 4)
WHERE nm_ctrl_cd LIKE '%[~!@.#$%^&*()_+}{":?><`x-=x]['';/., ]%' ESCAPE 'x'-PatP|||hey pat... i tried ur query... its executing without any errors but doesnt seem to update the records... the values having wild characters are not updated and still contain the wild characters... Can you please help me out with this
thanks.|||If you can tell me what you want, I can probably help. The code that you posted in your last question ought to have exactly the same effect as the code that I posted in response to it, but that doesn't appear to be what you actually want.
As I've given you several examples to work from, you ought to be able to get pretty close to what you want on your own. If not, please post:
1) whatever DML you have working
2) A DDL script to build your schema
3) At least a few sample rows of data (BCP native format would be preferred)
4) An example of the output that you'd like from your query
I'll help you, but I can't read your mind and I won't actually do your job for you.
-PatP
Conversion of procedure making crosstab to function
Hello Everybody,
I have the following problem. I found procedure alolowing me to create
dynamic crosstab and it works fine, but I cannot save the results as a
table. Is there any way to do this? Maybe someone is in possesion of
function which works as below procedure? Or someone is able to change
this procedure to function which returns table which could be used with
command CREATE TABLE?
Procedure looks as follows:
CREATE PROCEDURE sp_TRANSFORM
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Author: svenh@.itrain.de
Version: 1.1
History: march 2000 version 1.0
july 2002 version 1.1
Input parameters:
@.Aggregate_Function (optional)
the aggregate function to use for the pivot
default function is SUM
@.Aggregate_Column
name of column for aggregate
@.TableOrView_Name
name of table or view to use
if name contains spaces or other special
characters [] should be used
Can also be a valid SELECT statement
@.Select_Column
Column for first column in result table
for this column row values are displayed
@.Pivot_Column
Column that is transformed into columns
for this column column values are displayed
@.DEBUG
Set this flag to 1 to get debug-information
Example usage:
Table given aTable
content: Product Salesman Sales
P1 Sa 12
P2 Sb 10
P2 Sb 3
P3 Sa 12
P1 Sc 8
P3 Sa 1
P2 Sa NULL
CALL
EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or EXEC sp_Transform @.Aggregate_Column='Sales',
@.TableOrViewName='aTable',
@.Select_Column='Product',
@.Pivot_Column='Salesman'
Result:
Product| Sa | Sb | Sc | Total
--+--+--+--+--
P1 | 12,00 | 0,00 | 8,00 | 20,00
P2 | 0,00 | 13,00 | 0,00 | 13,00
P3 | 13,00 | 0,00 | 0,00 | 13,00
--+--+--+--+--
Total | 25,00 | 13,00 | 8,00 | 46,00
*/
@.Aggregate_Function nvarchar(30) = 'SUM',
@.Aggregate_Column nvarchar(255),
@.TableOrView_Name nvarchar(255),
@.Select_Column nvarchar(255),
@.Pivot_Column nvarchar(255),
@.DEBUG bit = 0
AS
SET NOCOUNT ON
DECLARE @.TransformPart nvarchar(4000)
DECLARE @.SQLColRetrieval nvarchar(4000)
DECLARE @.SQLSelectIntro nvarchar(4000)
DECLARE @.SQLSelectFinal nvarchar(4000)
IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
@.Aggregate_Function) END
ELSE
BEGIN
SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
QUOTENAME(@.Select_Column) +
') = 1) THEN ''Total'' ELSE ' +
'CAST( + ' +
QUOTENAME(@.Select_Column) +
' AS NVARCHAR(255)) END As ' +
QUOTENAME(@.Select_Column) +
', '
IF @.DEBUG = 1 PRINT @.sqlselectintro
SET @.SQLColRetrieval =
N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
N'''' + @.Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' AS VARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(@.Pivot_Column) +
N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
+
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
N' AS nVARCHAR(255)) + ''' THEN ' +
@.Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' FROM ' + @.TableOrView_Name + ') SelInner'
IF @.DEBUG = 1 PRINT @.SQLColRetrieval
EXEC sp_executesql @.SQLColRetrieval,
N'@.TransformPart nvarchar(4000) OUTPUT',
@.TransformPart OUTPUT
IF @.DEBUG = 1 PRINT @.TransformPart
SET @.SQLSelectFinal =
N', ' + @.Aggregate_Function + N'(' +
CAST(@.Aggregate_Column As Varchar(255)) +
N') As Total FROM ' + @.TableOrView_Name + N'
GROUP BY ' +
@.Select_Column + N' WITH CUBE'
IF @.DEBUG = 1 PRINT @.SQLSelectFinal
EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
END
GO
Thank you very much for any help,
Rafal
*** Sent via Developersdex http://www.examnotes.net ***You can try something like this...
INSERT INTO TableName
EXEC proc_name
For example
CREATE TABLE #HelpDB(name VARCHAR(255), db_size VARCHAR(255), owner
VARCHAR(255), dbid INT, created VARCHAR(255), status VARCHAR(255),
compatibility_level VARCHAR(255))
INSERT INTO #HelpDB
EXEC sp_helpdb
SELECT *
FROM #HelpDB
DROP TABLE #HelpDB
"Rafal Ba" <ash@.robertjanowski.pl> wrote in message
news:ONdDCvbmFHA.2904@.tk2msftngp13.phx.gbl...
> Hello Everybody,
> I have the following problem. I found procedure alolowing me to create
> dynamic crosstab and it works fine, but I cannot save the results as a
> table. Is there any way to do this? Maybe someone is in possesion of
> function which works as below procedure? Or someone is able to change
> this procedure to function which returns table which could be used with
> command CREATE TABLE?
> Procedure looks as follows:
> CREATE PROCEDURE sp_TRANSFORM
> /*
> Purpose: Creates a Pivot(tm) table for the specified table,
> view or select statement
> Author: svenh@.itrain.de
> Version: 1.1
> History: march 2000 version 1.0
> july 2002 version 1.1
> Input parameters:
> @.Aggregate_Function (optional)
> the aggregate function to use for the pivot
> default function is SUM
> @.Aggregate_Column
> name of column for aggregate
> @.TableOrView_Name
> name of table or view to use
> if name contains spaces or other special
> characters [] should be used
> Can also be a valid SELECT statement
> @.Select_Column
> Column for first column in result table
> for this column row values are displayed
> @.Pivot_Column
> Column that is transformed into columns
> for this column column values are displayed
> @.DEBUG
> Set this flag to 1 to get debug-information
> Example usage:
> Table given aTable
> content: Product Salesman Sales
> P1 Sa 12
> P2 Sb 10
> P2 Sb 3
> P3 Sa 12
> P1 Sc 8
> P3 Sa 1
> P2 Sa NULL
> CALL
> EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
> or EXEC sp_Transform @.Aggregate_Column='Sales',
> @.TableOrViewName='aTable',
> @.Select_Column='Product',
> @.Pivot_Column='Salesman'
> Result:
> Product| Sa | Sb | Sc | Total
> --+--+--+--+--
> P1 | 12,00 | 0,00 | 8,00 | 20,00
> P2 | 0,00 | 13,00 | 0,00 | 13,00
> P3 | 13,00 | 0,00 | 0,00 | 13,00
> --+--+--+--+--
> Total | 25,00 | 13,00 | 8,00 | 46,00
>
> */
> @.Aggregate_Function nvarchar(30) = 'SUM',
> @.Aggregate_Column nvarchar(255),
> @.TableOrView_Name nvarchar(255),
> @.Select_Column nvarchar(255),
> @.Pivot_Column nvarchar(255),
> @.DEBUG bit = 0
> AS
> SET NOCOUNT ON
> DECLARE @.TransformPart nvarchar(4000)
> DECLARE @.SQLColRetrieval nvarchar(4000)
> DECLARE @.SQLSelectIntro nvarchar(4000)
> DECLARE @.SQLSelectFinal nvarchar(4000)
> IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
> 'STDEV', 'VAR', 'VARP', 'STDEVP')
> BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
> @.Aggregate_Function) END
> ELSE
> BEGIN
> SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
> QUOTENAME(@.Select_Column) +
> ') = 1) THEN ''Total'' ELSE ' +
> 'CAST( + ' +
> QUOTENAME(@.Select_Column) +
> ' AS NVARCHAR(255)) END As ' +
> QUOTENAME(@.Select_Column) +
> ', '
> IF @.DEBUG = 1 PRINT @.sqlselectintro
> SET @.SQLColRetrieval =
> N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
> N'''' + @.Aggregate_Function + N'(CASE CAST(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' AS VARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(@.Pivot_Column) +
> N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
> +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
> N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
> N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
> N' AS nVARCHAR(255)) + ''' THEN ' +
> @.Aggregate_Column +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') END FROM (SELECT DISTINCT ' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' FROM ' + @.TableOrView_Name + ') SelInner'
> IF @.DEBUG = 1 PRINT @.SQLColRetrieval
> EXEC sp_executesql @.SQLColRetrieval,
> N'@.TransformPart nvarchar(4000) OUTPUT',
> @.TransformPart OUTPUT
> IF @.DEBUG = 1 PRINT @.TransformPart
> SET @.SQLSelectFinal =
> N', ' + @.Aggregate_Function + N'(' +
> CAST(@.Aggregate_Column As Varchar(255)) +
> N') As Total FROM ' + @.TableOrView_Name + N'
> GROUP BY ' +
> @.Select_Column + N' WITH CUBE'
> IF @.DEBUG = 1 PRINT @.SQLSelectFinal
> EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
> END
> GO
>
> Thank you very much for any help,
> Rafal
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Unfortunately, I can't make new table because number of her columns is
not constant in time. Probably, there is in all crosstables.
I have solved this problem as follows:
1) Create View from procedure using OPENROWSET
CREATE VIEW MyView AS
SELECT *
FROM OPENROWSET('SQLOLEDB','seattle1';'manage
r';'MyPass',
'EXEC MyProcedure')
2) Create table from view
SELECT * INTO NewTable FROM MyView
Maybe somebody has other idea?
*** Sent via Developersdex http://www.examnotes.net ***
I have the following problem. I found procedure alolowing me to create
dynamic crosstab and it works fine, but I cannot save the results as a
table. Is there any way to do this? Maybe someone is in possesion of
function which works as below procedure? Or someone is able to change
this procedure to function which returns table which could be used with
command CREATE TABLE?
Procedure looks as follows:
CREATE PROCEDURE sp_TRANSFORM
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Author: svenh@.itrain.de
Version: 1.1
History: march 2000 version 1.0
july 2002 version 1.1
Input parameters:
@.Aggregate_Function (optional)
the aggregate function to use for the pivot
default function is SUM
@.Aggregate_Column
name of column for aggregate
@.TableOrView_Name
name of table or view to use
if name contains spaces or other special
characters [] should be used
Can also be a valid SELECT statement
@.Select_Column
Column for first column in result table
for this column row values are displayed
@.Pivot_Column
Column that is transformed into columns
for this column column values are displayed
@.DEBUG
Set this flag to 1 to get debug-information
Example usage:
Table given aTable
content: Product Salesman Sales
P1 Sa 12
P2 Sb 10
P2 Sb 3
P3 Sa 12
P1 Sc 8
P3 Sa 1
P2 Sa NULL
CALL
EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or EXEC sp_Transform @.Aggregate_Column='Sales',
@.TableOrViewName='aTable',
@.Select_Column='Product',
@.Pivot_Column='Salesman'
Result:
Product| Sa | Sb | Sc | Total
--+--+--+--+--
P1 | 12,00 | 0,00 | 8,00 | 20,00
P2 | 0,00 | 13,00 | 0,00 | 13,00
P3 | 13,00 | 0,00 | 0,00 | 13,00
--+--+--+--+--
Total | 25,00 | 13,00 | 8,00 | 46,00
*/
@.Aggregate_Function nvarchar(30) = 'SUM',
@.Aggregate_Column nvarchar(255),
@.TableOrView_Name nvarchar(255),
@.Select_Column nvarchar(255),
@.Pivot_Column nvarchar(255),
@.DEBUG bit = 0
AS
SET NOCOUNT ON
DECLARE @.TransformPart nvarchar(4000)
DECLARE @.SQLColRetrieval nvarchar(4000)
DECLARE @.SQLSelectIntro nvarchar(4000)
DECLARE @.SQLSelectFinal nvarchar(4000)
IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
@.Aggregate_Function) END
ELSE
BEGIN
SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
QUOTENAME(@.Select_Column) +
') = 1) THEN ''Total'' ELSE ' +
'CAST( + ' +
QUOTENAME(@.Select_Column) +
' AS NVARCHAR(255)) END As ' +
QUOTENAME(@.Select_Column) +
', '
IF @.DEBUG = 1 PRINT @.sqlselectintro
SET @.SQLColRetrieval =
N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
N'''' + @.Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' AS VARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(@.Pivot_Column) +
N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
+
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
N' AS nVARCHAR(255)) + ''' THEN ' +
@.Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' FROM ' + @.TableOrView_Name + ') SelInner'
IF @.DEBUG = 1 PRINT @.SQLColRetrieval
EXEC sp_executesql @.SQLColRetrieval,
N'@.TransformPart nvarchar(4000) OUTPUT',
@.TransformPart OUTPUT
IF @.DEBUG = 1 PRINT @.TransformPart
SET @.SQLSelectFinal =
N', ' + @.Aggregate_Function + N'(' +
CAST(@.Aggregate_Column As Varchar(255)) +
N') As Total FROM ' + @.TableOrView_Name + N'
GROUP BY ' +
@.Select_Column + N' WITH CUBE'
IF @.DEBUG = 1 PRINT @.SQLSelectFinal
EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
END
GO
Thank you very much for any help,
Rafal
*** Sent via Developersdex http://www.examnotes.net ***You can try something like this...
INSERT INTO TableName
EXEC proc_name
For example
CREATE TABLE #HelpDB(name VARCHAR(255), db_size VARCHAR(255), owner
VARCHAR(255), dbid INT, created VARCHAR(255), status VARCHAR(255),
compatibility_level VARCHAR(255))
INSERT INTO #HelpDB
EXEC sp_helpdb
SELECT *
FROM #HelpDB
DROP TABLE #HelpDB
"Rafal Ba" <ash@.robertjanowski.pl> wrote in message
news:ONdDCvbmFHA.2904@.tk2msftngp13.phx.gbl...
> Hello Everybody,
> I have the following problem. I found procedure alolowing me to create
> dynamic crosstab and it works fine, but I cannot save the results as a
> table. Is there any way to do this? Maybe someone is in possesion of
> function which works as below procedure? Or someone is able to change
> this procedure to function which returns table which could be used with
> command CREATE TABLE?
> Procedure looks as follows:
> CREATE PROCEDURE sp_TRANSFORM
> /*
> Purpose: Creates a Pivot(tm) table for the specified table,
> view or select statement
> Author: svenh@.itrain.de
> Version: 1.1
> History: march 2000 version 1.0
> july 2002 version 1.1
> Input parameters:
> @.Aggregate_Function (optional)
> the aggregate function to use for the pivot
> default function is SUM
> @.Aggregate_Column
> name of column for aggregate
> @.TableOrView_Name
> name of table or view to use
> if name contains spaces or other special
> characters [] should be used
> Can also be a valid SELECT statement
> @.Select_Column
> Column for first column in result table
> for this column row values are displayed
> @.Pivot_Column
> Column that is transformed into columns
> for this column column values are displayed
> @.DEBUG
> Set this flag to 1 to get debug-information
> Example usage:
> Table given aTable
> content: Product Salesman Sales
> P1 Sa 12
> P2 Sb 10
> P2 Sb 3
> P3 Sa 12
> P1 Sc 8
> P3 Sa 1
> P2 Sa NULL
> CALL
> EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
> or EXEC sp_Transform @.Aggregate_Column='Sales',
> @.TableOrViewName='aTable',
> @.Select_Column='Product',
> @.Pivot_Column='Salesman'
> Result:
> Product| Sa | Sb | Sc | Total
> --+--+--+--+--
> P1 | 12,00 | 0,00 | 8,00 | 20,00
> P2 | 0,00 | 13,00 | 0,00 | 13,00
> P3 | 13,00 | 0,00 | 0,00 | 13,00
> --+--+--+--+--
> Total | 25,00 | 13,00 | 8,00 | 46,00
>
> */
> @.Aggregate_Function nvarchar(30) = 'SUM',
> @.Aggregate_Column nvarchar(255),
> @.TableOrView_Name nvarchar(255),
> @.Select_Column nvarchar(255),
> @.Pivot_Column nvarchar(255),
> @.DEBUG bit = 0
> AS
> SET NOCOUNT ON
> DECLARE @.TransformPart nvarchar(4000)
> DECLARE @.SQLColRetrieval nvarchar(4000)
> DECLARE @.SQLSelectIntro nvarchar(4000)
> DECLARE @.SQLSelectFinal nvarchar(4000)
> IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
> 'STDEV', 'VAR', 'VARP', 'STDEVP')
> BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
> @.Aggregate_Function) END
> ELSE
> BEGIN
> SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
> QUOTENAME(@.Select_Column) +
> ') = 1) THEN ''Total'' ELSE ' +
> 'CAST( + ' +
> QUOTENAME(@.Select_Column) +
> ' AS NVARCHAR(255)) END As ' +
> QUOTENAME(@.Select_Column) +
> ', '
> IF @.DEBUG = 1 PRINT @.sqlselectintro
> SET @.SQLColRetrieval =
> N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
> N'''' + @.Aggregate_Function + N'(CASE CAST(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' AS VARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(@.Pivot_Column) +
> N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
> +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
> N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
> N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
> N' AS nVARCHAR(255)) + ''' THEN ' +
> @.Aggregate_Column +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') END FROM (SELECT DISTINCT ' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' FROM ' + @.TableOrView_Name + ') SelInner'
> IF @.DEBUG = 1 PRINT @.SQLColRetrieval
> EXEC sp_executesql @.SQLColRetrieval,
> N'@.TransformPart nvarchar(4000) OUTPUT',
> @.TransformPart OUTPUT
> IF @.DEBUG = 1 PRINT @.TransformPart
> SET @.SQLSelectFinal =
> N', ' + @.Aggregate_Function + N'(' +
> CAST(@.Aggregate_Column As Varchar(255)) +
> N') As Total FROM ' + @.TableOrView_Name + N'
> GROUP BY ' +
> @.Select_Column + N' WITH CUBE'
> IF @.DEBUG = 1 PRINT @.SQLSelectFinal
> EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
> END
> GO
>
> Thank you very much for any help,
> Rafal
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Unfortunately, I can't make new table because number of her columns is
not constant in time. Probably, there is in all crosstables.
I have solved this problem as follows:
1) Create View from procedure using OPENROWSET
CREATE VIEW MyView AS
SELECT *
FROM OPENROWSET('SQLOLEDB','seattle1';'manage
r';'MyPass',
'EXEC MyProcedure')
2) Create table from view
SELECT * INTO NewTable FROM MyView
Maybe somebody has other idea?
*** Sent via Developersdex http://www.examnotes.net ***
Conversion of Oracle Version 0-1 from DEC Machine
Hi... Have a customer who's running version 1 of oracle on a DEC
machine.. is there a driver out there for that stuff? How might one
get the data from the DEC machine to Sql Server 2000?? All I have
now are fdl/sfl files.
machine.. is there a driver out there for that stuff? How might one
get the data from the DEC machine to Sql Server 2000?? All I have
now are fdl/sfl files.
thanks in advance.
SteveSteve Walker wrote:
> Hi... Have a customer who's running version 1 of oracle on a DEC
> machine.. is there a driver out there for that stuff? How might one
> get the data from the DEC machine to Sql Server 2000?? All I have
> now are fdl/sfl files.
> thanks in advance.
> Steve
It is extremely unlikely that your customer has version 1 of Oracle
considering Oracle customer list at that point. Find out what they
really have and how they determined it.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||Hi
If there is an ODBC driver you may be able to connect.
John
"Steve Walker" <swalker@.ainet.com> wrote in message
news:bde87b71.0410221056.b2368dc@.posting.google.co m...
> Hi... Have a customer who's running version 1 of oracle on a DEC
> machine.. is there a driver out there for that stuff? How might one
> get the data from the DEC machine to Sql Server 2000?? All I have
> now are fdl/sfl files.
> thanks in advance.
> Stevesqlsql
Conversion Of Oracle Date Time To Sql Server Date Time in SSIS
This is driving me nuts..
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley 
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. 
Thanks
Any idea why this simple straight forward string to date time conversion keeps failing with the error message, conversion failed due to potential loss of data?
The input values looks like this 20070511 23:06:30, what is that I'm missing here for the conversion to fail?
Thanks
|||As much as it sounds ridiculous, looks like SSIS does not understand YYYYMMDD format...
Thanks to Jamie Thomson' s post here, which solved the problem.
http://blogs.conchango.com/jamiethomson/archive/2006/06/26/SSIS_3A00_-Parsing-datetime-values.aspx
Conversion of non Ansi standard queries to ANSI Standard queries
Hi,
In our company we are trying to support SQL Server 2005 in 90 mode. Our
application consists around 120 Stored procedure written with non ansi
standard format joins (*=), is there any tools to convert them or any other
quicky method to do it.
Thanks in advance.
Cheers
RajeshIn article <D05276DF-6E55-49D4-A35E-03ECC8B953A4@.microsoft.com>, =?Utf-
8?B?UmFqZXNoIFY=?= <Rajesh V@.discussions.microsoft.com> says...
> Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
> application consists around 120 Stored procedure written with non ansi
> standard format joins (*=), is there any tools to convert them or any other
> quicky method to do it.
> Thanks in advance.
> Cheers
> Rajesh
>
Not sure about the available tools other than search and replace via any
good text editor, but another question is the ambiguity of old syntax
outer joins which may produce different results when converted to ANSI
standard syntax.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||On Wed, 26 Sep 2007 01:44:01 -0700, Rajesh V <Rajesh
V@.discussions.microsoft.com> wrote:
>Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
>application consists around 120 Stored procedure written with non ansi
>standard format joins (*=), is there any tools to convert them or any other
>quicky method to do it.
Hi Rajesh,
No automated tools that I know of. In similar cases in the past, I have
found that if you assign one person to the task, he or she will build up
routine quickly, so that once (s)he is past the learing curve, the
process of replacing the non-standard code becomes pretty fast.
Don't forget to reward the poor guy/gal with a day off or a bonus after
completing such an unrewarding and mind-numbing task!!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
In our company we are trying to support SQL Server 2005 in 90 mode. Our
application consists around 120 Stored procedure written with non ansi
standard format joins (*=), is there any tools to convert them or any other
quicky method to do it.
Thanks in advance.
Cheers
RajeshIn article <D05276DF-6E55-49D4-A35E-03ECC8B953A4@.microsoft.com>, =?Utf-
8?B?UmFqZXNoIFY=?= <Rajesh V@.discussions.microsoft.com> says...
> Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
> application consists around 120 Stored procedure written with non ansi
> standard format joins (*=), is there any tools to convert them or any other
> quicky method to do it.
> Thanks in advance.
> Cheers
> Rajesh
>
Not sure about the available tools other than search and replace via any
good text editor, but another question is the ambiguity of old syntax
outer joins which may produce different results when converted to ANSI
standard syntax.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||On Wed, 26 Sep 2007 01:44:01 -0700, Rajesh V <Rajesh
V@.discussions.microsoft.com> wrote:
>Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
>application consists around 120 Stored procedure written with non ansi
>standard format joins (*=), is there any tools to convert them or any other
>quicky method to do it.
Hi Rajesh,
No automated tools that I know of. In similar cases in the past, I have
found that if you assign one person to the task, he or she will build up
routine quickly, so that once (s)he is past the learing curve, the
process of replacing the non-standard code becomes pretty fast.
Don't forget to reward the poor guy/gal with a day off or a bonus after
completing such an unrewarding and mind-numbing task!!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Conversion of int data type error?!
Hi,
I keep getting the error:
System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@.qty' to data type int.
When I initiate the insert and update.
I tried adding a: Convert.ToInt32(TextBox1.Text), but it didn't work..
Could someone help?
My code:
private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
con.Open();
SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");
command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - '@.qty' WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();
con.Close();
return true;
}
private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
con.Open();
SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");
command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES (@.User,@.productID,@.Itemsname,@.date,@.qty)";
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", Convert.ToInt32(TextBox1.Text));
command.Parameters.Add("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();
con.Close();
return true;
}
protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );
}
protected void Button2_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "Update")
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteInsert(TextBox1.Text);
}
}
Thanks so much if someone can!
Jon
Hi,
I think the problem lies in your Command Text. Try this:
command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - " + @.qty +" WHERE productID=@.productID";
Hope this helps.
Hi,
I tried it but it gave me a different error message saying qty doesnt exist.
But actually the update seems to work - I think the problem lies with the insert commands..
Thanks,
Jon
|||In your original post, you need to remove the single quotes around @.qty.
|||
sswanner1:
In your original post, you need to remove the single quotes around @.qty.
Hi,
I put them in when I got a syntax error 'near WHERE'..
If I take them away the error comes back..
|||command.Parameters.Add("@.qty", TextBox1.Text); //need to convert into integer like Convert.ToInt32(TextBox1.Text)
TextBox.Text is string rather than integer. You need to valify and convert it to integer.
Hi,
You mean just change the update parameter to the same as the insert parameter (@.qty)?
If so, I have done that but it still gives the same error...
Thanks,
Jon
int qty = 0;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
if(TextBox1 != null)
{
qty = int.parse(TextBox1.Text);
}
catch{}
command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable -@.qty WHERE productID=@.productID";
command.Parameters.Add("@.qty",qty);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();
Following my code, and do it for both methods. And, you need to do same for @.productId.|||
Hi,
if(TextBox1 != null)
{
qty = int.parse(TextBox1.Text);
}
catch{}
Gives me a squiggly before catch{}, saying 'try' is expected? then when I type try it gives more syntax errors?
Thanks,
Jon
|||You will have to convert TextBox1.text to int . Try using ,
int qty = int.parse(TextBox1.text);
command.Parameters.Add("@.qty",SqlDbType.Int);
command..Parameters["@.qty"].Value = qty ;
|||My fault. It should be
if(TextBox1 != null){
try
{
qty = int.parse(TextBox1.Text);
}
catch{}
}
Hi again,
Now it gives the error:
CS0117: 'int' does not contain a definition for 'parse'
Line 40: qty = int.parse(TextBox1.Text);
??
Thanks again!
Jon|||
int.Parse. Sorry.
|||Hi,
New error:
CS0103: The name 'int32' does not exist in the current context
Line 40: qty = int32.parse(TextBox1.Text);
Cheers
Jon|||
it needs to be exactly as :
qty = int.Parse(TextBox1.Text);
Tim
Conversion of IDENTITY to UNIQUEIDENTIFIER during Replication?
Hi,
I tried to find an answer to this via BOL and web but to no avail.
Consider this situation:
CREATE TABLE [dbo].[foodetail] (
[fooid] [int] IDENTITY (1, 1) NOT NULL ,
[footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[foofact] (
[fooid] [int] NOT NULL ,
[volume] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
(
[fooid]
) ON [PRIMARY]
GO
I.e. a fact and a detail table that are joined via fooid but there is no
foreign key defined.
Now, is it possible to replicate content of these two tables and have
fooid converted consistently to a UUID during replication? If not, is it
possible to do it if there is a FK defined?
Thanks a lot!
Kind regards
robertWhy would you want to convert your primary key to GUID? Merge replication
will add rowguid column when you set it up and replicate independently. You
could add your own guid column and populate it, but there's no 'during
replication'. It stays
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I tried to find an answer to this via BOL and web but to no avail.
> Consider this situation:
> CREATE TABLE [dbo].[foodetail] (
> [fooid] [int] IDENTITY (1, 1) NOT NULL ,
> [footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[foofact] (
> [fooid] [int] NOT NULL ,
> [volume] [float] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
> CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
> (
> [fooid]
> ) ON [PRIMARY]
> GO
>
> I.e. a fact and a detail table that are joined via fooid but there is no
> foreign key defined.
> Now, is it possible to replicate content of these two tables and have
> fooid converted consistently to a UUID during replication? If not, is it
> possible to do it if there is a FK defined?
> Thanks a lot!
> Kind regards
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
[vbcol=seagreen]
> Why would you want to convert your primary key to GUID? Merge
> replication will add rowguid column when you set it up and replicate
> independently. You could add your own guid column and populate it,
> but there's no 'during replication'. It stays
I want to get data from n databases to a single centralized DB. In oder
to minimize changes needed to be done to application code ideally I use
IDENTITY columns on local instances and have them converted to GUID
columns during replication because IDENTITY is not globally unique (in
fact likelyhood of collisions is extremely high :-)).
Now, in order to not having to change application code and join generation
on the centralized server ideally we would continue to use the same column
names ("fooid" in this example).
As far as I understand functionality of merge replication, every row in a
table gets a GUID to uniquely identify the row. This would work for the
detail table but not for the fact table as that contains other detail id
columns as well and in order to be able to join them properly we would
need all detail table's GUID's here.
Basically the product in question was never meant to support replication
and now I'm trying to find out whether there's a way to retrofit that
efficiently (meaning developer time as well as run time). :-)
Cheers
robert|||I believe you're better off with adding the column on each table with local
info and extend keys to include it.
Something like 'locationID'. PK in that case is on two columns.
Not very nice, but it could be good enough. Implementing it wouldnt take a
lot of time since you would implement same changes on all databases and all
'LocationID' values are the same for each database.
As far as I can see, alternative would be to add guid column and then update
all FK columns with values from PK and then start replication or something.
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
> MC wrote:
>
>
> I want to get data from n databases to a single centralized DB. In oder
> to minimize changes needed to be done to application code ideally I use
> IDENTITY columns on local instances and have them converted to GUID
> columns during replication because IDENTITY is not globally unique (in
> fact likelyhood of collisions is extremely high :-)).
> Now, in order to not having to change application code and join generation
> on the centralized server ideally we would continue to use the same column
> names ("fooid" in this example).
> As far as I understand functionality of merge replication, every row in a
> table gets a GUID to uniquely identify the row. This would work for the
> detail table but not for the fact table as that contains other detail id
> columns as well and in order to be able to join them properly we would
> need all detail table's GUID's here.
> Basically the product in question was never meant to support replication
> and now I'm trying to find out whether there's a way to retrofit that
> efficiently (meaning developer time as well as run time). :-)
> Cheers
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
[vbcol=seagreen]
> I believe you're better off with adding the column on each table with
> local info and extend keys to include it.
Unfortunately "each table" means all tables that have to be replicated.
> Something like 'locationID'. PK in that case is on two columns.
> Not very nice, but it could be good enough. Implementing it wouldnt
> take a lot of time since you would implement same changes on all
> databases and all 'LocationID' values are the same for each database.
This would mean that the number of columns in fact tables (large!) nearly
doubles. Plus, this would necessitate an application change to change SQL
query generation (joins!).
> As far as I can see, alternative would be to add guid column and then
> update all FK columns with values from PK and then start replication
> or something.
Hmm... I'll have to think about this a bit. Thanks for the valuable
feedback!
Kind regards
robert
I tried to find an answer to this via BOL and web but to no avail.
Consider this situation:
CREATE TABLE [dbo].[foodetail] (
[fooid] [int] IDENTITY (1, 1) NOT NULL ,
[footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[foofact] (
[fooid] [int] NOT NULL ,
[volume] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
(
[fooid]
) ON [PRIMARY]
GO
I.e. a fact and a detail table that are joined via fooid but there is no
foreign key defined.
Now, is it possible to replicate content of these two tables and have
fooid converted consistently to a UUID during replication? If not, is it
possible to do it if there is a FK defined?
Thanks a lot!
Kind regards
robertWhy would you want to convert your primary key to GUID? Merge replication
will add rowguid column when you set it up and replicate independently. You
could add your own guid column and populate it, but there's no 'during
replication'. It stays
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I tried to find an answer to this via BOL and web but to no avail.
> Consider this situation:
> CREATE TABLE [dbo].[foodetail] (
> [fooid] [int] IDENTITY (1, 1) NOT NULL ,
> [footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[foofact] (
> [fooid] [int] NOT NULL ,
> [volume] [float] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
> CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
> (
> [fooid]
> ) ON [PRIMARY]
> GO
>
> I.e. a fact and a detail table that are joined via fooid but there is no
> foreign key defined.
> Now, is it possible to replicate content of these two tables and have
> fooid converted consistently to a UUID during replication? If not, is it
> possible to do it if there is a FK defined?
> Thanks a lot!
> Kind regards
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
[vbcol=seagreen]
> Why would you want to convert your primary key to GUID? Merge
> replication will add rowguid column when you set it up and replicate
> independently. You could add your own guid column and populate it,
> but there's no 'during replication'. It stays
I want to get data from n databases to a single centralized DB. In oder
to minimize changes needed to be done to application code ideally I use
IDENTITY columns on local instances and have them converted to GUID
columns during replication because IDENTITY is not globally unique (in
fact likelyhood of collisions is extremely high :-)).
Now, in order to not having to change application code and join generation
on the centralized server ideally we would continue to use the same column
names ("fooid" in this example).
As far as I understand functionality of merge replication, every row in a
table gets a GUID to uniquely identify the row. This would work for the
detail table but not for the fact table as that contains other detail id
columns as well and in order to be able to join them properly we would
need all detail table's GUID's here.
Basically the product in question was never meant to support replication
and now I'm trying to find out whether there's a way to retrofit that
efficiently (meaning developer time as well as run time). :-)
Cheers
robert|||I believe you're better off with adding the column on each table with local
info and extend keys to include it.
Something like 'locationID'. PK in that case is on two columns.
Not very nice, but it could be good enough. Implementing it wouldnt take a
lot of time since you would implement same changes on all databases and all
'LocationID' values are the same for each database.
As far as I can see, alternative would be to add guid column and then update
all FK columns with values from PK and then start replication or something.
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
> MC wrote:
>
>
> I want to get data from n databases to a single centralized DB. In oder
> to minimize changes needed to be done to application code ideally I use
> IDENTITY columns on local instances and have them converted to GUID
> columns during replication because IDENTITY is not globally unique (in
> fact likelyhood of collisions is extremely high :-)).
> Now, in order to not having to change application code and join generation
> on the centralized server ideally we would continue to use the same column
> names ("fooid" in this example).
> As far as I understand functionality of merge replication, every row in a
> table gets a GUID to uniquely identify the row. This would work for the
> detail table but not for the fact table as that contains other detail id
> columns as well and in order to be able to join them properly we would
> need all detail table's GUID's here.
> Basically the product in question was never meant to support replication
> and now I'm trying to find out whether there's a way to retrofit that
> efficiently (meaning developer time as well as run time). :-)
> Cheers
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
[vbcol=seagreen]
> I believe you're better off with adding the column on each table with
> local info and extend keys to include it.
Unfortunately "each table" means all tables that have to be replicated.
> Something like 'locationID'. PK in that case is on two columns.
> Not very nice, but it could be good enough. Implementing it wouldnt
> take a lot of time since you would implement same changes on all
> databases and all 'LocationID' values are the same for each database.
This would mean that the number of columns in fact tables (large!) nearly
doubles. Plus, this would necessitate an application change to change SQL
query generation (joins!).
> As far as I can see, alternative would be to add guid column and then
> update all FK columns with values from PK and then start replication
> or something.
Hmm... I'll have to think about this a bit. Thanks for the valuable
feedback!
Kind regards
robert
Conversion of DTS to SSIS command Line
I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.
What am i doing wrong ?
DTEXEC /DTS "\File System\Archive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW \package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:\Inetpub\wwwroot\output\Archive\'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"
Error I get
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "\package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds
Your command line is not correct as each set command needs a package path starting with \package just as the error message indicates. As I don't know tasks these properties belong to I can't give you the exact path but in general the set option should look something like "\Package.rest_of_path_to_property". You can use the configurations on the package to identify what the package path should look like. You should also remove the \package from the command line outside of the set because that is invalid.
HTH,
Matt
Conversion of DTS Application to SSIS Application - SourceConnectionId and SourceObjectName
Hello All,
I am trying to convert an application created using DTS classes to SSIS object model. I have found following code in the application.
For x As Integer = 1 To mTask.Properties.Count
If mTask.Properties.Item(x).Name = "SourceConnectionID" Then
CnID = mTask.Properties.Item(x).Value
Exit For
End If
Next
For x As Integer = 1 To mPkg.DTSPackage.Connections.Count
If mPkg.DTSPackage.Connections.Item(x).ID = CnID Then
Return mPkg.DTSPackage.Connections.Item(x).Name
End If
Next
Return mTask.Properties.Item("SourceObjectName").Value
How can I retrieve or Set "SourceConnectinId", "SourceObjectName" of a data flow task in SSIS
Please help me to solve this problem.
Thanks in advance
Subin
Hello All,
How can I retrieve source and destination connection information of a data flow task through code (c#) . My dataflow task have one flat file source, script task and oledb destination. In SQL Server 2000 DTS, there is a SourceConnectionId property to get connection information. Which SSIS property is equivalent to SourceConnectionId of DTS?.
Please help me
Thanks in advance
Subin
|||Subin,I've merged these two posts from you as they are the same topic. Please don't start a new thread for the same topic you've already posted about.
Thanks,
Phil Brammer|||
Subin wrote:
Hello All,
How can I retrieve source and destination connection information of a data flow task through code (c#) . My dataflow task have one flat file source, script task and oledb destination. In SQL Server 2000 DTS, there is a SourceConnectionId property to get connection information. Which SSIS property is equivalent to SourceConnectionId of DTS?.
Please help me
Thanks in advance
Subin
There is no apples-to-apples equivalent. DTS and SSIS have very different object models.
You will have to iterate over the SSIS object model exactly as you did in DTS. This post should help:
Building Packages Programatically
http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx
As Phil says - there is no need to post a thread more than once unless it has gone unanswered and therefore hidden many pages back.
-Jamie
|||Components reference connections through the RuntimeConnectionCollection collection, a property of the IDTSComponentMetaData90 class.Conversion of Database into text file
hi All,
Actually i have a project on data minning...n i have to convert the databases into text files so that they can be consolidate ....n when consolidation of the databases(in the form of text files) would be done i have to convert the consolidated one (text file) into database again.
so anyone plz tell me dat how to convert a database (using sql server & C#) into text file...
regards,
Hello.
This is what Sql Server Integration Services was made for. Any reason why you want to create the text-files?
You have many other options.
It looks to me that what you can do with an INSERT queries using linked servers. Have a look at sp_addlinkedserver in TSQL. You might get help from reading http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html (ignore step 1 and 3)
Hope this helps.
Subscribe to:
Posts (Atom)