Attaach 2000 data file from network drive

Hi
How can I attach mdf file without copying it to local disk.
I dont have enough space on the server which I have to attach db. But there
is enough space on an other server in the network.
Thanks
BanuYou will need to set this trace flag to enable
dbcc traceon(1807)
Be aware that this isn't entirley supported by Microsoft and any network
delays can lead to corruption. Also performance will suffer...
HTH. Ryan
"Banu_tr" <abuslu@.hotmail.com> wrote in message
news:D8AB0981-FBDF-479F-B09E-D5B1A5289D60@.microsoft.com...
> Hi
> How can I attach mdf file without copying it to local disk.
> I dont have enough space on the server which I have to attach db. But
> there
> is enough space on an other server in the network.
> Thanks
> Banu|||Dear Ryan,
I execute the dbcc traceon(1807) . But still couln't see the mapped network
drive when I try to attach the mdf file.
What else should I do?
"Ryan" wrote:

> You will need to set this trace flag to enable
> dbcc traceon(1807)
> Be aware that this isn't entirley supported by Microsoft and any network
> delays can lead to corruption. Also performance will suffer...
> --
> HTH. Ryan
> "Banu_tr" <abuslu@.hotmail.com> wrote in message
> news:D8AB0981-FBDF-479F-B09E-D5B1A5289D60@.microsoft.com...
>
>|||Banu_tr wrote:
> Dear Ryan,
> I execute the dbcc traceon(1807) . But still couln't see the mapped networ
k
> drive when I try to attach the mdf file.
> What else should I do?
> "Ryan" wrote:
Make sure you refer to the network path with the UNC name rather than a
drive letter. Like:
EXEC sp_attach_db 'JUNK',
'\\SERVER\share\MSSQL\data\JUNK.mdf',
'\\SERVER\share\MSSQL\data\JUNK_log.LDF'
or:
EXEC sp_attach_db 'JUNK',
'\\SERVER\C$\MSSQL\data\JUNK.mdf',
'\\SERVER\C$\MSSQL\data\JUNK_log.LDF'
IMO running a database from a network drive is a near suicidal thing to
do if you care about the integrity or availability of your data. Do
this for a READ-ONLY or SINGLE USER database only. Make sure you have a
current backup BEFORE you attempt it. Don't expect decent performance
or reliability.
Read:
http://support.microsoft.com/defaul...kb;en-us;304261
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
--|||Dear David,
I still got the message "Device Activation Error"
any advice?
"David Portas" wrote:

> Banu_tr wrote:
> Make sure you refer to the network path with the UNC name rather than a
> drive letter. Like:
> EXEC sp_attach_db 'JUNK',
> '\\SERVER\share\MSSQL\data\JUNK.mdf',
> '\\SERVER\share\MSSQL\data\JUNK_log.LDF'
> or:
> EXEC sp_attach_db 'JUNK',
> '\\SERVER\C$\MSSQL\data\JUNK.mdf',
> '\\SERVER\C$\MSSQL\data\JUNK_log.LDF'
> IMO running a database from a network drive is a near suicidal thing to
> do if you care about the integrity or availability of your data. Do
> this for a READ-ONLY or SINGLE USER database only. Make sure you have a
> current backup BEFORE you attempt it. Don't expect decent performance
> or reliability.
> Read:
> http://support.microsoft.com/defaul...kb;en-us;304261
> --
> 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
> --
>|||Banu_tr wrote:
> Dear David,
> I still got the message "Device Activation Error"
> any advice?
>
Are you sure the server has access to the share you are trying to use?
Same advice as before: don't do it. Not for production use anyway. Tell
your boss, customer or business owner that they need to purchase some
more storage.
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
--|||Banu_tr wrote:
> Dear David,
> I still got the message "Device Activation Error"
> any advice?
>
You need to check that the account that the SQL Server service runs
under can access the share where your data files are located. If SQL is
running under the service account then you need to change it to run as
a domain login, give that login the necessary permissions, then stop
and restart the SQL server service. Having done that, it should work
but as already indicated this is not a fully supported option and never
having tried it I don't know under what circumstances it might not work
at all.
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
--

assign variable value in Exists subquery

Hi
How to assign a variable value from if exist ?
like
declare @.i int
if exits( select @.i = ID from table1 where ID = 100)
-- do sth
but I always get an error
Thanks a lot for helpingAnn
You cannot do in that way.
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||Ann,
Posting the actual error would help.
Does it have to be in a subquery?
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
is easy to read/follow.
Regards
AJ
"Ann" <Ann@.discussions.microsoft.com> wrote in message news:14A79DE3-EC8D-45D9-8554-F96AF32
956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||declare @.i int
SET @.i = ( select ID from table1 where ID = 100)
if @.i IS NOT NULL ......
Although I assume you can do whatever you want to do probably simpler with a
join instead of an IF, but for that you would have to post the rest of your
code.
Jacco Schalkwijk
SQL Server MVP
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||My problem is
I have two tables
Product
Product_ID Product_Name
100 Apple
101 Peach
102 Banana
Order
Product_ID Customer_ID Quantity
100 1 5
101 1 6
Now I need to generate a report with every product and every customer. The
problem is that if nobody purchases Banana(which is 102),I need to insert
null
so it will look like
Customer_ID Product_ID Quantity
1 100 5
1 101 6
1 102 NULL
IF EXISTS(
SELECT * FROM Order WHERE Customer_ID =1) INSERT INTO
#temp(Customer_ID ,Product_ID , Quantity) SELECT Customer_ID ,Product_ID,
Quantity FROM Order WHERE Customer_ID = 1
ELSE
INSERT INTO #temp(Customer_ID ,Product_ID ,
Quantity) VALUES(1,Product_ID,NULL) -- suppose only one product here
If I use
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
I won't get 102(banana) in here
If I user
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
I'd have to select twice,that's why I am asking if possible,I can assign a
value in if exists
Thanks everyone
"Jacco Schalkwijk" wrote:

> declare @.i int
> SET @.i = ( select ID from table1 where ID = 100)
> if @.i IS NOT NULL ......
> Although I assume you can do whatever you want to do probably simpler with
a
> join instead of an IF, but for that you would have to post the rest of you
r
> code.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
>
>|||I assume you have a Customers table as well? Try:
SELECT C.customer_id, P.product_id,
COALESCE(SUM(quantity),0) AS quantity
FROM Customers AS C
CROSS JOIN Products AS P
LEFT JOIN Orders AS O
ON C.customer_id = O.customer_id
AND P.product_id = O.product_id
AND O.orderid = 10249
GROUP BY C.customer_id, P.product_id
I would think that the Orders table is denormalized if it has both the
order number and the customer id. Doesn't Order determine Customer?
David Portas
SQL Server MVP
--|||Thanks a lot,that 's what I need
"David Portas" wrote:

> I assume you have a Customers table as well? Try:
> SELECT C.customer_id, P.product_id,
> COALESCE(SUM(quantity),0) AS quantity
> FROM Customers AS C
> CROSS JOIN Products AS P
> LEFT JOIN Orders AS O
> ON C.customer_id = O.customer_id
> AND P.product_id = O.product_id
> AND O.orderid = 10249
> GROUP BY C.customer_id, P.product_id
> I would think that the Orders table is denormalized if it has both the
> order number and the customer id. Doesn't Order determine Customer?
> --
> David Portas
> SQL Server MVP
> --
>