Help on DB design

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

It depends,

You have 3 (or more) options,

Store as Individual Table(rows

Each file will be stored as single row with key field.

The master table can hold the key field & number of files

Store as Delimited Value

If you use SQL server 2000, you are not allowed to store more than 8000 chars, otherwise you have to change the data type to LOB(text). In SQL Server 2005 you can use the Varchar(max).

Manipulations need to be done on the UI.

Single row modification can’t be done on the UI, you have to stick with your UI.

Store as XML value

If you use SQL Server 2005, you can use new data type XML.

You can store at any number of chars

Easy to manipulate on Database itself.

Structured & Well Managed

My Rank is – 1.XML, 2.Table(rows), 3.Delimited value

|||^Thank you Manivannan.D.Sekaran . I will follow Table(rows) as i am using sql server 2000.

Help on DB Design

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

Quote:

Originally Posted by bootzwiz

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

Generate FileId in the database and GroupId and FilePath

FileId FileGroupID FilePath
--------------------------------

Like this|||

Quote:

Originally Posted by bootzwiz

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

i offer to have the following entity/table for your application :

1-an entity named "Session " with these fields (SessionID,SessionName,SessionDate ) with SessionID for primary key.

2-an entity named SessionFiles with these fields(SessionID,FilenameWithPath,uploadStatus) with "SessionID,FilenameWithPath" for primary key.

3-make relation for these two table with SessionID field.

4-each form shows one session in your application for uploading the related SessionFiles.

5-Enjoy it.sql

Help Needed: DataSet

Should I create a new dataset in order to populate a multi select parameter
drop down box for users?
I am trying to capture all Companies stored in our backend database as a
drop down selection to users.
Thanks!Hi Terry,
You are correct. Thats how the guy explained it on the Reporting Services
webcasts.
The webcasts are quite informative. I would advise everyone to watch them.
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Should I create a new dataset in order to populate a multi select parameter
> drop down box for users?
> I am trying to capture all Companies stored in our backend database as a
> drop down selection to users.
> Thanks!|||Which webcast are you referring to?
Provide URL or link, if available?
Again, thanks!
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||I am getting an error when I create a new dataset and use it as a parameter.
Error:
[rsMissingDataSetName] The data set name is missing in the data region
â'DataSetNameâ'
CompanyLookup Dataset:
select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
from addresses
where len(rtrim(ADDR_Org_Name_1)) > 0
order by ADDR_Org_Name_1
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||The best thing to do is to go through the webcasts and see what you like.
But the ones I'm reffering to is a 5 day series. They touch on everything.
Follow the URL and scroll down till you see the webcast that starts on 07
March part 1
Very long URL :-)
http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
Hope this helps man...
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Which webcast are you referring to?
> Provide URL or link, if available?
> Again, thanks!
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!|||Thank you for your assistance.
"Stan" wrote:
> The best thing to do is to go through the webcasts and see what you like.
> But the ones I'm reffering to is a 5 day series. They touch on everything.
> Follow the URL and scroll down till you see the webcast that starts on 07
> March part 1
> Very long URL :-)
> http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
> Hope this helps man...
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Which webcast are you referring to?
> >
> > Provide URL or link, if available?
> >
> > Again, thanks!
> >
> > "Stan" wrote:
> >
> > > Hi Terry,
> > > You are correct. Thats how the guy explained it on the Reporting Services
> > > webcasts.
> > > The webcasts are quite informative. I would advise everyone to watch them.
> > >
> > > R
> > > S
> > > --
> > > http://dotnet.org.za/stanley
> > >
> > >
> > > "Terry" wrote:
> > >
> > > > Should I create a new dataset in order to populate a multi select parameter
> > > > drop down box for users?
> > > >
> > > > I am trying to capture all Companies stored in our backend database as a
> > > > drop down selection to users.
> > > >
> > > > Thanks!|||I hope I understand you correctly.
Just to clear things out.
You have a report that takes parameters, but you want the user to select the
parameter from a drop down box. Is this correct?
If that is the case, go to you Data Tab and click on new dataset. Write
your query that will bring back the company names. Now go to your layout
view and click on the report menu. Go to report parameters. Now select on
your company parameter and then click the radio button that says from query.
Select your new dataset in there. When you preview your report the dropdown
box should be populated.
Hope this helps man...
--
http://dotnet.org.za/stanley
"Terry" wrote:
> I am getting an error when I create a new dataset and use it as a parameter.
> Error:
> [rsMissingDataSetName] The data set name is missing in the data region
> â'DataSetNameâ'
> CompanyLookup Dataset:
> select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
> from addresses
> where len(rtrim(ADDR_Org_Name_1)) > 0
> order by ADDR_Org_Name_1
>
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim Wile
Jim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim WileJim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim WileJim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:
> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

help needed to drop a table!!!

hi,

can any one tell me how to drop a table...i tried to delete using the command :

drop table sample;

but it displays an error stating that,

Cannot drop the table 'dbo.sample', because it does not exist or you do not have permission.

pls post the reply asap

-Sweety

To verify that the table does indeed exists, try:

SET ROWCOUNT 1
SELECT * FROM MyTable
SET ROWCOUNT 0

Then if you receive data, and the statement DROP TABLE MyTable again fails, check your permissions. It is possible that your login does not have permission to remove a table.

|||

Make sure you're using the correct db.

USE MyTest
GO
DROP TABLE Test

Adamus

|||

check whether the table exists in the db at all... if exists , see who owns it.... give fully qualified name.

in SQL 2005

select Name from Sys.Objects where name='tablename'

Drop table databasename.schema.Tablename

SQL 2000

select Name from SysObjects where name='tablename'

Drop Table databasename.owner.Tablename

Madhu

|||

May be that table actually does not exist

|||

sweet_salt wrote:

hi,

can any one tell me how to drop a table...i tried to delete using the command :

drop table sample;

but it displays an error stating that,

Cannot drop the table 'dbo.sample', because it does not exist or you do not have permission.

pls post the reply asap

-Sweety

Check that the account that you are using to run this query has the appropriate permission to do so.

Check the user properties in the security node.

help me i have by mistake drop a table in SQL 2005 how recovery

hi ,by mistake we have execute a script (drop table and create table ) on
the good data how recevory all the data?please help me it's very importantrichard wrote:
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important
Hi Richard
Assuming you have a backup schedule, you'll have to restore your latest
FULL backup with the NORECOVERY option. Then apply the log backups also
with the NORECOVERY option. You can then backup you current log file (of
course unless it already has been backed up after the mistake) and then
apply this log file with RECOVERY and STOPAT option. The STOPAT time
should then be just before the "accident" happended.
If you haven't got a valid backup, you're left with getting one of the
log reader tools and then get the data from there.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi Richard
In addition to Steen's reply... if you don't have an up-to-date backup, but
you do have an old one then that could be restored as a different database
and you could re-create your table and transfer the data from the old
database using (say) the INSERT...SELECT statement. If your data has been
updated since the backup then you will have lost those changes. You may also
want to read "Designing a Backup and Restore Strategy" in Books online.
John
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important|||... and here's some thoughts on the subject:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important|||"Steen Persson (DK)" wrote:
> richard wrote:
> > hi ,by mistake we have execute a script (drop table and create table ) on
> > the good data how recevory all the data?please help me it's very important
> Hi Richard
> Assuming you have a backup schedule, you'll have to restore your latest
> FULL backup with the NORECOVERY option. Then apply the log backups also
> with the NORECOVERY option. You can then backup you current log file (of
> course unless it already has been backed up after the mistake) and then
> apply this log file with RECOVERY and STOPAT option. The STOPAT time
> should then be just before the "accident" happended.
> If you haven't got a valid backup, you're left with getting one of the
> log reader tools and then get the data from there.
>
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
> thanks dear steen,
we are trying the step one: your latest
> FULL backup with the NORECOVERY option
it seems to take very long time (database' name restoring...) does it
normally?|||Hi Steen,
Further to richard's post please note that in this case after analysing the
transaction logs, surprisingly enough we cannot see the transaction entries
for "drop statements", so please suggest how can we recover the data from the
data file (.mdf).
- Joy
"Steen Persson (DK)" wrote:
> richard wrote:
> > hi ,by mistake we have execute a script (drop table and create table ) on
> > the good data how recevory all the data?please help me it's very important
> Hi Richard
> Assuming you have a backup schedule, you'll have to restore your latest
> FULL backup with the NORECOVERY option. Then apply the log backups also
> with the NORECOVERY option. You can then backup you current log file (of
> course unless it already has been backed up after the mistake) and then
> apply this log file with RECOVERY and STOPAT option. The STOPAT time
> should then be just before the "accident" happended.
> If you haven't got a valid backup, you're left with getting one of the
> log reader tools and then get the data from there.
>
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>|||richard wrote:
> "Steen Persson (DK)" wrote:
>
>>richard wrote:
>>hi ,by mistake we have execute a script (drop table and create table ) on
>>the good data how recevory all the data?please help me it's very important
>>Hi Richard
>>Assuming you have a backup schedule, you'll have to restore your latest
>>FULL backup with the NORECOVERY option. Then apply the log backups also
>>with the NORECOVERY option. You can then backup you current log file (of
>>course unless it already has been backed up after the mistake) and then
>>apply this log file with RECOVERY and STOPAT option. The STOPAT time
>>should then be just before the "accident" happended.
>>If you haven't got a valid backup, you're left with getting one of the
>>log reader tools and then get the data from there.
>>
>>--
>>Regards
>>Steen Schlüter Persson
>>Database Administrator / System Administrator
>>thanks dear steen,
> we are trying the step one: your latest
>>FULL backup with the NORECOVERY option
> it seems to take very long time (database' name restoring...) does it
> normally?
Hi Richard
When you restore with the NORECOVERY option, the database will not be
operational until you restore the following logfiles where the last one
will have to be with the RECOVERY option. This option tells the database
that no more data will be restored and the database will be operational.
You can read up on RESTORE options in Books On Line - here're also some
explanation to each option.
Regards
Steen|||Joy wrote:
> Hi Steen,
> Further to richard's post please note that in this case after analysing the
> transaction logs, surprisingly enough we cannot see the transaction entries
> for "drop statements", so please suggest how can we recover the data from the
> data file (.mdf).
> - Joy
Hi Joy
I'm not very good at the different log reader tools, but I'm sure you
should be able to see the delete transactions. How much of it that is
logged though depends on how you've deleted the data (i.e. what sql code
did you run?).
When data is deleted, there are no way to recover it from the mdf file.
You can try John's suggestion which was to restore your most recent
backup to a different database and then take the data from there. That
will of course require that the data hasn't changed since then.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

help me i have by mistake drop a table in SQL 2005 how recovery

hi ,by mistake we have execute a script (drop table and create table ) on
the good data how recevory all the data?please help me it's very importantrichard wrote:
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important
Hi Richard
Assuming you have a backup schedule, you'll have to restore your latest
FULL backup with the NORECOVERY option. Then apply the log backups also
with the NORECOVERY option. You can then backup you current log file (of
course unless it already has been backed up after the mistake) and then
apply this log file with RECOVERY and STOPAT option. The STOPAT time
should then be just before the "accident" happended.
If you haven't got a valid backup, you're left with getting one of the
log reader tools and then get the data from there.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi Richard
In addition to Steen's reply... if you don't have an up-to-date backup, but
you do have an old one then that could be restored as a different database
and you could re-create your table and transfer the data from the old
database using (say) the INSERT...SELECT statement. If your data has been
updated since the backup then you will have lost those changes. You may also
want to read "Designing a Backup and Restore Strategy" in Books online.
John
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important|||... and here's some thoughts on the subject:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important