I am not too good at programming but I need to get the
names and the count of the selected tables (Tables that
starts with aa). I should have the following fromat.
Tablename Count
table1 256
table2 347
..... ...
I wrote a while statement but my select at the end don't
work.
Can anyone show me how my query should look like '
Thanks for any help..There isn't any queries that I know of that will return the table names.
This would have to be done in program code there is probable an API for it,
however the query for the count is simple if you can get the table name (in
code) beginning with aa:
SELECT count(*) FROM TableName
Hope it helps
Gav
"George" <anonymous@.discussions.microsoft.com> wrote in message
news:8e6801c40841$78eb9ce0$a601280a@.phx.gbl...
> I am not too good at programming but I need to get the
> names and the count of the selected tables (Tables that
> starts with aa). I should have the following fromat.
> Tablename Count
> table1 256
> table2 347
> ..... ...
> I wrote a while statement but my select at the end don't
> work.
> Can anyone show me how my query should look like '
> Thanks for any help..|||Here is a basic rowcount routine:
use pubs
go
SELECT A.name, B.rows=20
FROM sysobjects A
JOIN sysindexes B ON A.ID =3D B.ID
WHERE A.type =3D 'U'
AND B.INDID < 2=20
ORDER BY A.Name
--=20
Keith
"George" <anonymous@.discussions.microsoft.com> wrote in message =
news:8e6801c40841$78eb9ce0$a601280a@.phx.gbl...
> I am not too good at programming but I need to get the=20
> names and the count of the selected tables (Tables that=20
> starts with aa). I should have the following fromat.
>=20
> Tablename Count
> table1 256
> table2 347
> ..... ...
>=20
> I wrote a while statement but my select at the end don't=20
> work.
> Can anyone show me how my query should look like '
>=20
> Thanks for any help..|||That did it.
Thanks a lot.
>--Original Message--
>Here is a basic rowcount routine:
>use pubs
>go
>SELECT A.name, B.rows
>FROM sysobjects A
>JOIN sysindexes B ON A.ID = B.ID
>WHERE A.type = 'U'
> AND B.INDID < 2
>ORDER BY A.Name
>--
>Keith
>
>"George" <anonymous@.discussions.microsoft.com> wrote in
message news:8e6801c40841$78eb9ce0$a601280a@.phx.gbl...
don't
>.
>sql
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Thursday, March 29, 2012
Count problems
I have sqldatasources that are used to display information in a gridview. The data that is being collected comes from 3 different tables. From the one table called comments all i want is a count of all comment table rows that have a common id with a row in the files table. The select query i have right now only returns results that have a comment count of more than 0, i want entries with a comment count of 0 to be found... here is what i have so far (i am a sql noob :( )
SELECT dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID AS Expr1, dbo.Users.Name, dbo.Users.Alias, COUNT(dbo.Comments.FID) AS comcount
FROM dbo.Files INNER JOIN dbo.Users ON dbo.Files.UID = dbo.Users.UID INNER JOIN dbo.Comments ON dbo.Files.FID = dbo.Comments.FID
WHERE (dbo.Files.Company = @.Company)
GROUP BY dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID, dbo.Users.Name, dbo.Users.AliasThere is probably an easier way to do this but try this.. I think it might work..
SELECT dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID AS Expr1, dbo.Users.Name, dbo.Users.Alias,
SUM(CASE WHEN dbo.Comments.FID IS NOT NULL THEN 1 ELSE 0 END) AS comcount
FROM dbo.Files
LEFT JOIN dbo.Users ON dbo.Files.UID = dbo.Users.UID
LEFT JOIN dbo.Comments ON dbo.Files.FID = dbo.Comments.FID
WHERE (dbo.Files.Company = @.Company)
GROUP BY dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID, dbo.Users.Name, dbo.Users.Aliassql
Count of Units * Unit Price
My dsv has two tables (one is used materials another is material price).
My used material dimension has "stock nr" and "count of material" attributes and material price table has "stock nr" and "unit price" attributes.
My goal is to calculate "total price" measure for the cube with count of material * unit price. Not all the materials have price value...
I don't know what to do. Could you recommend something?
- Using integration services instead of analysis service before processing.
- Using an MDX query (I am not good at MDX)
- Using Calculations?
Please help me in detail?
You could also join the 2 tables in the DSV, within a Named Query which returns total price as a computed column, like:
select u.[stock nr], p.[unit price], u.[count of material] * p.[unit price] as [total price]
from [used material] u
join [material price] p
on u.[stock nr] = p.[stock nr]
|||too late but thanks. I've used itCount of Units * Unit Price
My dsv has two tables (one is used materials another is material price).
My used material dimension has "stock nr" and "count of material" attributes and material price table has "stock nr" and "unit price" attributes.
My goal is to calculate "total price" measure for the cube with count of material * unit price. Not all the materials have price value...
I don't know what to do. Could you recommend something?
- Using integration services instead of analysis service before processing.
- Using an MDX query (I am not good at MDX)
- Using Calculations?
Please help me in detail?
You could also join the 2 tables in the DSV, within a Named Query which returns total price as a computed column, like:
select u.[stock nr], p.[unit price], u.[count of material] * p.[unit price] as [total price]
from [used material] u
join [material price] p
on u.[stock nr] = p.[stock nr]
|||too late but thanks. I've used itSunday, March 25, 2012
count how many documents are in the table for each name (was "Query Help")
Hello,
Brief overview. Got 2 tables, client table and document table. Both tables have client name as the primary key. Client table shows client info, address, phone, dob. Document table shows client name, document, document type.
I need to write a query that will count how many documents are in the table for each name.
This is attempt at it, please let me know whats wrong. Thanks.
SELECT count [client table].client name as cli_name, count ([document table].name as doc_qty)
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].nameYour syntax is funny:
Try
count ([document table].name) as doc_qty|||wow, that was it, thanks a lot|||Agree also no count at the start.
SELECT [client table].client name as cli_name, count ([document table].name) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].name
Also so check the ID`s think they might be wrong or badly named.
Ie. the document table link on id not clientID
If it does not work post the tables as well|||yeah, the id thing was throwing it off too, i had to rejoin by name and client name from both tables after I gave some thought as to what exactly this query needed to do
working query:
SELECT [client table].[client name] as cli_name, count ([document table].[name]) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].[client name] = [document table].name
GROUP BY [client table].[client name]
ORDER BY [client table].[client name];
Had another question, I'm supposed to place a constraint into both tables to lock them and not allow any new data to be entered. I have read how to write it in code, but am using access. Is there any way to modify and create tables in access2003 in sql view? I really cant stand the GUI.|||Sure, you can use a CREATE TABLE statement just as you would using Query Analyzer.|||sweet, thanks a lot for making my life a whole lot easier(for today atleast)sql
Brief overview. Got 2 tables, client table and document table. Both tables have client name as the primary key. Client table shows client info, address, phone, dob. Document table shows client name, document, document type.
I need to write a query that will count how many documents are in the table for each name.
This is attempt at it, please let me know whats wrong. Thanks.
SELECT count [client table].client name as cli_name, count ([document table].name as doc_qty)
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].nameYour syntax is funny:
Try
count ([document table].name) as doc_qty|||wow, that was it, thanks a lot|||Agree also no count at the start.
SELECT [client table].client name as cli_name, count ([document table].name) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].name
Also so check the ID`s think they might be wrong or badly named.
Ie. the document table link on id not clientID
If it does not work post the tables as well|||yeah, the id thing was throwing it off too, i had to rejoin by name and client name from both tables after I gave some thought as to what exactly this query needed to do
working query:
SELECT [client table].[client name] as cli_name, count ([document table].[name]) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].[client name] = [document table].name
GROUP BY [client table].[client name]
ORDER BY [client table].[client name];
Had another question, I'm supposed to place a constraint into both tables to lock them and not allow any new data to be entered. I have read how to write it in code, but am using access. Is there any way to modify and create tables in access2003 in sql view? I really cant stand the GUI.|||Sure, you can use a CREATE TABLE statement just as you would using Query Analyzer.|||sweet, thanks a lot for making my life a whole lot easier(for today atleast)sql
Count from multiple tables
I have 4 tables
One is a user table and the other three contain records for the users. They all have a USERNAME column
I would like to get a count of records for each table grouped by USERNAME
Thanks again
Greg
One is a user table and the other three contain records for the users. They all have a USERNAME column
I would like to get a count of records for each table grouped by USERNAME
My output would be:
username,totalFrom1,totalFrom2,totalFrom3
Thanks For the help!Mybe something like:
select|||Thanks! I would never have figured that one out on my own, but I see how it works.
username,
isnull(t1.ttlfrom1,0) ttlfrom1,
isnull(t2.ttlfrom2,0) ttlfrom2,
isnull(t3.ttlfrom3,0) ttlfrom3
from <users> u
left join
(
select username, count(username) ttlfrom1
from
<t1>
group by username
) t1 on t1.username = u.username
left join
(
select username, count(username) ttlfrom2
from
<t2>
group by username
) t2 on t2.username = u.username
left join
(
select username, count(username) ttlfrom3
from
<t3>
group by username
) t3 on t3.username = u.username
Thanks again
Greg
count for each month
I have 2 tables in Access 2000 : Members and Messages
I get all the members.Id for a category with a Procedure
List_Members_3 >>>
SELECT Members.Members_Id
FROM Members
WHERE Members.Cat = 3
then I want to get all the Messages of the Members for january 2004
SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
GROUP BY Year([DateMessages]), Month([DateMessages])
HAVING (((Year([DateMessages]))=2004) AND ((Month([DateMessages]))=1));
I get one row = Count
how can I get 12 rows for each month ?
Month([DateMessages])=(1 to 12)
--------
and how can I avoid >>>
SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
with sommething like >>>
SELECT Count(Messages.Id) AS CountOfId
FROM Messages Where Messages.Members_Id IN (SELECT Members.Id
FROM Members
WHERE Members.Cat = 3)
thank youuse an integers table:
create table integers ( i integer )
insert into integers values ( 1 )
insert into integers values ( 2 )
insert into integers values ( 3)
insert into integers values ( 4)
insert into integers values ( 5)
insert into integers values ( 6)
insert into integers values ( 7)
insert into integers values ( 8)
insert into integers values ( 9)
insert into integers values ( 10 )
insert into integers values ( 11 )
insert into integers values ( 12 )
then use a LEFT join from the integers to your data using i to match the month number
select i as month
, Count(Messages.Id) as CountOfId
from integers
left outer
join Messages
on i = Month(DateMessages)
and Year(DateMessages) = 2004
inner
join List_Members_3
on Messages.Id = List_Members_3.Id
group
by i|||it seems absolutly crazy and fantastic ! ::-))
thank you !!!
I get all the members.Id for a category with a Procedure
List_Members_3 >>>
SELECT Members.Members_Id
FROM Members
WHERE Members.Cat = 3
then I want to get all the Messages of the Members for january 2004
SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
GROUP BY Year([DateMessages]), Month([DateMessages])
HAVING (((Year([DateMessages]))=2004) AND ((Month([DateMessages]))=1));
I get one row = Count
how can I get 12 rows for each month ?
Month([DateMessages])=(1 to 12)
--------
and how can I avoid >>>
SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
with sommething like >>>
SELECT Count(Messages.Id) AS CountOfId
FROM Messages Where Messages.Members_Id IN (SELECT Members.Id
FROM Members
WHERE Members.Cat = 3)
thank youuse an integers table:
create table integers ( i integer )
insert into integers values ( 1 )
insert into integers values ( 2 )
insert into integers values ( 3)
insert into integers values ( 4)
insert into integers values ( 5)
insert into integers values ( 6)
insert into integers values ( 7)
insert into integers values ( 8)
insert into integers values ( 9)
insert into integers values ( 10 )
insert into integers values ( 11 )
insert into integers values ( 12 )
then use a LEFT join from the integers to your data using i to match the month number
select i as month
, Count(Messages.Id) as CountOfId
from integers
left outer
join Messages
on i = Month(DateMessages)
and Year(DateMessages) = 2004
inner
join List_Members_3
on Messages.Id = List_Members_3.Id
group
by i|||it seems absolutly crazy and fantastic ! ::-))
thank you !!!
Thursday, March 22, 2012
Count all records in all tables
I need some help with this. I was able to count all the records in our
database using the user_tables and user_tab_columns tables after
refreshing the statistics on this database.
We are doing an upgrade of a system and I will not be able to refresh
the statistics during the upgrade. I need more of a manual process of
running these queries.
database using the user_tables and user_tab_columns tables after
refreshing the statistics on this database.
We are doing an upgrade of a system and I will not be able to refresh
the statistics during the upgrade. I need more of a manual process of
running these queries.
Now I do:
select A.table_name, round(A.num_rows,0) as rowcount,
count(b.table_name) as ColumnCount
from dba_tables A, dba_tab_columns B
where A.table_name = B.table_name and A.owner in ('PS','SYSADM')
group by A.table_name, A.num_rows
order by rowcount desc, columncount desc
But I can't use the num_rows anymore so I was thinking more to do this:
Select A.table_name from
(select count(*) from A.Table_name B where A.Table_name =
B.Table_Name)
from user_table
This does not work for me since I don't know how to pass the table_name
from the first select to the second select. The logic is there but the
syntax is not.
Please help.Do you think this is the right group ? You are refering to oracle
tables / dynamic views.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
Count 4 tables
I am trying to get a count of rows in 4 tables at once.
Output:
Table1 Table2 Table3 Table4
12345 143 892626 28428
Or maybe even
Table Count
Table1 12345
Table2 143
Table3 892626
Table4 28428
Thanks,
Cory
here it is...
Select
(Select Count(*) From Table1) [Table1],
(Select Count(*) From Table2) [Table2],
(Select Count(*) From Table3) [Table3],
(Select Count(*) From Table4) [Table4]
--Or
Select 'Table1' Table, Count(*) Count From Table1
Union ALL
Select 'Table2' Table, Count(*) Count From Table2
Union ALL
Select 'Table3' Table, Count(*) Count From Table3
Union ALL
Select 'Table4' Table, Count(*) Count From Table4
|||Would this do the trick?
SELECT 'Table1' AS table, COUNT (*) AS count FROM table1 UNION
SELECT 'Table2', COUNT (*) FROM table2 UNION
SELECT 'Table3, COUNT (*) FROM table3 UNION
SELECT 'Table4', COUNT (*) FROM table4
|||At always better option to use UNION ALL rather than UNION.
Tuesday, March 20, 2012
Could this SP be causing Timeout errors
The other day I had added a little call to a proc that logged
the adds or updates to several tables. I wanted to do this to tell who made
changes to the records in case we have an issue down the road. After putting
this change in I started to really have a major problems with the system
slowing down. Here is some the tables and procs:
Table TableLog:
MessageId int
Message varchar 500
MessageDate datetime
AppLoggedInUser varchar 100
ComputerName varchar 100
CompLoggedInUser varchar 100
Proc to add new entries:
CREATE PROCEDURE [dbo].[AddTableLog]
@.Message varchar(500),
@.AppLoggedInUser varchar(100),
@.ComputerName varchar(100)
AS
Insert into TableLogs( Message, MessageDate, AppLoggedInUser, ComputerName,
CompLoggedInUser)
Values( @.Message, GetDate(), @.AppLoggedInUser, @.ComputerName, Session_User )
GO
How the above proc is used:
CREATE PROCEDURE [dbo].[nf_AddAttendanceV3_1]
@.PatientId varchar(20),
@.AttendDate datetime,
@.C_ID int,
@.GroupTime varchar(8),
@.Diagnostic tinyint,
@.Program tinyint,
@.AttendedGroup tinyint,
@.DocId int,
@.GroupId int,
@.GroupType Tinyint,
@.ComputerName varchar(100),
@.LoggedInUser varchar(100)
AS
Insert into Attendance (PatientId, AttendDate, C_ID, GroupTime, Diagnostic,
Program, AttendedGroup, GroupType, GroupId, DocId ,CreationDate,
UpdatedBy,ComputerName, LoggedInUser)
values(@.PatientId, @.AttendDate, @.C_ID, @.GroupTime, @.Diagnostic, @.Program,
@.AttendedGroup, @.GroupType, @.GroupId, @.DocId , GetDate(),
Session_User,@.ComputerName, @.LoggedInUser)
Declare @.UserMessage Varchar(500)
select @.UserMessage = 'User ' + @.LoggedInUser + ' has added a attendance
record for PatientId: ' + @.PatientId + ' for attendDate: ' + @.AttendDate
Exec AddTableLog @.UserMessage, @.LoggedInUser, @.ComputerName
GO
Does this look like it could cause the slow down in the system. I backed out
most of the procs that had the call to the Log table, but still have the
issue. If you think this is causing the issue with slow downs or locks, is
there a better way to do this. THanks for any suggestions.
MichaelHope this helps a bit:-
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
Manu Jaidka
"Michael" wrote:
> The other day I had added a little call to a proc that logged
> the adds or updates to several tables. I wanted to do this to tell who made
> changes to the records in case we have an issue down the road. After putting
> this change in I started to really have a major problems with the system
> slowing down. Here is some the tables and procs:
> Table TableLog:
> MessageId int
> Message varchar 500
> MessageDate datetime
> AppLoggedInUser varchar 100
> ComputerName varchar 100
> CompLoggedInUser varchar 100
> Proc to add new entries:
> CREATE PROCEDURE [dbo].[AddTableLog]
> @.Message varchar(500),
> @.AppLoggedInUser varchar(100),
> @.ComputerName varchar(100)
> AS
> Insert into TableLogs( Message, MessageDate, AppLoggedInUser, ComputerName,
> CompLoggedInUser)
> Values( @.Message, GetDate(), @.AppLoggedInUser, @.ComputerName, Session_User )
> GO
> How the above proc is used:
> CREATE PROCEDURE [dbo].[nf_AddAttendanceV3_1]
> @.PatientId varchar(20),
> @.AttendDate datetime,
> @.C_ID int,
> @.GroupTime varchar(8),
> @.Diagnostic tinyint,
> @.Program tinyint,
> @.AttendedGroup tinyint,
> @.DocId int,
> @.GroupId int,
> @.GroupType Tinyint,
> @.ComputerName varchar(100),
> @.LoggedInUser varchar(100)
> AS
> Insert into Attendance (PatientId, AttendDate, C_ID, GroupTime, Diagnostic,
> Program, AttendedGroup, GroupType, GroupId, DocId ,CreationDate,
> UpdatedBy,ComputerName, LoggedInUser)
> values(@.PatientId, @.AttendDate, @.C_ID, @.GroupTime, @.Diagnostic, @.Program,
> @.AttendedGroup, @.GroupType, @.GroupId, @.DocId , GetDate(),
> Session_User,@.ComputerName, @.LoggedInUser)
> Declare @.UserMessage Varchar(500)
> select @.UserMessage = 'User ' + @.LoggedInUser + ' has added a attendance
> record for PatientId: ' + @.PatientId + ' for attendDate: ' + @.AttendDate
> Exec AddTableLog @.UserMessage, @.LoggedInUser, @.ComputerName
> GO
> Does this look like it could cause the slow down in the system. I backed out
> most of the procs that had the call to the Log table, but still have the
> issue. If you think this is causing the issue with slow downs or locks, is
> there a better way to do this. THanks for any suggestions.
> Michael
>
the adds or updates to several tables. I wanted to do this to tell who made
changes to the records in case we have an issue down the road. After putting
this change in I started to really have a major problems with the system
slowing down. Here is some the tables and procs:
Table TableLog:
MessageId int
Message varchar 500
MessageDate datetime
AppLoggedInUser varchar 100
ComputerName varchar 100
CompLoggedInUser varchar 100
Proc to add new entries:
CREATE PROCEDURE [dbo].[AddTableLog]
@.Message varchar(500),
@.AppLoggedInUser varchar(100),
@.ComputerName varchar(100)
AS
Insert into TableLogs( Message, MessageDate, AppLoggedInUser, ComputerName,
CompLoggedInUser)
Values( @.Message, GetDate(), @.AppLoggedInUser, @.ComputerName, Session_User )
GO
How the above proc is used:
CREATE PROCEDURE [dbo].[nf_AddAttendanceV3_1]
@.PatientId varchar(20),
@.AttendDate datetime,
@.C_ID int,
@.GroupTime varchar(8),
@.Diagnostic tinyint,
@.Program tinyint,
@.AttendedGroup tinyint,
@.DocId int,
@.GroupId int,
@.GroupType Tinyint,
@.ComputerName varchar(100),
@.LoggedInUser varchar(100)
AS
Insert into Attendance (PatientId, AttendDate, C_ID, GroupTime, Diagnostic,
Program, AttendedGroup, GroupType, GroupId, DocId ,CreationDate,
UpdatedBy,ComputerName, LoggedInUser)
values(@.PatientId, @.AttendDate, @.C_ID, @.GroupTime, @.Diagnostic, @.Program,
@.AttendedGroup, @.GroupType, @.GroupId, @.DocId , GetDate(),
Session_User,@.ComputerName, @.LoggedInUser)
Declare @.UserMessage Varchar(500)
select @.UserMessage = 'User ' + @.LoggedInUser + ' has added a attendance
record for PatientId: ' + @.PatientId + ' for attendDate: ' + @.AttendDate
Exec AddTableLog @.UserMessage, @.LoggedInUser, @.ComputerName
GO
Does this look like it could cause the slow down in the system. I backed out
most of the procs that had the call to the Log table, but still have the
issue. If you think this is causing the issue with slow downs or locks, is
there a better way to do this. THanks for any suggestions.
MichaelHope this helps a bit:-
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
Manu Jaidka
"Michael" wrote:
> The other day I had added a little call to a proc that logged
> the adds or updates to several tables. I wanted to do this to tell who made
> changes to the records in case we have an issue down the road. After putting
> this change in I started to really have a major problems with the system
> slowing down. Here is some the tables and procs:
> Table TableLog:
> MessageId int
> Message varchar 500
> MessageDate datetime
> AppLoggedInUser varchar 100
> ComputerName varchar 100
> CompLoggedInUser varchar 100
> Proc to add new entries:
> CREATE PROCEDURE [dbo].[AddTableLog]
> @.Message varchar(500),
> @.AppLoggedInUser varchar(100),
> @.ComputerName varchar(100)
> AS
> Insert into TableLogs( Message, MessageDate, AppLoggedInUser, ComputerName,
> CompLoggedInUser)
> Values( @.Message, GetDate(), @.AppLoggedInUser, @.ComputerName, Session_User )
> GO
> How the above proc is used:
> CREATE PROCEDURE [dbo].[nf_AddAttendanceV3_1]
> @.PatientId varchar(20),
> @.AttendDate datetime,
> @.C_ID int,
> @.GroupTime varchar(8),
> @.Diagnostic tinyint,
> @.Program tinyint,
> @.AttendedGroup tinyint,
> @.DocId int,
> @.GroupId int,
> @.GroupType Tinyint,
> @.ComputerName varchar(100),
> @.LoggedInUser varchar(100)
> AS
> Insert into Attendance (PatientId, AttendDate, C_ID, GroupTime, Diagnostic,
> Program, AttendedGroup, GroupType, GroupId, DocId ,CreationDate,
> UpdatedBy,ComputerName, LoggedInUser)
> values(@.PatientId, @.AttendDate, @.C_ID, @.GroupTime, @.Diagnostic, @.Program,
> @.AttendedGroup, @.GroupType, @.GroupId, @.DocId , GetDate(),
> Session_User,@.ComputerName, @.LoggedInUser)
> Declare @.UserMessage Varchar(500)
> select @.UserMessage = 'User ' + @.LoggedInUser + ' has added a attendance
> record for PatientId: ' + @.PatientId + ' for attendDate: ' + @.AttendDate
> Exec AddTableLog @.UserMessage, @.LoggedInUser, @.ComputerName
> GO
> Does this look like it could cause the slow down in the system. I backed out
> most of the procs that had the call to the Log table, but still have the
> issue. If you think this is causing the issue with slow downs or locks, is
> there a better way to do this. THanks for any suggestions.
> Michael
>
Monday, March 19, 2012
could not open FCB...
I have a DB with 4 tables with errors.
If I do a SELECT count(*) on this tables, works ok
but if I do a SELECT * on this tables, the following
message appears:
Could not open FCB for invalid file ID 0 in database
I tried to drop the indexes ... no problem
I tried to create the indexes .. don't work the same error
appears.
Can I fix the problem ?
ThanksSounds like the indexes are OK, but the data table is corrupt... Run DBCC
checktable on the table... you may have to restore from the last known good
backup...
You may need to open a call to MS (FCB stands for File Control Block I
beleive)
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Pablo" <ppastormerlo@.lpa.com.ar> wrote in message
news:091e01c36bf4$cd0895b0$a001280a@.phx.gbl...
> I have a DB with 4 tables with errors.
> If I do a SELECT count(*) on this tables, works ok
> but if I do a SELECT * on this tables, the following
> message appears:
> Could not open FCB for invalid file ID 0 in database
> I tried to drop the indexes ... no problem
> I tried to create the indexes .. don't work the same error
> appears.
> Can I fix the problem ?
> Thanks
If I do a SELECT count(*) on this tables, works ok
but if I do a SELECT * on this tables, the following
message appears:
Could not open FCB for invalid file ID 0 in database
I tried to drop the indexes ... no problem
I tried to create the indexes .. don't work the same error
appears.
Can I fix the problem ?
ThanksSounds like the indexes are OK, but the data table is corrupt... Run DBCC
checktable on the table... you may have to restore from the last known good
backup...
You may need to open a call to MS (FCB stands for File Control Block I
beleive)
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Pablo" <ppastormerlo@.lpa.com.ar> wrote in message
news:091e01c36bf4$cd0895b0$a001280a@.phx.gbl...
> I have a DB with 4 tables with errors.
> If I do a SELECT count(*) on this tables, works ok
> but if I do a SELECT * on this tables, the following
> message appears:
> Could not open FCB for invalid file ID 0 in database
> I tried to drop the indexes ... no problem
> I tried to create the indexes .. don't work the same error
> appears.
> Can I fix the problem ?
> Thanks
Could not obtain a required interface error
I have created a linked server that connects to "mpp" file, and it connects and opens successfuly returning a list of tables that exists in the linked server to the mpp file.
But the problem i am facing now that whne i write a select statment to get data from the tables in this linked server
select * from linktompp.testmpp.dbo.tasks
such that linktompp is the linked server name
testmpp is the catalog name
tasks is the table name i need to select data from
it shows an error that says
"Could not obtain a required interface from OLE DB provider 'Microsoft.Project.OleDB.11.0'."
"[OLE/DB Provider 'Microsoft.Project.OleDB.11.0' IUnknown::QueryInterface returned 0x80004002: IGetDataSource]"
could anyone tell me the reason of this error
thanks
Hi my friend
I try to insert data into MSP
i can select data from MSP and show into datagrid
but now i coulde not insert data
i live in Iran,you can call to me with Email:jazayery@.gmail.com
Best Reagard
Could not obtain a required interface error
I have created a linked server that connects to "mpp" file, and it connects and opens successfuly returning a list of tables that exists in the linked server to the mpp file.
But the problem i am facing now that whne i write a select statment to get data from the tables in this linked server
select * from linktompp.testmpp.dbo.tasks
such that linktompp is the linked server name
testmpp is the catalog name
tasks is the table name i need to select data from
it shows an error that says
"Could not obtain a required interface from OLE DB provider 'Microsoft.Project.OleDB.11.0'."
"[OLE/DB Provider 'Microsoft.Project.OleDB.11.0' IUnknown::QueryInterface returned 0x80004002: IGetDataSource]"
could anyone tell me the reason of this error
thanks
Hi my friend
I try to insert data into MSP
i can select data from MSP and show into datagrid
but now i coulde not insert data
i live in Iran,you can call to me with Email:jazayery@.gmail.com
Best Reagard
Thursday, March 8, 2012
could not find table error message
I run a script that tells me what tables need to be defragged. This query
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct the
problem and get things back in sync?
Thanks,
Dan D.
Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I get
> an error message that says "Could not find a table or object named 'tblDNS'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.
|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
Dan D.
"Tibor Karaszi" wrote:
> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct the
problem and get things back in sync?
Thanks,
Dan D.
Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I get
> an error message that says "Could not find a table or object named 'tblDNS'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.
|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
Dan D.
"Tibor Karaszi" wrote:
> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>
could not find table error message
I run a script that tells me what tables need to be defragged. This query
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct th
e
problem and get things back in sync?
Thanks,
Dan D.Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I g
et
> an error message that says "Could not find a table or object named 'tblDNS
'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct
the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
--
Dan D.
"Tibor Karaszi" wrote:
> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct th
e
problem and get things back in sync?
Thanks,
Dan D.Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I g
et
> an error message that says "Could not find a table or object named 'tblDNS
'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct
the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
--
Dan D.
"Tibor Karaszi" wrote:
> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>
could not find table error message
I run a script that tells me what tables need to be defragged. This query
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct the
problem and get things back in sync?
Thanks,
--
Dan D.Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I get
> an error message that says "Could not find a table or object named 'tblDNS'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
--
Dan D.
"Tibor Karaszi" wrote:
> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
> >I run a script that tells me what tables need to be defragged. This query
> > gets a list of the tables:
> > SELECT TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE TABLE_TYPE = 'BASE TABLE'
> >
> > and then this query checks to see how fragmented the table is:
> > EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> >
> > The first query is finding some tables that when the second query runs I get
> > an error message that says "Could not find a table or object named 'tblDNS'.
> > Check sysobjects.
> >
> > Why would the first query find a table but the second query wouldn't find
> > the table. They must be looking in two different places. How do I correct the
> > problem and get things back in sync?
> >
> > Thanks,
> >
> >
> >
> > --
> > Dan D.
>
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct the
problem and get things back in sync?
Thanks,
--
Dan D.Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I get
> an error message that says "Could not find a table or object named 'tblDNS'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
--
Dan D.
"Tibor Karaszi" wrote:
> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
> >I run a script that tells me what tables need to be defragged. This query
> > gets a list of the tables:
> > SELECT TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE TABLE_TYPE = 'BASE TABLE'
> >
> > and then this query checks to see how fragmented the table is:
> > EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> >
> > The first query is finding some tables that when the second query runs I get
> > an error message that says "Could not find a table or object named 'tblDNS'.
> > Check sysobjects.
> >
> > Why would the first query find a table but the second query wouldn't find
> > the table. They must be looking in two different places. How do I correct the
> > problem and get things back in sync?
> >
> > Thanks,
> >
> >
> >
> > --
> > Dan D.
>
Wednesday, March 7, 2012
Could not find server 'x' in sysservers
SQL2005:
Updating some tables in a database works fine. Updating others in the
same database fails with
"Could not find server 'picasso' in sysservers".
How come it goes for some and not others?
"picasso" is our old server that doesn't exist anymore. It was holding
a SQL-2000 that has been upgraded to SQL-2005...?
Thanks in advance for any help.
Regards /SnedkerIs it possible that you have a trigger on those tables that tries to
insert into 'picasso' ?
run EXEC sp_helptrigger TableName to find out if there are triggers on
those tables
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Morten Snedker wrote:
> SQL2005:
> Updating some tables in a database works fine. Updating others in the
> same database fails with
> "Could not find server 'picasso' in sysservers".
> How come it goes for some and not others?
> "picasso" is our old server that doesn't exist anymore. It was holding
> a SQL-2000 that has been upgraded to SQL-2005...?
>
> Thanks in advance for any help.
>
> Regards /Snedker|||On 7 Jun 2006 05:56:57 -0700, "SQL Menace" <denis.gobo@.gmail.com>
wrote:
Aaaahaaaaaaaaaaaaaaaaaaa ! :-)
Actually I already changed some tables for that exact same thing, but
seems like I didn't get all the way around. Thanks for getting me back
on track! ;-)
Regards /Snedker
>Is it possible that you have a trigger on those tables that tries to
>insert into 'picasso' ?
>run EXEC sp_helptrigger TableName to find out if there are triggers on
>those tables
>
>Denis the SQL Menace
>http://sqlservercode.blogspot.com/
>Morten Snedker wrote:
Updating some tables in a database works fine. Updating others in the
same database fails with
"Could not find server 'picasso' in sysservers".
How come it goes for some and not others?
"picasso" is our old server that doesn't exist anymore. It was holding
a SQL-2000 that has been upgraded to SQL-2005...?
Thanks in advance for any help.
Regards /SnedkerIs it possible that you have a trigger on those tables that tries to
insert into 'picasso' ?
run EXEC sp_helptrigger TableName to find out if there are triggers on
those tables
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Morten Snedker wrote:
> SQL2005:
> Updating some tables in a database works fine. Updating others in the
> same database fails with
> "Could not find server 'picasso' in sysservers".
> How come it goes for some and not others?
> "picasso" is our old server that doesn't exist anymore. It was holding
> a SQL-2000 that has been upgraded to SQL-2005...?
>
> Thanks in advance for any help.
>
> Regards /Snedker|||On 7 Jun 2006 05:56:57 -0700, "SQL Menace" <denis.gobo@.gmail.com>
wrote:
Aaaahaaaaaaaaaaaaaaaaaaa ! :-)
Actually I already changed some tables for that exact same thing, but
seems like I didn't get all the way around. Thanks for getting me back
on track! ;-)
Regards /Snedker
>Is it possible that you have a trigger on those tables that tries to
>insert into 'picasso' ?
>run EXEC sp_helptrigger TableName to find out if there are triggers on
>those tables
>
>Denis the SQL Menace
>http://sqlservercode.blogspot.com/
>Morten Snedker wrote:
Friday, February 24, 2012
Could not Create Acceptable cursor
I have sql server 2005 to which a sql 2000 server is added as a linked server.
when I try to update some tables in SQL2000 server from sql2005 server
It generates the following error.
when I try to update some tables in SQL2000 server from sql2005 server
It generates the following error.
"Could not generate acceptable cursor."
Thanks in advance for the help
See KnowledgeBase article:
http://support.microsoft.com/kb/302477
|||I ran into the same thing just today.
I was running an update query on a linked server.
I added a primary key on the destination table and that handled the problem.
Could not Create Acceptable cursor
I have sql server 2005 to which a sql 2000 server is added as a linked server.
when I try to update some tables in SQL2000 server from sql2005 server
It generates the following error.
when I try to update some tables in SQL2000 server from sql2005 server
It generates the following error.
"Could not generate acceptable cursor."
Thanks in advance for the help
See KnowledgeBase article:
http://support.microsoft.com/kb/302477
|||I ran into the same thing just today.
I was running an update query on a linked server.
I added a primary key on the destination table and that handled the problem.
Could Not Bulk Copy
Hi:
I am doing a full snapshot on couple of subcriptions but getting this message from last couple of days. I have to do this at night because tables are very large so it does not block the users. I am not sure which table this error is happening. It used to work fine but from last couple of days this has started to happen. I need to re-sync my subscription database with production database but some of the tables are giving problem.
The process could not bulk copy out of table '[dbo].[syncobj_0x3735393934363031]'.
I/O error while writing BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
Please let me know.
Thanks
Mike, you are running out of disk space on the snapshot volume, please consider reducing the maximum distribution retention period, configuring the snapshot job to run less frequently, or simply add more disk space to the machine hosting the snapshot files.
HTH
-Raymond
|||Hi Raymond:
Thanks a lot for replying. How can I reduce the maximum distribution retention period using enterprise manager? Please let me know.
Thanks
|||Right-click Replication folder->Configure Publishing, Subscribers, Distribution...->Distributor tab->Properties button-> Store the transactions: At least: text box (min distribution retention) But not more than: (max distribution retention). But before you change that, you should probably make sure the distribution cleanup agent is running without problems (Right Click Replication Monitor->Agents->Miscellaneous Agent->Distribution clean up: <distribution database name> on the right plane). Snapshot files are supposed to be removed as soon as they have distributed to all applicable subscribers unless the immediate_sync publication property is set to 1 so you may want to check that at the publisher database also (select immediate_sync from syspublications [where name = 'your publication name'].
HTH
-Raymond
Subscribe to:
Posts (Atom)