Newbie question on dbo permission

Hi all,
I have a sql account in a shared hosting server and my account is able
to execute scripts to create [dbo].* tables and [dbo].* store
procedures.
however, after they are created, if I tried to execute them, I get for
example,
permission denied on object 'GetUserDataByEmail', database 'mypm',
owner 'dbo'.
so after googling around, I found out I had to do
GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
then i could execute it.
so i have these questions
1. I always thought in order to be able to create [dbo].* tables, you
need to a dbo?
2. If I'm right with #1, then how come my user can't excute
[dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
access to it?
3. I see in another person's code, it had "GRANT EXECUTE on
dbo.GetUserDataByEmail to public, what does public mean? anybody can
execute it? if so, then what's the point of setting [dbo] to it?
Really appreicate your 2 cents on this. Thanks"Liming" <lmxudotnet@.gmail.com> wrote in message
news:1168466707.519839.206840@.77g2000hsv.googlegroups.com...
> Hi all,
> I have a sql account in a shared hosting server and my account is able
> to execute scripts to create [dbo].* tables and [dbo].* store
> procedures.
> however, after they are created, if I tried to execute them, I get for
> example,
> permission denied on object 'GetUserDataByEmail', database 'mypm',
> owner 'dbo'.
> so after googling around, I found out I had to do
> GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
> then i could execute it.
> so i have these questions
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
> access to it?
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
> Really appreicate your 2 cents on this. Thanks
>
Is this SQL 2000 or 2005?
David|||Hi
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
You will have to be a member of sysadmin server role or db_owner database
role.
DBO is just a privileged user.
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
> access to it?
If you memeber of syadmin server role or db_owner database role you don't
need EXECUTE permission
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
Public means that every user can execute the sp , do you really want it
:-)))?
"Liming" <lmxudotnet@.gmail.com> wrote in message
news:1168466707.519839.206840@.77g2000hsv.googlegroups.com...
> Hi all,
> I have a sql account in a shared hosting server and my account is able
> to execute scripts to create [dbo].* tables and [dbo].* store
> procedures.
> however, after they are created, if I tried to execute them, I get for
> example,
> permission denied on object 'GetUserDataByEmail', database 'mypm',
> owner 'dbo'.
> so after googling around, I found out I had to do
> GRANT EXECUTE ON [dbo].[GetUserDataByEmail] TO 'myuser'
> then i could execute it.
> so i have these questions
> 1. I always thought in order to be able to create [dbo].* tables, you
> need to a dbo?
> 2. If I'm right with #1, then how come my user can't excute
> [dbo].[GetUserDataByEmail], instead I had to explicilty grant execute
> access to it?
> 3. I see in another person's code, it had "GRANT EXECUTE on
> dbo.GetUserDataByEmail to public, what does public mean? anybody can
> execute it? if so, then what's the point of setting [dbo] to it?
> Really appreicate your 2 cents on this. Thanks
>

Newbie Question About Indexes and Filegroups

Gang:
I have two hard-disk drives, one which I want to use to store
data and the other I want to store the indexes. I set up two
filegroups, one pointing to the first drive and the other pointing
to the second drive. In my CREATE TABLE statements, I use the ON
filegroup clause to point to the first filegroup (the "data"
filegroup).
In my CREATE INDEX statements, I use the ON filegroup clause to point
to the second filegroup (the "index" filegroup).
So far so good.
But! In the MSDN for "CREATE INDEX", there is this sentence: "Because
the leaf level of a clustered index and its data pages are the same
by definition, creating a clustered index and using the ON filegroup
clause EFFECTIVELY MOVES a table from the file on which the table was
created
to the new filegroup."
But I DON'T want my data moved from the "data" filegroup (the first
disk-drive) to the "index" filegroup (the second) disk-drive! Why?
What if I have to create a non-clustered index later on? That new
index will be located on the "index" filegroup, but because the data
was EFFECTIVELY MOVED due to the clustered index to the "index"
filegroup, I now have the data AND the non-
clustered index located on the SAME hard-drive (the "index" drive).
But, the whole idea is to have the data on one drive and the indexes
on another drive.
I realize that I can probably get away with have no clustered indexes
at
all by using the NONCLUSTERED keyword when creating PRIMARY KEYs and
when making indexes using CREATE INDEX.
Have I mis-understood something here'
Thanks,
ScottSince u already know that clustered index is basically
table data and non-clustered indexes are actual indexes so
it will be better if u store all ur clustered indexes on 1
filegroup( 1 drive) and all non-clustered indexes on 2nd
filegroup......Otherwise if u really want to use 2 hard-
disks effectively....place all ur data and indexes on 1st
hard disk and log files on 2nd hard disk....
Thanks
--Harvinder
>--Original Message--
>Gang:
>I have two hard-disk drives, one which I want to use to
store
>data and the other I want to store the indexes. I set up
two
>filegroups, one pointing to the first drive and the other
pointing
>to the second drive. In my CREATE TABLE statements, I
use the ON
>filegroup clause to point to the first filegroup
(the "data"
>filegroup).
>In my CREATE INDEX statements, I use the ON filegroup
clause to point
>to the second filegroup (the "index" filegroup).
>So far so good.
>But! In the MSDN for "CREATE INDEX", there is this
sentence: "Because
>the leaf level of a clustered index and its data pages
are the same
>by definition, creating a clustered index and using the
ON filegroup
>clause EFFECTIVELY MOVES a table from the file on which
the table was
>created
>to the new filegroup."
>But I DON'T want my data moved from the "data" filegroup
(the first
>disk-drive) to the "index" filegroup (the second) disk-
drive! Why?
>What if I have to create a non-clustered index later on?
That new
>index will be located on the "index" filegroup, but
because the data
>was EFFECTIVELY MOVED due to the clustered index to
the "index"
>filegroup, I now have the data AND the non-
>clustered index located on the SAME hard-drive
(the "index" drive).
>But, the whole idea is to have the data on one drive and
the indexes
>on another drive.
>I realize that I can probably get away with have no
clustered indexes
>at
>all by using the NONCLUSTERED keyword when creating
PRIMARY KEYs and
>when making indexes using CREATE INDEX.
>Have I mis-understood something here'
>Thanks,
>Scott
>.
>|||The data follows the clustered index. Period. You can create other =indexes on separate drives/file groups. In my opinion, I would be more =concerned about data availability (RAID) than I would about having =indexes on one drive and data on another. You don't go into much detail =about your machine, or the other hardware in the box, but I would be =tempted to set up a RAID array with the drives and put the data (and =indexes there). Don't worry about trying to separate them. This way =you will be able to loose a hard drive (it does happen) and you will be =able to continue working.
-- Keith, SQL Server MVP
"Scott" <slhecht@.attglobal.net> wrote in message =news:a018a20c.0307170621.756b5741@.posting.google.com...
> Gang:
> > I have two hard-disk drives, one which I want to use to store
> data and the other I want to store the indexes. I set up two
> filegroups, one pointing to the first drive and the other pointing
> to the second drive. In my CREATE TABLE statements, I use the ON
> filegroup clause to point to the first filegroup (the "data"
> filegroup).
> In my CREATE INDEX statements, I use the ON filegroup clause to point
> to the second filegroup (the "index" filegroup).
> > So far so good.
> > But! In the MSDN for "CREATE INDEX", there is this sentence: "Because
> the leaf level of a clustered index and its data pages are the same
> by definition, creating a clustered index and using the ON filegroup
> clause EFFECTIVELY MOVES a table from the file on which the table was
> created
> to the new filegroup."
> > But I DON'T want my data moved from the "data" filegroup (the first
> disk-drive) to the "index" filegroup (the second) disk-drive! Why? > What if I have to create a non-clustered index later on? That new
> index will be located on the "index" filegroup, but because the data
> was EFFECTIVELY MOVED due to the clustered index to the "index"
> filegroup, I now have the data AND the non-
> clustered index located on the SAME hard-drive (the "index" drive). > > But, the whole idea is to have the data on one drive and the indexes
> on another drive. > > I realize that I can probably get away with have no clustered indexes
> at
> all by using the NONCLUSTERED keyword when creating PRIMARY KEYs and > when making indexes using CREATE INDEX.
> > Have I mis-understood something here'
> > Thanks,
> Scott|||Hello,
The problem here is clustered indexes vrs non clustered
indexes. The nature of clustered indexes is they must be
saved in the same filegroup as the data, as they organise
the storage of the data.
An example of a clustered index is by default all primary
keys are clustered indexes.
Non clustered indexes however can be in a different
filegroup.
Peter
>--Original Message--
>Gang:
>I have two hard-disk drives, one which I want to use to
store
>data and the other I want to store the indexes. I set up
two
>filegroups, one pointing to the first drive and the other
pointing
>to the second drive. In my CREATE TABLE statements, I
use the ON
>filegroup clause to point to the first filegroup
(the "data"
>filegroup).
>In my CREATE INDEX statements, I use the ON filegroup
clause to point
>to the second filegroup (the "index" filegroup).
>So far so good.
>But! In the MSDN for "CREATE INDEX", there is this
sentence: "Because
>the leaf level of a clustered index and its data pages
are the same
>by definition, creating a clustered index and using the
ON filegroup
>clause EFFECTIVELY MOVES a table from the file on which
the table was
>created
>to the new filegroup."
>But I DON'T want my data moved from the "data" filegroup
(the first
>disk-drive) to the "index" filegroup (the second) disk-
drive! Why?
>What if I have to create a non-clustered index later on?
That new
>index will be located on the "index" filegroup, but
because the data
>was EFFECTIVELY MOVED due to the clustered index to
the "index"
>filegroup, I now have the data AND the non-
>clustered index located on the SAME hard-drive
(the "index" drive).
>But, the whole idea is to have the data on one drive and
the indexes
>on another drive.
>I realize that I can probably get away with have no
clustered indexes
>at
>all by using the NONCLUSTERED keyword when creating
PRIMARY KEYs and
>when making indexes using CREATE INDEX.
>Have I mis-understood something here'
>Thanks,
>Scott
>.
>|||I would agree completely with Keith. I see a LOT of customers spend a
tremendous amount of time worrying about how to partition thier data and
indexes across different file groups. Some people may disagree with me,
but... there are VERY few installations where this is ever necessary. I
might start to think about this if I had incredibly high IO rates or
hundreds of G (if not a full terrabyte) of data. Short of that... it just
won't make much of a noticeable difference and you could easily make things
worth.
Don't take this the wrong way, but... if you're asking questions like this,
then chances are that your SQL installation is small enough that you
wouldn't want to do this.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:eErOpGHTDHA.1920@.TK2MSFTNGP11.phx.gbl...
The data follows the clustered index. Period. You can create other indexes
on separate drives/file groups. In my opinion, I would be more concerned
about data availability (RAID) than I would about having indexes on one
drive and data on another. You don't go into much detail about your
machine, or the other hardware in the box, but I would be tempted to set up
a RAID array with the drives and put the data (and indexes there). Don't
worry about trying to separate them. This way you will be able to loose a
hard drive (it does happen) and you will be able to continue working.
--
Keith, SQL Server MVP
"Scott" <slhecht@.attglobal.net> wrote in message
news:a018a20c.0307170621.756b5741@.posting.google.com...
> Gang:
> I have two hard-disk drives, one which I want to use to store
> data and the other I want to store the indexes. I set up two
> filegroups, one pointing to the first drive and the other pointing
> to the second drive. In my CREATE TABLE statements, I use the ON
> filegroup clause to point to the first filegroup (the "data"
> filegroup).
> In my CREATE INDEX statements, I use the ON filegroup clause to point
> to the second filegroup (the "index" filegroup).
> So far so good.
> But! In the MSDN for "CREATE INDEX", there is this sentence: "Because
> the leaf level of a clustered index and its data pages are the same
> by definition, creating a clustered index and using the ON filegroup
> clause EFFECTIVELY MOVES a table from the file on which the table was
> created
> to the new filegroup."
> But I DON'T want my data moved from the "data" filegroup (the first
> disk-drive) to the "index" filegroup (the second) disk-drive! Why?
> What if I have to create a non-clustered index later on? That new
> index will be located on the "index" filegroup, but because the data
> was EFFECTIVELY MOVED due to the clustered index to the "index"
> filegroup, I now have the data AND the non-
> clustered index located on the SAME hard-drive (the "index" drive).
> But, the whole idea is to have the data on one drive and the indexes
> on another drive.
> I realize that I can probably get away with have no clustered indexes
> at
> all by using the NONCLUSTERED keyword when creating PRIMARY KEYs and
> when making indexes using CREATE INDEX.
> Have I mis-understood something here'
> Thanks,
> Scott

Newbie question

Hi All,
Is it possible to store tables (in general database objects) in SQL clients and access it from the SQL server?
Thanks
Eva
What are you trying to accomplish?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:2C3CCA52-4F57-409A-A331-4BFC9001F773@.microsoft.com...
> Hi All,
> Is it possible to store tables (in general database objects) in SQL
clients and access it from the SQL server?
> Thanks
>
|||I am designing a system in which there are about 50 computers containing region-wise data in tables. The tables in different regions have same structure but the data is region-specific. I need to transfer data from the tables in each region to a server. T
he problem is that I cannot have 50 SQL servers installations. I am looking for a work around. Can you suggest what should be done?
Thanks.
|||Eva
REPLICATION is what you need.
http://www.swynk.com/friends/achigrik/SetupMR.asp -- Setting Up Merge
Replication: A Step by Step Guide
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:F851497F-8B3B-4A3C-9482-97B5F0B75410@.microsoft.com...
> I am designing a system in which there are about 50 computers containing
region-wise data in tables. The tables in different regions have same
structure but the data is region-specific. I need to transfer data from the
tables in each region to a server. The problem is that I cannot have 50 SQL
servers installations. I am looking for a work around. Can you suggest what
should be done?
> Thanks.
|||Thanks Uri.
Yes, I could have the 50 computers set up as publishers and the server as a subscriber. But, what I understand of Publishers is that they have to be SQL servers!
|||Eva
How do they store the data? With text files?
Do they have an application (vb,c) which can connect to the main server?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:A89D4478-7858-493C-BD95-607A710004B3@.microsoft.com...
> Thanks Uri.
> Yes, I could have the 50 computers set up as publishers and the server as
a subscriber. But, what I understand of Publishers is that they have to be
SQL servers!
|||Data is stored in SQL tables. There is an application running on the server which accesses the tables in each of the 50 machines and fetches data from them!
|||Eva
Have you looked at DTS utility?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!
|||Merge replication would seem to fit the bill here. In merge the publisher/subscriber metaphore doesn't really hold, so in your case you'd have a central publisher and 50 subscribers. The subscribers don't need to be sql server - they could heterogenous eg
Access. I'm not sure from your post if you have the option of setting up the 50 subscriber's repositories. If so, then you could use MSDE.
Regards,
Paul Ibison
|||Eva,
When you say "SQL Tables" do you mean tables in a SQL Server database? Or is
it in another RDBMS?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!

Newbie question

Hi All,
Is it possible to store tables (in general database objects) in SQL clients
and access it from the SQL server?
ThanksEva
What are you trying to accomplish?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:2C3CCA52-4F57-409A-A331-4BFC9001F773@.microsoft.com...
> Hi All,
> Is it possible to store tables (in general database objects) in SQL
clients and access it from the SQL server?
> Thanks
>|||I am designing a system in which there are about 50 computers containing reg
ion-wise data in tables. The tables in different regions have same structure
but the data is region-specific. I need to transfer data from the tables in
each region to a server. T
he problem is that I cannot have 50 SQL servers installations. I am looking
for a work around. Can you suggest what should be done?
Thanks.|||Eva
REPLICATION is what you need.
http://www.swynk.com/friends/achigrik/SetupMR.asp -- Setting Up Merge
Replication: A Step by Step Guide
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:F851497F-8B3B-4A3C-9482-97B5F0B75410@.microsoft.com...
> I am designing a system in which there are about 50 computers containing
region-wise data in tables. The tables in different regions have same
structure but the data is region-specific. I need to transfer data from the
tables in each region to a server. The problem is that I cannot have 50 SQL
servers installations. I am looking for a work around. Can you suggest what
should be done?
> Thanks.|||Thanks Uri.
Yes, I could have the 50 computers set up as publishers and the server as a
subscriber. But, what I understand of Publishers is that they have to be SQL
servers!|||Eva
How do they store the data? With text files?
Do they have an application (vb,c) which can connect to the main server?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:A89D4478-7858-493C-BD95-607A710004B3@.microsoft.com...
> Thanks Uri.
> Yes, I could have the 50 computers set up as publishers and the server as
a subscriber. But, what I understand of Publishers is that they have to be
SQL servers!|||Data is stored in SQL tables. There is an application running on the server
which accesses the tables in each of the 50 machines and fetches data from t
hem!|||Eva
Have you looked at DTS utility?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!|||Merge replication would seem to fit the bill here. In merge the publisher/su
bscriber metaphore doesn't really hold, so in your case you'd have a central
publisher and 50 subscribers. The subscribers don't need to be sql server -
they could heterogenous eg
Access. I'm not sure from your post if you have the option of setting up the
50 subscriber's repositories. If so, then you could use MSDE.
Regards,
Paul Ibison|||Eva,
When you say "SQL Tables" do you mean tables in a SQL Server database? Or is
it in another RDBMS?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!

Newbie question

Hi All
Is it possible to store tables (in general database objects) in SQL clients and access it from the SQL server
ThankEva
What are you trying to accomplish?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:2C3CCA52-4F57-409A-A331-4BFC9001F773@.microsoft.com...
> Hi All,
> Is it possible to store tables (in general database objects) in SQL
clients and access it from the SQL server?
> Thanks
>|||I am designing a system in which there are about 50 computers containing region-wise data in tables. The tables in different regions have same structure but the data is region-specific. I need to transfer data from the tables in each region to a server. The problem is that I cannot have 50 SQL servers installations. I am looking for a work around. Can you suggest what should be done
Thanks.|||Eva
REPLICATION is what you need.
http://www.swynk.com/friends/achigrik/SetupMR.asp -- Setting Up Merge
Replication: A Step by Step Guide
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:F851497F-8B3B-4A3C-9482-97B5F0B75410@.microsoft.com...
> I am designing a system in which there are about 50 computers containing
region-wise data in tables. The tables in different regions have same
structure but the data is region-specific. I need to transfer data from the
tables in each region to a server. The problem is that I cannot have 50 SQL
servers installations. I am looking for a work around. Can you suggest what
should be done?
> Thanks.|||Thanks Uri
Yes, I could have the 50 computers set up as publishers and the server as a subscriber. But, what I understand of Publishers is that they have to be SQL servers!|||Eva
How do they store the data? With text files?
Do they have an application (vb,c) which can connect to the main server?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:A89D4478-7858-493C-BD95-607A710004B3@.microsoft.com...
> Thanks Uri.
> Yes, I could have the 50 computers set up as publishers and the server as
a subscriber. But, what I understand of Publishers is that they have to be
SQL servers!|||Data is stored in SQL tables. There is an application running on the server which accesses the tables in each of the 50 machines and fetches data from them!|||Eva
Have you looked at DTS utility?
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!|||Eva,
When you say "SQL Tables" do you mean tables in a SQL Server database? Or is
it in another RDBMS?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:4F67F94C-FFC2-4CB9-8ADB-2C66E427EA8C@.microsoft.com...
> Data is stored in SQL tables. There is an application running on the
server which accesses the tables in each of the 50 machines and fetches data
from them!|||Can't you have just one SQL Server and have all clients store their data in this SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eva" <anonymous@.discussions.microsoft.com> wrote in message
news:F851497F-8B3B-4A3C-9482-97B5F0B75410@.microsoft.com...
> I am designing a system in which there are about 50 computers containing region-wise data in tables. The
tables in different regions have same structure but the data is region-specific. I need to transfer data from
the tables in each region to a server. The problem is that I cannot have 50 SQL servers installations. I am
looking for a work around. Can you suggest what should be done?
> Thanks.

newbie how to create temp table and populate

Sorry guys I know this is easy but I've been looking for about an hour for a straight forward explanation.
I want to store a user's wish list while they browse the site, then they can send me an enquiry populated with their choices.
Basically, a shopping cart!
I thought of using session variables and string manipulations but I am more comfortable with DB queries.
a simple 4 column table would cover everything.
SQL server and VBScript
Thanks
Mhttp://www.sqlteam.com/item.asp?ItemID=2029|||thanks, that was the first one I found on Google, but it just confused me.

just after I posted my Query I looked again on Google and found this:
http://www.programmers-corner.com/article/76 which seemed to be more my level!
I am currently having a go.|||;-) well Google is your friend.|||aye,
Here you go Newbies, the three statement to create a temp table, insert some values and retrieve them:
CREATE TABLE #myTempTable
(
DummyField1 INT,
DummyField2 VARCHAR(20)
)
--------
INSERT into #myTempTable (DummyField1, DummyField2) VALUES (1,2)
--------
SELECT * from #myTempTable

I new it wasn't rocket science, sometimes just writing to a forum clears your mind...enought to formulate the correct query for Google! doh

Newbie - why store XML in Sql?

I don't understand why anyone would want to store pure XML data in SQL. Why not just parse the XML and store the information contained in "normal" (e.g. int, varchar) database fields?

TIA,

Barkingdog

Check http://msdn2.microsoft.com/en-us/library/ms187508.aspx, in particular the section "relational or XML data model" to find reason when it makes sense to use the XML data type in SQL server 2005.

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
RandySQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegroups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>|||Actually, SQL Server Management Studio Express|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:

> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegroups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;

> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
Randy
SQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegr oups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>
|||Actually, SQL Server Management Studio Express
|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy
|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:

> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>
|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy
|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy
|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegro ups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;

> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
RandySQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegroups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>|||Actually, SQL Server Management Studio Express|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:
> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegroups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;
> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||VB.NET you should be able to grab the binary data from the database, store
it in a Byte() array and create a Graphics object from it. I'm doing
something similar right now in VB 2005 with dynamically generated images
being passed from SQL Server to a client-side VB app where the binary
content is converted to a bitmap and displayed on a form. Just be sure to
properly dispose of your Graphics objects, etc., when you're done with them.
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182982110.182760.129310@.e16g2000pri.googlegroups.com...
> Ignore that last reply. Apparently, I wasn't looking at refreshed
> view of the db. The table is there, as are both columns. There was
> no data in the table, so I added the key column values and just ran
> the UPDATE part of the query, which seems to have populated the
> image_data values. Now, i just have to figure out how to pull this
> onto my VB form. When I look at the table data, it just says <Binary
> Data> in each of the fields, so I'm not certain that I have everything
> in place just yet. If I have trouble, I'll re-post.
> Thanks for everybody's help!
> Randy
>|||I need to do the exact same thing but in SQL Server 2000.
I have been tinkering with BULK INSERT and OPENROWSET but have not been able
to get anything to load the image data.
Actually in my case, the image data can be a Word document, Excel
spreadsheet, PDF file, text file, etc.
Thank you in advance for any information that you can provide.
Joe|||Hi Joe,
The BULK rowset provider functionality to load BLOBs is only available since
SQL Server 2005.
See the following example by Erland Sommarskog on how this can be done is
SQL Server 2000:
http://www.sommarskog.se/blobload.txt
HTH,
Plamen Ratchev
http://www.SQLStudio.com

NEWBE

I have web forms on my site that store data in an sql dbase.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate that
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.
If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:

>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each request
>was submitted. The only function that i found to automatically populate that
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.

NEWBE

I have web forms on my site that store data in an sql dbase.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate tha
t
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:

>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each reques
t
>was submitted. The only function that i found to automatically populate th
at
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.