I am trying to count entries into each column heading by zip code, but when I
run the script below I get the same count across all columns. Can someone
tell/show me how to make these unquie counts?
Thanks in advance.
SELECT DISTINCT P.ZIP,
COUNT (evC.day_care_center) AS 'Day Care',
COUNT (evC.drop_care) AS 'Drop-In Care',
COUNT (evC.family_day_care) AS 'Family day care',
COUNT (evC.financial) AS 'Financial',
COUNT (evC.montessori_program2)AS 'Montessori program',
COUNT (evC.nanny_service) AS 'Nanny service',
COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
COUNT (evC.parenting_info) AS 'Parenting information',
COUNT (evC.sick_child_care) AS 'Sick child care',
COUNT (evC.special_needs)AS 'Special needs',
COUNT (evC.summer_camp_care)AS 'Summer camps/care',
COUNT (evC.temporary) AS 'Temporary',
COUNT (evC.transportation2)AS 'Transportation',
COUNT (evC.support_groups) AS 'Support groups',
COUNT (evC.other) AS 'Other'
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
WHERE (pe.Payor_Key = 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1Put the DISTINCT keyword inside the COUNT():
SELECT P.ZIP,
COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jay via SQLMonster.com" <u7124@.uwe> wrote in message
news:7efd1fc265539@.uwe...
I am trying to count entries into each column heading by zip code, but when
I
run the script below I get the same count across all columns. Can someone
tell/show me how to make these unquie counts?
Thanks in advance.
SELECT DISTINCT P.ZIP,
COUNT (evC.day_care_center) AS 'Day Care',
COUNT (evC.drop_care) AS 'Drop-In Care',
COUNT (evC.family_day_care) AS 'Family day care',
COUNT (evC.financial) AS 'Financial',
COUNT (evC.montessori_program2)AS 'Montessori program',
COUNT (evC.nanny_service) AS 'Nanny service',
COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
COUNT (evC.parenting_info) AS 'Parenting information',
COUNT (evC.sick_child_care) AS 'Sick child care',
COUNT (evC.special_needs)AS 'Special needs',
COUNT (evC.summer_camp_care)AS 'Summer camps/care',
COUNT (evC.temporary) AS 'Temporary',
COUNT (evC.transportation2)AS 'Transportation',
COUNT (evC.support_groups) AS 'Support groups',
COUNT (evC.other) AS 'Other'
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
WHERE (pe.Payor_Key = 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||I'm guessing you might need to COUNT the distinct values for each
column. If that is the case:
SELECT P.ZIP,
COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
etc.
If that is not what you need please elaborate on what you actually
hope to see.
Note that since you were already doing a GROUP BY there was no purpose
to having a DISTINCT for the entire query.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Jan 2008 15:49:59 GMT, "Jay via SQLMonster.com" <u7124@.uwe>
wrote:
>I am trying to count entries into each column heading by zip code, but when I
>run the script below I get the same count across all columns. Can someone
>tell/show me how to make these unquie counts?
>Thanks in advance.
>SELECT DISTINCT P.ZIP,
> COUNT (evC.day_care_center) AS 'Day Care',
> COUNT (evC.drop_care) AS 'Drop-In Care',
> COUNT (evC.family_day_care) AS 'Family day care',
> COUNT (evC.financial) AS 'Financial',
> COUNT (evC.montessori_program2)AS 'Montessori program',
> COUNT (evC.nanny_service) AS 'Nanny service',
> COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
> COUNT (evC.parenting_info) AS 'Parenting information',
> COUNT (evC.sick_child_care) AS 'Sick child care',
> COUNT (evC.special_needs)AS 'Special needs',
> COUNT (evC.summer_camp_care)AS 'Summer camps/care',
> COUNT (evC.temporary) AS 'Temporary',
> COUNT (evC.transportation2)AS 'Transportation',
> COUNT (evC.support_groups) AS 'Support groups',
> COUNT (evC.other) AS 'Other'
>FROM Patient_Elg pe
> INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
> INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
>WHERE (pe.Payor_Key = 59)
> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>GROUP BY P.Zip|||I made the changes you suggested and it did make the counts unique but they
are not correct by column heading/category. To check I displayed all the
information for zip code 55901 under the column 'Day Care' for '07. I came up
with a count of 200, but for this script I only get 11. How can I get all
records to count?
Thanks again for your help in advance
Roy Harvey (SQL Server MVP) wrote:
>I'm guessing you might need to COUNT the distinct values for each
>column. If that is the case:
>SELECT P.ZIP,
> COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
> COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
>etc.
>If that is not what you need please elaborate on what you actually
>hope to see.
>Note that since you were already doing a GROUP BY there was no purpose
>to having a DISTINCT for the entire query.
>Roy Harvey
>Beacon Falls, CT
>>I am trying to count entries into each column heading by zip code, but when I
>>run the script below I get the same count across all columns. Can someone
>[quoted text clipped - 25 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip
--
Message posted via http://www.sqlmonster.com|||Usually the fastest way to get answers to questions like this is to give us
the SQL statements to create sample tables and load those tables with sample
data (see www.aspfaq.com/5006 for how to do this). Then tell us the result
you would want to see from that sample data. That keeps us from having to
guess what you want, and you will get an answer that has been tested against
your sample data.
Tom
"jpettigrew via SQLMonster.com" <u7124@.uwe> wrote in message
news:7efdb43406fee@.uwe...
>I made the changes you suggested and it did make the counts unique but they
> are not correct by column heading/category. To check I displayed all the
> information for zip code 55901 under the column 'Day Care' for '07. I came
> up
> with a count of 200, but for this script I only get 11. How can I get all
> records to count?
> Thanks again for your help in advance
> Roy Harvey (SQL Server MVP) wrote:
>>I'm guessing you might need to COUNT the distinct values for each
>>column. If that is the case:
>>SELECT P.ZIP,
>> COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
>> COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
>>etc.
>>If that is not what you need please elaborate on what you actually
>>hope to see.
>>Note that since you were already doing a GROUP BY there was no purpose
>>to having a DISTINCT for the entire query.
>>Roy Harvey
>>Beacon Falls, CT
>>I am trying to count entries into each column heading by zip code, but
>>when I
>>run the script below I get the same count across all columns. Can someone
>>[quoted text clipped - 25 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip
> --
> Message posted via http://www.sqlmonster.com
>|||What Tom Cooper said. SHOW us what you have, and what you want.
Your original query counted all the rows for the zip code. All the
columns were the same because COUNT(columnname) returns a count of all
the rows with non-null values for that column, and apparently every
row was non-null. If there are 200 rows for a zip code, why would you
expect any number except 200 for day_care_center or drop_care?
Roy Harvey
Beacon Falls, CT
On Wed, 30 Jan 2008 16:56:33 GMT, "jpettigrew via SQLMonster.com"
<u7124@.uwe> wrote:
>I made the changes you suggested and it did make the counts unique but they
>are not correct by column heading/category. To check I displayed all the
>information for zip code 55901 under the column 'Day Care' for '07. I came up
>with a count of 200, but for this script I only get 11. How can I get all
>records to count?
>Thanks again for your help in advance
>Roy Harvey (SQL Server MVP) wrote:
>>I'm guessing you might need to COUNT the distinct values for each
>>column. If that is the case:
>>SELECT P.ZIP,
>> COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
>> COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
>>etc.
>>If that is not what you need please elaborate on what you actually
>>hope to see.
>>Note that since you were already doing a GROUP BY there was no purpose
>>to having a DISTINCT for the entire query.
>>Roy Harvey
>>Beacon Falls, CT
>>I am trying to count entries into each column heading by zip code, but when I
>>run the script below I get the same count across all columns. Can someone
>>[quoted text clipped - 25 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip|||Because of HIPAA compliance it is difficult to provide information contained
in one of the tables. How do you suggest I provide the information? If i was
not specific in my previous posts I apologize I am trying to total the
columns in the script by zip code, but do not seem to be getting all the
information.
Tom Cooper wrote:
>Usually the fastest way to get answers to questions like this is to give us
>the SQL statements to create sample tables and load those tables with sample
>data (see www.aspfaq.com/5006 for how to do this). Then tell us the result
>you would want to see from that sample data. That keeps us from having to
>guess what you want, and you will get an answer that has been tested against
>your sample data.
>Tom
>>I made the changes you suggested and it did make the counts unique but they
>> are not correct by column heading/category. To check I displayed all the
>[quoted text clipped - 28 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||On Wed, 30 Jan 2008 20:11:22 GMT, Jay via SQLMonster.com wrote:
>Because of HIPAA compliance it is difficult to provide information contained
>in one of the tables. How do you suggest I provide the information?
Hi Jay,
* Post the table structure as CREATE TABLE statements, including all
constraints, properties, and indexes.
* Post the data as INSERT statements. It doesn't need to be the real
data, a made-up sample that illustrates the problem is just as well
(probably even better, as there is no need to posts thousands or even
millions of rows when you can illustrate the problem with ten). I don't
think HIPAA disallows the posting of made-up data.
* Post the expected results.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jan 30, 9:49=A0pm, "Jay via SQLMonster.com" <u7124@.uwe> wrote:
> I am trying to count entries into each column heading by zip code, but whe=n I
> run the script below I get the same count across all columns. Can someone
> tell/show me how to make these unquie counts?
> Thanks in advance.
> SELECT =A0DISTINCT P.ZIP,
> =A0 =A0 =A0 =A0 COUNT (evC.day_care_center) AS 'Day Care',
> =A0 =A0 =A0 =A0 COUNT (evC.drop_care) AS 'Drop-In Care',
> =A0 =A0 =A0 =A0 COUNT (evC.family_day_care) AS 'Family day care',
> =A0 =A0 =A0 =A0 COUNT (evC.financial) AS 'Financial',
> =A0 =A0 =A0 =A0 COUNT (evC.montessori_program2)AS 'Montessori program',
> =A0 =A0 =A0 =A0 COUNT (evC.nanny_service) AS 'Nanny service',
> =A0 =A0 =A0 =A0 COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn= centers',
> =A0 =A0 =A0 =A0 COUNT (evC.parenting_info) AS 'Parenting information',
> =A0 =A0 =A0 =A0 COUNT (evC.sick_child_care) AS 'Sick child care',
> =A0 =A0 =A0 =A0 COUNT (evC.special_needs)AS 'Special needs',
> =A0 =A0 =A0 =A0 COUNT (evC.summer_camp_care)AS 'Summer camps/care',
> =A0 =A0 =A0 =A0 COUNT (evC.temporary) AS 'Temporary',
> =A0 =A0 =A0 =A0 COUNT (evC.transportation2)AS 'Transportation',
> =A0 =A0 =A0 =A0 COUNT (evC.support_groups) AS 'Support groups',
> =A0 =A0 =A0 =A0 COUNT (evC.other) AS 'Other'
> FROM =A0 =A0Patient_Elg pe
> =A0 =A0 =A0 =A0 INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
> =A0 =A0 =A0 =A0 INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D ev=C.Patient_Key
> WHERE =A0 =A0 (pe.Payor_Key =3D 59)
> =A0 =A0 =A0 =A0 AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
> GROUP BY P.Zip
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx=
/sql-server/200801/1
Hi Jay,
I think you need to do each count(distinct field) separately and store
them (maybe into a variable). For example:
Declare @.zip int, @.center int, etc
Select @.zip =3D select count(distinct p.zip)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D
evC.Patient_Key
WHERE (pe.Payor_Key =3D 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
Select @.center int =3D select count(distinct evC.day_care_center)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D
evC.Patient_Key
WHERE (pe.Payor_Key =3D 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
etc.
HTH|||On Wed, 30 Jan 2008 20:40:12 -0800 (PST), SB <othellomy@.yahoo.com>
wrote:
>I think you need to do each count(distinct field) separately and store
>them (maybe into a variable).
All at once or individually, if they are always grouped by the same
thing there will be no difference.
Roy Harvey
Beacon Falls, CT
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
Could this be run using OSQL script
If there a way to detect if backup fails? I like to add better error
handling... I need to be able to run using a script not thru the Sql
Scheduler.
DECLARE @.name VARCHAR(50) -- database name
DECLARE @.path VARCHAR(256) -- path for backup files
DECLARE @.fileName VARCHAR(256) -- filename for backup
DECLARE @.fileDate VARCHAR(20) -- used for file name
SET @.path = 'C:\Backup\'
SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
BACKUP DATABASE @.name TO DISK = @.fileName
FETCH NEXT FROM db_cursor INTO @.name
END
CLOSE db_cursor
DEALLOCATE db_cursorHi
You could test @.@.ERROR after performing the backup to see if the backup
command worked and store a list of failures, but some errors will abort the
batch and therefore subsequent databases will not be backed up. SQL2005 has
better error handling. The script can be run using OSQL with the -i flag if
you save the script to a file. The -b flag reports as a command line error
any error value so it could be trapped using ERRORLEVEL it will be 1 if one
of the backups failed. You may also want to use the -n flag to suppress line
numbers. If using SQL Agent you do not need to use a command prompt.
Your backups will append to any files that are already present with the
given name you may want to specify the INIT command if you want to overwrite
these.
John
"AHartman" wrote:
> If there a way to detect if backup fails? I like to add better error
> handling... I need to be able to run using a script not thru the Sql
> Scheduler.
> DECLARE @.name VARCHAR(50) -- database name
> DECLARE @.path VARCHAR(256) -- path for backup files
> DECLARE @.fileName VARCHAR(256) -- filename for backup
> DECLARE @.fileDate VARCHAR(20) -- used for file name
> SET @.path = 'C:\Backup\'
> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
> DECLARE db_cursor CURSOR FOR
> SELECT name
> FROM master.dbo.sysdatabases
> WHERE name NOT IN ('master','model','msdb','tempdb')
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
> BACKUP DATABASE @.name TO DISK = @.fileName
> FETCH NEXT FROM db_cursor INTO @.name
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||Thanks... for the info
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:45510EB6-452B-4597-8775-99F7FC501054@.microsoft.com...
> Hi
> You could test @.@.ERROR after performing the backup to see if the backup
> command worked and store a list of failures, but some errors will abort
> the
> batch and therefore subsequent databases will not be backed up. SQL2005
> has
> better error handling. The script can be run using OSQL with the -i flag
> if
> you save the script to a file. The -b flag reports as a command line error
> any error value so it could be trapped using ERRORLEVEL it will be 1 if
> one
> of the backups failed. You may also want to use the -n flag to suppress
> line
> numbers. If using SQL Agent you do not need to use a command prompt.
> Your backups will append to any files that are already present with the
> given name you may want to specify the INIT command if you want to
> overwrite
> these.
> John
> "AHartman" wrote:
>> If there a way to detect if backup fails? I like to add better error
>> handling... I need to be able to run using a script not thru the Sql
>> Scheduler.
>> DECLARE @.name VARCHAR(50) -- database name
>> DECLARE @.path VARCHAR(256) -- path for backup files
>> DECLARE @.fileName VARCHAR(256) -- filename for backup
>> DECLARE @.fileDate VARCHAR(20) -- used for file name
>> SET @.path = 'C:\Backup\'
>> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
>> DECLARE db_cursor CURSOR FOR
>> SELECT name
>> FROM master.dbo.sysdatabases
>> WHERE name NOT IN ('master','model','msdb','tempdb')
>> OPEN db_cursor
>> FETCH NEXT FROM db_cursor INTO @.name
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
>> BACKUP DATABASE @.name TO DISK = @.fileName
>> FETCH NEXT FROM db_cursor INTO @.name
>> END
>> CLOSE db_cursor
>> DEALLOCATE db_cursor
>>sql
handling... I need to be able to run using a script not thru the Sql
Scheduler.
DECLARE @.name VARCHAR(50) -- database name
DECLARE @.path VARCHAR(256) -- path for backup files
DECLARE @.fileName VARCHAR(256) -- filename for backup
DECLARE @.fileDate VARCHAR(20) -- used for file name
SET @.path = 'C:\Backup\'
SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
BACKUP DATABASE @.name TO DISK = @.fileName
FETCH NEXT FROM db_cursor INTO @.name
END
CLOSE db_cursor
DEALLOCATE db_cursorHi
You could test @.@.ERROR after performing the backup to see if the backup
command worked and store a list of failures, but some errors will abort the
batch and therefore subsequent databases will not be backed up. SQL2005 has
better error handling. The script can be run using OSQL with the -i flag if
you save the script to a file. The -b flag reports as a command line error
any error value so it could be trapped using ERRORLEVEL it will be 1 if one
of the backups failed. You may also want to use the -n flag to suppress line
numbers. If using SQL Agent you do not need to use a command prompt.
Your backups will append to any files that are already present with the
given name you may want to specify the INIT command if you want to overwrite
these.
John
"AHartman" wrote:
> If there a way to detect if backup fails? I like to add better error
> handling... I need to be able to run using a script not thru the Sql
> Scheduler.
> DECLARE @.name VARCHAR(50) -- database name
> DECLARE @.path VARCHAR(256) -- path for backup files
> DECLARE @.fileName VARCHAR(256) -- filename for backup
> DECLARE @.fileDate VARCHAR(20) -- used for file name
> SET @.path = 'C:\Backup\'
> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
> DECLARE db_cursor CURSOR FOR
> SELECT name
> FROM master.dbo.sysdatabases
> WHERE name NOT IN ('master','model','msdb','tempdb')
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
> BACKUP DATABASE @.name TO DISK = @.fileName
> FETCH NEXT FROM db_cursor INTO @.name
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||Thanks... for the info
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:45510EB6-452B-4597-8775-99F7FC501054@.microsoft.com...
> Hi
> You could test @.@.ERROR after performing the backup to see if the backup
> command worked and store a list of failures, but some errors will abort
> the
> batch and therefore subsequent databases will not be backed up. SQL2005
> has
> better error handling. The script can be run using OSQL with the -i flag
> if
> you save the script to a file. The -b flag reports as a command line error
> any error value so it could be trapped using ERRORLEVEL it will be 1 if
> one
> of the backups failed. You may also want to use the -n flag to suppress
> line
> numbers. If using SQL Agent you do not need to use a command prompt.
> Your backups will append to any files that are already present with the
> given name you may want to specify the INIT command if you want to
> overwrite
> these.
> John
> "AHartman" wrote:
>> If there a way to detect if backup fails? I like to add better error
>> handling... I need to be able to run using a script not thru the Sql
>> Scheduler.
>> DECLARE @.name VARCHAR(50) -- database name
>> DECLARE @.path VARCHAR(256) -- path for backup files
>> DECLARE @.fileName VARCHAR(256) -- filename for backup
>> DECLARE @.fileDate VARCHAR(20) -- used for file name
>> SET @.path = 'C:\Backup\'
>> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
>> DECLARE db_cursor CURSOR FOR
>> SELECT name
>> FROM master.dbo.sysdatabases
>> WHERE name NOT IN ('master','model','msdb','tempdb')
>> OPEN db_cursor
>> FETCH NEXT FROM db_cursor INTO @.name
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
>> BACKUP DATABASE @.name TO DISK = @.fileName
>> FETCH NEXT FROM db_cursor INTO @.name
>> END
>> CLOSE db_cursor
>> DEALLOCATE db_cursor
>>sql
Could Someone Help me to Config SQL2005 to Connect to my Host Database Server
Hi
I'm new this .I' using SQL 2005 VWD05.Could some body tell me how to upload to my host server database to to run a membership user account.I dont know what are the procedure to do on my pc in order to transfer the file on the database.
Thanks
I'm not sure what's your exact meaning, but if you want to connect to a remote SQL Server (that's a SQL Server on a different machine rather than the client website application machine), you need to configure the connection string used by the membership provider. For more information, you can refer to this article:
http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
And if you want to upload a existing database file which is being used by your website to the remote SQL Server, you'd better copy the database file to the local disk on the remote SQL machine, and then have the database file attached to the SQL intance.
|||I'm trying run a membership page using the createuserwizard.So how do you upload to the database on
the host server inorder to run the createusewizard to accept new account.
|||Lori could you give me a walk through example on how to create a blank instance?|||
bigmike40:
Lori could you give me a walk through example on how to create a blank instance?
ScottGu made clearly answer to this question on the link I post
The blank instance here just mean a new database without database objects required by membership management--this can be a newly created database or some existing database. So you can use Enterprise Manager/Management Studio to create a new database, and then use aspnet_regsql.exe to create database objects on the newly created database. If you want to use existing database on the remote SQL Server, you can move the database to the remote SQL Server using Backup/Restore or Detach/Attach. Here are some useful links for moving database:
Move database with Backup/Restore:http://msdn2.microsoft.com/en-us/library/ms190436.aspx
Move database using Detach/Attach:http://msdn2.microsoft.com/en-us/library/ms187858.aspx
|||Ok i have a question.Are you suppose to upload the data explorer files... in vwd in order for the createuserwizard to work, or just the app data folder|||Sorry I'm not familiar with what you said "upload data explorer files". However to use CreateUserWizard control you need to connect to an available SQL database. So if you want to use the existing database file on the remote SQL database, you'd better copy the database file to the local disk on the remote SQL machine so that the database file can be attached to the SQL instance.|||I'm sorry lori but i'm a newbei and some to the term i don't understand ,so can be a little more specifc on what u mean byif you want to use the existing database file on the remote SQL database, you'd better copy the database file to the local disk on the remote SQL machine so that the database file can be attached to the SQL instance.
|||OK, when you connect to a SQL database, first you need to login to the SQL instance (specified in Data Source property in connection string), right? And then you can use Database property to access an existing database on the SQL instance (for example pubs, tempdb); or use AttachDBFilename to attach a database and then access the attached database (note currently AttachDBFilename must points to a local database file, UNC, network path, HTTP are not supported), this is what I mean "using an existing database file". Apparently, no matter which database you're going to use, the database file must be on the local disk of the SQL machine, that's why I say "copy database file to the local disk on the SQL machine"
|||Would this help my CreateUserWizard to configure and create new user account ? Because i cannot
Get the CreateUserWizard to work on my host server whenever... i hit the submit button it give me an error
But it work fine on my local machine
thanks
|||What's the error you got? Have you configure the membership database connection according to theScottGu's article?|||
Iori i'd like to thank you for taking the time on helping me.For the past month i've trying to get my project to work, but so far no luck.I did try setting up VWD the way scott did , but for some reason when i ran the wizard PUBS, ASPNET, NORTWIND ISSUE TRACKER STARTERKIT, doesn't shows up in the drop down box ..But the others components are listed.I do belive that my problem is that my database is not settup properly. In order to settup my database, do i need to download any other software in order for all the missing componets to show up in the drop down box. I think u did explain it another thread that you did that precedure before. So can you tell me how you went about doing setting up the database.
Thanks you
|||
but for some reason when i ran the wizard PUBS, ASPNET, NORTWIND ISSUE TRACKER STARTERKIT, doesn't shows up in the drop down box ..But the others components are listed.I do belive that my problem is that my database is not settup properly.
Nevermind, it's my pleasure to share your issue Did you mean you can connect to the remote SQL Server in the aspnet_regsql.exe wizard, but some existing databases (PUBS, ASPNET, NORTWIND ISSUE TRACKER STARTERKIT) on the remote SQL instance don't show up? Then what's the meaning of "others components are listed"? Did you mean other databases on the remote SQL can be listed? If so, that's really strange, as I know there is no such database setting to control the listed databases in the aspnet_regsql wizard. Are you sure the permission setting of the account you're using is proper on the remote SQL instance? Can you connect to the remote SQL instance using this account?
I'm talking about the page where it say to select server and database on Scott blog.In the dropdown list he as
appservers,
ASPNET
Issue Tracker Kits
Master
model
msdb
Northwind
pubs
Those are the componets that are listed under database in Scott blogs
I did settup my config, but northwind, pubs, aspnet and the tracker kit didnt show up.
is it suppose to show up in the dropdown menu like how scott as it listed ?
And as far as can i connect to a remote SQL instance the answer is yes
On my host server i notice that there is nothing listed there in the database.
But my database name is listed in vwd and when hit test connection it said connected, so it seems that i can connect to myhost database, but i think that the info some how is not transfering over there some how.My project works
fine on my local machine but once i uploaded it on the server the CreateUserWizard doesn't work but all the other pages work fine. I belive that all the server control componets doesnt work on the server
I'm using WEB.com as my Host provider and the tech support don't seem to understaand anything about ASPNET 2.0
I think i might just get a better hosting company that don't out source their tech support
Thanks
|||After trying to follow Scott config i cannot get this screen.
Step 3: Point your web.config file at the new SQL Database
ASP.NET 2.0 now supports a new section in your web.config file called “<connectionStrings>” which (not too surprisingly) are used to store connection strings. One nice thing from an administration perspective is that the new ASP.NET Admin MMC Snap-in now provides a GUI based way to configure and manage these:
Could RS's data mining be used in a production setting?
I just have a simple question that can hopefully answer whether I should dive too deeply into Reporting/Analysis Services. I'm looking to run some clustering algorithms on user-inputted keywords on objects within my database (please see http://flickr.com/photos/tags/friends/clusters/ for an example of what I'm talking about). It seems to me that the various reporting methods that SQL Server provides would be an ideal, quick and easy way of providing this for my SQL Server 2005 database. I envision having some sort of job that would get the data from the reporting service daily or hourly and insert it into my main database.
Can the reporting services provide this type of functionality? And if so, would this be scalable? I would want to be able to access this clustered data in much the same way I do queries across my database and would want them to be done quickly and efficiently.
Thank you for your help.Can you clarify what you mean by "get the data from the reporting service"? In general, you could have a mining model and produce a report from it or you could run a report that creates a temporary (session) mining model.|||I'm not entirely sure on the concept myself. I was thinking that accessing the mined data from whatever form reporting service uses on a production site would be too slow, wouldn't scale. Rather, I'd like to migrate that data and transform it on some schedule to the main SQL Server database so that the queries would be quicker and scalable.|||
When you process a data mining structure, the discovered patterns are saved in an SSAS database (the same storage format as with a cube) so the data mining prediction queries should be fast. Nevertheless, I would suggest a POC to prove that your scalability requirements are met.
sqlCould not run the pull subscription
Indra,
try changing the owner of the job to sa.
HTH,
Paul Ibison
Hi paul, Do i have to run sql agent and sql server in boht computer as a
sa, it runing now but just for information.
Can i ask you some more informatio.
As I am going to install MSDE to our field user to whome i wanted to
give database so that they can work offline and when they want to
synchronize on demand. How i have to create the security premision, what
type to user account, window/sql mix or only window or only sql and what
type of owner ship they need so that they can replicate or synchronize
the data with our center server. Could you please give me some
information or the link where from i can read a bit.
Thanks a lot.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Indra,
in BOL have a look at replication, security. The subsections are agent login
security and Publication Access Lists. Your SQL Server Agent is the context
under which the replication jobs function. Typically the replication agent
is set to impersonate its login on the server it connects to, so if you want
a really simple setup, have the same domain user as the service user for sql
server and the agent services on your publisher/distributer and the
subscriber. If you want it to be more granular then the references in BOL
above will help.
The reason I mentioned setting the job's owner to sa is that although the
job runs as the agent, the replication process involves a verification that
the job owner is in active directory and setting the owner to sa avoids this
issue.
SQL Logins in replication are usually used for non-trusted domains.
HTH,
Paul Ibison
|||Thanks a lot Paul,
This is really a good information. I wanted to go for the simplest way
so that there will be no problem from the client site. Write now i am
testing as you mention with giving the dbowner permition, is it
necessary to give that permision or just data reader or data writer also
work in the case of repliction.
I need little bit more information, if you can help me. Acutally we are
using ACCESS database asd i am on the process to migrate (doing all the
testing, before actual migration).
I already migrated (for test) to sql database keeping front end as it is
ADP file. What i want to do is copy all ADP file to client m/c and
install local MSDE AND SYNCHRONIZE THROUGH MERGE REPLICATION ON DEMAND,
SO THAT USER CAN ALSO WORK WHERE THERE IN NO INTERNET ACCESS. TILL NOW
I have just converted to sql and trying to run merge on the one client,
but not yet tested for ADP file with new replicated data. Do you have
any idea or information regarding this type of project.
Thank you very much for help.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Indra,
For security, the role requirements are different for each agent. Have a
look in BOL for replication, security, Role Requirements.
For Access-SQL replication, have a look in BOL for these articles:
"Implementing Merge Replication to Access Subscribers"
"Data type mapping to Jet-SQL 4.0 for Merge Replication"
HTH,
Paul Ibison
try changing the owner of the job to sa.
HTH,
Paul Ibison
Hi paul, Do i have to run sql agent and sql server in boht computer as a
sa, it runing now but just for information.
Can i ask you some more informatio.
As I am going to install MSDE to our field user to whome i wanted to
give database so that they can work offline and when they want to
synchronize on demand. How i have to create the security premision, what
type to user account, window/sql mix or only window or only sql and what
type of owner ship they need so that they can replicate or synchronize
the data with our center server. Could you please give me some
information or the link where from i can read a bit.
Thanks a lot.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Indra,
in BOL have a look at replication, security. The subsections are agent login
security and Publication Access Lists. Your SQL Server Agent is the context
under which the replication jobs function. Typically the replication agent
is set to impersonate its login on the server it connects to, so if you want
a really simple setup, have the same domain user as the service user for sql
server and the agent services on your publisher/distributer and the
subscriber. If you want it to be more granular then the references in BOL
above will help.
The reason I mentioned setting the job's owner to sa is that although the
job runs as the agent, the replication process involves a verification that
the job owner is in active directory and setting the owner to sa avoids this
issue.
SQL Logins in replication are usually used for non-trusted domains.
HTH,
Paul Ibison
|||Thanks a lot Paul,
This is really a good information. I wanted to go for the simplest way
so that there will be no problem from the client site. Write now i am
testing as you mention with giving the dbowner permition, is it
necessary to give that permision or just data reader or data writer also
work in the case of repliction.
I need little bit more information, if you can help me. Acutally we are
using ACCESS database asd i am on the process to migrate (doing all the
testing, before actual migration).
I already migrated (for test) to sql database keeping front end as it is
ADP file. What i want to do is copy all ADP file to client m/c and
install local MSDE AND SYNCHRONIZE THROUGH MERGE REPLICATION ON DEMAND,
SO THAT USER CAN ALSO WORK WHERE THERE IN NO INTERNET ACCESS. TILL NOW
I have just converted to sql and trying to run merge on the one client,
but not yet tested for ADP file with new replicated data. Do you have
any idea or information regarding this type of project.
Thank you very much for help.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Indra,
For security, the role requirements are different for each agent. Have a
look in BOL for replication, security, Role Requirements.
For Access-SQL replication, have a look in BOL for these articles:
"Implementing Merge Replication to Access Subscribers"
"Data type mapping to Jet-SQL 4.0 for Merge Replication"
HTH,
Paul Ibison
Could not run BEGIN TRANSACTION in database whatever because the database is read-only.
I had a databases running on SQL Server (EVALUATION VERSION).
I moved it to another system running:
-Windows 2000 Server
-IIS 5.0
-BlueDragon Server
-MSDE
I applied all of the latest service packs, security updates, etc...
Intalled SQL Server Web Data Administrator and .NET framework.
I attached the database using sp_attach_db:
>osql U sa
>password
>Sp_attach_db whatever',
>@.filename1 = C:\Program Files\Microsoft SQL
Server\MSSQL\Data\CollaborationTools.mdf',
>@.filename2 = C:\Program Files\Microsoft SQL
Server\MSSQL\Data\CollaborationTools.ldf'
>go
I added my datasource using the ODBC Admin Tool...
Verified it using the BlueDragon Admin Datasources page...
But now I'm getting the error message:
'Could not run BEGIN TRANSACTION in database 'whatever' because the
database is read-only.'
Any help/guidance would be appreciated.
Thanks very much.
Shaunokay, thanks for the guidance...
here's what i did:
i went to the location of the .mdf and .ldf files and made sure the
read only attribute wasn't checked.
i then entered the following commands via the cmd prompt:
>osql U sa
>password
>USE master
>EXEC sp_dboption collaboration', read only', FALSE'
>go
I verified by viewing the database properties in the Web Data
Administrator. The Status of the Database now reads Normal' not
Standby'.
Thanks very much.
-shaun|||"SS" <stiznoit@.yahoo.com> wrote in message
news:dab7211.0404141325.6df43251@.posting.google.co m...
> But now I'm getting the error message:
> 'Could not run BEGIN TRANSACTION in database 'whatever' because the
> database is read-only.'
Unless I'm missing something, it's pretty obvious. For some reason database
"whatever" has the read-only flag set.
See db_options to reset.
And since of course since it's read only, there's no point in allowing a
transaction since you can't do anything in it anyway.
> Any help/guidance would be appreciated.
> Thanks very much.
> Shaun|||okay, thanks for the guidance...
here's what i did:
i went to the location of the .mdf and .ldf files and made sure the
read only attribute wasn't checked.
i then entered the following commands via the cmd prompt:
>osql U sa
>password
>USE master
>EXEC sp_dboption collaboration', read only', FALSE'
>go
I verified by viewing the database properties in the Web Data
Administrator. The Status of the Database now reads Normal' not
Standby'.
Thanks very much.
-shaun
Could not run BEGIN TRANSACTION in database whatever because the database is read-only.
I had a databases running on SQL Server (EVALUATION VERSION).
I moved it to another system running:
-Windows 2000 Server
-IIS 5.0
-BlueDragon Server
-MSDE
I applied all of the latest service packs, security updates, etc...
Intalled SQL Server Web Data Administrator and .NET framework.
I attached the database using sp_attach_db:
>osql U sa
>password
>Sp_attach_db whatever',
>@.filename1 = C:\Program Files\Microsoft SQL
Server\MSSQL\Data\CollaborationTools.mdf',
>@.filename2 = C:\Program Files\Microsoft SQL
Server\MSSQL\Data\CollaborationTools.ldf'
>go
I added my datasource using the ODBC Admin Tool...
Verified it using the BlueDragon Admin Datasources page...
But now I'm getting the error message:
'Could not run BEGIN TRANSACTION in database 'whatever' because the
database is read-only.'
Any help/guidance would be appreciated.
Thanks very much.
Shaun"SS" <stiznoit@.yahoo.com> wrote in message
news:dab7211.0404141325.6df43251@.posting.google.co m...
> But now I'm getting the error message:
> 'Could not run BEGIN TRANSACTION in database 'whatever' because the
> database is read-only.'
Unless I'm missing something, it's pretty obvious. For some reason database
"whatever" has the read-only flag set.
See db_options to reset.
And since of course since it's read only, there's no point in allowing a
transaction since you can't do anything in it anyway.
> Any help/guidance would be appreciated.
> Thanks very much.
> Shaunsql
Monday, March 19, 2012
Could not locate entry in sysdatabases for database 'MYSQL
Hi
I am trying to run a stored procedure on a linked MySQL server
i can select from the MySQL server, so i know that there is a valid
link
i can run the stored procedure on the MySQL server using the command
call triodent.UpdateDownloadTable
but when i try to execute
exec MYSQL.triodent.UpdateDownloadTable
i get the "could not locate..." error
is it because MySQL is using call rather than exec? if so doesn't
anyone have an idea for the syntax?
many Thanks
Ian
Try
exec MYSQL.triodent..UpdateDownloadTable
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<ian.tiesdell@.hotmail.co.uk> wrote in message
news:9f6c966a-b884-4efe-b037-2b339fb5d236@.q1g2000prf.googlegroups.com...
> Hi
> I am trying to run a stored procedure on a linked MySQL server
> i can select from the MySQL server, so i know that there is a valid
> link
> i can run the stored procedure on the MySQL server using the command
> call triodent.UpdateDownloadTable
> but when i try to execute
> exec MYSQL.triodent.UpdateDownloadTable
> i get the "could not locate..." error
> is it because MySQL is using call rather than exec? if so doesn't
> anyone have an idea for the syntax?
> many Thanks
> Ian
|||thanks jason but i get
Msg 7212, Level 17, State 1, Line 3
Could not execute procedure 'UpdateDownloadTable' on remote server
'MYSQL'.
:-(
|||That's progress. It sounds like it is permissions now. Verify the linked
server user has the rights to execute on MySQL.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<ian.tiesdell@.hotmail.co.uk> wrote in message
news:f098afcc-03ba-4977-9698-f38b83f34499@.b9g2000prh.googlegroups.com...
> thanks jason but i get
> Msg 7212, Level 17, State 1, Line 3
> Could not execute procedure 'UpdateDownloadTable' on remote server
> 'MYSQL'.
> :-(
|||jason wrote:
> That's progress. It sounds like it is permissions now. Verify the linked
> server user has the rights to execute on MySQL.
>
Also check the properties for the linked server and verify RPC is turned on.
|||sorry to be dim but how do i check this?
i've looked at server objects/linked servers security settings and i
have
connections will be made using this security context
remote login root
with password and then the password
RPC is turned on
Data Access is True
Ian
|||ian.tiesdell@.hotmail.co.uk wrote:
> sorry to be dim but how do i check this?
> i've looked at server objects/linked servers security settings and i
> have
> connections will be made using this security context
> remote login root
> with password and then the password
> RPC is turned on
> Data Access is True
> Ian
Well, if you looked at the linked server properties and saw the RPC is
enabled (there are two options), then that is all you need to do.
Jeff
|||so if the security settings are Ok and the syntax is OK?
what else could be wrong?
using
exec MYSQL.triodent..UpdateDownloadTable
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'UpdateDownloadTable' on remote server
'MYSQL'
|||ian.tiesdell@.hotmail.co.uk wrote:
> so if the security settings are Ok and the syntax is OK?
> what else could be wrong?
> using
> exec MYSQL.triodent..UpdateDownloadTable
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'UpdateDownloadTable' on remote server
> 'MYSQL'
Only thing I can think of is maybe permission issues in 'MYSQL'? I
don't know 'MYSQL' - so I really can't help with that side.
|||I think it could be mySQL causing the problem - what i've done is to
mimic the actions of the stored proc using update statements from
MSSQL over to the linked server
thanks for your input
Ian
I am trying to run a stored procedure on a linked MySQL server
i can select from the MySQL server, so i know that there is a valid
link
i can run the stored procedure on the MySQL server using the command
call triodent.UpdateDownloadTable
but when i try to execute
exec MYSQL.triodent.UpdateDownloadTable
i get the "could not locate..." error
is it because MySQL is using call rather than exec? if so doesn't
anyone have an idea for the syntax?
many Thanks
Ian
Try
exec MYSQL.triodent..UpdateDownloadTable
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<ian.tiesdell@.hotmail.co.uk> wrote in message
news:9f6c966a-b884-4efe-b037-2b339fb5d236@.q1g2000prf.googlegroups.com...
> Hi
> I am trying to run a stored procedure on a linked MySQL server
> i can select from the MySQL server, so i know that there is a valid
> link
> i can run the stored procedure on the MySQL server using the command
> call triodent.UpdateDownloadTable
> but when i try to execute
> exec MYSQL.triodent.UpdateDownloadTable
> i get the "could not locate..." error
> is it because MySQL is using call rather than exec? if so doesn't
> anyone have an idea for the syntax?
> many Thanks
> Ian
|||thanks jason but i get
Msg 7212, Level 17, State 1, Line 3
Could not execute procedure 'UpdateDownloadTable' on remote server
'MYSQL'.
:-(
|||That's progress. It sounds like it is permissions now. Verify the linked
server user has the rights to execute on MySQL.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<ian.tiesdell@.hotmail.co.uk> wrote in message
news:f098afcc-03ba-4977-9698-f38b83f34499@.b9g2000prh.googlegroups.com...
> thanks jason but i get
> Msg 7212, Level 17, State 1, Line 3
> Could not execute procedure 'UpdateDownloadTable' on remote server
> 'MYSQL'.
> :-(
|||jason wrote:
> That's progress. It sounds like it is permissions now. Verify the linked
> server user has the rights to execute on MySQL.
>
Also check the properties for the linked server and verify RPC is turned on.
|||sorry to be dim but how do i check this?
i've looked at server objects/linked servers security settings and i
have
connections will be made using this security context
remote login root
with password and then the password
RPC is turned on
Data Access is True
Ian
|||ian.tiesdell@.hotmail.co.uk wrote:
> sorry to be dim but how do i check this?
> i've looked at server objects/linked servers security settings and i
> have
> connections will be made using this security context
> remote login root
> with password and then the password
> RPC is turned on
> Data Access is True
> Ian
Well, if you looked at the linked server properties and saw the RPC is
enabled (there are two options), then that is all you need to do.
Jeff
|||so if the security settings are Ok and the syntax is OK?
what else could be wrong?
using
exec MYSQL.triodent..UpdateDownloadTable
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'UpdateDownloadTable' on remote server
'MYSQL'
|||ian.tiesdell@.hotmail.co.uk wrote:
> so if the security settings are Ok and the syntax is OK?
> what else could be wrong?
> using
> exec MYSQL.triodent..UpdateDownloadTable
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'UpdateDownloadTable' on remote server
> 'MYSQL'
Only thing I can think of is maybe permission issues in 'MYSQL'? I
don't know 'MYSQL' - so I really can't help with that side.
|||I think it could be mySQL causing the problem - what i've done is to
mimic the actions of the stored proc using update statements from
MSSQL over to the linked server
thanks for your input
Ian
Could not locate entry in sysdatabases for database 'MYSQL
Hi
I am trying to run a stored procedure on a linked MySQL server
i can select from the MySQL server, so i know that there is a valid
link
i can run the stored procedure on the MySQL server using the command
call triodent.UpdateDownloadTable
but when i try to execute
exec MYSQL.triodent.UpdateDownloadTable
i get the "could not locate..." error
is it because MySQL is using call rather than exec? if so doesn't
anyone have an idea for the syntax?
many Thanks
IanTry
exec MYSQL.triodent..UpdateDownloadTable
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<ian.tiesdell@.hotmail.co.uk> wrote in message
news:9f6c966a-b884-4efe-b037-2b339fb5d236@.q1g2000prf.googlegroups.com...
> Hi
> I am trying to run a stored procedure on a linked MySQL server
> i can select from the MySQL server, so i know that there is a valid
> link
> i can run the stored procedure on the MySQL server using the command
> call triodent.UpdateDownloadTable
> but when i try to execute
> exec MYSQL.triodent.UpdateDownloadTable
> i get the "could not locate..." error
> is it because MySQL is using call rather than exec? if so doesn't
> anyone have an idea for the syntax?
> many Thanks
> Ian|||thanks jason but i get
Msg 7212, Level 17, State 1, Line 3
Could not execute procedure 'UpdateDownloadTable' on remote server
'MYSQL'.
:-(|||That's progress. It sounds like it is permissions now. Verify the linked
server user has the rights to execute on MySQL.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<ian.tiesdell@.hotmail.co.uk> wrote in message
news:f098afcc-03ba-4977-9698-f38b83f34499@.b9g2000prh.googlegroups.com...
> thanks jason but i get
> Msg 7212, Level 17, State 1, Line 3
> Could not execute procedure 'UpdateDownloadTable' on remote server
> 'MYSQL'.
> :-(|||jason wrote:
> That's progress. It sounds like it is permissions now. Verify the linked
> server user has the rights to execute on MySQL.
>
Also check the properties for the linked server and verify RPC is turned on.|||sorry to be dim but how do i check this?
i've looked at server objects/linked servers security settings and i
have
connections will be made using this security context
remote login root
with password and then the password
RPC is turned on
Data Access is True
Ian|||ian.tiesdell@.hotmail.co.uk wrote:
> sorry to be dim but how do i check this?
> i've looked at server objects/linked servers security settings and i
> have
> connections will be made using this security context
> remote login root
> with password and then the password
> RPC is turned on
> Data Access is True
> Ian
Well, if you looked at the linked server properties and saw the RPC is
enabled (there are two options), then that is all you need to do.
Jeff|||so if the security settings are Ok and the syntax is OK?
what else could be wrong?
using
exec MYSQL.triodent..UpdateDownloadTable
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'UpdateDownloadTable' on remote server
'MYSQL'|||ian.tiesdell@.hotmail.co.uk wrote:
> so if the security settings are Ok and the syntax is OK?
> what else could be wrong?
> using
> exec MYSQL.triodent..UpdateDownloadTable
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'UpdateDownloadTable' on remote server
> 'MYSQL'
Only thing I can think of is maybe permission issues in 'MYSQL'? I
don't know 'MYSQL' - so I really can't help with that side.|||I think it could be mySQL causing the problem - what i've done is to
mimic the actions of the stored proc using update statements from
MSSQL over to the linked server
thanks for your input
Ian
I am trying to run a stored procedure on a linked MySQL server
i can select from the MySQL server, so i know that there is a valid
link
i can run the stored procedure on the MySQL server using the command
call triodent.UpdateDownloadTable
but when i try to execute
exec MYSQL.triodent.UpdateDownloadTable
i get the "could not locate..." error
is it because MySQL is using call rather than exec? if so doesn't
anyone have an idea for the syntax?
many Thanks
IanTry
exec MYSQL.triodent..UpdateDownloadTable
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<ian.tiesdell@.hotmail.co.uk> wrote in message
news:9f6c966a-b884-4efe-b037-2b339fb5d236@.q1g2000prf.googlegroups.com...
> Hi
> I am trying to run a stored procedure on a linked MySQL server
> i can select from the MySQL server, so i know that there is a valid
> link
> i can run the stored procedure on the MySQL server using the command
> call triodent.UpdateDownloadTable
> but when i try to execute
> exec MYSQL.triodent.UpdateDownloadTable
> i get the "could not locate..." error
> is it because MySQL is using call rather than exec? if so doesn't
> anyone have an idea for the syntax?
> many Thanks
> Ian|||thanks jason but i get
Msg 7212, Level 17, State 1, Line 3
Could not execute procedure 'UpdateDownloadTable' on remote server
'MYSQL'.
:-(|||That's progress. It sounds like it is permissions now. Verify the linked
server user has the rights to execute on MySQL.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<ian.tiesdell@.hotmail.co.uk> wrote in message
news:f098afcc-03ba-4977-9698-f38b83f34499@.b9g2000prh.googlegroups.com...
> thanks jason but i get
> Msg 7212, Level 17, State 1, Line 3
> Could not execute procedure 'UpdateDownloadTable' on remote server
> 'MYSQL'.
> :-(|||jason wrote:
> That's progress. It sounds like it is permissions now. Verify the linked
> server user has the rights to execute on MySQL.
>
Also check the properties for the linked server and verify RPC is turned on.|||sorry to be dim but how do i check this?
i've looked at server objects/linked servers security settings and i
have
connections will be made using this security context
remote login root
with password and then the password
RPC is turned on
Data Access is True
Ian|||ian.tiesdell@.hotmail.co.uk wrote:
> sorry to be dim but how do i check this?
> i've looked at server objects/linked servers security settings and i
> have
> connections will be made using this security context
> remote login root
> with password and then the password
> RPC is turned on
> Data Access is True
> Ian
Well, if you looked at the linked server properties and saw the RPC is
enabled (there are two options), then that is all you need to do.
Jeff|||so if the security settings are Ok and the syntax is OK?
what else could be wrong?
using
exec MYSQL.triodent..UpdateDownloadTable
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'UpdateDownloadTable' on remote server
'MYSQL'|||ian.tiesdell@.hotmail.co.uk wrote:
> so if the security settings are Ok and the syntax is OK?
> what else could be wrong?
> using
> exec MYSQL.triodent..UpdateDownloadTable
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'UpdateDownloadTable' on remote server
> 'MYSQL'
Only thing I can think of is maybe permission issues in 'MYSQL'? I
don't know 'MYSQL' - so I really can't help with that side.|||I think it could be mySQL causing the problem - what i've done is to
mimic the actions of the stored proc using update statements from
MSSQL over to the linked server
thanks for your input
Ian
Sunday, March 11, 2012
Could not find xp_sqlagent_proxy_account
Our system is windows NT SP6, SQL Server 7 SP4. I tried to run
xp_sqlagent_proxy_account, but here is what I got: Could not find stored
procedure 'master.dbo.xp_sqlagent_proxy_account'. I tried to reset proxy
account. It says successfully, but I cannot find any local account
"SQLAgentCmdExec" in user managers. Could you please help? Thanks!Hi
Have you tried changing this from the Services applet?
John
"Min" wrote:
> Our system is windows NT SP6, SQL Server 7 SP4. I tried to run
> xp_sqlagent_proxy_account, but here is what I got: Could not find stored
> procedure 'master.dbo.xp_sqlagent_proxy_account'. I tried to reset proxy
> account. It says successfully, but I cannot find any local account
> "SQLAgentCmdExec" in user managers. Could you please help? Thanks!|||When you say "change this", can you please explan what you are referring to?
We have a domain account for both SQL Server and Agent Services. It seems
that we have 2 diferent issues. 1. cannot find the extended SP. 2. cannot
find the local account SQLAgentCmdExec. Can you tell me where I can lookup
for the account from a windows NT server? The user manager is only for the
domain. I don't know where to look for the local account for the machine.
Thanks again!
"John Bell" wrote:
> Hi
> Have you tried changing this from the Services applet?
> John
> "Min" wrote:
> > Our system is windows NT SP6, SQL Server 7 SP4. I tried to run
> > xp_sqlagent_proxy_account, but here is what I got: Could not find stored
> > procedure 'master.dbo.xp_sqlagent_proxy_account'. I tried to reset proxy
> > account. It says successfully, but I cannot find any local account
> > "SQLAgentCmdExec" in user managers. Could you please help? Thanks!|||Hi
I don't have a SQL 7 system to check, but the if you can't find the stored
procedure in the master database using Enterprise Manager, then it was either
deleted for security or does not exist in that version.
The local users are accessed through the Server applet in control panel. The
services applet in control panel will allow you to select a different user
for the service to be run as.
John
"Min" wrote:
> When you say "change this", can you please explan what you are referring to?
> We have a domain account for both SQL Server and Agent Services. It seems
> that we have 2 diferent issues. 1. cannot find the extended SP. 2. cannot
> find the local account SQLAgentCmdExec. Can you tell me where I can lookup
> for the account from a windows NT server? The user manager is only for the
> domain. I don't know where to look for the local account for the machine.
> Thanks again!
> "John Bell" wrote:
> > Hi
> >
> > Have you tried changing this from the Services applet?
> >
> > John
> >
> > "Min" wrote:
> >
> > > Our system is windows NT SP6, SQL Server 7 SP4. I tried to run
> > > xp_sqlagent_proxy_account, but here is what I got: Could not find stored
> > > procedure 'master.dbo.xp_sqlagent_proxy_account'. I tried to reset proxy
> > > account. It says successfully, but I cannot find any local account
> > > "SQLAgentCmdExec" in user managers. Could you please help? Thanks!|||1. Could it be deleted by some service patches from EM? But I can still find
xpstar.dll on the drive (our SQL Server is STD edition SP4). If I donot have
the XP listed in EM, does it mean I cannot reset proxy account?
2. From server applet, I can only see connected users (resources), not the
local users to the machine. Could you give me more details on how to find the
place to check if SQLAgentCmdExec has been created as a local user?
Thanks again!
"John Bell" wrote:
> Hi
> I don't have a SQL 7 system to check, but the if you can't find the stored
> procedure in the master database using Enterprise Manager, then it was either
> deleted for security or does not exist in that version.
> The local users are accessed through the Server applet in control panel. The
> services applet in control panel will allow you to select a different user
> for the service to be run as.
> John|||Hi
xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
If the machine is NT workstation you can manage local users using
musrmgr.exe but this is not available on NT Server.
What account is the service running under in the services applet? Usually
the account would be a domain user with restricted privileges.
John
"Min" wrote:
> 1. Could it be deleted by some service patches from EM? But I can still find
> xpstar.dll on the drive (our SQL Server is STD edition SP4). If I donot have
> the XP listed in EM, does it mean I cannot reset proxy account?
> 2. From server applet, I can only see connected users (resources), not the
> local users to the machine. Could you give me more details on how to find the
> place to check if SQLAgentCmdExec has been created as a local user?
> Thanks again!
> "John Bell" wrote:
> > Hi
> >
> > I don't have a SQL 7 system to check, but the if you can't find the stored
> > procedure in the master database using Enterprise Manager, then it was either
> > deleted for security or does not exist in that version.
> >
> > The local users are accessed through the Server applet in control panel. The
> > services applet in control panel will allow you to select a different user
> > for the service to be run as.
> >
> > John|||So is it right for SQL Server 7, the only way to reset proxy account is thru
EM, not programatically?
Does it mean for NT Server, there is no way I can check to verify if the
local account SQLAgentCmeExec has been created? When I tried EM by clicking
the "reset proxy" account button in "Job System", it says "successfully" but
there is an error log entry for agent says "SQLAgentCmdExec password
verification failed, required client privilege not held".
We use a domain acount for both SQL and Agent services. It is in local
administrator group.
Thanks again!
"John Bell" wrote:
> Hi
> xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
> http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
> If the machine is NT workstation you can manage local users using
> musrmgr.exe but this is not available on NT Server.
> What account is the service running under in the services applet? Usually
> the account would be a domain user with restricted privileges.|||I am having the same issue with the "dbo.xp_Backup_Log" procedure. Is there a
way to get the syntax of these procedures and just create on of our own?|||Hi
See inline:
"Min" wrote:
> So is it right for SQL Server 7, the only way to reset proxy account is thru
> EM, not programatically?
Probably but I don't have a system to check this. There may be a way to
write your own process to do this, but whether this is a justified effort for
a version of the product that is so old, I don't know. You may want to trace
what happens when you use EM and see what if you can do the same.
> Does it mean for NT Server, there is no way I can check to verify if the
> local account SQLAgentCmeExec has been created? When I tried EM by clicking
> the "reset proxy" account button in "Job System", it says "successfully" but
> there is an error log entry for agent says "SQLAgentCmdExec password
> verification failed, required client privilege not held".
Is there anything in the Event log?
> We use a domain acount for both SQL and Agent services. It is in local
> administrator group.
You may also want to check:
http://support.microsoft.com/default.aspx?scid=kb;en-us;248391
http://support.microsoft.com/default.aspx?scid=kb;en-us;253107
John
> Thanks again!
> "John Bell" wrote:
> > Hi
> >
> > xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
> >
> > If the machine is NT workstation you can manage local users using
> > musrmgr.exe but this is not available on NT Server.
> >
> > What account is the service running under in the services applet? Usually
> > the account would be a domain user with restricted privileges.|||Hi
I think xp_Backup_Log may be a SQL Litespeed procedure!
John
"Sara C via SQLMonster.com" wrote:
> I am having the same issue with the "dbo.xp_Backup_Log" procedure. Is there a
> way to get the syntax of these procedures and just create on of our own?
>|||Thanks. I guess my question now is really:
How can I create a proxy account correctly? Thru EM, I unchecked the box,
clicked "reset ..." and message came back saying " successfully". But I am
still getting errors: 1314 from LogonUser when I run xp_cmdshell from a
non-admin account.
Also, there is no utility on NT server to verify the account.
"John Bell" wrote:
> Hi
> See inline:
> "Min" wrote:
> > So is it right for SQL Server 7, the only way to reset proxy account is thru
> > EM, not programatically?
> Probably but I don't have a system to check this. There may be a way to
> write your own process to do this, but whether this is a justified effort for
> a version of the product that is so old, I don't know. You may want to trace
> what happens when you use EM and see what if you can do the same.
> >
> > Does it mean for NT Server, there is no way I can check to verify if the
> > local account SQLAgentCmeExec has been created? When I tried EM by clicking
> > the "reset proxy" account button in "Job System", it says "successfully" but
> > there is an error log entry for agent says "SQLAgentCmdExec password
> > verification failed, required client privilege not held".
> Is there anything in the Event log?
> >
> > We use a domain acount for both SQL and Agent services. It is in local
> > administrator group.
> You may also want to check:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;248391
> http://support.microsoft.com/default.aspx?scid=kb;en-us;253107
> John
> >
> > Thanks again!
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
> > >
> > > If the machine is NT workstation you can manage local users using
> > > musrmgr.exe but this is not available on NT Server.
> > >
> > > What account is the service running under in the services applet? Usually
> > > the account would be a domain user with restricted privileges.|||Yes...use Enterprise Manager and the Reset Proxy on the Job
System tab in SQL Agent Properties. Refer to the following
for more info:
INF: Reset Proxy and the SQLAgentCmdExec Account
http://support.microsoft.com/?id=264155
But your issues are likely related to the service account,
not the proxy account. In regards to the 1314 error, that's
generally related to rights of the MSSQLServer service
account, *not* the proxy account. So it's that account that
is missing the rights of either/both Act as part of
operating system and increase quotas. You could also be
missing Replace process level token based on some of what
you posted earlier. I'm pretty sure there is a Knowledge
base article on this somewhere.
Sounds like you have some issues with the MSSQLServer
service account and that you may have changed it through the
services applet instead of Enterprise Manager. If you change
the service account through Enterprise Manager, the
permissions and rights will be set correctly.
-Sue
On Wed, 24 Aug 2005 11:45:03 -0700, Min
<Min@.discussions.microsoft.com> wrote:
>Thanks. I guess my question now is really:
>How can I create a proxy account correctly? Thru EM, I unchecked the box,
>clicked "reset ..." and message came back saying " successfully". But I am
>still getting errors: 1314 from LogonUser when I run xp_cmdshell from a
>non-admin account.
>Also, there is no utility on NT server to verify the account.
>"John Bell" wrote:
>> Hi
>> See inline:
>> "Min" wrote:
>> > So is it right for SQL Server 7, the only way to reset proxy account is thru
>> > EM, not programatically?
>> Probably but I don't have a system to check this. There may be a way to
>> write your own process to do this, but whether this is a justified effort for
>> a version of the product that is so old, I don't know. You may want to trace
>> what happens when you use EM and see what if you can do the same.
>> >
>> > Does it mean for NT Server, there is no way I can check to verify if the
>> > local account SQLAgentCmeExec has been created? When I tried EM by clicking
>> > the "reset proxy" account button in "Job System", it says "successfully" but
>> > there is an error log entry for agent says "SQLAgentCmdExec password
>> > verification failed, required client privilege not held".
>> Is there anything in the Event log?
>> >
>> > We use a domain acount for both SQL and Agent services. It is in local
>> > administrator group.
>> You may also want to check:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;248391
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;253107
>> John
>> >
>> > Thanks again!
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi
>> > >
>> > > xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
>> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
>> > >
>> > > If the machine is NT workstation you can manage local users using
>> > > musrmgr.exe but this is not available on NT Server.
>> > >
>> > > What account is the service running under in the services applet? Usually
>> > > the account would be a domain user with restricted privileges.|||Okay...and right after I posted, I found the other KB
article I mentioned that has more info on error 1314:
PRB: Error 1314 Raised By xp_cmdshell When Executed as
Non-SA User
http://support.microsoft.com/?id=248391
-Sue
On Wed, 24 Aug 2005 11:45:03 -0700, Min
<Min@.discussions.microsoft.com> wrote:
>Thanks. I guess my question now is really:
>How can I create a proxy account correctly? Thru EM, I unchecked the box,
>clicked "reset ..." and message came back saying " successfully". But I am
>still getting errors: 1314 from LogonUser when I run xp_cmdshell from a
>non-admin account.
>Also, there is no utility on NT server to verify the account.
>"John Bell" wrote:
>> Hi
>> See inline:
>> "Min" wrote:
>> > So is it right for SQL Server 7, the only way to reset proxy account is thru
>> > EM, not programatically?
>> Probably but I don't have a system to check this. There may be a way to
>> write your own process to do this, but whether this is a justified effort for
>> a version of the product that is so old, I don't know. You may want to trace
>> what happens when you use EM and see what if you can do the same.
>> >
>> > Does it mean for NT Server, there is no way I can check to verify if the
>> > local account SQLAgentCmeExec has been created? When I tried EM by clicking
>> > the "reset proxy" account button in "Job System", it says "successfully" but
>> > there is an error log entry for agent says "SQLAgentCmdExec password
>> > verification failed, required client privilege not held".
>> Is there anything in the Event log?
>> >
>> > We use a domain acount for both SQL and Agent services. It is in local
>> > administrator group.
>> You may also want to check:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;248391
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;253107
>> John
>> >
>> > Thanks again!
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi
>> > >
>> > > xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
>> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
>> > >
>> > > If the machine is NT workstation you can manage local users using
>> > > musrmgr.exe but this is not available on NT Server.
>> > >
>> > > What account is the service running under in the services applet? Usually
>> > > the account would be a domain user with restricted privileges.|||Thanks ... I have tried to follow both articles (actually almost every
article online that I can dig out). It is still not working even though EM
says "successfully". I decided to use another technology instead of
XP_cmdshell. My guess is that some kind of security patches blocked it.
"Sue Hoegemeier" wrote:
> Okay...and right after I posted, I found the other KB
> article I mentioned that has more info on error 1314:
> PRB: Error 1314 Raised By xp_cmdshell When Executed as
> Non-SA User
> http://support.microsoft.com/?id=248391
xp_sqlagent_proxy_account, but here is what I got: Could not find stored
procedure 'master.dbo.xp_sqlagent_proxy_account'. I tried to reset proxy
account. It says successfully, but I cannot find any local account
"SQLAgentCmdExec" in user managers. Could you please help? Thanks!Hi
Have you tried changing this from the Services applet?
John
"Min" wrote:
> Our system is windows NT SP6, SQL Server 7 SP4. I tried to run
> xp_sqlagent_proxy_account, but here is what I got: Could not find stored
> procedure 'master.dbo.xp_sqlagent_proxy_account'. I tried to reset proxy
> account. It says successfully, but I cannot find any local account
> "SQLAgentCmdExec" in user managers. Could you please help? Thanks!|||When you say "change this", can you please explan what you are referring to?
We have a domain account for both SQL Server and Agent Services. It seems
that we have 2 diferent issues. 1. cannot find the extended SP. 2. cannot
find the local account SQLAgentCmdExec. Can you tell me where I can lookup
for the account from a windows NT server? The user manager is only for the
domain. I don't know where to look for the local account for the machine.
Thanks again!
"John Bell" wrote:
> Hi
> Have you tried changing this from the Services applet?
> John
> "Min" wrote:
> > Our system is windows NT SP6, SQL Server 7 SP4. I tried to run
> > xp_sqlagent_proxy_account, but here is what I got: Could not find stored
> > procedure 'master.dbo.xp_sqlagent_proxy_account'. I tried to reset proxy
> > account. It says successfully, but I cannot find any local account
> > "SQLAgentCmdExec" in user managers. Could you please help? Thanks!|||Hi
I don't have a SQL 7 system to check, but the if you can't find the stored
procedure in the master database using Enterprise Manager, then it was either
deleted for security or does not exist in that version.
The local users are accessed through the Server applet in control panel. The
services applet in control panel will allow you to select a different user
for the service to be run as.
John
"Min" wrote:
> When you say "change this", can you please explan what you are referring to?
> We have a domain account for both SQL Server and Agent Services. It seems
> that we have 2 diferent issues. 1. cannot find the extended SP. 2. cannot
> find the local account SQLAgentCmdExec. Can you tell me where I can lookup
> for the account from a windows NT server? The user manager is only for the
> domain. I don't know where to look for the local account for the machine.
> Thanks again!
> "John Bell" wrote:
> > Hi
> >
> > Have you tried changing this from the Services applet?
> >
> > John
> >
> > "Min" wrote:
> >
> > > Our system is windows NT SP6, SQL Server 7 SP4. I tried to run
> > > xp_sqlagent_proxy_account, but here is what I got: Could not find stored
> > > procedure 'master.dbo.xp_sqlagent_proxy_account'. I tried to reset proxy
> > > account. It says successfully, but I cannot find any local account
> > > "SQLAgentCmdExec" in user managers. Could you please help? Thanks!|||1. Could it be deleted by some service patches from EM? But I can still find
xpstar.dll on the drive (our SQL Server is STD edition SP4). If I donot have
the XP listed in EM, does it mean I cannot reset proxy account?
2. From server applet, I can only see connected users (resources), not the
local users to the machine. Could you give me more details on how to find the
place to check if SQLAgentCmdExec has been created as a local user?
Thanks again!
"John Bell" wrote:
> Hi
> I don't have a SQL 7 system to check, but the if you can't find the stored
> procedure in the master database using Enterprise Manager, then it was either
> deleted for security or does not exist in that version.
> The local users are accessed through the Server applet in control panel. The
> services applet in control panel will allow you to select a different user
> for the service to be run as.
> John|||Hi
xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
If the machine is NT workstation you can manage local users using
musrmgr.exe but this is not available on NT Server.
What account is the service running under in the services applet? Usually
the account would be a domain user with restricted privileges.
John
"Min" wrote:
> 1. Could it be deleted by some service patches from EM? But I can still find
> xpstar.dll on the drive (our SQL Server is STD edition SP4). If I donot have
> the XP listed in EM, does it mean I cannot reset proxy account?
> 2. From server applet, I can only see connected users (resources), not the
> local users to the machine. Could you give me more details on how to find the
> place to check if SQLAgentCmdExec has been created as a local user?
> Thanks again!
> "John Bell" wrote:
> > Hi
> >
> > I don't have a SQL 7 system to check, but the if you can't find the stored
> > procedure in the master database using Enterprise Manager, then it was either
> > deleted for security or does not exist in that version.
> >
> > The local users are accessed through the Server applet in control panel. The
> > services applet in control panel will allow you to select a different user
> > for the service to be run as.
> >
> > John|||So is it right for SQL Server 7, the only way to reset proxy account is thru
EM, not programatically?
Does it mean for NT Server, there is no way I can check to verify if the
local account SQLAgentCmeExec has been created? When I tried EM by clicking
the "reset proxy" account button in "Job System", it says "successfully" but
there is an error log entry for agent says "SQLAgentCmdExec password
verification failed, required client privilege not held".
We use a domain acount for both SQL and Agent services. It is in local
administrator group.
Thanks again!
"John Bell" wrote:
> Hi
> xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
> http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
> If the machine is NT workstation you can manage local users using
> musrmgr.exe but this is not available on NT Server.
> What account is the service running under in the services applet? Usually
> the account would be a domain user with restricted privileges.|||I am having the same issue with the "dbo.xp_Backup_Log" procedure. Is there a
way to get the syntax of these procedures and just create on of our own?|||Hi
See inline:
"Min" wrote:
> So is it right for SQL Server 7, the only way to reset proxy account is thru
> EM, not programatically?
Probably but I don't have a system to check this. There may be a way to
write your own process to do this, but whether this is a justified effort for
a version of the product that is so old, I don't know. You may want to trace
what happens when you use EM and see what if you can do the same.
> Does it mean for NT Server, there is no way I can check to verify if the
> local account SQLAgentCmeExec has been created? When I tried EM by clicking
> the "reset proxy" account button in "Job System", it says "successfully" but
> there is an error log entry for agent says "SQLAgentCmdExec password
> verification failed, required client privilege not held".
Is there anything in the Event log?
> We use a domain acount for both SQL and Agent services. It is in local
> administrator group.
You may also want to check:
http://support.microsoft.com/default.aspx?scid=kb;en-us;248391
http://support.microsoft.com/default.aspx?scid=kb;en-us;253107
John
> Thanks again!
> "John Bell" wrote:
> > Hi
> >
> > xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
> >
> > If the machine is NT workstation you can manage local users using
> > musrmgr.exe but this is not available on NT Server.
> >
> > What account is the service running under in the services applet? Usually
> > the account would be a domain user with restricted privileges.|||Hi
I think xp_Backup_Log may be a SQL Litespeed procedure!
John
"Sara C via SQLMonster.com" wrote:
> I am having the same issue with the "dbo.xp_Backup_Log" procedure. Is there a
> way to get the syntax of these procedures and just create on of our own?
>|||Thanks. I guess my question now is really:
How can I create a proxy account correctly? Thru EM, I unchecked the box,
clicked "reset ..." and message came back saying " successfully". But I am
still getting errors: 1314 from LogonUser when I run xp_cmdshell from a
non-admin account.
Also, there is no utility on NT server to verify the account.
"John Bell" wrote:
> Hi
> See inline:
> "Min" wrote:
> > So is it right for SQL Server 7, the only way to reset proxy account is thru
> > EM, not programatically?
> Probably but I don't have a system to check this. There may be a way to
> write your own process to do this, but whether this is a justified effort for
> a version of the product that is so old, I don't know. You may want to trace
> what happens when you use EM and see what if you can do the same.
> >
> > Does it mean for NT Server, there is no way I can check to verify if the
> > local account SQLAgentCmeExec has been created? When I tried EM by clicking
> > the "reset proxy" account button in "Job System", it says "successfully" but
> > there is an error log entry for agent says "SQLAgentCmdExec password
> > verification failed, required client privilege not held".
> Is there anything in the Event log?
> >
> > We use a domain acount for both SQL and Agent services. It is in local
> > administrator group.
> You may also want to check:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;248391
> http://support.microsoft.com/default.aspx?scid=kb;en-us;253107
> John
> >
> > Thanks again!
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
> > >
> > > If the machine is NT workstation you can manage local users using
> > > musrmgr.exe but this is not available on NT Server.
> > >
> > > What account is the service running under in the services applet? Usually
> > > the account would be a domain user with restricted privileges.|||Yes...use Enterprise Manager and the Reset Proxy on the Job
System tab in SQL Agent Properties. Refer to the following
for more info:
INF: Reset Proxy and the SQLAgentCmdExec Account
http://support.microsoft.com/?id=264155
But your issues are likely related to the service account,
not the proxy account. In regards to the 1314 error, that's
generally related to rights of the MSSQLServer service
account, *not* the proxy account. So it's that account that
is missing the rights of either/both Act as part of
operating system and increase quotas. You could also be
missing Replace process level token based on some of what
you posted earlier. I'm pretty sure there is a Knowledge
base article on this somewhere.
Sounds like you have some issues with the MSSQLServer
service account and that you may have changed it through the
services applet instead of Enterprise Manager. If you change
the service account through Enterprise Manager, the
permissions and rights will be set correctly.
-Sue
On Wed, 24 Aug 2005 11:45:03 -0700, Min
<Min@.discussions.microsoft.com> wrote:
>Thanks. I guess my question now is really:
>How can I create a proxy account correctly? Thru EM, I unchecked the box,
>clicked "reset ..." and message came back saying " successfully". But I am
>still getting errors: 1314 from LogonUser when I run xp_cmdshell from a
>non-admin account.
>Also, there is no utility on NT server to verify the account.
>"John Bell" wrote:
>> Hi
>> See inline:
>> "Min" wrote:
>> > So is it right for SQL Server 7, the only way to reset proxy account is thru
>> > EM, not programatically?
>> Probably but I don't have a system to check this. There may be a way to
>> write your own process to do this, but whether this is a justified effort for
>> a version of the product that is so old, I don't know. You may want to trace
>> what happens when you use EM and see what if you can do the same.
>> >
>> > Does it mean for NT Server, there is no way I can check to verify if the
>> > local account SQLAgentCmeExec has been created? When I tried EM by clicking
>> > the "reset proxy" account button in "Job System", it says "successfully" but
>> > there is an error log entry for agent says "SQLAgentCmdExec password
>> > verification failed, required client privilege not held".
>> Is there anything in the Event log?
>> >
>> > We use a domain acount for both SQL and Agent services. It is in local
>> > administrator group.
>> You may also want to check:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;248391
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;253107
>> John
>> >
>> > Thanks again!
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi
>> > >
>> > > xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
>> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
>> > >
>> > > If the machine is NT workstation you can manage local users using
>> > > musrmgr.exe but this is not available on NT Server.
>> > >
>> > > What account is the service running under in the services applet? Usually
>> > > the account would be a domain user with restricted privileges.|||Okay...and right after I posted, I found the other KB
article I mentioned that has more info on error 1314:
PRB: Error 1314 Raised By xp_cmdshell When Executed as
Non-SA User
http://support.microsoft.com/?id=248391
-Sue
On Wed, 24 Aug 2005 11:45:03 -0700, Min
<Min@.discussions.microsoft.com> wrote:
>Thanks. I guess my question now is really:
>How can I create a proxy account correctly? Thru EM, I unchecked the box,
>clicked "reset ..." and message came back saying " successfully". But I am
>still getting errors: 1314 from LogonUser when I run xp_cmdshell from a
>non-admin account.
>Also, there is no utility on NT server to verify the account.
>"John Bell" wrote:
>> Hi
>> See inline:
>> "Min" wrote:
>> > So is it right for SQL Server 7, the only way to reset proxy account is thru
>> > EM, not programatically?
>> Probably but I don't have a system to check this. There may be a way to
>> write your own process to do this, but whether this is a justified effort for
>> a version of the product that is so old, I don't know. You may want to trace
>> what happens when you use EM and see what if you can do the same.
>> >
>> > Does it mean for NT Server, there is no way I can check to verify if the
>> > local account SQLAgentCmeExec has been created? When I tried EM by clicking
>> > the "reset proxy" account button in "Job System", it says "successfully" but
>> > there is an error log entry for agent says "SQLAgentCmdExec password
>> > verification failed, required client privilege not held".
>> Is there anything in the Event log?
>> >
>> > We use a domain acount for both SQL and Agent services. It is in local
>> > administrator group.
>> You may also want to check:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;248391
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;253107
>> John
>> >
>> > Thanks again!
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi
>> > >
>> > > xp_sqlagent_proxy_account was intoduce in sp3 or SQL 2000 see
>> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;889551
>> > >
>> > > If the machine is NT workstation you can manage local users using
>> > > musrmgr.exe but this is not available on NT Server.
>> > >
>> > > What account is the service running under in the services applet? Usually
>> > > the account would be a domain user with restricted privileges.|||Thanks ... I have tried to follow both articles (actually almost every
article online that I can dig out). It is still not working even though EM
says "successfully". I decided to use another technology instead of
XP_cmdshell. My guess is that some kind of security patches blocked it.
"Sue Hoegemeier" wrote:
> Okay...and right after I posted, I found the other KB
> article I mentioned that has more info on error 1314:
> PRB: Error 1314 Raised By xp_cmdshell When Executed as
> Non-SA User
> http://support.microsoft.com/?id=248391
Thursday, March 8, 2012
Could not find the index entry for RID
WHen I run a simple query against a table I get the
following error:
Could not find the index entry for
RID '163939393034313038342020202020205041313130332020202020
203030333431332020202036000500' in index page (1:65918),
index ID 0, database 'MYDATABASE'."
What's the reason?|
| WHen I run a simple query against a table I get the
| following error:
|
| Could not find the index entry for
| RID '163939393034313038342020202020205041313130332020202020
| 203030333431332020202036000500' in index page (1:65918),
| index ID 0, database 'MYDATABASE'."
--
This looks like data corruption. Run DBCC CHECKDB on the database and see
how you go. If CHECKDB won't fix corruption, restore from your latest good
backup.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Hi Don
You've asked this in two separate newsgroups and started two threads of
answers.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"I_AM_DON_AND_YOU" <anonymous@.discussions.microsoft.com> wrote in message
news:1467a01c3c349$12455b30$a601280a@.phx.gbl...
> WHen I run a simple query against a table I get the
> following error:
> Could not find the index entry for
> RID '163939393034313038342020202020205041313130332020202020
> 203030333431332020202036000500' in index page (1:65918),
> index ID 0, database 'MYDATABASE'."
>
> What's the reason?
>
following error:
Could not find the index entry for
RID '163939393034313038342020202020205041313130332020202020
203030333431332020202036000500' in index page (1:65918),
index ID 0, database 'MYDATABASE'."
What's the reason?|
| WHen I run a simple query against a table I get the
| following error:
|
| Could not find the index entry for
| RID '163939393034313038342020202020205041313130332020202020
| 203030333431332020202036000500' in index page (1:65918),
| index ID 0, database 'MYDATABASE'."
--
This looks like data corruption. Run DBCC CHECKDB on the database and see
how you go. If CHECKDB won't fix corruption, restore from your latest good
backup.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Hi Don
You've asked this in two separate newsgroups and started two threads of
answers.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"I_AM_DON_AND_YOU" <anonymous@.discussions.microsoft.com> wrote in message
news:1467a01c3c349$12455b30$a601280a@.phx.gbl...
> WHen I run a simple query against a table I get the
> following error:
> Could not find the index entry for
> RID '163939393034313038342020202020205041313130332020202020
> 203030333431332020202036000500' in index page (1:65918),
> index ID 0, database 'MYDATABASE'."
>
> What's the reason?
>
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.
>
could not find table error
Using SS2000 SP4.
When I run DBCC SHOWCONTIG (ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES I
get an error "Server: Msg 2501, Level 16, State 45, Line 1
Could not find a table or object named 'ActionsNeeded'. Check sysobjects."
All objects in the database are owned by "memsym". If I try "DBCC SHOWCONTIG
(memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
"Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'."
I have a script that checks index fragmentation and it uses the above
command. I get the same error on all tables in the database. Any idea why and
how to fix it?
The tables do exist and I can select from them.
Thanks,
--
Dan D.> All objects in the database are owned by "memsym". If I try "DBCC
> SHOWCONTIG
> (memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
> "Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'."
Try enclosing the object name in quotes:
DBCC SHOWCONTIG ('memsym.ActionsNeeded')
WITH TABLERESULTS, ALL_INDEXES
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:AEC3EE22-C7D7-4E81-A29D-36C5B698B241@.microsoft.com...
> Using SS2000 SP4.
> When I run DBCC SHOWCONTIG (ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES
> I
> get an error "Server: Msg 2501, Level 16, State 45, Line 1
> Could not find a table or object named 'ActionsNeeded'. Check sysobjects."
> All objects in the database are owned by "memsym". If I try "DBCC
> SHOWCONTIG
> (memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
> "Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'."
> I have a script that checks index fragmentation and it uses the above
> command. I get the same error on all tables in the database. Any idea why
> and
> how to fix it?
> The tables do exist and I can select from them.
> Thanks,
> --
> Dan D.|||I guess I wasn't thinking this morning. That was too easy.
Thanks,
--
Dan D.
"Dan Guzman" wrote:
> > All objects in the database are owned by "memsym". If I try "DBCC
> > SHOWCONTIG
> > (memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
> > "Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '.'."
> Try enclosing the object name in quotes:
> DBCC SHOWCONTIG ('memsym.ActionsNeeded')
> WITH TABLERESULTS, ALL_INDEXES
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:AEC3EE22-C7D7-4E81-A29D-36C5B698B241@.microsoft.com...
> > Using SS2000 SP4.
> >
> > When I run DBCC SHOWCONTIG (ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES
> > I
> > get an error "Server: Msg 2501, Level 16, State 45, Line 1
> > Could not find a table or object named 'ActionsNeeded'. Check sysobjects."
> >
> > All objects in the database are owned by "memsym". If I try "DBCC
> > SHOWCONTIG
> > (memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
> > "Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '.'."
> >
> > I have a script that checks index fragmentation and it uses the above
> > command. I get the same error on all tables in the database. Any idea why
> > and
> > how to fix it?
> > The tables do exist and I can select from them.
> >
> > Thanks,
> > --
> > Dan D.
>
When I run DBCC SHOWCONTIG (ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES I
get an error "Server: Msg 2501, Level 16, State 45, Line 1
Could not find a table or object named 'ActionsNeeded'. Check sysobjects."
All objects in the database are owned by "memsym". If I try "DBCC SHOWCONTIG
(memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
"Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'."
I have a script that checks index fragmentation and it uses the above
command. I get the same error on all tables in the database. Any idea why and
how to fix it?
The tables do exist and I can select from them.
Thanks,
--
Dan D.> All objects in the database are owned by "memsym". If I try "DBCC
> SHOWCONTIG
> (memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
> "Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'."
Try enclosing the object name in quotes:
DBCC SHOWCONTIG ('memsym.ActionsNeeded')
WITH TABLERESULTS, ALL_INDEXES
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:AEC3EE22-C7D7-4E81-A29D-36C5B698B241@.microsoft.com...
> Using SS2000 SP4.
> When I run DBCC SHOWCONTIG (ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES
> I
> get an error "Server: Msg 2501, Level 16, State 45, Line 1
> Could not find a table or object named 'ActionsNeeded'. Check sysobjects."
> All objects in the database are owned by "memsym". If I try "DBCC
> SHOWCONTIG
> (memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
> "Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'."
> I have a script that checks index fragmentation and it uses the above
> command. I get the same error on all tables in the database. Any idea why
> and
> how to fix it?
> The tables do exist and I can select from them.
> Thanks,
> --
> Dan D.|||I guess I wasn't thinking this morning. That was too easy.
Thanks,
--
Dan D.
"Dan Guzman" wrote:
> > All objects in the database are owned by "memsym". If I try "DBCC
> > SHOWCONTIG
> > (memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
> > "Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '.'."
> Try enclosing the object name in quotes:
> DBCC SHOWCONTIG ('memsym.ActionsNeeded')
> WITH TABLERESULTS, ALL_INDEXES
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:AEC3EE22-C7D7-4E81-A29D-36C5B698B241@.microsoft.com...
> > Using SS2000 SP4.
> >
> > When I run DBCC SHOWCONTIG (ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES
> > I
> > get an error "Server: Msg 2501, Level 16, State 45, Line 1
> > Could not find a table or object named 'ActionsNeeded'. Check sysobjects."
> >
> > All objects in the database are owned by "memsym". If I try "DBCC
> > SHOWCONTIG
> > (memsym.ActionsNeeded) WITH TABLERESULTS, ALL_INDEXES" I get this error -
> > "Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '.'."
> >
> > I have a script that checks index fragmentation and it uses the above
> > command. I get the same error on all tables in the database. Any idea why
> > and
> > how to fix it?
> > The tables do exist and I can select from them.
> >
> > Thanks,
> > --
> > Dan D.
>
Could not find stored procedure GetActivePoll?
can anyone please help me with my poll application? whenever i run it, i will comes out with this error "Could not find stored procedure 'GetActivePoll'". i've got stored procedure in my database with the name GetActivePoll', how come it cannot find the stored procedured? below are some images and codes i've attached with.
1<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="poll.aspx.vb"Inherits="Polls_poll" title="Fanzine if Liverpool FC" %>2<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">3<div style="text-align: left">4 <span style="font-size: 30px; color: #3333ff; font-family: Verdana"><strong><span5 style="color: #000000">Please take a vote...</span><br />6 </strong></span>7 </div>8 <div style="text-align: left">9 <br />10 <table width="100%" align="center">11 <tr>12 <td style="width: 100px; border-top: thin solid; height: 20px;">13 <asp:Label ID="lblPollQuestion" runat="server" Font-Bold="True" Font-Names="Verdana"14 Font-Size="10pt" Text="Poll Question" Width="500px"></asp:Label></td>15 </tr>16 <tr>17 <td style="width: 100px">18 <asp:RadioButtonList ID="rdoPollOptionList" runat="server" Font-Names="Verdana" Font-Size="9pt" Width="500px" DataSourceID="SqlDataSource1" DataTextField="PK_PollId" DataValueField="PK_PollId">19 </asp:RadioButtonList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Poll.mdf;Integrated Security=True;User Instance=True"20 ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Polls]"></asp:SqlDataSource>21 </td>22 </tr>23 <tr>24 <td style="width: 100px; text-align: left">25 <asp:Button ID="btnVote" runat="server" Text="Vote" Width="71px" BackColor="Silver" BorderColor="Silver" BorderStyle="Solid" Font-Bold="True" ForeColor="White" /><br />26 <br />27 <asp:Label ID="lblError" runat="server" Font-Names="Verdana" Font-Size="Smaller"28 ForeColor="Red" Text="You cannot vote more than once..." Visible="False" Width="500px"></asp:Label></td>29 </tr>30 </table>31 </div>32</asp:Content>33
1Imports System.Data2Imports System.Data.SqlClient3PartialClass Polls_poll4Inherits System.Web.UI.Page5Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load6If Not IsPostBackThen7 DisplayPoll()8End If9 End Sub10 Private Sub DisplayPoll()11Try12 Dim dsAs DataSet = getActivePoll()1314 lblPollQuestion.Text = ds.Tables(0).Rows(0)("Question")1516Dim iAs Integer = 017For Each drAs DataRowIn ds.Tables(1).Rows18 rdoPollOptionList.Items.Add(dr("Answer"))19 rdoPollOptionList.Items(i).Value = dr("PK_OptionId")20 rdoPollOptionList.SelectedIndex = 02122 i = i + 123Next24 Catch exAs Exception25Throw ex26End Try27 End Sub28 Private Function getActivePoll()As DataSet29Dim strConnStringAs String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()30Dim sqlConnAs New SqlConnection(strConnString)3132 sqlConn.Open()33Dim sqlCmdAs New SqlCommand()3435 sqlCmd.CommandText ="GetActivePoll"36 sqlCmd.CommandType = Data.CommandType.StoredProcedure37 sqlCmd.Connection = sqlConn3839Dim dsAs New DataSet40Dim daAs New SqlDataAdapter(sqlCmd)4142 da.Fill(ds)4344 sqlConn.Close()4546Return ds47End Function48 Protected Sub btnVote_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnVote.Click49If Response.Cookies("Voted")Is Nothing Then50 Response.Cookies("Voted").Value ="Voted"51 Response.Cookies("Voted").Expires = DateTime.Now.AddDays(1)5253 lblError.Visible =False5455 RecordVote()56Else57 lblError.Visible =True58 End If59 End Sub60 Private Sub RecordVote()61Dim strConnStringAs String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()62Dim sqlConnAs New SqlConnection(strConnString)6364 sqlConn.Open()65Dim sqlCmdAs New SqlCommand()6667 sqlCmd.CommandText ="IncrementVote"68 sqlCmd.CommandType = Data.CommandType.StoredProcedure69 sqlCmd.Connection = sqlConn7071Dim sqlParamQuestionAs New SqlParameter("@.i_OptionId", Data.SqlDbType.Int)7273 sqlParamQuestion.Value = rdoPollOptionList.SelectedValue7475 sqlCmd.Parameters.Add(sqlParamQuestion)7677 sqlCmd.ExecuteNonQuery()7879 sqlConn.Close()80End Sub81End Class 
If you change line 35 to
sqlCmd.CommandText ="[GetActivePoll]"
does it work?
|||Are you looking in the right DB? Its a good idea to drop and recreate the proc and qualify with the owner as
CREATE PROCdbo.GetAcviePoll
Lot of times when developoers create the objects, if they are not created with the qualifier they are created under the developers credentials.
Wednesday, March 7, 2012
Could not find stored procedure
Hi -
I've been advised to run a few scripts located in my SQL Install directory
(messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure how.
When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
find stored procedure' error. I'm using the master database, but these
procedures are not in that (or any other) database. How can I run them?
Thanks for your help.
- Jeff
Jeff,
You need to open the scripts to run them. File->Open, just like with any
other text editor, then you can run whatever's in the files.
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthli nk.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them?
> Thanks for your help.
> - Jeff
>
|||messages.sql is not something you can run directly from Query Analyzer. Go
out to the command line and run osql ... see Books Online for information,
but it might look something like this:
osql -E -S (local) -i c:\path\messages.sql
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthli nk.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
> how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them?
> Thanks for your help.
> - Jeff
>
|||Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Thanks, guys -
I knew it had to be something simple!
- Jeff
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OsV9d0lHEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
I've been advised to run a few scripts located in my SQL Install directory
(messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure how.
When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
find stored procedure' error. I'm using the master database, but these
procedures are not in that (or any other) database. How can I run them?
Thanks for your help.
- Jeff
Jeff,
You need to open the scripts to run them. File->Open, just like with any
other text editor, then you can run whatever's in the files.
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthli nk.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them?
> Thanks for your help.
> - Jeff
>
|||messages.sql is not something you can run directly from Query Analyzer. Go
out to the command line and run osql ... see Books Online for information,
but it might look something like this:
osql -E -S (local) -i c:\path\messages.sql
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthli nk.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
> how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them?
> Thanks for your help.
> - Jeff
>
|||Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Thanks, guys -
I knew it had to be something simple!
- Jeff
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OsV9d0lHEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
Could not find stored procedure
Hi -
I've been advised to run a few scripts located in my SQL Install directory
(messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure how.
When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
find stored procedure' error. I'm using the master database, but these
procedures are not in that (or any other) database. How can I run them'
Thanks for your help.
- JeffJeff,
You need to open the scripts to run them. File->Open, just like with any
other text editor, then you can run whatever's in the files.
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthlink.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them'
> Thanks for your help.
> - Jeff
>|||messages.sql is not something you can run directly from Query Analyzer. Go
out to the command line and run osql ... see Books Online for information,
but it might look something like this:
osql -E -S (local) -i c:\path\messages.sql
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthlink.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
> how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them'
> Thanks for your help.
> - Jeff
>|||Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks, guys -
I knew it had to be something simple!
- Jeff
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OsV9d0lHEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
I've been advised to run a few scripts located in my SQL Install directory
(messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure how.
When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
find stored procedure' error. I'm using the master database, but these
procedures are not in that (or any other) database. How can I run them'
Thanks for your help.
- JeffJeff,
You need to open the scripts to run them. File->Open, just like with any
other text editor, then you can run whatever's in the files.
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthlink.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them'
> Thanks for your help.
> - Jeff
>|||messages.sql is not something you can run directly from Query Analyzer. Go
out to the command line and run osql ... see Books Online for information,
but it might look something like this:
osql -E -S (local) -i c:\path\messages.sql
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthlink.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
> how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them'
> Thanks for your help.
> - Jeff
>|||Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks, guys -
I knew it had to be something simple!
- Jeff
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OsV9d0lHEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
Could not find stored procedure
Hi -
I've been advised to run a few scripts located in my SQL Install directory
(messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure how.
When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
find stored procedure' error. I'm using the master database, but these
procedures are not in that (or any other) database. How can I run them'
Thanks for your help.
- JeffJeff,
You need to open the scripts to run them. File->Open, just like with any
other text editor, then you can run whatever's in the files.
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthlink.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them'
> Thanks for your help.
> - Jeff
>|||messages.sql is not something you can run directly from Query Analyzer. Go
out to the command line and run osql ... see Books Online for information,
but it might look something like this:
osql -E -S (local) -i c:\path\messages.sql
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthlink.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
> how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them'
> Thanks for your help.
> - Jeff
>|||Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks, guys -
I knew it had to be something simple!
- Jeff
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OsV9d0lHEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
I've been advised to run a few scripts located in my SQL Install directory
(messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure how.
When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
find stored procedure' error. I'm using the master database, but these
procedures are not in that (or any other) database. How can I run them'
Thanks for your help.
- JeffJeff,
You need to open the scripts to run them. File->Open, just like with any
other text editor, then you can run whatever's in the files.
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthlink.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them'
> Thanks for your help.
> - Jeff
>|||messages.sql is not something you can run directly from Query Analyzer. Go
out to the command line and run osql ... see Books Online for information,
but it might look something like this:
osql -E -S (local) -i c:\path\messages.sql
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff" <jeffg22@.mindspring.com> wrote in message
news:cJAdc.1884$l75.977@.newsread2.news.atl.earthlink.net...
> Hi -
> I've been advised to run a few scripts located in my SQL Install directory
> (messages, sp1_serv_uni, sp2_serv_uni, sp3_serv_uni), and I'm not sure
> how.
> When I use Query Analyzer and run 'EXEC messages.sql', I get a 'Could not
> find stored procedure' error. I'm using the master database, but these
> procedures are not in that (or any other) database. How can I run them'
> Thanks for your help.
> - Jeff
>|||Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks, guys -
I knew it had to be something simple!
- Jeff
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OsV9d0lHEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Or, of course, as Adam suggests, open the file in QA. <sheepish grin>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
Could not find database ID 65. Database may not be activated yet or may be in tr
When you run an sp for the first time it generates a query plan.
Sounds like it is getting the database ID at this time and so failing when the database is dropped. It will be accessing the old database after it is renamed.
You can probably cure this by specifying 'with recompile' so that it resolves the objects on each execution.
If you are runing the query from query analyser then it must be caching the query plan somewhere.I do have included recompile option and as you said, it is caching in procedure cache and even after clearing cache also it is resulting same error. I have observed one thing is that, it is not happening with all tables which are included in my select statements in stored procedures. Another thing is after executing Job for 3 to 4 times only this problem is raising and for your information, both servers are having SQL7 SP2 and MDAC 2.5 SP1 (This configuration is forced to keep since most our major clients are running under same environment). Please let me know if you have any answer. Thanks for your kind help
Sounds like it is getting the database ID at this time and so failing when the database is dropped. It will be accessing the old database after it is renamed.
You can probably cure this by specifying 'with recompile' so that it resolves the objects on each execution.
If you are runing the query from query analyser then it must be caching the query plan somewhere.I do have included recompile option and as you said, it is caching in procedure cache and even after clearing cache also it is resulting same error. I have observed one thing is that, it is not happening with all tables which are included in my select statements in stored procedures. Another thing is after executing Job for 3 to 4 times only this problem is raising and for your information, both servers are having SQL7 SP2 and MDAC 2.5 SP1 (This configuration is forced to keep since most our major clients are running under same environment). Please let me know if you have any answer. Thanks for your kind help
Subscribe to:
Posts (Atom)