Hi,
My question may be stupid, but can;t figure out a way to do it "simply".
I'm taking over the admin of some SQL instance and I want to clenu up the
logins list. But before deleting anything, I need to know what objects are
owned by the user I want to delete to not brak anything.
Is there any way (not matter how) to retrieve the exclusive list of object
owned by a user ?
thanks,
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !Try this
SELECT o.name
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE u.name = 'username'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:%23Q8GcImHFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Hi,
> My question may be stupid, but can;t figure out a way to do it "simply".
> I'm taking over the admin of some SQL instance and I want to clenu up the
> logins list. But before deleting anything, I need to know what objects are
> owned by the user I want to delete to not brak anything.
> Is there any way (not matter how) to retrieve the exclusive list of object
> owned by a user ?
> thanks,
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
>
Showing posts with label figure. Show all posts
Showing posts with label figure. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
get a count between a time range
I am trying to figure out if I can do this in a SQL query. I need to
know how many users are active during a given time period. I want to
be able to return the results below. If you look at the row with
User3 I only want to count User2 once even though he was active twice
during the time frame for User3
Start Time End Time User Id Concurrent User Count
06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
I am pretty much stumped as how to proceed.
Thanks TimAssuming you're trying to query users who were active for any part of
the duration @.StartTime...@.EndTime inclusive.
SELECT DISTINCT UserId
FROM Table1
WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||If you look at User3 there were 3 users active during User3's the start
time and end time including User3. I need to figure that count.
Thanks Tim
Lubdha Khandelwal wrote:
> Assuming you're trying to query users who were active for any part of
> the duration @.StartTime...@.EndTime inclusive.
> SELECT DISTINCT UserId
> FROM Table1
> WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
> OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||I'm
. Do you want to count all the unique users that were
active during each of the active duration for all the users?
If so, this could get you that count:
SELECT T1.UserId, COUNT(DISTINCT T2.UserId)
FROM UserTable T1 INNER JOIN UserTable T2
ON ( (T2.StartTime <= T1.StartTime AND T2.EndTime >= T1.StartTime)
OR (T2.StartTime BETWEEN T1.StartTime AND T1.EndTime) )
GROUP BY T1.UserId|||TDT (tim.trujillo@.gmd.com) writes:
> I am trying to figure out if I can do this in a SQL query. I need to
> know how many users are active during a given time period. I want to
> be able to return the results below. If you look at the row with
> User3 I only want to count User2 once even though he was active twice
> during the time frame for User3
> Start Time End Time User Id Concurrent User
> Count
> 06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
> 06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
> 06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
> 06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
> 06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
> 06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
> I am pretty much stumped as how to proceed.
I assume that the above is the desired output. But how does the input
look like? It would help to have the CREATE TABLE statement and
INSERT statements for the test data. Then it would be simple to copy-paste
into a query tool to develop a tested query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Lubdha that last query did not work. I was geting resource limit has
been reached and the query was cancelled. But here is script to create
the table and insert rows.
CREATE TABLE [dbo].[UserActivity]
(
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[UserID] [varchar](50) NULL
)
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
Erland Sommarskog wrote:
> TDT (tim.trujillo@.gmd.com) writes:
> I assume that the above is the desired output. But how does the input
> look like? It would help to have the CREATE TABLE statement and
> INSERT statements for the test data. Then it would be simple to copy-paste
> into a query tool to develop a tested query.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for providing the table and data.
SELECT A.StartTime,
A.EndTime,
A.UserID,
count(distinct B.UserID) as ConcurrentUserCount
FROM UserActivity as A
JOIN UserActivity as B
ON A.StartTime between B.StartTime and B.EndTime
OR A.EndTime between B.StartTime and B.EndTime
or B.StartTime between A.StartTime and A.EndTime
GROUP BY A.StartTime,
A.EndTime,
A.UserID
ORDER BY 1, 2, 3
Roy Harvey
Beacon Falls, CT
On 23 Jun 2006 15:46:23 -0700, "TDT" <tim.trujillo@.gmd.com> wrote:
>Lubdha that last query did not work. I was geting resource limit has
>been reached and the query was cancelled. But here is script to create
>the table and insert rows.
>CREATE TABLE [dbo].[UserActivity]
>(
> [StartTime] [datetime] NULL,
> [EndTime] [datetime] NULL,
> [UserID] [varchar](50) NULL
> )
>
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
>
>Erland Sommarskog wrote:|||This is not a table; it has no key and no possible way to get a key.
If you knew what a table was, would you have written this? If you knew
either ISO-8601 or SQL, would you have used the proper temporal
formats?
CREATE TABLE UserActivity
(user_id VARCHAR(50) NOT NULL, -- magic data type used by newbies!
start_time DATETIME NOT NULL,
end_time DATETIME, -- null = current
PRIMARY KEY (user_id, start_time))
Where is the DDL for your reporting periods? What is your
grandularity? There are some specs missing here. However, you can get
samples at points in time; Set up a VIEW or TABLE with times in it.
SELECT R.report_time, COUNT(user_id) AS active_user_cnt
FROM ReportPeriods AS R, UserActivity AS U
WHERE R.report_time BETWEEN U.start_time AND U.end_time
GROUP BY R.report_time;
This actually works pretty well with a fine grandularity for simple
time series analysis. But if you wanted to do (n)-minute durations,
then we need a very complex set of rules for logging in and out within
a single duration.
know how many users are active during a given time period. I want to
be able to return the results below. If you look at the row with
User3 I only want to count User2 once even though he was active twice
during the time frame for User3
Start Time End Time User Id Concurrent User Count
06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
I am pretty much stumped as how to proceed.
Thanks TimAssuming you're trying to query users who were active for any part of
the duration @.StartTime...@.EndTime inclusive.
SELECT DISTINCT UserId
FROM Table1
WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||If you look at User3 there were 3 users active during User3's the start
time and end time including User3. I need to figure that count.
Thanks Tim
Lubdha Khandelwal wrote:
> Assuming you're trying to query users who were active for any part of
> the duration @.StartTime...@.EndTime inclusive.
> SELECT DISTINCT UserId
> FROM Table1
> WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
> OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||I'm
. Do you want to count all the unique users that wereactive during each of the active duration for all the users?
If so, this could get you that count:
SELECT T1.UserId, COUNT(DISTINCT T2.UserId)
FROM UserTable T1 INNER JOIN UserTable T2
ON ( (T2.StartTime <= T1.StartTime AND T2.EndTime >= T1.StartTime)
OR (T2.StartTime BETWEEN T1.StartTime AND T1.EndTime) )
GROUP BY T1.UserId|||TDT (tim.trujillo@.gmd.com) writes:
> I am trying to figure out if I can do this in a SQL query. I need to
> know how many users are active during a given time period. I want to
> be able to return the results below. If you look at the row with
> User3 I only want to count User2 once even though he was active twice
> during the time frame for User3
> Start Time End Time User Id Concurrent User
> Count
> 06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
> 06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
> 06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
> 06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
> 06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
> 06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
> I am pretty much stumped as how to proceed.
I assume that the above is the desired output. But how does the input
look like? It would help to have the CREATE TABLE statement and
INSERT statements for the test data. Then it would be simple to copy-paste
into a query tool to develop a tested query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Lubdha that last query did not work. I was geting resource limit has
been reached and the query was cancelled. But here is script to create
the table and insert rows.
CREATE TABLE [dbo].[UserActivity]
(
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[UserID] [varchar](50) NULL
)
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
Erland Sommarskog wrote:
> TDT (tim.trujillo@.gmd.com) writes:
> I assume that the above is the desired output. But how does the input
> look like? It would help to have the CREATE TABLE statement and
> INSERT statements for the test data. Then it would be simple to copy-paste
> into a query tool to develop a tested query.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for providing the table and data.
SELECT A.StartTime,
A.EndTime,
A.UserID,
count(distinct B.UserID) as ConcurrentUserCount
FROM UserActivity as A
JOIN UserActivity as B
ON A.StartTime between B.StartTime and B.EndTime
OR A.EndTime between B.StartTime and B.EndTime
or B.StartTime between A.StartTime and A.EndTime
GROUP BY A.StartTime,
A.EndTime,
A.UserID
ORDER BY 1, 2, 3
Roy Harvey
Beacon Falls, CT
On 23 Jun 2006 15:46:23 -0700, "TDT" <tim.trujillo@.gmd.com> wrote:
>Lubdha that last query did not work. I was geting resource limit has
>been reached and the query was cancelled. But here is script to create
>the table and insert rows.
>CREATE TABLE [dbo].[UserActivity]
>(
> [StartTime] [datetime] NULL,
> [EndTime] [datetime] NULL,
> [UserID] [varchar](50) NULL
> )
>
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
>
>Erland Sommarskog wrote:|||This is not a table; it has no key and no possible way to get a key.
If you knew what a table was, would you have written this? If you knew
either ISO-8601 or SQL, would you have used the proper temporal
formats?
CREATE TABLE UserActivity
(user_id VARCHAR(50) NOT NULL, -- magic data type used by newbies!
start_time DATETIME NOT NULL,
end_time DATETIME, -- null = current
PRIMARY KEY (user_id, start_time))
Where is the DDL for your reporting periods? What is your
grandularity? There are some specs missing here. However, you can get
samples at points in time; Set up a VIEW or TABLE with times in it.
SELECT R.report_time, COUNT(user_id) AS active_user_cnt
FROM ReportPeriods AS R, UserActivity AS U
WHERE R.report_time BETWEEN U.start_time AND U.end_time
GROUP BY R.report_time;
This actually works pretty well with a fine grandularity for simple
time series analysis. But if you wanted to do (n)-minute durations,
then we need a very complex set of rules for logging in and out within
a single duration.
Geometry Data type in SQL Server 2005
Does SQL Server 2005 have a geometry data type?
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the develop
ment of a new workflow, to access data, analyze and map data stored in a dat
abase.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenan
ce of geo-spatial information, each vendor has chosen a fundamentally differ
ent approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the
underlying Oracle 9i kernel. Oracle allows users to interact directly with t
he database using SQL language. In addition, Oracle has published the struct
ure of its geometry, making it an open specification. Any GIS software is ca
pable accessing the data stored in Oracle. In addition, all business rules f
or data and geo-spatial data integrity is built into this model, and any acc
ess, input, edits or other interaction must adhere to the rules that are des
igned in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Eac
h GIS vendor is therefore responsible for developing a method for storing GI
S information in this database. Thus, Intergraph, ESRI, and MapInfo have dev
eloped their own binary geometry structures to allow them to store geo-spati
al information in SQL Server. This approach makes the geo-spatial informatio
n dependent on the GIS software that is used.
Can anyone please help me!docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the devel
opment of a new workflow, to access data, analyze and map data stored in a d
atabase.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and mainten
ance of geo-spatial information, each vendor has chosen a fundamentally diff
erent approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underl
ying Oracle 9i kernel. Oracle allows users to interact directly with the database us
ing SQL language. In addition, Oracle has published the structure of its geometry, m
aki
ng it an open specification. Any GIS software is capable accessing the data
stored in Oracle. In addition, all business rules for data and geo-spatial d
ata integrity is built into this model, and any access, input, edits or othe
r interaction must adhere t
o the rules that are designed in the database no matter the application accessing the databa
se.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS
vendor is therefore responsible for developing a method for storing GIS information
in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binar
y g
eometry structures to allow them to store geo-spatial information in SQL Server. This approa
ch makes the geo-spatial information dependent on the GIS software that is used.een">
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/0...arcsde_sql.html
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the develop
ment of a new workflow, to access data, analyze and map data stored in a dat
abase.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenan
ce of geo-spatial information, each vendor has chosen a fundamentally differ
ent approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the
underlying Oracle 9i kernel. Oracle allows users to interact directly with t
he database using SQL language. In addition, Oracle has published the struct
ure of its geometry, making it an open specification. Any GIS software is ca
pable accessing the data stored in Oracle. In addition, all business rules f
or data and geo-spatial data integrity is built into this model, and any acc
ess, input, edits or other interaction must adhere to the rules that are des
igned in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Eac
h GIS vendor is therefore responsible for developing a method for storing GI
S information in this database. Thus, Intergraph, ESRI, and MapInfo have dev
eloped their own binary geometry structures to allow them to store geo-spati
al information in SQL Server. This approach makes the geo-spatial informatio
n dependent on the GIS software that is used.
Can anyone please help me!docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the devel
opment of a new workflow, to access data, analyze and map data stored in a d
atabase.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and mainten
ance of geo-spatial information, each vendor has chosen a fundamentally diff
erent approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underl
ying Oracle 9i kernel. Oracle allows users to interact directly with the database us
ing SQL language. In addition, Oracle has published the structure of its geometry, m
aki
ng it an open specification. Any GIS software is capable accessing the data
stored in Oracle. In addition, all business rules for data and geo-spatial d
ata integrity is built into this model, and any access, input, edits or othe
r interaction must adhere t
o the rules that are designed in the database no matter the application accessing the databa
se.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS
vendor is therefore responsible for developing a method for storing GIS information
in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binar
y g
eometry structures to allow them to store geo-spatial information in SQL Server. This approa
ch makes the geo-spatial information dependent on the GIS software that is used.een">
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/0...arcsde_sql.html
Geometry Data type in SQL Server 2005
Does SQL Server 2005 have a geometry data type?
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, making it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere to the rules that are designed in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary geometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
Can anyone please help me!
docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, maki
ng it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere t
o the rules that are designed in the database no matter the application accessing the database.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary g
eometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/06...rcsde_sql.html
sql
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, making it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere to the rules that are designed in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary geometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
Can anyone please help me!
docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, maki
ng it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere t
o the rules that are designed in the database no matter the application accessing the database.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary g
eometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/06...rcsde_sql.html
sql
Monday, March 19, 2012
generating full script tables and data?
hi, I'm using sql server 2005 standard, and I want to be able to move my local database to another server, but I can't figure out how to script the database and the data so that I can just run one script to move the whole database. this can be done right? I can't imagine that such an obiviously necessary tool would be intentionally left out, so I'm figuring that I'm just a doofus and don't know where the option is...
can anyone help? thank you!
-SelAromYour request is one of those "You can get there from here, but..." kind of things. You're obviously comming from another environment where scripting a database is either the easiest or the only way to move it from one server to another. That isn't the case with Microsoft SQL Server.
See the KB Article How to move databases between computers that are running SQL Server (http://support.microsoft.com/default.aspx?scid=kb;en-us;314546) for more details.
If you really want to script out the schema and data, you can do it. Getting Started with SQL-DMO (http://msdn2.microsoft.com/en-us/library/ms144159.aspx) will start you on your way, but I'd strongly recommend using one of the simpler methods!
-PatP|||Your request is one of those "You can get there from here, but..." kind of things. You're obviously comming from another environment where scripting a database is either the easiest or the only way to move it from one server to another. That isn't the case with Microsoft SQL Server.
See the KB Article How to move databases between computers that are running SQL Server (http://support.microsoft.com/default.aspx?scid=kb;en-us;314546) for more details.
If you really want to script out the schema and data, you can do it. Getting Started with SQL-DMO (http://msdn2.microsoft.com/en-us/library/ms144159.aspx) will start you on your way, but I'd strongly recommend using one of the simpler methods!
-PatP
heh well I didn't mean to sound like such a n00b :) but why wouldn't this functionality be natively built in? I'll take a look at your links though, thanks a bunch!
-SelArom
can anyone help? thank you!
-SelAromYour request is one of those "You can get there from here, but..." kind of things. You're obviously comming from another environment where scripting a database is either the easiest or the only way to move it from one server to another. That isn't the case with Microsoft SQL Server.
See the KB Article How to move databases between computers that are running SQL Server (http://support.microsoft.com/default.aspx?scid=kb;en-us;314546) for more details.
If you really want to script out the schema and data, you can do it. Getting Started with SQL-DMO (http://msdn2.microsoft.com/en-us/library/ms144159.aspx) will start you on your way, but I'd strongly recommend using one of the simpler methods!
-PatP|||Your request is one of those "You can get there from here, but..." kind of things. You're obviously comming from another environment where scripting a database is either the easiest or the only way to move it from one server to another. That isn't the case with Microsoft SQL Server.
See the KB Article How to move databases between computers that are running SQL Server (http://support.microsoft.com/default.aspx?scid=kb;en-us;314546) for more details.
If you really want to script out the schema and data, you can do it. Getting Started with SQL-DMO (http://msdn2.microsoft.com/en-us/library/ms144159.aspx) will start you on your way, but I'd strongly recommend using one of the simpler methods!
-PatP
heh well I didn't mean to sound like such a n00b :) but why wouldn't this functionality be natively built in? I'll take a look at your links though, thanks a bunch!
-SelArom
Subscribe to:
Posts (Atom)