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:
>
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Problem with bcp
Hi all,
I'm using bcp from the command line to backup and
restore the data in a table.
To backup, I run a command like this:
bcp TestDatabase.dbo.tblTest out C:\test.txt -n
-S TestServer -U sa -P pwd
and this works fine. However, when I try to restore
the data that I have backed up by doing this
bcp TestDatabase.dbo.tblTest in C:\test.txt -n
-S TestServer -U sa -P pwd
I get the following error:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near 'varchar'.
Does anyone have any idea what the problem might
be?
TIA,
--
Akin
aknak at aksoto dot idps dot co dot ukuse the -n switch so you get a native dump.
Then, you'll have no trouble restoring as long as the schema is the same.
Otherwise, you've got to use format files.
(BTW, as a side not, BCP is not a backup utility).
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> Hi all,
> I'm using bcp from the command line to backup and
> restore the data in a table.
> To backup, I run a command like this:
> bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> -S TestServer -U sa -P pwd
> and this works fine. However, when I try to restore
> the data that I have backed up by doing this
> bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> -S TestServer -U sa -P pwd
> I get the following error:
> SQLState = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> syntax near 'varchar'.
> Does anyone have any idea what the problem might
> be?
> TIA,
>
> --
> Akin
> aknak at aksoto dot idps dot co dot uk
>
>|||Hello James,
Thanks for your reply.
If you see the command line I typed out, you will see that I
*am* using the -n switch, but the error still occurs.
Any other ideas?
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> use the -n switch so you get a native dump.
> Then, you'll have no trouble restoring as long as the schema is the same.
> Otherwise, you've got to use format files.
> (BTW, as a side not, BCP is not a backup utility).
> James Hokes
>
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > Hi all,
> >
> > I'm using bcp from the command line to backup and
> > restore the data in a table.
> >
> > To backup, I run a command like this:
> >
> > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > and this works fine. However, when I try to restore
> > the data that I have backed up by doing this
> >
> > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > I get the following error:
> >
> > SQLState = 37000, NativeError = 170
> > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> > syntax near 'varchar'.
> >
> > Does anyone have any idea what the problem might
> > be?
> >
> > TIA,
> >
> >
> > --
> > Akin
> >
> > aknak at aksoto dot idps dot co dot uk
> >
> >
> >
>|||Doh!
Thanks for not flaming - chalk one up to 'reading too fast, eh?'
Now I'm well and fully stumped.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> Hello James,
> Thanks for your reply.
> If you see the command line I typed out, you will see that I
> *am* using the -n switch, but the error still occurs.
> Any other ideas?
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > use the -n switch so you get a native dump.
> > Then, you'll have no trouble restoring as long as the schema is the
same.
> > Otherwise, you've got to use format files.
> >
> > (BTW, as a side not, BCP is not a backup utility).
> >
> > James Hokes
> >
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > Hi all,
> > >
> > > I'm using bcp from the command line to backup and
> > > restore the data in a table.
> > >
> > > To backup, I run a command like this:
> > >
> > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > and this works fine. However, when I try to restore
> > > the data that I have backed up by doing this
> > >
> > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > I get the following error:
> > >
> > > SQLState = 37000, NativeError = 170
> > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect
> > > syntax near 'varchar'.
> > >
> > > Does anyone have any idea what the problem might
> > > be?
> > >
> > > TIA,
> > >
> > >
> > > --
> > > Akin
> > >
> > > aknak at aksoto dot idps dot co dot uk
> > >
> > >
> > >
> >
> >
>|||OK James, I figured it out. The problem was that I had
some fields in the table which had spaces between their
names, like '[First Name]' and I wasn't using the quoted
identifier switch (-q). I thought I only needed to use
this when the name of the *table* or *database* whose
data I wanted to import had a space, but this seems to
apply to fields to.
Cheers,
Akin
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Doh!
> Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> Now I'm well and fully stumped.
> James Hokes
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > Hello James,
> >
> > Thanks for your reply.
> >
> > If you see the command line I typed out, you will see that I
> > *am* using the -n switch, but the error still occurs.
> >
> > Any other ideas?
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > use the -n switch so you get a native dump.
> > > Then, you'll have no trouble restoring as long as the schema is the
> same.
> > > Otherwise, you've got to use format files.
> > >
> > > (BTW, as a side not, BCP is not a backup utility).
> > >
> > > James Hokes
> > >
> > >
> > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > Hi all,
> > > >
> > > > I'm using bcp from the command line to backup and
> > > > restore the data in a table.
> > > >
> > > > To backup, I run a command like this:
> > > >
> > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > and this works fine. However, when I try to restore
> > > > the data that I have backed up by doing this
> > > >
> > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > I get the following error:
> > > >
> > > > SQLState = 37000, NativeError = 170
> > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect
> > > > syntax near 'varchar'.
> > > >
> > > > Does anyone have any idea what the problem might
> > > > be?
> > > >
> > > > TIA,
> > > >
> > > >
> > > > --
> > > > Akin
> > > >
> > > > aknak at aksoto dot idps dot co dot uk
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sky Fly,
Hey, way to go! That was one that I had not thought of, but now,thanks to
you, I'll keep it in my bag of tricks.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs14nd$8dlel$1@.ID-18325.news.uni-berlin.de...
> OK James, I figured it out. The problem was that I had
> some fields in the table which had spaces between their
> names, like '[First Name]' and I wasn't using the quoted
> identifier switch (-q). I thought I only needed to use
> this when the name of the *table* or *database* whose
> data I wanted to import had a space, but this seems to
> apply to fields to.
> Cheers,
> Akin
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Doh!
> >
> > Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> >
> > Now I'm well and fully stumped.
> >
> > James Hokes
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > > Hello James,
> > >
> > > Thanks for your reply.
> > >
> > > If you see the command line I typed out, you will see that I
> > > *am* using the -n switch, but the error still occurs.
> > >
> > > Any other ideas?
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > > use the -n switch so you get a native dump.
> > > > Then, you'll have no trouble restoring as long as the schema is the
> > same.
> > > > Otherwise, you've got to use format files.
> > > >
> > > > (BTW, as a side not, BCP is not a backup utility).
> > > >
> > > > James Hokes
> > > >
> > > >
> > > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > > Hi all,
> > > > >
> > > > > I'm using bcp from the command line to backup and
> > > > > restore the data in a table.
> > > > >
> > > > > To backup, I run a command like this:
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > and this works fine. However, when I try to restore
> > > > > the data that I have backed up by doing this
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > I get the following error:
> > > > >
> > > > > SQLState = 37000, NativeError = 170
> > > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> > Incorrect
> > > > > syntax near 'varchar'.
> > > > >
> > > > > Does anyone have any idea what the problem might
> > > > > be?
> > > > >
> > > > > TIA,
> > > > >
> > > > >
> > > > > --
> > > > > Akin
> > > > >
> > > > > aknak at aksoto dot idps dot co dot uk
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
I'm using bcp from the command line to backup and
restore the data in a table.
To backup, I run a command like this:
bcp TestDatabase.dbo.tblTest out C:\test.txt -n
-S TestServer -U sa -P pwd
and this works fine. However, when I try to restore
the data that I have backed up by doing this
bcp TestDatabase.dbo.tblTest in C:\test.txt -n
-S TestServer -U sa -P pwd
I get the following error:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near 'varchar'.
Does anyone have any idea what the problem might
be?
TIA,
--
Akin
aknak at aksoto dot idps dot co dot ukuse the -n switch so you get a native dump.
Then, you'll have no trouble restoring as long as the schema is the same.
Otherwise, you've got to use format files.
(BTW, as a side not, BCP is not a backup utility).
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> Hi all,
> I'm using bcp from the command line to backup and
> restore the data in a table.
> To backup, I run a command like this:
> bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> -S TestServer -U sa -P pwd
> and this works fine. However, when I try to restore
> the data that I have backed up by doing this
> bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> -S TestServer -U sa -P pwd
> I get the following error:
> SQLState = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> syntax near 'varchar'.
> Does anyone have any idea what the problem might
> be?
> TIA,
>
> --
> Akin
> aknak at aksoto dot idps dot co dot uk
>
>|||Hello James,
Thanks for your reply.
If you see the command line I typed out, you will see that I
*am* using the -n switch, but the error still occurs.
Any other ideas?
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> use the -n switch so you get a native dump.
> Then, you'll have no trouble restoring as long as the schema is the same.
> Otherwise, you've got to use format files.
> (BTW, as a side not, BCP is not a backup utility).
> James Hokes
>
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > Hi all,
> >
> > I'm using bcp from the command line to backup and
> > restore the data in a table.
> >
> > To backup, I run a command like this:
> >
> > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > and this works fine. However, when I try to restore
> > the data that I have backed up by doing this
> >
> > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > I get the following error:
> >
> > SQLState = 37000, NativeError = 170
> > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> > syntax near 'varchar'.
> >
> > Does anyone have any idea what the problem might
> > be?
> >
> > TIA,
> >
> >
> > --
> > Akin
> >
> > aknak at aksoto dot idps dot co dot uk
> >
> >
> >
>|||Doh!
Thanks for not flaming - chalk one up to 'reading too fast, eh?'
Now I'm well and fully stumped.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> Hello James,
> Thanks for your reply.
> If you see the command line I typed out, you will see that I
> *am* using the -n switch, but the error still occurs.
> Any other ideas?
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > use the -n switch so you get a native dump.
> > Then, you'll have no trouble restoring as long as the schema is the
same.
> > Otherwise, you've got to use format files.
> >
> > (BTW, as a side not, BCP is not a backup utility).
> >
> > James Hokes
> >
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > Hi all,
> > >
> > > I'm using bcp from the command line to backup and
> > > restore the data in a table.
> > >
> > > To backup, I run a command like this:
> > >
> > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > and this works fine. However, when I try to restore
> > > the data that I have backed up by doing this
> > >
> > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > I get the following error:
> > >
> > > SQLState = 37000, NativeError = 170
> > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect
> > > syntax near 'varchar'.
> > >
> > > Does anyone have any idea what the problem might
> > > be?
> > >
> > > TIA,
> > >
> > >
> > > --
> > > Akin
> > >
> > > aknak at aksoto dot idps dot co dot uk
> > >
> > >
> > >
> >
> >
>|||OK James, I figured it out. The problem was that I had
some fields in the table which had spaces between their
names, like '[First Name]' and I wasn't using the quoted
identifier switch (-q). I thought I only needed to use
this when the name of the *table* or *database* whose
data I wanted to import had a space, but this seems to
apply to fields to.
Cheers,
Akin
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Doh!
> Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> Now I'm well and fully stumped.
> James Hokes
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > Hello James,
> >
> > Thanks for your reply.
> >
> > If you see the command line I typed out, you will see that I
> > *am* using the -n switch, but the error still occurs.
> >
> > Any other ideas?
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > use the -n switch so you get a native dump.
> > > Then, you'll have no trouble restoring as long as the schema is the
> same.
> > > Otherwise, you've got to use format files.
> > >
> > > (BTW, as a side not, BCP is not a backup utility).
> > >
> > > James Hokes
> > >
> > >
> > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > Hi all,
> > > >
> > > > I'm using bcp from the command line to backup and
> > > > restore the data in a table.
> > > >
> > > > To backup, I run a command like this:
> > > >
> > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > and this works fine. However, when I try to restore
> > > > the data that I have backed up by doing this
> > > >
> > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > I get the following error:
> > > >
> > > > SQLState = 37000, NativeError = 170
> > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect
> > > > syntax near 'varchar'.
> > > >
> > > > Does anyone have any idea what the problem might
> > > > be?
> > > >
> > > > TIA,
> > > >
> > > >
> > > > --
> > > > Akin
> > > >
> > > > aknak at aksoto dot idps dot co dot uk
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sky Fly,
Hey, way to go! That was one that I had not thought of, but now,thanks to
you, I'll keep it in my bag of tricks.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs14nd$8dlel$1@.ID-18325.news.uni-berlin.de...
> OK James, I figured it out. The problem was that I had
> some fields in the table which had spaces between their
> names, like '[First Name]' and I wasn't using the quoted
> identifier switch (-q). I thought I only needed to use
> this when the name of the *table* or *database* whose
> data I wanted to import had a space, but this seems to
> apply to fields to.
> Cheers,
> Akin
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Doh!
> >
> > Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> >
> > Now I'm well and fully stumped.
> >
> > James Hokes
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > > Hello James,
> > >
> > > Thanks for your reply.
> > >
> > > If you see the command line I typed out, you will see that I
> > > *am* using the -n switch, but the error still occurs.
> > >
> > > Any other ideas?
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > > use the -n switch so you get a native dump.
> > > > Then, you'll have no trouble restoring as long as the schema is the
> > same.
> > > > Otherwise, you've got to use format files.
> > > >
> > > > (BTW, as a side not, BCP is not a backup utility).
> > > >
> > > > James Hokes
> > > >
> > > >
> > > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > > Hi all,
> > > > >
> > > > > I'm using bcp from the command line to backup and
> > > > > restore the data in a table.
> > > > >
> > > > > To backup, I run a command like this:
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > and this works fine. However, when I try to restore
> > > > > the data that I have backed up by doing this
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > I get the following error:
> > > > >
> > > > > SQLState = 37000, NativeError = 170
> > > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> > Incorrect
> > > > > syntax near 'varchar'.
> > > > >
> > > > > Does anyone have any idea what the problem might
> > > > > be?
> > > > >
> > > > > TIA,
> > > > >
> > > > >
> > > > > --
> > > > > Akin
> > > > >
> > > > > aknak at aksoto dot idps dot co dot uk
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Friday, March 23, 2012
Problem with accessing stored procedure in the report
Hi,
I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:
But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.Stack Trace:
[FormatException: Input string was not in a correct format.] System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715 System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102 Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64 I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.
I am not sure why I am getting this error and I greatly appreciate any help.
Thanks
ngk,
I have seen this before. Are you using any schema namespaces in your stored procedure name.
Such as HumanResources.GetAllEmployees instead of the old default of dbo.GetAllEmployees.
If so are you also running your stored procedures in Query Analyser as the "SAME" user that your Report Datasource uses.
Pay close attention to these details...
I have seen where 1 sql user has the default schema set to HumanResources and the stored procedure call is made such as exec GetAllEmployees instead of
exec HumanResources.GetAllEmployees.
In this situation any user that makes the first call (exec GetAllEmployees ) and has the default schema of HumanResources will succeed.
Any user that does not have this default will return an error because it is looking for dbo.GetAllEmployees and this may not exist.
Hope this helps.. if not please provide more details on what users you are using and the exact call you are making for the stored procedure.
|||Hi Bret,
Thanks for the info. I am not using any schema namespaces in my stored procedures. I actually got this error when I tried to connect to a remote sql server. Now I have installed developer editon on my local machine and I have reporting services also on my local machine. I have been able to connect to the strored procedures and deploy the reports to the local report server and view the reports without any problems. I am not sure whether I may have to face the same issue when the reports are deployed to the remote SQL DB and Reporting Services production servers.
Thanks
Problem with accessing stored procedure in the report
Hi,
I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:
But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[FormatException: Input string was not in a correct format.] System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715 System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102 Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64 I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.
I am not sure why I am getting this error and I greatly appreciate any help.
Thanks
ngk,
I have seen this before. Are you using any schema namespaces in your stored procedure name.
Such as HumanResources.GetAllEmployees instead of the old default of dbo.GetAllEmployees.
If so are you also running your stored procedures in Query Analyser as the "SAME" user that your Report Datasource uses.
Pay close attention to these details...
I have seen where 1 sql user has the default schema set to HumanResources and the stored procedure call is made such as exec GetAllEmployees instead of
exec HumanResources.GetAllEmployees.
In this situation any user that makes the first call (exec GetAllEmployees ) and has the default schema of HumanResources will succeed.
Any user that does not have this default will return an error because it is looking for dbo.GetAllEmployees and this may not exist.
Hope this helps.. if not please provide more details on what users you are using and the exact call you are making for the stored procedure.
|||Hi Bret,
Thanks for the info. I am not using any schema namespaces in my stored procedures. I actually got this error when I tried to connect to a remote sql server. Now I have installed developer editon on my local machine and I have reporting services also on my local machine. I have been able to connect to the strored procedures and deploy the reports to the local report server and view the reports without any problems. I am not sure whether I may have to face the same issue when the reports are deployed to the remote SQL DB and Reporting Services production servers.
Thanks
Problem with accessing stored procedure in the report
Hi,
I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:
But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[FormatException: Input string was not in a correct format.] System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102
Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.
I am not sure why I am getting this error and I greatly appreciate any help.
Thanks
ngk,
I have seen this before. Are you using any schema namespaces in your stored procedure name.
Such as HumanResources.GetAllEmployees instead of the old default of dbo.GetAllEmployees.
If so are you also running your stored procedures in Query Analyser as the "SAME" user that your Report Datasource uses.
Pay close attention to these details...
I have seen where 1 sql user has the default schema set to HumanResources and the stored procedure call is made such as exec GetAllEmployees instead of
exec HumanResources.GetAllEmployees.
In this situation any user that makes the first call (exec GetAllEmployees ) and has the default schema of HumanResources will succeed.
Any user that does not have this default will return an error because it is looking for dbo.GetAllEmployees and this may not exist.
Hope this helps.. if not please provide more details on what users you are using and the exact call you are making for the stored procedure.
|||Hi Bret,
Thanks for the info. I am not using any schema namespaces in my stored procedures. I actually got this error when I tried to connect to a remote sql server. Now I have installed developer editon on my local machine and I have reporting services also on my local machine. I have been able to connect to the strored procedures and deploy the reports to the local report server and view the reports without any problems. I am not sure whether I may have to face the same issue when the reports are deployed to the remote SQL DB and Reporting Services production servers.
Thanks
Wednesday, March 21, 2012
Problem with a proxy
Hi
i have a problem with a proxy and i don't know how to resolve this
So, i want to run a SSIS package so i follow this http://support.microsoft.com/kb/918760
However, the package fails and i have a error message which tell me that i can't get proxy's data for proxy_id = 23!
If someone has the solution please help me!!
A little more information would help.
How have you set up the proxy and credentials?
K
sqlTuesday, March 20, 2012
Problem wit sp_execute SQL
I have to make a large number of updates (about 29k) so I generated teh update statements into a table and am trying to sue sp_executesql to run them. Here is my code:
Declare @.SQLState NVARCHAR(500)
Declare Code Cursor
for
select SQLState from updates
open Code
FETCH NEXT FROM Code
into @.SQLState
While @.@.fetch_Status = 0
Begin
Exec sp_executesql @.SQLState
FETCH NEXT FROM Code
END
CLOSE Code
DEALLOCATE Code
IT appears to run succesfully, but the updates never happen - I get the following results for each update line:
UPDATE REEmployeeEvent SET UpdatedByEmployeeID= '00013' Where UpdatedByEmployeeID='00279'
(1 row(s) affected)
(0 row(s) affected)
Any ideas what I am doing wrong?
BTW - If I run the statements manually, they do work.
Thanks for any help!is there any particular reason why you want to use such a non-standard method to execute a script?
Why not execute dump them to a file, and execute the file as a single batch? At least that way you could visually inspect the scripts for correctness.
What you are trying to do here seems risky at best.
Declare @.SQLState NVARCHAR(500)
Declare Code Cursor
for
select SQLState from updates
open Code
FETCH NEXT FROM Code
into @.SQLState
While @.@.fetch_Status = 0
Begin
Exec sp_executesql @.SQLState
FETCH NEXT FROM Code
END
CLOSE Code
DEALLOCATE Code
IT appears to run succesfully, but the updates never happen - I get the following results for each update line:
UPDATE REEmployeeEvent SET UpdatedByEmployeeID= '00013' Where UpdatedByEmployeeID='00279'
(1 row(s) affected)
(0 row(s) affected)
Any ideas what I am doing wrong?
BTW - If I run the statements manually, they do work.
Thanks for any help!is there any particular reason why you want to use such a non-standard method to execute a script?
Why not execute dump them to a file, and execute the file as a single batch? At least that way you could visually inspect the scripts for correctness.
What you are trying to do here seems risky at best.
Wednesday, March 7, 2012
Problem Warming Cache (Fails to Run MDX Statement)
Hello all – I’m running into an issue that has me a little stuck and I was hoping to get your advice.I have an SSIS package which runs after my dimension / cube processing that iterates through a relational table containing MDX statements (from several key reports) and executes them to warm the cache.
This has been a very successful strategy for me until the recent addition of a MDX statement that absolutely refuses to be executed via SSIS using the ADO.NET connection type / MSOLAP.3 provider.This MDX statement will run fine in Management Studio as well as from the report.To make matters worse, if I run the MDX statement from the report or from Management Studio, the SSIS package will not fail on this particular statement.It only fails if the cache is cold:
{SQL Server Analysis Services 9.0 build 3042 (SP2)}
Error: 0xC002F210 at Run MDX Query, Execute SQL Task: Executing the query " SELECT NON EMPTY { [Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals], [Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume], [Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders], [Measures].[Volume - Actuals] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Location].[Location Name].[Location Name].ALLMEMBERS * [Profile].[Profile].[Profile].ALLMEMBERS * [Location].[Location ID].[Location ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Closure Flash Current] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" failed with the following error: "Errors in the back-end database access module. The data provider does not support preparing queries.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
SELECT NON EMPTY
{[Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals],
[Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume],
[Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders],
[Measures].[Volume - Actuals] } ON COLUMNS,
NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS *
[Location].[Location Name].[Location Name].ALLMEMBERS *
[Profile].[Profile].[Profile].ALLMEMBERS *
[Location].[Location ID].[Location ID].ALLMEMBERS ) } ON ROWS
FROM [Closure Flash Current]
I’m sure I’m missing something obvious, but whatever it may be is successfully stumping me.I appreciate any help or advice you can provide!
I figured it out; thought I would share it with all in-case you run across a similar scenario (I know when I was searching for this problem I found very little out there in the way of help):
When I ran profiler against the SSAS instance I noticed that it was trying to resolve the offending MDX statement into T-SQL statements (like you would expect to see in ROLAP storage) but it was attempting to PREPARE them against the SSAS instance, which of course would never work.
After some investigation I found that one of the partitions on the cube had been set to ROLAP and was causing the issue. After converting to MOLAP and deploying / processing, the issue went away and now my cache warming SSIS package is successful.
I would argue that this is a bug since the provider from SSIS is trying to prepare the T-SQL statements for a ROLAP cube against SSAS, but the same behavior isn't experienced in SSMS / SSRS.
Problem Warming Cache (Fails to Run MDX Statement)
Hello all – I’m running into an issue that has me a little stuck and I was hoping to get your advice.I have an SSIS package which runs after my dimension / cube processing that iterates through a relational table containing MDX statements (from several key reports) and executes them to warm the cache.
This has been a very successful strategy for me until the recent addition of a MDX statement that absolutely refuses to be executed via SSIS using the ADO.NET connection type / MSOLAP.3 provider.This MDX statement will run fine in Management Studio as well as from the report.To make matters worse, if I run the MDX statement from the report or from Management Studio, the SSIS package will not fail on this particular statement.It only fails if the cache is cold:
{SQL Server Analysis Services 9.0 build 3042 (SP2)}
Error: 0xC002F210 at Run MDX Query, Execute SQL Task: Executing the query " SELECT NON EMPTY { [Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals], [Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume], [Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders], [Measures].[Volume - Actuals] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Location].[Location Name].[Location Name].ALLMEMBERS * [Profile].[Profile].[Profile].ALLMEMBERS * [Location].[Location ID].[Location ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Closure Flash Current] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" failed with the following error: "Errors in the back-end database access module. The data provider does not support preparing queries.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
SELECTNONEMPTY
{[Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals],
[Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume],
[Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders],
[Measures].[Volume - Actuals] }ONCOLUMNS,
NONEMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS *
[Location].[Location Name].[Location Name].ALLMEMBERS *
[Profile].[Profile].[Profile].ALLMEMBERS *
[Location].[Location ID].[Location ID].ALLMEMBERS ) }ONROWS
FROM [Closure Flash Current]
I’m sure I’m missing something obvious, but whatever it may be is successfully stumping me.I appreciate any help or advice you can provide!
I figured it out; thought I would share it with all in-case you run across a similar scenario (I know when I was searching for this problem I found very little out there in the way of help):
When I ran profiler against the SSAS instance I noticed that it was trying to resolve the offending MDX statement into T-SQL statements (like you would expect to see in ROLAP storage) but it was attempting to PREPARE them against the SSAS instance, which of course would never work.
After some investigation I found that one of the partitions on the cube had been set to ROLAP and was causing the issue. After converting to MOLAP and deploying / processing, the issue went away and now my cache warming SSIS package is successful.
I would argue that this is a bug since the provider from SSIS is trying to prepare the T-SQL statements for a ROLAP cube against SSAS, but the same behavior isn't experienced in SSMS / SSRS.
Saturday, February 25, 2012
Problem using sp_attach_db with an encypted file system
I want to run a copy of our Sql2000 production database on my WinXP
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.com/ck_database_encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.com/ck_database_encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...
> > I want to run a copy of our Sql2000 production database on my WinXP
> > laptop for development. Because this database contains sensitive
> > information and the laptop cannot be physically secured, I have
> > enabled File Encryption on the project directory to protect the
> > database in the event that someone steals the laptop. To set up a
> > development environment I installed Sql2000 personal edition, and
> > disconnected the production database with:
> >
> > EXEC sp_detach_db @.dbname ='myDB'
> >
> > I then copied the mdf and log files to the encrypted directory on the
> > laptop and attempted to attach with:
> >
> > EXEC sp_attach_db @.dbname = N'myDB',
> > @.filename1 = N'C:\Projects\Data.mdf',
> > @.filename2 = N'C:\Projects\Log.ldf'
> >
> > This failed with the error message: "Device activation error. The
> > physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> > freaking me out, because the exact same command worked fine on the
> > production server to reattach the database. After some trial and
> > error, I removed file encryption on the mdf and ldf file and the
> > database attached without any problem.
> >
> > So my questions is, is this a known problem and is it possible to have
> > file encryption on an SQL database?
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.com/ck_database_encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.com/ck_database_encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...
> > I want to run a copy of our Sql2000 production database on my WinXP
> > laptop for development. Because this database contains sensitive
> > information and the laptop cannot be physically secured, I have
> > enabled File Encryption on the project directory to protect the
> > database in the event that someone steals the laptop. To set up a
> > development environment I installed Sql2000 personal edition, and
> > disconnected the production database with:
> >
> > EXEC sp_detach_db @.dbname ='myDB'
> >
> > I then copied the mdf and log files to the encrypted directory on the
> > laptop and attempted to attach with:
> >
> > EXEC sp_attach_db @.dbname = N'myDB',
> > @.filename1 = N'C:\Projects\Data.mdf',
> > @.filename2 = N'C:\Projects\Log.ldf'
> >
> > This failed with the error message: "Device activation error. The
> > physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> > freaking me out, because the exact same command worked fine on the
> > production server to reattach the database. After some trial and
> > error, I removed file encryption on the mdf and ldf file and the
> > database attached without any problem.
> >
> > So my questions is, is this a known problem and is it possible to have
> > file encryption on an SQL database?
Problem using sp_attach_db with an encypted file system
I want to run a copy of our Sql2000 production database on my WinXP
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?
Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.co...encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.co m...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?
|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.co...encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.co m...
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?
Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.co...encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.co m...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?
|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.co...encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.co m...
Problem using sp_attach_db with an encypted file system
I want to run a copy of our Sql2000 production database on my WinXP
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.c..._encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP
12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files resi
de
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected v
ia
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database wi
ll
> not start.
> See the below link:-
> http://www.sql-server-performance.c..._encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.c..._encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP
12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files resi
de
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected v
ia
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database wi
ll
> not start.
> See the below link:-
> http://www.sql-server-performance.c..._encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...
Problem using EXEC() to run DBCC DBREINDEX
I am trying to run DBCC DBREINDEX using EXEC(), code is below.
Based upon the error message at the bottom, the @.currenttable variable
receives the value 1 but when @.currenttable is referenece in the DBCC
statement, the value isn't there. Can anyone tell me what I'm doing wrong?
declare @.sqltest varchar(40), @.currenttable int
set @.currenttable = (select table_id from Table_Space where table_id = 1)
set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
print @.currenttable
print @.sqltest
EXEC(@.sqltest)
Below is the message I get:
1
DBCC DBREINDEX('@.currenttable','',75)
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named '@.currenttable'. Check sysobjects.nosurfdj,
DBCC DBREINDEX expects a table name and there is not table named
'@.currenttable'.
declare @.sqltest varchar(40), @.currenttable int
declare @.tn sysname
set @.tn = (select table_name from Table_Space where table_id = 1)
set @.sqltest = 'DBCC DBREINDEX(''' + @.tn + ''','''',75)'
print @.currenttable
print @.sqltest
EXEC(@.sqltest)
go
AMB
"nosurfdj" wrote:
> I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @.currenttable variable
> receives the value 1 but when @.currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong
?
> declare @.sqltest varchar(40), @.currenttable int
> set @.currenttable = (select table_id from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
> Below is the message I get:
> 1
> DBCC DBREINDEX('@.currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@.currenttable'. Check sysobjects.
>|||Quote problems around ''@.currenttable''.
Try:
'DBCC DBREINDEX(' + @.currenttable + ','''',75)'
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"nosurfdj" <nosurfdj@.discussions.microsoft.com> wrote in message news:2406FBD3-FD2A-4F69-8A
8E-F446DC1473BF@.microsoft.com...
>I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @.currenttable variable
> receives the value 1 but when @.currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong
?
>
> declare @.sqltest varchar(40), @.currenttable int
> set @.currenttable = (select table_id from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
>
> Below is the message I get:
> 1
> DBCC DBREINDEX('@.currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@.currenttable'. Check sysobjects.
>|||I knew it was going to be something simple.
Thanks for your help-that did it.
"Alejandro Mesa" wrote:
> nosurfdj,
> DBCC DBREINDEX expects a table name and there is not table named
> '@.currenttable'.
> declare @.sqltest varchar(40), @.currenttable int
> declare @.tn sysname
> set @.tn = (select table_name from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''' + @.tn + ''','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
> go
>
> AMB
> "nosurfdj" wrote:
>
Based upon the error message at the bottom, the @.currenttable variable
receives the value 1 but when @.currenttable is referenece in the DBCC
statement, the value isn't there. Can anyone tell me what I'm doing wrong?
declare @.sqltest varchar(40), @.currenttable int
set @.currenttable = (select table_id from Table_Space where table_id = 1)
set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
print @.currenttable
print @.sqltest
EXEC(@.sqltest)
Below is the message I get:
1
DBCC DBREINDEX('@.currenttable','',75)
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named '@.currenttable'. Check sysobjects.nosurfdj,
DBCC DBREINDEX expects a table name and there is not table named
'@.currenttable'.
declare @.sqltest varchar(40), @.currenttable int
declare @.tn sysname
set @.tn = (select table_name from Table_Space where table_id = 1)
set @.sqltest = 'DBCC DBREINDEX(''' + @.tn + ''','''',75)'
print @.currenttable
print @.sqltest
EXEC(@.sqltest)
go
AMB
"nosurfdj" wrote:
> I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @.currenttable variable
> receives the value 1 but when @.currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong
?
> declare @.sqltest varchar(40), @.currenttable int
> set @.currenttable = (select table_id from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
> Below is the message I get:
> 1
> DBCC DBREINDEX('@.currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@.currenttable'. Check sysobjects.
>|||Quote problems around ''@.currenttable''.
Try:
'DBCC DBREINDEX(' + @.currenttable + ','''',75)'
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"nosurfdj" <nosurfdj@.discussions.microsoft.com> wrote in message news:2406FBD3-FD2A-4F69-8A
8E-F446DC1473BF@.microsoft.com...
>I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @.currenttable variable
> receives the value 1 but when @.currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong
?
>
> declare @.sqltest varchar(40), @.currenttable int
> set @.currenttable = (select table_id from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
>
> Below is the message I get:
> 1
> DBCC DBREINDEX('@.currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@.currenttable'. Check sysobjects.
>|||I knew it was going to be something simple.
Thanks for your help-that did it.
"Alejandro Mesa" wrote:
> nosurfdj,
> DBCC DBREINDEX expects a table name and there is not table named
> '@.currenttable'.
> declare @.sqltest varchar(40), @.currenttable int
> declare @.tn sysname
> set @.tn = (select table_name from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''' + @.tn + ''','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
> go
>
> AMB
> "nosurfdj" wrote:
>
Monday, February 20, 2012
problem updating view with instead of trigger
Hi all,
I have created wv_details view that has an instead of update trigger. My
question is how can I update this view?
When I run the following query I get an error - "View 'wv_details' has
an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
statement."
UPDATE wv_details
SET REFERENCE=latest.REFERENCE,
[NOTES]=latest.NOTES,
[TITLE]=latest.TITLE,
[LINKACCT]=latest.LINKACCT,
[COUNTRY]=latest.COUNTRY,
[ZIP]=latest.ZIP,
[EXT]=latest.EXT,
[STATE]=latest.STATE,
[ADDRESS1]=latest.ADDRESS1,
[ADDRESS2]=latest.ADDRESS2,
[MERGECODES]=latest.MERGECODES,
[STATUS]=latest.STATUS,
[LASTUSER]=latest.LASTUSER
FROM wv_details latest
JOIN wv_details
ON latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
So I changed this query as shown below. It does not use a FROM clause
anymore. But I get a different error with this query - "The text, ntext, and
image data types are invalid in this subquery or aggregate expression.". The
NOTES field is a text column.
UPDATE wv_details
SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[NOTES]=(SELECT TOP 1 latest.NOTES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[TITLE]=(SELECT TOP 1 latest.TITLE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LINKACCT]=(SELECT TOP 1 latest.LINKACCT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[COUNTRY]=(SELECT TOP 1 latest.COUNTRY
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ZIP]=(SELECT TOP 1 latest.ZIP
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[EXT]=(SELECT TOP 1 latest.EXT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATE]=(SELECT TOP 1 latest.STATE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[MERGECODES]=(SELECT TOP 1 latest.MERGECODES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATUS]=(SELECT TOP 1 latest.STATUS
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LASTUSER]=(SELECT TOP 1 latest.LASTUSER
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
)
So my question is how can I update this view?
Thanks...
-NikhilThere are quite a few issues:
1. You reference the view as if it's either an "inserted" or "deleted"
virtual table. It's not so.
2. You have an Instead Of trigger on the view, you should be updating the
base table within that trigger. You have full access the virtual tables
there.
3. You cannot do (update obj set col =(select top 1 lob_col from ...)).
You're are doing aggregation on the blob which is not allowed (by MS
design).
4. Even if (col=select top 1 lob) is allowed, this update is going to cost
you royally.
You have been posting for a while here. You know it would be easier if you
post ddl+sample data/code+expected output, it would be easier to help you.
http://groups.google.co.uk/groups?h...er+Nikhil+Patel
http://www.aspfaq.com/etiquette.asp?id=5006
-oj
"Nikhil Patel" <donotspam@.nospaml.com> wrote in message
news:eI6SpeYBFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created wv_details view that has an instead of update trigger.
> My question is how can I update this view?
> When I run the following query I get an error - "View 'wv_details' has
> an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
> statement."
> UPDATE wv_details
> SET REFERENCE=latest.REFERENCE,
> [NOTES]=latest.NOTES,
> [TITLE]=latest.TITLE,
> [LINKACCT]=latest.LINKACCT,
> [COUNTRY]=latest.COUNTRY,
> [ZIP]=latest.ZIP,
> [EXT]=latest.EXT,
> [STATE]=latest.STATE,
> [ADDRESS1]=latest.ADDRESS1,
> [ADDRESS2]=latest.ADDRESS2,
> [MERGECODES]=latest.MERGECODES,
> [STATUS]=latest.STATUS,
> [LASTUSER]=latest.LASTUSER
> FROM wv_details latest
> JOIN wv_details
> ON latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> So I changed this query as shown below. It does not use a FROM clause
> anymore. But I get a different error with this query - "The text, ntext,
> and image data types are invalid in this subquery or aggregate
> expression.". The NOTES field is a text column.
> UPDATE wv_details
> SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [NOTES]=(SELECT TOP 1 latest.NOTES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [TITLE]=(SELECT TOP 1 latest.TITLE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LINKACCT]=(SELECT TOP 1 latest.LINKACCT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [COUNTRY]=(SELECT TOP 1 latest.COUNTRY
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ZIP]=(SELECT TOP 1 latest.ZIP
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [EXT]=(SELECT TOP 1 latest.EXT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATE]=(SELECT TOP 1 latest.STATE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [MERGECODES]=(SELECT TOP 1 latest.MERGECODES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATUS]=(SELECT TOP 1 latest.STATUS
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LASTUSER]=(SELECT TOP 1 latest.LASTUSER
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> )
>
> So my question is how can I update this view?
> Thanks...
> -Nikhil
>
I have created wv_details view that has an instead of update trigger. My
question is how can I update this view?
When I run the following query I get an error - "View 'wv_details' has
an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
statement."
UPDATE wv_details
SET REFERENCE=latest.REFERENCE,
[NOTES]=latest.NOTES,
[TITLE]=latest.TITLE,
[LINKACCT]=latest.LINKACCT,
[COUNTRY]=latest.COUNTRY,
[ZIP]=latest.ZIP,
[EXT]=latest.EXT,
[STATE]=latest.STATE,
[ADDRESS1]=latest.ADDRESS1,
[ADDRESS2]=latest.ADDRESS2,
[MERGECODES]=latest.MERGECODES,
[STATUS]=latest.STATUS,
[LASTUSER]=latest.LASTUSER
FROM wv_details latest
JOIN wv_details
ON latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
So I changed this query as shown below. It does not use a FROM clause
anymore. But I get a different error with this query - "The text, ntext, and
image data types are invalid in this subquery or aggregate expression.". The
NOTES field is a text column.
UPDATE wv_details
SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[NOTES]=(SELECT TOP 1 latest.NOTES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[TITLE]=(SELECT TOP 1 latest.TITLE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LINKACCT]=(SELECT TOP 1 latest.LINKACCT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[COUNTRY]=(SELECT TOP 1 latest.COUNTRY
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ZIP]=(SELECT TOP 1 latest.ZIP
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[EXT]=(SELECT TOP 1 latest.EXT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATE]=(SELECT TOP 1 latest.STATE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[MERGECODES]=(SELECT TOP 1 latest.MERGECODES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATUS]=(SELECT TOP 1 latest.STATUS
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LASTUSER]=(SELECT TOP 1 latest.LASTUSER
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
)
So my question is how can I update this view?
Thanks...
-NikhilThere are quite a few issues:
1. You reference the view as if it's either an "inserted" or "deleted"
virtual table. It's not so.
2. You have an Instead Of trigger on the view, you should be updating the
base table within that trigger. You have full access the virtual tables
there.
3. You cannot do (update obj set col =(select top 1 lob_col from ...)).
You're are doing aggregation on the blob which is not allowed (by MS
design).
4. Even if (col=select top 1 lob) is allowed, this update is going to cost
you royally.
You have been posting for a while here. You know it would be easier if you
post ddl+sample data/code+expected output, it would be easier to help you.
http://groups.google.co.uk/groups?h...er+Nikhil+Patel
http://www.aspfaq.com/etiquette.asp?id=5006
-oj
"Nikhil Patel" <donotspam@.nospaml.com> wrote in message
news:eI6SpeYBFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created wv_details view that has an instead of update trigger.
> My question is how can I update this view?
> When I run the following query I get an error - "View 'wv_details' has
> an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
> statement."
> UPDATE wv_details
> SET REFERENCE=latest.REFERENCE,
> [NOTES]=latest.NOTES,
> [TITLE]=latest.TITLE,
> [LINKACCT]=latest.LINKACCT,
> [COUNTRY]=latest.COUNTRY,
> [ZIP]=latest.ZIP,
> [EXT]=latest.EXT,
> [STATE]=latest.STATE,
> [ADDRESS1]=latest.ADDRESS1,
> [ADDRESS2]=latest.ADDRESS2,
> [MERGECODES]=latest.MERGECODES,
> [STATUS]=latest.STATUS,
> [LASTUSER]=latest.LASTUSER
> FROM wv_details latest
> JOIN wv_details
> ON latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> So I changed this query as shown below. It does not use a FROM clause
> anymore. But I get a different error with this query - "The text, ntext,
> and image data types are invalid in this subquery or aggregate
> expression.". The NOTES field is a text column.
> UPDATE wv_details
> SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [NOTES]=(SELECT TOP 1 latest.NOTES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [TITLE]=(SELECT TOP 1 latest.TITLE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LINKACCT]=(SELECT TOP 1 latest.LINKACCT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [COUNTRY]=(SELECT TOP 1 latest.COUNTRY
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ZIP]=(SELECT TOP 1 latest.ZIP
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [EXT]=(SELECT TOP 1 latest.EXT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATE]=(SELECT TOP 1 latest.STATE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [MERGECODES]=(SELECT TOP 1 latest.MERGECODES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATUS]=(SELECT TOP 1 latest.STATUS
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LASTUSER]=(SELECT TOP 1 latest.LASTUSER
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> )
>
> So my question is how can I update this view?
> Thanks...
> -Nikhil
>
Subscribe to:
Posts (Atom)