I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>>I have a view with 3 integer fields: FailureCount, QuestionCount and
>>AuditCount. I want to get the following calculated result in a SELECT
>>statement:
>>SELECT FailureCount / (QuestionCount * AuditCount)
>>Problem is, this returns an integer result (as one would expect from 3
>>integers). I need the real number value, and CONVERT isn't working. I've
>>tried various syntaxes and I either get errors or no effect at all.
>>Any ideas how to properly do this?
>>Thanks,
>>Randall Arnold
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Tuesday, March 27, 2012
Converting calculated field to real
I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.
4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.
4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Converting calculated field to real
I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall Arnold
On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall Arnold
On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Thursday, March 22, 2012
Converting a date field to the month
How can I create a field in a view that takes a date field and
converts it to a month?
For instance, if I have a field called "CreatedOn" with a date of
'2/22/2007', how can I create another field called "Month" that will
say "February"? Is there a built-in SQL function that does this (like
using CONVERT) or do I need to write my own?
Look up datename in Books Online.
converts it to a month?
For instance, if I have a field called "CreatedOn" with a date of
'2/22/2007', how can I create another field called "Month" that will
say "February"? Is there a built-in SQL function that does this (like
using CONVERT) or do I need to write my own?
Thanks!
Lisa(lisa.moffitt@.gmail.com) writes:
Quote:
Originally Posted by
How can I create a field in a view that takes a date field and
converts it to a month?
For instance, if I have a field called "CreatedOn" with a date of
'2/22/2007', how can I create another field called "Month" that will
say "February"? Is there a built-in SQL function that does this (like
using CONVERT) or do I need to write my own?
Look up datename in Books Online.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Tuesday, March 20, 2012
Converted report too far against left side of screen
I converted an RS2000 report to RS2005 and uploaded it to a new 2005 Report Server.
When I view the report through IE 6 at the Report Server URL, the report seems jammed against the left side of the screen. This didn't happen when the report was in RS2000. When it was still an RS2000 report, there appeared to be some space (perhaps 1/8 - 1/4 inch) of space between the left edge of the screen and the left margin of the report.
Using Visual Studio 2005, I've tried moving all the objects within the report a little to the right, but that messes up the way the report prints.
Is this a common problem? Is there a way to get the space to the left of the report back when the report is rendered?
Thanks for your help!
Nancy
Also...
The border that used to be around the body of the report no longer shows up. I've checked the properties for the body of the report, and it is formatted to Border Color = Black, Border Style = Solid, and Border Width = 2pt. But the border no longer appears when the report is rendered.
sqlsqlconvert view to table
Hi,
How to convert a view to a table in SQL server?
I have created the view with related tables and filtered. But my system
studying on can not work with a view.
or any other solution?
Thanks without expect reply,
SELECT * INTO NewTable FROM Yourview
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Hseyin Grler" <hguruler@.mu.edu.tr> schrieb im Newsbeitrag
news:OJOWF6ORFHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How to convert a view to a table in SQL server?
> I have created the view with related tables and filtered. But my system
> studying on can not work with a view.
> or any other solution?
> Thanks without expect reply,
>
How to convert a view to a table in SQL server?
I have created the view with related tables and filtered. But my system
studying on can not work with a view.
or any other solution?
Thanks without expect reply,
SELECT * INTO NewTable FROM Yourview
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Hseyin Grler" <hguruler@.mu.edu.tr> schrieb im Newsbeitrag
news:OJOWF6ORFHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How to convert a view to a table in SQL server?
> I have created the view with related tables and filtered. But my system
> studying on can not work with a view.
> or any other solution?
> Thanks without expect reply,
>
Sunday, March 11, 2012
Convert text to varchar
When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.
Convert text to varchar
When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx
.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx
.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.
Convert text to varchar
When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?
I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?
|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?
I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?
|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.
Thursday, March 8, 2012
Convert SQL Data
I am looking for a way to convert SQL data to Access 2002 format. I want to
be able to view the data in access. Is there a way to connect to my SQL
server with access and be able to view and save the databases to the access
format. I want to be able to have a DBF copy of my access databases in the
event of a total failure.KMD,
There are a couple of ways to do this. 1) you can use Access to import your
SQL Server database into Access. This is probably the easiest way. 2)
Attach the SQL Server tables to your Access database. From there you can ru
n
queries that create new tables or just keep them attached and view them that
way.
Hope this helps.
Scott
"kmd" wrote:
> I am looking for a way to convert SQL data to Access 2002 format. I want
to
> be able to view the data in access. Is there a way to connect to my SQL
> server with access and be able to view and save the databases to the acces
s
> format. I want to be able to have a DBF copy of my access databases in th
e
> event of a total failure.
>
>
be able to view the data in access. Is there a way to connect to my SQL
server with access and be able to view and save the databases to the access
format. I want to be able to have a DBF copy of my access databases in the
event of a total failure.KMD,
There are a couple of ways to do this. 1) you can use Access to import your
SQL Server database into Access. This is probably the easiest way. 2)
Attach the SQL Server tables to your Access database. From there you can ru
n
queries that create new tables or just keep them attached and view them that
way.
Hope this helps.
Scott
"kmd" wrote:
> I am looking for a way to convert SQL data to Access 2002 format. I want
to
> be able to view the data in access. Is there a way to connect to my SQL
> server with access and be able to view and save the databases to the acces
s
> format. I want to be able to have a DBF copy of my access databases in th
e
> event of a total failure.
>
>
Convert SQL Data
I am looking for a way to convert SQL data to Access 2002 format. I want to
be able to view the data in access. Is there a way to connect to my SQL
server with access and be able to view and save the databases to the access
format. I want to be able to have a DBF copy of my access databases in the
event of a total failure.
KMD,
There are a couple of ways to do this. 1) you can use Access to import your
SQL Server database into Access. This is probably the easiest way. 2)
Attach the SQL Server tables to your Access database. From there you can run
queries that create new tables or just keep them attached and view them that
way.
Hope this helps.
Scott
"kmd" wrote:
> I am looking for a way to convert SQL data to Access 2002 format. I want to
> be able to view the data in access. Is there a way to connect to my SQL
> server with access and be able to view and save the databases to the access
> format. I want to be able to have a DBF copy of my access databases in the
> event of a total failure.
>
>
be able to view the data in access. Is there a way to connect to my SQL
server with access and be able to view and save the databases to the access
format. I want to be able to have a DBF copy of my access databases in the
event of a total failure.
KMD,
There are a couple of ways to do this. 1) you can use Access to import your
SQL Server database into Access. This is probably the easiest way. 2)
Attach the SQL Server tables to your Access database. From there you can run
queries that create new tables or just keep them attached and view them that
way.
Hope this helps.
Scott
"kmd" wrote:
> I am looking for a way to convert SQL data to Access 2002 format. I want to
> be able to view the data in access. Is there a way to connect to my SQL
> server with access and be able to view and save the databases to the access
> format. I want to be able to have a DBF copy of my access databases in the
> event of a total failure.
>
>
Saturday, February 25, 2012
Convert Null Values to 0
Hi
I've got a view which returns Null values due to an left outer join. The field which sometimes returns Nulls is called ClientCount. I want to create a new field which displays the value of ClientCount if ClientCount is not null and 0 if it is null.
In MS Access I use the following: IIF([ClientCount] Is Null,0,[ClientCount])
This does not seem to work in MS SQL. Is there some equivalent function I could use?
Thanks
David
David:
The best way to convert a null to a zero is to use ISNULL( [Client Count], 0 ) or COALESCE( [Client Count], 0 ).
|||How to convert null to zero ina query.Convert Null Values to 0
Hi
I've got a view which returns Null values due to an left outer join. The field which sometimes returns Nulls is called ClientCount. I want to create a new field which displays the value of ClientCount if ClientCount is not null and 0 if it is null.
In MS Access I use the following: IIF([ClientCount] Is Null,0,[ClientCount])
This does not seem to work in MS SQL. Is there some equivalent function I could use?
Thanks
David
David:
The best way to convert a null to a zero is to use ISNULL( [Client Count], 0 ) or COALESCE( [Client Count], 0 ).
|||How to convert null to zero ina query.convert null to 0
I am trying to convert a value of null to 0. Can I do ot in the VIEW i created. I am calling from the VIEW from a query on my page and using a datareader to populate a datagrid. The error I get is
The sql function ISNULL(mydecimal,'0') as my decimal
thanks anyway!!|||use
Operator is not valid for type 'DBNull' and type 'Decimal'.Thanks for any helpOH,<<itemtemplate><%# String.Format("<font color="darkred">{0:c}</font>", (DataBinder.Eval(Container.DataItem, "MyDecimal"))*(DataBinder.Eval(Container.DataItem, "price"))) %>
Line 81: </itemtemplate>
Line 82: </asp:templatecolumn>
The sql function ISNULL(mydecimal,'0') as my decimal
thanks anyway!!|||use
isnull(price,0)in the query
hth
Tuesday, February 14, 2012
Convert from .sdf file to .mdb file
Hellow
I have an application that builds a .sdf Sqlq Server Compact Edition file I want to view the file on a desktop computer How can I do it ?
If there is no simple way to do this how can I write a C# application to convert the .sdf file to .mdb file so I will be able to use Microsoft Access to view it.
Thanks
Ofer
You can connect to your SQL Compact Edition database using SQL Server 2005 Management Studio or Visual Studio 2005 SP1. If you need to convert from .SDF to MS Access (or vice-versa), check out the Data Port Wizard tool from one of the other MVPs, JP Figueroa:
http://www.primeworks-mobile.com/
Regards,
Darren Shaffer
|||You can also try SQL Server Express edition if you do not have SQL Server 2005.
Cheers
Jeba
Sunday, February 12, 2012
Convert Error
I am trying to convert a varchar(10) into a smalldatetime field in a view.
The table holds the date information in the format dd/mm/yyyy in a
varchar(10).
I can convert most fields in this format in my view using;
CONVERT (smalldatetime, ActualInDate, 103)
However one field fails, I have some dates as 02/01/0000, this is because it is a blank system date which is output from my mainframe job to extract data from another server.
Can I convert these blank dates to sql blank dates in my convert statement, ie replace these dates from 02/01/0000 to 01/01/1900.
The error I get is the error below;
Database Server: Microsoft SQL Server
Version 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime datatype resulted in an out-of-range small datetime value.
Cheers,
JonMy guess would be that you are having problems with different data rows than you think are causing the problems. Use the IsDate() function to show you the "problem children" in your data. I suspect that your server is interpreting the dates as MM/DD/YYYY instead of dd/mm/yyyy as you expect. If that is the case, I'd convert the whole bunch to ISO standard format of YYYY-MM-DD (like 1900-01-01), which is a lot harder to misinterpret!
-PatP|||This appears to work:
CREATE TABLE MyTable
(
ActualInDate varchar(20)
)
INSERT INTO MyTable(ActualInDate)
VALUES ('02/01/0000')
INSERT INTO MyTable(ActualInDate)
VALUES ('13/02/1975')
SELECT CONVERT (smalldatetime, (CASE WHEN ActualInDate = '02/01/0000' THEN '01/01/1900' ELSE ActualInDate END), 103)
FROM MyTable|||Yup, it appears, but appearances are misleading...
SELECT CONVERT (smalldatetime, (
case
when charindex('0000', ActualInDate) > 0 then '01/01/1900'
else case
when isdate(ActualInDate) = 0 then '01/01/1900'
else ActualInDate end
end), 103)
FROM MyTable|||Thanks for the ideas but I just used this to do it and it seems fine:
CONVERT (smalldatetime, REPLACE(ActualIn, '02/01/0000', '01/01/1900'), 103)
Cheers|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.
He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.
He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.I don't think that's his sole purpose in life, but rdjabarov often comes across that way. I've just had to develop thicker skin where his posts are concerned.
He really does know what he's doing (at least as often as most of us do anyway :)), but he comes across pretty abrasively at time.
-PatP|||Well there's the other side of the coin as well...you have to take everything with a grain of salt...because
IT JUST DOESN'T MATTER
Speaking of which...where's Rudy?|||I did not say he did'nt know what he was talking about. I was objecting to the snide remarks
I am sorry.
I am cranky ass.
Quitting smoking.
Unrealistic deadline.
Little sleep.
Stressed from a couple job interviews.
Too easily offended from my hot southern celtic blood.
I apologize.|||Speaking of which...where's Rudy?speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood
;)|||i was actually enjoying the intellectual dialogue in this thread
I'm still looking...|||speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood
;)Yeah, but it does help to put things into context!
-PatP|||Pat does contexts ;)
The table holds the date information in the format dd/mm/yyyy in a
varchar(10).
I can convert most fields in this format in my view using;
CONVERT (smalldatetime, ActualInDate, 103)
However one field fails, I have some dates as 02/01/0000, this is because it is a blank system date which is output from my mainframe job to extract data from another server.
Can I convert these blank dates to sql blank dates in my convert statement, ie replace these dates from 02/01/0000 to 01/01/1900.
The error I get is the error below;
Database Server: Microsoft SQL Server
Version 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime datatype resulted in an out-of-range small datetime value.
Cheers,
JonMy guess would be that you are having problems with different data rows than you think are causing the problems. Use the IsDate() function to show you the "problem children" in your data. I suspect that your server is interpreting the dates as MM/DD/YYYY instead of dd/mm/yyyy as you expect. If that is the case, I'd convert the whole bunch to ISO standard format of YYYY-MM-DD (like 1900-01-01), which is a lot harder to misinterpret!
-PatP|||This appears to work:
CREATE TABLE MyTable
(
ActualInDate varchar(20)
)
INSERT INTO MyTable(ActualInDate)
VALUES ('02/01/0000')
INSERT INTO MyTable(ActualInDate)
VALUES ('13/02/1975')
SELECT CONVERT (smalldatetime, (CASE WHEN ActualInDate = '02/01/0000' THEN '01/01/1900' ELSE ActualInDate END), 103)
FROM MyTable|||Yup, it appears, but appearances are misleading...
SELECT CONVERT (smalldatetime, (
case
when charindex('0000', ActualInDate) > 0 then '01/01/1900'
else case
when isdate(ActualInDate) = 0 then '01/01/1900'
else ActualInDate end
end), 103)
FROM MyTable|||Thanks for the ideas but I just used this to do it and it seems fine:
CONVERT (smalldatetime, REPLACE(ActualIn, '02/01/0000', '01/01/1900'), 103)
Cheers|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.
He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.
He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.I don't think that's his sole purpose in life, but rdjabarov often comes across that way. I've just had to develop thicker skin where his posts are concerned.
He really does know what he's doing (at least as often as most of us do anyway :)), but he comes across pretty abrasively at time.
-PatP|||Well there's the other side of the coin as well...you have to take everything with a grain of salt...because
IT JUST DOESN'T MATTER
Speaking of which...where's Rudy?|||I did not say he did'nt know what he was talking about. I was objecting to the snide remarks
I am sorry.
I am cranky ass.
Quitting smoking.
Unrealistic deadline.
Little sleep.
Stressed from a couple job interviews.
Too easily offended from my hot southern celtic blood.
I apologize.|||Speaking of which...where's Rudy?speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood
;)|||i was actually enjoying the intellectual dialogue in this thread
I'm still looking...|||speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood
;)Yeah, but it does help to put things into context!
-PatP|||Pat does contexts ;)
Friday, February 10, 2012
convert date issue in view
i have created a view to a table (90,000+ records) that i want to use to filter the table by the current year and month. the code looks like this:
CREATE VIEW [billy.bhuj].[bo current month]
AS SELECT [dbo].[bo].[recnum], [dbo].[bo].[queue],
[dbo].[bo].[queue_name], [dbo].[bo].[node],
[dbo].[bo].[interval], [dbo].[bo].[tot_calls],
[dbo].[bo].[calls_less_20_sec], [dbo].[bo].[calls_more_20_sec],
[dbo].[bo].[calls_abandon], [dbo].[bo].[abandon_before_20_sec],
[dbo].[bo].[abandon_after_20_sec],
[dbo].[bo].[queue_date],
month (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate,
year (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate1,
convert (datetime,[dbo].[bo].[queue_date], 103) as QDate2,
year (convert (datetime,(getdate()), 104)) as year1,
[dbo].[bo].[region], [dbo].[bo].[queue_type],
[dbo].[bo].[month],
[dbo].[bo].[unit],
[dbo].[bo].[service], [dbo].[bo].[reportable], [dbo].[bo].[source_dest],
[dbo].[bo].[file_name]
FROM [dbo].[bo]
Where (year (convert (datetime,[dbo].[bo].[queue_date], 104)))
= (year (convert (datetime,(getdate()), 104)))
the syntax checks fine, and without the "where" clause , i get all the original data returned no problem, so the "month", "year", and "convert" functions work fine. however, when i try to filter the data with the "where" clause above, i get about 15-20 lines of data returned and an error message referring to "Arithmetic overflow...". on their own in the "select" area the statements do what they should, but in the "where" statement they don't. sorry, i'm a big time newbie in sql, so any help would be appreciated.
hi,
are your trying to do something like a parameterized view
well if you are you should be creating stored procedure
rather than a view
regards,
joey
|||
sorry, my bad...
i re checked the results without the "where" clause, and i was getting an error code further down the result list! same Arithmetic Overflow" error! i removed all the "convert" lines and it runs okay.
bo.queue_date is a nvarchar. i am using "convert" to change it to a date field. i do not own the table where bo.queue_date originates.
so how do i convert from a nvarchar field to a date field, and then filter out the dates i don't want in the "where" clause? should i be using "cast" instead of "convert"? or could this be a result of improper data in the bo.queue_date of the original table?
i am not trying to pass a parameter, just filter the main table down to a more manageable size by filtering by current month and year. if a stored proc makes more sense to do this though, i could try that instead...
|||How do you know if the values in queue_date can be successfully converted to datetime? Is it always in one particular format? Why is that column nvarchar instead of datetime or smalldatetime? You could do check like below and then convert to prevent the conversion error:
select ...
, year(t.q_dt)
, month(t.q_dt)
from (
select ...
, case isdate(bo.queue_date) when 1 then convert(datetime, bo.queue_date, 104) end as q_dt
from bo
) as t
where year(t.q_dt) = year(CURRENT_TIMESTAMP)
But I don't think this is the problem. You talked about arithmetic overflow error which is different. Is bo a table or view? If bo is a view then you need to check the SELECT statement of the bo to see if there are any expressions that can result in arithmetic overflow. Also, please post the exact error message when asking for help.
Subscribe to:
Comments (Atom)