Sorry - this question has probably been asked many times before as it
is about the first issue a newbie would hit.
I am moving a backend currently held in 5 (cos we keep exceeding file
size limit) MS access databases in to two SQL server databases .
After cleaning up a few data issues I am quite happily importing about
200 access tables - I have even set up some DTS packages to perform
the task.
Question:
Is there any tool available to assist in applying the indices ?
Something that scans the access tables and assembles a change sql
script for each table based on the indexing we were using under
access.
I wouldn't expect the scripts to run problem free - but just something
to cut down the slog work a bit.
A collection of cleaned up / refined scripts is what I would really
like at the end of the day - as I am testing all this out in my home
office and then would like to repeat it all at the client site.
I have had a look at the meta data facility but that does not appear
to pick up the MS Access indexing in the source databases
Many thanks in advance
Tony
With as many tables as you are talking about you could probably script
something.
Using DAO (yes.. the old old old Data Access Objects)
You can navigate your table hierarchy and pull the the index information out
that way. Then dump it to a file somewhere.
The hieararchy IIRC Goes:
DatabaseEngine
Workspaces
Databases
TableDefs
Indexes
Fields
Something like the following (Note, this is from memory and not tested, but
should get you on the right track).
HTH
Dim db as DAO.Database
Set db = CurrentDB()
Dim CreateIndex as String
Dim td as DAO.TableDef
Dim idx as DAO.Index
ForEach (td in db.TableDefs)
ForEach (idx in td.Indexes)
' Put your Create Index commands here.
' Add the fields involved in a loop as well. Something like
'Foreach (fld in idx.Fields)
Next
Next
Next
Rick Sawtell
MCT, MCSD, MCDBA
|||
>With as many tables as you are talking about you could probably script
>something.
>Using DAO <snip>
Thanks Rick
I was afraid that I would have to go down that path ... :-(
Your answer has been very helpful - because I was afraid I was
overlooking some really obvious, quick tool that everyone knew about -
but now I can go about writing the DAO code happy in the knowledge
that I am not wasting my time. :-)
Many thanks
Tony
ps I am ashamed to admit that I am still using DAO code for everything
I do.
Saturday, February 25, 2012
Newbie - step1 - importing MS access tables
Sorry - this question has probably been asked many times before as it
is about the first issue a newbie would hit.
I am moving a backend currently held in 5 (cos we keep exceeding file
size limit) MS access databases in to two SQL server databases .
After cleaning up a few data issues I am quite happily importing about
200 access tables - I have even set up some DTS packages to perform
the task.
Question:
Is there any tool available to assist in applying the indices ?
Something that scans the access tables and assembles a change sql
script for each table based on the indexing we were using under
access.
I wouldn't expect the scripts to run problem free - but just something
to cut down the slog work a bit.
A collection of cleaned up / refined scripts is what I would really
like at the end of the day - as I am testing all this out in my home
office and then would like to repeat it all at the client site.
I have had a look at the meta data facility but that does not appear
to pick up the MS Access indexing in the source databases
Many thanks in advance
TonyWith as many tables as you are talking about you could probably script
something.
Using DAO (yes.. the old old old Data Access Objects)
You can navigate your table hierarchy and pull the the index information out
that way. Then dump it to a file somewhere.
The hieararchy IIRC Goes:
DatabaseEngine
Workspaces
Databases
TableDefs
Indexes
Fields
Something like the following (Note, this is from memory and not tested, but
should get you on the right track).
HTH
Dim db as DAO.Database
Set db = CurrentDB()
Dim CreateIndex as String
Dim td as DAO.TableDef
Dim idx as DAO.Index
ForEach (td in db.TableDefs)
ForEach (idx in td.Indexes)
' Put your Create Index commands here.
' Add the fields involved in a loop as well. Something like
'Foreach (fld in idx.Fields)
Next
Next
Next
Rick Sawtell
MCT, MCSD, MCDBA|||>With as many tables as you are talking about you could probably script
>something.
>Using DAO <snip>
Thanks Rick
I was afraid that I would have to go down that path ... :-(
Your answer has been very helpful - because I was afraid I was
overlooking some really obvious, quick tool that everyone knew about -
but now I can go about writing the DAO code happy in the knowledge
that I am not wasting my time. :-)
Many thanks
Tony
ps I am ashamed to admit that I am still using DAO code for everything
I do.
is about the first issue a newbie would hit.
I am moving a backend currently held in 5 (cos we keep exceeding file
size limit) MS access databases in to two SQL server databases .
After cleaning up a few data issues I am quite happily importing about
200 access tables - I have even set up some DTS packages to perform
the task.
Question:
Is there any tool available to assist in applying the indices ?
Something that scans the access tables and assembles a change sql
script for each table based on the indexing we were using under
access.
I wouldn't expect the scripts to run problem free - but just something
to cut down the slog work a bit.
A collection of cleaned up / refined scripts is what I would really
like at the end of the day - as I am testing all this out in my home
office and then would like to repeat it all at the client site.
I have had a look at the meta data facility but that does not appear
to pick up the MS Access indexing in the source databases
Many thanks in advance
TonyWith as many tables as you are talking about you could probably script
something.
Using DAO (yes.. the old old old Data Access Objects)
You can navigate your table hierarchy and pull the the index information out
that way. Then dump it to a file somewhere.
The hieararchy IIRC Goes:
DatabaseEngine
Workspaces
Databases
TableDefs
Indexes
Fields
Something like the following (Note, this is from memory and not tested, but
should get you on the right track).
HTH
Dim db as DAO.Database
Set db = CurrentDB()
Dim CreateIndex as String
Dim td as DAO.TableDef
Dim idx as DAO.Index
ForEach (td in db.TableDefs)
ForEach (idx in td.Indexes)
' Put your Create Index commands here.
' Add the fields involved in a loop as well. Something like
'Foreach (fld in idx.Fields)
Next
Next
Next
Rick Sawtell
MCT, MCSD, MCDBA|||>With as many tables as you are talking about you could probably script
>something.
>Using DAO <snip>
Thanks Rick
I was afraid that I would have to go down that path ... :-(
Your answer has been very helpful - because I was afraid I was
overlooking some really obvious, quick tool that everyone knew about -
but now I can go about writing the DAO code happy in the knowledge
that I am not wasting my time. :-)
Many thanks
Tony
ps I am ashamed to admit that I am still using DAO code for everything
I do.
Newbie - SqlConnection statement
I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
RandyHi Randy
"Randy" wrote:
> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
http://msdn.microsoft.com/library/d...opi
c.asp
John
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
RandyHi Randy
"Randy" wrote:
> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
http://msdn.microsoft.com/library/d...opi
c.asp
John
Newbie - SqlConnection statement
I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
Randy
Hi Randy
"Randy" wrote:
> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.as p[/url]
John
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
Randy
Hi Randy
"Randy" wrote:
> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.as p[/url]
John
Newbie - SqlConnection statement
I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
RandyHi Randy
"Randy" wrote:
> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.asp
John
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:
Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"
This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:
Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"
End Sub
The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.
I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.
Can anybody see where I am going wrong?
Thanks,
RandyHi Randy
"Randy" wrote:
> I'm trying to get a combobox to fill with values from a table. I've
> set up an example using the Northwind database with the Categories
> table. Dragging in the CategoryID field onto my form as a combobox,
> I've set the following properties for the CategoryID combobox:
> Value member = "CategoriesBindingSource - CategoryID"
> Display Member = "CategoriesBindingSource - CategoryID"
> This seems to bring in the correct values in the two non-index fields
> (CategoryName and Description). I've then added the following code to
> the combobox:
> Dim Conn As SqlConnection
> Conn = New SqlConnection("Database=Northwnd.mdf")
> 'Conn.Open()
> Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
> CategoryID FROM Categories", Conn)
> Dim ds As New DataSet
> da.Fill(ds, "Categories")
> CategoryIDComboBox.DataSource = ds
> 'CategoryIDComboBox.ValueMember = "CategoryID"
> 'CategoryIDComboBox.DisplayMember = "CategoryID"
> End Sub
> The lines that are commented out are other ideas that I've tried to no
> avail. I left them here in case they are relevant.
> I suspect that at least part of my problem is in the SqlDataAdapter
> statement where I am pointing to the database. I am working on a
> standalone pc.
> Can anybody see where I am going wrong?
> Thanks,
> Randy
>
For connection string information check out
http://www.connectionstrings.com/?carrier=sqlserver2005
You may also want to some of the examples such as
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.asp
John
Newbie - sql server 2000 transaction log size limit
Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.Originally posted by axis
Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.
When the size is set to unlimited I think the limit is as big the win2k can handle?
I had once a log file of 15 Gb big|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.
thanks|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.
thanks
:D Thats true !!! sp_helpdb dbname always helps ...
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.Originally posted by axis
Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.
When the size is set to unlimited I think the limit is as big the win2k can handle?
I had once a log file of 15 Gb big|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.
thanks|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.
thanks
:D Thats true !!! sp_helpdb dbname always helps ...
Newbie - sa password error after ACT! install - Need pro's help!!!
Hello,
I am admittedly new to this, especially sql server and am in need of a pro's assistance.. I installed Microsoft Small Business accounting, which in turn installed and configured a sql server. When I installed ACT! 2005 (v7.0) and tried to create a database, it states that it cannot find the master file.... so I tried to install sql server from Microsofts site - downloaded all the packages, unzipped them and ran them... the error I get is that the sql server needs to have an sa password and to use some switch to change it... for the life of me I am lost here. I can't use ACT! at this point and I have not ideas of how to remove, reinstall or repair the sql server.... Any help you can provide would be greatly appreicated!
I am admittedly new to this, especially sql server and am in need of a pro's assistance.. I installed Microsoft Small Business accounting, which in turn installed and configured a sql server. When I installed ACT! 2005 (v7.0) and tried to create a database, it states that it cannot find the master file.... so I tried to install sql server from Microsofts site - downloaded all the packages, unzipped them and ran them... the error I get is that the sql server needs to have an sa password and to use some switch to change it... for the life of me I am lost here. I can't use ACT! at this point and I have not ideas of how to remove, reinstall or repair the sql server.... Any help you can provide would be greatly appreicated!
The default sa password is blank. Is it complaining that this is not acceptable and you need to change it ?
What version of SQL Server do you have ? It sounds like SQL Server Express 2005 ? You may well have two instances installed now, one that came with ACT, and one that you've installed.
Overall, I'd say ACT are the people to talk to, you're using SQL Server with ACT, right ?
|||Yes, it is saying that the sa password is empty...specifically: "A strong sa password is required for security purposes. Please us SAPWD switch to supply the same. Refer to readme for more details. Setup will now exit." That is what is says when I attempt to run ANY installation version of SQL ... attempting to correct the sql error that I get when I run ACT!. Is there a way to repair or rebuild the sql server? I have only one instance, from the ACT! install... Thanks for the reply.. !!|||By the way.. that was the install error for SQL Server 2000 version A specifically... I installed SQL Server 2005 Express... or tried to... it would run either..|||
ACT will support either one ? I'm not sure that you don't have two instances running now ( SQL Server can have more than one version, or multiple instances of the same version, running on a machine ). If I were you, I'd uninstall the lot, including SQL Server, and reinstall ACT. To set the password, you run the installer for SQL Server from the command line and specify SAPWD="whatever", which will set the SA password. If the version of SQL Server you have installed has a management console, or whatever it was called in SS2000, you can go into it, find the Users section, and change the password there.
However, I would be more inclined to do a reinstall from scratch and then try the ACT site:
http://www.act.com/support/technicalsupport/index.cfm
I've looked and I can't see anything specific there, but the problem obviously relates to ACT, and if you've paid for it, they would be the ones who know what's going on, and should be supporting you to get their program working.
|||Sounds great... as for ACT! I called them already.. since I bought this version a while ago,. they stopped supporting it a couple months ago! Nice huh?! As for uninstalling them, I'm all for it.. how do I uninstall SS2000 and all the other instances of SQL Server? It's not in my remove programs dialog....... Thanks again for the help... look for to hearing back from you|||
If SS2000 is not in your list, perhaps MSDE is ( that's the free version ). Otherwise, maybe it uninstalls with ACT. If you go to Control Panel/Administrative Tools/Services, the list is alphabetical, if there are any listings that start with SQL Server, you still have SQL Server installed :-)
Your question is right on the edge of what is on topic here, and that means you're unlikely to find people with direct, first hand experience of the problem. If an uninstall/reinstall doesn't solve it, then I'd suggest looking for a forum populated with people who use ACT. I'm happy to try and help, but I'm pretty much in the dark, all I can do is comment based on what I know of SQL Server.
Newbie - Report Design help.
Hi folks,
New to RS, but have this really annoying colleague b*tching about
coversheets and TPS reports. (Sorry, off-topic).
Anyways, I've been ranting about the wonders of SQL RS to him, and then
he turns up wanting me to actually do a real report for him. In the
sample below, I'm using the Northwind database.
Basic idea is: You select a year in a combobox and run the report.
The report then shows the quanties sold for each product for that year
in one column, and in the next column it shows the equivalent data for
the previous year.
Tables used in this sample: Orders, OrderDetails and Products.
Hope someone can help me. :-)
-McWawa, the next President of The United States of America.
---
Select Year: 1999 |
2000 |
2001 | <- these are in a combobox
2002 |
2003 |
Products | 2001* | 2000**
---
Produkt A | 145 | 84
Produkt B | 200 | 129
Produkt C | 101 | 88
.. | ... | ...
.. | ... | ...
Produkt Z | 140 | 31
* = The Year selected in the combobox
** = The Year BEFORE the on selected in the combobox
---What exactly do you need help with?
This is a fairly straightforward report. Just create a parameter to allow
the user to select a year, run your query based upon the parameter, and
populate your simple table with the results.
What specifically are you having a problem with?
"McWawa" <n@.dachanche.ever> wrote in message
news:uuStdiczGHA.3512@.TK2MSFTNGP04.phx.gbl...
> Hi folks,
> New to RS, but have this really annoying colleague b*tching about
> coversheets and TPS reports. (Sorry, off-topic).
> Anyways, I've been ranting about the wonders of SQL RS to him, and then he
> turns up wanting me to actually do a real report for him. In the sample
> below, I'm using the Northwind database.
> Basic idea is: You select a year in a combobox and run the report.
> The report then shows the quanties sold for each product for that year in
> one column, and in the next column it shows the equivalent data for the
> previous year.
> Tables used in this sample: Orders, OrderDetails and Products.
> Hope someone can help me. :-)
> -McWawa, the next President of The United States of America.
>
> ---
> Select Year: 1999 |
> 2000 |
> 2001 | <- these are in a combobox
> 2002 |
> 2003 |
>
> Products | 2001* | 2000**
> ---
> Produkt A | 145 | 84
> Produkt B | 200 | 129
> Produkt C | 101 | 88
> .. | ... | ...
> .. | ... | ...
> Produkt Z | 140 | 31
>
> * = The Year selected in the combobox
> ** = The Year BEFORE the on selected in the combobox
>
> ---|||> This is a fairly straightforward report. Just create a parameter to allow
> the user to select a year, run your query based upon the parameter, and
> populate your simple table with the results.
> What specifically are you having a problem with?
Well, I think my problems may be caused by my lack of tsql experience.
If you (or anyone) could point me in the right direction with the query, I
think I could piece the report together from that.
McWawa|||Solved it through my query. :-)
New to RS, but have this really annoying colleague b*tching about
coversheets and TPS reports. (Sorry, off-topic).
Anyways, I've been ranting about the wonders of SQL RS to him, and then
he turns up wanting me to actually do a real report for him. In the
sample below, I'm using the Northwind database.
Basic idea is: You select a year in a combobox and run the report.
The report then shows the quanties sold for each product for that year
in one column, and in the next column it shows the equivalent data for
the previous year.
Tables used in this sample: Orders, OrderDetails and Products.
Hope someone can help me. :-)
-McWawa, the next President of The United States of America.
---
Select Year: 1999 |
2000 |
2001 | <- these are in a combobox
2002 |
2003 |
Products | 2001* | 2000**
---
Produkt A | 145 | 84
Produkt B | 200 | 129
Produkt C | 101 | 88
.. | ... | ...
.. | ... | ...
Produkt Z | 140 | 31
* = The Year selected in the combobox
** = The Year BEFORE the on selected in the combobox
---What exactly do you need help with?
This is a fairly straightforward report. Just create a parameter to allow
the user to select a year, run your query based upon the parameter, and
populate your simple table with the results.
What specifically are you having a problem with?
"McWawa" <n@.dachanche.ever> wrote in message
news:uuStdiczGHA.3512@.TK2MSFTNGP04.phx.gbl...
> Hi folks,
> New to RS, but have this really annoying colleague b*tching about
> coversheets and TPS reports. (Sorry, off-topic).
> Anyways, I've been ranting about the wonders of SQL RS to him, and then he
> turns up wanting me to actually do a real report for him. In the sample
> below, I'm using the Northwind database.
> Basic idea is: You select a year in a combobox and run the report.
> The report then shows the quanties sold for each product for that year in
> one column, and in the next column it shows the equivalent data for the
> previous year.
> Tables used in this sample: Orders, OrderDetails and Products.
> Hope someone can help me. :-)
> -McWawa, the next President of The United States of America.
>
> ---
> Select Year: 1999 |
> 2000 |
> 2001 | <- these are in a combobox
> 2002 |
> 2003 |
>
> Products | 2001* | 2000**
> ---
> Produkt A | 145 | 84
> Produkt B | 200 | 129
> Produkt C | 101 | 88
> .. | ... | ...
> .. | ... | ...
> Produkt Z | 140 | 31
>
> * = The Year selected in the combobox
> ** = The Year BEFORE the on selected in the combobox
>
> ---|||> This is a fairly straightforward report. Just create a parameter to allow
> the user to select a year, run your query based upon the parameter, and
> populate your simple table with the results.
> What specifically are you having a problem with?
Well, I think my problems may be caused by my lack of tsql experience.
If you (or anyone) could point me in the right direction with the query, I
think I could piece the report together from that.
McWawa|||Solved it through my query. :-)
Newbie RDL to RDLC Conversion
Hi,
I am developing a few reports using the Micrsoft report server which
create a "RDL" file. I was wondering if I can directly transfer these
reports to my ASP.Net application and convert them to the RDLC format?
can anybody provide any information about this functionality?
ThanksAssuming they are RS 2005 files all you have to do is change the extension
and bring them into you project.
I looked at using the viewer control in local mode (which is what you are
talking about doing) and if you have a RS server around you are much better
off to use it in server mode. There is a lot of mucking around that you have
to do with local reports. For instance, for a subreport you have to wire up
an event. It is nothing all that difficult but between hooking up events,
retrieving the data etc it is a good bit more effort than just calling the
report and passing a parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Momomo" <le_mo_mo@.yahoo.com> wrote in message
news:1188354990.554512.26060@.k79g2000hse.googlegroups.com...
> Hi,
> I am developing a few reports using the Micrsoft report server which
> create a "RDL" file. I was wondering if I can directly transfer these
> reports to my ASP.Net application and convert them to the RDLC format?
> can anybody provide any information about this functionality?
> Thanks
>|||Hi,
Thank you for the response.How do I deal with "RDS (Shared Data
Source)" and "RDL.Data" files? hwo di I migrate those to my web site?
Mo|||I think you are a bit confused about how the control works. First, you are
using the reportviewer control that ships with VS 2005. It comes both as a
webform and as a winform control. Assuming you are then you need to decide
whether you want to use local mode or server mode. In server mode it
connects to RS server. You provide the parameters and it calls and then
displays the report. This is pretty straight forward to implement. In local
mode you give it the report (rdlc file) and hand it the dataset (really a
datatable). You have total control and you have to do more work.
RS does not have an RDL file on the server, it stores the object. You have
to save the report from Report Manager or get it out of the original VS
project that created the reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Momomo" <le_mo_mo@.yahoo.com> wrote in message
news:1188418919.496400.315330@.57g2000hsv.googlegroups.com...
> Hi,
> Thank you for the response.How do I deal with "RDS (Shared Data
> Source)" and "RDL.Data" files? hwo di I migrate those to my web site?
> Mo
>
I am developing a few reports using the Micrsoft report server which
create a "RDL" file. I was wondering if I can directly transfer these
reports to my ASP.Net application and convert them to the RDLC format?
can anybody provide any information about this functionality?
ThanksAssuming they are RS 2005 files all you have to do is change the extension
and bring them into you project.
I looked at using the viewer control in local mode (which is what you are
talking about doing) and if you have a RS server around you are much better
off to use it in server mode. There is a lot of mucking around that you have
to do with local reports. For instance, for a subreport you have to wire up
an event. It is nothing all that difficult but between hooking up events,
retrieving the data etc it is a good bit more effort than just calling the
report and passing a parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Momomo" <le_mo_mo@.yahoo.com> wrote in message
news:1188354990.554512.26060@.k79g2000hse.googlegroups.com...
> Hi,
> I am developing a few reports using the Micrsoft report server which
> create a "RDL" file. I was wondering if I can directly transfer these
> reports to my ASP.Net application and convert them to the RDLC format?
> can anybody provide any information about this functionality?
> Thanks
>|||Hi,
Thank you for the response.How do I deal with "RDS (Shared Data
Source)" and "RDL.Data" files? hwo di I migrate those to my web site?
Mo|||I think you are a bit confused about how the control works. First, you are
using the reportviewer control that ships with VS 2005. It comes both as a
webform and as a winform control. Assuming you are then you need to decide
whether you want to use local mode or server mode. In server mode it
connects to RS server. You provide the parameters and it calls and then
displays the report. This is pretty straight forward to implement. In local
mode you give it the report (rdlc file) and hand it the dataset (really a
datatable). You have total control and you have to do more work.
RS does not have an RDL file on the server, it stores the object. You have
to save the report from Report Manager or get it out of the original VS
project that created the reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Momomo" <le_mo_mo@.yahoo.com> wrote in message
news:1188418919.496400.315330@.57g2000hsv.googlegroups.com...
> Hi,
> Thank you for the response.How do I deal with "RDS (Shared Data
> Source)" and "RDL.Data" files? hwo di I migrate those to my web site?
> Mo
>
Newbie - Querying SQL Server Express 2005 database from Excel
Sorry if this is a stupid question, but I created a database using SQl Server Express 2005 and I would like to query one of its tables from an Excel spreadsheet.
Here's what I want to do in pseudo code.
cell A2.value = select OLIGO_ID from table OLIGO where SEQUENCE = 'content of cell D2, a string'
In other words, I want to search the database for a string that is in a cell and retrieve its associated ID number into another cell. I need to do this on many cells.
Any help is appreciated. Thanks.
I guess you can do that with VBA.
As far as SQL is concerned I do something like that to extract data from a cube down to Excel. You will just have to build the connection string for SQL Express (get it from the macro recorder) and adjust the query to a more TSQL like query rather than OLAP. You can build any string you want.
Function to query SQL with a query string
-
Private Sub ado(Connection As String, Query As String, destination As String)
Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Set cnnConnect = New ADODB.Connection
cnnConnect.Open Connection
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
Source:=Query, _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText
With ActiveSheet.QueryTables.Add( _
Connection:=rstRecordset, _
destination:=Range(destination))
.FieldNames = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
cnnConnect.Close
Set cnnConnect = Nothing
Set rstRecordset = Nothing
End Sub
-
-- OLAPMENU
OlapMenu = _
"Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Location=analysis.onsemi.com;Initial Catalog=" & InitialCatalog & ""
--
call to the function
Query = "select" & _
"[data Switch].[Switch].members on axis(0)," & _
" Filter( [Region].[Rep Sales Region Desc].members, [Data Switch].[On] >0 ) on axis(1)" & _
"from [ST_Crawl]"
Application.StatusBar = "Now Populating Pull-down Region, please wait..."
Application.Cursor = xlWait
Call ado(OlapMenu, Query, "A3")
Newbie - Query with loop?
Howdy,
I am relatively new to Transact SQL and need some help on what should be a
simple query...
I have a table with
* Item
* Cost
* MonthEndingDate (e.g. 1/31/2006, 2/28/2006)
I need a query to return the Cost for a given month, but if the cost is null
or there is no record, I need the previous month's Cost. If the previous
month's Cost is null or there is no record, I need the month before that
(going back 3 months). If all three months are null then I need a null.
Example
Item Cost MonthEnding
Book 10.25 1/31/2006
Book 10.50 2/28/2006
Pen 1.07 2/28/2006
Pen 1.08 3/31/2006
Pen 1.10 4/30/2006
Pencil .10 12/31/2006
Pencil .15 1/31/2006
I would need the query to return:
Item Cost
Book 10.50
Pen 1.10
Pencil null
Any help would be greatly appreciated!! Thanks in advance!
-KatWill you be passing Item into the query/stored procedure as a param or do
you want to run against all items at one time?
Do you have a list of unique items stored in a separate table or will the
query be expected to find a list of all the possible items and then return
the most recent (within a three month period of time) cost?
Can you call a stored procedure or do you have to do this within one select
statement?
Keith Kratochvil
"kat" <kat@.discussions.microsoft.com> wrote in message
news:556D2F1C-6288-47CF-94A4-7C7DB884410F@.microsoft.com...
> Howdy,
> I am relatively new to Transact SQL and need some help on what should be a
> simple query...
> I have a table with
> * Item
> * Cost
> * MonthEndingDate (e.g. 1/31/2006, 2/28/2006)
> I need a query to return the Cost for a given month, but if the cost is
> null
> or there is no record, I need the previous month's Cost. If the previous
> month's Cost is null or there is no record, I need the month before that
> (going back 3 months). If all three months are null then I need a null.
> Example
> Item Cost MonthEnding
> Book 10.25 1/31/2006
> Book 10.50 2/28/2006
> Pen 1.07 2/28/2006
> Pen 1.08 3/31/2006
> Pen 1.10 4/30/2006
> Pencil .10 12/31/2006
> Pencil .15 1/31/2006
> I would need the query to return:
> Item Cost
> Book 10.50
> Pen 1.10
> Pencil null
> Any help would be greatly appreciated!! Thanks in advance!
> -Kat|||Hi Keith,
Thanks for taking the time to read my question... To answer yours:
I want to run against all items at one time.
I do have an Items table that contains all items.
I'd like to do this within a single select statement but can use other
methods it they are required. I was hoping to use as a view for multiple
projects!
Again, thanks!
-Kat
"Keith Kratochvil" wrote:
> Will you be passing Item into the query/stored procedure as a param or do
> you want to run against all items at one time?
> Do you have a list of unique items stored in a separate table or will the
> query be expected to find a list of all the possible items and then return
> the most recent (within a three month period of time) cost?
> Can you call a stored procedure or do you have to do this within one selec
t
> statement?
>
> --
> Keith Kratochvil
>
> "kat" <kat@.discussions.microsoft.com> wrote in message
> news:556D2F1C-6288-47CF-94A4-7C7DB884410F@.microsoft.com...
>
>|||I do not like this answer. It works, but it seems too complicated.
The "given month" is an issue. You mention in your second message
that you were hoping for a view, but a view - at least in SQL Server
2000 - can not have a parameter. There are a few ways this might be
simplified in 2005.
Anyway, here is something.
CREATE TABLE TheTable
(Item varchar(20),
Cost decimal(8,2),
MonthEnding smalldatetime)
INSERT TheTable values('Book', 10.25, '01/31/2006')
INSERT TheTable values('Book', 10.50, '02/28/2006')
INSERT TheTable values('Pen', 1.07, '02/28/2006')
INSERT TheTable values('Pen', 1.08, '03/31/2006')
INSERT TheTable values('Pen', 1.10, '04/30/2006')
INSERT TheTable values('Pencil', 0.10, '12/31/2006')
INSERT TheTable values('Pencil', 0.15, '01/31/2006')
declare @.givenmonth datetime --match MonthEnding
set @.givenmonth = '20060430'
SELECT T1.Item, T2.Cost
FROM (select distinct Item from TheTable) as T1
LEFT OUTER
JOIN TheTable as T2
ON T1.Item = T2.Item
AND T2.MonthEnding =
(select max(S.MonthEnding)
from TheTable as S
where T2.Item = S.Item
and S.MonthEnding IN
(select top 3 MonthEnding
from TheTable
where MonthEnding <= @.givenmonth
order by MonthEnding desc))
drop table TheTable
I have another idea that might look better. Maybe I will work it out
too.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 14:12:02 -0700, kat <kat@.discussions.microsoft.com>
wrote:
>Howdy,
>I am relatively new to Transact SQL and need some help on what should be a
>simple query...
>I have a table with
>* Item
>* Cost
>* MonthEndingDate (e.g. 1/31/2006, 2/28/2006)
>I need a query to return the Cost for a given month, but if the cost is nul
l
>or there is no record, I need the previous month's Cost. If the previous
>month's Cost is null or there is no record, I need the month before that
>(going back 3 months). If all three months are null then I need a null.
>Example
>Item Cost MonthEnding
>Book 10.25 1/31/2006
>Book 10.50 2/28/2006
>Pen 1.07 2/28/2006
>Pen 1.08 3/31/2006
>Pen 1.10 4/30/2006
>Pencil .10 12/31/2006
>Pencil .15 1/31/2006
>I would need the query to return:
>Item Cost
>Book 10.50
>Pen 1.10
>Pencil null
>Any help would be greatly appreciated!! Thanks in advance!
>-Kat|||On Wed, 03 May 2006 17:47:14 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:
>I have another idea that might look better. Maybe I will work it out
>too.
This includes the original solution, as well as two alternatives that
might be easier to work with.
Roy Harvey
Beacon Falls, CT
CREATE TABLE TheTable
(Item varchar(20),
Cost decimal(8,2),
MonthEnding smalldatetime)
INSERT TheTable values('Book', 10.25, '01/31/2006')
INSERT TheTable values('Book', 10.50, '02/28/2006')
INSERT TheTable values('Pen', 1.07, '02/28/2006')
INSERT TheTable values('Pen', 1.08, '03/31/2006')
INSERT TheTable values('Pen', 1.10, '04/30/2006')
INSERT TheTable values('Pencil', 0.10, '12/31/2006')
INSERT TheTable values('Pencil', 0.15, '01/31/2006')
declare @.givenmonth datetime --match MonthEnding
set @.givenmonth = '20060430'
SELECT T1.Item, T2.Cost
FROM (select distinct Item from TheTable) as T1
LEFT OUTER
JOIN TheTable as T2
ON T1.Item = T2.Item
AND T2.MonthEnding =
(select max(S.MonthEnding)
from TheTable as S
where T2.Item = S.Item
and S.MonthEnding IN
(select top 3 MonthEnding
from TheTable
where MonthEnding <= @.givenmonth
order by MonthEnding desc))
--This alternative requires another table
CREATE TABLE MonthEnds
(MonthEnding smalldatetime)
INSERT MonthEnds
SELECT DISTINCT MonthEnding FROM TheTable
declare @.givenminus1 datetime
SELECT @.givenminus1 = MAX(MonthEnding)
FROM MonthEnds
WHERE MonthEnding < @.givenmonth
declare @.givenminus2 datetime
SELECT @.givenminus2 = MAX(MonthEnding)
FROM MonthEnds
WHERE MonthEnding < @.givenminus1
SELECT K.Item,
COALESCE(T1.Cost, T2.Cost, T3.Cost) as Cost
FROM (select distinct Item from TheTable) as K
LEFT OUTER
JOIN TheTable as T1
ON K.Item = T1.Item
AND T1.MonthEnding = @.givenmonth
LEFT OUTER
JOIN TheTable as T2
ON K.Item = T2.Item
AND T2.MonthEnding = @.givenminus1
LEFT OUTER
JOIN TheTable as T3
ON K.Item = T3.Item
AND T3.MonthEnding = @.givenminus2
SELECT K.Item,
COALESCE(
(SELECT Cost FROM TheTable as T1
WHERE K.Item = T1.Item
AND T1.MonthEnding = @.givenmonth),
(SELECT Cost FROM TheTable as T2
WHERE K.Item = T2.Item
AND T2.MonthEnding = @.givenminus1),
(SELECT Cost FROM TheTable as T3
WHERE K.Item = T3.Item
AND T3.MonthEnding = @.givenminus2)) as Cost
FROM (select distinct Item from TheTable) as K
drop table TheTable, MonthEnds|||Hi Kat,
try this and let me know if this is what you want.
DECLARE @.GIVENMONTH DATETIME
SET @.GIVENMONTH = '20060430'
SELECT
A.ITEM,
CASE WHEN MAX(A.MONTHENDING) < DATEADD(MM,-2,@.GIVENMONTH)
THEN NULL
ELSE
(SELECT COST FROM THETABLE B
WHERE B.ITEM = A.ITEM AND B.MONTHENDING = MAX(A.MONTHENDING))
END AS COST
FROM THETABLE A WHERE
A.COST IS NOT NULL
GROUP BY ITEM|||Hi Kat,
You may try this,
Create Table tmpItem
(
Item varchar(20),
price numeric,
edate datetime);
Insert into tmpItem values('Book',10.5,'20060131');
Insert into tmpItem values('Book',10.4,'20060228');
Insert into tmpItem values('Book',10.3,'20060328');
Insert into tmpItem values('Pen',1.5,'20060131');
Insert into tmpItem values('Pen',1.4,'20060228');
Insert into tmpItem values('Pen',1.3,'20060328');
Insert into tmpItem values('Pencil',2.4,'20060228');
Insert into tmpItem values('Pencil',1.3,'20060328');
Select Item ,(Select Price from tmpItem X Where X.Item = Y.Item And
edate = '20060131')
from tmpItem Y group by Item
drop table tmpItem
With Warm regards
Jatinder Singh|||It really helsp if you post DDL; her is my guess about keys and
constaints... and the table name!
CREATE TABLE ItemSummary
(month_end DATETIME NOT NULL,
item_name VARCHAR(20) NOT NULL,
item_cost DECIMAL(8,2) NOT NULL,
PRIMARY KEY (month_end, item_name));
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Book', 10.25, '2006-01-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Book', 10.50, '2006-02-28');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.07, '2006-02-28');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.08, '2006-03-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.10, '2006-04-30');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pencil', 0.10, '2006-12-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pencil', 0.15, '2006-01-31');
This answer is more of a demonstration of soem coding tricks with
COALESCE(), but it might run better than you think, if the optimizer
does early evaluation on the COALESCE() list.
SELECT DISTINCT item_name,
COALESCE (
(SELECT MAX (item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-04-30'
AND I2.item_name = I1.item_name),
(SELECT MAX(item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-03-31'
AND I2.item_name = I1.item_name),
(SELECT MAX(item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-02-28'
AND I2.item_name = I1.item_name)
) AS recent_cost
FROM ItemSummary AS I1;
item_name recent_cost
==================
Book 10.50
Pen 1.10
Pencil NULL
I hard-wired the month ends, but you can use a DATEDIFF function and a
parameter to make this more general. A better way would be with a
report period calendar table.
I am relatively new to Transact SQL and need some help on what should be a
simple query...
I have a table with
* Item
* Cost
* MonthEndingDate (e.g. 1/31/2006, 2/28/2006)
I need a query to return the Cost for a given month, but if the cost is null
or there is no record, I need the previous month's Cost. If the previous
month's Cost is null or there is no record, I need the month before that
(going back 3 months). If all three months are null then I need a null.
Example
Item Cost MonthEnding
Book 10.25 1/31/2006
Book 10.50 2/28/2006
Pen 1.07 2/28/2006
Pen 1.08 3/31/2006
Pen 1.10 4/30/2006
Pencil .10 12/31/2006
Pencil .15 1/31/2006
I would need the query to return:
Item Cost
Book 10.50
Pen 1.10
Pencil null
Any help would be greatly appreciated!! Thanks in advance!
-KatWill you be passing Item into the query/stored procedure as a param or do
you want to run against all items at one time?
Do you have a list of unique items stored in a separate table or will the
query be expected to find a list of all the possible items and then return
the most recent (within a three month period of time) cost?
Can you call a stored procedure or do you have to do this within one select
statement?
Keith Kratochvil
"kat" <kat@.discussions.microsoft.com> wrote in message
news:556D2F1C-6288-47CF-94A4-7C7DB884410F@.microsoft.com...
> Howdy,
> I am relatively new to Transact SQL and need some help on what should be a
> simple query...
> I have a table with
> * Item
> * Cost
> * MonthEndingDate (e.g. 1/31/2006, 2/28/2006)
> I need a query to return the Cost for a given month, but if the cost is
> null
> or there is no record, I need the previous month's Cost. If the previous
> month's Cost is null or there is no record, I need the month before that
> (going back 3 months). If all three months are null then I need a null.
> Example
> Item Cost MonthEnding
> Book 10.25 1/31/2006
> Book 10.50 2/28/2006
> Pen 1.07 2/28/2006
> Pen 1.08 3/31/2006
> Pen 1.10 4/30/2006
> Pencil .10 12/31/2006
> Pencil .15 1/31/2006
> I would need the query to return:
> Item Cost
> Book 10.50
> Pen 1.10
> Pencil null
> Any help would be greatly appreciated!! Thanks in advance!
> -Kat|||Hi Keith,
Thanks for taking the time to read my question... To answer yours:
I want to run against all items at one time.
I do have an Items table that contains all items.
I'd like to do this within a single select statement but can use other
methods it they are required. I was hoping to use as a view for multiple
projects!
Again, thanks!
-Kat
"Keith Kratochvil" wrote:
> Will you be passing Item into the query/stored procedure as a param or do
> you want to run against all items at one time?
> Do you have a list of unique items stored in a separate table or will the
> query be expected to find a list of all the possible items and then return
> the most recent (within a three month period of time) cost?
> Can you call a stored procedure or do you have to do this within one selec
t
> statement?
>
> --
> Keith Kratochvil
>
> "kat" <kat@.discussions.microsoft.com> wrote in message
> news:556D2F1C-6288-47CF-94A4-7C7DB884410F@.microsoft.com...
>
>|||I do not like this answer. It works, but it seems too complicated.
The "given month" is an issue. You mention in your second message
that you were hoping for a view, but a view - at least in SQL Server
2000 - can not have a parameter. There are a few ways this might be
simplified in 2005.
Anyway, here is something.
CREATE TABLE TheTable
(Item varchar(20),
Cost decimal(8,2),
MonthEnding smalldatetime)
INSERT TheTable values('Book', 10.25, '01/31/2006')
INSERT TheTable values('Book', 10.50, '02/28/2006')
INSERT TheTable values('Pen', 1.07, '02/28/2006')
INSERT TheTable values('Pen', 1.08, '03/31/2006')
INSERT TheTable values('Pen', 1.10, '04/30/2006')
INSERT TheTable values('Pencil', 0.10, '12/31/2006')
INSERT TheTable values('Pencil', 0.15, '01/31/2006')
declare @.givenmonth datetime --match MonthEnding
set @.givenmonth = '20060430'
SELECT T1.Item, T2.Cost
FROM (select distinct Item from TheTable) as T1
LEFT OUTER
JOIN TheTable as T2
ON T1.Item = T2.Item
AND T2.MonthEnding =
(select max(S.MonthEnding)
from TheTable as S
where T2.Item = S.Item
and S.MonthEnding IN
(select top 3 MonthEnding
from TheTable
where MonthEnding <= @.givenmonth
order by MonthEnding desc))
drop table TheTable
I have another idea that might look better. Maybe I will work it out
too.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 14:12:02 -0700, kat <kat@.discussions.microsoft.com>
wrote:
>Howdy,
>I am relatively new to Transact SQL and need some help on what should be a
>simple query...
>I have a table with
>* Item
>* Cost
>* MonthEndingDate (e.g. 1/31/2006, 2/28/2006)
>I need a query to return the Cost for a given month, but if the cost is nul
l
>or there is no record, I need the previous month's Cost. If the previous
>month's Cost is null or there is no record, I need the month before that
>(going back 3 months). If all three months are null then I need a null.
>Example
>Item Cost MonthEnding
>Book 10.25 1/31/2006
>Book 10.50 2/28/2006
>Pen 1.07 2/28/2006
>Pen 1.08 3/31/2006
>Pen 1.10 4/30/2006
>Pencil .10 12/31/2006
>Pencil .15 1/31/2006
>I would need the query to return:
>Item Cost
>Book 10.50
>Pen 1.10
>Pencil null
>Any help would be greatly appreciated!! Thanks in advance!
>-Kat|||On Wed, 03 May 2006 17:47:14 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:
>I have another idea that might look better. Maybe I will work it out
>too.
This includes the original solution, as well as two alternatives that
might be easier to work with.
Roy Harvey
Beacon Falls, CT
CREATE TABLE TheTable
(Item varchar(20),
Cost decimal(8,2),
MonthEnding smalldatetime)
INSERT TheTable values('Book', 10.25, '01/31/2006')
INSERT TheTable values('Book', 10.50, '02/28/2006')
INSERT TheTable values('Pen', 1.07, '02/28/2006')
INSERT TheTable values('Pen', 1.08, '03/31/2006')
INSERT TheTable values('Pen', 1.10, '04/30/2006')
INSERT TheTable values('Pencil', 0.10, '12/31/2006')
INSERT TheTable values('Pencil', 0.15, '01/31/2006')
declare @.givenmonth datetime --match MonthEnding
set @.givenmonth = '20060430'
SELECT T1.Item, T2.Cost
FROM (select distinct Item from TheTable) as T1
LEFT OUTER
JOIN TheTable as T2
ON T1.Item = T2.Item
AND T2.MonthEnding =
(select max(S.MonthEnding)
from TheTable as S
where T2.Item = S.Item
and S.MonthEnding IN
(select top 3 MonthEnding
from TheTable
where MonthEnding <= @.givenmonth
order by MonthEnding desc))
--This alternative requires another table
CREATE TABLE MonthEnds
(MonthEnding smalldatetime)
INSERT MonthEnds
SELECT DISTINCT MonthEnding FROM TheTable
declare @.givenminus1 datetime
SELECT @.givenminus1 = MAX(MonthEnding)
FROM MonthEnds
WHERE MonthEnding < @.givenmonth
declare @.givenminus2 datetime
SELECT @.givenminus2 = MAX(MonthEnding)
FROM MonthEnds
WHERE MonthEnding < @.givenminus1
SELECT K.Item,
COALESCE(T1.Cost, T2.Cost, T3.Cost) as Cost
FROM (select distinct Item from TheTable) as K
LEFT OUTER
JOIN TheTable as T1
ON K.Item = T1.Item
AND T1.MonthEnding = @.givenmonth
LEFT OUTER
JOIN TheTable as T2
ON K.Item = T2.Item
AND T2.MonthEnding = @.givenminus1
LEFT OUTER
JOIN TheTable as T3
ON K.Item = T3.Item
AND T3.MonthEnding = @.givenminus2
SELECT K.Item,
COALESCE(
(SELECT Cost FROM TheTable as T1
WHERE K.Item = T1.Item
AND T1.MonthEnding = @.givenmonth),
(SELECT Cost FROM TheTable as T2
WHERE K.Item = T2.Item
AND T2.MonthEnding = @.givenminus1),
(SELECT Cost FROM TheTable as T3
WHERE K.Item = T3.Item
AND T3.MonthEnding = @.givenminus2)) as Cost
FROM (select distinct Item from TheTable) as K
drop table TheTable, MonthEnds|||Hi Kat,
try this and let me know if this is what you want.
DECLARE @.GIVENMONTH DATETIME
SET @.GIVENMONTH = '20060430'
SELECT
A.ITEM,
CASE WHEN MAX(A.MONTHENDING) < DATEADD(MM,-2,@.GIVENMONTH)
THEN NULL
ELSE
(SELECT COST FROM THETABLE B
WHERE B.ITEM = A.ITEM AND B.MONTHENDING = MAX(A.MONTHENDING))
END AS COST
FROM THETABLE A WHERE
A.COST IS NOT NULL
GROUP BY ITEM|||Hi Kat,
You may try this,
Create Table tmpItem
(
Item varchar(20),
price numeric,
edate datetime);
Insert into tmpItem values('Book',10.5,'20060131');
Insert into tmpItem values('Book',10.4,'20060228');
Insert into tmpItem values('Book',10.3,'20060328');
Insert into tmpItem values('Pen',1.5,'20060131');
Insert into tmpItem values('Pen',1.4,'20060228');
Insert into tmpItem values('Pen',1.3,'20060328');
Insert into tmpItem values('Pencil',2.4,'20060228');
Insert into tmpItem values('Pencil',1.3,'20060328');
Select Item ,(Select Price from tmpItem X Where X.Item = Y.Item And
edate = '20060131')
from tmpItem Y group by Item
drop table tmpItem
With Warm regards
Jatinder Singh|||It really helsp if you post DDL; her is my guess about keys and
constaints... and the table name!
CREATE TABLE ItemSummary
(month_end DATETIME NOT NULL,
item_name VARCHAR(20) NOT NULL,
item_cost DECIMAL(8,2) NOT NULL,
PRIMARY KEY (month_end, item_name));
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Book', 10.25, '2006-01-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Book', 10.50, '2006-02-28');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.07, '2006-02-28');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.08, '2006-03-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.10, '2006-04-30');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pencil', 0.10, '2006-12-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pencil', 0.15, '2006-01-31');
This answer is more of a demonstration of soem coding tricks with
COALESCE(), but it might run better than you think, if the optimizer
does early evaluation on the COALESCE() list.
SELECT DISTINCT item_name,
COALESCE (
(SELECT MAX (item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-04-30'
AND I2.item_name = I1.item_name),
(SELECT MAX(item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-03-31'
AND I2.item_name = I1.item_name),
(SELECT MAX(item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-02-28'
AND I2.item_name = I1.item_name)
) AS recent_cost
FROM ItemSummary AS I1;
item_name recent_cost
==================
Book 10.50
Pen 1.10
Pencil NULL
I hard-wired the month ends, but you can use a DATEDIFF function and a
parameter to make this more general. A better way would be with a
report period calendar table.
Newbie - Query convert money
I have been unsuccessful at getting this to display the way I want it to.
BOL says that convert(money, [expression] , 1) will display my sales figures
as 1,234,567.89. However the results for my query
SELECT TOP 100 PERCENT SalespersonID, convert(money, SUM(NetSales),1)
AS NetSales
GROUP BY SalespersonID
ORDER BY SalespersonID
returns the column NetSales as 1234567.89.
This is driving my boss crazy trying to determine whether the result is
$100,000 or $1,000,000.
Can someone explain this to me? The data type is already money, so I'm
wondering if it is just ignoring the convert...
I appreciate your help!Format at the client / presentation tier, because asking SQL Server to do
this is silly:
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,money_colum
n),1)),1,100))
AS money_column
FROM table
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:B3584EDB-D8FA-422B-8A67-D2B45735E2ED@.microsoft.com...
>I have been unsuccessful at getting this to display the way I want it to.
> BOL says that convert(money, [expression] , 1) will display my sales
> figures
> as 1,234,567.89. However the results for my query
> SELECT TOP 100 PERCENT SalespersonID, convert(money, SUM(NetSales),1)
> AS NetSales
> GROUP BY SalespersonID
> ORDER BY SalespersonID
> returns the column NetSales as 1234567.89.
> This is driving my boss crazy trying to determine whether the result is
> $100,000 or $1,000,000.
> Can someone explain this to me? The data type is already money, so I'm
> wondering if it is just ignoring the convert...
> I appreciate your help!
>
BOL says that convert(money, [expression] , 1) will display my sales figures
as 1,234,567.89. However the results for my query
SELECT TOP 100 PERCENT SalespersonID, convert(money, SUM(NetSales),1)
AS NetSales
GROUP BY SalespersonID
ORDER BY SalespersonID
returns the column NetSales as 1234567.89.
This is driving my boss crazy trying to determine whether the result is
$100,000 or $1,000,000.
Can someone explain this to me? The data type is already money, so I'm
wondering if it is just ignoring the convert...
I appreciate your help!Format at the client / presentation tier, because asking SQL Server to do
this is silly:
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,money_colum
n),1)),1,100))
AS money_column
FROM table
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:B3584EDB-D8FA-422B-8A67-D2B45735E2ED@.microsoft.com...
>I have been unsuccessful at getting this to display the way I want it to.
> BOL says that convert(money, [expression] , 1) will display my sales
> figures
> as 1,234,567.89. However the results for my query
> SELECT TOP 100 PERCENT SalespersonID, convert(money, SUM(NetSales),1)
> AS NetSales
> GROUP BY SalespersonID
> ORDER BY SalespersonID
> returns the column NetSales as 1234567.89.
> This is driving my boss crazy trying to determine whether the result is
> $100,000 or $1,000,000.
> Can someone explain this to me? The data type is already money, so I'm
> wondering if it is just ignoring the convert...
> I appreciate your help!
>
Newbie - Program
This is my first foray into MS SQL Server, so I am trying to get my feet wet
with a couple of things. One thing I am trying to do right now is fill in a
uniqueidentifier field I added to a table using the NEWID() function. In MS
Access, I would have cranked out a little VBA code that simply went through
all the records in the table one by one and setting the Record_ID (type
uniqueidentifier) field using NEWID().
Unfortunately, I don't have a warm and fuzzy on how to accomplish this
within SQL Server itself. Best I have been able to tell so far, I probably
want to be developing a procedure. But I am not sure how to accomplish what
I want to do using SQL.
Suggestions and pointers to references will be greatly appreciated!
Thanks!
DonHello, Don
You only need:
UPDATE YourTable SET YourColumn=NEWID()
That's because the NEWID() function will be called for each row
(therefore you will get a different value in each row).
Razvan|||In SQL server there is a thing called default value
You will see it in Enterprise Manager when you click on design table
In the default value property of a field you can type newid() and whenever a
new record is inserted it will 'automatically' get this value
Default values are very handy for Created Date you would give it a default
value of getdate()
Hope this helped you and did not add to the confusion
http://sqlservercode.blogspot.com/
"Don" wrote:
> This is my first foray into MS SQL Server, so I am trying to get my feet w
et
> with a couple of things. One thing I am trying to do right now is fill in
a
> uniqueidentifier field I added to a table using the NEWID() function. In
MS
> Access, I would have cranked out a little VBA code that simply went throug
h
> all the records in the table one by one and setting the Record_ID (type
> uniqueidentifier) field using NEWID().
> Unfortunately, I don't have a warm and fuzzy on how to accomplish this
> within SQL Server itself. Best I have been able to tell so far, I probabl
y
> want to be developing a procedure. But I am not sure how to accomplish wh
at
> I want to do using SQL.
> Suggestions and pointers to references will be greatly appreciated!
> Thanks!
> Don
>
>
>|||SQL,
While I was playing around, I discovered the default value and that if you
select that property when you create the field it will automatically fill in
values! In my first attempt, I had created the field, but not set the
default value so I had a table full of <Null> for that field (never mind the
issue of adding a new record later!).
Thanks for the help!!
Don
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:75975E43-A123-43DC-96C3-B120D0B35BF0@.microsoft.com...
> In SQL server there is a thing called default value
> You will see it in Enterprise Manager when you click on design table
> In the default value property of a field you can type newid() and whenever
> a
> new record is inserted it will 'automatically' get this value
> Default values are very handy for Created Date you would give it a default
> value of getdate()
> Hope this helped you and did not add to the confusion
> http://sqlservercode.blogspot.com/
>
> "Don" wrote:
>|||Razvan,
I actually solved my immediate problem a little differently, but your
suggestion will definitely help in the future!!
Thanks!
Don
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1127663811.868041.231450@.g43g2000cwa.googlegroups.com...
> Hello, Don
> You only need:
> UPDATE YourTable SET YourColumn=NEWID()
> That's because the NEWID() function will be called for each row
> (therefore you will get a different value in each row).
> Razvan
>|||Your entire mental model of SQL and RDBMS is totally wrong and will
take at least one year of solid work to change. This is based on 20+
years with SQL and teaching it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. NEWID and uniqueidentifier "field" are proprietary,
non-relational crap. Since you started with ACCESS, you need to add an
extra six months of UN-learning.
A newsgroup is a piss poor substitue for an education. Please read
something before you post again.
with a couple of things. One thing I am trying to do right now is fill in a
uniqueidentifier field I added to a table using the NEWID() function. In MS
Access, I would have cranked out a little VBA code that simply went through
all the records in the table one by one and setting the Record_ID (type
uniqueidentifier) field using NEWID().
Unfortunately, I don't have a warm and fuzzy on how to accomplish this
within SQL Server itself. Best I have been able to tell so far, I probably
want to be developing a procedure. But I am not sure how to accomplish what
I want to do using SQL.
Suggestions and pointers to references will be greatly appreciated!
Thanks!
DonHello, Don
You only need:
UPDATE YourTable SET YourColumn=NEWID()
That's because the NEWID() function will be called for each row
(therefore you will get a different value in each row).
Razvan|||In SQL server there is a thing called default value
You will see it in Enterprise Manager when you click on design table
In the default value property of a field you can type newid() and whenever a
new record is inserted it will 'automatically' get this value
Default values are very handy for Created Date you would give it a default
value of getdate()
Hope this helped you and did not add to the confusion
http://sqlservercode.blogspot.com/
"Don" wrote:
> This is my first foray into MS SQL Server, so I am trying to get my feet w
et
> with a couple of things. One thing I am trying to do right now is fill in
a
> uniqueidentifier field I added to a table using the NEWID() function. In
MS
> Access, I would have cranked out a little VBA code that simply went throug
h
> all the records in the table one by one and setting the Record_ID (type
> uniqueidentifier) field using NEWID().
> Unfortunately, I don't have a warm and fuzzy on how to accomplish this
> within SQL Server itself. Best I have been able to tell so far, I probabl
y
> want to be developing a procedure. But I am not sure how to accomplish wh
at
> I want to do using SQL.
> Suggestions and pointers to references will be greatly appreciated!
> Thanks!
> Don
>
>
>|||SQL,
While I was playing around, I discovered the default value and that if you
select that property when you create the field it will automatically fill in
values! In my first attempt, I had created the field, but not set the
default value so I had a table full of <Null> for that field (never mind the
issue of adding a new record later!).
Thanks for the help!!
Don
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:75975E43-A123-43DC-96C3-B120D0B35BF0@.microsoft.com...
> In SQL server there is a thing called default value
> You will see it in Enterprise Manager when you click on design table
> In the default value property of a field you can type newid() and whenever
> a
> new record is inserted it will 'automatically' get this value
> Default values are very handy for Created Date you would give it a default
> value of getdate()
> Hope this helped you and did not add to the confusion
> http://sqlservercode.blogspot.com/
>
> "Don" wrote:
>|||Razvan,
I actually solved my immediate problem a little differently, but your
suggestion will definitely help in the future!!
Thanks!
Don
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1127663811.868041.231450@.g43g2000cwa.googlegroups.com...
> Hello, Don
> You only need:
> UPDATE YourTable SET YourColumn=NEWID()
> That's because the NEWID() function will be called for each row
> (therefore you will get a different value in each row).
> Razvan
>|||Your entire mental model of SQL and RDBMS is totally wrong and will
take at least one year of solid work to change. This is based on 20+
years with SQL and teaching it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. NEWID and uniqueidentifier "field" are proprietary,
non-relational crap. Since you started with ACCESS, you need to add an
extra six months of UN-learning.
A newsgroup is a piss poor substitue for an education. Please read
something before you post again.
NEWBIE - Parameter @EmployeeName to select a particular employee OR all employees
I have an operational parameter in my SQL select statement, @.EmployeeName,
that will filter timecard data for a particular employee. When I am running
the query and it prompts me for @.EmployeeName, I would like the option of
putting in * or [ALL] or something of that nature to return all the timecard
data.
Is there a wildcard that I can put in my parameter prompt to return all the
records?
I will greatly appreciate any help you can offer on the subject. Thank you,
-Dave> that will filter timecard data for a particular employee. When I am
running
> the query and it prompts me for @.EmployeeName, I would like the option of
> putting in * or [ALL] or something of that nature to return all the
timecard
> data.
What prompts you for this? Can you not leave the parameter empty? How is
the stored procedure coded?
Typically, you can implement optional parameters, and when you call the
procedure, you can either include that parameter or not.
http://www.aspfaq.com/2348
I think you are being slowed down by the GUI tool you are using, not the
nature of parameters. Also, keep in mind that * is only a wildcard in DOS,
Microsoft Access and a few other places. SQL Server uses % and _ ...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I am using the Query Builder in VS.NET 2003, not a stored procedure. Do I
need to use a stored procedure to achieve this result?
When I leave the parameter empty, I get no results for my query.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23RLQapIMFHA.1308@.TK2MSFTNGP15.phx.gbl...
> running
> timecard
> What prompts you for this? Can you not leave the parameter empty? How is
> the stored procedure coded?
> Typically, you can implement optional parameters, and when you call the
> procedure, you can either include that parameter or not.
> http://www.aspfaq.com/2348
> I think you are being slowed down by the GUI tool you are using, not the
> nature of parameters. Also, keep in mind that * is only a wildcard in
> DOS,
> Microsoft Access and a few other places. SQL Server uses % and _ ...
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
that will filter timecard data for a particular employee. When I am running
the query and it prompts me for @.EmployeeName, I would like the option of
putting in * or [ALL] or something of that nature to return all the timecard
data.
Is there a wildcard that I can put in my parameter prompt to return all the
records?
I will greatly appreciate any help you can offer on the subject. Thank you,
-Dave> that will filter timecard data for a particular employee. When I am
running
> the query and it prompts me for @.EmployeeName, I would like the option of
> putting in * or [ALL] or something of that nature to return all the
timecard
> data.
What prompts you for this? Can you not leave the parameter empty? How is
the stored procedure coded?
Typically, you can implement optional parameters, and when you call the
procedure, you can either include that parameter or not.
http://www.aspfaq.com/2348
I think you are being slowed down by the GUI tool you are using, not the
nature of parameters. Also, keep in mind that * is only a wildcard in DOS,
Microsoft Access and a few other places. SQL Server uses % and _ ...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I am using the Query Builder in VS.NET 2003, not a stored procedure. Do I
need to use a stored procedure to achieve this result?
When I leave the parameter empty, I get no results for my query.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23RLQapIMFHA.1308@.TK2MSFTNGP15.phx.gbl...
> running
> timecard
> What prompts you for this? Can you not leave the parameter empty? How is
> the stored procedure coded?
> Typically, you can implement optional parameters, and when you call the
> procedure, you can either include that parameter or not.
> http://www.aspfaq.com/2348
> I think you are being slowed down by the GUI tool you are using, not the
> nature of parameters. Also, keep in mind that * is only a wildcard in
> DOS,
> Microsoft Access and a few other places. SQL Server uses % and _ ...
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
Newbie - Need Help
Hi,
I'm totally newbie in this age of coding / development & need to start to jump-in in this part of IT & need help that how to start / learn client / server database development either web based or something like this using sql2000 / vs2003.net ( vb ect. ). I know that there r new versions of sql & vs.net but I've just have to manage the versions mentioned above.
Just want to let u know that I've just handed over a half completed employee's attendance system ( database running in sql2000 ) & have asked to develop the reports for the data entered daily by employee's in/out barcode card scanning.
Rather to start develop reporting, I want to understand from roots that how the database ( tables etc. ) created / linked & how / which application I need to use at front end for the reporting in this project.
Any help / idea / guideness / advise in this regard will be highly apprecitaed.
Regards / Sulman
Sulman,
I'm sorry, I'm not understanding your question very well. Are you asking for more information on the best way to do reporting out of a SQL Server database?
Thanks, MJ|||
One way of doing reporting would be using Reporting Services. Here's a bunch of tutorials here.
http://msdn2.microsoft.com/en-us/library/ms170246.aspx
Thanks< MJ
newbie - Most Recent Records from multiple tables
Hi,
I'm trying to create a view or TSQL statement to return in one recordset...
a) the most recent record of a PK in table1 [foodRecipes]
b) the most recent record (if exists) of FK from table 1 with the PK from table2
Goal: Each recipe can have many versions, and each version can have many historical attempts at making cookies...
example:
table 1: foodRecipes (PK = foodGroup + recipeName + recipeDateModified)
foodGroup [nvarchar (50)]
recipeName [nvarchar (50]
recipeDateModified [datetime]
cupsOfSugar [float]
sampleData:
cookies, peanutButter, 3/3/2007, 1.5
cookies, peanutButter, 3/4/2007, 2.0
cookies, sugar, 3/3/2007, 5.0
table 2: foodRecipeHistory (PK = foodGroup + recipeName + recipeDateModified + historyDateModified)
foodGroup [nvarchar (50)] ...FK from table1
recipeName [nvarchar (50] ...FK from table1
recipeDateModified [datetime] ...FK from table1
historyDateModified [datetime]
cupsOfSugarHistory [float]
sampleData:
cookies, peanutButter, 3/3/2007, 3/3/2007 10:15:00 AM, 1.5
cookies, peanutButter, 3/4/2007, 3/4/2007 10:20:00 AM, 2.0
cookies, peanutButter, 3/4/2007, 3/4/2007 10:21:00 AM, 2.2
What I want: the view or TSQL should provide the most recent unique recipes data + the most recent history (if exists, otherwise NULL)
SELECT * FROM myRecipies
sample Resultset:
foodGroup, recipeName, recipeDateModified, cupsOfSugar, historyDateModified, cupsOfSugarHistory
cookies, peanutButter, 3/4/2007, 2.0, 2.2
cookies, sugar, 3/3/2007, 5.0, <NULL>
What I've got now:
1. TSQL that gives me back the most recent recipes (No History yet)
SELECT foodGroup, recipeName, recipeDateModified, cupsOfSugar, CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate
FROM dbo.foodRecipes oher
WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN
(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))
FROM dbo.foodRecipes
WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))
...and this works great, I get back each unique recipe from table #1, the most recent...
anyone good at this?
thanks in advance,
bsierad
You must have a sweet tooth if your only ingredient is CupsOfSugar.... ;)
Anyway, try the query below to see if this is what you're after.
Chris
SELECT foodGroup,
recipeName,
recipeDateModified,
cupsOfSugar,
CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate,
(SELECT TOP 1 frh.cupsOfSugarHistory
FROM dbo.foodRecipeHistory frh
WHERE frh.foodGroup = oher.foodGroup
AND frh.recipeName = oher.recipeName
AND frh.recipeDateModified = oher.recipeDateModified
ORDER BY frh.historyDateModified DESC) AS cupsOfSugarHistory
FROM dbo.foodRecipes oher
WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN
|||(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))
FROM dbo.foodRecipes
WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))
Thanks!
Works great...and I can soak this in and apply it in other areas...
My real fields don't taste this good...machineGasFlow sounds pretty boring...
Can't thank you enough,
bsierad
Newbie - Layout Toolbar Disabled
This has got to be one of those "doh" things: how does one enable the
Layout Toolbar? Thanks!Hi,
From your descriptions, I understood you would like know how to show
"Layout Toolbar" in the Report Designer. If I have misunderstood your
concern, please feel free to point it out.
You may check View -> Toolbars, make sure Layout is selected.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
I've selected Layout in the Toolbar dropdown, but all of the items are
disabled, regardless of the View.|||Hi,
The items will be enabled when some items in the report is selected. For
example, when we select only one item in the Report, 'Size to Grid' will be
enabled; when we select two items in the Report, almost all items in the
Layout Toolbar will be enabled.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
The title seems to be the only item in the report that can be selected. I
want the entire report to be centered horizontally on the page. Is there a
way to do this?
Thanks.
"Michael Cheng [MSFT]" wrote:
> Hi,
> The items will be enabled when some items in the report is selected. For
> example, when we select only one item in the Report, 'Size to Grid' will be
> enabled; when we select two items in the Report, almost all items in the
> Layout Toolbar will be enabled.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi,
Which object in your report you want to be centered horizontally? It won't
be a easy job to do this unless you select the object one by one and center
horizontally them one by one.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Layout Toolbar? Thanks!Hi,
From your descriptions, I understood you would like know how to show
"Layout Toolbar" in the Report Designer. If I have misunderstood your
concern, please feel free to point it out.
You may check View -> Toolbars, make sure Layout is selected.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
I've selected Layout in the Toolbar dropdown, but all of the items are
disabled, regardless of the View.|||Hi,
The items will be enabled when some items in the report is selected. For
example, when we select only one item in the Report, 'Size to Grid' will be
enabled; when we select two items in the Report, almost all items in the
Layout Toolbar will be enabled.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
The title seems to be the only item in the report that can be selected. I
want the entire report to be centered horizontally on the page. Is there a
way to do this?
Thanks.
"Michael Cheng [MSFT]" wrote:
> Hi,
> The items will be enabled when some items in the report is selected. For
> example, when we select only one item in the Report, 'Size to Grid' will be
> enabled; when we select two items in the Report, almost all items in the
> Layout Toolbar will be enabled.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi,
Which object in your report you want to be centered horizontally? It won't
be a easy job to do this unless you select the object one by one and center
horizontally them one by one.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Newbie - Is this a job for a sproc?
I'm optimizing an Access mdb to run in front of a SS 2005 database. My approach is to move as much of the processing as possible out of Access and in to SQL Server.
I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.
I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?
Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.
Is it possible to do this with a sproc? Is this the right way to use a sproc?Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria,
Where is your data?|||Where is your data?
Thanks for the quick response..
Not sure what you're asking.. The set of data to be looped through is in a view. The set of data for the results to be attached to will also be a view.
Both views are based on tables residing in my database.
Do you need me to post a sample of my data?|||here's more detail on my data
The data to loop through is in a view called ActionItems.
Relevant fields are:
project_id, actionitem_id, actionitem_order, status_id, milestone_id
Projects have about 50-60 action items, each with their unique id. They're in order by actionitem_order. Action items are grouped into milestones - figure 6 items per milestone.
To get the return value, I want to loop through each action item until the first one with a status_id of 3 is reached. When that one is reached, the loop can be stopped and the prior milestone_id is passed on as the result of the function.
I want to attach the result of that function to another view joined on Project_id.|||Create this as a stored procedure that not only encorporates your views as nested subqueries, but which also find the first one with a value of 3 and returns it as a resultset.
Basically, if you are looping through records, you are doing something wrong. Its time to join the big boys and learn set-based processing.|||Create this as a stored procedure that not only encorporates your views as nested subqueries, but which also find the first one with a value of 3 and returns it as a resultset.
Basically, if you are looping through records, you are doing something wrong. Its time to join the big boys and learn set-based processing.
Ok, cool. I'll dump the "loop-through" concept.. I have it as a series of SELECT queries in Access anyway, so moving that into a sproc is more of a direct migration anyway..
Ok - you mention nested subqueries.. Do you have an example of a sproc that does this? One approach I've found in trying to figure this out myself is that of using temporary tables after each SELECT statement.. For some reason, this sounds like it would require more overhead than just chaining the SELECT statements together. Problem is, I don't know how to nest the statements together.. I'll do more research, but if you've got an example, I'd appreciate it..
Thanks!|||select * from (select * from (select * from SomeTable) Nest2) Nest1|||select * from (select * from (select * from SomeTable) Nest2) Nest1
That's what I was thinking, but I couldn't find any examples to confirm..
I'll work with it and post the followup questions that are sure to come..
Thanks!|||Ok, really quick on the followup, eh?
This is probably more of a SQL question, and maybe it should probably be obvious to me, but anyway..
In Access, my top-level query would look something like...
SELECT field1, field2, field3 FROM table INNER JOIN table ON query1.field1 = table.field1 GROUP BY query1.field2
So in the sproc I go:
SELECT field1, field2, field3 FROM table INNER JOIN table ON (Long SELECT Statement that makes up query1).field1 = table.field1 GROUP BY (Long SELECT Statement that makes up query1).field2
Obviously the issue is the fact that I have two very long SELECT statements to deal with (on after the JOIN, one after the GROUP BY). Not a big deal with a simple 2 level combination of queries, but my actual progression is 5 levels progression with 7 different queries, some of which have criteria and multiple joins.
That would make this a pretty large sproc with lots of syntax to verify and (in my case) probably get wrong.
If that's what it takes, no problem.. I just want to confirm that before I go on..|||No. You are heading down the wrong path here. You join subqueries in your statement as virtual tables with their own aliases. You should not be using them as criteria.
Plus, I highly doubt that your task requires five levels of subqueries.
Read the sections on subqueries in Books Online. Then review your task again. Don't try to build the entire sql statement at once, but instead write code that performs the lowest level subqueries efficiently and then build up from there.
I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.
I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?
Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.
Is it possible to do this with a sproc? Is this the right way to use a sproc?Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria,
Where is your data?|||Where is your data?
Thanks for the quick response..
Not sure what you're asking.. The set of data to be looped through is in a view. The set of data for the results to be attached to will also be a view.
Both views are based on tables residing in my database.
Do you need me to post a sample of my data?|||here's more detail on my data
The data to loop through is in a view called ActionItems.
Relevant fields are:
project_id, actionitem_id, actionitem_order, status_id, milestone_id
Projects have about 50-60 action items, each with their unique id. They're in order by actionitem_order. Action items are grouped into milestones - figure 6 items per milestone.
To get the return value, I want to loop through each action item until the first one with a status_id of 3 is reached. When that one is reached, the loop can be stopped and the prior milestone_id is passed on as the result of the function.
I want to attach the result of that function to another view joined on Project_id.|||Create this as a stored procedure that not only encorporates your views as nested subqueries, but which also find the first one with a value of 3 and returns it as a resultset.
Basically, if you are looping through records, you are doing something wrong. Its time to join the big boys and learn set-based processing.|||Create this as a stored procedure that not only encorporates your views as nested subqueries, but which also find the first one with a value of 3 and returns it as a resultset.
Basically, if you are looping through records, you are doing something wrong. Its time to join the big boys and learn set-based processing.
Ok, cool. I'll dump the "loop-through" concept.. I have it as a series of SELECT queries in Access anyway, so moving that into a sproc is more of a direct migration anyway..
Ok - you mention nested subqueries.. Do you have an example of a sproc that does this? One approach I've found in trying to figure this out myself is that of using temporary tables after each SELECT statement.. For some reason, this sounds like it would require more overhead than just chaining the SELECT statements together. Problem is, I don't know how to nest the statements together.. I'll do more research, but if you've got an example, I'd appreciate it..
Thanks!|||select * from (select * from (select * from SomeTable) Nest2) Nest1|||select * from (select * from (select * from SomeTable) Nest2) Nest1
That's what I was thinking, but I couldn't find any examples to confirm..
I'll work with it and post the followup questions that are sure to come..
Thanks!|||Ok, really quick on the followup, eh?
This is probably more of a SQL question, and maybe it should probably be obvious to me, but anyway..
In Access, my top-level query would look something like...
SELECT field1, field2, field3 FROM table INNER JOIN table ON query1.field1 = table.field1 GROUP BY query1.field2
So in the sproc I go:
SELECT field1, field2, field3 FROM table INNER JOIN table ON (Long SELECT Statement that makes up query1).field1 = table.field1 GROUP BY (Long SELECT Statement that makes up query1).field2
Obviously the issue is the fact that I have two very long SELECT statements to deal with (on after the JOIN, one after the GROUP BY). Not a big deal with a simple 2 level combination of queries, but my actual progression is 5 levels progression with 7 different queries, some of which have criteria and multiple joins.
That would make this a pretty large sproc with lots of syntax to verify and (in my case) probably get wrong.
If that's what it takes, no problem.. I just want to confirm that before I go on..|||No. You are heading down the wrong path here. You join subqueries in your statement as virtual tables with their own aliases. You should not be using them as criteria.
Plus, I highly doubt that your task requires five levels of subqueries.
Read the sections on subqueries in Books Online. Then review your task again. Don't try to build the entire sql statement at once, but instead write code that performs the lowest level subqueries efficiently and then build up from there.
Newbie - How to update multiple rows with select statment
Newbie to the SQL Server.
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
PamI am disappointed that all the experts here have no time for my basic question ...|||you need to write a query like
update a
set a.col = b.col
from yourtbl a, (your select query) b
where a.primarykey = b.primarykey|||Thanks for the reply.
Thats too basic. Are you suggesting me to create new table and then associate the values as you quoted above ??|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax
in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem
here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0|||I'd suggest:DROP TABLE Pam24
GO
CREATE TABLE Pam24 (
Pam24id INT IDENTITY
CONSTRAINT XPKPam24
PRIMARY KEY (Pam24id)
, thingie VARCHAR(50) NULL
)
INSERT INTO Pam24 (thingie)
SELECT '123 Main Street' UNION ALL
SELECT NULL UNION ALL
SELECT '456Any Road' UNION ALL
SELECT '' UNION ALL
SELECT '789 My Place'
UPDATE Pam24
SET thingie = Left(thingie, PatIndex('%[^0-9]%', thingie) - 1)
WHERE thingie LIKE '[0-9]%'
SELECT *
FROM Pam24Note that the two patterns are different, in fact exact opposites. I dislike having two patterns, but it was better than any alternative I could think of on short notice.
-PatP|||pat, nice try, but if the string does not start with a number, the original spec (yes, i realize it's buried inside some non-functional sql) required that you reset the entire value to 0
:)|||actually, now that i look at it more closely, the 0 was actually the length parameter of the substring function, so i think maybe it's supposed to reset all alpha-only strings to empty strings|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax
in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem
here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0
Thanks r937. It does answers my question.
Cheers
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
PamI am disappointed that all the experts here have no time for my basic question ...|||you need to write a query like
update a
set a.col = b.col
from yourtbl a, (your select query) b
where a.primarykey = b.primarykey|||Thanks for the reply.
Thats too basic. Are you suggesting me to create new table and then associate the values as you quoted above ??|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax
in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem
here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0|||I'd suggest:DROP TABLE Pam24
GO
CREATE TABLE Pam24 (
Pam24id INT IDENTITY
CONSTRAINT XPKPam24
PRIMARY KEY (Pam24id)
, thingie VARCHAR(50) NULL
)
INSERT INTO Pam24 (thingie)
SELECT '123 Main Street' UNION ALL
SELECT NULL UNION ALL
SELECT '456Any Road' UNION ALL
SELECT '' UNION ALL
SELECT '789 My Place'
UPDATE Pam24
SET thingie = Left(thingie, PatIndex('%[^0-9]%', thingie) - 1)
WHERE thingie LIKE '[0-9]%'
SELECT *
FROM Pam24Note that the two patterns are different, in fact exact opposites. I dislike having two patterns, but it was better than any alternative I could think of on short notice.
-PatP|||pat, nice try, but if the string does not start with a number, the original spec (yes, i realize it's buried inside some non-functional sql) required that you reset the entire value to 0
:)|||actually, now that i look at it more closely, the 0 was actually the length parameter of the substring function, so i think maybe it's supposed to reset all alpha-only strings to empty strings|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax
in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem
here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0
Thanks r937. It does answers my question.
Cheers
Newbie - Help with Constraints
Dear all,
I am new to SQL Server (using the express edition.) I need to set up a
contraint that checks values from one column in three tables against
one column's value in a pending insertion. If the value that is
attempting to be inserted equals a value from either of the three
tables, the insertion should fail (throw a SQL exception').
Essentially this constraint should operate just like a "no duplicates"
constraint would operate if all the data was in a single column in a
single table and a client attempted to add a duplicate value.
Any advice is appreciated.
Thanks,
JohnnyJohnny,
You could embed the INSERT in a stored procedure and perform the table
checks (SELECTs) prior to the INSERT. Or you could create an INSERT trigger
that would ROLLBACK if the value existed in one of the tables. The former
would be better for performance.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148501225.921395.254460@.y43g2000cwc.googlegroups.com...
> Dear all,
> I am new to SQL Server (using the express edition.) I need to set up a
> contraint that checks values from one column in three tables against
> one column's value in a pending insertion. If the value that is
> attempting to be inserted equals a value from either of the three
> tables, the insertion should fail (throw a SQL exception').
> Essentially this constraint should operate just like a "no duplicates"
> constraint would operate if all the data was in a single column in a
> single table and a client attempted to add a duplicate value.
> Any advice is appreciated.
> Thanks,
> Johnny
>|||If I put this in a stored procedure, how could I ensure that the stored
procedure is called every time an insert statement is executed against
3 different tables? Is this a situation where a trigger is the only
choice?
Thanks,
Johnny|||Johnny,
Put the INSERT statement within the stored procedure and call the proc to
perform the INSERT.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148527276.838343.179960@.i39g2000cwa.googlegroups.com...
> If I put this in a stored procedure, how could I ensure that the stored
> procedure is called every time an insert statement is executed against
> 3 different tables? Is this a situation where a trigger is the only
> choice?
> Thanks,
> Johnny
>
I am new to SQL Server (using the express edition.) I need to set up a
contraint that checks values from one column in three tables against
one column's value in a pending insertion. If the value that is
attempting to be inserted equals a value from either of the three
tables, the insertion should fail (throw a SQL exception').
Essentially this constraint should operate just like a "no duplicates"
constraint would operate if all the data was in a single column in a
single table and a client attempted to add a duplicate value.
Any advice is appreciated.
Thanks,
JohnnyJohnny,
You could embed the INSERT in a stored procedure and perform the table
checks (SELECTs) prior to the INSERT. Or you could create an INSERT trigger
that would ROLLBACK if the value existed in one of the tables. The former
would be better for performance.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148501225.921395.254460@.y43g2000cwc.googlegroups.com...
> Dear all,
> I am new to SQL Server (using the express edition.) I need to set up a
> contraint that checks values from one column in three tables against
> one column's value in a pending insertion. If the value that is
> attempting to be inserted equals a value from either of the three
> tables, the insertion should fail (throw a SQL exception').
> Essentially this constraint should operate just like a "no duplicates"
> constraint would operate if all the data was in a single column in a
> single table and a client attempted to add a duplicate value.
> Any advice is appreciated.
> Thanks,
> Johnny
>|||If I put this in a stored procedure, how could I ensure that the stored
procedure is called every time an insert statement is executed against
3 different tables? Is this a situation where a trigger is the only
choice?
Thanks,
Johnny|||Johnny,
Put the INSERT statement within the stored procedure and call the proc to
perform the INSERT.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148527276.838343.179960@.i39g2000cwa.googlegroups.com...
> If I put this in a stored procedure, how could I ensure that the stored
> procedure is called every time an insert statement is executed against
> 3 different tables? Is this a situation where a trigger is the only
> choice?
> Thanks,
> Johnny
>
Newbie - Help with Constraints
Dear all,
I am new to SQL Server (using the express edition.) I need to set up a
contraint that checks values from one column in three tables against
one column's value in a pending insertion. If the value that is
attempting to be inserted equals a value from either of the three
tables, the insertion should fail (throw a SQL exception').
Essentially this constraint should operate just like a "no duplicates"
constraint would operate if all the data was in a single column in a
single table and a client attempted to add a duplicate value.
Any advice is appreciated.
Thanks,
JohnnyJohnny,
You could embed the INSERT in a stored procedure and perform the table
checks (SELECTs) prior to the INSERT. Or you could create an INSERT trigger
that would ROLLBACK if the value existed in one of the tables. The former
would be better for performance.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148501225.921395.254460@.y43g2000cwc.googlegroups.com...
> Dear all,
> I am new to SQL Server (using the express edition.) I need to set up a
> contraint that checks values from one column in three tables against
> one column's value in a pending insertion. If the value that is
> attempting to be inserted equals a value from either of the three
> tables, the insertion should fail (throw a SQL exception').
> Essentially this constraint should operate just like a "no duplicates"
> constraint would operate if all the data was in a single column in a
> single table and a client attempted to add a duplicate value.
> Any advice is appreciated.
> Thanks,
> Johnny
>|||If I put this in a stored procedure, how could I ensure that the stored
procedure is called every time an insert statement is executed against
3 different tables? Is this a situation where a trigger is the only
choice?
Thanks,
Johnny|||Johnny,
Put the INSERT statement within the stored procedure and call the proc to
perform the INSERT.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148527276.838343.179960@.i39g2000cwa.googlegroups.com...
> If I put this in a stored procedure, how could I ensure that the stored
> procedure is called every time an insert statement is executed against
> 3 different tables? Is this a situation where a trigger is the only
> choice?
> Thanks,
> Johnny
>
I am new to SQL Server (using the express edition.) I need to set up a
contraint that checks values from one column in three tables against
one column's value in a pending insertion. If the value that is
attempting to be inserted equals a value from either of the three
tables, the insertion should fail (throw a SQL exception').
Essentially this constraint should operate just like a "no duplicates"
constraint would operate if all the data was in a single column in a
single table and a client attempted to add a duplicate value.
Any advice is appreciated.
Thanks,
JohnnyJohnny,
You could embed the INSERT in a stored procedure and perform the table
checks (SELECTs) prior to the INSERT. Or you could create an INSERT trigger
that would ROLLBACK if the value existed in one of the tables. The former
would be better for performance.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148501225.921395.254460@.y43g2000cwc.googlegroups.com...
> Dear all,
> I am new to SQL Server (using the express edition.) I need to set up a
> contraint that checks values from one column in three tables against
> one column's value in a pending insertion. If the value that is
> attempting to be inserted equals a value from either of the three
> tables, the insertion should fail (throw a SQL exception').
> Essentially this constraint should operate just like a "no duplicates"
> constraint would operate if all the data was in a single column in a
> single table and a client attempted to add a duplicate value.
> Any advice is appreciated.
> Thanks,
> Johnny
>|||If I put this in a stored procedure, how could I ensure that the stored
procedure is called every time an insert statement is executed against
3 different tables? Is this a situation where a trigger is the only
choice?
Thanks,
Johnny|||Johnny,
Put the INSERT statement within the stored procedure and call the proc to
perform the INSERT.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148527276.838343.179960@.i39g2000cwa.googlegroups.com...
> If I put this in a stored procedure, how could I ensure that the stored
> procedure is called every time an insert statement is executed against
> 3 different tables? Is this a situation where a trigger is the only
> choice?
> Thanks,
> Johnny
>
Newbie - Help required with Query (sample tables/data included)
** Also posted to: comp.databases.ms-sqlserver
Hi all,
I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
column that refers
to a 'ZoneData' row. Table definitions and sample data are:
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
Running the Query:
SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
Displays the data:
Id1 Id2 ZoneId IsDefault
100 1 3 1
100 1 2 0
100 1 4 0
101 2 5 0
101 2 1 0
101 2 3 1
102 3 5 0
103 4 3 1
104 5 2 0
104 5 5 0
For each combination of 'Id1' and 'Id2' there may be 0 or more rows
with different 'ZoneId' values.
The Problem: I would like to create a query that could return a row for
every
'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
found in
database' - not as a result of some sort order) row where 'IsDefault ==
0'
Using the above data, the output would be:
100 1 2 0 < ZoneId '2' id 1st row where 'IsDefault
# 1'
101 2 5 0
102 3 5 0
104 5 2 0
* There is no row returned for Id1=103 Id2=4 as there is no row where
'IsDefault = 0'
Has anyone got an idea on how I might do this? I'm using SQL Server
2005
cheers,
davedmm
Thanks for DDL.
I have added rowid column as IDENTITY property to ZoneUser table
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](rowid INT NOT NULL IDENTITY(1,1),
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
CREATE VIEW myview
AS
SELECT ZoneUser.*, ZoneData.IsDefault
FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
WHERE IsDefault=0
SELECT * FROM
(
SELECT *,(SELECT COUNT(*) FROM myview v
WHERE v.Id1=myview.Id1 AND v.rowId<=myview.rowId ) rnk
FROM myview
) AS Der WHERE rnk=1
ORDER BY id1
DROP VIEW myview
DROP TABLE ZoneData,ZoneUser
"dmm" <stope19@.optusnet.com.au> wrote in message
news:1141619306.147227.283440@.v46g2000cwv.googlegroups.com...
> ** Also posted to: comp.databases.ms-sqlserver
> Hi all,
> I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
> column that refers
> to a 'ZoneData' row. Table definitions and sample data are:
>
> CREATE TABLE [dbo].[ZoneData](
> [ZoneId] [int] NOT NULL,
> [ZoneName] [nchar](10) NOT NULL,
> [IsDefault] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[ZoneUser](
> [Id1] [int] NOT NULL,
> [Id2] [int] NOT NULL,
> [ZoneId] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
> GO
>
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
> GO
>
> Running the Query:
>
> SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
> ON ZoneData.ZoneId = ZoneUser.ZoneId
>
> Displays the data:
>
> Id1 Id2 ZoneId IsDefault
> 100 1 3 1
> 100 1 2 0
> 100 1 4 0
> 101 2 5 0
> 101 2 1 0
> 101 2 3 1
> 102 3 5 0
> 103 4 3 1
> 104 5 2 0
> 104 5 5 0
>
> For each combination of 'Id1' and 'Id2' there may be 0 or more rows
> with different 'ZoneId' values.
>
> The Problem: I would like to create a query that could return a row for
> every
> 'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
> found in
> database' - not as a result of some sort order) row where 'IsDefault ==
> 0'
> Using the above data, the output would be:
>
> 100 1 2 0 < ZoneId '2' id 1st row where 'IsDefault
> # 1'
> 101 2 5 0
> 102 3 5 0
> 104 5 2 0
>
> * There is no row returned for Id1=103 Id2=4 as there is no row where
> 'IsDefault = 0'
>
> Has anyone got an idea on how I might do this? I'm using SQL Server
> 2005
>
> cheers,
> dave
>|||Thanks for the help Uri. Much appreciated.
cheers,
dave
Hi all,
I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
column that refers
to a 'ZoneData' row. Table definitions and sample data are:
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
Running the Query:
SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
Displays the data:
Id1 Id2 ZoneId IsDefault
100 1 3 1
100 1 2 0
100 1 4 0
101 2 5 0
101 2 1 0
101 2 3 1
102 3 5 0
103 4 3 1
104 5 2 0
104 5 5 0
For each combination of 'Id1' and 'Id2' there may be 0 or more rows
with different 'ZoneId' values.
The Problem: I would like to create a query that could return a row for
every
'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
found in
database' - not as a result of some sort order) row where 'IsDefault ==
0'
Using the above data, the output would be:
100 1 2 0 < ZoneId '2' id 1st row where 'IsDefault
# 1'
101 2 5 0
102 3 5 0
104 5 2 0
* There is no row returned for Id1=103 Id2=4 as there is no row where
'IsDefault = 0'
Has anyone got an idea on how I might do this? I'm using SQL Server
2005
cheers,
davedmm
Thanks for DDL.
I have added rowid column as IDENTITY property to ZoneUser table
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](rowid INT NOT NULL IDENTITY(1,1),
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
CREATE VIEW myview
AS
SELECT ZoneUser.*, ZoneData.IsDefault
FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
WHERE IsDefault=0
SELECT * FROM
(
SELECT *,(SELECT COUNT(*) FROM myview v
WHERE v.Id1=myview.Id1 AND v.rowId<=myview.rowId ) rnk
FROM myview
) AS Der WHERE rnk=1
ORDER BY id1
DROP VIEW myview
DROP TABLE ZoneData,ZoneUser
"dmm" <stope19@.optusnet.com.au> wrote in message
news:1141619306.147227.283440@.v46g2000cwv.googlegroups.com...
> ** Also posted to: comp.databases.ms-sqlserver
> Hi all,
> I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
> column that refers
> to a 'ZoneData' row. Table definitions and sample data are:
>
> CREATE TABLE [dbo].[ZoneData](
> [ZoneId] [int] NOT NULL,
> [ZoneName] [nchar](10) NOT NULL,
> [IsDefault] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[ZoneUser](
> [Id1] [int] NOT NULL,
> [Id2] [int] NOT NULL,
> [ZoneId] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
> GO
>
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
> GO
>
> Running the Query:
>
> SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
> ON ZoneData.ZoneId = ZoneUser.ZoneId
>
> Displays the data:
>
> Id1 Id2 ZoneId IsDefault
> 100 1 3 1
> 100 1 2 0
> 100 1 4 0
> 101 2 5 0
> 101 2 1 0
> 101 2 3 1
> 102 3 5 0
> 103 4 3 1
> 104 5 2 0
> 104 5 5 0
>
> For each combination of 'Id1' and 'Id2' there may be 0 or more rows
> with different 'ZoneId' values.
>
> The Problem: I would like to create a query that could return a row for
> every
> 'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
> found in
> database' - not as a result of some sort order) row where 'IsDefault ==
> 0'
> Using the above data, the output would be:
>
> 100 1 2 0 < ZoneId '2' id 1st row where 'IsDefault
> # 1'
> 101 2 5 0
> 102 3 5 0
> 104 5 2 0
>
> * There is no row returned for Id1=103 Id2=4 as there is no row where
> 'IsDefault = 0'
>
> Has anyone got an idea on how I might do this? I'm using SQL Server
> 2005
>
> cheers,
> dave
>|||Thanks for the help Uri. Much appreciated.
cheers,
dave
Newbie - Help required with Query (sample tables/data included)
Hi all,
I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
column that refers
to a 'ZoneData' row. Table definitions and sample data are:
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
Running the Query:
SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
Displays the data:
Id1Id2ZoneIdIsDefault
100131
100120
100140
101250
101210
101231
102350
103431
104520
104550
For each combination of 'Id1' and 'Id2' there may be 0 or more rows
with different 'ZoneId' values.
The Problem: I would like to create a query that could return a row for
every
'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
found in
database' - not as a result of some sort order) row where 'IsDefault ==
0'
Using the above data, the output would be:
100120 < ZoneId '2' id 1st row where 'IsDefault # 1'
101250
102350
104520
* There is no row returned for Id1=103Id2=4 as there is no row where
'IsDefault = 0'
Has anyone got an idea on how I might do this? I'm using SQL Server
2005
cheers,
daveHi, Dave
Your DDL has no primary keys, foreign keys or unique constraints.
This is a serious mistake, because:
1. It allows bad data to be entered in the tables
2. It prevents us from understanding the meaning of your tables, so we
cannot provide a good answer without them.
I assume the following constraints:
ALTER TABLE ZoneData ADD PRIMARY KEY (ZoneId), UNIQUE (ZoneName)
ALTER TABLE ZoneUser ADD UNIQUE (ZoneId, Id1)
ALTER TABLE ZoneUser ADD FOREIGN KEY (ZoneId) REFERENCES ZoneData
There is no such thing as "first found in database". By definition,
tables are unordered sets of rows. We have to use a sort criteria to
specify which is the first row.
First time I read your message, I believed you wanted something like
this:
SELECT U.*, D.IsDefault
FROM ZoneData D
INNER JOIN ZoneUser U ON D.ZoneId = U.ZoneId
INNER JOIN (
SELECT ZoneId, MIN(Id1) as MinOfId1
FROM ZoneUser GROUP BY ZoneId
) X ON U.ZoneId=X.ZoneId AND U.Id1=MinOfId1
WHERE D.IsDefault=0
The above query returns the following results:
Id1 Id2 ZoneId IsDefault
---- ---- ---- ---
101 2 1 0
100 1 2 0
100 1 4 0
101 2 5 0
(4 row(s) affected)
Are you sure you don't want these results instead of what you wrote ?
If you are sure, I'm going to try writing another query that will
return what you wrote (but it doesn't have a lot of sense). Maybe you
will tell us what Id1 and Id2 mean, so we can better understand what
you want to do.
Razvan|||Hi Razan,
Thanks for your reply. Your comment re 'database order' not existing
has me thinking perhaps my concept of what I want to do may be wrong. I
will consider your reply in detail to see where I might have 'got
lost'. Thanks you for taking the time to explain this.
cheers,
dave|||Do not use assembly language style bit flags in a high level language
like SQL. Use a sequence number for zones, if the zone-id will not do
the job. All relationships have to be expressed as values in columns
in tables. You never refer to the physical storage in a quiery.
CREATE TABLE Zones
(zone_id INTEGER NOT NULL PRIMARY KEY
zone_name CHAR(10) NOT NULL,
zone_rank INTEGER DEFAULT 0 NOT NULL
CHECK (zone_rank > 0),
UNIQUE (zone_id, zone_rank))
);
CREATE TABLE ZoneUsers
(user_id_1 INTEGER NOT NULL,
user_id_2 INTEGER NOT NULL,
PRIMARY KEY (user_id_1, user_id_2),
zone_id INTEGER NOT NULL
REFERENCES Zones(zone_id)
);
SELECT U.user_id_1, U.user_id_2, U.zone_id, MIN(Z.zone_rank)
FROM ZoneUsers AS U, Zones AS Z
WHERE Z.zone_id = U.zone_id
GROUP BY U.user_id_1, U.user_id_2, U.zone_id;|||Thanks for the comments. Looks like I have some work to do!
cheers,
dave|||After 20+ years of SQL, I tell people it takes one year of full-time
programming with college -level education to be an SQL programmer.
This is cheap; it takes 6 yers to become a Union Journeyman Carpenter
in New York State.
A bad ptrogrammer can kill or maim a lot more people than a bad
carpenter.|||>A bad ptrogrammer can kill or maim a lot more people than a bad
carpenter.
Oh?? I'll bite. How does a programmer kill or maim a lot of people?|||"Doug" <drmiller100@.hotmail.com> wrote in message
news:1141706342.190761.230170@.j33g2000cwa.googlegr oups.com...
> >A bad ptrogrammer can kill or maim a lot more people than a bad
> carpenter.
> Oh?? I'll bite. How does a programmer kill or maim a lot of people?
One of several ways.
There's a recent case in Panama where a radiological machine used to deliver
doses of radiation to kill cancer was improperly used and killed a number of
patients. Besides the techs being indicted there was at least talk of
bringing the programmers to court since they wrote the software that
permitted the misuse of the machine w/o proper feedback.
http://www.findarticles.com/p/artic...3/ai_ziff120920
Or imagine the case of the Shuttle Software (which is among the most
"perfect" ever written) where a condition was found (preflight fortunately)
that locked up the shuttle arm. Evidently the programmer made a simply
mistake and assumed that its rotational functionality extended from 0 to 360
degrees rather than 1-360 or 0-359. A search found a couple of other places
where a similar error (i.e. overrunning by 1) was in the code.
In the case of the arm, they could have jestisoned it. In the case of a
botched landing, a similar error could have crashed the shuttle.
It's not hard to imagine extended such errors to avionics software or
software controlling a nuclear reactor, etc.
newbie - have backup file and trn file - how do I get it up and going?
Hello there,
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.Check out RESTORE DATABASE and RESTORE LOG in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
Hello there,
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.|||It is probably easiest for you to read about the below commands in Books Online
RESTORE HEADERONLY
RESTORE FILELISTONLY
Use above two to investigate what is on those backup files. Then do the restore operations using
RESTORE DATABASE
RESTORE LOG
You probably have to use the MOVE option for above commands, guided but he output from RESTORE
FILELISTONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
> Hello there,
> I have a project to look into where I need to look into an MSSQL database.
> I have been sent a file that contains a BAK file and a TRN file. I am told this is a dump of the
> MSSQL database.
> I have installed the evaluation version of SQL Server 2000 database onto my Windows 2000 PC.
> My first question is, am I going about it the right way, and what is the first thing I want to do
> with regard to actually opening the database.
> I looked into the Enterprise manager about restoring a database, but it did not seem to let me
> choose a file location to restore from.
> Any guidance or input would be appreciated.
> Kind regards
> Dave.
>
>
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.Check out RESTORE DATABASE and RESTORE LOG in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
Hello there,
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.|||It is probably easiest for you to read about the below commands in Books Online
RESTORE HEADERONLY
RESTORE FILELISTONLY
Use above two to investigate what is on those backup files. Then do the restore operations using
RESTORE DATABASE
RESTORE LOG
You probably have to use the MOVE option for above commands, guided but he output from RESTORE
FILELISTONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
> Hello there,
> I have a project to look into where I need to look into an MSSQL database.
> I have been sent a file that contains a BAK file and a TRN file. I am told this is a dump of the
> MSSQL database.
> I have installed the evaluation version of SQL Server 2000 database onto my Windows 2000 PC.
> My first question is, am I going about it the right way, and what is the first thing I want to do
> with regard to actually opening the database.
> I looked into the Enterprise manager about restoring a database, but it did not seem to let me
> choose a file location to restore from.
> Any guidance or input would be appreciated.
> Kind regards
> Dave.
>
>
newbie - have backup file and trn file - how do I get it up and going?
Hello there,
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.Check out RESTORE DATABASE and RESTORE LOG in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
Hello there,
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.|||It is probably easiest for you to read about the below commands in Books Onl
ine
RESTORE HEADERONLY
RESTORE FILELISTONLY
Use above two to investigate what is on those backup files. Then do the rest
ore operations using
RESTORE DATABASE
RESTORE LOG
You probably have to use the MOVE option for above commands, guided but he o
utput from RESTORE
FILELISTONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
> Hello there,
> I have a project to look into where I need to look into an MSSQL database.
> I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the
> MSSQL database.
> I have installed the evaluation version of SQL Server 2000 database onto m
y Windows 2000 PC.
> My first question is, am I going about it the right way, and what is the f
irst thing I want to do
> with regard to actually opening the database.
> I looked into the Enterprise manager about restoring a database, but it di
d not seem to let me
> choose a file location to restore from.
> Any guidance or input would be appreciated.
> Kind regards
> Dave.
>
>
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.Check out RESTORE DATABASE and RESTORE LOG in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
Hello there,
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.|||It is probably easiest for you to read about the below commands in Books Onl
ine
RESTORE HEADERONLY
RESTORE FILELISTONLY
Use above two to investigate what is on those backup files. Then do the rest
ore operations using
RESTORE DATABASE
RESTORE LOG
You probably have to use the MOVE option for above commands, guided but he o
utput from RESTORE
FILELISTONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
> Hello there,
> I have a project to look into where I need to look into an MSSQL database.
> I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the
> MSSQL database.
> I have installed the evaluation version of SQL Server 2000 database onto m
y Windows 2000 PC.
> My first question is, am I going about it the right way, and what is the f
irst thing I want to do
> with regard to actually opening the database.
> I looked into the Enterprise manager about restoring a database, but it di
d not seem to let me
> choose a file location to restore from.
> Any guidance or input would be appreciated.
> Kind regards
> Dave.
>
>
Newbie - Good Doumentation Needed
Our current environment consists of a SQL 2000 server and report server with reporting services. I hope I didn't screw that up but I guess the important thing is, I'm sure it's SQL 2000. I have Visual Studio 2005. I need to edit existing reports on the report server website and create new reports with what I have. I am familiar with writing reports in a report viewer and I am familiar with SQL queries. I need some good documentation to get me started, and I'm having trouble finding it related to SQL 2000/VS2005. I dont have the foggiest of ideas of how to get VS2005 to see my exisitng reports and to get them to hook up with each other. Any advice to get me out the door would be much appreciated. Thanks!!
you can't creat Bi project in VS2005 as long as you don't install the MS Reporting service 2005 but you can create the BI project in VS2003 and just refere to the deployed reports on the reporting server
http://<machine name>/reportserver and use them in your VS2005 web or windows project by report viewer
|||
Since upgrading SQL and SQL reporting services to 2005 is out of the question at this point, does this mean I either need to get Visual Studio 2003 or I'm dead in the water?
Sounds like I'm stuck with my current setup. Correct?
yes you need Visual studio 2003 and create BI project , develop yoour reports then deploy them on the reporting server so you can easily view them in your web or windows application using report viewer control.
newbie - getting data out of AS / cube
We are in the beginning phases of designing a data warehouse solution, using
SSAS 2005 and .Net technologies. I know that we can use BI portals such as
from Proclarity, Panorama, Business Objects, etc. However, we wanted to
create our own UI front end (either Web or Winform).
My question is this -- can someone recommend what (and how) .Net
technologies to use to consume the data from SSAS? e.g. in a traditional
OLTP system, I would use sql queries and ado.net to fill my data table, and
maybe use an OR mapper to hydrate custom business objects. The data table
would then be sent to the UI front end for display / control binding and
handling.
For a data warehouse... what would I do differently? Do I still use ado.net
and maybe MDX / XMLA to query the cube? What data structures do I use to
consume the data? A Dataset with relationships? Would you transform that
data to customized business objects? Do you have any strategies on how to
support drill-down on the front end?
Or, would the middle-tier be very "thin" -- meaning the front-end would just
interface to AS directly and we have smart controls to bind to the raw data?
Any pointers would be greatly appreciated.
Thanks
Ray
p.s. I've been cross posting to the two newsgroups -- could someone tell me
what questions belong to which group?
Reporting Portal for OLAP
Hello Ray,
For a quick start have a look at SQL Server 2005 Reporting Services.
It comes free with SQL server and provides a web base portal that you
can customize using .NET and or Sharepoint. You can create your own
app/ portal using RS web services.
RS2005 Reports support MDX natively so you can use Reporting Services
to deliver standard reports against Analysis Services.
New to SQL server 2005 is Report Builder that provides a great web
based UI for creating ad hoc reports and publishing them back to the
Reporting services portal. Report Builder also supports drill through
to underlying data.
Check out
http://www.microsoft.com/technet/pro.../2005ssrs.mspx
Hope this gets you started.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hi,
Thank you for the response. I have looked a little bit into Reporting
Services... I may be a bit weak on the Sharepoint side of things though --
my concern is that the RS2005/Sharepoint didn't offer sufficient
customization abilities. Some things our Web App may need to do:
- controls for boston matrix, various types of chargs, speedometers
- full user session management, security, roles, etc.
- integration with other data sources (e.g. Weather, newsfeed)
- support for user input -- e.g. in cost/budget/forecasting analysis
- support for "advanced" reports -- our organization is proficient with
Crystal Reports, so we may wish to continue using it over RS2005.
I was concerned that the solution/portal offered by Microsoft (or other
vendors) would not meet our requirements. This is why I think it would make
sense if we built our own custom portal using our own technologies.
Hence, my question about the middle-tier -- What type of data constructs
should I be using?
Thank you,
Ray
<Myles.Matheson@.gmail.com> wrote in message
news:1124414479.649578.162120@.g49g2000cwa.googlegr oups.com...
> Reporting Portal for OLAP
> Hello Ray,
> For a quick start have a look at SQL Server 2005 Reporting Services.
> It comes free with SQL server and provides a web base portal that you
> can customize using .NET and or Sharepoint. You can create your own
> app/ portal using RS web services.
> RS2005 Reports support MDX natively so you can use Reporting Services
> to deliver standard reports against Analysis Services.
> New to SQL server 2005 is Report Builder that provides a great web
> based UI for creating ad hoc reports and publishing them back to the
> Reporting services portal. Report Builder also supports drill through
> to underlying data.
> Check out
> http://www.microsoft.com/technet/pro.../2005ssrs.mspx
> Hope this gets you started.
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
|||I found the best way to exchange data between the UI and business layers was
"processed" XML/A.
That is to say, the business layer implements services that receive user
requests, maps them to MDX query patterns, queries the server using Adomd.net
and specifying an XML/A response type, transforms the XML/A to match the
request (table, indicator, chart) and returns it to the UI layer.
Consider that a standard XML/A response will usually include tons of
unnecessary metadata (in a custom app), so you can substantially reduce the
size of the XML/A message before sending it to the UI layer.
HTH,
Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html
"Ray" wrote:
> Hi,
> Thank you for the response. I have looked a little bit into Reporting
> Services... I may be a bit weak on the Sharepoint side of things though --
> my concern is that the RS2005/Sharepoint didn't offer sufficient
> customization abilities. Some things our Web App may need to do:
> - controls for boston matrix, various types of chargs, speedometers
> - full user session management, security, roles, etc.
> - integration with other data sources (e.g. Weather, newsfeed)
> - support for user input -- e.g. in cost/budget/forecasting analysis
> - support for "advanced" reports -- our organization is proficient with
> Crystal Reports, so we may wish to continue using it over RS2005.
> I was concerned that the solution/portal offered by Microsoft (or other
> vendors) would not meet our requirements. This is why I think it would make
> sense if we built our own custom portal using our own technologies.
> Hence, my question about the middle-tier -- What type of data constructs
> should I be using?
> Thank you,
> Ray
> <Myles.Matheson@.gmail.com> wrote in message
> news:1124414479.649578.162120@.g49g2000cwa.googlegr oups.com...
>
>
SSAS 2005 and .Net technologies. I know that we can use BI portals such as
from Proclarity, Panorama, Business Objects, etc. However, we wanted to
create our own UI front end (either Web or Winform).
My question is this -- can someone recommend what (and how) .Net
technologies to use to consume the data from SSAS? e.g. in a traditional
OLTP system, I would use sql queries and ado.net to fill my data table, and
maybe use an OR mapper to hydrate custom business objects. The data table
would then be sent to the UI front end for display / control binding and
handling.
For a data warehouse... what would I do differently? Do I still use ado.net
and maybe MDX / XMLA to query the cube? What data structures do I use to
consume the data? A Dataset with relationships? Would you transform that
data to customized business objects? Do you have any strategies on how to
support drill-down on the front end?
Or, would the middle-tier be very "thin" -- meaning the front-end would just
interface to AS directly and we have smart controls to bind to the raw data?
Any pointers would be greatly appreciated.
Thanks
Ray
p.s. I've been cross posting to the two newsgroups -- could someone tell me
what questions belong to which group?
Reporting Portal for OLAP
Hello Ray,
For a quick start have a look at SQL Server 2005 Reporting Services.
It comes free with SQL server and provides a web base portal that you
can customize using .NET and or Sharepoint. You can create your own
app/ portal using RS web services.
RS2005 Reports support MDX natively so you can use Reporting Services
to deliver standard reports against Analysis Services.
New to SQL server 2005 is Report Builder that provides a great web
based UI for creating ad hoc reports and publishing them back to the
Reporting services portal. Report Builder also supports drill through
to underlying data.
Check out
http://www.microsoft.com/technet/pro.../2005ssrs.mspx
Hope this gets you started.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hi,
Thank you for the response. I have looked a little bit into Reporting
Services... I may be a bit weak on the Sharepoint side of things though --
my concern is that the RS2005/Sharepoint didn't offer sufficient
customization abilities. Some things our Web App may need to do:
- controls for boston matrix, various types of chargs, speedometers
- full user session management, security, roles, etc.
- integration with other data sources (e.g. Weather, newsfeed)
- support for user input -- e.g. in cost/budget/forecasting analysis
- support for "advanced" reports -- our organization is proficient with
Crystal Reports, so we may wish to continue using it over RS2005.
I was concerned that the solution/portal offered by Microsoft (or other
vendors) would not meet our requirements. This is why I think it would make
sense if we built our own custom portal using our own technologies.
Hence, my question about the middle-tier -- What type of data constructs
should I be using?
Thank you,
Ray
<Myles.Matheson@.gmail.com> wrote in message
news:1124414479.649578.162120@.g49g2000cwa.googlegr oups.com...
> Reporting Portal for OLAP
> Hello Ray,
> For a quick start have a look at SQL Server 2005 Reporting Services.
> It comes free with SQL server and provides a web base portal that you
> can customize using .NET and or Sharepoint. You can create your own
> app/ portal using RS web services.
> RS2005 Reports support MDX natively so you can use Reporting Services
> to deliver standard reports against Analysis Services.
> New to SQL server 2005 is Report Builder that provides a great web
> based UI for creating ad hoc reports and publishing them back to the
> Reporting services portal. Report Builder also supports drill through
> to underlying data.
> Check out
> http://www.microsoft.com/technet/pro.../2005ssrs.mspx
> Hope this gets you started.
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
|||I found the best way to exchange data between the UI and business layers was
"processed" XML/A.
That is to say, the business layer implements services that receive user
requests, maps them to MDX query patterns, queries the server using Adomd.net
and specifying an XML/A response type, transforms the XML/A to match the
request (table, indicator, chart) and returns it to the UI layer.
Consider that a standard XML/A response will usually include tons of
unnecessary metadata (in a custom app), so you can substantially reduce the
size of the XML/A message before sending it to the UI layer.
HTH,
Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html
"Ray" wrote:
> Hi,
> Thank you for the response. I have looked a little bit into Reporting
> Services... I may be a bit weak on the Sharepoint side of things though --
> my concern is that the RS2005/Sharepoint didn't offer sufficient
> customization abilities. Some things our Web App may need to do:
> - controls for boston matrix, various types of chargs, speedometers
> - full user session management, security, roles, etc.
> - integration with other data sources (e.g. Weather, newsfeed)
> - support for user input -- e.g. in cost/budget/forecasting analysis
> - support for "advanced" reports -- our organization is proficient with
> Crystal Reports, so we may wish to continue using it over RS2005.
> I was concerned that the solution/portal offered by Microsoft (or other
> vendors) would not meet our requirements. This is why I think it would make
> sense if we built our own custom portal using our own technologies.
> Hence, my question about the middle-tier -- What type of data constructs
> should I be using?
> Thank you,
> Ray
> <Myles.Matheson@.gmail.com> wrote in message
> news:1124414479.649578.162120@.g49g2000cwa.googlegr oups.com...
>
>
Subscribe to:
Posts (Atom)