No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No Access to mapped drives

Hi,
I have a Wndows 2003 server running SQL Server (SP 3a).
My problem: Neither via Enterprise Manager nor T-SQL is it possible to do a
backup / restore on a mapped network drive. Using Query Analyzer, an error
shows up saying "Backup medium <xyz> cannot be opened. Media error or medium
offline", using Enterprise Manager, no network drive is being shown at all.
Strange enough, it is possible to list and access all network drives in the
(file-) explorer from this server. I do not consider this to be a security
issue, as the sql server runs under the domain administrator's account
having all required permissions. The network drives are assigned to
different servers, all of which belong to the same domain as the sql server.
The domain administrator is permanently logged on the sql server so the
network drives are present all the time.
It makes no difference if I run the Enterprise Manager directly on the sql
server or from a different machine. It also makes no difference if the
connection to the sql server is established using Integrated Security or sql
server's sa account.
There is another sql server in our domain which is configured identically,
apart from running under Win 2000 Server. This one has no problems accessing
any network drives...
For different reasons I do want to use a mapped drive instead of an UNC
path, so I hope someone can help me )
Thanks in advance,
Olaf
Hi,
Start the SQL Server service using a Domain user which has got rights in
remote server share. After that you will be able to access the remot path
from query analyzer.
backup database dbname to disk='\\remoteserver\backupshare\dbname.bak' with
init
Note:-
Enterprise manager will not show the mapped drives
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a Wndows 2003 server running SQL Server (SP 3a).
> My problem: Neither via Enterprise Manager nor T-SQL is it possible to do
a
> backup / restore on a mapped network drive. Using Query Analyzer, an error
> shows up saying "Backup medium <xyz> cannot be opened. Media error or
medium
> offline", using Enterprise Manager, no network drive is being shown at
all.
> Strange enough, it is possible to list and access all network drives in
the
> (file-) explorer from this server. I do not consider this to be a security
> issue, as the sql server runs under the domain administrator's account
> having all required permissions. The network drives are assigned to
> different servers, all of which belong to the same domain as the sql
server.
> The domain administrator is permanently logged on the sql server so the
> network drives are present all the time.
> It makes no difference if I run the Enterprise Manager directly on the sql
> server or from a different machine. It also makes no difference if the
> connection to the sql server is established using Integrated Security or
sql
> server's sa account.
> There is another sql server in our domain which is configured identically,
> apart from running under Win 2000 Server. This one has no problems
accessing
> any network drives...
> For different reasons I do want to use a mapped drive instead of an UNC
> path, so I hope someone can help me )
> Thanks in advance,
> Olaf
>
|||Hari,
thanks for the quick reply, but:
the sql server account does have full permissions as it is the domain admin
account. Also, I do not want to use an UNC path, but a mapped drive. The
other sql server I mentioned does show the mapped drives in Enterprise
Manager.
Anyway, thx
Olaf
"Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:%23z%23S4OtUEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Start the SQL Server service using a Domain user which has got rights in
> remote server share. After that you will be able to access the remot path
> from query analyzer.
> backup database dbname to disk='\\remoteserver\backupshare\dbname.bak'
with[vbcol=seagreen]
> init
> Note:-
> Enterprise manager will not show the mapped drives
>
> --
> Thanks
> Hari
> MCDBA
> "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
do[vbcol=seagreen]
> a
error[vbcol=seagreen]
> medium
> all.
> the
security[vbcol=seagreen]
> server.
sql[vbcol=seagreen]
> sql
identically,
> accessing
>
|||Hi,
Can you try this from query analyzer:-
xp_cmdshell 'net use k: \\remoteserver\share'
(No need of specifying user name and password , becuase u are starting sql
server in domain admin user)
The above command will map the drive k:, now try
Backup database dbname to disk='k:\dbname.bak' with init
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:e0jYRTtUEHA.2504@.TK2MSFTNGP11.phx.gbl...
> Hari,
> thanks for the quick reply, but:
> the sql server account does have full permissions as it is the domain
admin[vbcol=seagreen]
> account. Also, I do not want to use an UNC path, but a mapped drive. The
> other sql server I mentioned does show the mapped drives in Enterprise
> Manager.
> Anyway, thx
> Olaf
> "Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> news:%23z%23S4OtUEHA.2388@.TK2MSFTNGP09.phx.gbl...
path[vbcol=seagreen]
> with
> do
> error
in[vbcol=seagreen]
> security
the[vbcol=seagreen]
> sql
or[vbcol=seagreen]
> identically,
UNC
>
|||Hi,
yes, this works fine, thanks.
Anyway, I am wondering why the other sql server in our domain can access the
mapped drives without doing anything. Maybe this is a bug ..?
Thanks,
Olaf
"Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uBh4gbtUEHA.556@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> Can you try this from query analyzer:-
> xp_cmdshell 'net use k: \\remoteserver\share'
> (No need of specifying user name and password , becuase u are starting sql
> server in domain admin user)
> The above command will map the drive k:, now try
> Backup database dbname to disk='k:\dbname.bak' with init
> --
> Thanks
> Hari
> MCDBA
> "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> news:e0jYRTtUEHA.2504@.TK2MSFTNGP11.phx.gbl...
> admin
in[vbcol=seagreen]
> path
to[vbcol=seagreen]
or[vbcol=seagreen]
at[vbcol=seagreen]
> in
account[vbcol=seagreen]
> the
the[vbcol=seagreen]
the[vbcol=seagreen]
Security
> or
> UNC
>
|||Hi,
Thats good. I am not sure about the error.
Are you running this command from server or from a client PC using a Query
analyzer. If it is client PC you need to
map the drive from query analyzer. If you are executing from server that
command is suppose to work directly.
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:espbrftUEHA.1036@.TK2MSFTNGP12.phx.gbl...
> Hi,
> yes, this works fine, thanks.
> Anyway, I am wondering why the other sql server in our domain can access
the[vbcol=seagreen]
> mapped drives without doing anything. Maybe this is a bug ..?
> Thanks,
> Olaf
> "Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> news:uBh4gbtUEHA.556@.tk2msftngp13.phx.gbl...
sql[vbcol=seagreen]
The[vbcol=seagreen]
rights[vbcol=seagreen]
> in
disk='\\remoteserver\backupshare\dbname.bak'[vbcol=seagreen]
possible[vbcol=seagreen]
> to
an[vbcol=seagreen]
> or
shown[vbcol=seagreen]
> at
drives[vbcol=seagreen]
> account
to[vbcol=seagreen]
sql[vbcol=seagreen]
so[vbcol=seagreen]
> the
> the
> Security
an
>

No Access to mapped drives

Hi,
I have a Wndows 2003 server running SQL Server (SP 3a).
My problem: Neither via Enterprise Manager nor T-SQL is it possible to do a
backup / restore on a mapped network drive. Using Query Analyzer, an error
shows up saying "Backup medium <xyz> cannot be opened. Media error or medium
offline", using Enterprise Manager, no network drive is being shown at all.
Strange enough, it is possible to list and access all network drives in the
(file-) explorer from this server. I do not consider this to be a security
issue, as the sql server runs under the domain administrator's account
having all required permissions. The network drives are assigned to
different servers, all of which belong to the same domain as the sql server.
The domain administrator is permanently logged on the sql server so the
network drives are present all the time.
It makes no difference if I run the Enterprise Manager directly on the sql
server or from a different machine. It also makes no difference if the
connection to the sql server is established using Integrated Security or sql
server's sa account.
There is another sql server in our domain which is configured identically,
apart from running under Win 2000 Server. This one has no problems accessing
any network drives...
For different reasons I do want to use a mapped drive instead of an UNC
path, so I hope someone can help me )
Thanks in advance,
OlafHi,
Start the SQL Server service using a Domain user which has got rights in
remote server share. After that you will be able to access the remot path
from query analyzer.
backup database dbname to disk='\\remoteserver\backupshare\dbname.bak' with
init
Note:-
Enterprise manager will not show the mapped drives
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a Wndows 2003 server running SQL Server (SP 3a).
> My problem: Neither via Enterprise Manager nor T-SQL is it possible to do
a
> backup / restore on a mapped network drive. Using Query Analyzer, an error
> shows up saying "Backup medium <xyz> cannot be opened. Media error or
medium
> offline", using Enterprise Manager, no network drive is being shown at
all.
> Strange enough, it is possible to list and access all network drives in
the
> (file-) explorer from this server. I do not consider this to be a security
> issue, as the sql server runs under the domain administrator's account
> having all required permissions. The network drives are assigned to
> different servers, all of which belong to the same domain as the sql
server.
> The domain administrator is permanently logged on the sql server so the
> network drives are present all the time.
> It makes no difference if I run the Enterprise Manager directly on the sql
> server or from a different machine. It also makes no difference if the
> connection to the sql server is established using Integrated Security or
sql
> server's sa account.
> There is another sql server in our domain which is configured identically,
> apart from running under Win 2000 Server. This one has no problems
accessing
> any network drives...
> For different reasons I do want to use a mapped drive instead of an UNC
> path, so I hope someone can help me )
> Thanks in advance,
> Olaf
>

No Access to mapped drives

Hi,
I have a Wndows 2003 server running SQL Server (SP 3a).
My problem: Neither via Enterprise Manager nor T-SQL is it possible to do a
backup / restore on a mapped network drive. Using Query Analyzer, an error
shows up saying "Backup medium <xyz> cannot be opened. Media error or medium
offline", using Enterprise Manager, no network drive is being shown at all.
Strange enough, it is possible to list and access all network drives in the
(file-) explorer from this server. I do not consider this to be a security
issue, as the sql server runs under the domain administrator's account
having all required permissions. The network drives are assigned to
different servers, all of which belong to the same domain as the sql server.
The domain administrator is permanently logged on the sql server so the
network drives are present all the time.
It makes no difference if I run the Enterprise Manager directly on the sql
server or from a different machine. It also makes no difference if the
connection to the sql server is established using Integrated Security or sql
server's sa account.
There is another sql server in our domain which is configured identically,
apart from running under Win 2000 Server. This one has no problems accessing
any network drives...
For different reasons I do want to use a mapped drive instead of an UNC
path, so I hope someone can help me )
Thanks in advance,
OlafHi,
Start the SQL Server service using a Domain user which has got rights in
remote server share. After that you will be able to access the remot path
from query analyzer.
backup database dbname to disk='\\remoteserver\backupshare\dbname.bak' with
init
Note:-
Enterprise manager will not show the mapped drives
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a Wndows 2003 server running SQL Server (SP 3a).
> My problem: Neither via Enterprise Manager nor T-SQL is it possible to do
a
> backup / restore on a mapped network drive. Using Query Analyzer, an error
> shows up saying "Backup medium <xyz> cannot be opened. Media error or
medium
> offline", using Enterprise Manager, no network drive is being shown at
all.
> Strange enough, it is possible to list and access all network drives in
the
> (file-) explorer from this server. I do not consider this to be a security
> issue, as the sql server runs under the domain administrator's account
> having all required permissions. The network drives are assigned to
> different servers, all of which belong to the same domain as the sql
server.
> The domain administrator is permanently logged on the sql server so the
> network drives are present all the time.
> It makes no difference if I run the Enterprise Manager directly on the sql
> server or from a different machine. It also makes no difference if the
> connection to the sql server is established using Integrated Security or
sql
> server's sa account.
> There is another sql server in our domain which is configured identically,
> apart from running under Win 2000 Server. This one has no problems
accessing
> any network drives...
> For different reasons I do want to use a mapped drive instead of an UNC
> path, so I hope someone can help me )
> Thanks in advance,
> Olaf
>|||Hari,
thanks for the quick reply, but:
the sql server account does have full permissions as it is the domain admin
account. Also, I do not want to use an UNC path, but a mapped drive. The
other sql server I mentioned does show the mapped drives in Enterprise
Manager.
Anyway, thx
Olaf
"Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:%23z%23S4OtUEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Start the SQL Server service using a Domain user which has got rights in
> remote server share. After that you will be able to access the remot path
> from query analyzer.
> backup database dbname to disk='\\remoteserver\backupshare\dbname.bak'
with
> init
> Note:-
> Enterprise manager will not show the mapped drives
>
> --
> Thanks
> Hari
> MCDBA
> "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
do[vbcol=seagreen]
> a
error[vbcol=seagreen]
> medium
> all.
> the
security[vbcol=seagreen]
> server.
sql[vbcol=seagreen]
> sql
identically,[vbcol=seagreen]
> accessing
>|||Hi,
Can you try this from query analyzer:-
xp_cmdshell 'net use k: \\remoteserver\share'
(No need of specifying user name and password , becuase u are starting sql
server in domain admin user)
The above command will map the drive k:, now try
Backup database dbname to disk='k:\dbname.bak' with init
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:e0jYRTtUEHA.2504@.TK2MSFTNGP11.phx.gbl...
> Hari,
> thanks for the quick reply, but:
> the sql server account does have full permissions as it is the domain
admin
> account. Also, I do not want to use an UNC path, but a mapped drive. The
> other sql server I mentioned does show the mapped drives in Enterprise
> Manager.
> Anyway, thx
> Olaf
> "Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> news:%23z%23S4OtUEHA.2388@.TK2MSFTNGP09.phx.gbl...
path[vbcol=seagreen]
> with
> do
> error
in[vbcol=seagreen]
> security
the[vbcol=seagreen]
> sql
or[vbcol=seagreen]
> identically,
UNC[vbcol=seagreen]
>|||Hi,
yes, this works fine, thanks.
Anyway, I am wondering why the other sql server in our domain can access the
mapped drives without doing anything. Maybe this is a bug ..?
Thanks,
Olaf
"Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uBh4gbtUEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> Can you try this from query analyzer:-
> xp_cmdshell 'net use k: \\remoteserver\share'
> (No need of specifying user name and password , becuase u are starting sql
> server in domain admin user)
> The above command will map the drive k:, now try
> Backup database dbname to disk='k:\dbname.bak' with init
> --
> Thanks
> Hari
> MCDBA
> "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> news:e0jYRTtUEHA.2504@.TK2MSFTNGP11.phx.gbl...
> admin
in[vbcol=seagreen]
> path
to[vbcol=seagreen]
or[vbcol=seagreen]
at[vbcol=seagreen]
> in
account[vbcol=seagreen]
> the
the[vbcol=seagreen]
the[vbcol=seagreen]
Security[vbcol=seagreen]
> or
> UNC
>|||Hi,
Thats good. I am not sure about the error.
Are you running this command from server or from a client PC using a Query
analyzer. If it is client PC you need to
map the drive from query analyzer. If you are executing from server that
command is suppose to work directly.
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:espbrftUEHA.1036@.TK2MSFTNGP12.phx.gbl...
> Hi,
> yes, this works fine, thanks.
> Anyway, I am wondering why the other sql server in our domain can access
the
> mapped drives without doing anything. Maybe this is a bug ..?
> Thanks,
> Olaf
> "Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> news:uBh4gbtUEHA.556@.tk2msftngp13.phx.gbl...
sql[vbcol=seagreen]
The[vbcol=seagreen]
rights[vbcol=seagreen]
> in
disk='\\remoteserver\backupshare\dbname.bak'[vbcol=seagreen]
possible[vbcol=seagreen]
> to
an[vbcol=seagreen]
> or
shown[vbcol=seagreen]
> at
drives[vbcol=seagreen]
> account
to[vbcol=seagreen]
sql[vbcol=seagreen]
so[vbcol=seagreen]
> the
> the
> Security
an[vbcol=seagreen]
>

No Access to mapped drives

Hi,
I have a Wíndows 2003 server running SQL Server (SP 3a).
My problem: Neither via Enterprise Manager nor T-SQL is it possible to do a
backup / restore on a mapped network drive. Using Query Analyzer, an error
shows up saying "Backup medium <xyz> cannot be opened. Media error or medium
offline", using Enterprise Manager, no network drive is being shown at all.
Strange enough, it is possible to list and access all network drives in the
(file-) explorer from this server. I do not consider this to be a security
issue, as the sql server runs under the domain administrator's account
having all required permissions. The network drives are assigned to
different servers, all of which belong to the same domain as the sql server.
The domain administrator is permanently logged on the sql server so the
network drives are present all the time.
It makes no difference if I run the Enterprise Manager directly on the sql
server or from a different machine. It also makes no difference if the
connection to the sql server is established using Integrated Security or sql
server's sa account.
There is another sql server in our domain which is configured identically,
apart from running under Win 2000 Server. This one has no problems accessing
any network drives...
For different reasons I do want to use a mapped drive instead of an UNC
path, so I hope someone can help me :))
Thanks in advance,
OlafHi,
Start the SQL Server service using a Domain user which has got rights in
remote server share. After that you will be able to access the remot path
from query analyzer.
backup database dbname to disk='\\remoteserver\backupshare\dbname.bak' with
init
Note:-
Enterprise manager will not show the mapped drives
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a Wíndows 2003 server running SQL Server (SP 3a).
> My problem: Neither via Enterprise Manager nor T-SQL is it possible to do
a
> backup / restore on a mapped network drive. Using Query Analyzer, an error
> shows up saying "Backup medium <xyz> cannot be opened. Media error or
medium
> offline", using Enterprise Manager, no network drive is being shown at
all.
> Strange enough, it is possible to list and access all network drives in
the
> (file-) explorer from this server. I do not consider this to be a security
> issue, as the sql server runs under the domain administrator's account
> having all required permissions. The network drives are assigned to
> different servers, all of which belong to the same domain as the sql
server.
> The domain administrator is permanently logged on the sql server so the
> network drives are present all the time.
> It makes no difference if I run the Enterprise Manager directly on the sql
> server or from a different machine. It also makes no difference if the
> connection to the sql server is established using Integrated Security or
sql
> server's sa account.
> There is another sql server in our domain which is configured identically,
> apart from running under Win 2000 Server. This one has no problems
accessing
> any network drives...
> For different reasons I do want to use a mapped drive instead of an UNC
> path, so I hope someone can help me :))
> Thanks in advance,
> Olaf
>|||Hari,
thanks for the quick reply, but:
the sql server account does have full permissions as it is the domain admin
account. Also, I do not want to use an UNC path, but a mapped drive. The
other sql server I mentioned does show the mapped drives in Enterprise
Manager.
Anyway, thx
Olaf
"Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:%23z%23S4OtUEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Start the SQL Server service using a Domain user which has got rights in
> remote server share. After that you will be able to access the remot path
> from query analyzer.
> backup database dbname to disk='\\remoteserver\backupshare\dbname.bak'
with
> init
> Note:-
> Enterprise manager will not show the mapped drives
>
> --
> Thanks
> Hari
> MCDBA
> "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > I have a Wíndows 2003 server running SQL Server (SP 3a).
> > My problem: Neither via Enterprise Manager nor T-SQL is it possible to
do
> a
> > backup / restore on a mapped network drive. Using Query Analyzer, an
error
> > shows up saying "Backup medium <xyz> cannot be opened. Media error or
> medium
> > offline", using Enterprise Manager, no network drive is being shown at
> all.
> > Strange enough, it is possible to list and access all network drives in
> the
> > (file-) explorer from this server. I do not consider this to be a
security
> > issue, as the sql server runs under the domain administrator's account
> > having all required permissions. The network drives are assigned to
> > different servers, all of which belong to the same domain as the sql
> server.
> > The domain administrator is permanently logged on the sql server so the
> > network drives are present all the time.
> > It makes no difference if I run the Enterprise Manager directly on the
sql
> > server or from a different machine. It also makes no difference if the
> > connection to the sql server is established using Integrated Security or
> sql
> > server's sa account.
> > There is another sql server in our domain which is configured
identically,
> > apart from running under Win 2000 Server. This one has no problems
> accessing
> > any network drives...
> > For different reasons I do want to use a mapped drive instead of an UNC
> > path, so I hope someone can help me :))
> > Thanks in advance,
> > Olaf
> >
> >
>|||Hi,
Can you try this from query analyzer:-
xp_cmdshell 'net use k: \\remoteserver\share'
(No need of specifying user name and password , becuase u are starting sql
server in domain admin user)
The above command will map the drive k:, now try
Backup database dbname to disk='k:\dbname.bak' with init
--
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:e0jYRTtUEHA.2504@.TK2MSFTNGP11.phx.gbl...
> Hari,
> thanks for the quick reply, but:
> the sql server account does have full permissions as it is the domain
admin
> account. Also, I do not want to use an UNC path, but a mapped drive. The
> other sql server I mentioned does show the mapped drives in Enterprise
> Manager.
> Anyway, thx
> Olaf
> "Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> news:%23z%23S4OtUEHA.2388@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Start the SQL Server service using a Domain user which has got rights in
> > remote server share. After that you will be able to access the remot
path
> > from query analyzer.
> >
> > backup database dbname to disk='\\remoteserver\backupshare\dbname.bak'
> with
> > init
> >
> > Note:-
> >
> > Enterprise manager will not show the mapped drives
> >
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> > news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > > I have a Wíndows 2003 server running SQL Server (SP 3a).
> > > My problem: Neither via Enterprise Manager nor T-SQL is it possible to
> do
> > a
> > > backup / restore on a mapped network drive. Using Query Analyzer, an
> error
> > > shows up saying "Backup medium <xyz> cannot be opened. Media error or
> > medium
> > > offline", using Enterprise Manager, no network drive is being shown at
> > all.
> > > Strange enough, it is possible to list and access all network drives
in
> > the
> > > (file-) explorer from this server. I do not consider this to be a
> security
> > > issue, as the sql server runs under the domain administrator's account
> > > having all required permissions. The network drives are assigned to
> > > different servers, all of which belong to the same domain as the sql
> > server.
> > > The domain administrator is permanently logged on the sql server so
the
> > > network drives are present all the time.
> > > It makes no difference if I run the Enterprise Manager directly on the
> sql
> > > server or from a different machine. It also makes no difference if the
> > > connection to the sql server is established using Integrated Security
or
> > sql
> > > server's sa account.
> > > There is another sql server in our domain which is configured
> identically,
> > > apart from running under Win 2000 Server. This one has no problems
> > accessing
> > > any network drives...
> > > For different reasons I do want to use a mapped drive instead of an
UNC
> > > path, so I hope someone can help me :))
> > > Thanks in advance,
> > > Olaf
> > >
> > >
> >
> >
>|||Hi,
yes, this works fine, thanks.
Anyway, I am wondering why the other sql server in our domain can access the
mapped drives without doing anything. Maybe this is a bug ..?
Thanks,
Olaf
"Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uBh4gbtUEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> Can you try this from query analyzer:-
> xp_cmdshell 'net use k: \\remoteserver\share'
> (No need of specifying user name and password , becuase u are starting sql
> server in domain admin user)
> The above command will map the drive k:, now try
> Backup database dbname to disk='k:\dbname.bak' with init
> --
> Thanks
> Hari
> MCDBA
> "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> news:e0jYRTtUEHA.2504@.TK2MSFTNGP11.phx.gbl...
> > Hari,
> > thanks for the quick reply, but:
> > the sql server account does have full permissions as it is the domain
> admin
> > account. Also, I do not want to use an UNC path, but a mapped drive. The
> > other sql server I mentioned does show the mapped drives in Enterprise
> > Manager.
> > Anyway, thx
> > Olaf
> >
> > "Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> > news:%23z%23S4OtUEHA.2388@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > Start the SQL Server service using a Domain user which has got rights
in
> > > remote server share. After that you will be able to access the remot
> path
> > > from query analyzer.
> > >
> > > backup database dbname to disk='\\remoteserver\backupshare\dbname.bak'
> > with
> > > init
> > >
> > > Note:-
> > >
> > > Enterprise manager will not show the mapped drives
> > >
> > >
> > > --
> > > Thanks
> > > Hari
> > > MCDBA
> > > "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> > > news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> > > > Hi,
> > > > I have a Wíndows 2003 server running SQL Server (SP 3a).
> > > > My problem: Neither via Enterprise Manager nor T-SQL is it possible
to
> > do
> > > a
> > > > backup / restore on a mapped network drive. Using Query Analyzer, an
> > error
> > > > shows up saying "Backup medium <xyz> cannot be opened. Media error
or
> > > medium
> > > > offline", using Enterprise Manager, no network drive is being shown
at
> > > all.
> > > > Strange enough, it is possible to list and access all network drives
> in
> > > the
> > > > (file-) explorer from this server. I do not consider this to be a
> > security
> > > > issue, as the sql server runs under the domain administrator's
account
> > > > having all required permissions. The network drives are assigned to
> > > > different servers, all of which belong to the same domain as the sql
> > > server.
> > > > The domain administrator is permanently logged on the sql server so
> the
> > > > network drives are present all the time.
> > > > It makes no difference if I run the Enterprise Manager directly on
the
> > sql
> > > > server or from a different machine. It also makes no difference if
the
> > > > connection to the sql server is established using Integrated
Security
> or
> > > sql
> > > > server's sa account.
> > > > There is another sql server in our domain which is configured
> > identically,
> > > > apart from running under Win 2000 Server. This one has no problems
> > > accessing
> > > > any network drives...
> > > > For different reasons I do want to use a mapped drive instead of an
> UNC
> > > > path, so I hope someone can help me :))
> > > > Thanks in advance,
> > > > Olaf
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi,
Thats good. I am not sure about the error.
Are you running this command from server or from a client PC using a Query
analyzer. If it is client PC you need to
map the drive from query analyzer. If you are executing from server that
command is suppose to work directly.
Thanks
Hari
MCDBA
"Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
news:espbrftUEHA.1036@.TK2MSFTNGP12.phx.gbl...
> Hi,
> yes, this works fine, thanks.
> Anyway, I am wondering why the other sql server in our domain can access
the
> mapped drives without doing anything. Maybe this is a bug ..?
> Thanks,
> Olaf
> "Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> news:uBh4gbtUEHA.556@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Can you try this from query analyzer:-
> >
> > xp_cmdshell 'net use k: \\remoteserver\share'
> >
> > (No need of specifying user name and password , becuase u are starting
sql
> > server in domain admin user)
> >
> > The above command will map the drive k:, now try
> >
> > Backup database dbname to disk='k:\dbname.bak' with init
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> > news:e0jYRTtUEHA.2504@.TK2MSFTNGP11.phx.gbl...
> > > Hari,
> > > thanks for the quick reply, but:
> > > the sql server account does have full permissions as it is the domain
> > admin
> > > account. Also, I do not want to use an UNC path, but a mapped drive.
The
> > > other sql server I mentioned does show the mapped drives in Enterprise
> > > Manager.
> > > Anyway, thx
> > > Olaf
> > >
> > > "Hari" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
> > > news:%23z%23S4OtUEHA.2388@.TK2MSFTNGP09.phx.gbl...
> > > > Hi,
> > > >
> > > > Start the SQL Server service using a Domain user which has got
rights
> in
> > > > remote server share. After that you will be able to access the remot
> > path
> > > > from query analyzer.
> > > >
> > > > backup database dbname to
disk='\\remoteserver\backupshare\dbname.bak'
> > > with
> > > > init
> > > >
> > > > Note:-
> > > >
> > > > Enterprise manager will not show the mapped drives
> > > >
> > > >
> > > > --
> > > > Thanks
> > > > Hari
> > > > MCDBA
> > > > "Olaf Gebert" <OGebert@.Eurocamp.de> wrote in message
> > > > news:eyyKXLtUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> > > > > Hi,
> > > > > I have a Wíndows 2003 server running SQL Server (SP 3a).
> > > > > My problem: Neither via Enterprise Manager nor T-SQL is it
possible
> to
> > > do
> > > > a
> > > > > backup / restore on a mapped network drive. Using Query Analyzer,
an
> > > error
> > > > > shows up saying "Backup medium <xyz> cannot be opened. Media error
> or
> > > > medium
> > > > > offline", using Enterprise Manager, no network drive is being
shown
> at
> > > > all.
> > > > > Strange enough, it is possible to list and access all network
drives
> > in
> > > > the
> > > > > (file-) explorer from this server. I do not consider this to be a
> > > security
> > > > > issue, as the sql server runs under the domain administrator's
> account
> > > > > having all required permissions. The network drives are assigned
to
> > > > > different servers, all of which belong to the same domain as the
sql
> > > > server.
> > > > > The domain administrator is permanently logged on the sql server
so
> > the
> > > > > network drives are present all the time.
> > > > > It makes no difference if I run the Enterprise Manager directly on
> the
> > > sql
> > > > > server or from a different machine. It also makes no difference if
> the
> > > > > connection to the sql server is established using Integrated
> Security
> > or
> > > > sql
> > > > > server's sa account.
> > > > > There is another sql server in our domain which is configured
> > > identically,
> > > > > apart from running under Win 2000 Server. This one has no problems
> > > > accessing
> > > > > any network drives...
> > > > > For different reasons I do want to use a mapped drive instead of
an
> > UNC
> > > > > path, so I hope someone can help me :))
> > > > > Thanks in advance,
> > > > > Olaf
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Newboe: table containing XML schemas in database

I just created a schema in the AdventureWorks DB (the place for newbies). Where are these schemas stored and what t-sql statement can list all XML Schemas defined in a database\table\column?

TIA,

Barkingdog

select x.name, t.name, c.name
from sys.column_xml_schema_collection_usages cx,
sys.tables t,
sys.columns c,
sys.xml_schema_collections x
where x.xml_collection_id = cx.xml_collection_id
and t.object_id = cx.object_id
and c.column_id = cx.column_id
and c.object_id = t.object_id

The best way to learn this (catalog views) is thru SQL Server Management Studio -> Object Explorer -> Database -> master -> Views -> System views. Just do select * from every one of them.

newbie: processing records sequentially

Hi to all,
I'm a newbie to T-SQL but not to programming. What I want to do is
process every record in a table, and depending on the values in the
record itself, either copy the record to another table or update a
third table, and the delete the original record. Basically, it's a data
preparation area table where the records are imported with no error
check, and then validated against a set of conditions.

THE QUESTION IS: which approach should I follow? Is there a sort of
FORALL loop in T-SQL that I can execute on every record in the table?
Or shall I rely on other tools (VB Scripting, or...)? SQL Server 2000

Thanx to allIn SQL your goal should generally be to avoid processing data a row at
a time. This example may help you:

INSERT INTO AnotherTable (col1, col2, ...)
SELECT col1, col2, ...
FROM YourTable
WHERE ... ? /* unspecified */

UPDATE AnotherTable
SET ... ? /* unspecified */
WHERE ... /* uspecified */

DELETE FROM YourTable
WHERE ...

etc.

Hopefully this gives you the idea. If you need more help, please refer
to the following article which describes the best way to post a fuller
description of your problem:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||David,
thanks for prompt response. I understand the process is somehow
reversed from a row-by-row cycle. It seems to me somehow inefficient,
though, to run a query against a third table twice in order to UPDATE
the first time and DELETE the second.

Cheers

David Portas wrote:
> In SQL your goal should generally be to avoid processing data a row at
> a time. This example may help you:
> INSERT INTO AnotherTable (col1, col2, ...)
> SELECT col1, col2, ...
> FROM YourTable
> WHERE ... ? /* unspecified */
> UPDATE AnotherTable
> SET ... ? /* unspecified */
> WHERE ... /* uspecified */
> DELETE FROM YourTable
> WHERE ...
> etc.
> Hopefully this gives you the idea. If you need more help, please refer
> to the following article which describes the best way to post a fuller
> description of your problem:
> http://www.aspfaq.com/etiquette.asp?id=5006|||It depends. INSERT, DELETE and UPDATE are separate operations so will
always require separate statements whatever method you use. The main
question is how best to optimise the WHERE selection criteria. You
didn't specify what those criteria are so I can only guess. It is at
least likely that putting the criteria in WHERE clauses, with suitable
indexes, will be much more efficient than the overhead of looping
through each row in a cursor. Understand that SQL is a declarative
language, unlike procedural languages that you are probably more
familiar with.Cursor processing (row by row) is rarely a good idea in
SQL and when manipulating data should usually be a last resort only.

--
David Portas
SQL Server MVP
--|||>> I'm a newbie to T-SQL but not to programming.<<

You missed the point of a declarative, set-oriented language and it
will take you about a year to un-learn your old mental models. I have
a book entitled SQL PROGRAMMING STYLE that deals with this problem in a
few chapters.

>> What I want to do is process every record [sic] in a table, and
depending on the values in the record [sic] itself, either copy the
record [sic] to another table or update a third table, and the delete
the original record [sic] . <<

This is a common problem; rows are not records, tables are not files
and columns are not fields. Totally differrent concepts. I will give
you the long painful details that I post on this topic if you ask.
Have you ever worked with LISP, FP, Prolog or a language that was not
based on procedures? This is a new way of thinking.

>> Basically, it's a data preparation area table where the records
[sic] are imported with no error check, and then validated against a
set of conditions. <<

This is usually done with an ETL tool these days, but you can do this
with a few statements. If you have an implementation of the new SQL-99
MERGE statement, then you can probably do it in one statement. It will
be a very complex statement, tho :)

BEGIN
UPDATE RawData
SET <column edits>;
DELETE GoodStuff
WHERE EXISTS
(SELECT *
FROM RawData
WHERE <<conditions>>;
INSERT INTO GoodStuff
SELECT a, b, c, ..
FROM RawData
WHERE <<conditions>>;
END;

First scrub the raw data, then remove the old data that is going to be
replaced, and finally insert the new clean data.|||moco (makelovenotspamdaldegan@.email.it) writes:
> thanks for prompt response. I understand the process is somehow
> reversed from a row-by-row cycle. It seems to me somehow inefficient,
> though, to run a query against a third table twice in order to UPDATE
> the first time and DELETE the second.

And with a loop there will be many queries against the table.

But as they say, seeing is believing. Go ahead and bench-mark. You will
be amazed...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp