Showing posts with label script. Show all posts
Showing posts with label script. Show all posts

Monday, March 19, 2012

Cannot work with Scrip task

Hey guys,
It's sort of madness I guess. :))
I use Script Task.
It has no code inside, but "Dts.TaskResult = Dts.Results.Success"
I execute package. I'm getting error

Error: 0x5 at Script Task: The script files failed to load.

Task failed: Script Task
What is it? How I can fix it? What's wrong?
Thanks.

Check out this thread ...

http://forums.microsoft.com/msdn/showpost.aspx?postid=68546&siteid=1

Donald

|||Thanks. It works.

cannot view wsdl of endpoint.

i created this endpoint in SSMS:

Code Snippet

/****** Object: Endpoint [first_Endpoint] Script Date: 06/19/2007 16:39:22 ******/

CREATEENDPOINT [first_Endpoint]

AUTHORIZATION [Domain\username]--scrubbed my username out of post!

STATE=STARTED

ASHTTP(PATH=N'/sql',

PORTS=(CLEAR),

AUTHENTICATION=(NTLM,KERBEROS,INTEGRATED),

SITE=N'sitename,

CLEAR_PORT= 80,

COMPRESSION=DISABLED)

FORSOAP(

WEBMETHOD 'provideInfo'(NAME=N'[adventureworks].[dbo].[uspGetBillOfMaterials]'

,SCHEMA=DEFAULT

, FORMAT=ALL_RESULTS),BATCHES=DISABLED,

SESSIONS=DISABLED,SESSION_TIMEOUT=60,

DATABASE=N'AdventureWorks',

NAMESPACE=N'http://tempuri.org/',

SCHEMA=STANDARD,

CHARACTER_SET=XML)

When i type http://localhost/sql/provideinfo?wsdl into internet explorer, i just get a 404 page cannot be found error.

When i type http://server/sql/provideinfo?wsdl in to the browser i get a Page cannot be displayd error.

I get the same when i type http://myServerName/sql/provideinfo?wsdl

I get the same when i type http://sitename/sql/provideinfo?wsdl

Sql server is running under an account with admin access to the box and sa access to the sql server. what am i doing wrong here that i cannot view my wsdl. oh, the OS is vista.

Could it be a configuration issue that im not seeing?

Can you try the following?
http://localhost/sql?wsdl

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372924&SiteID=1|||i tried http://localhost/sql?wsdl but still get a 401 not found error. as i am not getting the error speicifed in the link you provided, it would make me think that it is unrelated to my issue. thanks for the reply though.|||

when i use http://server/sql?wsdl i get a 502 error, a connection could not be made as the server actively refused it.

Are there some network settings i should change on my machine possibly?

|||Is server here the same name which you specified in SITE?|||no. the server and the site have different names. I have tried the url both ways though. when i specify the servername in the url, i get an iis page not found error. when i try with the sitename, i get a page cannot be displayed error.|||

The way the 'SITE' keyword value is treated is that it is passed to the OS HTTP.sys service. The HTTP.sys service uses a combination of information to determine which process to redirect the HTTP request to. In this scenario, only HTTP requests send to http://sitename/sql are redirected to this SQL Server endpoint. So, unless machine running SQL Server is named 'sitename', it is very unlikely that the HTTP request will get to SQL Server.

If possible, I recommend changing the SITE value to '*' or the actual machine name.

BTW, I believe the default value for the WSDL generation is NONE, so ?wsdl will get you a 502 HTTP error.

Jimmy

|||

Hi Jimmy.


That indeed was the problem!!! i dropped the endpoint, changed the site name to the name of the pc the endpoint is hosted upon and bingo!!!! cant believe i was so stupid in the first place!!!! it makes total sense now that i look at it! I expected to see the page that comes up when you view the definition of an enpoint in IE, but i also realise that that wont happen. i just see a lot of xml making up the wsdl file, and thats what i required. Thanks for the help and suggestions everyone, and Jimmy, for the answer that gave me the required results.

Friday, February 24, 2012

Cannot show the editor for this task. Specified cast is not valid

This error is pretty persistent. I re-installed VS 2005 and SQL Server 2005 but did not help. Every time I try to open a Script Task editor it gives me the same error regardless of the project or package. DO I NEED TO REBUILD MY SYSTEM ?

===================================

Cannot show the editor for this task. (Microsoft Visual Studio)

===================================

Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)


Program Location:

at Microsoft.VisualBasic.Vsa.Dt.VsaIDE.get_ExtensibilityObject()
at Microsoft.SqlServer.VSAHosting.DesignTime.get_ExtensibilityObject()
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskUI.Dispose()
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskMainWnd.Dispose(Boolean disposing)
at System.ComponentModel.Component.Dispose()
at Microsoft.DataTransformationServices.Design.DtrPackageDesigner.DoDefaultActionForTask(TaskHost task)

-

Any help is appreciated.

Gulden

.......

have you tried re-installing ssis?|||

I've re-installed SQL Server 2005 and Visual Studio 2005. Still cannot open the scripts.

|||

Last couple of days I have been re-installing VS 2005 and SQL Server 2005 from scratch. This is related to a system or temp file which was created by SSIS which does not go away when these apps uninstalled.

Would anybody know if I should go ahead and rebuild my system or are there any specific files or registry items involved that I should search for?

|||

When editing scripts, VB for applications flashes for a split second and goes away. When I am running the packages on the Debug mode, I get "SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for inconvenience" right away. And does not iterate through any script after that. I wonder if I have too many script tasks in the package (about 90)

Something has seriously been corrupted concerning the Script editing that no installation can fix. It is so unexplainable to me.

Any advice ?

|||

Mistery solved!

Apparently they pushed a Macromedia package to hundreds of people's computers in my workplace re-registering at least 20 dlls including vbscript.dll.

|||I am still experiencing this problem and none of the suggestions seem to work. Any help would be appreciated.|||Did you get this problem resolved?

I have the same problem on a production package that I need to run once a week. Any help is appreciated.

Pade|||

I face the same error. It was working fine - until suddenly i started getting this message "Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)"

and it doesn't go away neither the script editor opens.

Anyone who has solved this issue ?

Thanks

Sunday, February 19, 2012

Cannot show the editor for this task. Specified cast is not valid

This error is pretty persistent. I re-installed VS 2005 and SQL Server 2005 but did not help. Every time I try to open a Script Task editor it gives me the same error regardless of the project or package. DO I NEED TO REBUILD MY SYSTEM ?

===================================

Cannot show the editor for this task. (Microsoft Visual Studio)

===================================

Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)


Program Location:

at Microsoft.VisualBasic.Vsa.Dt.VsaIDE.get_ExtensibilityObject()
at Microsoft.SqlServer.VSAHosting.DesignTime.get_ExtensibilityObject()
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskUI.Dispose()
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskMainWnd.Dispose(Boolean disposing)
at System.ComponentModel.Component.Dispose()
at Microsoft.DataTransformationServices.Design.DtrPackageDesigner.DoDefaultActionForTask(TaskHost task)

-

Any help is appreciated.

Gulden

.......

have you tried re-installing ssis?|||

I've re-installed SQL Server 2005 and Visual Studio 2005. Still cannot open the scripts.

|||

Last couple of days I have been re-installing VS 2005 and SQL Server 2005 from scratch. This is related to a system or temp file which was created by SSIS which does not go away when these apps uninstalled.

Would anybody know if I should go ahead and rebuild my system or are there any specific files or registry items involved that I should search for?

|||

When editing scripts, VB for applications flashes for a split second and goes away. When I am running the packages on the Debug mode, I get "SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for inconvenience" right away. And does not iterate through any script after that. I wonder if I have too many script tasks in the package (about 90)

Something has seriously been corrupted concerning the Script editing that no installation can fix. It is so unexplainable to me.

Any advice ?

|||

Mistery solved!

Apparently they pushed a Macromedia package to hundreds of people's computers in my workplace re-registering at least 20 dlls including vbscript.dll.

|||I am still experiencing this problem and none of the suggestions seem to work. Any help would be appreciated.|||Did you get this problem resolved?

I have the same problem on a production package that I need to run once a week. Any help is appreciated.

Pade|||

I face the same error. It was working fine - until suddenly i started getting this message "Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)"

and it doesn't go away neither the script editor opens.

Anyone who has solved this issue ?

Thanks

Cannot show the editor for this task. Specified cast is not valid

This error is pretty persistent. I re-installed VS 2005 and SQL Server 2005 but did not help. Every time I try to open a Script Task editor it gives me the same error regardless of the project or package. DO I NEED TO REBUILD MY SYSTEM ?

===================================

Cannot show the editor for this task. (Microsoft Visual Studio)

===================================

Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)


Program Location:

at Microsoft.VisualBasic.Vsa.Dt.VsaIDE.get_ExtensibilityObject()
at Microsoft.SqlServer.VSAHosting.DesignTime.get_ExtensibilityObject()
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskUI.Dispose()
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskMainWnd.Dispose(Boolean disposing)
at System.ComponentModel.Component.Dispose()
at Microsoft.DataTransformationServices.Design.DtrPackageDesigner.DoDefaultActionForTask(TaskHost task)

-

Any help is appreciated.

Gulden

.......

have you tried re-installing ssis?|||

I've re-installed SQL Server 2005 and Visual Studio 2005. Still cannot open the scripts.

|||

Last couple of days I have been re-installing VS 2005 and SQL Server 2005 from scratch. This is related to a system or temp file which was created by SSIS which does not go away when these apps uninstalled.

Would anybody know if I should go ahead and rebuild my system or are there any specific files or registry items involved that I should search for?

|||

When editing scripts, VB for applications flashes for a split second and goes away. When I am running the packages on the Debug mode, I get "SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for inconvenience" right away. And does not iterate through any script after that. I wonder if I have too many script tasks in the package (about 90)

Something has seriously been corrupted concerning the Script editing that no installation can fix. It is so unexplainable to me.

Any advice ?

|||

Mistery solved!

Apparently they pushed a Macromedia package to hundreds of people's computers in my workplace re-registering at least 20 dlls including vbscript.dll.

|||I am still experiencing this problem and none of the suggestions seem to work. Any help would be appreciated.|||Did you get this problem resolved?

I have the same problem on a production package that I need to run once a week. Any help is appreciated.

Pade
|||

I face the same error. It was working fine - until suddenly i started getting this message "Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)"

and it doesn't go away neither the script editor opens.

Anyone who has solved this issue ?

Thanks

Cannot show the editor for this task. Specified cast is not valid

This error is pretty persistent. I re-installed VS 2005 and SQL Server 2005 but did not help. Every time I try to open a Script Task editor it gives me the same error regardless of the project or package. DO I NEED TO REBUILD MY SYSTEM ?

===================================

Cannot show the editor for this task. (Microsoft Visual Studio)

===================================

Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)


Program Location:

at Microsoft.VisualBasic.Vsa.Dt.VsaIDE.get_ExtensibilityObject()
at Microsoft.SqlServer.VSAHosting.DesignTime.get_ExtensibilityObject()
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskUI.Dispose()
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskMainWnd.Dispose(Boolean disposing)
at System.ComponentModel.Component.Dispose()
at Microsoft.DataTransformationServices.Design.DtrPackageDesigner.DoDefaultActionForTask(TaskHost task)

-

Any help is appreciated.

Gulden

.......

have you tried re-installing ssis?|||

I've re-installed SQL Server 2005 and Visual Studio 2005. Still cannot open the scripts.

|||

Last couple of days I have been re-installing VS 2005 and SQL Server 2005 from scratch. This is related to a system or temp file which was created by SSIS which does not go away when these apps uninstalled.

Would anybody know if I should go ahead and rebuild my system or are there any specific files or registry items involved that I should search for?

|||

When editing scripts, VB for applications flashes for a split second and goes away. When I am running the packages on the Debug mode, I get "SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for inconvenience" right away. And does not iterate through any script after that. I wonder if I have too many script tasks in the package (about 90)

Something has seriously been corrupted concerning the Script editing that no installation can fix. It is so unexplainable to me.

Any advice ?

|||

Mistery solved!

Apparently they pushed a Macromedia package to hundreds of people's computers in my workplace re-registering at least 20 dlls including vbscript.dll.

|||I am still experiencing this problem and none of the suggestions seem to work. Any help would be appreciated.|||Did you get this problem resolved?

I have the same problem on a production package that I need to run once a week. Any help is appreciated.

Pade|||

I face the same error. It was working fine - until suddenly i started getting this message "Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)"

and it doesn't go away neither the script editor opens.

Anyone who has solved this issue ?

Thanks

Tuesday, February 14, 2012

cannot script ONLY indexes

hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
and keys defaults and contraints, i do NOT want to generate anything else as
these already exist in the destination database that im going to run the
script on. what options will i need to tick on the "general" tab in order
for sql to generate the script correctly?, i have tried scripting all
objects and then unticking the create and drop objects commands, but this
then creates an empty script, i have also tried leaving the create and drop
objects commands ticked, but this does not seem to script any of the
existing indexes, anybody have a clue as to how i get around this?.
many thanks!,
paul.Hi Paul,
You need to select to script all the tables, and all indexes and
constraints, with the create statements only. This will generate a script
with everything you want, plus create scripts for all the table. The create
statements for all the tables will be first in the script, and all the
indexes and constraints will be in the script after that, so you can easily
delete the create statements for all the tables from the script.
Jacco Schalkwijk
SQL Server MVP
"Paul Eccleston" <paul.eccleston@.get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@.nnrp1.news.uk.psi.net...
> hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
> and keys defaults and contraints, i do NOT want to generate anything else
as
> these already exist in the destination database that im going to run the
> script on. what options will i need to tick on the "general" tab in order
> for sql to generate the script correctly?, i have tried scripting all
> objects and then unticking the create and drop objects commands, but this
> then creates an empty script, i have also tried leaving the create and
drop
> objects commands ticked, but this does not seem to script any of the
> existing indexes, anybody have a clue as to how i get around this?.
> many thanks!,
> paul.
>|||Just to add, you might want to make use of SQL DMO object model. This gives
you ultimate flexibility.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Paul Eccleston" <paul.eccleston@.get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@.nnrp1.news.uk.psi.net...
hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
and keys defaults and contraints, i do NOT want to generate anything else as
these already exist in the destination database that im going to run the
script on. what options will i need to tick on the "general" tab in order
for sql to generate the script correctly?, i have tried scripting all
objects and then unticking the create and drop objects commands, but this
then creates an empty script, i have also tried leaving the create and drop
objects commands ticked, but this does not seem to script any of the
existing indexes, anybody have a clue as to how i get around this?.
many thanks!,
paul.|||Hi ,
I have Wrote a vry good utility to script the Indexes (Drop and Create). Use
this script and put in your suggestions, so that I can enhance the script
before publishing.
This will consider the following,
1. CLustered Index
2. Primary key clusterd index
3. Primary key non clustered index
4. Unique CLsutered
5. Unique non clustered
6. Clusterd Index
Attached the script , I will be publishing this in MSDB India site shortly
with a document.
CREATE PROC INDX_SCRIPING(
@.iTableName SYSNAME = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tableId INT
DECLARE @.indexId INT
DECLARE @.indexName SYSNAME
DECLARE @.tableName SYSNAME
DECLARE @.colId INT
DECLARE @.keyNo INT
DECLARE INDEX_CURSOR CURSOR FOR
SELECT I.ID,I.INDID,I.NAME, OBJECT_NAME(O.ID)
FROM SYSINDEXES I
INNER JOIN SYSOBJECTS O
ON I.ID = O.ID
WHERE O.XTYPE = 'U'
AND ((@.iTableName IS NULL) OR (O.ID = OBJECT_ID(@.iTableName)))
CREATE TABLE #indexInfo
(tableId INT,
indexId INT,
indexName SYSNAME,
tableName SYSNAME,
keys VARCHAR(7000),
isClustered BIT,
isPrimaryKey BIT,
IsUnique BIT,
IsUniqueCnst BIT)
OPEN INDEX_CURSOR
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #indexInfo Values
(@.tableId,
@.indexId,
@.indexName,
@.tableName,
'',
CASE @.indexId
WHEN 1 then 1
ELSE 0
END,
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsPrimaryKey'),0),
ISNULL(INDEXPROPERTY(OBJECT_ID(@.tableNam
e), @.indexName,'IsUnique'),0),
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsUniqueCnst'),0))
--PRINT 'Table ' + @.tableName
DECLARE indexKey_Cursor CURSOR FOR
SELECT colid,KeyNo
FROM SYSINDEXKEYS
WHERE ID = @.tableId
AND INDID = @.indexId
OPEN indexKey_Cursor
FETCH NEXT FROM indexKey_Cursor
INTO @.colId,@.keyNO
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE #indexInfo SET
keys = keys + COL_NAME(@.tableid,@.colId) + ','
WHERE tableId = @.tableId
AND indexId = @.indexId
--PRINT 'COL ' + COL_NAME(@.tableid,@.colId)
FETCH NEXT FROM indexKey_Cursor
INTO @.colId,@.keyNO
END
CLOSE indexKey_Cursor
DEALLOCATE indexKey_Cursor
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableName
END
DELETE #indexInfo WHERE keys = ''
UPDATE #indexInfo SET keys = LEFT(keys,LEN(Keys)-1)
declare @.index table(sql varchar(8000))
--Drop clustered primaty key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 1
-- Drop unique Constraint
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
-- Drop unique Constraint
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
--Drop clustered index
INSERT INTO @.INDEX SELECT 'DROP INDEX' + tablename+'.'+indexName + CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
--Drop non clustered index
INSERT INTO @.INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
--Drop unique Clustred Index
INSERT INTO @.INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
--Drop unique nonClustred Index
INSERT INTO @.INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
--Drop non clustered primary key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 0
-- Create Index Statement
-- Create Clustered Primary key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT PK_' + tableName +
' PRIMARY KEY CLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 1
-- Create Unique nonClustered index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT ' + indexName +
' UNIQUE NONCLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
-- Create Unique Clustered index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT ' + indexName +
' UNIQUE CLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
-- Create NonClustered Primary key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT PK_' + tableName +
' PRIMARY KEY NONCLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 0
--Create NonClustered Index
INSERT INTO @.INDEX SELECT 'CREATE NONCLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
--Create Clustered index
INSERT INTO @.INDEX SELECT 'CREATE CLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
-- Create Unique Clustered Index
INSERT INTO @.INDEX SELECT 'CREATE UNIQUE CLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
-- Create Unique nonClustered Index
INSERT INTO @.INDEX SELECT 'CREATE UNIQUE NONCLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
TRUNCATE TABLE #indexInfo
DROP TABLE #indexInfo
CLOSE INDEX_CURSOR
DEALLOCATE INDEX_CURSOR
SELECT * FROM @.INDEX
END
How to execute
RECREATE_INDEX 'TEST_INDEX'
If you not giving the table name the script will generated for all user
tables.
Thanks
Hari
MCDBA
"Paul Eccleston" <paul.eccleston@.get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@.nnrp1.news.uk.psi.net...
> hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
> and keys defaults and contraints, i do NOT want to generate anything else
as
> these already exist in the destination database that im going to run the
> script on. what options will i need to tick on the "general" tab in order
> for sql to generate the script correctly?, i have tried scripting all
> objects and then unticking the create and drop objects commands, but this
> then creates an empty script, i have also tried leaving the create and
drop
> objects commands ticked, but this does not seem to script any of the
> existing indexes, anybody have a clue as to how i get around this?.
> many thanks!,
> paul.
>

cannot script ONLY indexes

hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
and keys defaults and contraints, i do NOT want to generate anything else as
these already exist in the destination database that im going to run the
script on. what options will i need to tick on the "general" tab in order
for sql to generate the script correctly?, i have tried scripting all
objects and then unticking the create and drop objects commands, but this
then creates an empty script, i have also tried leaving the create and drop
objects commands ticked, but this does not seem to script any of the
existing indexes, anybody have a clue as to how i get around this?.
many thanks!,
paul.Hi Paul,
You need to select to script all the tables, and all indexes and
constraints, with the create statements only. This will generate a script
with everything you want, plus create scripts for all the table. The create
statements for all the tables will be first in the script, and all the
indexes and constraints will be in the script after that, so you can easily
delete the create statements for all the tables from the script.
Jacco Schalkwijk
SQL Server MVP
"Paul Eccleston" <paul.eccleston@.get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@.nnrp1.news.uk.psi.net...
> hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
> and keys defaults and contraints, i do NOT want to generate anything else
as
> these already exist in the destination database that im going to run the
> script on. what options will i need to tick on the "general" tab in order
> for sql to generate the script correctly?, i have tried scripting all
> objects and then unticking the create and drop objects commands, but this
> then creates an empty script, i have also tried leaving the create and
drop
> objects commands ticked, but this does not seem to script any of the
> existing indexes, anybody have a clue as to how i get around this?.
> many thanks!,
> paul.
>|||Just to add, you might want to make use of SQL DMO object model. This gives
you ultimate flexibility.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Paul Eccleston" <paul.eccleston@.get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@.nnrp1.news.uk.psi.net...
hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
and keys defaults and contraints, i do NOT want to generate anything else as
these already exist in the destination database that im going to run the
script on. what options will i need to tick on the "general" tab in order
for sql to generate the script correctly?, i have tried scripting all
objects and then unticking the create and drop objects commands, but this
then creates an empty script, i have also tried leaving the create and drop
objects commands ticked, but this does not seem to script any of the
existing indexes, anybody have a clue as to how i get around this?.
many thanks!,
paul.|||I was working on some SQL scripts to do that, as well as droping and recreat
ing the FKeys.
I need to check how complete they are. I can let you have them, but I can't
guarrentee that they work 100%|||Hi ,
I have Wrote a vry good utility to script the Indexes (Drop and Create). Use
this script and put in your suggestions, so that I can enhance the script
before publishing.
This will consider the following,
1. CLustered Index
2. Primary key clusterd index
3. Primary key non clustered index
4. Unique CLsutered
5. Unique non clustered
6. Clusterd Index
Attached the script , I will be publishing this in MSDB India site shortly
with a document.
CREATE PROC INDX_SCRIPING(
@.iTableName SYSNAME = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tableId INT
DECLARE @.indexId INT
DECLARE @.indexName SYSNAME
DECLARE @.tableName SYSNAME
DECLARE @.colId INT
DECLARE @.keyNo INT
DECLARE INDEX_CURSOR CURSOR FOR
SELECT I.ID,I.INDID,I.NAME, OBJECT_NAME(O.ID)
FROM SYSINDEXES I
INNER JOIN SYSOBJECTS O
ON I.ID = O.ID
WHERE O.XTYPE = 'U'
AND ((@.iTableName IS NULL) OR (O.ID = OBJECT_ID(@.iTableName)))
CREATE TABLE #indexInfo
(tableId INT,
indexId INT,
indexName SYSNAME,
tableName SYSNAME,
keys VARCHAR(7000),
isClustered BIT,
isPrimaryKey BIT,
IsUnique BIT,
IsUniqueCnst BIT)
OPEN INDEX_CURSOR
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #indexInfo Values
(@.tableId,
@.indexId,
@.indexName,
@.tableName,
'',
CASE @.indexId
WHEN 1 then 1
ELSE 0
END,
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsPrimaryKey'),0),
ISNULL(INDEXPROPERTY(OBJECT_ID(@.tableNam
e), @.indexName,'IsUnique'),0),
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsUniqueCnst'),0))
--PRINT 'Table ' + @.tableName
DECLARE indexKey_Cursor CURSOR FOR
SELECT colid,KeyNo
FROM SYSINDEXKEYS
WHERE ID = @.tableId
AND INDID = @.indexId
OPEN indexKey_Cursor
FETCH NEXT FROM indexKey_Cursor
INTO @.colId,@.keyNO
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE #indexInfo SET
keys = keys + COL_NAME(@.tableid,@.colId) + ','
WHERE tableId = @.tableId
AND indexId = @.indexId
--PRINT 'COL ' + COL_NAME(@.tableid,@.colId)
FETCH NEXT FROM indexKey_Cursor
INTO @.colId,@.keyNO
END
CLOSE indexKey_Cursor
DEALLOCATE indexKey_Cursor
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableName
END
DELETE #indexInfo WHERE keys = ''
UPDATE #indexInfo SET keys = LEFT(keys,LEN(Keys)-1)
declare @.index table(sql varchar(8000))
--Drop clustered primaty key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 1
-- Drop unique Constraint
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
-- Drop unique Constraint
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
--Drop clustered index
INSERT INTO @.INDEX SELECT 'DROP INDEX' + tablename+'.'+indexName + CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
--Drop non clustered index
INSERT INTO @.INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
--Drop unique Clustred Index
INSERT INTO @.INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
--Drop unique nonClustred Index
INSERT INTO @.INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
--Drop non clustered primary key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 0
-- Create Index Statement
-- Create Clustered Primary key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT PK_' + tableName +
' PRIMARY KEY CLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 1
-- Create Unique nonClustered index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT ' + indexName +
' UNIQUE NONCLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
-- Create Unique Clustered index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT ' + indexName +
' UNIQUE CLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
-- Create NonClustered Primary key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT PK_' + tableName +
' PRIMARY KEY NONCLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 0
--Create NonClustered Index
INSERT INTO @.INDEX SELECT 'CREATE NONCLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
--Create Clustered index
INSERT INTO @.INDEX SELECT 'CREATE CLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
-- Create Unique Clustered Index
INSERT INTO @.INDEX SELECT 'CREATE UNIQUE CLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
-- Create Unique nonClustered Index
INSERT INTO @.INDEX SELECT 'CREATE UNIQUE NONCLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
TRUNCATE TABLE #indexInfo
DROP TABLE #indexInfo
CLOSE INDEX_CURSOR
DEALLOCATE INDEX_CURSOR
SELECT * FROM @.INDEX
END
How to execute
RECREATE_INDEX 'TEST_INDEX'
If you not giving the table name the script will generated for all user
tables.
Thanks
Hari
MCDBA
"Paul Eccleston" <paul.eccleston@.get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@.nnrp1.news.uk.psi.net...
> hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
> and keys defaults and contraints, i do NOT want to generate anything else
as
> these already exist in the destination database that im going to run the
> script on. what options will i need to tick on the "general" tab in order
> for sql to generate the script correctly?, i have tried scripting all
> objects and then unticking the create and drop objects commands, but this
> then creates an empty script, i have also tried leaving the create and
drop
> objects commands ticked, but this does not seem to script any of the
> existing indexes, anybody have a clue as to how i get around this?.
> many thanks!,
> paul.
>|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:unZqlpJDEHA.684@.tk2msftngp13.phx.gbl...
> Hi ,
> I have Wrote a vry good utility to script the Indexes (Drop and Create).
Use
> this script and put in your suggestions, so that I can enhance the script
> before publishing.
I've made a few changes to your script to support a few extra features:
Fixed BUG with not showing NONCLUSTERED Primary Keys
Supports Table Owners other than [dbo]
Supports FileGroups
Supports DESC index columns
Supports FILLFACTOR & PAD_INDEX options
Changed order of index/constraint creation so that CLUSTERED is always first
Added [] around all object names
You could also add support for the IGNORE_DUP_KEY index option if you want.
CREATE PROC INDX_SCRIPING(
@.iTableName SYSNAME = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tableId INT
DECLARE @.indexId INT
DECLARE @.indexName SYSNAME
DECLARE @.tableOwner SYSNAME
DECLARE @.tableName SYSNAME
DECLARE @.groupName SYSNAME
DECLARE @.colId INT
DECLARE @.keyNo INT
DECLARE INDEX_CURSOR CURSOR FOR
SELECT I.ID,I.INDID,I.NAME,U.NAME,OBJECT_NAME(O.ID),F.GROUPNAME
FROM SYSINDEXES I
INNER JOIN SYSOBJECTS O ON I.ID = O.ID
INNER JOIN SYSFILEGROUPS F ON I.GROUPID=F.GROUPID
INNER JOIN SYSUSERS U ON O.UID=U.UID
WHERE O.XTYPE = 'U' AND ((@.iTableName IS NULL) OR (O.ID =
OBJECT_ID(@.iTableName)))
CREATE TABLE #indexInfo
(tableId INT,
indexId INT,
indexName SYSNAME,
tableOwner SYSNAME,
tableName SYSNAME,
groupname SYSNAME,
keys VARCHAR(6800),
isClustered BIT,
isPrimaryKey BIT,
IsUnique BIT,
IsUniqueCnst BIT,
IsPadIndex BIT,
indFillFactor int null)
OPEN INDEX_CURSOR
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableOwner, @.tableName, @.groupName
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #indexInfo Values
(@.tableId,
@.indexId,
@.indexName,
@.tableOwner,
@.tableName,
@.groupName,
'',
CASE @.indexId
WHEN 1 then 1
ELSE 0
END,
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsPrimaryKey'),0),
ISNULL(INDEXPROPERTY(@.tableId, @.indexName,'IsUnique'),0),
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsUniqueCnst'),0),
ISNULL(INDEXPROPERTY(@.tableId, @.indexName,'IsPadIndex'),0),
INDEXPROPERTY(@.tableId, @.indexName,'IndexFillFactor') )
--PRINT 'Table ' + @.tableName
DECLARE indexKey_Cursor CURSOR FOR
SELECT colid,KeyNo
FROM SYSINDEXKEYS
WHERE ID = @.tableId AND INDID = @.indexId
OPEN indexKey_Cursor
FETCH NEXT FROM indexKey_Cursor
INTO @.colId,@.keyNO
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE #indexInfo
SET keys = keys + '[' + COL_NAME(@.tableid,@.colId) + ']' +
CASE WHEN indexkey_property(@.tableId, @.indexId, @.keyNO,
N'isdescending') = 1 THEN ' DESC,' ELSE ',' END
WHERE tableId = @.tableId
AND indexId = @.indexId
FETCH NEXT FROM indexKey_Cursor
INTO @.colId,@.keyNO
END
CLOSE indexKey_Cursor
DEALLOCATE indexKey_Cursor
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableOwner, @.tableName, @.groupName
END
CLOSE INDEX_CURSOR
DEALLOCATE INDEX_CURSOR
DELETE #indexInfo WHERE keys = ''
UPDATE #indexInfo SET keys = LEFT(keys,LEN(Keys)-1)
declare @.index table(pos smallint,sql varchar(8000))
-- Drop Primaty Key or Unique Constraint
INSERT INTO @.INDEX SELECT 1,'ALTER TABLE [' + tableOwner + '].[' + t
ablename
+ '] DROP CONSTRAINT [' + indexName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 1 or IsUniqueCnst = 1
-- Drop Index
INSERT INTO @.INDEX SELECT 2,'DROP INDEX [' + tableOwner + '].[' + ta
blename
+ '].[' + indexName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 0 and IsUniqueCnst = 0
-- Create Primary Key Constraint
INSERT INTO @.INDEX SELECT CASE WHEN isClustered = 1 THEN 3 ELSE 4 END,
'ALTER TABLE [' + tableOwner + '].[' + tableName + ']' +
' ADD CONSTRAINT [' + indexName + ']' +
' PRIMARY KEY' +
CASE WHEN isClustered = 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED'
END +
' (' + keys + ')' +
' ON [' + groupName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 1
-- Create Unique Constraint
INSERT INTO @.INDEX SELECT CASE WHEN isClustered = 1 THEN 3 ELSE 4 END,
'ALTER TABLE [' + tableOwner + '].[' + tableName + ']' +
' ADD CONSTRAINT [' + indexName + ']' +
' UNIQUE' +
CASE WHEN isClustered = 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED'
END +
' (' + keys + ')' +
CASE WHEN indFillFactor is not null then ' WITH FILLFACTOR=' +
CAST(indFillFactor AS VARCHAR) ELSE '' END +
CASE WHEN (indFillFactor is not null AND IsPadIndex = 1 ) THEN ', PAD_INDEX'
ELSE '' END +
' ON [' + groupName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 0 and IsUniqueCnst = 1 and IsUnique = 1
-- Create Index
INSERT INTO @.INDEX SELECT CASE WHEN isClustered = 1 THEN 3 ELSE 5 END,
'CREATE' +
CASE WHEN IsUnique = 1 THEN ' UNIQUE' ELSE '' END +
CASE WHEN isClustered = 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED'
END +
' INDEX [' + indexName + '] ON [' + tableOwner + '].[' +
tableName + ']' +
'(' + keys + ')' +
CASE WHEN indFillFactor is not null then ' WITH FILLFACTOR=' +
CAST(indFillFactor AS VARCHAR) ELSE '' END +
CASE WHEN (indFillFactor is not null AND IsPadIndex = 1 ) THEN ', PAD_INDEX'
ELSE '' END +
' ON [' + groupName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 0 and IsUniqueCnst = 0
TRUNCATE TABLE #indexInfo
DROP TABLE #indexInfo
SELECT sql FROM @.INDEX ORDER BY pos
END

Sunday, February 12, 2012

Cannot run snapshot agent from command line

I have a transactional replication running and it's running well.
But now i'm writing a script so I can start the Snapshot Agent from
distance. therefore I want to run it from the command prompt.
I'm using this command: C:\Program Files\Microsoft SQL Server\80\COM
\snapshot.exe -Publisher <server> -PublisherDB <pubDB> -Publication
<pub> -DistributorLogin <login> -DistributorPassword <pwd>
And this is my output:
==== Output start ======
Microsoft SQL Server Snapshot Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
The process could not create file '\\<server>\ReplData\unc'.
Finished with exit code 2
==== Output end ======
When I start the agent from SQL Server itself it works fine.
the location of the snapshot folder is defined as '\\<server>
\ReplData' and (for testing now) this folder has full access.
Can somebody tell mee what;s going wrong and what I can do about it?
Can you create your command as text in a batch file then use RunAs (or log on
again if RunAs is disabled). The user to select is the sql server agent
login...
HTH,
Paul Ibison
|||You need to check to ensure that the share permissions are full for the
account which the SQL Server Agent runs under, or the account the snapshot
agent is proxied under, and the underlying security is full for the path and
all child objects.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"acsnaterse" <snaterse@.gmail.com> wrote in message
news:1184079506.481076.126000@.22g2000hsm.googlegro ups.com...
>I have a transactional replication running and it's running well.
> But now i'm writing a script so I can start the Snapshot Agent from
> distance. therefore I want to run it from the command prompt.
> I'm using this command: C:\Program Files\Microsoft SQL Server\80\COM
> \snapshot.exe -Publisher <server> -PublisherDB <pubDB> -Publication
> <pub> -DistributorLogin <login> -DistributorPassword <pwd>
> And this is my output:
> ==== Output start ======
> Microsoft SQL Server Snapshot Agent 8.00.2039
> Copyright (c) 2000 Microsoft Corporation
> The process could not create file '\\<server>\ReplData\unc'.
> Finished with exit code 2
> ==== Output end ======
> When I start the agent from SQL Server itself it works fine.
> the location of the snapshot folder is defined as '\\<server>
> \ReplData' and (for testing now) this folder has full access.
> Can somebody tell mee what;s going wrong and what I can do about it?
>
|||On 10 jul, 17:58, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
> Can you create your command as text in a batch file then use RunAs (or log on
> again if RunAs is disabled). The user to select is the sql server agent
> login...
> HTH,
> Paul Ibison
Hi,
Thanks! With the runas commando it works great!
Hint: in combination with Sanur (http://www.commandline.co.uk/
sanur_unsupported/index3.html) you can automate the password fill-in.

Friday, February 10, 2012

Cannot resolve collation

Trying tun a script that updates a table and i get the
following error
'Cannot resolve collation conflict for equal to operation.'
Any ideas what causes this?Probably different collations between your system databases and the
application database. Either make sure that the databases has the same
collation or adjust the offending query and specify which collation the
query is to use (using the COLLATE clause).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:b7b501c40815$73fe2f30$a301280a@.phx.gbl...
> Trying tun a script that updates a table and i get the
> following error
> 'Cannot resolve collation conflict for equal to operation.'
> Any ideas what causes this?