We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
--
Larry Menzin
American Techsystems Corp.If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:
> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automated
> fashion. Is there a way to determine the pathname and filename of the latest
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||Just what I was looking for. Thanks a lot.
--
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
> > We are instituting a procedure to restore the latest full backup (*.BAK
> > extension) of our production database to a training database in an automated
> > fashion. Is there a way to determine the pathname and filename of the latest
> > full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> > command manipulation to get this information from file timestamps? I would
> > then use T-SQL to restore the filename over the existing training database.
> > Any ideas?
> >
> > --
> > Larry Menzin
> > American Techsystems Corp.
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Thursday, March 29, 2012
Finding latest full backup file name
We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
Larry Menzin
American Techsystems Corp.
If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:
> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automated
> fashion. Is there a way to determine the pathname and filename of the latest
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.
|||Just what I was looking for. Thanks a lot.
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
Larry Menzin
American Techsystems Corp.
If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:
> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automated
> fashion. Is there a way to determine the pathname and filename of the latest
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.
|||Just what I was looking for. Thanks a lot.
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
Finding latest full backup file name
We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
Larry Menzin
American Techsystems Corp.If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:
> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automat
ed
> fashion. Is there a way to determine the pathname and filename of the late
st
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database
.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||Just what I was looking for. Thanks a lot.
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
>
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
Larry Menzin
American Techsystems Corp.If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:
> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automat
ed
> fashion. Is there a way to determine the pathname and filename of the late
st
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database
.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||Just what I was looking for. Thanks a lot.
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
>
Monday, March 26, 2012
finding connection string
to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
Thanks
It was UDL, I remember now.
"me" wrote:
> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
Thanks
It was UDL, I remember now.
"me" wrote:
> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks
finding connection string
to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:
> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:
> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks
finding connection string
to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:
> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:
> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks
Finding BINN directory for Multiple Instances
I am writing an installer that needs to put a dll file with a bunch of
extended stored procedures in the BINN directory. The problem is that
if the machine is running multiple instances of SQL Server, there is
more than one BINN directory. The installer has a dynamically
populated dropdown with the name of the instances, so does anyone know
if this logic is correct and/or reliable assuming SQL Server 2000?
extended stored procedures in the BINN directory. The problem is that
if the machine is running multiple instances of SQL Server, there is
more than one BINN directory. The installer has a dynamically
populated dropdown with the name of the instances, so does anyone know
if this logic is correct and/or reliable assuming SQL Server 2000?
If the instance name = "MSSQLSERVER"
then path to BINN directory = registry value of
HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"
If the instance name != "MSSQLSERVER"
then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"
Thanks in advance for any help."Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0407231210.28d2403c@.posting.google.c om...
> I am writing an installer that needs to put a dll file with a bunch of
> extended stored procedures in the BINN directory. The problem is that
> if the machine is running multiple instances of SQL Server, there is
> more than one BINN directory. The installer has a dynamically
> populated dropdown with the name of the instances, so does anyone know
> if this logic is correct and/or reliable assuming SQL Server 2000?
> If the instance name = "MSSQLSERVER"
> then path to BINN directory = registry value of
> HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"
> If the instance name != "MSSQLSERVER"
> then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
> SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"
> Thanks in advance for any help.
See questions 12 and 13:
http://support.microsoft.com/defaul...6&Product=sql2k
Simon
Wednesday, March 21, 2012
find transaction that generated 4 Go in journal file
We are using SQL Server with logshipping.
I noticed in the last two days the presence of 2 bigs journal files
(about 2Go each, transfered from primary to secondary ).
I want to know if there is a way to see what was the operation that lead to have this to big 2 log files
(sql statement or transaction... : with Oracle for instance, if we have chance, we can find this kind of info in
a dynamic views by the name of : v$sqlarea...)...
Thanks in advanceI don't think you can do this without a third party tool, such as the Lumigent (http://www.lumigent.com/) log explorer. I'm not even sure what that can do with log shipping, but it is where I'd start.
-PatP
I noticed in the last two days the presence of 2 bigs journal files
(about 2Go each, transfered from primary to secondary ).
I want to know if there is a way to see what was the operation that lead to have this to big 2 log files
(sql statement or transaction... : with Oracle for instance, if we have chance, we can find this kind of info in
a dynamic views by the name of : v$sqlarea...)...
Thanks in advanceI don't think you can do this without a third party tool, such as the Lumigent (http://www.lumigent.com/) log explorer. I'm not even sure what that can do with log shipping, but it is where I'd start.
-PatP
Friday, March 9, 2012
Find number of records in text file.
Hi All,
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
[vbcol=seagreen]
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL serve
r.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
[vbcol=seagreen]
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL serve
r.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Find number of records in text file.
Hi All,
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Hi All,
> >
> > Is there a way to find out how many records (rows) in the text file in
> > sql. ( It is a fix lengh record)
> >
> > Example: I have a text file in my local machine
> >
> > c:\test\t1.txt
> > c:\test\t2.txt
> >
> > in t1.txt I have these rows:
> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >
> > and t2.txt have this rows:
> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >
> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >
> > Please let me know. Thanks in advance
> >
> > Teed Lee
> >
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > Hi All,
>> >
>> > Is there a way to find out how many records (rows) in the text file in
>> > sql. ( It is a fix lengh record)
>> >
>> > Example: I have a text file in my local machine
>> >
>> > c:\test\t1.txt
>> > c:\test\t2.txt
>> >
>> > in t1.txt I have these rows:
>> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
>> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
>> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
>> >
>> > and t2.txt have this rows:
>> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
>> >
>> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
>> >
>> > Please let me know. Thanks in advance
>> >
>> > Teed Lee
>> >
>> One way would be to use OPENROWSET, documented in Books Online, and lots
>> of samples online.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;HDR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Thank You,
> > I try this statement
> >
> > select count(*)
> > from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> > 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> > Test.txt')
> >
> > but when I try to run your statement I got this error:
> >
> > OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> > returned message "Unspecified error".
> > Msg 7303, Level 16, State 1, Line 1
> > Cannot initialize the data source object of OLE DB provider
> > "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> >
> > The different between your statement vs. mine is "my file is on the
> > network"
> > Do I have to setup ODBC connect on the server where the file locate or
> > somehting.
> > Would you please tell me what else I need to setup or what do I do
> > wrong here.
> >
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > I work fine on the local account. On the local account I have to setup
> > the "SQL Server Surface Area Configuration"
> >
> > The problem I have is:
> > my sql server is on different machine Server "MachineA"
> > my files is on difrrent server "MachineB"
> >
> > I am login as my account and I am able to get in (permission) to both
> > server.
> >
> > On the "MachineA" do I have to set something to connect to "MachineB"
> > or
> > On the MachineB server do I have to set something
> > ('Microsoft.Jet.OLEDB.4.0')?
> > I am not sure. Please help.
> >
> > Teed
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL server.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Okay...but that doesn't make sense. Looks like we're losing
things in translation here.
Try this. Open up Enterprise Manager. Select the server.
Right click and select properties. Then click on the
security tab. In the bottom of the screen on the security
tab, there is a section named Start up service account.
Which one of the two is selected - System Account or This
Account? It can only be one of the two.
-Sue
On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
>Hi,
>Yes,
>I login (domain\username) is part of the group (domain\groupname) in
>the SQL service account and also my login (domain\username) also have
>permission to access that network share.
>Teed
>
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > I work fine on the local account. On the local account I have to setup
>> > the "SQL Server Surface Area Configuration"
>> >
>> > The problem I have is:
>> > my sql server is on different machine Server "MachineA"
>> > my files is on difrrent server "MachineB"
>> >
>> > I am login as my account and I am able to get in (permission) to both
>> > server.
>> >
>> > On the "MachineA" do I have to set something to connect to "MachineB"
>> > or
>> > On the MachineB server do I have to set something
>> > ('Microsoft.Jet.OLEDB.4.0')?
>> > I am not sure. Please help.
>> >
>> > Teed
>> Huh? I repeat my question - "Does the SQL service account have
>> permission to access that network share?"
>> If I understand your response, it sounds like you have SQL running under
>> the "Local System" context. If so, you cannot access network resources
>> this way - SQL Server MUST run under a domain account, and that domain
>> account MUST have permission to access the desired network resources.
>> This has nothing to do with the login that you use to connect to SQL server.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, if I login I login as "sa", then I am able
to run the query find.
I get my NT login does not have enough persimision on the server
somewhere right?
Thanks for your time and help.
Teed
Sue Hoegemeier wrote:
> Okay...but that doesn't make sense. Looks like we're losing
> things in translation here.
> Try this. Open up Enterprise Manager. Select the server.
> Right click and select properties. Then click on the
> security tab. In the bottom of the screen on the security
> tab, there is a section named Start up service account.
> Which one of the two is selected - System Account or This
> Account? It can only be one of the two.
> -Sue
> On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >Hi,
> >Yes,
> >
> >I login (domain\username) is part of the group (domain\groupname) in
> >the SQL service account and also my login (domain\username) also have
> >permission to access that network share.
> >
> >Teed
> >
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > I work fine on the local account. On the local account I have to setup
> >> > the "SQL Server Surface Area Configuration"
> >> >
> >> > The problem I have is:
> >> > my sql server is on different machine Server "MachineA"
> >> > my files is on difrrent server "MachineB"
> >> >
> >> > I am login as my account and I am able to get in (permission) to both
> >> > server.
> >> >
> >> > On the "MachineA" do I have to set something to connect to "MachineB"
> >> > or
> >> > On the MachineB server do I have to set something
> >> > ('Microsoft.Jet.OLEDB.4.0')?
> >> > I am not sure. Please help.
> >> >
> >> > Teed
> >>
> >> Huh? I repeat my question - "Does the SQL service account have
> >> permission to access that network share?"
> >>
> >> If I understand your response, it sounds like you have SQL running under
> >> the "Local System" context. If so, you cannot access network resources
> >> this way - SQL Server MUST run under a domain account, and that domain
> >> account MUST have permission to access the desired network resources.
> >> This has nothing to do with the login that you use to connect to SQL server.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, at my local machine I bring up the "SQL
Server Management Studio" then connect to that dabase as "sa", then I
am able to run the query fine. Also if I remote login that server as as
Administrator, then I am able to run the query fine, but at my local
machine I bring up the "SQL Server Management Studio" and login that
database as my "NT domain username" then I got error message above.
Thanks for your time and help. I am new to this.
ntuyen01@.yahoo.com wrote:
> Teed
>
> Sue Hoegemeier wrote:
> > Okay...but that doesn't make sense. Looks like we're losing
> > things in translation here.
> > Try this. Open up Enterprise Manager. Select the server.
> > Right click and select properties. Then click on the
> > security tab. In the bottom of the screen on the security
> > tab, there is a section named Start up service account.
> > Which one of the two is selected - System Account or This
> > Account? It can only be one of the two.
> >
> > -Sue
> >
> > On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >
> > >Hi,
> > >Yes,
> > >
> > >I login (domain\username) is part of the group (domain\groupname) in
> > >the SQL service account and also my login (domain\username) also have
> > >permission to access that network share.
> > >
> > >Teed
> > >
> > >
> > >Tracy McKibben wrote:
> > >> ntuyen01@.yahoo.com wrote:
> > >> > I work fine on the local account. On the local account I have to setup
> > >> > the "SQL Server Surface Area Configuration"
> > >> >
> > >> > The problem I have is:
> > >> > my sql server is on different machine Server "MachineA"
> > >> > my files is on difrrent server "MachineB"
> > >> >
> > >> > I am login as my account and I am able to get in (permission) to both
> > >> > server.
> > >> >
> > >> > On the "MachineA" do I have to set something to connect to "MachineB"
> > >> > or
> > >> > On the MachineB server do I have to set something
> > >> > ('Microsoft.Jet.OLEDB.4.0')?
> > >> > I am not sure. Please help.
> > >> >
> > >> > Teed
> > >>
> > >> Huh? I repeat my question - "Does the SQL service account have
> > >> permission to access that network share?"
> > >>
> > >> If I understand your response, it sounds like you have SQL running under
> > >> the "Local System" context. If so, you cannot access network resources
> > >> this way - SQL Server MUST run under a domain account, and that domain
> > >> account MUST have permission to access the desired network resources.
> > >> This has nothing to do with the login that you use to connect to SQL server.
> > >>
> > >>
> > >> --
> > >> Tracy McKibben
> > >> MCDBA
> > >> http://www.realsqlguy.com
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Hi All,
> >
> > Is there a way to find out how many records (rows) in the text file in
> > sql. ( It is a fix lengh record)
> >
> > Example: I have a text file in my local machine
> >
> > c:\test\t1.txt
> > c:\test\t2.txt
> >
> > in t1.txt I have these rows:
> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >
> > and t2.txt have this rows:
> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >
> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >
> > Please let me know. Thanks in advance
> >
> > Teed Lee
> >
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > Hi All,
>> >
>> > Is there a way to find out how many records (rows) in the text file in
>> > sql. ( It is a fix lengh record)
>> >
>> > Example: I have a text file in my local machine
>> >
>> > c:\test\t1.txt
>> > c:\test\t2.txt
>> >
>> > in t1.txt I have these rows:
>> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
>> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
>> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
>> >
>> > and t2.txt have this rows:
>> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
>> >
>> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
>> >
>> > Please let me know. Thanks in advance
>> >
>> > Teed Lee
>> >
>> One way would be to use OPENROWSET, documented in Books Online, and lots
>> of samples online.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;HDR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Thank You,
> > I try this statement
> >
> > select count(*)
> > from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> > 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> > Test.txt')
> >
> > but when I try to run your statement I got this error:
> >
> > OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> > returned message "Unspecified error".
> > Msg 7303, Level 16, State 1, Line 1
> > Cannot initialize the data source object of OLE DB provider
> > "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> >
> > The different between your statement vs. mine is "my file is on the
> > network"
> > Do I have to setup ODBC connect on the server where the file locate or
> > somehting.
> > Would you please tell me what else I need to setup or what do I do
> > wrong here.
> >
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > I work fine on the local account. On the local account I have to setup
> > the "SQL Server Surface Area Configuration"
> >
> > The problem I have is:
> > my sql server is on different machine Server "MachineA"
> > my files is on difrrent server "MachineB"
> >
> > I am login as my account and I am able to get in (permission) to both
> > server.
> >
> > On the "MachineA" do I have to set something to connect to "MachineB"
> > or
> > On the MachineB server do I have to set something
> > ('Microsoft.Jet.OLEDB.4.0')?
> > I am not sure. Please help.
> >
> > Teed
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL server.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Okay...but that doesn't make sense. Looks like we're losing
things in translation here.
Try this. Open up Enterprise Manager. Select the server.
Right click and select properties. Then click on the
security tab. In the bottom of the screen on the security
tab, there is a section named Start up service account.
Which one of the two is selected - System Account or This
Account? It can only be one of the two.
-Sue
On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
>Hi,
>Yes,
>I login (domain\username) is part of the group (domain\groupname) in
>the SQL service account and also my login (domain\username) also have
>permission to access that network share.
>Teed
>
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > I work fine on the local account. On the local account I have to setup
>> > the "SQL Server Surface Area Configuration"
>> >
>> > The problem I have is:
>> > my sql server is on different machine Server "MachineA"
>> > my files is on difrrent server "MachineB"
>> >
>> > I am login as my account and I am able to get in (permission) to both
>> > server.
>> >
>> > On the "MachineA" do I have to set something to connect to "MachineB"
>> > or
>> > On the MachineB server do I have to set something
>> > ('Microsoft.Jet.OLEDB.4.0')?
>> > I am not sure. Please help.
>> >
>> > Teed
>> Huh? I repeat my question - "Does the SQL service account have
>> permission to access that network share?"
>> If I understand your response, it sounds like you have SQL running under
>> the "Local System" context. If so, you cannot access network resources
>> this way - SQL Server MUST run under a domain account, and that domain
>> account MUST have permission to access the desired network resources.
>> This has nothing to do with the login that you use to connect to SQL server.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, if I login I login as "sa", then I am able
to run the query find.
I get my NT login does not have enough persimision on the server
somewhere right?
Thanks for your time and help.
Teed
Sue Hoegemeier wrote:
> Okay...but that doesn't make sense. Looks like we're losing
> things in translation here.
> Try this. Open up Enterprise Manager. Select the server.
> Right click and select properties. Then click on the
> security tab. In the bottom of the screen on the security
> tab, there is a section named Start up service account.
> Which one of the two is selected - System Account or This
> Account? It can only be one of the two.
> -Sue
> On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >Hi,
> >Yes,
> >
> >I login (domain\username) is part of the group (domain\groupname) in
> >the SQL service account and also my login (domain\username) also have
> >permission to access that network share.
> >
> >Teed
> >
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > I work fine on the local account. On the local account I have to setup
> >> > the "SQL Server Surface Area Configuration"
> >> >
> >> > The problem I have is:
> >> > my sql server is on different machine Server "MachineA"
> >> > my files is on difrrent server "MachineB"
> >> >
> >> > I am login as my account and I am able to get in (permission) to both
> >> > server.
> >> >
> >> > On the "MachineA" do I have to set something to connect to "MachineB"
> >> > or
> >> > On the MachineB server do I have to set something
> >> > ('Microsoft.Jet.OLEDB.4.0')?
> >> > I am not sure. Please help.
> >> >
> >> > Teed
> >>
> >> Huh? I repeat my question - "Does the SQL service account have
> >> permission to access that network share?"
> >>
> >> If I understand your response, it sounds like you have SQL running under
> >> the "Local System" context. If so, you cannot access network resources
> >> this way - SQL Server MUST run under a domain account, and that domain
> >> account MUST have permission to access the desired network resources.
> >> This has nothing to do with the login that you use to connect to SQL server.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, at my local machine I bring up the "SQL
Server Management Studio" then connect to that dabase as "sa", then I
am able to run the query fine. Also if I remote login that server as as
Administrator, then I am able to run the query fine, but at my local
machine I bring up the "SQL Server Management Studio" and login that
database as my "NT domain username" then I got error message above.
Thanks for your time and help. I am new to this.
ntuyen01@.yahoo.com wrote:
> Teed
>
> Sue Hoegemeier wrote:
> > Okay...but that doesn't make sense. Looks like we're losing
> > things in translation here.
> > Try this. Open up Enterprise Manager. Select the server.
> > Right click and select properties. Then click on the
> > security tab. In the bottom of the screen on the security
> > tab, there is a section named Start up service account.
> > Which one of the two is selected - System Account or This
> > Account? It can only be one of the two.
> >
> > -Sue
> >
> > On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >
> > >Hi,
> > >Yes,
> > >
> > >I login (domain\username) is part of the group (domain\groupname) in
> > >the SQL service account and also my login (domain\username) also have
> > >permission to access that network share.
> > >
> > >Teed
> > >
> > >
> > >Tracy McKibben wrote:
> > >> ntuyen01@.yahoo.com wrote:
> > >> > I work fine on the local account. On the local account I have to setup
> > >> > the "SQL Server Surface Area Configuration"
> > >> >
> > >> > The problem I have is:
> > >> > my sql server is on different machine Server "MachineA"
> > >> > my files is on difrrent server "MachineB"
> > >> >
> > >> > I am login as my account and I am able to get in (permission) to both
> > >> > server.
> > >> >
> > >> > On the "MachineA" do I have to set something to connect to "MachineB"
> > >> > or
> > >> > On the MachineB server do I have to set something
> > >> > ('Microsoft.Jet.OLEDB.4.0')?
> > >> > I am not sure. Please help.
> > >> >
> > >> > Teed
> > >>
> > >> Huh? I repeat my question - "Does the SQL service account have
> > >> permission to access that network share?"
> > >>
> > >> If I understand your response, it sounds like you have SQL running under
> > >> the "Local System" context. If so, you cannot access network resources
> > >> this way - SQL Server MUST run under a domain account, and that domain
> > >> account MUST have permission to access the desired network resources.
> > >> This has nothing to do with the login that you use to connect to SQL server.
> > >>
> > >>
> > >> --
> > >> Tracy McKibben
> > >> MCDBA
> > >> http://www.realsqlguy.com
Wednesday, March 7, 2012
Find location of data files
What is the tsql cmb to view the file structure location on a database?
This will help me with my attach and detach.sp_help '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||Sorry, make that:
sp_helpdb '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||try the following:
use <dbname>
sp_helpfile
sp_helpgroup
select * from sysfiles
use master
select * from sysaltfiles
"sqlteam32" <mtorpy@.gmail.com> wrote in message
news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.
>|||Note that following will yield an error. If a call to a stored procedure is
not the first thing in a batch, you must use the keyword EXECUTE (or EXEC)
Also, I'm not sure why you need sp_helpgroup, which returns security groups.
Perhaps you want sp_helpfilegroup?
USE <dbname>
EXEC sp_helpfile
EXEC sp_helpfilegroup
SELECT * FROM sysfiles
--
HTH
Kalen Delaney, SQL Server MVP
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:OvXadVDjGHA.3800@.TK2MSFTNGP03.phx.gbl...
> try the following:
> use <dbname>
> sp_helpfile
> sp_helpgroup
> select * from sysfiles
>
> use master
> select * from sysaltfiles
>
> "sqlteam32" <mtorpy@.gmail.com> wrote in message
> news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
>> What is the tsql cmb to view the file structure location on a database?
>> This will help me with my attach and detach.
>
This will help me with my attach and detach.sp_help '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||Sorry, make that:
sp_helpdb '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||try the following:
use <dbname>
sp_helpfile
sp_helpgroup
select * from sysfiles
use master
select * from sysaltfiles
"sqlteam32" <mtorpy@.gmail.com> wrote in message
news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.
>|||Note that following will yield an error. If a call to a stored procedure is
not the first thing in a batch, you must use the keyword EXECUTE (or EXEC)
Also, I'm not sure why you need sp_helpgroup, which returns security groups.
Perhaps you want sp_helpfilegroup?
USE <dbname>
EXEC sp_helpfile
EXEC sp_helpfilegroup
SELECT * FROM sysfiles
--
HTH
Kalen Delaney, SQL Server MVP
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:OvXadVDjGHA.3800@.TK2MSFTNGP03.phx.gbl...
> try the following:
> use <dbname>
> sp_helpfile
> sp_helpgroup
> select * from sysfiles
>
> use master
> select * from sysaltfiles
>
> "sqlteam32" <mtorpy@.gmail.com> wrote in message
> news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
>> What is the tsql cmb to view the file structure location on a database?
>> This will help me with my attach and detach.
>
Find location of data files
What is the tsql cmb to view the file structure location on a database?
This will help me with my attach and detach.sp_help '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||Sorry, make that:
sp_helpdb '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||try the following:
use <dbname>
sp_helpfile
sp_helpgroup
select * from sysfiles
use master
select * from sysaltfiles
"sqlteam32" <mtorpy@.gmail.com> wrote in message
news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.
>|||Note that following will yield an error. If a call to a stored procedure is
not the first thing in a batch, you must use the keyword EXECUTE (or EXEC)
Also, I'm not sure why you need sp_helpgroup, which returns security groups.
Perhaps you want sp_helpfilegroup?
USE <dbname>
EXEC sp_helpfile
EXEC sp_helpfilegroup
SELECT * FROM sysfiles
--
HTH
Kalen Delaney, SQL Server MVP
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:OvXadVDjGHA.3800@.TK2MSFTNGP03.phx.gbl...
> try the following:
> use <dbname>
> sp_helpfile
> sp_helpgroup
> select * from sysfiles
>
> use master
> select * from sysaltfiles
>
> "sqlteam32" <mtorpy@.gmail.com> wrote in message
> news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
>
This will help me with my attach and detach.sp_help '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||Sorry, make that:
sp_helpdb '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||try the following:
use <dbname>
sp_helpfile
sp_helpgroup
select * from sysfiles
use master
select * from sysaltfiles
"sqlteam32" <mtorpy@.gmail.com> wrote in message
news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.
>|||Note that following will yield an error. If a call to a stored procedure is
not the first thing in a batch, you must use the keyword EXECUTE (or EXEC)
Also, I'm not sure why you need sp_helpgroup, which returns security groups.
Perhaps you want sp_helpfilegroup?
USE <dbname>
EXEC sp_helpfile
EXEC sp_helpfilegroup
SELECT * FROM sysfiles
--
HTH
Kalen Delaney, SQL Server MVP
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:OvXadVDjGHA.3800@.TK2MSFTNGP03.phx.gbl...
> try the following:
> use <dbname>
> sp_helpfile
> sp_helpgroup
> select * from sysfiles
>
> use master
> select * from sysaltfiles
>
> "sqlteam32" <mtorpy@.gmail.com> wrote in message
> news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
>
Friday, February 24, 2012
find
Can someone help on this:
I am just learning, and I'm connecting to the the northwindcs.mdf tables /
open file is northwindcs.adp.
This is the sample installed using msde, which is supposed to be mini sql
server to learn.
Please don't refer me elsewhere, here is what I'm trying to learn:
If I want to hit a command button and do the following:
1. Find a customerid
2. if found, edit the record, if not found, add a new record.
How would the below code need to look for this, I'm not even sure the
connection string is correct.
I'm getting following error:
run-time error 3219
operation not allowed in this context.
I get the y messagebox, but rst!ContactTitle = "The Owner" doesn't work.
When I hit the debug, rst.close is highlighted.
Also, how do you handle a no find situation here, I noticed a nomatch
doesn't work.
I am real good at programming, but new to the server thing.
And finally, is there a way to hit this command button, and do all from a
stored procedure instead of code? But in background, no user intevention
once button is hit. Which is better, this code approach or a possible
stored procedure.
Please help, if I get this down, I think I'll have the rest wipped. The
connect string is one big thing confusing me along with handling record once
found / not found. I'm used of DAO. If some one is willing to help, I can
email detailed real code from a database I'm really working on. I need to
learn this first to convert code.
I am just learning, and I'm connecting to the the northwindcs.mdf tables /
open file is northwindcs.adp.
This is the sample installed using msde, which is supposed to be mini sql
server to learn.
Please don't refer me elsewhere, here is what I'm trying to learn:
If I want to hit a command button and do the following:
1. Find a customerid
2. if found, edit the record, if not found, add a new record.
How would the below code need to look for this, I'm not even sure the
connection string is correct.
I'm getting following error:
run-time error 3219
operation not allowed in this context.
I get the y messagebox, but rst!ContactTitle = "The Owner" doesn't work.
When I hit the debug, rst.close is highlighted.
Also, how do you handle a no find situation here, I noticed a nomatch
doesn't work.
I am real good at programming, but new to the server thing.
And finally, is there a way to hit this command button, and do all from a
stored procedure instead of code? But in background, no user intevention
once button is hit. Which is better, this code approach or a possible
stored procedure.
Please help, if I get this down, I think I'll have the rest wipped. The
connect string is one big thing confusing me along with handling record once
found / not found. I'm used of DAO. If some one is willing to help, I can
email detailed real code from a database I'm really working on. I need to
learn this first to convert code.
HERE IS SAMPLE CODE
Private Sub Command16_Click()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim mark As Variant
Dim count As Integer
count = 0
cnn.Open "DSN=NorthwindCS; Provider=SQLOLEDB;Data Source=OEMCOMPUTER;Initial
Catalog=NorthwindCS; uid=sa; pwd=;"
rst.Open "SELECT * FROM Customers", cnn, _
adOpenDynamic, adLockOptimistic, adCmdText
'rst.Open "SELECT CustomerID FROM Customers", cnn, _
' adOpenDynamic, adLockReadOnly, adCmdText
' The default parameters are sufficient to search forward
' through a Recordset.
rst.Find "CustomerID = 'CHOPS'"
If rst!CustomerID = "CHOPS" Then
MsgBox "y"
rst!ContactTitle = "The Owner"
Else
MsgBox "n"
End If
' Skip the current record to avoid finding the same row repeatedly.
' The bookmark is redundant because Find searches from the current
' position.
'Do While rst.EOF <> True 'Continue if last find succeeded.
' Debug.Print "Title ID: "; rst!CustomerID
count = count + 1 'Count the last title found.
'mark = rst.Bookmark 'Note current position.
'rst.Find "CustomerID = 'CHOPS'", 1, adSearchForward, mark
'Exit Do
'Loop
rst.Close
cnn.Close
Debug.Print "The number of business titles is " & count
End SubJIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> Please don't refer me elsewhere, here is what I'm trying to learn:
I am afraid that I will have to. The error message you get comes from
ADO, and you may have better luck in a forum devoted to ADO. I have
not worked much with updating data through record sets. (And I have
never liked the way ADO does it, beause things happen behind my back
that I don't have control over.)
The one suggestion I could make is to set cnn.CursorLocation to
adUseClient to use a client side cursor. You would then have to
write back your changes with .Update or .UpdateBatch. Using a
client-side cursor means that you get all data to the client and
work with it there. You can even disconnect between data retrieval
and update if you like. This is leaner on resources on the server
and scales better. And probably comes with fewer mysteries as well.
> How would the below code need to look for this, I'm not even sure the
> connection string is correct.
Hmm..
> cnn.Open "DSN=NorthwindCS; Provider=SQLOLEDB;Data
> Source=OEMCOMPUTER;Initial Catalog=NorthwindCS; uid=sa; pwd=;"
If that is the actual password you have a security problem. :-)
Best if you can is to use integrated security. Replace uid, pwd with
IntegratedSecurity=SSPI.
And I don't think you need the DSN part.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I figured it out, thanks: for other users, here is what works:
My question is:
I'm just learning, so this was done on same computer with msde. (supposed to
be just like sql server) If this works on single computer, would it work on
a real sql server? Please advise
Public Sub MoveFirstX()
Dim rst As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intCommand As Integer
Dim TVAR
strCnn = "Provider=sqloledb;" & _
"Data Source=OEMCOMPUTER;Initial Catalog=LEDGER9SQL;User
Id=sa;Password=; "
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.CursorLocation = adUseServer 'or client works here
rst.LockType = adLockOptimistic
rst.Open "Transactions", strCnn, , , adCmdTable
TVAR = Text2
rst.Find "TransactionID = " & TVAR
Do While True
strMessage = "Name: " & rst!TransactionID & " " & _ 'this mess is
just sample code from help, not needed
"[1 - MoveFirst, 2 - MoveLast, " & vbCr & _
"3 - MoveNext, 4 - MovePrevious]"
intCommand = Val(Left(InputBox(strMessage), 1))
If intCommand < 1 Or intCommand > 4 Then Exit Do
If intCommand = 3 Then rst.MoveNext
If rst!TransactionID = 2 Then
rst!DepositAmount = 500
rst.Update
End If
Loop
rst.Close
End Sub|||JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> I'm just learning, so this was done on same computer with msde.
> (supposed to be just like sql server) If this works on single computer,
> would it work on a real sql server? Please advise
Yes, MSDE is just a stripped down version of SQL Server. There are a
few things that you can to in Enterprise Edition, that you can't to
on MSDE, but you would have to learn a lot to run into it. :-)
But you really need to fix that password... And if you are running
on your on box, you should be able to use integrated security.
You see, if you have a blank password for sa and expose that server
on the Internet - because you are connected, and you don't have a
firewall - you will soon have uninvited guests in your server.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for reply, in real life I would have pass word, this is only practice
while learning.
By the way, I ran and update stored procedure. A message comes letting you
know it ran. Is there a way to turn messages off after a stored procedure
runs?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9523F25DDECCFYazorman@.127.0.0.1...
> JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> > I'm just learning, so this was done on same computer with msde.
> > (supposed to be just like sql server) If this works on single computer,
> > would it work on a real sql server? Please advise
> Yes, MSDE is just a stripped down version of SQL Server. There are a
> few things that you can to in Enterprise Edition, that you can't to
> on MSDE, but you would have to learn a lot to run into it. :-)
> But you really need to fix that password... And if you are running
> on your on box, you should be able to use integrated security.
> You see, if you have a blank password for sa and expose that server
> on the Internet - because you are connected, and you don't have a
> firewall - you will soon have uninvited guests in your server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:arjIc.84061$OB3.38188@.bgtnsc05-news.ops.worldnet.att.net...
> Thanks for reply, in real life I would have pass word, this is only
practice
> while learning.
Well, best time to start good practices is now. Seriously.
Remember, all it takes is someone connecting to port 1434 as sa and doing a
xp_cmdshell 'net user foo test /add'
xp_cmdshell 'net group "domain admins" foo /add'
and they've owned your box.|||Not to mention that there are viruses that regularly scan the net for open
port 1433 (if not 1434?) and spread this way, if SA has no password. It only
took me a few minutes on the net to catch it once... I used system restore
to roll back my system immediately afterwards... and System Restore may not
work with future viruses like this.
ALWAYS install MS SQL 2000 SP2 or earlier while *disconnected* from the
network and only reconnect after changing the SA password (and/or installing
SP3, which prompts for an SA password.
Search vil.nai.com for sql and you'll see a lot...
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:_6mIc.60155$iJ4.59427@.twister.nyroc.rr.com...
"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:arjIc.84061$OB3.38188@.bgtnsc05-news.ops.worldnet.att.net...
> Thanks for reply, in real life I would have pass word, this is only
practice
> while learning.
Well, best time to start good practices is now. Seriously.
Remember, all it takes is someone connecting to port 1434 as sa and doing a
xp_cmdshell 'net user foo test /add'
xp_cmdshell 'net group "domain admins" foo /add'
and they've owned your box.|||Aaron W. West (tallpeak@.hotmail.NO.SPAM) writes:
> Not to mention that there are viruses that regularly scan the net for
> open port 1433 (if not 1434?) and spread this way, if SA has no
> password. It only took me a few minutes on the net to catch it once... I
> used system restore to roll back my system immediately afterwards... and
> System Restore may not work with future viruses like this.
And there were the days when I ran with a blank sa password at home. I
had Zonealarm, so I though I was safe. It was just that ZoneAlarm had
asked whether it was OK SQL Server to accept connections from 0.0.0.0,
and that was the Internet zone...
So one Sunday afternoon, ZoneAlarm asks me if it was OK for tftp to access
some Internet address. I was curions what could be using tftp behind
the scenes, so I fired up task manager, to see that the user "sql" was
into it. An sp_who revealed a hostname I had never seen before. There
was a quick kill, and a quick change of password. Thankfully, there was
no further damage. (This was some years ago.)
And now I'm using the XP firewall for incoming traffic, as it seals of
SQL Server as well.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> Thanks for reply, in real life I would have pass word, this is only
> practice while learning. By the way, I ran and update stored procedure.
> A message comes letting you know it ran. Is there a way to turn
> messages off after a stored procedure runs?
Could reprint that message?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, February 19, 2012
Filters in rdl.
Hi,
I've a big trouble. According the documentation, the <Filters> section of a
rdl file contains a collection of <Filter>. But there is no information how
this collection is handled. It seem that the datasource is filtered based on
every <Filter> with AND.
If you are in the Filters tab of a graph, you can add several <Filter>. The
"and/or" column is greyed and a default AND is used.
My question is how to change this to OR.
My filters have to be like this
City="New York" or
City="New Jersey"
Instead of
City="New York" and
City="New Jersey"
who will never return any rows.
Any idea ?
Thanks.
Regardsm
--
Stéphaneif you select the same column name from the drop down it assumes "OR" if you
select diferent column name it assumes "and"
For e.g
contact id = 1 and when you go to the second line and select contact id you
can see it turns to "or".
if you select AccountName then it turns to "and"
Amarnath
"Suedois" wrote:
> Hi,
> I've a big trouble. According the documentation, the <Filters> section of a
> rdl file contains a collection of <Filter>. But there is no information how
> this collection is handled. It seem that the datasource is filtered based on
> every <Filter> with AND.
> If you are in the Filters tab of a graph, you can add several <Filter>. The
> "and/or" column is greyed and a default AND is used.
> My question is how to change this to OR.
> My filters have to be like this
> City="New York" or
> City="New Jersey"
> Instead of
> City="New York" and
> City="New Jersey"
> who will never return any rows.
> Any idea ?
> Thanks.
> Regardsm
>
> --
> Stéphane|||Thanks for you reply.
Didi you know if it's possible to do an 'or' on different columns ?
I'll try you tips and see what is done in XML file.
Regards,
--
Stéphane
"Amarnath" wrote:
> if you select the same column name from the drop down it assumes "OR" if you
> select diferent column name it assumes "and"
> For e.g
> contact id = 1 and when you go to the second line and select contact id you
> can see it turns to "or".
> if you select AccountName then it turns to "and"
> Amarnath
>
> "Suedois" wrote:
> > Hi,
> >
> > I've a big trouble. According the documentation, the <Filters> section of a
> > rdl file contains a collection of <Filter>. But there is no information how
> > this collection is handled. It seem that the datasource is filtered based on
> > every <Filter> with AND.
> >
> > If you are in the Filters tab of a graph, you can add several <Filter>. The
> > "and/or" column is greyed and a default AND is used.
> >
> > My question is how to change this to OR.
> >
> > My filters have to be like this
> > City="New York" or
> > City="New Jersey"
> > Instead of
> > City="New York" and
> > City="New Jersey"
> > who will never return any rows.
> >
> > Any idea ?
> >
> > Thanks.
> >
> > Regardsm
> >
> >
> >
> > --
> > Stéphane
I've a big trouble. According the documentation, the <Filters> section of a
rdl file contains a collection of <Filter>. But there is no information how
this collection is handled. It seem that the datasource is filtered based on
every <Filter> with AND.
If you are in the Filters tab of a graph, you can add several <Filter>. The
"and/or" column is greyed and a default AND is used.
My question is how to change this to OR.
My filters have to be like this
City="New York" or
City="New Jersey"
Instead of
City="New York" and
City="New Jersey"
who will never return any rows.
Any idea ?
Thanks.
Regardsm
--
Stéphaneif you select the same column name from the drop down it assumes "OR" if you
select diferent column name it assumes "and"
For e.g
contact id = 1 and when you go to the second line and select contact id you
can see it turns to "or".
if you select AccountName then it turns to "and"
Amarnath
"Suedois" wrote:
> Hi,
> I've a big trouble. According the documentation, the <Filters> section of a
> rdl file contains a collection of <Filter>. But there is no information how
> this collection is handled. It seem that the datasource is filtered based on
> every <Filter> with AND.
> If you are in the Filters tab of a graph, you can add several <Filter>. The
> "and/or" column is greyed and a default AND is used.
> My question is how to change this to OR.
> My filters have to be like this
> City="New York" or
> City="New Jersey"
> Instead of
> City="New York" and
> City="New Jersey"
> who will never return any rows.
> Any idea ?
> Thanks.
> Regardsm
>
> --
> Stéphane|||Thanks for you reply.
Didi you know if it's possible to do an 'or' on different columns ?
I'll try you tips and see what is done in XML file.
Regards,
--
Stéphane
"Amarnath" wrote:
> if you select the same column name from the drop down it assumes "OR" if you
> select diferent column name it assumes "and"
> For e.g
> contact id = 1 and when you go to the second line and select contact id you
> can see it turns to "or".
> if you select AccountName then it turns to "and"
> Amarnath
>
> "Suedois" wrote:
> > Hi,
> >
> > I've a big trouble. According the documentation, the <Filters> section of a
> > rdl file contains a collection of <Filter>. But there is no information how
> > this collection is handled. It seem that the datasource is filtered based on
> > every <Filter> with AND.
> >
> > If you are in the Filters tab of a graph, you can add several <Filter>. The
> > "and/or" column is greyed and a default AND is used.
> >
> > My question is how to change this to OR.
> >
> > My filters have to be like this
> > City="New York" or
> > City="New Jersey"
> > Instead of
> > City="New York" and
> > City="New Jersey"
> > who will never return any rows.
> >
> > Any idea ?
> >
> > Thanks.
> >
> > Regardsm
> >
> >
> >
> > --
> > Stéphane
Subscribe to:
Posts (Atom)