Bulk SQL Server 2000 Registration

Hi All,
I have about 60 or so SQL Servers to register. I don't have the option of
importing from another machine.
I think I maybe able to do this via SQL-DMO but not sure how to get started.
Does anyone have any ideas about doing mass SQl Server registrations?
Thanks.Check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_r_62pg.asp
Haven't tried it but you might also be able to create a query and loop for
sp_addserver via QA or OSQL.
HTH
Jerry
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> Hi All,
> I have about 60 or so SQL Servers to register. I don't have the option of
> importing from another machine.
> I think I maybe able to do this via SQL-DMO but not sure how to get
> started.
> Does anyone have any ideas about doing mass SQl Server registrations?
> Thanks.|||Thanks. I will try this.
Will keep the community posted ( pun intended)!
"Jerry Spivey" wrote:
> Check out:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_r_62pg.asp
> Haven't tried it but you might also be able to create a query and loop for
> sp_addserver via QA or OSQL.
> HTH
> Jerry
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> > Hi All,
> >
> > I have about 60 or so SQL Servers to register. I don't have the option of
> > importing from another machine.
> >
> > I think I maybe able to do this via SQL-DMO but not sure how to get
> > started.
> >
> > Does anyone have any ideas about doing mass SQl Server registrations?
> >
> > Thanks.
>
>|||OK. I have made a little progress.
I got the script to create the Server Groups in EM but it keeps crashing
with a memory error when I try to add the RegisteredServer object to the
RegisteredServers collection. In other words, I am unable to start
registering servers.
I have included the vbscript below:
' Declare variables
Const ForReading = 1, ForWriting = 2
Dim dmoApp, dmoServerGroup, dmoRegServer
Dim fso, MyFile, strServerName
on error resume Next
' Create a ref to the SQL Server Object
Set dmoApp = Wscript.CreateObject("SQLDMO.Application")
' Create a ServerGroup Object
' Set dmoServerGroup = CreateObject("SQLDMO.ServerGroup")
Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
' Add the CRAWLEY Server Group name
dmoServerGroup.Name = "CRAWLEY"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Add the TAMPA Server Group name
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Register the TAMPA Servers
Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
dmoRegServer.UseTrustedConnection = 1
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile("C:\RWray\VBScripts\TampaServers.txt",
ForReading, True)
Do Until MyFile.AtEndOfStream
strServerName = MyFile.Readline
dmoRegServer.Name = strServerName
Wscript.Echo strServerName
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
It crashes on the line
dmoServerGroup.RegisteredServers.Add(dmoRegServer).
Thanks.
"brawtaman" wrote:
> Thanks. I will try this.
> Will keep the community posted ( pun intended)!
> "Jerry Spivey" wrote:
> > Check out:
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_r_62pg.asp
> >
> > Haven't tried it but you might also be able to create a query and loop for
> > sp_addserver via QA or OSQL.
> >
> > HTH
> >
> > Jerry
> > "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> > news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> > > Hi All,
> > >
> > > I have about 60 or so SQL Servers to register. I don't have the option of
> > > importing from another machine.
> > >
> > > I think I maybe able to do this via SQL-DMO but not sure how to get
> > > started.
> > >
> > > Does anyone have any ideas about doing mass SQl Server registrations?
> > >
> > > Thanks.
> >
> >
> >

Bulk SQL Server 2000 Registration

Hi All,
I have about 60 or so SQL Servers to register. I don't have the option of
importing from another machine.
I think I maybe able to do this via SQL-DMO but not sure how to get started.
Does anyone have any ideas about doing mass SQl Server registrations?
Thanks.Check out:
http://msdn.microsoft.com/library/d... />
r_62pg.asp
Haven't tried it but you might also be able to create a query and loop for
sp_addserver via QA or OSQL.
HTH
Jerry
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> Hi All,
> I have about 60 or so SQL Servers to register. I don't have the option of
> importing from another machine.
> I think I maybe able to do this via SQL-DMO but not sure how to get
> started.
> Does anyone have any ideas about doing mass SQl Server registrations?
> Thanks.|||Thanks. I will try this.
Will keep the community posted ( pun intended)!
"Jerry Spivey" wrote:

> Check out:
> http://msdn.microsoft.com/library/d...>
b_r_62pg.asp
> Haven't tried it but you might also be able to create a query and loop for
> sp_addserver via QA or OSQL.
> HTH
> Jerry
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
>
>|||OK. I have made a little progress.
I got the script to create the Server Groups in EM but it keeps crashing
with a memory error when I try to add the RegisteredServer object to the
RegisteredServers collection. In other words, I am unable to start
registering servers.
I have included the vbscript below:
' Declare variables
Const ForReading = 1, ForWriting = 2
Dim dmoApp, dmoServerGroup, dmoRegServer
Dim fso, MyFile, strServerName
on error resume Next
' Create a ref to the SQL Server Object
Set dmoApp = Wscript.CreateObject("SQLDMO.Application")
' Create a ServerGroup Object
' Set dmoServerGroup = CreateObject("SQLDMO.ServerGroup")
Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
' Add the CRAWLEY Server Group name
dmoServerGroup.Name = "CRAWLEY"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Add the TAMPA Server Group name
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Register the TAMPA Servers
Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
dmoRegServer.UseTrustedConnection = 1
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile("C:\RWray\VBScripts\TampaServers.txt",
ForReading, True)
Do Until MyFile.AtEndOfStream
strServerName = MyFile.Readline
dmoRegServer.Name = strServerName
Wscript.Echo strServerName
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
It crashes on the line
dmoServerGroup.RegisteredServers.Add(dmoRegServer).
Thanks.
"brawtaman" wrote:
[vbcol=seagreen]
> Thanks. I will try this.
> Will keep the community posted ( pun intended)!
> "Jerry Spivey" wrote:
>sql

Bulk SQL Server 2000 Registration

Hi All,
I have about 60 or so SQL Servers to register. I don't have the option of
importing from another machine.
I think I maybe able to do this via SQL-DMO but not sure how to get started.
Does anyone have any ideas about doing mass SQl Server registrations?
Thanks.
Check out:
http://msdn.microsoft.com/library/de..._ob_r_62pg.asp
Haven't tried it but you might also be able to create a query and loop for
sp_addserver via QA or OSQL.
HTH
Jerry
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> Hi All,
> I have about 60 or so SQL Servers to register. I don't have the option of
> importing from another machine.
> I think I maybe able to do this via SQL-DMO but not sure how to get
> started.
> Does anyone have any ideas about doing mass SQl Server registrations?
> Thanks.
|||Thanks. I will try this.
Will keep the community posted ( pun intended)!
"Jerry Spivey" wrote:

> Check out:
> http://msdn.microsoft.com/library/de..._ob_r_62pg.asp
> Haven't tried it but you might also be able to create a query and loop for
> sp_addserver via QA or OSQL.
> HTH
> Jerry
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
>
>
|||OK. I have made a little progress.
I got the script to create the Server Groups in EM but it keeps crashing
with a memory error when I try to add the RegisteredServer object to the
RegisteredServers collection. In other words, I am unable to start
registering servers.
I have included the vbscript below:
' Declare variables
Const ForReading = 1, ForWriting = 2
Dim dmoApp, dmoServerGroup, dmoRegServer
Dim fso, MyFile, strServerName
on error resume Next
' Create a ref to the SQL Server Object
Set dmoApp = Wscript.CreateObject("SQLDMO.Application")
' Create a ServerGroup Object
' Set dmoServerGroup = CreateObject("SQLDMO.ServerGroup")
Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
' Add the CRAWLEY Server Group name
dmoServerGroup.Name = "CRAWLEY"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Add the TAMPA Server Group name
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Register the TAMPA Servers
Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
dmoRegServer.UseTrustedConnection = 1
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile("C:\RWray\VBScripts\TampaServers. txt",
ForReading, True)
Do Until MyFile.AtEndOfStream
strServerName = MyFile.Readline
dmoRegServer.Name = strServerName
Wscript.Echo strServerName
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
It crashes on the line
dmoServerGroup.RegisteredServers.Add(dmoRegServer) .
Thanks.
"brawtaman" wrote:
[vbcol=seagreen]
> Thanks. I will try this.
> Will keep the community posted ( pun intended)!
> "Jerry Spivey" wrote:

BULK INSERT stop inserting rows

All,

I am just having this weird issue recently with the BULK INSERT command on one of our SQL2K servers which always returns the message "command(s) completed successfully" (Query Analyzer) but no rows get actually inserted into the bulk table.

BULK INSERT LOADTMP FROM 'D:\TEMP\MYFILE.CSV' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', MAXERRORS = 20)

> The command(s) completed successfully.

The same BULK INSERT command used to work for the past year or so and still works just fine on the other servers but for some unknown reasons, it just stopped working on this one box while always returning the message: "the command(s) completed successfully"...

I did try to provide some bogus non-existent filename in the BULK INSERT command and it would also return "the command(s) completed successfully" as well - as long as the entire BULK INSERT command syntax and arguments are correct !!!

I thought that I found some kind of solution in the msdn knowledge database related to the issue by re-applying the SQL2K service pack 4 but it didn't fix it. Rebooting the instance and the server several times did not help either.

Have any of you had encountered similar issues before and how to resolve it? Would appreciate all you inputs...

Thanks,
JohnSince you have "MAXERRORS = 20" in your statement, it returns success unless 21 errors occur.

Try setting maxerrors = 1 and see what message you get.

Bulk insert silently does nothing

Hi.. I am using a simple bulk insert statement which works on all our
servers except one. The servers are windows 2000 and are running
Sql Server SP4.
The bulk insert statement simply does nothing.
In query analyser, it returns
"The command(s) completed successfully." and yet returns no rowcount, error
etc
There are no events in the event log and no errors in the sql log
DTS bulk insert happily completes without doing a thing.
bcp of the same file does work however (from the server ) ..
Can someone point me in the right direction regarding DLL's etc to be
checkig please ?
Thanks Dave
Did you try:
-o output_file
Specifies the name of a file that receives output from bcp redirected from
the command prompt.
"gilkida" <gilkida@.discussions.microsoft.com> wrote in message
news:894DB758-93BC-432D-8167-F05F83B2467C@.microsoft.com...
> Hi.. I am using a simple bulk insert statement which works on all our
> servers except one. The servers are windows 2000 and are running
> Sql Server SP4.
> The bulk insert statement simply does nothing.
> In query analyser, it returns
> "The command(s) completed successfully." and yet returns no rowcount,
> error
> etc
> There are no events in the event log and no errors in the sql log
> DTS bulk insert happily completes without doing a thing.
> bcp of the same file does work however (from the server ) ..
> Can someone point me in the right direction regarding DLL's etc to be
> checkig please ?
> Thanks Dave
|||Sorry Chris.. probably not clear.. I am running the Sql Command Bult Insert
BULK INSERT t_BulkLoad FROM 'Some Filename"
works on every server but one... t
"ChrisR" wrote:

> Did you try:
> -o output_file
> Specifies the name of a file that receives output from bcp redirected from
> the command prompt.
>
>
> "gilkida" <gilkida@.discussions.microsoft.com> wrote in message
> news:894DB758-93BC-432D-8167-F05F83B2467C@.microsoft.com...
>
>
|||Woops, sorry.
"gilkida" <gilkida@.discussions.microsoft.com> wrote in message
news:C20BE904-1DDD-4511-ADBD-11C98CB62A98@.microsoft.com...[vbcol=seagreen]
> Sorry Chris.. probably not clear.. I am running the Sql Command Bult
> Insert
> BULK INSERT t_BulkLoad FROM 'Some Filename"
> works on every server but one... t
>
> "ChrisR" wrote:
|||I have the same issue. I found this posted today.
http://support.microsoft.com/?kbid=896425
It says it's applicable to Standard Edition SP3, I too have Enterprise
Edition SP4 + hotfix.
"ChrisR" wrote:

> Woops, sorry.
>
> "gilkida" <gilkida@.discussions.microsoft.com> wrote in message
> news:C20BE904-1DDD-4511-ADBD-11C98CB62A98@.microsoft.com...
>
>

Bulk insert silently does nothing

Hi.. I am using a simple bulk insert statement which works on all our
servers except one. The servers are windows 2000 and are running
Sql Server SP4.
The bulk insert statement simply does nothing.
In query analyser, it returns
"The command(s) completed successfully." and yet returns no rowcount, error
etc
There are no events in the event log and no errors in the sql log
DTS bulk insert happily completes without doing a thing.
bcp of the same file does work however (from the server ) ..
Can someone point me in the right direction regarding DLL's etc to be
checkig please ?
Thanks DaveDid you try:
-o output_file
Specifies the name of a file that receives output from bcp redirected from
the command prompt.
"gilkida" <gilkida@.discussions.microsoft.com> wrote in message
news:894DB758-93BC-432D-8167-F05F83B2467C@.microsoft.com...
> Hi.. I am using a simple bulk insert statement which works on all our
> servers except one. The servers are windows 2000 and are running
> Sql Server SP4.
> The bulk insert statement simply does nothing.
> In query analyser, it returns
> "The command(s) completed successfully." and yet returns no rowcount,
> error
> etc
> There are no events in the event log and no errors in the sql log
> DTS bulk insert happily completes without doing a thing.
> bcp of the same file does work however (from the server ) ..
> Can someone point me in the right direction regarding DLL's etc to be
> checkig please ?
> Thanks Dave|||Sorry Chris.. probably not clear.. I am running the Sql Command Bult Insert
BULK INSERT t_BulkLoad FROM 'Some Filename"
works on every server but one... t
"ChrisR" wrote:
> Did you try:
> -o output_file
> Specifies the name of a file that receives output from bcp redirected from
> the command prompt.
>
>
> "gilkida" <gilkida@.discussions.microsoft.com> wrote in message
> news:894DB758-93BC-432D-8167-F05F83B2467C@.microsoft.com...
> > Hi.. I am using a simple bulk insert statement which works on all our
> > servers except one. The servers are windows 2000 and are running
> > Sql Server SP4.
> >
> > The bulk insert statement simply does nothing.
> >
> > In query analyser, it returns
> > "The command(s) completed successfully." and yet returns no rowcount,
> > error
> > etc
> >
> > There are no events in the event log and no errors in the sql log
> >
> > DTS bulk insert happily completes without doing a thing.
> > bcp of the same file does work however (from the server ) ..
> >
> > Can someone point me in the right direction regarding DLL's etc to be
> > checkig please ?
> >
> > Thanks Dave
>
>|||Woops, sorry.
"gilkida" <gilkida@.discussions.microsoft.com> wrote in message
news:C20BE904-1DDD-4511-ADBD-11C98CB62A98@.microsoft.com...
> Sorry Chris.. probably not clear.. I am running the Sql Command Bult
> Insert
> BULK INSERT t_BulkLoad FROM 'Some Filename"
> works on every server but one... t
>
> "ChrisR" wrote:
>> Did you try:
>> -o output_file
>> Specifies the name of a file that receives output from bcp redirected
>> from
>> the command prompt.
>>
>>
>> "gilkida" <gilkida@.discussions.microsoft.com> wrote in message
>> news:894DB758-93BC-432D-8167-F05F83B2467C@.microsoft.com...
>> > Hi.. I am using a simple bulk insert statement which works on all our
>> > servers except one. The servers are windows 2000 and are running
>> > Sql Server SP4.
>> >
>> > The bulk insert statement simply does nothing.
>> >
>> > In query analyser, it returns
>> > "The command(s) completed successfully." and yet returns no rowcount,
>> > error
>> > etc
>> >
>> > There are no events in the event log and no errors in the sql log
>> >
>> > DTS bulk insert happily completes without doing a thing.
>> > bcp of the same file does work however (from the server ) ..
>> >
>> > Can someone point me in the right direction regarding DLL's etc to be
>> > checkig please ?
>> >
>> > Thanks Dave
>>|||I have the same issue. I found this posted today.
http://support.microsoft.com/?kbid=896425
It says it's applicable to Standard Edition SP3, I too have Enterprise
Edition SP4 + hotfix.
"ChrisR" wrote:
> Woops, sorry.
>
> "gilkida" <gilkida@.discussions.microsoft.com> wrote in message
> news:C20BE904-1DDD-4511-ADBD-11C98CB62A98@.microsoft.com...
> > Sorry Chris.. probably not clear.. I am running the Sql Command Bult
> > Insert
> >
> > BULK INSERT t_BulkLoad FROM 'Some Filename"
> > works on every server but one... t
> >
> >
> > "ChrisR" wrote:
> >
> >> Did you try:
> >>
> >> -o output_file
> >>
> >> Specifies the name of a file that receives output from bcp redirected
> >> from
> >> the command prompt.
> >>
> >>
> >>
> >>
> >>
> >> "gilkida" <gilkida@.discussions.microsoft.com> wrote in message
> >> news:894DB758-93BC-432D-8167-F05F83B2467C@.microsoft.com...
> >> > Hi.. I am using a simple bulk insert statement which works on all our
> >> > servers except one. The servers are windows 2000 and are running
> >> > Sql Server SP4.
> >> >
> >> > The bulk insert statement simply does nothing.
> >> >
> >> > In query analyser, it returns
> >> > "The command(s) completed successfully." and yet returns no rowcount,
> >> > error
> >> > etc
> >> >
> >> > There are no events in the event log and no errors in the sql log
> >> >
> >> > DTS bulk insert happily completes without doing a thing.
> >> > bcp of the same file does work however (from the server ) ..
> >> >
> >> > Can someone point me in the right direction regarding DLL's etc to be
> >> > checkig please ?
> >> >
> >> > Thanks Dave
> >>
> >>
> >>
>
>

Bulk insert silently does nothing

Hi.. I am using a simple bulk insert statement which works on all our
servers except one. The servers are Windows 2000 and are running
Sql Server SP4.
The bulk insert statement simply does nothing.
In query analyser, it returns
"The command(s) completed successfully." and yet returns no rowcount, error
etc
There are no events in the event log and no errors in the sql log
DTS bulk insert happily completes without doing a thing.
bcp of the same file does work however (from the server ) ..
Can someone point me in the right direction regarding DLL's etc to be
checkig please ?
Thanks DaveDid you try:
-o output_file
Specifies the name of a file that receives output from bcp redirected from
the command prompt.
"gilkida" <gilkida@.discussions.microsoft.com> wrote in message
news:894DB758-93BC-432D-8167-F05F83B2467C@.microsoft.com...
> Hi.. I am using a simple bulk insert statement which works on all our
> servers except one. The servers are Windows 2000 and are running
> Sql Server SP4.
> The bulk insert statement simply does nothing.
> In query analyser, it returns
> "The command(s) completed successfully." and yet returns no rowcount,
> error
> etc
> There are no events in the event log and no errors in the sql log
> DTS bulk insert happily completes without doing a thing.
> bcp of the same file does work however (from the server ) ..
> Can someone point me in the right direction regarding DLL's etc to be
> checkig please ?
> Thanks Dave|||Sorry Chris.. probably not clear.. I am running the Sql Command Bult Insert
BULK INSERT t_BulkLoad FROM 'Some Filename"
works on every server but one... t
"ChrisR" wrote:

> Did you try:
> -o output_file
> Specifies the name of a file that receives output from bcp redirected from
> the command prompt.
>
>
> "gilkida" <gilkida@.discussions.microsoft.com> wrote in message
> news:894DB758-93BC-432D-8167-F05F83B2467C@.microsoft.com...
>
>|||Woops, sorry.
"gilkida" <gilkida@.discussions.microsoft.com> wrote in message
news:C20BE904-1DDD-4511-ADBD-11C98CB62A98@.microsoft.com...[vbcol=seagreen]
> Sorry Chris.. probably not clear.. I am running the Sql Command Bult
> Insert
> BULK INSERT t_BulkLoad FROM 'Some Filename"
> works on every server but one... t
>
> "ChrisR" wrote:
>|||I have the same issue. I found this posted today.
http://support.microsoft.com/?kbid=896425
It says it's applicable to Standard Edition SP3, I too have Enterprise
Edition SP4 + hotfix.
"ChrisR" wrote:

> Woops, sorry.
>
> "gilkida" <gilkida@.discussions.microsoft.com> wrote in message
> news:C20BE904-1DDD-4511-ADBD-11C98CB62A98@.microsoft.com...
>
>

BULK INSERT silent failure on one SQL7 server - Help!

On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's
importing a simple tab-tab-return text file says that the query completed
successfully, but doesn't import any rows. The failure happens when running
the query via Query Analyzer running on the same machine, on and also
through ColdFusion, itself running with full privileges.
An sp_dboption query and Enterprise Mgr both say the 'select into/bulkcopy'
option is on, and this behavior happens even when logged in as the user sa,
with full privileges. SQL Server has been completely uninstalled and
reinstalled, and all known service packs have been installed, up to SP4. OS
is Windows 2000 Pro.
I don't know if this is related or not, but the 'Text file' data source
option is missing from the DTS import wizard, as it appears on that machine,
and also on another client machine on that network.
Does anyone have any idea what can be done to fix the BULK INSERT problem?
It's a serious blocking issue that needs to be resolved.
At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
rebuild the whole machine from scratch. Both those would consume admin time
and take the server down far more than would be good.
Help!
DaveDave,
If you post the ddl+sample data+your bulk insert query, we might be able to
help. If this is really critical, a call to MS PSS might prove the best
option.
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...

quote:

---

> On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's
> importing a simple tab-tab-return text file says that the query completed
> successfully, but doesn't import any rows. The failure happens when

---

running

quote:

---

> the query via Query Analyzer running on the same machine, on and also
> through ColdFusion, itself running with full privileges.
> An sp_dboption query and Enterprise Mgr both say the 'select

---

into/bulkcopy'

quote:

---

> option is on, and this behavior happens even when logged in as the user

---

sa,

quote:

---

> with full privileges. SQL Server has been completely uninstalled and
> reinstalled, and all known service packs have been installed, up to SP4.

---

OS

quote:

---

> is Windows 2000 Pro.
> I don't know if this is related or not, but the 'Text file' data source
> option is missing from the DTS import wizard, as it appears on that

---

machine,

quote:

---

> and also on another client machine on that network.
> Does anyone have any idea what can be done to fix the BULK INSERT problem?
> It's a serious blocking issue that needs to be resolved.
> At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
> rebuild the whole machine from scratch. Both those would consume admin

---

time

quote:

---

> and take the server down far more than would be good.
> Help!
> Dave
>

---

|||Dave,
I have successfully run the bulk insert. This is what returned from the
select *.
Wellesley Hills MA 02481
Claremont NH 03743
Peabody MA 01960
Quincy MA 02169
Scituate MA 02066
Melrose MA 02176
West Wareham MA 02576
Lexington MA 02421
Lexington MA 02420
Springfield VA 22152
The only time I got (0 row(s) affected) is when I rollback the transaction.
So, check to see if you have a trigger that rollback the transaction.
e.g.
if exists (select * from sysobjects where id =
object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table bulk_insert_test
CREATE TABLE bulk_insert_test (
city varchar(2000),
state varchar(2000),
zip varchar(2000)
)
go
SET NOCOUNT ON
go
begin tran
BULK INSERT bulk_insert_test
FROM 'C:\tab_rtn_test.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
MAXERRORS = 0
)
select @.@.ROWCOUNT as row_count
rollback tran
go
select * from bulk_insert_test
go
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Thanks oj, here you go, assuming attachments are allowed here.
> Put the file tab_rtn_test.txt on the root of C, then run the .sql code in
> query analyzer in grid mode. It will create a table called

---

bulk_insert_test,

quote:

---

> dropping it first if it exists, BULK INSERT the data from the file into

---

it,

quote:

---

> and show the resulting data. In QA, result tab 1 should show the number of
> rows reported by BULK INSERT (10), tab 2 should show the data in the file,
> and Messages should show '(10 row(s) affected)'.
> Note that the test table doesn't get dropped, so you can examine it any
> other way you want, but you'll want to kill it when you're done.
> Any ideas would be hugely appreciated.
> Thanks again,
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> to
> that's
> completed
user[QUOTE]
SP4.[QUOTE]
source[QUOTE]
> problem?
and[QUOTE]
>
>

---

|||Thanks for working with me on this oj.
I don't get what you're after by checking if there's a trigger to roll it
back. The table is was created today, specifically for this test, and no
such trigger was ever designed. In fact, the table is dropped and recreated
on the fly by the test code, so there couldn't be a trigger referring to it,
right?
What I don't get is that this works fine on two other servers, but does this
weird silent failure on just this one. No separate development on this test
table has ever been done, just the code I sent, so it's very unlikely that
there's any specific trigger or other code-level difference.
Seems like it must be some kind of configuration thing I haven't thought of,
or a haunted SQL install. Fear of haunting is why we completely reinstalled,
but it made no difference.
Help!
Dave
"oj" <nospam_ojngo@.home.com> wrote in message
news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Dave,
> I have successfully run the bulk insert. This is what returned from the
> select *.
> Wellesley Hills MA 02481
> Claremont NH 03743
> Peabody MA 01960
> Quincy MA 02169
> Scituate MA 02066
> Melrose MA 02176
> West Wareham MA 02576
> Lexington MA 02421
> Lexington MA 02420
> Springfield VA 22152
> The only time I got (0 row(s) affected) is when I rollback the

---

transaction.

quote:

---

> So, check to see if you have a trigger that rollback the transaction.
> e.g.
> if exists (select * from sysobjects where id =
> object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table bulk_insert_test
> CREATE TABLE bulk_insert_test (
> city varchar(2000),
> state varchar(2000),
> zip varchar(2000)
> )
> go
> SET NOCOUNT ON
> go
> begin tran
> BULK INSERT bulk_insert_test
> FROM 'C:\tab_rtn_test.txt'
> WITH (
> FIRSTROW = 2,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> MAXERRORS = 0
> )
> select @.@.ROWCOUNT as row_count
> rollback tran
> go
> select * from bulk_insert_test
> go
> --
> -oj
> http://www.rac4sql.net
>
> "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
in[QUOTE]
> bulk_insert_test,
> it,
of[QUOTE]
file,[QUOTE]
able[QUOTE]
best[QUOTE]
also[QUOTE]
> user
> SP4.
> source
> and
admin[QUOTE]
>

---

|||Dave,
Try it with bcp and see if the data is committed. Also, try specifying the
object owner in the test script. Perhaps, there are multiple objects with
the same name.
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23TvF%230I3DHA.2460@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Thanks for working with me on this oj.
> I don't get what you're after by checking if there's a trigger to roll it
> back. The table is was created today, specifically for this test, and no
> such trigger was ever designed. In fact, the table is dropped and

---

recreated

quote:

---

> on the fly by the test code, so there couldn't be a trigger referring to

---

it,

quote:

---

> right?
> What I don't get is that this works fine on two other servers, but does

---

this

quote:

---

> weird silent failure on just this one. No separate development on this

---

test

quote:

---

> table has ever been done, just the code I sent, so it's very unlikely that
> there's any specific trigger or other code-level difference.
> Seems like it must be some kind of configuration thing I haven't thought

---

of,

quote:

---

> or a haunted SQL install. Fear of haunting is why we completely

---

reinstalled,

quote:

---

> but it made no difference.
> Help!
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...
> transaction.
1)[QUOTE]
> in
into[QUOTE]
number[QUOTE]
> of
> file,
any[QUOTE]
> able
> best
when[QUOTE]
> also
and[QUOTE]
to[QUOTE]
that[QUOTE]
wipe[QUOTE]
> admin
>

---

sql

BULK INSERT silent failure on one SQL7 server - Help!

On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's
importing a simple tab-tab-return text file says that the query completed
successfully, but doesn't import any rows. The failure happens when running
the query via Query Analyzer running on the same machine, on and also
through ColdFusion, itself running with full privileges.
An sp_dboption query and Enterprise Mgr both say the 'select into/bulkcopy'
option is on, and this behavior happens even when logged in as the user sa,
with full privileges. SQL Server has been completely uninstalled and
reinstalled, and all known service packs have been installed, up to SP4. OS
is Windows 2000 Pro.
I don't know if this is related or not, but the 'Text file' data source
option is missing from the DTS import wizard, as it appears on that machine,
and also on another client machine on that network.
Does anyone have any idea what can be done to fix the BULK INSERT problem?
It's a serious blocking issue that needs to be resolved.
At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
rebuild the whole machine from scratch. Both those would consume admin time
and take the server down far more than would be good.
Help!
DaveDave,
If you post the ddl+sample data+your bulk insert query, we might be able to
help. If this is really critical, a call to MS PSS might prove the best
option.
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's
> importing a simple tab-tab-return text file says that the query completed
> successfully, but doesn't import any rows. The failure happens when
running
> the query via Query Analyzer running on the same machine, on and also
> through ColdFusion, itself running with full privileges.
> An sp_dboption query and Enterprise Mgr both say the 'select
into/bulkcopy'
> option is on, and this behavior happens even when logged in as the user
sa,
> with full privileges. SQL Server has been completely uninstalled and
> reinstalled, and all known service packs have been installed, up to SP4.
OS
> is Windows 2000 Pro.
> I don't know if this is related or not, but the 'Text file' data source
> option is missing from the DTS import wizard, as it appears on that
machine,
> and also on another client machine on that network.
> Does anyone have any idea what can be done to fix the BULK INSERT problem?
> It's a serious blocking issue that needs to be resolved.
> At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
> rebuild the whole machine from scratch. Both those would consume admin
time
> and take the server down far more than would be good.
> Help!
> Dave
>|||Thanks oj, here you go, assuming attachments are allowed here.
Put the file tab_rtn_test.txt on the root of C, then run the .sql code in
query analyzer in grid mode. It will create a table called bulk_insert_test,
dropping it first if it exists, BULK INSERT the data from the file into it,
and show the resulting data. In QA, result tab 1 should show the number of
rows reported by BULK INSERT (10), tab 2 should show the data in the file,
and Messages should show '(10 row(s) affected)'.
Note that the test table doesn't get dropped, so you can examine it any
other way you want, but you'll want to kill it when you're done.
Any ideas would be hugely appreciated.
Thanks again,
Dave
"oj" <nospam_ojngo@.home.com> wrote in message
news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> Dave,
> If you post the ddl+sample data+your bulk insert query, we might be able
to
> help. If this is really critical, a call to MS PSS might prove the best
> option.
> --
> -oj
> http://www.rac4sql.net
>
> "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
that's
> > importing a simple tab-tab-return text file says that the query
completed
> > successfully, but doesn't import any rows. The failure happens when
> running
> > the query via Query Analyzer running on the same machine, on and also
> > through ColdFusion, itself running with full privileges.
> >
> > An sp_dboption query and Enterprise Mgr both say the 'select
> into/bulkcopy'
> > option is on, and this behavior happens even when logged in as the user
> sa,
> > with full privileges. SQL Server has been completely uninstalled and
> > reinstalled, and all known service packs have been installed, up to SP4.
> OS
> > is Windows 2000 Pro.
> >
> > I don't know if this is related or not, but the 'Text file' data source
> > option is missing from the DTS import wizard, as it appears on that
> machine,
> > and also on another client machine on that network.
> >
> > Does anyone have any idea what can be done to fix the BULK INSERT
problem?
> > It's a serious blocking issue that needs to be resolved.
> >
> > At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
> > rebuild the whole machine from scratch. Both those would consume admin
> time
> > and take the server down far more than would be good.
> >
> > Help!
> >
> > Dave
> >
> >
>
begin 666 tab_rtn_test.txt
M0VET>0E3=&%T90E::7 -"E=E;&QE<VQE>2!(:6QL<PE-00DP,C0X,0T*0VQA
M<F5M;VYT"4Y("3 S-S0S#0I096%B;V1Y"4U!"3 Q.38P#0I1=6EN8WD)34$)
M,#(Q-CD-"E-C:71U871E"4U!"3 R,#8V#0I-96QR;W-E"4U!"3 R,3<V#0I7
M97-T(%=A<F5H86T)34$),#(U-S8-"DQE>&EN9W1O;@.E-00DP,C0R,0T*3&5X
G:6YG=&]N"4U!"3 R-#(P#0I3<')I;F=F:65L9 E600DR,C$U,@.T*
`
end
begin 666 bulk insert test.sql
M:68@.97AI<W1S("AS96QE8W0@.*B!F<F]M('-Y<V]B:F5C=',@.=VAE<F4@.:60@.
M/2!O8FIE8W1?:60H3B=B=6QK7VEN<V5R=%]T97-T)RD@.86YD($]"2D5#5%!2
M3U!%4E19*&ED+"!.)TES57-E<E1A8FQE)RD@./2 Q*0T*"61R;W @.=&%B;&4@.
M8G5L:U]I;G-E<G1?=&5S= T*#0I#4D5!5$4@.5$%"3$4@.8G5L:U]I;G-E<G1?
M=&5S=" H#0H)8VET>2!V87)C:&%R*#(P,# I+ T*"7-T871E('9A<F-H87(H
M,C P,"DL#0H)>FEP('9A<F-H87(H,C P,"D-"BD-"E-%5"!.3T-/54Y4($].
M#0H-"D)53$L@.24Y315)4(&)U;&M?:6YS97)T7W1E<W0-"D923TT@.)T,Z7'1A
M8E]R=&Y?=&5S="YT>'0G#0I7251(("@.-"@.E&25)35%)/5R ](#(L#0H)1DE%
M3$1415)-24Y!5$]2(#T@.)UQT)RP-"@.E23U=415)-24Y!5$]2(#T@.)UQN)RP-
M"@.E-05A%4E)/4E,@./2 P#0HI#0IS96QE8W0@.0$!23U=#3U5.5"!A<R!R;W=?
M8V]U;G0-"E-%5"!.3T-/54Y4($]&1@.T*#0IS96QE8W0@.*B!F<F]M(&)U;&M?
-:6YS97)T7W1E<W0-"@.``
`
end|||Dave,
I have successfully run the bulk insert. This is what returned from the
select *.
Wellesley Hills MA 02481
Claremont NH 03743
Peabody MA 01960
Quincy MA 02169
Scituate MA 02066
Melrose MA 02176
West Wareham MA 02576
Lexington MA 02421
Lexington MA 02420
Springfield VA 22152
The only time I got (0 row(s) affected) is when I rollback the transaction.
So, check to see if you have a trigger that rollback the transaction.
e.g.
if exists (select * from sysobjects where id =object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table bulk_insert_test
CREATE TABLE bulk_insert_test (
city varchar(2000),
state varchar(2000),
zip varchar(2000)
)
go
SET NOCOUNT ON
go
begin tran
BULK INSERT bulk_insert_test
FROM 'C:\tab_rtn_test.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
MAXERRORS = 0
)
select @.@.ROWCOUNT as row_count
rollback tran
go
select * from bulk_insert_test
go
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thanks oj, here you go, assuming attachments are allowed here.
> Put the file tab_rtn_test.txt on the root of C, then run the .sql code in
> query analyzer in grid mode. It will create a table called
bulk_insert_test,
> dropping it first if it exists, BULK INSERT the data from the file into
it,
> and show the resulting data. In QA, result tab 1 should show the number of
> rows reported by BULK INSERT (10), tab 2 should show the data in the file,
> and Messages should show '(10 row(s) affected)'.
> Note that the test table doesn't get dropped, so you can examine it any
> other way you want, but you'll want to kill it when you're done.
> Any ideas would be hugely appreciated.
> Thanks again,
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> > Dave,
> >
> > If you post the ddl+sample data+your bulk insert query, we might be able
> to
> > help. If this is really critical, a call to MS PSS might prove the best
> > option.
> >
> > --
> > -oj
> > http://www.rac4sql.net
> >
> >
> > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
> that's
> > > importing a simple tab-tab-return text file says that the query
> completed
> > > successfully, but doesn't import any rows. The failure happens when
> > running
> > > the query via Query Analyzer running on the same machine, on and also
> > > through ColdFusion, itself running with full privileges.
> > >
> > > An sp_dboption query and Enterprise Mgr both say the 'select
> > into/bulkcopy'
> > > option is on, and this behavior happens even when logged in as the
user
> > sa,
> > > with full privileges. SQL Server has been completely uninstalled and
> > > reinstalled, and all known service packs have been installed, up to
SP4.
> > OS
> > > is Windows 2000 Pro.
> > >
> > > I don't know if this is related or not, but the 'Text file' data
source
> > > option is missing from the DTS import wizard, as it appears on that
> > machine,
> > > and also on another client machine on that network.
> > >
> > > Does anyone have any idea what can be done to fix the BULK INSERT
> problem?
> > > It's a serious blocking issue that needs to be resolved.
> > >
> > > At this point, my only ideas are to upgrade to SQL 2000, or to wipe
and
> > > rebuild the whole machine from scratch. Both those would consume admin
> > time
> > > and take the server down far more than would be good.
> > >
> > > Help!
> > >
> > > Dave
> > >
> > >
> >
> >
>
>|||Thanks for working with me on this oj.
I don't get what you're after by checking if there's a trigger to roll it
back. The table is was created today, specifically for this test, and no
such trigger was ever designed. In fact, the table is dropped and recreated
on the fly by the test code, so there couldn't be a trigger referring to it,
right?
What I don't get is that this works fine on two other servers, but does this
weird silent failure on just this one. No separate development on this test
table has ever been done, just the code I sent, so it's very unlikely that
there's any specific trigger or other code-level difference.
Seems like it must be some kind of configuration thing I haven't thought of,
or a haunted SQL install. Fear of haunting is why we completely reinstalled,
but it made no difference.
Help!
Dave
"oj" <nospam_ojngo@.home.com> wrote in message
news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...
> Dave,
> I have successfully run the bulk insert. This is what returned from the
> select *.
> Wellesley Hills MA 02481
> Claremont NH 03743
> Peabody MA 01960
> Quincy MA 02169
> Scituate MA 02066
> Melrose MA 02176
> West Wareham MA 02576
> Lexington MA 02421
> Lexington MA 02420
> Springfield VA 22152
> The only time I got (0 row(s) affected) is when I rollback the
transaction.
> So, check to see if you have a trigger that rollback the transaction.
> e.g.
> if exists (select * from sysobjects where id => object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table bulk_insert_test
> CREATE TABLE bulk_insert_test (
> city varchar(2000),
> state varchar(2000),
> zip varchar(2000)
> )
> go
> SET NOCOUNT ON
> go
> begin tran
> BULK INSERT bulk_insert_test
> FROM 'C:\tab_rtn_test.txt'
> WITH (
> FIRSTROW = 2,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> MAXERRORS = 0
> )
> select @.@.ROWCOUNT as row_count
> rollback tran
> go
> select * from bulk_insert_test
> go
> --
> -oj
> http://www.rac4sql.net
>
> "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
> > Thanks oj, here you go, assuming attachments are allowed here.
> >
> > Put the file tab_rtn_test.txt on the root of C, then run the .sql code
in
> > query analyzer in grid mode. It will create a table called
> bulk_insert_test,
> > dropping it first if it exists, BULK INSERT the data from the file into
> it,
> > and show the resulting data. In QA, result tab 1 should show the number
of
> > rows reported by BULK INSERT (10), tab 2 should show the data in the
file,
> > and Messages should show '(10 row(s) affected)'.
> >
> > Note that the test table doesn't get dropped, so you can examine it any
> > other way you want, but you'll want to kill it when you're done.
> >
> > Any ideas would be hugely appreciated.
> >
> > Thanks again,
> >
> > Dave
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> > > Dave,
> > >
> > > If you post the ddl+sample data+your bulk insert query, we might be
able
> > to
> > > help. If this is really critical, a call to MS PSS might prove the
best
> > > option.
> > >
> > > --
> > > -oj
> > > http://www.rac4sql.net
> > >
> > >
> > > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > > news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
> > that's
> > > > importing a simple tab-tab-return text file says that the query
> > completed
> > > > successfully, but doesn't import any rows. The failure happens when
> > > running
> > > > the query via Query Analyzer running on the same machine, on and
also
> > > > through ColdFusion, itself running with full privileges.
> > > >
> > > > An sp_dboption query and Enterprise Mgr both say the 'select
> > > into/bulkcopy'
> > > > option is on, and this behavior happens even when logged in as the
> user
> > > sa,
> > > > with full privileges. SQL Server has been completely uninstalled and
> > > > reinstalled, and all known service packs have been installed, up to
> SP4.
> > > OS
> > > > is Windows 2000 Pro.
> > > >
> > > > I don't know if this is related or not, but the 'Text file' data
> source
> > > > option is missing from the DTS import wizard, as it appears on that
> > > machine,
> > > > and also on another client machine on that network.
> > > >
> > > > Does anyone have any idea what can be done to fix the BULK INSERT
> > problem?
> > > > It's a serious blocking issue that needs to be resolved.
> > > >
> > > > At this point, my only ideas are to upgrade to SQL 2000, or to wipe
> and
> > > > rebuild the whole machine from scratch. Both those would consume
admin
> > > time
> > > > and take the server down far more than would be good.
> > > >
> > > > Help!
> > > >
> > > > Dave
> > > >
> > > >
> > >
> > >
> >
> >
> >
>|||Dave,
Try it with bcp and see if the data is committed. Also, try specifying the
object owner in the test script. Perhaps, there are multiple objects with
the same name.
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23TvF%230I3DHA.2460@.TK2MSFTNGP10.phx.gbl...
> Thanks for working with me on this oj.
> I don't get what you're after by checking if there's a trigger to roll it
> back. The table is was created today, specifically for this test, and no
> such trigger was ever designed. In fact, the table is dropped and
recreated
> on the fly by the test code, so there couldn't be a trigger referring to
it,
> right?
> What I don't get is that this works fine on two other servers, but does
this
> weird silent failure on just this one. No separate development on this
test
> table has ever been done, just the code I sent, so it's very unlikely that
> there's any specific trigger or other code-level difference.
> Seems like it must be some kind of configuration thing I haven't thought
of,
> or a haunted SQL install. Fear of haunting is why we completely
reinstalled,
> but it made no difference.
> Help!
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...
> > Dave,
> >
> > I have successfully run the bulk insert. This is what returned from the
> > select *.
> >
> > Wellesley Hills MA 02481
> > Claremont NH 03743
> > Peabody MA 01960
> > Quincy MA 02169
> > Scituate MA 02066
> > Melrose MA 02176
> > West Wareham MA 02576
> > Lexington MA 02421
> > Lexington MA 02420
> > Springfield VA 22152
> >
> > The only time I got (0 row(s) affected) is when I rollback the
> transaction.
> > So, check to see if you have a trigger that rollback the transaction.
> >
> > e.g.
> > if exists (select * from sysobjects where id => > object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') =1)
> > drop table bulk_insert_test
> >
> > CREATE TABLE bulk_insert_test (
> > city varchar(2000),
> > state varchar(2000),
> > zip varchar(2000)
> > )
> > go
> > SET NOCOUNT ON
> > go
> > begin tran
> >
> > BULK INSERT bulk_insert_test
> > FROM 'C:\tab_rtn_test.txt'
> > WITH (
> > FIRSTROW = 2,
> > FIELDTERMINATOR = '\t',
> > ROWTERMINATOR = '\n',
> > MAXERRORS = 0
> > )
> > select @.@.ROWCOUNT as row_count
> >
> > rollback tran
> > go
> > select * from bulk_insert_test
> > go
> > --
> > -oj
> > http://www.rac4sql.net
> >
> >
> > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
> > > Thanks oj, here you go, assuming attachments are allowed here.
> > >
> > > Put the file tab_rtn_test.txt on the root of C, then run the .sql code
> in
> > > query analyzer in grid mode. It will create a table called
> > bulk_insert_test,
> > > dropping it first if it exists, BULK INSERT the data from the file
into
> > it,
> > > and show the resulting data. In QA, result tab 1 should show the
number
> of
> > > rows reported by BULK INSERT (10), tab 2 should show the data in the
> file,
> > > and Messages should show '(10 row(s) affected)'.
> > >
> > > Note that the test table doesn't get dropped, so you can examine it
any
> > > other way you want, but you'll want to kill it when you're done.
> > >
> > > Any ideas would be hugely appreciated.
> > >
> > > Thanks again,
> > >
> > > Dave
> > >
> > >
> > > "oj" <nospam_ojngo@.home.com> wrote in message
> > > news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> > > > Dave,
> > > >
> > > > If you post the ddl+sample data+your bulk insert query, we might be
> able
> > > to
> > > > help. If this is really critical, a call to MS PSS might prove the
> best
> > > > option.
> > > >
> > > > --
> > > > -oj
> > > > http://www.rac4sql.net
> > > >
> > > >
> > > > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > > > news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > > > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
> > > that's
> > > > > importing a simple tab-tab-return text file says that the query
> > > completed
> > > > > successfully, but doesn't import any rows. The failure happens
when
> > > > running
> > > > > the query via Query Analyzer running on the same machine, on and
> also
> > > > > through ColdFusion, itself running with full privileges.
> > > > >
> > > > > An sp_dboption query and Enterprise Mgr both say the 'select
> > > > into/bulkcopy'
> > > > > option is on, and this behavior happens even when logged in as the
> > user
> > > > sa,
> > > > > with full privileges. SQL Server has been completely uninstalled
and
> > > > > reinstalled, and all known service packs have been installed, up
to
> > SP4.
> > > > OS
> > > > > is Windows 2000 Pro.
> > > > >
> > > > > I don't know if this is related or not, but the 'Text file' data
> > source
> > > > > option is missing from the DTS import wizard, as it appears on
that
> > > > machine,
> > > > > and also on another client machine on that network.
> > > > >
> > > > > Does anyone have any idea what can be done to fix the BULK INSERT
> > > problem?
> > > > > It's a serious blocking issue that needs to be resolved.
> > > > >
> > > > > At this point, my only ideas are to upgrade to SQL 2000, or to
wipe
> > and
> > > > > rebuild the whole machine from scratch. Both those would consume
> admin
> > > > time
> > > > > and take the server down far more than would be good.
> > > > >
> > > > > Help!
> > > > >
> > > > > Dave
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>

Bulk Insert in 2005

After upgrading our servers to 2005, I am getting a problem with Bulk Insert
where it will does not import a file that is located on another server. I
am using UNC paths.
I have looked into BOL and investgated the changes in security but Ive
turned up nothing. The sql server process runs with a domain account that
has full rights on the remote UNC path...in fact i made it a local admin on
that box!
Running in 2000 compatibility doesnt help. i get this message..
Cannot bulk load because the file "filename" could not be opened. Operating
system error code 5(Access is denied.).
Hello, JL
See the topic "Security Considerations for Using Transact-SQL to Bulk
Import Data" in Books Online 2005:
http://msdn2.microsoft.com/ms186286.aspx
Razvan
|||When I use sa account, it works...because it is using the service account.
But I still cannot get it to work using any domain account..even one which
is a domain admin. Somehow its not passing the security rights along.
I really want to stop using sql server authentication
JL
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138175309.065558.27770@.g49g2000cwa.googlegro ups.com...
> Hello, JL
> See the topic "Security Considerations for Using Transact-SQL to Bulk
> Import Data" in Books Online 2005:
> http://msdn2.microsoft.com/ms186286.aspx
> Razvan
>

Bulk Insert in 2005

After upgrading our servers to 2005, I am getting a problem with Bulk Insert
where it will does not import a file that is located on another server. I
am using UNC paths.
I have looked into BOL and investgated the changes in security but Ive
turned up nothing. The sql server process runs with a domain account that
has full rights on the remote UNC path...in fact i made it a local admin on
that box!
Running in 2000 compatibility doesnt help. i get this message..
Cannot bulk load because the file "filename" could not be opened. Operating
system error code 5(Access is denied.).Hello, JL
See the topic "Security Considerations for Using Transact-SQL to Bulk
Import Data" in Books Online 2005:
http://msdn2.microsoft.com/ms186286.aspx
Razvan|||When I use sa account, it works...because it is using the service account.
But I still cannot get it to work using any domain account..even one which
is a domain admin. Somehow its not passing the security rights along.
I really want to stop using sql server authentication
JL
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138175309.065558.27770@.g49g2000cwa.googlegroups.com...
> Hello, JL
> See the topic "Security Considerations for Using Transact-SQL to Bulk
> Import Data" in Books Online 2005:
> http://msdn2.microsoft.com/ms186286.aspx
> Razvan
>

Bulk Insert in 2005

After upgrading our servers to 2005, I am getting a problem with Bulk Insert
where it will does not import a file that is located on another server. I
am using UNC paths.
I have looked into BOL and investgated the changes in security but Ive
turned up nothing. The sql server process runs with a domain account that
has full rights on the remote UNC path...in fact i made it a local admin on
that box!
Running in 2000 compatibility doesnt help. i get this message..
Cannot bulk load because the file "filename" could not be opened. Operating
system error code 5(Access is denied.).Hello, JL
See the topic "Security Considerations for Using Transact-SQL to Bulk
Import Data" in Books Online 2005:
http://msdn2.microsoft.com/ms186286.aspx
Razvan|||When I use sa account, it works...because it is using the service account.
But I still cannot get it to work using any domain account..even one which
is a domain admin. Somehow its not passing the security rights along.
I really want to stop using sql server authentication
JL
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138175309.065558.27770@.g49g2000cwa.googlegroups.com...
> Hello, JL
> See the topic "Security Considerations for Using Transact-SQL to Bulk
> Import Data" in Books Online 2005:
> http://msdn2.microsoft.com/ms186286.aspx
> Razvan
>

builtin\admin

Our servers are in mixed mode.

I have about 10 Window NT accounts.

if i log in domain\myacocunt into windows NT then i bring up SQL Query and do connect with Windows NT i can do what ever i like inside of sql i can delete add etc just like being the sa.

We have the builtin\admin enabled.

The question is i thought i had to have domain\myaccount in sql server logins regardless so that it goes windows nt authentication then sql authentication but it looks like i don't.

In order to take control....i need to have domain\myaccount only access DatabaseA

So to get this working.........do i just remove builtin\admin from security, server roles, system adminsitration......

Also i would like to know why not having a nt login inside can allow user to do what they like.

If you are a local administrator, you will get SQL Server access by virtue of being a member of Builtin\Administrators. It is sufficient for a group you are a member of to be granted access to SQL Server, for you to get access as well. This allows administrators to grant access to a Windows group instead of individually granting access to each group member.

Removing the Builtin\Administrators login will prevent local administrators from getting in, unless a login was created for them or some group they belonged to was given access. But note that a local administrator can always connect, if he can start the server in single-user mode - this is allowed to prevent an administrator from locking himself out.

Thanks
Laurentiu

|||

Thanks for replying.

So if i put the new login domain/startupsql as the administrator on the SQL server.

In sql server i create a new login domain/startupsql add server role of system administrator and then

remove builtin/admin from security server roles system administrator...do i have to delete from security logins or ok to leave here if done remove above.

So the start up will be domain/startupsql.

Do i change the properties on EM or can i stop and start by chainging connections in windows ....services.

Is that about all i have to do.........do you know what this NT Authority server account is and why i need that and what are the server roles.

When i view details of builtin/adimin it have evey server roles selected and every database do i need to do that with the new domain/startupsql

|||

I'm not sure I understand what you are trying to do.

You can remove builtin\administrators if you don't want other local administrators to connect.

Do you want to change the service account as well? I don't understand what you mean by "So the start up will be domain/startupsql."

The NT Authority account is most likely the entry for your current service account. Server roles are explained here: http://msdn2.microsoft.com/en-US/library/ms188659.aspx.

Thanks
Laurentiu

|||

Right now all our server administrators can go into SQL and do everything...so i need to remove builtin/administrators from security, server roles, system administrators and remove builtin

From what i understand is that...when you click on SERVER properites in EM under, Security you have start up service account and it should not be the system account but be a account that is a domain/newaccount to start up SQL.

I am wondering what else i need to be aware of before turning this off...i read it may not start SQl and such..

Thanks

|||

If you don't want to have administrators accessing your server, just remove the Builtin\Administrators builtin. This will prevent direct access to the server. You don't need to worry about server roles, just removing the group with "DROP LOGIN [Builtin\Administrators]" is sufficient.

The service account is a different thing - it's the account under which the SQL Server service is running. Configuring this is external to SQL Server and is unrelated to whether you have or not the Builtin\Administrators login present.

Of course, you should try all these ideas in a test environment until you're happy with the result, before attempting to do configuration changes on your main system.

I assume you're using SQL Server 2000 - if that's the case, note that if you remove Builtin\Administrators and you have no other sysadmin access, you may lock yourself out of the server.

Thanks
Laurentiu

|||

drop...u mean just go to security, server roles, system administrations and do remove buitlin/administrators.....does sql need this for anything else...do i need to replace it with a new login ? what does this do except allow the administrators of windows to do use sql like sa.

Yes the part u mention re to sysadmin access....- i have set up the domain/sql account as system administrators and myself aswell...do i need a just a regular sql account that has security, server roles, system administrators flag set aswell.

I was under the impression in the server properties the start up settings that it should not be a system account (not sure what system account the default sql uses) but i need to change that to domain/sql.

how is your server configured.

Built-In Administrators disabled! Please help!

We're in a big mess here:
One of our groups has taken it upon themselves to cleanup some of their SQL
servers of old and unwanted logins. The first thing to go on the first
machine was the 'built-in administrators' group. Now, the SQL server is
unreachable. Can't register to another machine, can't make changes, NOTHING.
Can someone provide some guidance to enabling the account, please?
Also, I wasn't aware that disabling the built-in would cause this problem,
especially since we have alternate accounts that act as db owners and
administrators. is the built-in account somehing we should just put on the
side and leave alone? Thanks!add a login account for the Local System account
using sp_grantlogin [NT Authority\System] and ensure that the login account
is a member of the sysadmin fixed server role.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
> SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
> NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
> the
> side and leave alone? Thanks!|||hillary's idea sounds right to me. I will add that disabling the local
admins is a great security idea but the genius left out a step.
Create a SQL Server Administrators group in the domain. Add that group to
the local administrators group on the box. Add that group to the SQL Server
and make it a sysadmin. NOW take out the local administrators and you'll be
fine.
BTW: don't forget to add people to the SQL Server Administrators group.
d.
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
the
> side and leave alone? Thanks!

Building data warehouse

I have 35 small (<2 GB) OLTP databases containing about 80 tables. We have
a need to mine this data and do not want to do this on the OLTP servers and
would prefer creating a seperate large merged database on a different
system. There are no columns in the table that indicate which database they
came from.
I think it would be simple to clone any one of the OLTP databases on our
warehouse server, add a database name column to all the table and then write
a few simple append table queues which would allow to to consolidate the 35
database into the warehouse server.
This doesn't need to be done on a transactional basis and a nighly of weekly
merge would be sufficient.
Can anyone tell me if any replication methods would be better suited to do
this and if so specifically which kind/type/etc?
In the data on the warehouse I need to know which database the data came
from and I don't want to add a column to all the tables on all the databases
just to do this which is what got me into thinking the brute force approach
was a better idea.
Any comments of suggestions would be greatly appreciated.
Transactional would be best depending on the volume of transactions. If you
have a lot of transactions you will have to set a shorter distribution
frequency.
Merge would entail more processing than transactional, and snapshot would
send your entire set of tables each time which depending on the size of your
tables can be problematic.
"Myles Duffy" <MylesDuffy@.softwhere.com> wrote in message
news:%23V7%23QMOnEHA.392@.tk2msftngp13.phx.gbl...
>I have 35 small (<2 GB) OLTP databases containing about 80 tables. We have
> a need to mine this data and do not want to do this on the OLTP servers
> and
> would prefer creating a seperate large merged database on a different
> system. There are no columns in the table that indicate which database
> they
> came from.
> I think it would be simple to clone any one of the OLTP databases on our
> warehouse server, add a database name column to all the table and then
> write
> a few simple append table queues which would allow to to consolidate the
> 35
> database into the warehouse server.
> This doesn't need to be done on a transactional basis and a nighly of
> weekly
> merge would be sufficient.
> Can anyone tell me if any replication methods would be better suited to do
> this and if so specifically which kind/type/etc?
> In the data on the warehouse I need to know which database the data came
> from and I don't want to add a column to all the tables on all the
> databases
> just to do this which is what got me into thinking the brute force
> approach
> was a better idea.
> Any comments of suggestions would be greatly appreciated.
>

Build a Web Interface to transfer DATA between Sql servers 2005

Hello,
I would like to know if there is any SDK i can use to build a WebPage
which would enable me to transfer data between sql servers.
Purpose: Non technical people who are not familiar with using
administrative managers, would like to have a publish button where by
they can publish/move data from staging to production. Usually its done
via DTS, but they will not be able to handle DTS.
thanksIs linked servers an option? You didn't specify a language for your
project, but if it's ASP.NET what about just opening up two SqlConnections
to the two different servers and coding SELECTs from one and INSERTs/UPDATEs
into the other? There are a lot of options, but without more specifics it's
hard to say what would be useful for you...
"tillu" <tillu77@.gmail.com> wrote in message
news:1147818011.964849.297580@.j55g2000cwa.googlegroups.com...
> Hello,
> I would like to know if there is any SDK i can use to build a WebPage
> which would enable me to transfer data between sql servers.
> Purpose: Non technical people who are not familiar with using
> administrative managers, would like to have a publish button where by
> they can publish/move data from staging to production. Usually its done
> via DTS, but they will not be able to handle DTS.
> thanks
>