In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Con
nection=False"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Thursday, March 29, 2012
Getting a permission denied - but not using that user?
In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Connection=Fa lse"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?
No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
sql
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Connection=Fa lse"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?
No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
sql
getting a list of user created tables ONLY
sql server 2k
I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.
I am currently using the following. Isn't there a more built in way to do
this?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'Here's one way...
--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
(Table_Qualfier varchar(100),
Table_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100),
Table_Count numeric(9))
--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables
--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||See view information_schema.tables and function objectproperty.
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go
AMB
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||Thanks to the two of you.
Alejandro, that was the ticket. Gracias!!
"Alejandro Mesa" wrote:
> See view information_schema.tables and function objectproperty.
> Example:
> use northwind
> go
> select
> *
> from
> information_schema.tables
> where
> table_type = 'base table'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
> AMB
> "kevin" wrote:
>
I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.
I am currently using the following. Isn't there a more built in way to do
this?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'Here's one way...
--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
(Table_Qualfier varchar(100),
Table_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100),
Table_Count numeric(9))
--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables
--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||See view information_schema.tables and function objectproperty.
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go
AMB
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||Thanks to the two of you.
Alejandro, that was the ticket. Gracias!!
"Alejandro Mesa" wrote:
> See view information_schema.tables and function objectproperty.
> Example:
> use northwind
> go
> select
> *
> from
> information_schema.tables
> where
> table_type = 'base table'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
> AMB
> "kevin" wrote:
>
Tuesday, March 27, 2012
Getting a .rdl from a ReportBuilder save
I have created and deployed a report model.
I can go to http://localhost/reports not problem
I can create reports no problem using the Report Builder.
My question is: Is there a way of grabbing some sort of .rdl file or
something of that report so I can publish it to another server? After
I save the report I cannot find it anywhere.
Using the Report builder is way simpler than building reports in a VS
report project.
Is there something I am missing? Can you build .rdls in VS using the
same simple UI that is in Report Builder?
Thanks,
BillBill,
By default the report builder will save the RDLin Reporting Services in the
folder where your model is located. You can navigate to this folder via the
Report Manager Website (http://{server}/Reports), navigate to where your
model is located and your RDL should be there.
OR
You can save the RDL to your local hard drive through the Report Builder
application via the File \ Save To File and specify on your local hard drive
where u'd like to save the RDL.
Hope this helps.
-Rick
"bill" wrote:
> I have created and deployed a report model.
> I can go to http://localhost/reports not problem
> I can create reports no problem using the Report Builder.
> My question is: Is there a way of grabbing some sort of .rdl file or
> something of that report so I can publish it to another server? After
> I save the report I cannot find it anywhere.
> Using the Report builder is way simpler than building reports in a VS
> report project.
> Is there something I am missing? Can you build .rdls in VS using the
> same simple UI that is in Report Builder?
> Thanks,
> Bill
>
I can go to http://localhost/reports not problem
I can create reports no problem using the Report Builder.
My question is: Is there a way of grabbing some sort of .rdl file or
something of that report so I can publish it to another server? After
I save the report I cannot find it anywhere.
Using the Report builder is way simpler than building reports in a VS
report project.
Is there something I am missing? Can you build .rdls in VS using the
same simple UI that is in Report Builder?
Thanks,
BillBill,
By default the report builder will save the RDLin Reporting Services in the
folder where your model is located. You can navigate to this folder via the
Report Manager Website (http://{server}/Reports), navigate to where your
model is located and your RDL should be there.
OR
You can save the RDL to your local hard drive through the Report Builder
application via the File \ Save To File and specify on your local hard drive
where u'd like to save the RDL.
Hope this helps.
-Rick
"bill" wrote:
> I have created and deployed a report model.
> I can go to http://localhost/reports not problem
> I can create reports no problem using the Report Builder.
> My question is: Is there a way of grabbing some sort of .rdl file or
> something of that report so I can publish it to another server? After
> I save the report I cannot find it anywhere.
> Using the Report builder is way simpler than building reports in a VS
> report project.
> Is there something I am missing? Can you build .rdls in VS using the
> same simple UI that is in Report Builder?
> Thanks,
> Bill
>
Getting 5105 error when I try to attach database
Here's what I did. I created a new database on SQL Server 2000, on a remote
server, using Enterprise Manager. I then exported some data from another
database (same instance) into my new database. I then detached the database
I created, and copied the .MDF and .LDF files to my machine.
I've been trying to attach the .MDF and .LDF files, again using Enterprise
Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
Database... I give it the path to my .MDF and .LDF files, hit Verify, and
they verify (both the .MDF and .LDF file paths show up in the window,
checked). The problem comes when I hit OK. It gives me error 5105 (device
activation error), it gives the same path that I gave it, and says that the
filename may be wrong.
I don't think this is a permissions problem, and I've checked whether any
other process has grabbed ahold of it, and there isn't any. So I'm not sure
what's wrong. Please help.
Thanks.
Miller -
Please go through the following link :-
http://support.microsoft.com/default...b;en-us;304261
Let me know if you where able to get the help for your problem.
Regards
"Mark Miller" wrote:
> Here's what I did. I created a new database on SQL Server 2000, on a remote
> server, using Enterprise Manager. I then exported some data from another
> database (same instance) into my new database. I then detached the database
> I created, and copied the .MDF and .LDF files to my machine.
> I've been trying to attach the .MDF and .LDF files, again using Enterprise
> Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> they verify (both the .MDF and .LDF file paths show up in the window,
> checked). The problem comes when I hit OK. It gives me error 5105 (device
> activation error), it gives the same path that I gave it, and says that the
> filename may be wrong.
> I don't think this is a permissions problem, and I've checked whether any
> other process has grabbed ahold of it, and there isn't any. So I'm not sure
> what's wrong. Please help.
> Thanks.
>
|||I tried turning on trace flag 1807, like the article says, to turn off the
check SQL Server does to detect if I'm accessing a database file through a
network share, and I get the same error. I gave this a try because it seemed
to be in the ballpark of my problem, but I'm not trying to access the
database file over a network share anyway. It's on my computer's hard drive.
Other than this bit of information in the article, nothing else in it seemed
relevant.
"surajits" wrote:
[vbcol=seagreen]
> Miller -
> Please go through the following link :-
> http://support.microsoft.com/default...b;en-us;304261
> Let me know if you where able to get the help for your problem.
> Regards
>
> "Mark Miller" wrote:
|||When you did the backup, did you specify a device or a file? If a device and
you are restoring from the file, you may be seeing a header conflict.
"Mark Miller" wrote:
[vbcol=seagreen]
> I tried turning on trace flag 1807, like the article says, to turn off the
> check SQL Server does to detect if I'm accessing a database file through a
> network share, and I get the same error. I gave this a try because it seemed
> to be in the ballpark of my problem, but I'm not trying to access the
> database file over a network share anyway. It's on my computer's hard drive.
> Other than this bit of information in the article, nothing else in it seemed
> relevant.
> "surajits" wrote:
|||I wasn't trying to back up data. I wanted to transfer data from one database
to another, because I'm working on a software development project. The
source database is behind a firewall, and I can't access it from my
application on my development system. All I need is a subset of the data on
the source database. So I decided to create a new database on the source
instance, export the data I needed into it, detach it, and then copy it to my
machine, where I have my own SQL Server 2000 instance, and where I was
planning on accessing it from my application. I transferred it using a
straight network file copy process (ie. copy from source file system and
paste to destination file system). No tape backup was involved. Is the
picture getting clearer now?
Forgive me for not making this clearer. I
sometimes forget I'm not always talking to other developers.
"jrpm" wrote:
[vbcol=seagreen]
> When you did the backup, did you specify a device or a file? If a device and
> you are restoring from the file, you may be seeing a header conflict.
> "Mark Miller" wrote:
|||I suggest to first try doing this from Query Analyzer using sp_attach_db and make sure you get the
parameters correct. Possibly, you need to specify correct path for the ldf file if not in same path
as on original SQL Server. If that doesn't work (and you feel that you got the parameters correct),
I'd try to attach the original files (before the file copy) on the originating server. If that work,
I'd copy the files over the network back onto the originating server and try to attach those
files...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Miller" <MarkMiller@.discussions.microsoft.com> wrote in message
news:6637F49E-D3B5-4C20-800B-45CB72EE5F78@.microsoft.com...[vbcol=seagreen]
>I wasn't trying to back up data. I wanted to transfer data from one database
> to another, because I'm working on a software development project. The
> source database is behind a firewall, and I can't access it from my
> application on my development system. All I need is a subset of the data on
> the source database. So I decided to create a new database on the source
> instance, export the data I needed into it, detach it, and then copy it to my
> machine, where I have my own SQL Server 2000 instance, and where I was
> planning on accessing it from my application. I transferred it using a
> straight network file copy process (ie. copy from source file system and
> paste to destination file system). No tape backup was involved. Is the
> picture getting clearer now? Forgive me for not making this clearer. I
> sometimes forget I'm not always talking to other developers.
> "jrpm" wrote:
server, using Enterprise Manager. I then exported some data from another
database (same instance) into my new database. I then detached the database
I created, and copied the .MDF and .LDF files to my machine.
I've been trying to attach the .MDF and .LDF files, again using Enterprise
Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
Database... I give it the path to my .MDF and .LDF files, hit Verify, and
they verify (both the .MDF and .LDF file paths show up in the window,
checked). The problem comes when I hit OK. It gives me error 5105 (device
activation error), it gives the same path that I gave it, and says that the
filename may be wrong.
I don't think this is a permissions problem, and I've checked whether any
other process has grabbed ahold of it, and there isn't any. So I'm not sure
what's wrong. Please help.
Thanks.
Miller -
Please go through the following link :-
http://support.microsoft.com/default...b;en-us;304261
Let me know if you where able to get the help for your problem.
Regards
"Mark Miller" wrote:
> Here's what I did. I created a new database on SQL Server 2000, on a remote
> server, using Enterprise Manager. I then exported some data from another
> database (same instance) into my new database. I then detached the database
> I created, and copied the .MDF and .LDF files to my machine.
> I've been trying to attach the .MDF and .LDF files, again using Enterprise
> Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> they verify (both the .MDF and .LDF file paths show up in the window,
> checked). The problem comes when I hit OK. It gives me error 5105 (device
> activation error), it gives the same path that I gave it, and says that the
> filename may be wrong.
> I don't think this is a permissions problem, and I've checked whether any
> other process has grabbed ahold of it, and there isn't any. So I'm not sure
> what's wrong. Please help.
> Thanks.
>
|||I tried turning on trace flag 1807, like the article says, to turn off the
check SQL Server does to detect if I'm accessing a database file through a
network share, and I get the same error. I gave this a try because it seemed
to be in the ballpark of my problem, but I'm not trying to access the
database file over a network share anyway. It's on my computer's hard drive.
Other than this bit of information in the article, nothing else in it seemed
relevant.
"surajits" wrote:
[vbcol=seagreen]
> Miller -
> Please go through the following link :-
> http://support.microsoft.com/default...b;en-us;304261
> Let me know if you where able to get the help for your problem.
> Regards
>
> "Mark Miller" wrote:
|||When you did the backup, did you specify a device or a file? If a device and
you are restoring from the file, you may be seeing a header conflict.
"Mark Miller" wrote:
[vbcol=seagreen]
> I tried turning on trace flag 1807, like the article says, to turn off the
> check SQL Server does to detect if I'm accessing a database file through a
> network share, and I get the same error. I gave this a try because it seemed
> to be in the ballpark of my problem, but I'm not trying to access the
> database file over a network share anyway. It's on my computer's hard drive.
> Other than this bit of information in the article, nothing else in it seemed
> relevant.
> "surajits" wrote:
|||I wasn't trying to back up data. I wanted to transfer data from one database
to another, because I'm working on a software development project. The
source database is behind a firewall, and I can't access it from my
application on my development system. All I need is a subset of the data on
the source database. So I decided to create a new database on the source
instance, export the data I needed into it, detach it, and then copy it to my
machine, where I have my own SQL Server 2000 instance, and where I was
planning on accessing it from my application. I transferred it using a
straight network file copy process (ie. copy from source file system and
paste to destination file system). No tape backup was involved. Is the
picture getting clearer now?
Forgive me for not making this clearer. Isometimes forget I'm not always talking to other developers.
"jrpm" wrote:
[vbcol=seagreen]
> When you did the backup, did you specify a device or a file? If a device and
> you are restoring from the file, you may be seeing a header conflict.
> "Mark Miller" wrote:
|||I suggest to first try doing this from Query Analyzer using sp_attach_db and make sure you get the
parameters correct. Possibly, you need to specify correct path for the ldf file if not in same path
as on original SQL Server. If that doesn't work (and you feel that you got the parameters correct),
I'd try to attach the original files (before the file copy) on the originating server. If that work,
I'd copy the files over the network back onto the originating server and try to attach those
files...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Miller" <MarkMiller@.discussions.microsoft.com> wrote in message
news:6637F49E-D3B5-4C20-800B-45CB72EE5F78@.microsoft.com...[vbcol=seagreen]
>I wasn't trying to back up data. I wanted to transfer data from one database
> to another, because I'm working on a software development project. The
> source database is behind a firewall, and I can't access it from my
> application on my development system. All I need is a subset of the data on
> the source database. So I decided to create a new database on the source
> instance, export the data I needed into it, detach it, and then copy it to my
> machine, where I have my own SQL Server 2000 instance, and where I was
> planning on accessing it from my application. I transferred it using a
> straight network file copy process (ie. copy from source file system and
> paste to destination file system). No tape backup was involved. Is the
> picture getting clearer now?
Forgive me for not making this clearer. I> sometimes forget I'm not always talking to other developers.
> "jrpm" wrote:
Getting 5105 error when I try to attach database
Here's what I did. I created a new database on SQL Server 2000, on a remote
server, using Enterprise Manager. I then exported some data from another
database (same instance) into my new database. I then detached the database
I created, and copied the .MDF and .LDF files to my machine.
I've been trying to attach the .MDF and .LDF files, again using Enterprise
Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
Database... I give it the path to my .MDF and .LDF files, hit Verify, and
they verify (both the .MDF and .LDF file paths show up in the window,
checked). The problem comes when I hit OK. It gives me error 5105 (device
activation error), it gives the same path that I gave it, and says that the
filename may be wrong.
I don't think this is a permissions problem, and I've checked whether any
other process has grabbed ahold of it, and there isn't any. So I'm not sure
what's wrong. Please help.
Thanks.Miller -
Please go through the following link :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
Let me know if you where able to get the help for your problem.
Regards
"Mark Miller" wrote:
> Here's what I did. I created a new database on SQL Server 2000, on a remote
> server, using Enterprise Manager. I then exported some data from another
> database (same instance) into my new database. I then detached the database
> I created, and copied the .MDF and .LDF files to my machine.
> I've been trying to attach the .MDF and .LDF files, again using Enterprise
> Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> they verify (both the .MDF and .LDF file paths show up in the window,
> checked). The problem comes when I hit OK. It gives me error 5105 (device
> activation error), it gives the same path that I gave it, and says that the
> filename may be wrong.
> I don't think this is a permissions problem, and I've checked whether any
> other process has grabbed ahold of it, and there isn't any. So I'm not sure
> what's wrong. Please help.
> Thanks.
>|||I tried turning on trace flag 1807, like the article says, to turn off the
check SQL Server does to detect if I'm accessing a database file through a
network share, and I get the same error. I gave this a try because it seemed
to be in the ballpark of my problem, but I'm not trying to access the
database file over a network share anyway. It's on my computer's hard drive.
Other than this bit of information in the article, nothing else in it seemed
relevant.
"surajits" wrote:
> Miller -
> Please go through the following link :-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
> Let me know if you where able to get the help for your problem.
> Regards
>
> "Mark Miller" wrote:
> > Here's what I did. I created a new database on SQL Server 2000, on a remote
> > server, using Enterprise Manager. I then exported some data from another
> > database (same instance) into my new database. I then detached the database
> > I created, and copied the .MDF and .LDF files to my machine.
> >
> > I've been trying to attach the .MDF and .LDF files, again using Enterprise
> > Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> > Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> > they verify (both the .MDF and .LDF file paths show up in the window,
> > checked). The problem comes when I hit OK. It gives me error 5105 (device
> > activation error), it gives the same path that I gave it, and says that the
> > filename may be wrong.
> >
> > I don't think this is a permissions problem, and I've checked whether any
> > other process has grabbed ahold of it, and there isn't any. So I'm not sure
> > what's wrong. Please help.
> >
> > Thanks.
> >|||When you did the backup, did you specify a device or a file? If a device and
you are restoring from the file, you may be seeing a header conflict.
"Mark Miller" wrote:
> I tried turning on trace flag 1807, like the article says, to turn off the
> check SQL Server does to detect if I'm accessing a database file through a
> network share, and I get the same error. I gave this a try because it seemed
> to be in the ballpark of my problem, but I'm not trying to access the
> database file over a network share anyway. It's on my computer's hard drive.
> Other than this bit of information in the article, nothing else in it seemed
> relevant.
> "surajits" wrote:
> > Miller -
> > Please go through the following link :-
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
> >
> > Let me know if you where able to get the help for your problem.
> >
> > Regards
> >
> >
> > "Mark Miller" wrote:
> >
> > > Here's what I did. I created a new database on SQL Server 2000, on a remote
> > > server, using Enterprise Manager. I then exported some data from another
> > > database (same instance) into my new database. I then detached the database
> > > I created, and copied the .MDF and .LDF files to my machine.
> > >
> > > I've been trying to attach the .MDF and .LDF files, again using Enterprise
> > > Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> > > Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> > > they verify (both the .MDF and .LDF file paths show up in the window,
> > > checked). The problem comes when I hit OK. It gives me error 5105 (device
> > > activation error), it gives the same path that I gave it, and says that the
> > > filename may be wrong.
> > >
> > > I don't think this is a permissions problem, and I've checked whether any
> > > other process has grabbed ahold of it, and there isn't any. So I'm not sure
> > > what's wrong. Please help.
> > >
> > > Thanks.
> > >|||I wasn't trying to back up data. I wanted to transfer data from one database
to another, because I'm working on a software development project. The
source database is behind a firewall, and I can't access it from my
application on my development system. All I need is a subset of the data on
the source database. So I decided to create a new database on the source
instance, export the data I needed into it, detach it, and then copy it to my
machine, where I have my own SQL Server 2000 instance, and where I was
planning on accessing it from my application. I transferred it using a
straight network file copy process (ie. copy from source file system and
paste to destination file system). No tape backup was involved. Is the
picture getting clearer now? :) Forgive me for not making this clearer. I
sometimes forget I'm not always talking to other developers.
"jrpm" wrote:
> When you did the backup, did you specify a device or a file? If a device and
> you are restoring from the file, you may be seeing a header conflict.
> "Mark Miller" wrote:
> > I tried turning on trace flag 1807, like the article says, to turn off the
> > check SQL Server does to detect if I'm accessing a database file through a
> > network share, and I get the same error. I gave this a try because it seemed
> > to be in the ballpark of my problem, but I'm not trying to access the
> > database file over a network share anyway. It's on my computer's hard drive.
> > Other than this bit of information in the article, nothing else in it seemed
> > relevant.
> >
> > "surajits" wrote:
> >
> > > Miller -
> > > Please go through the following link :-
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
> > >
> > > Let me know if you where able to get the help for your problem.
> > >
> > > Regards
> > >
> > >
> > > "Mark Miller" wrote:
> > >
> > > > Here's what I did. I created a new database on SQL Server 2000, on a remote
> > > > server, using Enterprise Manager. I then exported some data from another
> > > > database (same instance) into my new database. I then detached the database
> > > > I created, and copied the .MDF and .LDF files to my machine.
> > > >
> > > > I've been trying to attach the .MDF and .LDF files, again using Enterprise
> > > > Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> > > > Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> > > > they verify (both the .MDF and .LDF file paths show up in the window,
> > > > checked). The problem comes when I hit OK. It gives me error 5105 (device
> > > > activation error), it gives the same path that I gave it, and says that the
> > > > filename may be wrong.
> > > >
> > > > I don't think this is a permissions problem, and I've checked whether any
> > > > other process has grabbed ahold of it, and there isn't any. So I'm not sure
> > > > what's wrong. Please help.
> > > >
> > > > Thanks.
> > > >|||I suggest to first try doing this from Query Analyzer using sp_attach_db and make sure you get the
parameters correct. Possibly, you need to specify correct path for the ldf file if not in same path
as on original SQL Server. If that doesn't work (and you feel that you got the parameters correct),
I'd try to attach the original files (before the file copy) on the originating server. If that work,
I'd copy the files over the network back onto the originating server and try to attach those
files...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Miller" <MarkMiller@.discussions.microsoft.com> wrote in message
news:6637F49E-D3B5-4C20-800B-45CB72EE5F78@.microsoft.com...
>I wasn't trying to back up data. I wanted to transfer data from one database
> to another, because I'm working on a software development project. The
> source database is behind a firewall, and I can't access it from my
> application on my development system. All I need is a subset of the data on
> the source database. So I decided to create a new database on the source
> instance, export the data I needed into it, detach it, and then copy it to my
> machine, where I have my own SQL Server 2000 instance, and where I was
> planning on accessing it from my application. I transferred it using a
> straight network file copy process (ie. copy from source file system and
> paste to destination file system). No tape backup was involved. Is the
> picture getting clearer now? :) Forgive me for not making this clearer. I
> sometimes forget I'm not always talking to other developers.
> "jrpm" wrote:
>> When you did the backup, did you specify a device or a file? If a device and
>> you are restoring from the file, you may be seeing a header conflict.
>> "Mark Miller" wrote:
>> > I tried turning on trace flag 1807, like the article says, to turn off the
>> > check SQL Server does to detect if I'm accessing a database file through a
>> > network share, and I get the same error. I gave this a try because it seemed
>> > to be in the ballpark of my problem, but I'm not trying to access the
>> > database file over a network share anyway. It's on my computer's hard drive.
>> > Other than this bit of information in the article, nothing else in it seemed
>> > relevant.
>> >
>> > "surajits" wrote:
>> >
>> > > Miller -
>> > > Please go through the following link :-
>> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
>> > >
>> > > Let me know if you where able to get the help for your problem.
>> > >
>> > > Regards
>> > >
>> > >
>> > > "Mark Miller" wrote:
>> > >
>> > > > Here's what I did. I created a new database on SQL Server 2000, on a remote
>> > > > server, using Enterprise Manager. I then exported some data from another
>> > > > database (same instance) into my new database. I then detached the database
>> > > > I created, and copied the .MDF and .LDF files to my machine.
>> > > >
>> > > > I've been trying to attach the .MDF and .LDF files, again using Enterprise
>> > > > Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
>> > > > Database... I give it the path to my .MDF and .LDF files, hit Verify, and
>> > > > they verify (both the .MDF and .LDF file paths show up in the window,
>> > > > checked). The problem comes when I hit OK. It gives me error 5105 (device
>> > > > activation error), it gives the same path that I gave it, and says that the
>> > > > filename may be wrong.
>> > > >
>> > > > I don't think this is a permissions problem, and I've checked whether any
>> > > > other process has grabbed ahold of it, and there isn't any. So I'm not sure
>> > > > what's wrong. Please help.
>> > > >
>> > > > Thanks.
>> > > >sql
server, using Enterprise Manager. I then exported some data from another
database (same instance) into my new database. I then detached the database
I created, and copied the .MDF and .LDF files to my machine.
I've been trying to attach the .MDF and .LDF files, again using Enterprise
Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
Database... I give it the path to my .MDF and .LDF files, hit Verify, and
they verify (both the .MDF and .LDF file paths show up in the window,
checked). The problem comes when I hit OK. It gives me error 5105 (device
activation error), it gives the same path that I gave it, and says that the
filename may be wrong.
I don't think this is a permissions problem, and I've checked whether any
other process has grabbed ahold of it, and there isn't any. So I'm not sure
what's wrong. Please help.
Thanks.Miller -
Please go through the following link :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
Let me know if you where able to get the help for your problem.
Regards
"Mark Miller" wrote:
> Here's what I did. I created a new database on SQL Server 2000, on a remote
> server, using Enterprise Manager. I then exported some data from another
> database (same instance) into my new database. I then detached the database
> I created, and copied the .MDF and .LDF files to my machine.
> I've been trying to attach the .MDF and .LDF files, again using Enterprise
> Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> they verify (both the .MDF and .LDF file paths show up in the window,
> checked). The problem comes when I hit OK. It gives me error 5105 (device
> activation error), it gives the same path that I gave it, and says that the
> filename may be wrong.
> I don't think this is a permissions problem, and I've checked whether any
> other process has grabbed ahold of it, and there isn't any. So I'm not sure
> what's wrong. Please help.
> Thanks.
>|||I tried turning on trace flag 1807, like the article says, to turn off the
check SQL Server does to detect if I'm accessing a database file through a
network share, and I get the same error. I gave this a try because it seemed
to be in the ballpark of my problem, but I'm not trying to access the
database file over a network share anyway. It's on my computer's hard drive.
Other than this bit of information in the article, nothing else in it seemed
relevant.
"surajits" wrote:
> Miller -
> Please go through the following link :-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
> Let me know if you where able to get the help for your problem.
> Regards
>
> "Mark Miller" wrote:
> > Here's what I did. I created a new database on SQL Server 2000, on a remote
> > server, using Enterprise Manager. I then exported some data from another
> > database (same instance) into my new database. I then detached the database
> > I created, and copied the .MDF and .LDF files to my machine.
> >
> > I've been trying to attach the .MDF and .LDF files, again using Enterprise
> > Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> > Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> > they verify (both the .MDF and .LDF file paths show up in the window,
> > checked). The problem comes when I hit OK. It gives me error 5105 (device
> > activation error), it gives the same path that I gave it, and says that the
> > filename may be wrong.
> >
> > I don't think this is a permissions problem, and I've checked whether any
> > other process has grabbed ahold of it, and there isn't any. So I'm not sure
> > what's wrong. Please help.
> >
> > Thanks.
> >|||When you did the backup, did you specify a device or a file? If a device and
you are restoring from the file, you may be seeing a header conflict.
"Mark Miller" wrote:
> I tried turning on trace flag 1807, like the article says, to turn off the
> check SQL Server does to detect if I'm accessing a database file through a
> network share, and I get the same error. I gave this a try because it seemed
> to be in the ballpark of my problem, but I'm not trying to access the
> database file over a network share anyway. It's on my computer's hard drive.
> Other than this bit of information in the article, nothing else in it seemed
> relevant.
> "surajits" wrote:
> > Miller -
> > Please go through the following link :-
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
> >
> > Let me know if you where able to get the help for your problem.
> >
> > Regards
> >
> >
> > "Mark Miller" wrote:
> >
> > > Here's what I did. I created a new database on SQL Server 2000, on a remote
> > > server, using Enterprise Manager. I then exported some data from another
> > > database (same instance) into my new database. I then detached the database
> > > I created, and copied the .MDF and .LDF files to my machine.
> > >
> > > I've been trying to attach the .MDF and .LDF files, again using Enterprise
> > > Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> > > Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> > > they verify (both the .MDF and .LDF file paths show up in the window,
> > > checked). The problem comes when I hit OK. It gives me error 5105 (device
> > > activation error), it gives the same path that I gave it, and says that the
> > > filename may be wrong.
> > >
> > > I don't think this is a permissions problem, and I've checked whether any
> > > other process has grabbed ahold of it, and there isn't any. So I'm not sure
> > > what's wrong. Please help.
> > >
> > > Thanks.
> > >|||I wasn't trying to back up data. I wanted to transfer data from one database
to another, because I'm working on a software development project. The
source database is behind a firewall, and I can't access it from my
application on my development system. All I need is a subset of the data on
the source database. So I decided to create a new database on the source
instance, export the data I needed into it, detach it, and then copy it to my
machine, where I have my own SQL Server 2000 instance, and where I was
planning on accessing it from my application. I transferred it using a
straight network file copy process (ie. copy from source file system and
paste to destination file system). No tape backup was involved. Is the
picture getting clearer now? :) Forgive me for not making this clearer. I
sometimes forget I'm not always talking to other developers.
"jrpm" wrote:
> When you did the backup, did you specify a device or a file? If a device and
> you are restoring from the file, you may be seeing a header conflict.
> "Mark Miller" wrote:
> > I tried turning on trace flag 1807, like the article says, to turn off the
> > check SQL Server does to detect if I'm accessing a database file through a
> > network share, and I get the same error. I gave this a try because it seemed
> > to be in the ballpark of my problem, but I'm not trying to access the
> > database file over a network share anyway. It's on my computer's hard drive.
> > Other than this bit of information in the article, nothing else in it seemed
> > relevant.
> >
> > "surajits" wrote:
> >
> > > Miller -
> > > Please go through the following link :-
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
> > >
> > > Let me know if you where able to get the help for your problem.
> > >
> > > Regards
> > >
> > >
> > > "Mark Miller" wrote:
> > >
> > > > Here's what I did. I created a new database on SQL Server 2000, on a remote
> > > > server, using Enterprise Manager. I then exported some data from another
> > > > database (same instance) into my new database. I then detached the database
> > > > I created, and copied the .MDF and .LDF files to my machine.
> > > >
> > > > I've been trying to attach the .MDF and .LDF files, again using Enterprise
> > > > Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
> > > > Database... I give it the path to my .MDF and .LDF files, hit Verify, and
> > > > they verify (both the .MDF and .LDF file paths show up in the window,
> > > > checked). The problem comes when I hit OK. It gives me error 5105 (device
> > > > activation error), it gives the same path that I gave it, and says that the
> > > > filename may be wrong.
> > > >
> > > > I don't think this is a permissions problem, and I've checked whether any
> > > > other process has grabbed ahold of it, and there isn't any. So I'm not sure
> > > > what's wrong. Please help.
> > > >
> > > > Thanks.
> > > >|||I suggest to first try doing this from Query Analyzer using sp_attach_db and make sure you get the
parameters correct. Possibly, you need to specify correct path for the ldf file if not in same path
as on original SQL Server. If that doesn't work (and you feel that you got the parameters correct),
I'd try to attach the original files (before the file copy) on the originating server. If that work,
I'd copy the files over the network back onto the originating server and try to attach those
files...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Miller" <MarkMiller@.discussions.microsoft.com> wrote in message
news:6637F49E-D3B5-4C20-800B-45CB72EE5F78@.microsoft.com...
>I wasn't trying to back up data. I wanted to transfer data from one database
> to another, because I'm working on a software development project. The
> source database is behind a firewall, and I can't access it from my
> application on my development system. All I need is a subset of the data on
> the source database. So I decided to create a new database on the source
> instance, export the data I needed into it, detach it, and then copy it to my
> machine, where I have my own SQL Server 2000 instance, and where I was
> planning on accessing it from my application. I transferred it using a
> straight network file copy process (ie. copy from source file system and
> paste to destination file system). No tape backup was involved. Is the
> picture getting clearer now? :) Forgive me for not making this clearer. I
> sometimes forget I'm not always talking to other developers.
> "jrpm" wrote:
>> When you did the backup, did you specify a device or a file? If a device and
>> you are restoring from the file, you may be seeing a header conflict.
>> "Mark Miller" wrote:
>> > I tried turning on trace flag 1807, like the article says, to turn off the
>> > check SQL Server does to detect if I'm accessing a database file through a
>> > network share, and I get the same error. I gave this a try because it seemed
>> > to be in the ballpark of my problem, but I'm not trying to access the
>> > database file over a network share anyway. It's on my computer's hard drive.
>> > Other than this bit of information in the article, nothing else in it seemed
>> > relevant.
>> >
>> > "surajits" wrote:
>> >
>> > > Miller -
>> > > Please go through the following link :-
>> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
>> > >
>> > > Let me know if you where able to get the help for your problem.
>> > >
>> > > Regards
>> > >
>> > >
>> > > "Mark Miller" wrote:
>> > >
>> > > > Here's what I did. I created a new database on SQL Server 2000, on a remote
>> > > > server, using Enterprise Manager. I then exported some data from another
>> > > > database (same instance) into my new database. I then detached the database
>> > > > I created, and copied the .MDF and .LDF files to my machine.
>> > > >
>> > > > I've been trying to attach the .MDF and .LDF files, again using Enterprise
>> > > > Manager (I'm also running SQL Server 2000). In EM, I tell it to Attach
>> > > > Database... I give it the path to my .MDF and .LDF files, hit Verify, and
>> > > > they verify (both the .MDF and .LDF file paths show up in the window,
>> > > > checked). The problem comes when I hit OK. It gives me error 5105 (device
>> > > > activation error), it gives the same path that I gave it, and says that the
>> > > > filename may be wrong.
>> > > >
>> > > > I don't think this is a permissions problem, and I've checked whether any
>> > > > other process has grabbed ahold of it, and there isn't any. So I'm not sure
>> > > > what's wrong. Please help.
>> > > >
>> > > > Thanks.
>> > > >sql
Getting 20533 Error as a result of subreports.
I am using VB6 with crystal reports version 8.5 (the OCX) and Access (Jet) database.
I have created a report which works fine on my dev machine and am trying to deploy to another machine.
When the report is run on the other machine an error 20533 Unable to open database error is received.
The report has an embedded subreport, which if I remove the subreport, works fine.
I have validated the database for both the main report and the subreport.
Here is the code I am using to run the report.
With MDIMain.CrystalReport
.ReportFileName = App.Path & "\reports\Summary.rpt"
.RetrieveDataFiles
Do Until .DataFiles(xCntr) = ""
.DataFiles(xCntr) = gSystem.DB_Path
xCntr = xCntr + 1
Loop
.SelectionFormula = "{OrderHeader.OrderID} =" & rsOrder("OrderID") '{?OrderID}
.ReportTitle = gSystem.Company_Name
.Action = 1
End With
I'm at a loss... any help would be apprieciated. Thanks.Hi all... I figured out what the issue was. It was fixed with the following code that sets the database location for the subreport.
I have posted the code fix for anyone else that may run into this issue.
Thanks to all that took a look at this for me.
With MDIMain.CrystalReport
.ReportFileName = App.Path & "\reports\Summary.rpt"
.RetrieveDataFiles
Do Until .DataFiles(xCntr) = ""
.DataFiles(xCntr) = gSystem.DB_Path
xCntr = xCntr + 1
Loop
.SelectionFormula = "{OrderHeader.OrderID} =" & rsOrder("OrderID")
.ReportTitle = gSystem.Company_Name
.SubreportToChange = "Selected Options"
.DataFiles(0) = gSystem.DB_Path
.SubreportToChange = ""
.Action = 1
End With
I have created a report which works fine on my dev machine and am trying to deploy to another machine.
When the report is run on the other machine an error 20533 Unable to open database error is received.
The report has an embedded subreport, which if I remove the subreport, works fine.
I have validated the database for both the main report and the subreport.
Here is the code I am using to run the report.
With MDIMain.CrystalReport
.ReportFileName = App.Path & "\reports\Summary.rpt"
.RetrieveDataFiles
Do Until .DataFiles(xCntr) = ""
.DataFiles(xCntr) = gSystem.DB_Path
xCntr = xCntr + 1
Loop
.SelectionFormula = "{OrderHeader.OrderID} =" & rsOrder("OrderID") '{?OrderID}
.ReportTitle = gSystem.Company_Name
.Action = 1
End With
I'm at a loss... any help would be apprieciated. Thanks.Hi all... I figured out what the issue was. It was fixed with the following code that sets the database location for the subreport.
I have posted the code fix for anyone else that may run into this issue.
Thanks to all that took a look at this for me.
With MDIMain.CrystalReport
.ReportFileName = App.Path & "\reports\Summary.rpt"
.RetrieveDataFiles
Do Until .DataFiles(xCntr) = ""
.DataFiles(xCntr) = gSystem.DB_Path
xCntr = xCntr + 1
Loop
.SelectionFormula = "{OrderHeader.OrderID} =" & rsOrder("OrderID")
.ReportTitle = gSystem.Company_Name
.SubreportToChange = "Selected Options"
.DataFiles(0) = gSystem.DB_Path
.SubreportToChange = ""
.Action = 1
End With
Monday, March 26, 2012
GetReportParameters ignores language="en-gb"?
Hello
I've integrated my reports into an app using the ReportViewer control, and I've created custom parameter selection controls. I want the user to be able to enter dates in dd/mm/yy format into a textbox. I've set the language of my reports to en-gb.
The SetParametes method of the ReportViewer control copes with this fine, but when I pass a date string in dd/mm/yy format into GetReportParameters, I get a 'parameter value not valid for its type' error. But it works for date strings in mm/dd/yy format, so it's as if GetReportParameters is ignoring my language setting.
Has anyone got any idea as to where I'm going wrong?
Thanks in advance
Dominic
After a little more playing around with this I've found that, on my machine, the SetParameters method always wants dates in dd/mm/yy format, whilst the GetReportParameters web method of the Report Service always wants dates in mm/dd/yy format, regardless of what I set the language of the report to.
Does anyone know what's going on?
Wednesday, March 21, 2012
Getdate() default value does not get created
I have a table in which a field named DateCreated has the default value
(getdate()). THis has always worked before in other apps, it would naturally
write the computer's date time whenever a new record was created. I can't
figure out why all of a sudden the field in this table no longer get
populated with the default value.
Anyone have any ideas on where to start looking? I'm stumped.
BobAn INSERT that references the column will override the default, even
if it is assigned NULL. Any INSERT without the (column list) after
the table name will have this problem. The INSERT must use an
explicit column list, and the column must not appear in that list, for
the default to be applied.
Roy Harvey
Beacon Falls, CT
On Fri, 16 Jun 2006 15:57:44 -0400, "Bob" <bdufour@.sgiims.com> wrote:
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would naturall
y
>write the computer's date time whenever a new record was created. I can't
>figure out why all of a sudden the field in this table no longer get
>populated with the default value.
>Anyone have any ideas on where to start looking? I'm stumped.
>Bob|||Can you show the table structure (CREATE TABLE, not a hand-written list of
columns) and the actual insert statement that fails to generate the correct
value?
"Bob" <bdufour@.sgiims.com> wrote in message
news:%239SVj8XkGHA.4528@.TK2MSFTNGP05.phx.gbl...
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would
>naturally write the computer's date time whenever a new record was created.
>I can't figure out why all of a sudden the field in this table no longer
>get populated with the default value.
> Anyone have any ideas on where to start looking? I'm stumped.
> Bob
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Can you show the table structure (CREATE TABLE, not a hand-written list of
> columns) and the actual insert statement that fails to generate the corre
ct
> value?
>
Also any triggers that are on the table...
(getdate()). THis has always worked before in other apps, it would naturally
write the computer's date time whenever a new record was created. I can't
figure out why all of a sudden the field in this table no longer get
populated with the default value.
Anyone have any ideas on where to start looking? I'm stumped.
BobAn INSERT that references the column will override the default, even
if it is assigned NULL. Any INSERT without the (column list) after
the table name will have this problem. The INSERT must use an
explicit column list, and the column must not appear in that list, for
the default to be applied.
Roy Harvey
Beacon Falls, CT
On Fri, 16 Jun 2006 15:57:44 -0400, "Bob" <bdufour@.sgiims.com> wrote:
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would naturall
y
>write the computer's date time whenever a new record was created. I can't
>figure out why all of a sudden the field in this table no longer get
>populated with the default value.
>Anyone have any ideas on where to start looking? I'm stumped.
>Bob|||Can you show the table structure (CREATE TABLE, not a hand-written list of
columns) and the actual insert statement that fails to generate the correct
value?
"Bob" <bdufour@.sgiims.com> wrote in message
news:%239SVj8XkGHA.4528@.TK2MSFTNGP05.phx.gbl...
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would
>naturally write the computer's date time whenever a new record was created.
>I can't figure out why all of a sudden the field in this table no longer
>get populated with the default value.
> Anyone have any ideas on where to start looking? I'm stumped.
> Bob
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Can you show the table structure (CREATE TABLE, not a hand-written list of
> columns) and the actual insert statement that fails to generate the corre
ct
> value?
>
Also any triggers that are on the table...
Monday, March 19, 2012
get trigger schema within CLR code
Hi.
I am trying to get the schema in which the trigger is created within the CLR code.
1)create a new schema MySchema.
2) created a table MySchema.MyTable
3) created the assembly and trigger ( create trigger MySchema.MyTrigger on MySchema.MyTable..... ) Trigger writes to another table MySchema.MyLog.
Code works fine if I hardcode Myschema.MyLog in the CLR but fails when I say just MyLog.
So how do dynamically get the trigger's schema name ?
Thanks for your help.
Nach
There is no way to get the schema of the currently executing trigger from clr. With T-SQL, you code would have to create dynamic sql based on the current trigger, or rely on the implict schema name binding that occurs.
There are alternatives
1. Use execute as user and have the specified user have a default schema of MySchema
2. Create a T-SQL trigger that calls your clr trigger converted to a proc, passing it the @.@.ProcId of the T-SQL trigger as a parameter, and you can then get object_name & schema id from the clr procedure
Monday, March 12, 2012
Get the Row Count
I have created a package which is transforming the data from Source OLEDB Sql Server to Destination OLEDB Sql Server programatically in VB.NET
During execution phase, I want to generate an event after every 1000 rows are transformed. As per I think, OnProgress event of IDTSEvent doesn't support this kind of a thing.
And after my Transformation is completed, I want to know how many rows have being transformed.
So how can the above two task be performed?
Add a Row Count component to your data flow to capture the number of rows.
You should be able to fire events using the FireProgress or FireInformation methods of the ComponentMetaData class. See this topic in Books Online: Raising Events in the Script Component (http://msdn2.microsoft.com/en-us/library/aa337081.aspx)
|||I am creating the package programmatically and not using designer for it.
I have already created the package which has OLEDB and Source and Destination and added to TaskHost. For Row Count Component I think I have to create another Task Host and then add it to main pipe. But it has to be added as For Each Loop. But if I use For Each Loop then one Task Host will process at a time.
So what is the solution for this?
|||I have added Row Count Component as Transformation (DTSTransform.RowCount) to the ComponentMetaData.
Now I get the number of Row Transformed after post execute event whereas I need to raise an event after every 1000 rows are transformed. According to me after PostExecute event of the DataFlow, the number of Rows Transformed as set into the variable by Row Count Component. So how can I raise event after every 1000 rows?
|||One way to do this would be to add a script transform to monitor the number of rows and fire the event after 1000 rows pass through.|||I have used Script Component to transform. As I am creating this programmatically, I have copied the code generated in xml format of dtsx file to my application as a string array exactly as in the file to SourceCode property.
I have set PreCompile Property to false. So I don't need to set the BinaryCode property.
I have used code as below:
Dim DFTransform As IDTSComponentMetaData90
DFTransform = DTP.ComponentMetaDataCollection.New()
DFTransform.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
DFTransform.Name = "ScriptTransform"
DFTransform.UsesDispositions = False
Dim TransInst As CManagedComponentWrapper = DFTransform.Instantiate()
TransInst.ProvideComponentProperties()
DTP.PathCollection.New().AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFTransform.InputCollection(0))
TransInst.SetComponentProperty("VsaProjectName", "ScriptComponent_3a1cf20682b14906bbdc971f7768e55c")
TransInst.SetComponentProperty("SourceCode", AddSourceCode(DFTransform.ComponentClassID))
TransInst.SetComponentProperty("BinaryCode", AddBinaryCode)
TransInst.SetComponentProperty("PreCompile", False)
TransInst.SetComponentProperty("UserComponentTypeName", "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
TransInst.AcquireConnections(Nothing)
TransInst.ReinitializeMetaData()
Dim output As IDTSOutput90 = DFTransform.OutputCollection(0)
Dim outputColumn As IDTSOutputColumn90 = output.OutputColumnCollection.New()
outputColumn.Name = "myCount"
outputColumn.SetDataTypeProperties(Wrapper.DataType.DT_I4, 0, 0, 0, 0)
DFTransform.OutputCollection(0).ExternalMetadataColumnCollection.IsUsed = False
TransInst.ReleaseConnections()
But when I compile my application I get following errors:
Error Code :-1073450910
Sub Component :- Script Component [43]
Description :- System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()
Error Code :-1073450901
Sub Component :-DTS.Pipeline
Description :- "component "Script Component" (43)" failed validation and returned validation status "VS_ISBROKEN".
Is there anything which I need to do differently
Sunday, February 26, 2012
Get results from VB SQLCRL Stored Procedure
Hi,
I created a VB SQL CRL Stored procedure for calculating a value. Value
is returned as below
Using sConn4 As New SqlConnection("context connection=true")
sConn4.Open()
scmd = New SqlCommand("SELECT " & var_max, sConn4)
sdrd = scmd.ExecuteReader()
SqlContext.Pipe.Send(sdrd)
End Using
When calling this stored procedure from a TSQL stored procedure for
using the value for further processing the value returned to my
variable is 0. The correct value should be 56. In results tab I get the
correct result, but how can I assign it to my variable @.max ?
DECLARE @.max1 int
DECLARE @.max int
EXEC @.max1 = [dbo].[VBSTP_calculate_MAX_no]
@.vsp_table_name = N'[dbo].[Message]',
@.vsp_table_key = N'message_no',
@.vsp_WHERE = N''
print @.max1 -- value here is 0
SET @.max = (SELECT @.max1)
Thanks a lot.Chris (CLarkou@.gmail.com) writes:
> When calling this stored procedure from a TSQL stored procedure for
> using the value for further processing the value returned to my
> variable is 0. The correct value should be 56. In results tab I get the
> correct result, but how can I assign it to my variable @.max ?
> DECLARE @.max1 int
> DECLARE @.max int
> EXEC @.max1 = [dbo].[VBSTP_calculate_MAX_no]
> @.vsp_table_name = N'[dbo].[Message]',
> @.vsp_table_key = N'message_no',
> @.vsp_WHERE = N''
> print @.max1 -- value here is 0
A stored procedure (no matter if it's written in T-SQL or VB .Net) can
return values in three different ways:
1) Return value.
2) Output parameters.
3) Result set.
Your procedure returns a result set, but above you are retrieving the
return value. In my opinion, return values should be used to indicate
success/failure (with 0 meaning success) and nothing else.
If the purpose of your VB procedure is to compute a single value, you
should not return a result set from it, but you should return an output
parameter. Or maybe even better - you should make it a function.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Friday, February 24, 2012
Get records count from SQL cursor
Hi experts,
I have created a SQL cursor for records processing in a stored procedure. I
probably can use the @.@.Cursor_Rows function in order to obtain total rows of
record contained inside the cursor. But once I declare the cursor as
FAST_FORWARD, it always return me -1. I need to declare the cursor as
FAST_FORWARD as it really helps in tuning the performance. Else my stored
procedure will take longer time to execute.
Any other way I can use to get the total records being returned? I've tried
to signal another SQL statement to perform the records count but this seems
to create redundant overhead. I believe if I can do anything to existing
cursor without having extra Select Count statement, it would help to reduce
unnecessary processing and shorten the overall time required.
Really appreciate for any advice or suggestion. Thanks a lot.Can you test perfoermance between your current cursor declaration and the on
e
I am posting?
declare my_cursor cursor
local
forward_only
static
read_only
for
...
AMB
"LBT" wrote:
> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've trie
d
> to signal another SQL statement to perform the records count but this seem
s
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to reduc
e
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||If performance is a concern for you then how about getting rid of the
cursor altogether? Cursors are rarely a good idea.
David Portas
SQL Server MVP
--|||Thanks for the suggestion. The time taken is still approximately equal to th
e
cursor which is declared without those keywords. And I can't declare the
cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I forgo
t
to write out this concern in my previous post).
"Alejandro Mesa" wrote:
> Can you test perfoermance between your current cursor declaration and the
one
> I am posting?
> declare my_cursor cursor
> local
> forward_only
> static
> read_only
> for
> ...
>
> AMB
>
> "LBT" wrote:
>|||You can use select count(*)... yada,,, the optimizer can use the index
entries instead of having to read the data ( if there is a supporting
index.)
You can also use select @.@.rowcount AFTER the query...
You have to open the cursor before the rowcount info is available for
cursors...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
> I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
> of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've
> tried
> to signal another SQL statement to perform the records count but this
> seems
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to
> reduce
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||I performed the following test but @.@.rowcount return me 0. Please check for
me if there is something wrong with the code. Thanks
---
declare @.temp varchar(50)
declare my_cursor cursor fast_forward for
select column01 from table01
open my_cursor
select @.@.rowcount
fetch my_cursor into @.temp
while @.@.fetch_status = 0
begin
print @.temp
fetch my_cursor into @.temp
end
close my_cursor
deallocate my_cursor
----
--
"Wayne Snyder" wrote:
> You can use select count(*)... yada,,, the optimizer can use the index
> entries instead of having to read the data ( if there is a supporting
> index.)
> You can also use select @.@.rowcount AFTER the query...
> You have to open the cursor before the rowcount info is available for
> cursors...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
>
>|||I do agree that cursor is definately not a good idea to be used...I did thin
k
of using table variable previously but since I need to use dynamic SQL and
table variable is not supported to be used together with dynamic SQL...any
other way to get rid of cursor? Will temp table use the equivalent overhead
as cursor? I think i need to temporarily store records (based on passed-in
filtering criteria) to somewhere else so that i'm able to loop the records
and perform further analysis. My stored procedure will return one summarized
result based on passed-in filtering criteria.
"David Portas" wrote:
> If performance is a concern for you then how about getting rid of the
> cursor altogether? Cursors are rarely a good idea.
> --
> David Portas
> SQL Server MVP
> --
>|||> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
Who said that?
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.c cursor
set @.sql = N'set @.c = cursor local forward_only static read_only for select
orderid, orderdate from orders where orderdate >= ''19960101'' and orderdate
< ''19970101''; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c output
if cursor_status('variable', '@.c') >= 0
begin
print @.@.cursor_rows
close @.c
deallocate @.c
end
go
I am not advocating for cursors.
AMB
"LBT" wrote:
> Thanks for the suggestion. The time taken is still approximately equal to
the
> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
>
> "Alejandro Mesa" wrote:
>|||Your reply just poses more questions upon questions. Why dynamic SQL?
Dynamic SQL, like cursors, is something you should aim to avoid, or at
least minimize. Why "loop the records"? Or for that matter, why
"temporarily store records"? (BTW the term "rows" is generally
preferred to "records" in RDBMS).
In short, the best way to get help is to describe *what* you want to do
rather than *how* you think you should go about it. Tell us what your
goal is and show us your data structure as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||>> My stored procedure will return one summarized result based on
passed-in filtering criteria. <<
Get a **basic** software engineering book and look up coupling and
cohesion. In a properly designed module, you do not pass in criteria.
That module would have no cohesion. This is far more basic than SQL;
this is how to be any kind of programmer.
Let's go back to square one and find out what you are trying to do and
then we can look for a set-based, relational approach that will give
you a maintainable procedure. You might also want to consider taking
college courses on software engineering, then learn databasess and data
modeling.
I have created a SQL cursor for records processing in a stored procedure. I
probably can use the @.@.Cursor_Rows function in order to obtain total rows of
record contained inside the cursor. But once I declare the cursor as
FAST_FORWARD, it always return me -1. I need to declare the cursor as
FAST_FORWARD as it really helps in tuning the performance. Else my stored
procedure will take longer time to execute.
Any other way I can use to get the total records being returned? I've tried
to signal another SQL statement to perform the records count but this seems
to create redundant overhead. I believe if I can do anything to existing
cursor without having extra Select Count statement, it would help to reduce
unnecessary processing and shorten the overall time required.
Really appreciate for any advice or suggestion. Thanks a lot.Can you test perfoermance between your current cursor declaration and the on
e
I am posting?
declare my_cursor cursor
local
forward_only
static
read_only
for
...
AMB
"LBT" wrote:
> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've trie
d
> to signal another SQL statement to perform the records count but this seem
s
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to reduc
e
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||If performance is a concern for you then how about getting rid of the
cursor altogether? Cursors are rarely a good idea.
David Portas
SQL Server MVP
--|||Thanks for the suggestion. The time taken is still approximately equal to th
e
cursor which is declared without those keywords. And I can't declare the
cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I forgo
t
to write out this concern in my previous post).
"Alejandro Mesa" wrote:
> Can you test perfoermance between your current cursor declaration and the
one
> I am posting?
> declare my_cursor cursor
> local
> forward_only
> static
> read_only
> for
> ...
>
> AMB
>
> "LBT" wrote:
>|||You can use select count(*)... yada,,, the optimizer can use the index
entries instead of having to read the data ( if there is a supporting
index.)
You can also use select @.@.rowcount AFTER the query...
You have to open the cursor before the rowcount info is available for
cursors...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
> I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
> of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've
> tried
> to signal another SQL statement to perform the records count but this
> seems
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to
> reduce
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||I performed the following test but @.@.rowcount return me 0. Please check for
me if there is something wrong with the code. Thanks
---
declare @.temp varchar(50)
declare my_cursor cursor fast_forward for
select column01 from table01
open my_cursor
select @.@.rowcount
fetch my_cursor into @.temp
while @.@.fetch_status = 0
begin
print @.temp
fetch my_cursor into @.temp
end
close my_cursor
deallocate my_cursor
----
--
"Wayne Snyder" wrote:
> You can use select count(*)... yada,,, the optimizer can use the index
> entries instead of having to read the data ( if there is a supporting
> index.)
> You can also use select @.@.rowcount AFTER the query...
> You have to open the cursor before the rowcount info is available for
> cursors...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
>
>|||I do agree that cursor is definately not a good idea to be used...I did thin
k
of using table variable previously but since I need to use dynamic SQL and
table variable is not supported to be used together with dynamic SQL...any
other way to get rid of cursor? Will temp table use the equivalent overhead
as cursor? I think i need to temporarily store records (based on passed-in
filtering criteria) to somewhere else so that i'm able to loop the records
and perform further analysis. My stored procedure will return one summarized
result based on passed-in filtering criteria.
"David Portas" wrote:
> If performance is a concern for you then how about getting rid of the
> cursor altogether? Cursors are rarely a good idea.
> --
> David Portas
> SQL Server MVP
> --
>|||> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
Who said that?
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.c cursor
set @.sql = N'set @.c = cursor local forward_only static read_only for select
orderid, orderdate from orders where orderdate >= ''19960101'' and orderdate
< ''19970101''; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c output
if cursor_status('variable', '@.c') >= 0
begin
print @.@.cursor_rows
close @.c
deallocate @.c
end
go
I am not advocating for cursors.
AMB
"LBT" wrote:
> Thanks for the suggestion. The time taken is still approximately equal to
the
> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
>
> "Alejandro Mesa" wrote:
>|||Your reply just poses more questions upon questions. Why dynamic SQL?
Dynamic SQL, like cursors, is something you should aim to avoid, or at
least minimize. Why "loop the records"? Or for that matter, why
"temporarily store records"? (BTW the term "rows" is generally
preferred to "records" in RDBMS).
In short, the best way to get help is to describe *what* you want to do
rather than *how* you think you should go about it. Tell us what your
goal is and show us your data structure as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||>> My stored procedure will return one summarized result based on
passed-in filtering criteria. <<
Get a **basic** software engineering book and look up coupling and
cohesion. In a properly designed module, you do not pass in criteria.
That module would have no cohesion. This is far more basic than SQL;
this is how to be any kind of programmer.
Let's go back to square one and find out what you are trying to do and
then we can look for a set-based, relational approach that will give
you a maintainable procedure. You might also want to consider taking
college courses on software engineering, then learn databasess and data
modeling.
Subscribe to:
Posts (Atom)