Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
Bill
You will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Friday, March 30, 2012
Problem with Charindex function
Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
Problem with Charindex function
Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
Problem with Bulk Upload. Uploads twice.
When I run the following stored procedure I get these results. My file has
24 rows in it. However I am uploading it twice for some reason.
Stored Procedure:
CREATE PROCEDURE [dbo].[dsp_Manual_Import]
(
@.PathFileName varchar(50),
@.SQL varchar(2000)
)
AS
SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
WITH (FIELDTERMINATOR = ',') "
EXEC (@.SQL)
return
GO
Results:
(24 row(s) affected)
(24 row(s) affected)
Stored Procedure: ILVS.dbo.dsp_Manual_Import
Return Code = 0Have you confirmed the duplicate rows were inserted by running a query
against the table afterward?
Are there any triggers on the table, perhaps it is inserting to an audit
table?
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
> When I run the following stored procedure I get these results. My file
> has
> 24 rows in it. However I am uploading it twice for some reason.
>
> Stored Procedure:
> CREATE PROCEDURE [dbo].[dsp_Manual_Import]
> (
> @.PathFileName varchar(50),
> @.SQL varchar(2000)
> )
> AS
>
> SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
> WITH (FIELDTERMINATOR = ',') "
> EXEC (@.SQL)
>
> return
> GO
> Results:
> (24 row(s) affected)
>
> (24 row(s) affected)
> Stored Procedure: ILVS.dbo.dsp_Manual_Import
> Return Code = 0|||Yes I checked the table and it is importing twice. There are no triggers.
"JT" wrote:
> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||Another concern is that I only am importing 24 records, and it is reporting
48.
"JT" wrote:
> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||That's strange. Just as an experiment, try the following and confirm if they
all behave the same way.
#1 Execute dsp_Manual_Import manually from Query Analyzer instead of from
your application.
#2 Execute the same bulk insert command from Query Analyzer instead of
from within the stored procedure.
#3 Execute a similar bulk insert command against a different table.
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:F28E3D8A-5386-4173-998C-5865E2866C23@.microsoft.com...
> Another concern is that I only am importing 24 records, and it is
> reporting 48.
> "JT" wrote:
>
24 rows in it. However I am uploading it twice for some reason.
Stored Procedure:
CREATE PROCEDURE [dbo].[dsp_Manual_Import]
(
@.PathFileName varchar(50),
@.SQL varchar(2000)
)
AS
SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
WITH (FIELDTERMINATOR = ',') "
EXEC (@.SQL)
return
GO
Results:
(24 row(s) affected)
(24 row(s) affected)
Stored Procedure: ILVS.dbo.dsp_Manual_Import
Return Code = 0Have you confirmed the duplicate rows were inserted by running a query
against the table afterward?
Are there any triggers on the table, perhaps it is inserting to an audit
table?
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
> When I run the following stored procedure I get these results. My file
> has
> 24 rows in it. However I am uploading it twice for some reason.
>
> Stored Procedure:
> CREATE PROCEDURE [dbo].[dsp_Manual_Import]
> (
> @.PathFileName varchar(50),
> @.SQL varchar(2000)
> )
> AS
>
> SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
> WITH (FIELDTERMINATOR = ',') "
> EXEC (@.SQL)
>
> return
> GO
> Results:
> (24 row(s) affected)
>
> (24 row(s) affected)
> Stored Procedure: ILVS.dbo.dsp_Manual_Import
> Return Code = 0|||Yes I checked the table and it is importing twice. There are no triggers.
"JT" wrote:
> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||Another concern is that I only am importing 24 records, and it is reporting
48.
"JT" wrote:
> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||That's strange. Just as an experiment, try the following and confirm if they
all behave the same way.
#1 Execute dsp_Manual_Import manually from Query Analyzer instead of from
your application.
#2 Execute the same bulk insert command from Query Analyzer instead of
from within the stored procedure.
#3 Execute a similar bulk insert command against a different table.
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:F28E3D8A-5386-4173-998C-5865E2866C23@.microsoft.com...
> Another concern is that I only am importing 24 records, and it is
> reporting 48.
> "JT" wrote:
>
Problem with BULK INSERT ASCII file into nvarchar column
Hi,
I have a problem with BULK INSERT. I created the following table:
Code Snippet
create table Test(id char(4), name nvarchar(16), last char(1))
I am trying to bulk insert data from ASCII (not unicode) file with only two rows:
0011First name
0018Second name
Since it is a fixed length file, I am using the following format file:
Code Snippet
8.0
3
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "" 2 NAME HEBREW_CI_AS
3 SQLCHAR 0 0 "\r\n" 3 Last HEBREW_CI_AS
With bcp utility everything works just fine!
Code Snippet
bcp Demo.dbo.test in c:\test -T -f c:\test.fmt
But when I use BULK INSERT in the following form:
Code Snippet
BULK INSERT Test FROM 'c:\Test'
WITH
(
FORMATFILE='c:\Test.fmt',
CODEPAGE='OEM'
);
I am getting error
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (name).
Now, one interesting thing: if I change the name field from nvarchar to varchar, it is working with BULK INSERT as well.
Can anybody explain what is going on here?
I am using MS SQL 2000 and MSDE
Thanks in advance,
Eugene.
Another thing is that if I set the format file to specify row delimiter for that nvarchar field, it will also work.
Code Snippet
8.02
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "\r\n" 2 NAME HEBREW_CI_AS
But then in the real system i can't have multiple fields within the file...
|||On SQL2005 the problem does not exist! Then it seems like a bug in SQL2000!
sqlWednesday, March 28, 2012
problem with bit datatype conversion
Hi All,
I have create following table and inserted few records
Code Snippet
USE
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_position](
[part] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[price] [money] NOT NULL,
[opt] [bit] NOT NULL
) ON [PRIMARY]
I am executing following procedure to update the table.
Code Snippet
USE
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )
AS
BEGIN
declare @.sSQL as nvarchar(max);
SET @.sSQL = 'update tbl_position set opt=@.OPT,price=@.PRICE WHERE part = '+ @.part;
exec sp_executesql @.sSQL
END
ex:
Code Snippet
exec PROC1 1,2.50,0but I am getting following error
Code Snippet
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@.OPT".
I am struck up with this query .
Please Help me regarding this issue.
Regards
Gomaz
Code Snippet
CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )
AS
BEGIN
declare @.sSQL as nvarchar(max);
SET @.sSQL = 'update tbl_position set opt=' +cast(@.OPT as char(1) )+',price='+@.PRICE+' WHERE part = '+ @.part;
exec sp_executesql @.sSQL
END
sp_executesql execute query in different bacth. As you couldn't use opt=@.OPT
Problem with BCP command
I am trying to execute following command using xp_cmdshell
EXEC master..xp_cmdshell 'bcp "select nc_value from fileade.dbo.nms_command (nolock) order by nc_pk desc" queryout \\filw\ExternalTools\NMS3\reports\NMS3_20070525_051507.txt -c -S"FILW2K" -Uabc -Pabc'
but the error message returned is
Copy direction must be either 'in' or 'out'.
Syntax Error in 'queryout'.
usage: bcp [[database_name.]owner.]table_name[
lice_number] {in | out} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c] [-t field_terminator] [-r row_terminator]
[-U username] [-P password] [-I interfaces_file] [-S server]
[-a display_charset] [-q datafile_charset] [-z language] [-v]
[-A packet size] [-J client character set]
[-T text or image size] [-E] [-g id_start_value] [-N] [-X]
[-M LabelName LabelValue] [-labeled]
[-K keytab_file] [-R remote_server_principal]
[-V [security_options]] [-Z security_mechanism] [-Q]
NULL
Further the same command is running successfully in my DEV environment.
That sounds like you have a SQL 7 bcp.exe in your path. Run "bcp -v" on both machines and make sure the versions match.|||Wrong forum. Moving to Transact-SQL.|||
When the path for the output file contains spaces or other 'non-acceptable' characters, you 'should' enclose it in double quotes.
The Server name does not need to be in double quotes.
|||Hi Tom,
I have checked the version on all the three plateform
DEV : 8.00.382
PROD : 8.00.382
TestPROD : 8.00.382
and version are same.
|||Hi Arnie,
The command is giving problem on Production environment only while in DEV & staging server working perfectly wheather to export file in local drive or in a network drive.
|||On the production server, does the SQL Agent account have permissions for the file locations?|||Not Sure how to check this,
BUt we have checkred that other scheduled JOBS which also export some file from PROD DB server to other server are working OK, Further the BCP command is executed via windows service
Windows Service
Batch File
Stored Procedure
BCP Command.
|||Somewhere you have an old bcp.exe which is being picked up. The error message you posted above is from the SQL 7 bcp.exe program, not 8.00.382. The "queryout" option was added in SQL 2000 bcp.exe.Search your hard drive for bcp.exe and remove anything not in C:\Program Files\Microsoft SQL Server\90 (or 80)\tools\binn.
|||
Hi Tom,
Your check point really help me to found the the problem although Production Hard Drive was not having the bcp.exe of version 7 instead the server was having Sybase BCP.EXE also , so whenever the window service try to execute the BCP command instead of picking up the SQL Server BCP path it was picking the Syabse BCP due to which the error was coming.
Thanks again for your help.
|||Good. I am glad you found it.Problem with BCP
Hi,
I execute this command in CMD:
bcp pubs..authors out c:\test.dat -T -n
I get the following error:
Code page 720 is not supported by SQL Server.
Unable to resolve column level collation.
I'm using Personal Edition on Win XP Prof. I have not modified anything in
pubs database.
Any help would be greatly appreciated.
LeilaHi
You may want to try the -C option to specify 720 or RAW. Alternatively a
format file specifying the collation may work.
http://msdn.microsoft.com/library/d...>
bcp_1y43.asp
John
"Leila" <leilas@.hotpop.com> wrote in message
news:eUUw97aTFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> I execute this command in CMD:
> bcp pubs..authors out c:\test.dat -T -n
> I get the following error:
> Code page 720 is not supported by SQL Server.
> Unable to resolve column level collation.
> I'm using Personal Edition on Win XP Prof. I have not modified anything in
> pubs database.
> Any help would be greatly appreciated.
> Leila
>
>|||Thanks John!
RAW worked :-)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:#gQu8whTFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi
> You may want to try the -C option to specify 720 or RAW. Alternatively a
> format file specifying the collation may work.
>
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_impt_bcp_1y43.asp
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eUUw97aTFHA.2172@.tk2msftngp13.phx.gbl...
in
>
I execute this command in CMD:
bcp pubs..authors out c:\test.dat -T -n
I get the following error:
Code page 720 is not supported by SQL Server.
Unable to resolve column level collation.
I'm using Personal Edition on Win XP Prof. I have not modified anything in
pubs database.
Any help would be greatly appreciated.
LeilaHi
You may want to try the -C option to specify 720 or RAW. Alternatively a
format file specifying the collation may work.
http://msdn.microsoft.com/library/d...>
bcp_1y43.asp
John
"Leila" <leilas@.hotpop.com> wrote in message
news:eUUw97aTFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> I execute this command in CMD:
> bcp pubs..authors out c:\test.dat -T -n
> I get the following error:
> Code page 720 is not supported by SQL Server.
> Unable to resolve column level collation.
> I'm using Personal Edition on Win XP Prof. I have not modified anything in
> pubs database.
> Any help would be greatly appreciated.
> Leila
>
>|||Thanks John!
RAW worked :-)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:#gQu8whTFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi
> You may want to try the -C option to specify 720 or RAW. Alternatively a
> format file specifying the collation may work.
>
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_impt_bcp_1y43.asp
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eUUw97aTFHA.2172@.tk2msftngp13.phx.gbl...
in
>
Problem with Backup
Hello!
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
PcyMgntProd backup, NOSKIP , STATS = 10, DESCRIPTION = Daily
backup, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Probl...ict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=874693
Hi
This may be the issue:
http://support.microsoft.com/default...b;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551220d0a@.dbf orumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
> 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Probl...ict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
PcyMgntProd backup, NOSKIP , STATS = 10, DESCRIPTION = Daily
backup, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Probl...ict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=874693
Hi
This may be the issue:
http://support.microsoft.com/default...b;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551220d0a@.dbf orumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
> 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Probl...ict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693
Problem with Backup
Hello!
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
PcyMgntProd backup, NOSKIP , STATS = 10, DESCRIPTION = Daily
backup, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Prob...pict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=874693Hi
This may be the issue:
http://support.microsoft.com/defaul...kb;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551
220d0a@.dbforumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
> 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Prob...pict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
PcyMgntProd backup, NOSKIP , STATS = 10, DESCRIPTION = Daily
backup, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Prob...pict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=874693Hi
This may be the issue:
http://support.microsoft.com/defaul...kb;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551
220d0a@.dbforumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
> 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Prob...pict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693
Problem with Backup
Hello!
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME = ?PcyMgntProd backup?, NOSKIP , STATS = 10, DESCRIPTION = ?Daily
backup?, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Problem-Backup-ftopict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=874693Hi
This may be the issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551220d0a@.dbforumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME => 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Problem-Backup-ftopict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME = ?PcyMgntProd backup?, NOSKIP , STATS = 10, DESCRIPTION = ?Daily
backup?, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Problem-Backup-ftopict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=874693Hi
This may be the issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551220d0a@.dbforumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME => 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Problem-Backup-ftopict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693
Monday, March 26, 2012
Problem with attaching database on network share (SQL Server 2005)
I'm trying to execute following command:
DBCC TRACEON (1807, -1);
sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
DataBase\PGDT\PGDT_log.ldf'
and I get following error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf" failed
with the operating system error 5(error not found).
Note: this file exists and I have full permissions
Any help appreciated.
Hi
Do you have permission to this path?
Why would you want to do that? RESTORE , doesn't help you?
"D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
> I'm trying to execute following command:
> DBCC TRACEON (1807, -1);
> sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
> DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
> DataBase\PGDT\PGDT_log.ldf'
> and I get following error:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf"
> failed with the operating system error 5(error not found).
>
> Note: this file exists and I have full permissions
> Any help appreciated.
>
|||Solved!
I had to start sql server under Administrator account, and not local
account.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euzNHQ$8FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi
> Do you have permission to this path?
> Why would you want to do that? RESTORE , doesn't help you?
>
> "D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
>
|||Yup - worked like that in SQL 2000 as well (for remote backups and
restores). You will probably need to check that other services e.g.
agent can still work with SQL running on the domain account. Agree with
above - a remote ATTACH sounds like a pretty risky idea - performance
will likely be lousy and could be fragile as network issues will
destabilise?
DBCC TRACEON (1807, -1);
sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
DataBase\PGDT\PGDT_log.ldf'
and I get following error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf" failed
with the operating system error 5(error not found).
Note: this file exists and I have full permissions
Any help appreciated.
Hi
Do you have permission to this path?
Why would you want to do that? RESTORE , doesn't help you?
"D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
> I'm trying to execute following command:
> DBCC TRACEON (1807, -1);
> sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
> DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
> DataBase\PGDT\PGDT_log.ldf'
> and I get following error:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf"
> failed with the operating system error 5(error not found).
>
> Note: this file exists and I have full permissions
> Any help appreciated.
>
|||Solved!
I had to start sql server under Administrator account, and not local
account.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euzNHQ$8FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi
> Do you have permission to this path?
> Why would you want to do that? RESTORE , doesn't help you?
>
> "D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
>
|||Yup - worked like that in SQL 2000 as well (for remote backups and
restores). You will probably need to check that other services e.g.
agent can still work with SQL running on the domain account. Agree with
above - a remote ATTACH sounds like a pretty risky idea - performance
will likely be lousy and could be fragile as network issues will
destabilise?
Problem with attaching database on network share (SQL Server 2005)
I'm trying to execute following command:
DBCC TRACEON (1807, -1);
sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
DataBase\PGDT\PGDT_log.ldf'
and I get following error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf" failed
with the operating system error 5(error not found).
Note: this file exists and I have full permissions
Any help appreciated.Hi
Do you have permission to this path?
Why would you want to do that? RESTORE , doesn't help you?
"D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
> I'm trying to execute following command:
> DBCC TRACEON (1807, -1);
> sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
> DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
> DataBase\PGDT\PGDT_log.ldf'
> and I get following error:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf"
> failed with the operating system error 5(error not found).
>
> Note: this file exists and I have full permissions
> Any help appreciated.
>|||Solved!
I had to start sql server under Administrator account, and not local
account.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euzNHQ$8FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi
> Do you have permission to this path?
> Why would you want to do that? RESTORE , doesn't help you?
>
> "D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
>|||Yup - worked like that in SQL 2000 as well (for remote backups and
restores). You will probably need to check that other services e.g.
agent can still work with SQL running on the domain account. Agree with
above - a remote ATTACH sounds like a pretty risky idea - performance
will likely be lousy and could be fragile as network issues will
destabilise?
DBCC TRACEON (1807, -1);
sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
DataBase\PGDT\PGDT_log.ldf'
and I get following error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf" failed
with the operating system error 5(error not found).
Note: this file exists and I have full permissions
Any help appreciated.Hi
Do you have permission to this path?
Why would you want to do that? RESTORE , doesn't help you?
"D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
> I'm trying to execute following command:
> DBCC TRACEON (1807, -1);
> sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
> DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
> DataBase\PGDT\PGDT_log.ldf'
> and I get following error:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf"
> failed with the operating system error 5(error not found).
>
> Note: this file exists and I have full permissions
> Any help appreciated.
>|||Solved!
I had to start sql server under Administrator account, and not local
account.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euzNHQ$8FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi
> Do you have permission to this path?
> Why would you want to do that? RESTORE , doesn't help you?
>
> "D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
>|||Yup - worked like that in SQL 2000 as well (for remote backups and
restores). You will probably need to check that other services e.g.
agent can still work with SQL running on the domain account. Agree with
above - a remote ATTACH sounds like a pretty risky idea - performance
will likely be lousy and could be fragile as network issues will
destabilise?
Problem with attaching database on network share (SQL Server 2005)
I'm trying to execute following command:
DBCC TRACEON (1807, -1);
sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
DataBase\PGDT\PGDT_log.ldf'
and I get following error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf" failed
with the operating system error 5(error not found).
Note: this file exists and I have full permissions
Any help appreciated.Hi
Do you have permission to this path?
Why would you want to do that? RESTORE , doesn't help you?
"D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
> I'm trying to execute following command:
> DBCC TRACEON (1807, -1);
> sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
> DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
> DataBase\PGDT\PGDT_log.ldf'
> and I get following error:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf"
> failed with the operating system error 5(error not found).
>
> Note: this file exists and I have full permissions
> Any help appreciated.
>|||Solved!
I had to start sql server under Administrator account, and not local
account.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euzNHQ$8FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi
> Do you have permission to this path?
> Why would you want to do that? RESTORE , doesn't help you?
>
> "D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
>> I'm trying to execute following command:
>> DBCC TRACEON (1807, -1);
>> sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
>> DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
>> DataBase\PGDT\PGDT_log.ldf'
>> and I get following error:
>> Msg 5133, Level 16, State 1, Line 1
>> Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf"
>> failed with the operating system error 5(error not found).
>>
>> Note: this file exists and I have full permissions
>> Any help appreciated.
>|||Yup - worked like that in SQL 2000 as well (for remote backups and
restores). You will probably need to check that other services e.g.
agent can still work with SQL running on the domain account. Agree with
above - a remote ATTACH sounds like a pretty risky idea - performance
will likely be lousy and could be fragile as network issues will
destabilise?sql
DBCC TRACEON (1807, -1);
sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
DataBase\PGDT\PGDT_log.ldf'
and I get following error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf" failed
with the operating system error 5(error not found).
Note: this file exists and I have full permissions
Any help appreciated.Hi
Do you have permission to this path?
Why would you want to do that? RESTORE , doesn't help you?
"D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
> I'm trying to execute following command:
> DBCC TRACEON (1807, -1);
> sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
> DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
> DataBase\PGDT\PGDT_log.ldf'
> and I get following error:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf"
> failed with the operating system error 5(error not found).
>
> Note: this file exists and I have full permissions
> Any help appreciated.
>|||Solved!
I had to start sql server under Administrator account, and not local
account.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euzNHQ$8FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi
> Do you have permission to this path?
> Why would you want to do that? RESTORE , doesn't help you?
>
> "D" <destro@.mail.com> wrote in message news:dmedq5$sv8$1@.ss405.t-com.hr...
>> I'm trying to execute following command:
>> DBCC TRACEON (1807, -1);
>> sp_attach_db @.dbname = N'PGDT', @.filename1 = N'\\Server\D\SQL
>> DataBase\PGDT\PGDT.mdf', @.filename2 = '\\Server\D\SQL
>> DataBase\PGDT\PGDT_log.ldf'
>> and I get following error:
>> Msg 5133, Level 16, State 1, Line 1
>> Directory lookup for the file "\\Server\D\SQL DataBase\PGDT\PGDT.mdf"
>> failed with the operating system error 5(error not found).
>>
>> Note: this file exists and I have full permissions
>> Any help appreciated.
>|||Yup - worked like that in SQL 2000 as well (for remote backups and
restores). You will probably need to check that other services e.g.
agent can still work with SQL running on the domain account. Agree with
above - a remote ATTACH sounds like a pretty risky idea - performance
will likely be lousy and could be fragile as network issues will
destabilise?sql
Problem with attaching database
I try to attach a db by running the following command:
EXEC sp_attach_single_file_db @.dbname = 'test',
@.physname = 'C:\Documents and Settings\renee.wilson\My Documents\PD DD 2.nvp'
But I get the following error message:
File activation failure. The physical file name "C:\Documents and Settings\renee.wilson\My Documents\PD DD 2_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
It looks like that the db was not detached from the server properly. I know that .LDF file does not exist, therefore, I was hoping by running the sp_attach_single_file_db, a new .LDF file can be created.
Any body has any idea on how to fix the problem? Thanks in advance.
Hi,one last chance left, here are some instructions for the crashd database:
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/f584b644548cd68f
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
Thanks, Jens.
But, I tried both suggestions in
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/f584b644548cd68f
without much luck.
I am having the following problems:
1. DBCC rebuilt_log does not exist in SQL Server 2005 Express which I am using.
2. When the database is in emergency mode, which I can see via the tool(Microsoft SQL Server Management Studio Express) I am using, I can not view any of its object, for example, I click on Tables(try to see the tables), I get error msg: Could not continue scan with NOLOCK due to data movement, (Microsoft SQL Server, Error:601). For the same reason, I can not generate scripts as suggested.
Any further suggestion? Thanks in advance.
Problem with Alter table disable trigger all
Hi,
I am replicating few production databases and I am running into a problem with "Alter table tblname disable trigger all". I get the following error: Cannot alter the table 'tblname' because it is being published for replication. I am aware that when you want to add or drop a column, you need to use some system stored procedures to do so. But how can I disable the triggers before running some queries/statement on a table that is being published to a subscriber?
I really apreciate you help,
Regards,
SasIt seems you have to use sp_dropsubscription and then sp_droparticle to discontinue replication of the table, do you alter, then use sp_addarticle and sp_addsubscription to add continue replication. If there is an easier way, I'd be interested to know as well.
http://www.databasejournal.com/features/mssql/article.php/1477441|||UPDATE sysobjects SET replinfo=0 WHERE xtype='U' AND name='YOUR TABLE'
It would disable the table in the publication.
When u r done with ur work, Add the table back into Publication through Enterprise-Manager.
However sometimes it goes messed-up and SQL doesn't allow to add the table back into publication. In that case u may need to reinialize the subscriptions followed by the SNAPSHOT.
Regards!!
I am replicating few production databases and I am running into a problem with "Alter table tblname disable trigger all". I get the following error: Cannot alter the table 'tblname' because it is being published for replication. I am aware that when you want to add or drop a column, you need to use some system stored procedures to do so. But how can I disable the triggers before running some queries/statement on a table that is being published to a subscriber?
I really apreciate you help,
Regards,
SasIt seems you have to use sp_dropsubscription and then sp_droparticle to discontinue replication of the table, do you alter, then use sp_addarticle and sp_addsubscription to add continue replication. If there is an easier way, I'd be interested to know as well.
http://www.databasejournal.com/features/mssql/article.php/1477441|||UPDATE sysobjects SET replinfo=0 WHERE xtype='U' AND name='YOUR TABLE'
It would disable the table in the publication.
When u r done with ur work, Add the table back into Publication through Enterprise-Manager.
However sometimes it goes messed-up and SQL doesn't allow to add the table back into publication. In that case u may need to reinialize the subscriptions followed by the SNAPSHOT.
Regards!!
Friday, March 23, 2012
Problem with a String formula
I need to change the following string formula to be Right Justified it is currently print out as Left. Can anyone help me change it.
//firstpay e-name 40 digits
StringVar T1;
StringVar T2;
StringVar ExStr;
NumberVar L;
NumberVar X;
T1:=' ';
X:=Length(T1);
ExStr:=left({CHECK_MSTR.NAME},40);
L:=Length(ExStr);
T2:=left(T1, X-L)+ExStr;
T2;right(replicatestring(' ', 40) & left(({CHECK_MSTR.NAME},40), 40)
//firstpay e-name 40 digits
StringVar T1;
StringVar T2;
StringVar ExStr;
NumberVar L;
NumberVar X;
T1:=' ';
X:=Length(T1);
ExStr:=left({CHECK_MSTR.NAME},40);
L:=Length(ExStr);
T2:=left(T1, X-L)+ExStr;
T2;right(replicatestring(' ', 40) & left(({CHECK_MSTR.NAME},40), 40)
Wednesday, March 21, 2012
Problem with a iif or a validation
Hello,
I have the following iif:
IIF(len(Fields!CustBirth.Value)<10,nothing,datediff(dateinterval.day, cdate(mid(Fields!CustBirth.Value,4,2)+"/"+left(...) + "/" + Right(...) )))
The thing that it is happening is that when i run a report without that CustBirth field i get an error stating that it cant convert the string "//" to date, of course it can't that is why i made the iif, how can i tell it to just try to execute the datediff when the field has the lenght bigger then 10 chars?
I have already installed SP2.
Thank you
Hello,
Please see this blog post. It describes the general issue with IIF that you are encountering. In a nutshell, IIF is a function which means that all arguments are evaluated before the function is called:
http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx
Best regards,
Chris
Tuesday, March 20, 2012
Problem with "Not in " operator
I have a table with 2 columns
The cloumn cp_lot_status can be null or 'PL','RE'
The following shows the current data
SQL> SELECT CP_LOT_NUMBER,CP_LOT_STATUS FROM CP_LOTS_GENERATED ;
CP_LOT_NUMBER CP
----- --
203750001
103750001
SQL> SELECT * FROM CP_LOTS_GENERATED WHERE CP_LOT_STATUS NOT IN ('PL','RE') AND
2 CP_LOT_STATUS IS NULL;
no rows selected
SQL> DESC CP_LOTS_GENERATED
Name Null? Type
---------------- --- -------
CP_LOT_NUMBER NOT NULL NUMBER(12)
CP_LOT_STATUS VARCHAR2(2)
So my question is does "Not in " operator not show the null values while doing the above query.IF so what could i do to show the null value rowsNULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:
WHERE CP_LOT_STATUS NOT IN ('PL','RE')
is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.
As for what you could do - well, just remove that predicate, it was redundant anyway:
SELECT * FROM CP_LOTS_GENERATED
WHERE CP_LOT_STATUS IS NULL;|||But the values 'PL' and 'RE' may occur at some point of time.....
Originally posted by andrewst
NULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:
WHERE CP_LOT_STATUS NOT IN ('PL','RE')
is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.
As for what you could do - well, just remove that predicate, it was redundant anyway:
SELECT * FROM CP_LOTS_GENERATED
WHERE CP_LOT_STATUS IS NULL;|||Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".
Maybe what you want is
WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
OR CP_LOT_STATUS IS NULL);|||yeah this worked out
thanx.................
Originally posted by andrewst
Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".
Maybe what you want is
WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
OR CP_LOT_STATUS IS NULL);
The cloumn cp_lot_status can be null or 'PL','RE'
The following shows the current data
SQL> SELECT CP_LOT_NUMBER,CP_LOT_STATUS FROM CP_LOTS_GENERATED ;
CP_LOT_NUMBER CP
----- --
203750001
103750001
SQL> SELECT * FROM CP_LOTS_GENERATED WHERE CP_LOT_STATUS NOT IN ('PL','RE') AND
2 CP_LOT_STATUS IS NULL;
no rows selected
SQL> DESC CP_LOTS_GENERATED
Name Null? Type
---------------- --- -------
CP_LOT_NUMBER NOT NULL NUMBER(12)
CP_LOT_STATUS VARCHAR2(2)
So my question is does "Not in " operator not show the null values while doing the above query.IF so what could i do to show the null value rowsNULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:
WHERE CP_LOT_STATUS NOT IN ('PL','RE')
is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.
As for what you could do - well, just remove that predicate, it was redundant anyway:
SELECT * FROM CP_LOTS_GENERATED
WHERE CP_LOT_STATUS IS NULL;|||But the values 'PL' and 'RE' may occur at some point of time.....
Originally posted by andrewst
NULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:
WHERE CP_LOT_STATUS NOT IN ('PL','RE')
is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.
As for what you could do - well, just remove that predicate, it was redundant anyway:
SELECT * FROM CP_LOTS_GENERATED
WHERE CP_LOT_STATUS IS NULL;|||Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".
Maybe what you want is
WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
OR CP_LOT_STATUS IS NULL);|||yeah this worked out
thanx.................
Originally posted by andrewst
Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".
Maybe what you want is
WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
OR CP_LOT_STATUS IS NULL);
Problem with "Delivering Replicated Transactions"
Greetings! Any help with the following situation would=20
be greatly appreciated. We have a push transactional=20
replication of a subset of the tables in a production=20
database involving 3 machines; the production db machine, =20
a distribution db machine, and a subscriber db machine to=20
which this table subset is replicated. The subscriber is=20
used for complex searches and has 6 indexed views resident=20
on it. All replication agents are set to run continuously.=20
The Distribution Agent profiles have been left at the=20
defaults except for QueryTimeout, which has been set to=20
3600.
Each morning recently we have seen the distribution=20
agent showing "Delivering Replicated Transactions", a=20
state which lasts approximately 1=BD hours. Since our search=20
volume is minimal in the wee hours we would like to shift=20
this state to occur at 2 or 3 AM. Is there any way to=20
eliminate or control the timing of this condition by=20
further adjusting Agent parameters, etc. Thank you.
Not by adjusting parameters. You can control when the agent runs through
scheduling. You would essentially go into the job running your distribution
agent and change the frequency or even the hours during which it will run.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Thanks for your reply, but it's necessary for us to run
the Distribution Agent continously to keep latency to a
minimum. Any other suggestions, especially in light of the
indexed views that must be continuously updated on the
subscriber?
>--Original Message--
>Not by adjusting parameters. You can control when the
agent runs through
>scheduling. You would essentially go into the job
running your distribution
>agent and change the frequency or even the hours during
which it will run.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||It seems there must be some batch operation occurring on your Publisher
which causes this "delivering replicated transactions" message.
See if you can isolate it using profiler on the publisher, distributor or
subscriber.
Then see if you can't change when this job kicks off.
Also try to replication the execution of a stored procedure to minimize the
impact of this process on your publisher/distributor.
"Fundster" <anonymous@.discussions.microsoft.com> wrote in message
news:2e9701c4288e$52687120$a001280a@.phx.gbl...
Greetings! Any help with the following situation would
be greatly appreciated. We have a push transactional
replication of a subset of the tables in a production
database involving 3 machines; the production db machine,
a distribution db machine, and a subscriber db machine to
which this table subset is replicated. The subscriber is
used for complex searches and has 6 indexed views resident
on it. All replication agents are set to run continuously.
The Distribution Agent profiles have been left at the
defaults except for QueryTimeout, which has been set to
3600.
Each morning recently we have seen the distribution
agent showing "Delivering Replicated Transactions", a
state which lasts approximately 1 hours. Since our search
volume is minimal in the wee hours we would like to shift
this state to occur at 2 or 3 AM. Is there any way to
eliminate or control the timing of this condition by
further adjusting Agent parameters, etc. Thank you.
be greatly appreciated. We have a push transactional=20
replication of a subset of the tables in a production=20
database involving 3 machines; the production db machine, =20
a distribution db machine, and a subscriber db machine to=20
which this table subset is replicated. The subscriber is=20
used for complex searches and has 6 indexed views resident=20
on it. All replication agents are set to run continuously.=20
The Distribution Agent profiles have been left at the=20
defaults except for QueryTimeout, which has been set to=20
3600.
Each morning recently we have seen the distribution=20
agent showing "Delivering Replicated Transactions", a=20
state which lasts approximately 1=BD hours. Since our search=20
volume is minimal in the wee hours we would like to shift=20
this state to occur at 2 or 3 AM. Is there any way to=20
eliminate or control the timing of this condition by=20
further adjusting Agent parameters, etc. Thank you.
Not by adjusting parameters. You can control when the agent runs through
scheduling. You would essentially go into the job running your distribution
agent and change the frequency or even the hours during which it will run.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Thanks for your reply, but it's necessary for us to run
the Distribution Agent continously to keep latency to a
minimum. Any other suggestions, especially in light of the
indexed views that must be continuously updated on the
subscriber?
>--Original Message--
>Not by adjusting parameters. You can control when the
agent runs through
>scheduling. You would essentially go into the job
running your distribution
>agent and change the frequency or even the hours during
which it will run.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||It seems there must be some batch operation occurring on your Publisher
which causes this "delivering replicated transactions" message.
See if you can isolate it using profiler on the publisher, distributor or
subscriber.
Then see if you can't change when this job kicks off.
Also try to replication the execution of a stored procedure to minimize the
impact of this process on your publisher/distributor.
"Fundster" <anonymous@.discussions.microsoft.com> wrote in message
news:2e9701c4288e$52687120$a001280a@.phx.gbl...
Greetings! Any help with the following situation would
be greatly appreciated. We have a push transactional
replication of a subset of the tables in a production
database involving 3 machines; the production db machine,
a distribution db machine, and a subscriber db machine to
which this table subset is replicated. The subscriber is
used for complex searches and has 6 indexed views resident
on it. All replication agents are set to run continuously.
The Distribution Agent profiles have been left at the
defaults except for QueryTimeout, which has been set to
3600.
Each morning recently we have seen the distribution
agent showing "Delivering Replicated Transactions", a
state which lasts approximately 1 hours. Since our search
volume is minimal in the wee hours we would like to shift
this state to occur at 2 or 3 AM. Is there any way to
eliminate or control the timing of this condition by
further adjusting Agent parameters, etc. Thank you.
Subscribe to:
Posts (Atom)