I need to write an UPDATE statement that adds to a field from data in
another table. Can someone help? below is sample:
UPDATE TableA
SET Total = Total + TableB.Amount
FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
WHERE TableB.PrdYr = 2005
When I do this, it does not add in the incremented Total field and I end up
with the last TableB.Amount value.
Thanks.
David>> I need to write an UPDATE statement that adds to a field from data in
Yes, but you will have to provide sufficient information for others to
understand your problem. Pl. read www.aspfaq.com/5006 and post your DDLs,
sample data & expected results
Anith|||Try this, it will keep a running total in TableA each time the query is
run. If this is going to be run and needs all of the values to start
out 0 (no running total), then remove the 'Total + ' part of the query.
UPDATE TableA
SET Total = Total +
( SELECT ISNULL(SUM(TableB.Amount),0)
FROM TableB
WHERE TableB.PrdYr = 2005
and TableB.EmpNo = TableA.EmpNo
)
Kalvin|||David,
An UPDATE statement will only make one assignment
to each column. UPDATE .. FROM is a T-SQL extension
to standard SQL that allows poorly defined statements, and
while it can be handy, it can also cause confusion. I wish an
error were raised in situations like this, but that's not the case.
To do what you want, you probably need something like
update TableA set
Total = Total + (
select sum(TableB.Amount)
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
)
Steve Kass
Drew University
David wrote:
>I need to write an UPDATE statement that adds to a field from data in
>another table. Can someone help? below is sample:
>UPDATE TableA
>SET Total = Total + TableB.Amount
>FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
>WHERE TableB.PrdYr = 2005
>When I do this, it does not add in the incremented Total field and I end up
>with the last TableB.Amount value.
>Thanks.
>David
>
>|||Kalvin caught one thing I didn't. This needs either COALESCE
or a WHERE condition on the update, to avoid NULLing out
Total values when there's no match in TableB. Here's a WHERE
condition that ought to do it.
update TableA set
Total = Total + (
..
)
where exists (
select *
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
and TableB.Amount is not null
)
SK
Steve Kass wrote:
> David,
> An UPDATE statement will only make one assignment
> to each column. UPDATE .. FROM is a T-SQL extension
> to standard SQL that allows poorly defined statements, and
> while it can be handy, it can also cause confusion. I wish an
> error were raised in situations like this, but that's not the case.
> To do what you want, you probably need something like
> update TableA set
> Total = Total + (
> select sum(TableB.Amount)
> from TableB
> where TableB.EmpNo = TableA.EmpNo
> and TableB.PrdYr = 2005
> )
>
> Steve Kass
> Drew University
> David wrote:
>|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
2) Would you like to learn REAL SQL or only some proprietary kludges
that have unpredicatable results, as you have posted?
Did you actually split out a year as a temporal column'!! Surely not
!
Why don't you know that column and field are **totally** different?
Why don't you know that there is no such thing as a generic, magical
"amount" -- it has to be the amount of something. Have you ever had a
BASIC -- repeat BASIC in capital letters -- data modeling class?
You can probably get enough kludges in a newsgroup to slip past your
boss unitl you get to the next job to screw up them too.
I got an email tonight form a kid who volunteered to do a DB for an
African Relief agency and seriously screwed it up. I got the consult
after things got messed up and I posted this in some newsgroups as an
example. I guess he found me via those postings.
I know his design crippled some children; I am not sure about causing
deaths and a part of me does not want to know. Please care enough not
to do that. To other people. To other people.sql
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Thursday, March 22, 2012
Can TSQL query create new output column ?
How can i write a query to split a database column and shows 2 new columns. In my database column
I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter
and run another query.
For example a database column with average number and range number.
Thanks
Daniel
Can you post some DDL, sample data and expected results?
AMB
|||
Hai,
Can you try the below query, and let me know that, it relates to your requirement or not:
DECLARE @.Columns varchar(1000)
SET @.Columns = ''
-- Create a temporary table.
CREATE TABLE #TempTable(Items varchar(50))
INSERT INTO #TempTable(Items) VALUES('A')
INSERT INTO #TempTable(Items) VALUES('A')
INSERT INTO #TempTable(Items) VALUES('A')
INSERT INTO #TempTable(Items) VALUES('B')
INSERT INTO #TempTable(Items) VALUES('B')
INSERT INTO #TempTable(Items) VALUES('B')
INSERT INTO #TempTable(Items) VALUES('C')
INSERT INTO #TempTable(Items) VALUES('C')
INSERT INTO #TempTable(Items) VALUES('D')
INSERT INTO #TempTable(Items) VALUES('D')
-- Before
SELECT * FROM #TempTable
-- Make a column list
SELECT
@.Columns = @.Columns + '[' + Items + '], '
FROM #TempTable
GROUP BY Items
-- Check the column values exits or not.
IF ( @.Columns IS NOT NULL ) AND ( @.Columns <> '' )
BEGIN
DECLARE @.Query nvarchar(1000)
SELECT @.Columns = SUBSTRING(@.Columns,1, LEN(@.Columns)-1)
SELECT @.Query = '
SELECT
*
FROM
(
SELECT
Items
FROM #TempTable
) AS Dummy
PIVOT
(
MAX(Items)
FOR Items IN (' + @.Columns + ')
)AS PvtTable'
EXEC(@.Query)
END
-- Drop the temporary table.
DROP TABLE #TempTable
Please clarify If I did any wrong.
Regards,
Kiran.Y
|||Perhaps something like:
SET NOCOUNT ON
DECLARE @.MyTable table
( RowID int IDENTITY,
MyGroup int,
MyValue decimal(10,2)
)
INSERT INTO @.MyTable VALUES ( 1, 25 )
INSERT INTO @.MyTable VALUES ( 2, 5 )
INSERT INTO @.MyTable VALUES ( 1, 10 )
INSERT INTO @.MyTable VALUES ( 1, 15 )
INSERT INTO @.MyTable VALUES ( 1, 4 )
INSERT INTO @.MyTable VALUES ( 2, 6 )
INSERT INTO @.MyTable VALUES ( 2, 11 )
INSERT INTO @.MyTable VALUES ( 2, 0 )
INSERT INTO @.MyTable VALUES ( 1, 12 )
SELECT
Average = cast( avg( MyValue ) AS decimal(10,2)),
Range = ( cast( min( MyValue ) AS varchar(10)) + '-' +
cast( max( MyValue ) AS varchar(10)))
FROM @.MyTable
GROUP BY MyGroup
Average Range
13.20 4.00-25.00
5.50 0.00-11.00
|||
Hi Kiran
Thanks for answering my email. To clarify this below are my tables and columns and my query
Table: Item Stat_label Stat_value
column: Pack ID Stat_label_ID Stat_value_ID
Pack_Num Label ( has 2 rows Value
Ave and Range)
My query to list Pack_Num, Ave and it's value
SELECT Item.Pack_Num, Stat_label.Label, Stat_value.Value
FROM Item, Stat_label, Stat_value
WHERE Item.packID=Stat_label.Stat_label_ID AND
Stat_label.Stat_lavel_ID=Stat_value.Stat_value_ID
AND Stat_label.Label= Ave
My question: I want a query to list Pack_Num, Ave, Range and value
How can I do it?
That's mean this query need to split the Stat_label and list another
column name"Range".
Thanks
Daniel
If you are using SQL 2005, look into the PIVOT function.
If you are using SQL 2000, explore using CASE.
Maybe these articles will help:
Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html
Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574
Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955
Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/
|||
Thanks all
I can not use "insert" because my account for this is read only and I avoid to list everything in a column and and use Excel pivot to summary.
Daniel
|||
Daniel,
If you would carefully examine the code provided, you will see that the INSERT statements are only building a sample table so that we could demonstrate a query suggestion.
You didn't bother to provide the table DDL, or sample data, so we have to waste our time creating sample data for you. and apparently, you can't read and understand example code.
|||
This may be closer to what you are hoping to find:
SELECT
i.Pack_Num,
sl.Stat_Label,
Average = cast( avg( sv.Stat_Value ) AS decimal(10,2)),
Range = ( cast( min( sv.Stat_Value ) AS varchar(10)) + '-' +
cast( max( sv.Stat_Value ) AS varchar(10)))
FROM Item i
JOIN Stat_Label sl
ON i.Pack_ID = sl.Stat_Label_ID
JOIN Stat_Value sv
ON sl.Stat_Label_ID = sv.Stat_Value_ID
WHERE sl.Label = 'Ave'
GROUP BY
i.Pack_Num,
sl.Stat_Label
|||
Thanks Anrnie but It is not working
Error at Average= cast......
Error at Range= (cast......
My Average and Range are decimal, no need cast
Do I have to declare a temp table?
Daniel
|||
Actually, it appears that the Stat_Value is most most likely a varchar().
Before we can help you any further, please post the table DDL and some sample data in the form of INSERT statements. Please refer to this link for help in preparing your material.
|||
Can SQL query create a new column or not?. DO NOT want to make a temp table.
Thanks
Daniel
|||
Can TSQL create a new column at the output?
If not I need 2 select statement but how to joint them? Can not use EXCEPT in TSQL? Tried to use UNION but
the results in one column.
It's complicated with creating a temp table since I do not know how to insert to temp table from database.
Thanks
Daniel
Tuesday, March 20, 2012
can this be done?
Table1
ID|CATID|NAME
1 3 A
2 3 B
3 3 C
4 4 D
5 4 E
I want to write a query that pull all the record from the same CATID but
with only ID given.
If ID = 4 it would return ID 4 and 5 because they are in the same category
If ID = 2 it would return ID 1,2,3
Thanks,
HowardHoward wrote:
> Table1
> ID|CATID|NAME
> 1 3 A
> 2 3 B
> 3 3 C
> 4 4 D
> 5 4 E
> I want to write a query that pull all the record from the same CATID but
> with only ID given.
> If ID = 4 it would return ID 4 and 5 because they are in the same category
> If ID = 2 it would return ID 1,2,3
> Thanks,
> Howard
DECLARE @.id INT;
SET @.id = 4;
SELECT id
FROM table1
WHERE catid =
(SELECT catid
FROM table1
WHERE id = @.id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I want to write a query that pull all the record from the same CATID
> but with only ID given.
> If ID = 4 it would return ID 4 and 5 because they are in the same
> category If ID = 2 it would return ID 1,2,3
A relatively easy way to do that would be using in sub-query:
create table dbo.table1(id tinyint,catid tinyint,name char(1))
go
insert into dbo.table1(id,catid,name)
select 1,3,'A' union all
select 2,3,'B' union all
select 3,3,'C' union all
select 4,4,'D' union all
select 5,4,'E'
go
declare @.id tinyint
set @.id = 1
select * from dbo.table1
where catid in
(select catid from dbo.table1 where id=@.id)
go
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello Kent,
declare @.id tinyint
set @.id = 4
select t2.* from dbo.table1 t1
left join dbo.table1 t2
on t1.catid = t2.catid
where t1.id = @.id
is an option too.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql
ID|CATID|NAME
1 3 A
2 3 B
3 3 C
4 4 D
5 4 E
I want to write a query that pull all the record from the same CATID but
with only ID given.
If ID = 4 it would return ID 4 and 5 because they are in the same category
If ID = 2 it would return ID 1,2,3
Thanks,
HowardHoward wrote:
> Table1
> ID|CATID|NAME
> 1 3 A
> 2 3 B
> 3 3 C
> 4 4 D
> 5 4 E
> I want to write a query that pull all the record from the same CATID but
> with only ID given.
> If ID = 4 it would return ID 4 and 5 because they are in the same category
> If ID = 2 it would return ID 1,2,3
> Thanks,
> Howard
DECLARE @.id INT;
SET @.id = 4;
SELECT id
FROM table1
WHERE catid =
(SELECT catid
FROM table1
WHERE id = @.id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I want to write a query that pull all the record from the same CATID
> but with only ID given.
> If ID = 4 it would return ID 4 and 5 because they are in the same
> category If ID = 2 it would return ID 1,2,3
A relatively easy way to do that would be using in sub-query:
create table dbo.table1(id tinyint,catid tinyint,name char(1))
go
insert into dbo.table1(id,catid,name)
select 1,3,'A' union all
select 2,3,'B' union all
select 3,3,'C' union all
select 4,4,'D' union all
select 5,4,'E'
go
declare @.id tinyint
set @.id = 1
select * from dbo.table1
where catid in
(select catid from dbo.table1 where id=@.id)
go
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello Kent,
declare @.id tinyint
set @.id = 4
select t2.* from dbo.table1 t1
left join dbo.table1 t2
on t1.catid = t2.catid
where t1.id = @.id
is an option too.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql
Wednesday, March 7, 2012
Can SQL Reporting write to a WebDAV sharepoint?
We have a sharepoint <\\ces\cesgypdav> that is a WebDAV connection, not an actual fileshare. A SSRS Subscription fails on file write ... "Failure writing file <name here > : The network path was not found" ... to this path.
Can SQL Reporting write to a WebDAV sharepoint?
WebDAV issue is known, need to 'work around'.This helps: http://sqljunkies.com/WebLog/tpagel/archive/2005/12/23/17682.aspx
Saturday, February 25, 2012
Can SQL 2005 already be used ?
Hi,
Maybe this is a silly question, but can SQL 2005 already be used for
customers?
We have some new customer-projects for which we have to write our own data
extensions for webservices and xml in Reporting Services 2000 while it's a
standard Rep srv 2005!
Can we start with 2005 even though the official release date is November
7th?
Please say yes ;-)
Kind regards,
Jeroen dbNo.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx
"SQL Server June 2005 Community Technology Preview (CTP) is the first
version of SQL Server 2005 made available for general testing. Previous
versions have been available only to customers enrolled in the SQL Server
2005 beta program or those with an MSDN subscription. Please note that you
cannot deploy SQL Server June 2005 Community Technology Preview in
production environments"
And license.txt that ships with the setup:
" (a) Microsoft grants to Recipient a limited, non-exclusive,
nontransferable, royalty-free license to install and use up to twenty-five
(25) total copies of the executable code of the Software on computers,
including servers, workstations, terminals or other digital electronic
devices residing on Recipient's premises, solely for purposes of testing
software programs that run in conjunction with the Software, and to evaluate
the Software for the purpose of providing feedback thereon to Microsoft.
Due to the nature of the development work, Microsoft provides no assurance
that any specific errors or discrepancies in the Software will be
corrected."
" (f) Recipient may not use the Software in a live operating environment
where it may be relied upon to perform in the same manner as a commercially
released product. The default installation configuration in this
pre-release version of the Software does not necessarily reflect which
features will be enabled in the commercially released version based on final
trustworthy computing analysis. Recipient must take adequate precautionary
measures to back up and protect its data prior to installing the Software."
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>|||Mike is correct unless for a specific customer they contact MS and get on
the Ascend program. This is the program for early adopters which MS
encourages but also reminds you that support is limited to nonexistent.
Danny
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>
Maybe this is a silly question, but can SQL 2005 already be used for
customers?
We have some new customer-projects for which we have to write our own data
extensions for webservices and xml in Reporting Services 2000 while it's a
standard Rep srv 2005!
Can we start with 2005 even though the official release date is November
7th?
Please say yes ;-)
Kind regards,
Jeroen dbNo.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx
"SQL Server June 2005 Community Technology Preview (CTP) is the first
version of SQL Server 2005 made available for general testing. Previous
versions have been available only to customers enrolled in the SQL Server
2005 beta program or those with an MSDN subscription. Please note that you
cannot deploy SQL Server June 2005 Community Technology Preview in
production environments"
And license.txt that ships with the setup:
" (a) Microsoft grants to Recipient a limited, non-exclusive,
nontransferable, royalty-free license to install and use up to twenty-five
(25) total copies of the executable code of the Software on computers,
including servers, workstations, terminals or other digital electronic
devices residing on Recipient's premises, solely for purposes of testing
software programs that run in conjunction with the Software, and to evaluate
the Software for the purpose of providing feedback thereon to Microsoft.
Due to the nature of the development work, Microsoft provides no assurance
that any specific errors or discrepancies in the Software will be
corrected."
" (f) Recipient may not use the Software in a live operating environment
where it may be relied upon to perform in the same manner as a commercially
released product. The default installation configuration in this
pre-release version of the Software does not necessarily reflect which
features will be enabled in the commercially released version based on final
trustworthy computing analysis. Recipient must take adequate precautionary
measures to back up and protect its data prior to installing the Software."
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>|||Mike is correct unless for a specific customer they contact MS and get on
the Ascend program. This is the program for early adopters which MS
encourages but also reminds you that support is limited to nonexistent.
Danny
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>
Can SQL 2005 already be used ?
Hi,
Maybe this is a silly question, but can SQL 2005 already be used for
customers?
We have some new customer-projects for which we have to write our own data
extensions for webservices and xml in Reporting Services 2000 while it's a
standard Rep srv 2005!
Can we start with 2005 even though the official release date is November
7th?
Please say yes ;-)
Kind regards,
Jeroen db
No.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx
"SQL Server June 2005 Community Technology Preview (CTP) is the first
version of SQL Server 2005 made available for general testing. Previous
versions have been available only to customers enrolled in the SQL Server
2005 beta program or those with an MSDN subscription. Please note that you
cannot deploy SQL Server June 2005 Community Technology Preview in
production environments"
And license.txt that ships with the setup:
" (a) Microsoft grants to Recipient a limited, non-exclusive,
nontransferable, royalty-free license to install and use up to twenty-five
(25) total copies of the executable code of the Software on computers,
including servers, workstations, terminals or other digital electronic
devices residing on Recipient's premises, solely for purposes of testing
software programs that run in conjunction with the Software, and to evaluate
the Software for the purpose of providing feedback thereon to Microsoft.
Due to the nature of the development work, Microsoft provides no assurance
that any specific errors or discrepancies in the Software will be
corrected."
" (f) Recipient may not use the Software in a live operating environment
where it may be relied upon to perform in the same manner as a commercially
released product. The default installation configuration in this
pre-release version of the Software does not necessarily reflect which
features will be enabled in the commercially released version based on final
trustworthy computing analysis. Recipient must take adequate precautionary
measures to back up and protect its data prior to installing the Software."
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>
|||Mike is correct unless for a specific customer they contact MS and get on
the Ascend program. This is the program for early adopters which MS
encourages but also reminds you that support is limited to nonexistent.
Danny
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>
Maybe this is a silly question, but can SQL 2005 already be used for
customers?
We have some new customer-projects for which we have to write our own data
extensions for webservices and xml in Reporting Services 2000 while it's a
standard Rep srv 2005!
Can we start with 2005 even though the official release date is November
7th?
Please say yes ;-)
Kind regards,
Jeroen db
No.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx
"SQL Server June 2005 Community Technology Preview (CTP) is the first
version of SQL Server 2005 made available for general testing. Previous
versions have been available only to customers enrolled in the SQL Server
2005 beta program or those with an MSDN subscription. Please note that you
cannot deploy SQL Server June 2005 Community Technology Preview in
production environments"
And license.txt that ships with the setup:
" (a) Microsoft grants to Recipient a limited, non-exclusive,
nontransferable, royalty-free license to install and use up to twenty-five
(25) total copies of the executable code of the Software on computers,
including servers, workstations, terminals or other digital electronic
devices residing on Recipient's premises, solely for purposes of testing
software programs that run in conjunction with the Software, and to evaluate
the Software for the purpose of providing feedback thereon to Microsoft.
Due to the nature of the development work, Microsoft provides no assurance
that any specific errors or discrepancies in the Software will be
corrected."
" (f) Recipient may not use the Software in a live operating environment
where it may be relied upon to perform in the same manner as a commercially
released product. The default installation configuration in this
pre-release version of the Software does not necessarily reflect which
features will be enabled in the commercially released version based on final
trustworthy computing analysis. Recipient must take adequate precautionary
measures to back up and protect its data prior to installing the Software."
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>
|||Mike is correct unless for a specific customer they contact MS and get on
the Ascend program. This is the program for early adopters which MS
encourages but also reminds you that support is limited to nonexistent.
Danny
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>
Can SQL 2005 already be used ?
Hi,
Maybe this is a silly question, but can SQL 2005 already be used for
customers?
We have some new customer-projects for which we have to write our own data
extensions for webservices and xml in Reporting Services 2000 while it's a
standard Rep srv 2005!
Can we start with 2005 even though the official release date is November
7th?
Please say yes ;-)
Kind regards,
Jeroen dbNo.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx
"SQL Server June 2005 Community Technology Preview (CTP) is the first
version of SQL Server 2005 made available for general testing. Previous
versions have been available only to customers enrolled in the SQL Server
2005 beta program or those with an MSDN subscription. Please note that you
cannot deploy SQL Server June 2005 Community Technology Preview in
production environments"
And license.txt that ships with the setup:
" (a) Microsoft grants to Recipient a limited, non-exclusive,
nontransferable, royalty-free license to install and use up to twenty-five
(25) total copies of the executable code of the Software on computers,
including servers, workstations, terminals or other digital electronic
devices residing on Recipient's premises, solely for purposes of testing
software programs that run in conjunction with the Software, and to evaluate
the Software for the purpose of providing feedback thereon to Microsoft.
Due to the nature of the development work, Microsoft provides no assurance
that any specific errors or discrepancies in the Software will be
corrected."
" (f) Recipient may not use the Software in a live operating environment
where it may be relied upon to perform in the same manner as a commercially
released product. The default installation configuration in this
pre-release version of the Software does not necessarily reflect which
features will be enabled in the commercially released version based on final
trustworthy computing analysis. Recipient must take adequate precautionary
measures to back up and protect its data prior to installing the Software."
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>|||Mike is correct unless for a specific customer they contact MS and get on
the Ascend program. This is the program for early adopters which MS
encourages but also reminds you that support is limited to nonexistent.
Danny
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>
Maybe this is a silly question, but can SQL 2005 already be used for
customers?
We have some new customer-projects for which we have to write our own data
extensions for webservices and xml in Reporting Services 2000 while it's a
standard Rep srv 2005!
Can we start with 2005 even though the official release date is November
7th?
Please say yes ;-)
Kind regards,
Jeroen dbNo.
http://www.microsoft.com/sql/2005/productinfo/ctp.mspx
"SQL Server June 2005 Community Technology Preview (CTP) is the first
version of SQL Server 2005 made available for general testing. Previous
versions have been available only to customers enrolled in the SQL Server
2005 beta program or those with an MSDN subscription. Please note that you
cannot deploy SQL Server June 2005 Community Technology Preview in
production environments"
And license.txt that ships with the setup:
" (a) Microsoft grants to Recipient a limited, non-exclusive,
nontransferable, royalty-free license to install and use up to twenty-five
(25) total copies of the executable code of the Software on computers,
including servers, workstations, terminals or other digital electronic
devices residing on Recipient's premises, solely for purposes of testing
software programs that run in conjunction with the Software, and to evaluate
the Software for the purpose of providing feedback thereon to Microsoft.
Due to the nature of the development work, Microsoft provides no assurance
that any specific errors or discrepancies in the Software will be
corrected."
" (f) Recipient may not use the Software in a live operating environment
where it may be relied upon to perform in the same manner as a commercially
released product. The default installation configuration in this
pre-release version of the Software does not necessarily reflect which
features will be enabled in the commercially released version based on final
trustworthy computing analysis. Recipient must take adequate precautionary
measures to back up and protect its data prior to installing the Software."
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>|||Mike is correct unless for a specific customer they contact MS and get on
the Ascend program. This is the program for early adopters which MS
encourages but also reminds you that support is limited to nonexistent.
Danny
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:%23HhM83gbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Maybe this is a silly question, but can SQL 2005 already be used for
> customers?
> We have some new customer-projects for which we have to write our own data
> extensions for webservices and xml in Reporting Services 2000 while it's a
> standard Rep srv 2005!
> Can we start with 2005 even though the official release date is November
> 7th?
> Please say yes ;-)
> Kind regards,
> Jeroen db
>
Can somone crack this query?
This is a simplification of a query I'm attempting to write at the
moment. I can do it with a temp table, but I think it can be done in
one query. 15 minutes of fame to the correct answer.
moment. I can do it with a temp table, but I think it can be done in
one query. 15 minutes of fame to the correct answer.
3 tables:
Table Continent
================Columns==================
Columns:
ID (primary key)
Name
================Data=====================
NA,North America
SA,South America
Table Country
================Columns==================
ContinentID (foreign key)
Name
Population
================Data=====================
NA,United States,295734134
NA,Canada,32805041
SA,Brazil,186112794
SA,Peru, 27269482
Can someone come up with the query to return the country with the
lowest population in each continent, ie:
CA,Canada,32805041
SA,Peru,27269482
Need to have the country name in the query (would be very easy if you
didn't!).--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
SELECT ContinentID, Name, Population
FROM Country As C
WHERE Population = (SELECT Min(Population) FROM Country
WHERE ContinentID = C.ContinentID)
ORDER BY ContinentID, Name
Do I get a Gold Star?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQtMcJYechKqOuFEgEQK58wCgyTJWEzJIGiiynKX9fRrHOA n2/5AAnjhF
XjBxajYEv1Afl+VAJVYY9DLI
=3lQX
--END PGP SIGNATURE--
russ wrote:
> This is a simplification of a query I'm attempting to write at the
> moment. I can do it with a temp table, but I think it can be done in
> one query. 15 minutes of fame to the correct answer.
> 3 tables:
> Table Continent
> ================Columns==================
> Columns:
> ID (primary key)
> Name
> ================Data=====================
> NA,North America
> SA,South America
> Table Country
> ================Columns==================
> ContinentID (foreign key)
> Name
> Population
> ================Data=====================
> NA,United States,295734134
> NA,Canada,32805041
> SA,Brazil,186112794
> SA,Peru, 27269482
> Can someone come up with the query to return the country with the
> lowest population in each continent, ie:
> CA,Canada,32805041
> SA,Peru,27269482
> Need to have the country name in the query (would be very easy if you
> didn't!).|||you most certainly do! correlated sub-query, ended up doing the same
myself.
thanks for your help
Can someone post a working sample of this?
I need to write an UPDATE using a SET where I fill in a NULL if a default
field is blank. Like the below:
UPDATE table SET birthdate = { expression | default | null }
I simply don't know the correct syntax (even after reading the online
books). I really need a working sample with anything close to this. I
can't get it to work and end-up with the birthdate pulling from a text
field and only placing a null in the table when no birthday is given.
Thanks in advance.
BobbyJI thnk you want COALESCE ( expression [ ,...n ] ), it returns the first non-null argument or Null if all argumetns are null.|||Originally posted by BobbyJ
I need to write an UPDATE using a SET where I fill in a NULL if a default
field is blank. Like the below:
UPDATE table SET birthdate = { expression | default | null }
I simply don't know the correct syntax (even after reading the online
books). I really need a working sample with anything close to this. I
can't get it to work and end-up with the birthdate pulling from a text
field and only placing a null in the table when no birthday is given.
Thanks in advance.
BobbyJ
Hi
Try
UPDATE table
set birthdate = isnull(yourtextitem,' ')|||All-
Thanks for the replies. I will try it out this weekend.|||I tried this from a prompt and got the same results (1/1/1900).
Any other ideas?|||Which one did you try? For sure walshx's will not work. Inserting a '' value into a date/time field results in the '1/1/1900' that you see. I thought Paul's suggestion would work, but I get the following error when doing a test:
None of the result expressions in a CASE specification can be NULL.
The test code I ran was:
declare @.temp varchar(20)
select @.temp = coalesce(null,null,null)
select @.temp as results
I even tried it with ansi_warnings off with no luck. I ran into a similar issue with a vb script i was running. My solution (bad as it is) was to append an update script that searched for 1/1/1900 and changed those entries to null.
I am sorry, but I don't have any good answers right now.
Hugh Scott
Originally posted by BobbyJ
I tried this from a prompt and got the same results (1/1/1900).
Any other ideas?|||It's funny you should mention that. I was thinking the exact same thing
before I started doing any of this. I figured I could write a server service
to periodically check the table and replace empty values or 1/1/1900 with nulls. I'll do this for now and keep searching for a simpler way as
I go. Thanks.
BobbbyJ|||1.select isnull(isnull(A,B),null) works
2.Look at this code. Periodic running of code is not needed.
create table XXXX
(
idX int identity(1,1) primary key
,X int null
)
GO
create trigger ti_XXXX_I on XXXX
instead of insert as
insert XXXX(X)
select isnull(inserted.X,0) from inserted
GO
create trigger ti_XXXX_U on XXXX
instead of update as
update t set
t.X=isnull(i.X,0)
from XXXX t
join inserted i on t.idx=i.idx
GO
insert XXXX values(NULL)
insert XXXX values(3)
select * from XXXX
update XXXX set X=NULL where X=3
select * from XXXX
GO
drop table XXXX
GO|||I'm sorry, I forgot to mention I'm doing this all within Visual Studio .NET (VB) and SQL2000 standard calls. I'm not too familiar with straight SQL
without referring to a book. Can I save a null in an
UPDATE table column = value and if value is empty save it as a null?
BobbyJ|||Empty means NULL is SQL.|||I'm referring to empty as VB sees a textbox with nothing typed in it or
where the length is 0 bytes.
All the coding I've done always places a 1/1/1900 in SQL. I don't have
the code in front of me (it's at work but looks like this - from memory).
UPDATE tblEMPLOYEE SET BIRTHDATE = ISNULL(txtBIRTH.text,'') WHERE EMPLOYEEID = form.EMPLOYEEID
There are more fields being update, I just selected one for this sample
in VB coding. The second half of the ISNULL I even replaced with
system.dbnull.value and get the same result. Can't figure out what's
wrong. I suppose VB never makes it a null as SQL needs to see it (just
an empty string coming in - at times).
BobbyJ|||I am not familiar with VB(.NET). In VB(6) Textbox property Text cannot store NULL values. If you want to use NULL, try variable for example Text1IsNull as boolean or Textbox.BackColor indication.|||I follow you (I think). Is this what you're saying:
Example:
Dim xBIRTHDATE as string (strings can be null if I recall)
'Birthdate is a textbox on a webform
if BIRTHDATE.TEXT <> STRING.EMPTY then
xBIRTHDATE = BIRTHDATE.TEXT
endif
'So at this point if the textbox is empty xBIRTHDATE is still set to null
'and it is safe to save.
UPDATE table SET dbBIRTHDATE = ISNULL(xBIRTHDATE,'')
Correct??|||bobbyj, can you have a look at the table definition please
if the birthdate column is defined NOT NULL you will never get a null in there
alternatively, it may have DEFAULT 0 which would explain the 1/1/1900 (this is the date that a day number of 0 converts to)
so before you write any weird script, check whether the database will even let you put a null in there
as for the syntax, try this --
script logic to generate update statement:
update table
set foo ='bar'
if birthdate form field is empty
, birthdate = null
else
, birthdate = form field value
endif|||Yes. It does allow nulls and I'll give that script a shot (maybe later tonight).
Thanks,|||I think I've figured out what's wrong. In Visual Studio .NET VB, it doesn't
set variables to NULL but something called NOTHING. When NOTHING
is passed to ISNULL, ISNULL thinks it's an empty string and not a NULL.
Do I need to declare my VS.NET variables as SQLTYPES in order to get
a true NULL? At first I thought this was a simple SQL issue but starting to
think otherwise.
BobbyJ|||Try
IIF(YourVar is nothing,"NULL","'+replace(YourVar,"'","''")+'")
for string variables to pass variable to sql.|||I will try this later today (I'm actually off today - after the SuperBowl)
when I remote in to check mail. We had system problems from the
worm virus that started out last Saturday (so hopefully the systems
are available - SQL).
Thanks,
BobbyJ|||I tried it but my compiler has issues with the syntax of the replace
statement. It's getting hung up on the single ' marks (thinks it's a comment of sorts). I really appreciate you and other taking time to
help with this. It's GREATLY appreciated.
BobbyJ|||Corrected in VB6, I dont know if this syntax can be used in .NET.
IIf(YourVar Is Nothing, "NULL", "'" + Replace(YourVar, "'", "''") + "'")|||Thanks. I'll try again.|||No matter what happens, the YOURVAR is always returned as NOTHING
and not NULL. It must be an issue with .NET. Your logic looks fine as did
my old code but I can't get a NULL for a return value. I think I need to
do some research on how to obtain a NULL value in the .NET. I suppose
I may have to look deeper into the SQLTYPES as I know there should be
a DBNULL.VALUE I can load into SQL in order to get a NULL in the database. This whole thing is really strange.
BobbyJ|||SOLVED! Code I used:
Dim strRANKDATE As String
If Me.txtGradeDate.Text <> String.Empty Then
strRANKDATE = "RANK = '" & Me.txtGradeDate.Text & "', "
Else
strRANKDATE = "RANK = NULL ,"
End If
'Building SQL string
strUpdateStatement = "UPDATE tblEMPLOYEE SET " & _
"FIRSTNAME = '" & Me.txtFirstName.Text & "', " & _
"MIDDLENAME = '" & Me.txtMiddle.Text & "', " & _
"LASTNAME = '" & Me.txtLastName.Text & "', " & _
"SUFFIX = '" & Me.ddlSuffix.SelectedItem.Text & "', " & _
"NICKNAME = '" & Me.txtNickname.Text & "', " & _
"SERVICE = '" & Me.ddlService.SelectedItem.Text & "', " & _
"GRADE = '" & Me.ddlGrade.SelectedItem.Text & "', " & _
strRANKDATE & _
"RANK = '" & Me.ddlRankTitle.SelectedItem.Text & "', " & _
etc. Now the NULL is properly added to the table when the user
removes the date from date fields on the webform. The If else
can be modied to a shorter IIF or a function can be made of it.
BobbyJ
field is blank. Like the below:
UPDATE table SET birthdate = { expression | default | null }
I simply don't know the correct syntax (even after reading the online
books). I really need a working sample with anything close to this. I
can't get it to work and end-up with the birthdate pulling from a text
field and only placing a null in the table when no birthday is given.
Thanks in advance.
BobbyJI thnk you want COALESCE ( expression [ ,...n ] ), it returns the first non-null argument or Null if all argumetns are null.|||Originally posted by BobbyJ
I need to write an UPDATE using a SET where I fill in a NULL if a default
field is blank. Like the below:
UPDATE table SET birthdate = { expression | default | null }
I simply don't know the correct syntax (even after reading the online
books). I really need a working sample with anything close to this. I
can't get it to work and end-up with the birthdate pulling from a text
field and only placing a null in the table when no birthday is given.
Thanks in advance.
BobbyJ
Hi
Try
UPDATE table
set birthdate = isnull(yourtextitem,' ')|||All-
Thanks for the replies. I will try it out this weekend.|||I tried this from a prompt and got the same results (1/1/1900).
Any other ideas?|||Which one did you try? For sure walshx's will not work. Inserting a '' value into a date/time field results in the '1/1/1900' that you see. I thought Paul's suggestion would work, but I get the following error when doing a test:
None of the result expressions in a CASE specification can be NULL.
The test code I ran was:
declare @.temp varchar(20)
select @.temp = coalesce(null,null,null)
select @.temp as results
I even tried it with ansi_warnings off with no luck. I ran into a similar issue with a vb script i was running. My solution (bad as it is) was to append an update script that searched for 1/1/1900 and changed those entries to null.
I am sorry, but I don't have any good answers right now.
Hugh Scott
Originally posted by BobbyJ
I tried this from a prompt and got the same results (1/1/1900).
Any other ideas?|||It's funny you should mention that. I was thinking the exact same thing
before I started doing any of this. I figured I could write a server service
to periodically check the table and replace empty values or 1/1/1900 with nulls. I'll do this for now and keep searching for a simpler way as
I go. Thanks.
BobbbyJ|||1.select isnull(isnull(A,B),null) works
2.Look at this code. Periodic running of code is not needed.
create table XXXX
(
idX int identity(1,1) primary key
,X int null
)
GO
create trigger ti_XXXX_I on XXXX
instead of insert as
insert XXXX(X)
select isnull(inserted.X,0) from inserted
GO
create trigger ti_XXXX_U on XXXX
instead of update as
update t set
t.X=isnull(i.X,0)
from XXXX t
join inserted i on t.idx=i.idx
GO
insert XXXX values(NULL)
insert XXXX values(3)
select * from XXXX
update XXXX set X=NULL where X=3
select * from XXXX
GO
drop table XXXX
GO|||I'm sorry, I forgot to mention I'm doing this all within Visual Studio .NET (VB) and SQL2000 standard calls. I'm not too familiar with straight SQL
without referring to a book. Can I save a null in an
UPDATE table column = value and if value is empty save it as a null?
BobbyJ|||Empty means NULL is SQL.|||I'm referring to empty as VB sees a textbox with nothing typed in it or
where the length is 0 bytes.
All the coding I've done always places a 1/1/1900 in SQL. I don't have
the code in front of me (it's at work but looks like this - from memory).
UPDATE tblEMPLOYEE SET BIRTHDATE = ISNULL(txtBIRTH.text,'') WHERE EMPLOYEEID = form.EMPLOYEEID
There are more fields being update, I just selected one for this sample
in VB coding. The second half of the ISNULL I even replaced with
system.dbnull.value and get the same result. Can't figure out what's
wrong. I suppose VB never makes it a null as SQL needs to see it (just
an empty string coming in - at times).
BobbyJ|||I am not familiar with VB(.NET). In VB(6) Textbox property Text cannot store NULL values. If you want to use NULL, try variable for example Text1IsNull as boolean or Textbox.BackColor indication.|||I follow you (I think). Is this what you're saying:
Example:
Dim xBIRTHDATE as string (strings can be null if I recall)
'Birthdate is a textbox on a webform
if BIRTHDATE.TEXT <> STRING.EMPTY then
xBIRTHDATE = BIRTHDATE.TEXT
endif
'So at this point if the textbox is empty xBIRTHDATE is still set to null
'and it is safe to save.
UPDATE table SET dbBIRTHDATE = ISNULL(xBIRTHDATE,'')
Correct??|||bobbyj, can you have a look at the table definition please
if the birthdate column is defined NOT NULL you will never get a null in there
alternatively, it may have DEFAULT 0 which would explain the 1/1/1900 (this is the date that a day number of 0 converts to)
so before you write any weird script, check whether the database will even let you put a null in there
as for the syntax, try this --
script logic to generate update statement:
update table
set foo ='bar'
if birthdate form field is empty
, birthdate = null
else
, birthdate = form field value
endif|||Yes. It does allow nulls and I'll give that script a shot (maybe later tonight).
Thanks,|||I think I've figured out what's wrong. In Visual Studio .NET VB, it doesn't
set variables to NULL but something called NOTHING. When NOTHING
is passed to ISNULL, ISNULL thinks it's an empty string and not a NULL.
Do I need to declare my VS.NET variables as SQLTYPES in order to get
a true NULL? At first I thought this was a simple SQL issue but starting to
think otherwise.
BobbyJ|||Try
IIF(YourVar is nothing,"NULL","'+replace(YourVar,"'","''")+'")
for string variables to pass variable to sql.|||I will try this later today (I'm actually off today - after the SuperBowl)
when I remote in to check mail. We had system problems from the
worm virus that started out last Saturday (so hopefully the systems
are available - SQL).
Thanks,
BobbyJ|||I tried it but my compiler has issues with the syntax of the replace
statement. It's getting hung up on the single ' marks (thinks it's a comment of sorts). I really appreciate you and other taking time to
help with this. It's GREATLY appreciated.
BobbyJ|||Corrected in VB6, I dont know if this syntax can be used in .NET.
IIf(YourVar Is Nothing, "NULL", "'" + Replace(YourVar, "'", "''") + "'")|||Thanks. I'll try again.|||No matter what happens, the YOURVAR is always returned as NOTHING
and not NULL. It must be an issue with .NET. Your logic looks fine as did
my old code but I can't get a NULL for a return value. I think I need to
do some research on how to obtain a NULL value in the .NET. I suppose
I may have to look deeper into the SQLTYPES as I know there should be
a DBNULL.VALUE I can load into SQL in order to get a NULL in the database. This whole thing is really strange.
BobbyJ|||SOLVED! Code I used:
Dim strRANKDATE As String
If Me.txtGradeDate.Text <> String.Empty Then
strRANKDATE = "RANK = '" & Me.txtGradeDate.Text & "', "
Else
strRANKDATE = "RANK = NULL ,"
End If
'Building SQL string
strUpdateStatement = "UPDATE tblEMPLOYEE SET " & _
"FIRSTNAME = '" & Me.txtFirstName.Text & "', " & _
"MIDDLENAME = '" & Me.txtMiddle.Text & "', " & _
"LASTNAME = '" & Me.txtLastName.Text & "', " & _
"SUFFIX = '" & Me.ddlSuffix.SelectedItem.Text & "', " & _
"NICKNAME = '" & Me.txtNickname.Text & "', " & _
"SERVICE = '" & Me.ddlService.SelectedItem.Text & "', " & _
"GRADE = '" & Me.ddlGrade.SelectedItem.Text & "', " & _
strRANKDATE & _
"RANK = '" & Me.ddlRankTitle.SelectedItem.Text & "', " & _
etc. Now the NULL is properly added to the table when the user
removes the date from date fields on the webform. The If else
can be modied to a shorter IIF or a function can be made of it.
BobbyJ
Friday, February 24, 2012
Can someone helpl me write this query to create a crosstab(pivot t
Hi. I am trying to write a crosstab or pivot table, but I don't think the
syntax that I'm writing is very efficient. It is using a union statement to
add an overall total, but I think this is the problem that is inefficient.
Does anyone know of a better way to write this query to cut down on time?
I'm using this type syntax on a much larger scale(searching 200k record
creating a pivot table with 100 columns and between 100-200 rows).
drop table testme
Create Table Testme ( ID int PRIMARY KEY, City NVARCHAR(255), Country
NVARCHAR(255) )
Insert Into Testme (ID, City, Country) Values (1, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (2, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (3, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (4, N'Chicago', 'USA')
Insert Into Testme (ID, City, Country) Values (5, N'Chicago', 'USA')
select * from testme
select
[country], [total], [nyc], [chicago]
from
(
select
[country], [myorder] = 0, count([id]) as total, count(case when [city]
like 'nyc' THEN [id] else null END) as [nyc], count(case when [city] like
'Chicago' THEN [id] else null END) as [chicago]
from
testme
where
city in ('nyc', 'Chicago')
group by
country
union
select
[country] = 'Total', [myorder] = 1, count([id]) as total, count(case
when [city] like 'nyc' THEN [id] else null END) as [nyc], count(case when
[city] like 'Chicago' THEN [id] else null END) as [chicago]
from
testme
where
city in ('nyc', 'Chicago')
) as source order by [myorder], [country]Presumably you're going to display your results somewhere, such as in
ReportingServices, or a web page, etc... So why not add them up there instea
d
if you're worried?
Personally, I'm not too keen on the PivotTable concept of SQL. I would
rather return all the data, and then use something at the presentation layer
to turn it into a PivotTable.
syntax that I'm writing is very efficient. It is using a union statement to
add an overall total, but I think this is the problem that is inefficient.
Does anyone know of a better way to write this query to cut down on time?
I'm using this type syntax on a much larger scale(searching 200k record
creating a pivot table with 100 columns and between 100-200 rows).
drop table testme
Create Table Testme ( ID int PRIMARY KEY, City NVARCHAR(255), Country
NVARCHAR(255) )
Insert Into Testme (ID, City, Country) Values (1, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (2, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (3, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (4, N'Chicago', 'USA')
Insert Into Testme (ID, City, Country) Values (5, N'Chicago', 'USA')
select * from testme
select
[country], [total], [nyc], [chicago]
from
(
select
[country], [myorder] = 0, count([id]) as total, count(case when [city]
like 'nyc' THEN [id] else null END) as [nyc], count(case when [city] like
'Chicago' THEN [id] else null END) as [chicago]
from
testme
where
city in ('nyc', 'Chicago')
group by
country
union
select
[country] = 'Total', [myorder] = 1, count([id]) as total, count(case
when [city] like 'nyc' THEN [id] else null END) as [nyc], count(case when
[city] like 'Chicago' THEN [id] else null END) as [chicago]
from
testme
where
city in ('nyc', 'Chicago')
) as source order by [myorder], [country]Presumably you're going to display your results somewhere, such as in
ReportingServices, or a web page, etc... So why not add them up there instea
d
if you're worried?
Personally, I'm not too keen on the PivotTable concept of SQL. I would
rather return all the data, and then use something at the presentation layer
to turn it into a PivotTable.
Can someone helpl me write this query to create a crosstab(piv
Hi Rob,
Originally I was sending the data and using a function in VB.Net to create
the pivot table, but I'm trying to have the work done on the SQL Server
instead of transferring all that data to the ASP.Net page. I think at the
moment that it's duplicating some of the work in the query but was wondering
if someone knew of a better way to write the query.
David
"Rob Farley" wrote:
> Presumably you're going to display your results somewhere, such as in
> ReportingServices, or a web page, etc... So why not add them up there inst
ead
> if you're worried?
> Personally, I'm not too keen on the PivotTable concept of SQL. I would
> rather return all the data, and then use something at the presentation lay
er
> to turn it into a PivotTable.
>Just to be sure, could you lay out the output that you are looking for'
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
> "Rob Farley" wrote:
>|||There's no pretty way of returning a PivotTable directly from SQL. You can d
o
it through a large amount of temporary table population or creating an ugly
piece of 'dynamic' SQL. But honestly, it's much easier to do it once you've
got the data away from SQL.
If you group by all your columns and rows, so that you get only one record
back for each cell of your PivotTable, then you can easily handle that in
VB.Net. If you're worried about the amount of data passed back, you could
group by IDs instead of column/row names, and pass back separate datasets
which translate the IDs into the more human-readable form.
If you're really determined to create a stored procedure that will do it for
you, then I'm sure we can come up with something, but please, pick the
'simple' solution, which is to find a control that will display the
PivotTable for you.
Rob
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
Originally I was sending the data and using a function in VB.Net to create
the pivot table, but I'm trying to have the work done on the SQL Server
instead of transferring all that data to the ASP.Net page. I think at the
moment that it's duplicating some of the work in the query but was wondering
if someone knew of a better way to write the query.
David
"Rob Farley" wrote:
> Presumably you're going to display your results somewhere, such as in
> ReportingServices, or a web page, etc... So why not add them up there inst
ead
> if you're worried?
> Personally, I'm not too keen on the PivotTable concept of SQL. I would
> rather return all the data, and then use something at the presentation lay
er
> to turn it into a PivotTable.
>Just to be sure, could you lay out the output that you are looking for'
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
> "Rob Farley" wrote:
>|||There's no pretty way of returning a PivotTable directly from SQL. You can d
o
it through a large amount of temporary table population or creating an ugly
piece of 'dynamic' SQL. But honestly, it's much easier to do it once you've
got the data away from SQL.
If you group by all your columns and rows, so that you get only one record
back for each cell of your PivotTable, then you can easily handle that in
VB.Net. If you're worried about the amount of data passed back, you could
group by IDs instead of column/row names, and pass back separate datasets
which translate the IDs into the more human-readable form.
If you're really determined to create a stored procedure that will do it for
you, then I'm sure we can come up with something, but please, pick the
'simple' solution, which is to find a control that will display the
PivotTable for you.
Rob
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
Thursday, February 16, 2012
Can Reporting Server do this?
I have a simple database see below and I would like the outcome group by
month with subtotal. How do I write the query and then use Reporting Server
to genereate a report like the following?
Data in database:
--
Datetime Amount
1/1/2005 1400
1/1/2005 1600
2/1/2005 800
2/5/2005 600
Report outcome:
--
January
1/1/2005 1400
1/1/2005 1600
Subtotal : 3000
Febuary
2/1/2005 800
2/5/2005 600
Subtotal: 1400Create a report containing the dataset from your database. Create a field
that only shows the month of your DateTime and then group it by that field
(Month) and include subtotals.
If you create a view that strips out the month in your datetime, then you
should be able to achieve your result by using the report wizard.
I know Crystal Reports has the ability to group by datetime based on
month/week/daily basis, but I am not sure if that functionality is in RS.
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:UqqdnbKEK4HT5g3eRVn-jA@.rogers.com...
>I have a simple database see below and I would like the outcome group by
>month with subtotal. How do I write the query and then use Reporting
>Server to genereate a report like the following?
> Data in database:
> --
> Datetime Amount
> 1/1/2005 1400
> 1/1/2005 1600
> 2/1/2005 800
> 2/5/2005 600
>
> Report outcome:
> --
> January
> 1/1/2005 1400
> 1/1/2005 1600
> Subtotal : 3000
> Febuary
> 2/1/2005 800
> 2/5/2005 600
> Subtotal: 1400
>|||Thanks Pedro!! To help other people.. here is the Query I used:
By using DataName and DatePart in SQL query:
SELECT DATENAME(mm, DateTime) + ', ' + CAST(DATENAME(yyyy, DateTime) AS
varchar(4)) AS MonthYearName, DATEPART(yyyy, DateTime) AS Year,
DATEPART(mm, DateTime) AS Month, *
FROM CorporateSales
Then, in Reporting Server, add the GROUP and then group the data by Month,
and by Year.
Add "MonthYearName" in header.
Add "Subtotal" in footer.
I will be able to show exactly what I wanted in the first place.
"Pedro" <pedro@.newsgroups.nospam> wrote in message
news:e16lK2J%23FHA.3308@.TK2MSFTNGP11.phx.gbl...
> Create a report containing the dataset from your database. Create a field
> that only shows the month of your DateTime and then group it by that field
> (Month) and include subtotals.
> If you create a view that strips out the month in your datetime, then you
> should be able to achieve your result by using the report wizard.
> I know Crystal Reports has the ability to group by datetime based on
> month/week/daily basis, but I am not sure if that functionality is in RS.
> "Zean Smith" <nospam@.nospamaaamail.com> wrote in message
> news:UqqdnbKEK4HT5g3eRVn-jA@.rogers.com...
>>I have a simple database see below and I would like the outcome group by
>>month with subtotal. How do I write the query and then use Reporting
>>Server to genereate a report like the following?
>> Data in database:
>> --
>> Datetime Amount
>> 1/1/2005 1400
>> 1/1/2005 1600
>> 2/1/2005 800
>> 2/5/2005 600
>>
>> Report outcome:
>> --
>> January
>> 1/1/2005 1400
>> 1/1/2005 1600
>> Subtotal : 3000
>> Febuary
>> 2/1/2005 800
>> 2/5/2005 600
>> Subtotal: 1400
>>
>
month with subtotal. How do I write the query and then use Reporting Server
to genereate a report like the following?
Data in database:
--
Datetime Amount
1/1/2005 1400
1/1/2005 1600
2/1/2005 800
2/5/2005 600
Report outcome:
--
January
1/1/2005 1400
1/1/2005 1600
Subtotal : 3000
Febuary
2/1/2005 800
2/5/2005 600
Subtotal: 1400Create a report containing the dataset from your database. Create a field
that only shows the month of your DateTime and then group it by that field
(Month) and include subtotals.
If you create a view that strips out the month in your datetime, then you
should be able to achieve your result by using the report wizard.
I know Crystal Reports has the ability to group by datetime based on
month/week/daily basis, but I am not sure if that functionality is in RS.
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:UqqdnbKEK4HT5g3eRVn-jA@.rogers.com...
>I have a simple database see below and I would like the outcome group by
>month with subtotal. How do I write the query and then use Reporting
>Server to genereate a report like the following?
> Data in database:
> --
> Datetime Amount
> 1/1/2005 1400
> 1/1/2005 1600
> 2/1/2005 800
> 2/5/2005 600
>
> Report outcome:
> --
> January
> 1/1/2005 1400
> 1/1/2005 1600
> Subtotal : 3000
> Febuary
> 2/1/2005 800
> 2/5/2005 600
> Subtotal: 1400
>|||Thanks Pedro!! To help other people.. here is the Query I used:
By using DataName and DatePart in SQL query:
SELECT DATENAME(mm, DateTime) + ', ' + CAST(DATENAME(yyyy, DateTime) AS
varchar(4)) AS MonthYearName, DATEPART(yyyy, DateTime) AS Year,
DATEPART(mm, DateTime) AS Month, *
FROM CorporateSales
Then, in Reporting Server, add the GROUP and then group the data by Month,
and by Year.
Add "MonthYearName" in header.
Add "Subtotal" in footer.
I will be able to show exactly what I wanted in the first place.
"Pedro" <pedro@.newsgroups.nospam> wrote in message
news:e16lK2J%23FHA.3308@.TK2MSFTNGP11.phx.gbl...
> Create a report containing the dataset from your database. Create a field
> that only shows the month of your DateTime and then group it by that field
> (Month) and include subtotals.
> If you create a view that strips out the month in your datetime, then you
> should be able to achieve your result by using the report wizard.
> I know Crystal Reports has the ability to group by datetime based on
> month/week/daily basis, but I am not sure if that functionality is in RS.
> "Zean Smith" <nospam@.nospamaaamail.com> wrote in message
> news:UqqdnbKEK4HT5g3eRVn-jA@.rogers.com...
>>I have a simple database see below and I would like the outcome group by
>>month with subtotal. How do I write the query and then use Reporting
>>Server to genereate a report like the following?
>> Data in database:
>> --
>> Datetime Amount
>> 1/1/2005 1400
>> 1/1/2005 1600
>> 2/1/2005 800
>> 2/5/2005 600
>>
>> Report outcome:
>> --
>> January
>> 1/1/2005 1400
>> 1/1/2005 1600
>> Subtotal : 3000
>> Febuary
>> 2/1/2005 800
>> 2/5/2005 600
>> Subtotal: 1400
>>
>
Can Report Parameter Prompt be Dynamically changed?
I am attempting to write a multilingual report and would like to have
the Prompt property of a Report Parameter dynamically change. Is this
possible? Support for something like an expression would be ideal,
but I cannot seem to find where prompt supports such a concept.
Thanks,
DaveOn Dec 6, 3:59 pm, david.gab...@.swagelok.com wrote:
> I am attempting to write a multilingual report and would like to have
> the Prompt property of a Report Parameter dynamically change. Is this
> possible? Support for something like an expression would be ideal,
> but I cannot seem to find where prompt supports such a concept.
> Thanks,
> Dave
As far as I know, this functionality does not exist. One work around
would be to modify the .rdl file programmatically between the <prompt>
tags based on the language (via StreamReader, StreamWriter, etc).
Also, you might try creating a custom ASP.NET website and allow the
user to select a language from a drop-down list and then create 2
reports: each one based on a different language. Then you could show
either report based on the language selected. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
the Prompt property of a Report Parameter dynamically change. Is this
possible? Support for something like an expression would be ideal,
but I cannot seem to find where prompt supports such a concept.
Thanks,
DaveOn Dec 6, 3:59 pm, david.gab...@.swagelok.com wrote:
> I am attempting to write a multilingual report and would like to have
> the Prompt property of a Report Parameter dynamically change. Is this
> possible? Support for something like an expression would be ideal,
> but I cannot seem to find where prompt supports such a concept.
> Thanks,
> Dave
As far as I know, this functionality does not exist. One work around
would be to modify the .rdl file programmatically between the <prompt>
tags based on the language (via StreamReader, StreamWriter, etc).
Also, you might try creating a custom ASP.NET website and allow the
user to select a language from a drop-down list and then create 2
reports: each one based on a different language. Then you could show
either report based on the language selected. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Tuesday, February 14, 2012
can openxml write multiple fields - 1 row?
I am starting to get the hang of this. The trick is in
the ConvertToXML UDF. Here is the xml string that I want
to create which will work with openxml
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
Here is the string that I pass in to the ConvertToXML UDF
'1,Joe,Smith,1/1/01,3'
Here is the Original function
CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
@.Delim varchar (5))
returns varchar (8000)
as
begin
return ('<ROOT><Worktable Value="' + replace (@.InputStr,
@.Delim, '"/><Worktable Value="') + '"/></ROOT>')
end
which yields the following from '1,Joe,Smith,1/1/01,3'
<ROOT><Worktable Value="1"/><Worktable
Value="Joe"/><Worktable Value="Smith"/><Worktable
Value="1/1/01"/><Worktable Value="3"/></ROOT>
which is 5 rows and one column
But I need it to look like this:
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
to get 1 row and 5 columns
so in the function my kludge would look like this:
...
declare @.rownum int
declare @.fName varchar(10)
declare @.lName varchar(10)
declare @.dob datetime
declare @.age int
declare @.pos1 int
declare @.pos2 int
begin
--@.input = '1,Joe,Smith,1/1/01,3'
set @.rownum = Left(@.input, 1)
set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
comma so @.pos1 is at 2nd comma
set @.fName = substring(@.input, 3, @.pos1 - 3)
set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
+ @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
+ @.age + '/><ROOT>
End
If anyone has a suggestion how I could streamline this -
please share because my actual row is more like 50
fields. I am thinking a loop would be more efficient, but
I don't know how to implement a loop in a Sql Server UDF.
Could someone share please?
Thanks,
Ed
set @.pos1 = charindex(@.input, ',', @.pos2) --
Hi Ed,
You might have a slightly easier time converting the input string to XML
using C#/VB (or some other mid-tier programming language). Also from a DB
performance standpoint offloading all this string manipulation elsewhere may
help (although the XML you send over the wire will be bigger) - of course
all this depends on how your app is set up and where your bottlenecks are,
so take this with a grain of salt
You can check out
http://msdn.microsoft.com/library/de...chtutorial.asp
for a good example of implementing a class that supports the foreach loop
construct over an array of string tokens. Combine that with your favorite
TextReader implementation to read through all the "rows" of string tokens to
generate your XML. In terms of generating the XML you can just use string
manipulation or the XmlWriter API
(http://msdn.microsoft.com/library/de...-us/cpref/html
/frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
getting used to but it gives you a higher level abstraction write access to
the underlying XML.
You could loop through your string tokens and create XML that looks like:
<Root value1='{somevalue}' value2='{someothervalue}...</Root> by appending
the iteration number to the name of the attribute value - if there is a
consistent ordering of fields in the file you could then just use the
"colPattern" construct in OPENXML to map the attributes to the appropriate
columns in the table.
If you need to do the whole thing in the server let me know and we can think
about further.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"ED" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
> I am starting to get the hang of this. The trick is in
> the ConvertToXML UDF. Here is the xml string that I want
> to create which will work with openxml
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> Here is the string that I pass in to the ConvertToXML UDF
> '1,Joe,Smith,1/1/01,3'
> Here is the Original function
> ----
> CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
> @.Delim varchar (5))
> returns varchar (8000)
> as
> begin
> return ('<ROOT><Worktable Value="' + replace (@.InputStr,
> @.Delim, '"/><Worktable Value="') + '"/></ROOT>')
> end
> which yields the following from '1,Joe,Smith,1/1/01,3'
> <ROOT><Worktable Value="1"/><Worktable
> Value="Joe"/><Worktable Value="Smith"/><Worktable
> Value="1/1/01"/><Worktable Value="3"/></ROOT>
> which is 5 rows and one column
> ----
> But I need it to look like this:
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> to get 1 row and 5 columns
> so in the function my kludge would look like this:
> ...
> declare @.rownum int
> declare @.fName varchar(10)
> declare @.lName varchar(10)
> declare @.dob datetime
> declare @.age int
> declare @.pos1 int
> declare @.pos2 int
> begin
> --@.input = '1,Joe,Smith,1/1/01,3'
> set @.rownum = Left(@.input, 1)
> set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
> comma so @.pos1 is at 2nd comma
> set @.fName = substring(@.input, 3, @.pos1 - 3)
> set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
> set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
> set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
> set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
> set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
> set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
> set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
> return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
> + @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
> + @.age + '/><ROOT>
> End
> If anyone has a suggestion how I could streamline this -
> please share because my actual row is more like 50
> fields. I am thinking a loop would be more efficient, but
> I don't know how to implement a loop in a Sql Server UDF.
> Could someone share please?
> Thanks,
> Ed
> set @.pos1 = charindex(@.input, ',', @.pos2) --
>
>
|||Actually I was just looking around MSDN and if you can do it using C# then
you will have a much easier time with Chris Lovett's XmlCsvReader at
http://msdn.microsoft.com/library/de...lcsvreader.asp -
This will give you a much easier time - just put the column names you want
in the first row of your CSV file/stream and this thing will do the work for
you. The link has a code sample to get you going.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi Ed,
> You might have a slightly easier time converting the input string to XML
> using C#/VB (or some other mid-tier programming language). Also from a DB
> performance standpoint offloading all this string manipulation elsewhere
may
> help (although the XML you send over the wire will be bigger) - of course
> all this depends on how your app is set up and where your bottlenecks are,
> so take this with a grain of salt
> You can check out
>
http://msdn.microsoft.com/library/de...chtutorial.asp
> for a good example of implementing a class that supports the foreach loop
> construct over an array of string tokens. Combine that with your favorite
> TextReader implementation to read through all the "rows" of string tokens
to
> generate your XML. In terms of generating the XML you can just use string
> manipulation or the XmlWriter API
>
(http://msdn.microsoft.com/library/de...-us/cpref/html
> /frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
> getting used to but it gives you a higher level abstraction write access
to
> the underlying XML.
> You could loop through your string tokens and create XML that looks like:
> <Root value1='{somevalue}' value2='{someothervalue}...</Root> by
appending
> the iteration number to the name of the attribute value - if there is a
> consistent ordering of fields in the file you could then just use the
> "colPattern" construct in OPENXML to map the attributes to the appropriate
> columns in the table.
> If you need to do the whole thing in the server let me know and we can
think
> about further.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ED" <anonymous@.discussions.microsoft.com> wrote in message
> news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
>
|||Depending on what you finally want to do with it, I am not sure that going
from CSV to XML to rowset is the best way.
At least in the context of SQL Server 2005, you probably want to write a CLR
user defined function that takes the CSV and makes it directly into a
table-valued result.
Otherwise, Adam's suggestions are useful too.
Best regards
Michael
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:uvUdwO7fEHA.536@.TK2MSFTNGP11.phx.gbl...
> Actually I was just looking around MSDN and if you can do it using C# then
> you will have a much easier time with Chris Lovett's XmlCsvReader at
> http://msdn.microsoft.com/library/de...lcsvreader.asp -
> This will give you a much easier time - just put the column names you want
> in the first row of your CSV file/stream and this thing will do the work
> for
> you. The link has a code sample to get you going.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
> news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> may
> http://msdn.microsoft.com/library/de...chtutorial.asp
> to
> (http://msdn.microsoft.com/library/de...-us/cpref/html
> to
> appending
> think
> rights.
>
the ConvertToXML UDF. Here is the xml string that I want
to create which will work with openxml
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
Here is the string that I pass in to the ConvertToXML UDF
'1,Joe,Smith,1/1/01,3'
Here is the Original function
CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
@.Delim varchar (5))
returns varchar (8000)
as
begin
return ('<ROOT><Worktable Value="' + replace (@.InputStr,
@.Delim, '"/><Worktable Value="') + '"/></ROOT>')
end
which yields the following from '1,Joe,Smith,1/1/01,3'
<ROOT><Worktable Value="1"/><Worktable
Value="Joe"/><Worktable Value="Smith"/><Worktable
Value="1/1/01"/><Worktable Value="3"/></ROOT>
which is 5 rows and one column
But I need it to look like this:
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
to get 1 row and 5 columns
so in the function my kludge would look like this:
...
declare @.rownum int
declare @.fName varchar(10)
declare @.lName varchar(10)
declare @.dob datetime
declare @.age int
declare @.pos1 int
declare @.pos2 int
begin
--@.input = '1,Joe,Smith,1/1/01,3'
set @.rownum = Left(@.input, 1)
set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
comma so @.pos1 is at 2nd comma
set @.fName = substring(@.input, 3, @.pos1 - 3)
set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
+ @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
+ @.age + '/><ROOT>
End
If anyone has a suggestion how I could streamline this -
please share because my actual row is more like 50
fields. I am thinking a loop would be more efficient, but
I don't know how to implement a loop in a Sql Server UDF.
Could someone share please?
Thanks,
Ed
set @.pos1 = charindex(@.input, ',', @.pos2) --
Hi Ed,
You might have a slightly easier time converting the input string to XML
using C#/VB (or some other mid-tier programming language). Also from a DB
performance standpoint offloading all this string manipulation elsewhere may
help (although the XML you send over the wire will be bigger) - of course
all this depends on how your app is set up and where your bottlenecks are,
so take this with a grain of salt
You can check out
http://msdn.microsoft.com/library/de...chtutorial.asp
for a good example of implementing a class that supports the foreach loop
construct over an array of string tokens. Combine that with your favorite
TextReader implementation to read through all the "rows" of string tokens to
generate your XML. In terms of generating the XML you can just use string
manipulation or the XmlWriter API
(http://msdn.microsoft.com/library/de...-us/cpref/html
/frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
getting used to but it gives you a higher level abstraction write access to
the underlying XML.
You could loop through your string tokens and create XML that looks like:
<Root value1='{somevalue}' value2='{someothervalue}...</Root> by appending
the iteration number to the name of the attribute value - if there is a
consistent ordering of fields in the file you could then just use the
"colPattern" construct in OPENXML to map the attributes to the appropriate
columns in the table.
If you need to do the whole thing in the server let me know and we can think
about further.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"ED" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
> I am starting to get the hang of this. The trick is in
> the ConvertToXML UDF. Here is the xml string that I want
> to create which will work with openxml
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> Here is the string that I pass in to the ConvertToXML UDF
> '1,Joe,Smith,1/1/01,3'
> Here is the Original function
> ----
> CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
> @.Delim varchar (5))
> returns varchar (8000)
> as
> begin
> return ('<ROOT><Worktable Value="' + replace (@.InputStr,
> @.Delim, '"/><Worktable Value="') + '"/></ROOT>')
> end
> which yields the following from '1,Joe,Smith,1/1/01,3'
> <ROOT><Worktable Value="1"/><Worktable
> Value="Joe"/><Worktable Value="Smith"/><Worktable
> Value="1/1/01"/><Worktable Value="3"/></ROOT>
> which is 5 rows and one column
> ----
> But I need it to look like this:
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> to get 1 row and 5 columns
> so in the function my kludge would look like this:
> ...
> declare @.rownum int
> declare @.fName varchar(10)
> declare @.lName varchar(10)
> declare @.dob datetime
> declare @.age int
> declare @.pos1 int
> declare @.pos2 int
> begin
> --@.input = '1,Joe,Smith,1/1/01,3'
> set @.rownum = Left(@.input, 1)
> set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
> comma so @.pos1 is at 2nd comma
> set @.fName = substring(@.input, 3, @.pos1 - 3)
> set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
> set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
> set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
> set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
> set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
> set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
> set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
> return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
> + @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
> + @.age + '/><ROOT>
> End
> If anyone has a suggestion how I could streamline this -
> please share because my actual row is more like 50
> fields. I am thinking a loop would be more efficient, but
> I don't know how to implement a loop in a Sql Server UDF.
> Could someone share please?
> Thanks,
> Ed
> set @.pos1 = charindex(@.input, ',', @.pos2) --
>
>
|||Actually I was just looking around MSDN and if you can do it using C# then
you will have a much easier time with Chris Lovett's XmlCsvReader at
http://msdn.microsoft.com/library/de...lcsvreader.asp -
This will give you a much easier time - just put the column names you want
in the first row of your CSV file/stream and this thing will do the work for
you. The link has a code sample to get you going.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi Ed,
> You might have a slightly easier time converting the input string to XML
> using C#/VB (or some other mid-tier programming language). Also from a DB
> performance standpoint offloading all this string manipulation elsewhere
may
> help (although the XML you send over the wire will be bigger) - of course
> all this depends on how your app is set up and where your bottlenecks are,
> so take this with a grain of salt
> You can check out
>
http://msdn.microsoft.com/library/de...chtutorial.asp
> for a good example of implementing a class that supports the foreach loop
> construct over an array of string tokens. Combine that with your favorite
> TextReader implementation to read through all the "rows" of string tokens
to
> generate your XML. In terms of generating the XML you can just use string
> manipulation or the XmlWriter API
>
(http://msdn.microsoft.com/library/de...-us/cpref/html
> /frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
> getting used to but it gives you a higher level abstraction write access
to
> the underlying XML.
> You could loop through your string tokens and create XML that looks like:
> <Root value1='{somevalue}' value2='{someothervalue}...</Root> by
appending
> the iteration number to the name of the attribute value - if there is a
> consistent ordering of fields in the file you could then just use the
> "colPattern" construct in OPENXML to map the attributes to the appropriate
> columns in the table.
> If you need to do the whole thing in the server let me know and we can
think
> about further.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ED" <anonymous@.discussions.microsoft.com> wrote in message
> news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
>
|||Depending on what you finally want to do with it, I am not sure that going
from CSV to XML to rowset is the best way.
At least in the context of SQL Server 2005, you probably want to write a CLR
user defined function that takes the CSV and makes it directly into a
table-valued result.
Otherwise, Adam's suggestions are useful too.
Best regards
Michael
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:uvUdwO7fEHA.536@.TK2MSFTNGP11.phx.gbl...
> Actually I was just looking around MSDN and if you can do it using C# then
> you will have a much easier time with Chris Lovett's XmlCsvReader at
> http://msdn.microsoft.com/library/de...lcsvreader.asp -
> This will give you a much easier time - just put the column names you want
> in the first row of your CSV file/stream and this thing will do the work
> for
> you. The link has a code sample to get you going.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
> news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> may
> http://msdn.microsoft.com/library/de...chtutorial.asp
> to
> (http://msdn.microsoft.com/library/de...-us/cpref/html
> to
> appending
> think
> rights.
>
Can not write to a SQL table from Access 2003
I've read through many posts regarding this type of issue and all posts were
relevant but not exactly my problem.
I use Access 2003 and I have a simple linked table to a Sql2000 DB table, I
then have a form that I can use to view/amend data directly on the sql
table. However, I then have another table in the same DB that I have created
a form to view/amend the data but this table does not allow me to amend, I
can only view.
Both tables are in the same DB, and I am logged in as the same user. As far
as I can see both Tables have the same permissions. The only difference is
the table that I can not amend was created by performing a DTS Import. I
have ran the profiler and found that when accessing the table that fails to
amend it only passes a SELECT statement to the server, the table that does
amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so I'm confused if the
problem is in Access or on the Table in SQL. Any Help would be very much
appreciated.
Rob
SORRY, SORRY, SORRY. I've just realised what was happening, bit embarrassed
to say. I never had a primary key.
All sorted.
"Rob" <rbotterill@.hspg.com> wrote in message
news:uhZHI6lHFHA.3484@.TK2MSFTNGP12.phx.gbl...
> I've read through many posts regarding this type of issue and all posts
> were relevant but not exactly my problem.
> I use Access 2003 and I have a simple linked table to a Sql2000 DB table,
> I then have a form that I can use to view/amend data directly on the sql
> table. However, I then have another table in the same DB that I have
> created a form to view/amend the data but this table does not allow me to
> amend, I can only view.
> Both tables are in the same DB, and I am logged in as the same user. As
> far as I can see both Tables have the same permissions. The only
> difference is the table that I can not amend was created by performing a
> DTS Import. I have ran the profiler and found that when accessing the
> table that fails to amend it only passes a SELECT statement to the server,
> the table that does amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so
> I'm confused if the problem is in Access or on the Table in SQL. Any Help
> would be very much appreciated.
> Rob
>
|||"Rob" <rbotterill@.hspg.com> wrote in message
news:OLe0EknHFHA.2552@.TK2MSFTNGP10.phx.gbl...
> SORRY, SORRY, SORRY. I've just realised what was happening, bit
embarrassed
> to say. I never had a primary key.
Don't be sorry, this is a common mistake... you may also want to add a
column of timestamp as well.
http://support.microsoft.com/default...b;en-us;208842
Steve
relevant but not exactly my problem.
I use Access 2003 and I have a simple linked table to a Sql2000 DB table, I
then have a form that I can use to view/amend data directly on the sql
table. However, I then have another table in the same DB that I have created
a form to view/amend the data but this table does not allow me to amend, I
can only view.
Both tables are in the same DB, and I am logged in as the same user. As far
as I can see both Tables have the same permissions. The only difference is
the table that I can not amend was created by performing a DTS Import. I
have ran the profiler and found that when accessing the table that fails to
amend it only passes a SELECT statement to the server, the table that does
amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so I'm confused if the
problem is in Access or on the Table in SQL. Any Help would be very much
appreciated.
Rob
SORRY, SORRY, SORRY. I've just realised what was happening, bit embarrassed
to say. I never had a primary key.
All sorted.
"Rob" <rbotterill@.hspg.com> wrote in message
news:uhZHI6lHFHA.3484@.TK2MSFTNGP12.phx.gbl...
> I've read through many posts regarding this type of issue and all posts
> were relevant but not exactly my problem.
> I use Access 2003 and I have a simple linked table to a Sql2000 DB table,
> I then have a form that I can use to view/amend data directly on the sql
> table. However, I then have another table in the same DB that I have
> created a form to view/amend the data but this table does not allow me to
> amend, I can only view.
> Both tables are in the same DB, and I am logged in as the same user. As
> far as I can see both Tables have the same permissions. The only
> difference is the table that I can not amend was created by performing a
> DTS Import. I have ran the profiler and found that when accessing the
> table that fails to amend it only passes a SELECT statement to the server,
> the table that does amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so
> I'm confused if the problem is in Access or on the Table in SQL. Any Help
> would be very much appreciated.
> Rob
>
|||"Rob" <rbotterill@.hspg.com> wrote in message
news:OLe0EknHFHA.2552@.TK2MSFTNGP10.phx.gbl...
> SORRY, SORRY, SORRY. I've just realised what was happening, bit
embarrassed
> to say. I never had a primary key.
Don't be sorry, this is a common mistake... you may also want to add a
column of timestamp as well.
http://support.microsoft.com/default...b;en-us;208842
Steve
Can not write to a SQL table from Access 2003
I've read through many posts regarding this type of issue and all posts were
relevant but not exactly my problem.
I use Access 2003 and I have a simple linked table to a Sql2000 DB table, I
then have a form that I can use to view/amend data directly on the sql
table. However, I then have another table in the same DB that I have created
a form to view/amend the data but this table does not allow me to amend, I
can only view.
Both tables are in the same DB, and I am logged in as the same user. As far
as I can see both Tables have the same permissions. The only difference is
the table that I can not amend was created by performing a DTS Import. I
have ran the profiler and found that when accessing the table that fails to
amend it only passes a SELECT statement to the server, the table that does
amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so I'm confused if the
problem is in Access or on the Table in SQL. Any Help would be very much
appreciated.
RobSORRY, SORRY, SORRY. I've just realised what was happening, bit embarrassed
to say. I never had a primary key.
All sorted.
"Rob" <rbotterill@.hspg.com> wrote in message
news:uhZHI6lHFHA.3484@.TK2MSFTNGP12.phx.gbl...
> I've read through many posts regarding this type of issue and all posts
> were relevant but not exactly my problem.
> I use Access 2003 and I have a simple linked table to a Sql2000 DB table,
> I then have a form that I can use to view/amend data directly on the sql
> table. However, I then have another table in the same DB that I have
> created a form to view/amend the data but this table does not allow me to
> amend, I can only view.
> Both tables are in the same DB, and I am logged in as the same user. As
> far as I can see both Tables have the same permissions. The only
> difference is the table that I can not amend was created by performing a
> DTS Import. I have ran the profiler and found that when accessing the
> table that fails to amend it only passes a SELECT statement to the server,
> the table that does amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so
> I'm confused if the problem is in Access or on the Table in SQL. Any Help
> would be very much appreciated.
> Rob
>|||"Rob" <rbotterill@.hspg.com> wrote in message
news:OLe0EknHFHA.2552@.TK2MSFTNGP10.phx.gbl...
> SORRY, SORRY, SORRY. I've just realised what was happening, bit
embarrassed
> to say. I never had a primary key.
Don't be sorry, this is a common mistake... you may also want to add a
column of timestamp as well.
http://support.microsoft.com/defaul...kb;en-us;208842
Steve
relevant but not exactly my problem.
I use Access 2003 and I have a simple linked table to a Sql2000 DB table, I
then have a form that I can use to view/amend data directly on the sql
table. However, I then have another table in the same DB that I have created
a form to view/amend the data but this table does not allow me to amend, I
can only view.
Both tables are in the same DB, and I am logged in as the same user. As far
as I can see both Tables have the same permissions. The only difference is
the table that I can not amend was created by performing a DTS Import. I
have ran the profiler and found that when accessing the table that fails to
amend it only passes a SELECT statement to the server, the table that does
amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so I'm confused if the
problem is in Access or on the Table in SQL. Any Help would be very much
appreciated.
RobSORRY, SORRY, SORRY. I've just realised what was happening, bit embarrassed
to say. I never had a primary key.
All sorted.
"Rob" <rbotterill@.hspg.com> wrote in message
news:uhZHI6lHFHA.3484@.TK2MSFTNGP12.phx.gbl...
> I've read through many posts regarding this type of issue and all posts
> were relevant but not exactly my problem.
> I use Access 2003 and I have a simple linked table to a Sql2000 DB table,
> I then have a form that I can use to view/amend data directly on the sql
> table. However, I then have another table in the same DB that I have
> created a form to view/amend the data but this table does not allow me to
> amend, I can only view.
> Both tables are in the same DB, and I am logged in as the same user. As
> far as I can see both Tables have the same permissions. The only
> difference is the table that I can not amend was created by performing a
> DTS Import. I have ran the profiler and found that when accessing the
> table that fails to amend it only passes a SELECT statement to the server,
> the table that does amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so
> I'm confused if the problem is in Access or on the Table in SQL. Any Help
> would be very much appreciated.
> Rob
>|||"Rob" <rbotterill@.hspg.com> wrote in message
news:OLe0EknHFHA.2552@.TK2MSFTNGP10.phx.gbl...
> SORRY, SORRY, SORRY. I've just realised what was happening, bit
embarrassed
> to say. I never had a primary key.
Don't be sorry, this is a common mistake... you may also want to add a
column of timestamp as well.
http://support.microsoft.com/defaul...kb;en-us;208842
Steve
Subscribe to:
Posts (Atom)