install SQL on WinXP: type

Showing posts with label type. Show all posts

Sunday, March 25, 2012

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

strDBName = dbDatabase.Name
jobDumpJob = New Job(srvMgmtServer.JobServer, "YourJobName")
jobDumpJob.Description = "Job Description"
jobDumpJob.Category = "[Uncategorized (Local)]"
jobDumpJob.OwnerLoginName = "sa"
jobDumpJob.Create()
strJobID = jobDumpJob.JobID

jbsDumpJobStep = New JobStep(jobDumpJob, "Step 1")
jbsDumpJobStep.DatabaseName = "UserDB"
jbsDumpJobStep.Command = "TSQL command"
jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsDumpJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
jbsDumpJobStep.Create()
intStepID = jbsDumpJobStep.ID

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The details from the error message are as follows. Any help would be much appreciated. I deleted all SSIS packages and Jobs with no luck. We've rebooted and cycled services per suggestions from users.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

Technical details

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

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.JobCollection.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.Contains(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.JobExists(String jobName)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.ApplyChanges()
at Microsoft.SqlServer.Management.SqlManagerUI.JobPropertySheet.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

however, that doesn't solve the problem because the problem lies in scheduling your job using Management Studio through your local machine. If you logged onto your SQL 2005 server that has SP2, you should be able to schedule from there. But it's through Management Studio, which I'm guessing most of you are trying to create a job schedule for your packages. Usually, people don't think of upgrading their client tools as well.

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

I finally uninstalled all SQL related products and services, reinstalled everything, added hot fixes and finally we have two of the three servers working--mostly. There are still occaisional errors, but most seem limited to SSMS' and its UI

I haven't rolled SP2 to production yet and won't be adding any new SQL servers to our mix until this mess settles. I thought we'd be going to some of the better features (like Mirroring) later this year but now it'll be Q1'08 at the earliest.

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Unable to create new jobs in sql 2005, SSIS service is running. Finally i was surfing net for solution and understood need to install SP2 in my machine. But if i need to create new jobs in remote system what should i do.

Please suggest me a solution.

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

Technical details

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

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Please suggest me a solution.

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

Technical details

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

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Please suggest me a solution.

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

Technical details

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

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Please suggest me a solution.

sql

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

Technical details

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

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Please suggest me a solution.

Can not create Job on SQL2005

Could not create SQL jobs. Getting Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type
'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

- Tried several different job types, always same result.
- Installing SP1 or SP2 does not help.
- Installing with or without Integration Service does not help.

Any help is highly appreciated

Thanks.

Try this:

Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()

|||

I am also having this problem.

Im trying to create a job to run SQL Server Integration Package.

The Code that you have given above. What is that for?

Thanks

|||This code uses SMO in VB.Net to create a job to execute a TSQL command. Browse around in ObjectBrowser and you should find the properties necessary to execute a SSIS package.|||

I can execute the SISS Package.

But i want to execute it within a job.

Is there any tutorials for creating jobs that i could look at?

|||for some reason it works on an another machine. I dont no why.|||

I figured it out. Despite you could install Management Tool on any computer it will work only on machine that have SQL Server 2005 SP2 installed.

|||Try installing SP2 for the client components on all computers taht run management studio, reboot, then try again from management studio client.|||

Identical problems here--also tried the new hotfix as well.

We've held off rolling out SP2 due to these and other errors. Until someone deals with these problems I suppose my company is stuck.

|||Check whether Distributed Transaction Coordinator service is started. If not, start it and try again.|||

We have a sql2005 installation that I've created jobs on routinely for months and then today we hit this message. I'm not going to install SP2 just yet since some users report that it doesn't help.

The original error message:

TITLE: Microsoft SQL Server Management Studio

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

BUTTONS:

Technical details

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

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

Program Location:

|||

Hi folks,

I too had the same exact error when trying to schedule a package. After reading numerous posts and forums, some people have said

SP2 solved their problems and some said it didn't.

Here's what I did to get it to work.

On our SQL Server 2005 development machine, we did install SP2

I verified this by upgrading my local machine to SP2 for the client tools and was able to successfully schedule. You might also need to upgrade your server machine to SP2 to work as well.

I hope this helps.

-SX

|||

Haven't had this much fun since SQL2KSP4! Keep smiling....

|||

Hello Visgor Allen and others!

Thank you for reporting this problem, and sorry for the problems that you are experiencing.

I'd like to understand this issue better so we can provide guidance to other customers. Can somebody on this thread please provide more details on how to repro this problem:

1) What is the initial configuration on both server and client machines (if different)? Is it SQL Server 2005 RTM or SP1?

2) Does the problem reproduce after upgrading client/server/both machines to SP2?

3) From what machine (client/server) are you trying to create a new job?

Thanks in advance!

|||

Please suggest me a solution.

Thursday, March 8, 2012

Can I use table data type in a trigger?

This little batch:
========================================
====
create trigger x on T after insert, update
as
declare @.z table (id int not null)
select
@.z = (select col from table)
go
========================================
====
seems to contain some invalid logic.
QA returns me that I have to declare @.z. But i do this.
Any help appreciated.
Regards.
fabri
Sei un italiano medio, di oltre trent'anni se:
Sai che il codice SYS64738 serviva per riavviare il Commodore 64 ed hai
cambiato almeno una mezza dozzina di joystick Quickshot I giocando a
Summer Games.
MKDS nick & friend Code:
Joker - 055895 043343I got it:

> create trigger x on T after insert, update
> as
> declare @.z table (id int not null)
> insert into @.z = select col from table
> go
fabri
Sei un italiano medio, di oltre trent'anni se:
Sai che il codice SYS64738 serviva per riavviare il Commodore 64 ed hai
cambiato almeno una mezza dozzina di joystick Quickshot I giocando a
Summer Games.
MKDS nick & friend Code:
Joker - 055895 043343

Wednesday, March 7, 2012

Can I use sql:variable() to change insert type from 'into' to 'as first into' ?

Hi
I'm trying to dynamically affect how text is inserted into an xml
element. I'd like to be able to use the following:
-- ============================================= --
declare @.x xml
,@.insert int
,@.insertType varchar(20)
set @.x = N'
<webpage>
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<info>Here is</info>
</webpage>
';
set @.insert = 3;
set @.insertType =
case @.insert
when 1 then ' into '
when 2 then ' as first into '
when 3 then ' as last into '
when 4 then ' before '
when 5 then ' after '
end
SET @.x.modify('insert text {" some text."}
sql:variable("@.insertType")
(/webpage/info)[1]');
select @.x;
select 'Insert type: "'+@.insertType+'"';
-- ============================================= --
The above doesn't work since sql:variable("@.insertType") is not
evaluated to the string ' as last into ' when the query is executed.
I've been able to get it to work by creating a query string and
executing it with sp_executesql but for longish strings it turns into
an apostrophe-escaping nightmare and quickly becomes very difficult to
read and debug.
Does anyone have any ideas how I could get this to work?
Thanks
XQuery (neither our implementation nor the standard) does not provide for a
dynamic evaluation expression. So the only way to achieve what you want, is
to use the SQL Server dynamic SQL evaluation using either EXEC or
sp_executesql.
E.g.,
declare @.x xml
,@.insert int
,@.insertType nvarchar(20)
,@.stmtstr nvarchar(max)
set @.x = N'
<webpage>
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<info>Here is</info>
</webpage>
';
set @.insert = 3;
set @.insertType =
case @.insert
when 1 then N' into '
when 2 then N' as first into '
when 3 then N' as last into '
when 4 then N' before '
when 5 then N' after '
end
set @.stmtstr = N'DECLARE @.x xml; set @.x = N'''
+ cast(@.x as nvarchar(max))
+ '''; SET @.x.modify(''insert text {" some text."}'
+ @.insertType
+ N'(/webpage/info)[1]''); select @.x';
select @.stmtstr;
exec sp_executesql @.stmtstr;
select 'Insert type: "'+@.insertType+'"';
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1128331019.225989.284590@.g43g2000cwa.googlegr oups.com...
> Hi
> I'm trying to dynamically affect how text is inserted into an xml
> element. I'd like to be able to use the following:
>
> -- ============================================= --
> declare @.x xml
> ,@.insert int
> ,@.insertType varchar(20)
> set @.x = N'
> <webpage>
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <info>Here is</info>
> </webpage>
> ';
> set @.insert = 3;
> set @.insertType =
> case @.insert
> when 1 then ' into '
> when 2 then ' as first into '
> when 3 then ' as last into '
> when 4 then ' before '
> when 5 then ' after '
> end
> SET @.x.modify('insert text {" some text."}
> sql:variable("@.insertType")
> (/webpage/info)[1]');
> select @.x;
> select 'Insert type: "'+@.insertType+'"';
> -- ============================================= --
>
> The above doesn't work since sql:variable("@.insertType") is not
> evaluated to the string ' as last into ' when the query is executed.
> I've been able to get it to work by creating a query string and
> executing it with sp_executesql but for longish strings it turns into
> an apostrophe-escaping nightmare and quickly becomes very difficult to
> read and debug.
> Does anyone have any ideas how I could get this to work?
> Thanks
>
|||Yes, I wasn't sure if dynamic evaluation was supported in sql server's
implementation of XQuery. Thanks for the confirmation - much
appreciated.

Can I use sql:variable() to change insert type from 'into' to 'as first into' ?

Hi
I'm trying to dynamically affect how text is inserted into an xml
element. I'd like to be able to use the following:
-- ========================================
===== --
declare @.x xml
,@.insert int
,@.insertType varchar(20)
set @.x = N'
<webpage>
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<info>Here is</info>
</webpage>
';
set @.insert = 3;
set @.insertType =
case @.insert
when 1 then ' into '
when 2 then ' as first into '
when 3 then ' as last into '
when 4 then ' before '
when 5 then ' after '
end
SET @.x.modify('insert text {" some text."}
sql:variable("@.insertType")
(/webpage/info)[1]');
select @.x;
select 'Insert type: "'+@.insertType+'"';
-- ========================================
===== --
The above doesn't work since sql:variable("@.insertType") is not
evaluated to the string ' as last into ' when the query is executed.
I've been able to get it to work by creating a query string and
executing it with sp_executesql but for longish strings it turns into
an apostrophe-escaping nightmare and quickly becomes very difficult to
read and debug.
Does anyone have any ideas how I could get this to work?
ThanksXQuery (neither our implementation nor the standard) does not provide for a
dynamic evaluation expression. So the only way to achieve what you want, is
to use the SQL Server dynamic SQL evaluation using either EXEC or
sp_executesql.
E.g.,
declare @.x xml
,@.insert int
,@.insertType nvarchar(20)
,@.stmtstr nvarchar(max)
set @.x = N'
<webpage>
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<info>Here is</info>
</webpage>
';
set @.insert = 3;
set @.insertType =
case @.insert
when 1 then N' into '
when 2 then N' as first into '
when 3 then N' as last into '
when 4 then N' before '
when 5 then N' after '
end
set @.stmtstr = N'DECLARE @.x xml; set @.x = N'''
+ cast(@.x as nvarchar(max))
+ '''; SET @.x.modify(''insert text {" some text."}'
+ @.insertType
+ N'(/webpage/info)[1]''); select @.x';
select @.stmtstr;
exec sp_executesql @.stmtstr;
select 'Insert type: "'+@.insertType+'"';
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1128331019.225989.284590@.g43g2000cwa.googlegroups.com...
> Hi
> I'm trying to dynamically affect how text is inserted into an xml
> element. I'd like to be able to use the following:
>
> -- ========================================
===== --
> declare @.x xml
> ,@.insert int
> ,@.insertType varchar(20)
> set @.x = N'
> <webpage>
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <info>Here is</info>
> </webpage>
> ';
> set @.insert = 3;
> set @.insertType =
> case @.insert
> when 1 then ' into '
> when 2 then ' as first into '
> when 3 then ' as last into '
> when 4 then ' before '
> when 5 then ' after '
> end
> SET @.x.modify('insert text {" some text."}
> sql:variable("@.insertType")
> (/webpage/info)[1]');
> select @.x;
> select 'Insert type: "'+@.insertType+'"';
> -- ========================================
===== --
>
> The above doesn't work since sql:variable("@.insertType") is not
> evaluated to the string ' as last into ' when the query is executed.
> I've been able to get it to work by creating a query string and
> executing it with sp_executesql but for longish strings it turns into
> an apostrophe-escaping nightmare and quickly becomes very difficult to
> read and debug.
> Does anyone have any ideas how I could get this to work?
> Thanks
>|||Yes, I wasn't sure if dynamic evaluation was supported in sql server's
implementation of XQuery. Thanks for the confirmation - much
appreciated.

Friday, February 24, 2012

Can I store a file in a SQL Server Database?

I have SQL Server 2000 and I have to make a table for storing Word, Excel or maybe some other Microsoft Office files. What data type should I use for this? Can I search through the stored files by words in a stored documents using a query and how?
Also the database where this table is created should provide similar functionality as a file server open, edit, check in, and check out on stored files. Can I do this in SQL Server 2000?As fare as I know, SQL Server has no specific support for office files.

So, you have the choice between re-inventing the functionality of document information systems, or to buy some. These systems are storing office files as BLOBs to keep all controls, and they are storing the text in a separate TEXT field to enable searching.

However, there is a third way: store only the path to the documents on your netwerk, and use Office's functionality to prevent users to open the same document twice.

Can I store a file in a SQL Server Database?

I have SQL Server 2000 and I have to make a table for storing Word, Excel or maybe some other Microsoft Office files. What data type should I use for this? Can I search through the stored files by words in a stored documents using a query and how?
Also the database where this table is created should provide similar functionality as a file server open, edit, check in, and check out on stored files. Can I do this in SQL Server 2000?You can store documents in the text or image data type.

You probably can't search on them, but your could store a summary in a character-based field with the record, or a column that just contains keywords.

Yes, you can provide functionality similar to a file-server...you just have to write it all yourself. :(

I can tell you that you are looking at a daunting project here that will likely involve complex coding. The user-interface will probably also prove difficult.

Perhaps somebody else on the forum will see this post and could recommend some professionally designed 3rd party software for document management?

blindman

Sunday, February 12, 2012

Can I output the result of sp_spaceused to a table ?

Firstly, I created a table : test01 as follow :
Field Data type
db_name char (80)
db_size char (20)
unallocated_space char (20)
reserved char (20)
data char (20)
index_size char (20)
unused char (20)
Then, I executed this command in SQL Analyzer :
INSERT INTO test01 EXEC sp_spaceused
But I only got this result :
Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
Insert Error: Column name or number of supplied values does not match table
definition
Can I output the result of : EXEC sp_spaceused to a table ?
Was my SQL statement correct or incorrect ?sp_spaceused has two result sets, so no you can't do that directly...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c8t0tc$91f2@.imsp212.netvigator.com...
> Firstly, I created a table : test01 as follow :
> Field Data type
> db_name char (80)
> db_size char (20)
> unallocated_space char (20)
> reserved char (20)
> data char (20)
> index_size char (20)
> unused char (20)
> Then, I executed this command in SQL Analyzer :
> INSERT INTO test01 EXEC sp_spaceused
> But I only got this result :
> Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
> Insert Error: Column name or number of supplied values does not match
table
> definition
>
> Can I output the result of : EXEC sp_spaceused to a table ?
> Was my SQL statement correct or incorrect ?
>
>|||You can do that at a table level:
Example for Northwind Employees table.
Create Table #Temp1
(Tablename CHAR(80),
rows int,
un_space VARCHAR(50),
reserved VARCHAR(50),
data VARCHAR(50),
index_size VARCHAR(50))
GO
Insert into #Temp1
Exec sp_spaceused 'Employees'
GO
Select * from #Temp1
GO
Drop table #Temp1
GO
>--Original Message--
>Firstly, I created a table : test01 as follow :
>Field Data type
>db_name char (80)
>db_size char (20)
>unallocated_space char (20)
>reserved char (20)
>data char (20)
>index_size char (20)
>unused char (20)
>Then, I executed this command in SQL Analyzer :
>INSERT INTO test01 EXEC sp_spaceused
>But I only got this result :
>Server: Msg 213, Level 16, State 7, Procedure
sp_spaceused, Line 142
>Insert Error: Column name or number of supplied values
does not match table
>definition
>
>Can I output the result of : EXEC sp_spaceused to a
table ?
>Was my SQL statement correct or incorrect ?
>
>.
>|||Try looking at the code in master..sp_spaceused. You could probably pick
out the portions into separate procs. Would loose the features of new
sp_spaceused during a SQL Server version upgrade but if you are desperate
enough this is an option.
-Paritosh
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eoF5B4ZQEHA.3744@.TK2MSFTNGP10.phx.gbl...
> sp_spaceused has two result sets, so no you can't do that directly...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c8t0tc$91f2@.imsp212.netvigator.com...
> > Firstly, I created a table : test01 as follow :
> >
> > Field Data type
> > db_name char (80)
> > db_size char (20)
> > unallocated_space char (20)
> > reserved char (20)
> > data char (20)
> > index_size char (20)
> > unused char (20)
> >
> > Then, I executed this command in SQL Analyzer :
> >
> > INSERT INTO test01 EXEC sp_spaceused
> >
> > But I only got this result :
> >
> > Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
> > Insert Error: Column name or number of supplied values does not match
> table
> > definition
> >
> >
> > Can I output the result of : EXEC sp_spaceused to a table ?
> > Was my SQL statement correct or incorrect ?
> >
> >
> >
>|||> Would loose the features of new
> sp_spaceused during a SQL Server version upgrade but if you are desperate
> enough this is an option.
Right, but to be honest, that is slightly safer than relying on the sp_ to
not change... since if you *could* do insert #table exec sp_spaceused and
the resultset changes, suddenly your code breaks. If your code is merely
*based on* the sp_ then it won't break when the sp_ changes.
Then again, your code would likely rely on calls to system tables such as
sysindexes etc. which are going to change and which will eventually
disappear, so neither method would be truly safe...
A|||Thanks to everybodies.
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:115ac01c441a1$917301b0$a001280a@.phx.gbl...
> You can do that at a table level:
> Example for Northwind Employees table.
>
> Create Table #Temp1
> (Tablename CHAR(80),
> rows int,
> un_space VARCHAR(50),
> reserved VARCHAR(50),
> data VARCHAR(50),
> index_size VARCHAR(50))
> GO
> Insert into #Temp1
> Exec sp_spaceused 'Employees'
> GO
> Select * from #Temp1
> GO
> Drop table #Temp1
> GO
>
> >--Original Message--
> >Firstly, I created a table : test01 as follow :
> >
> >Field Data type
> >db_name char (80)
> >db_size char (20)
> >unallocated_space char (20)
> >reserved char (20)
> >data char (20)
> >index_size char (20)
> >unused char (20)
> >
> >Then, I executed this command in SQL Analyzer :
> >
> >INSERT INTO test01 EXEC sp_spaceused
> >
> >But I only got this result :
> >
> >Server: Msg 213, Level 16, State 7, Procedure
> sp_spaceused, Line 142
> >Insert Error: Column name or number of supplied values
> does not match table
> >definition
> >
> >
> >Can I output the result of : EXEC sp_spaceused to a
> table ?
> >Was my SQL statement correct or incorrect ?
> >
> >
> >
> >.
> >

Sunday, March 25, 2012

Can not create Job on SQL2005

Can not create Job on SQL2005

Can not create Job on SQL2005

Can not create Job on SQL2005

Can not create Job on SQL2005

Can not create Job on SQL2005

Thursday, March 8, 2012

Can I use table data type in a trigger?

Wednesday, March 7, 2012

Can I use sql:variable() to change insert type from 'into' to 'as first into' ?

Can I use sql:variable() to change insert type from 'into' to 'as first into' ?

Friday, February 24, 2012

Can I store a file in a SQL Server Database?

Can I store a file in a SQL Server Database?

Sunday, February 12, 2012

Can I output the result of sp_spaceused to a table ?

install SQL on WinXP

Blog Archive

About Me