connect from ASP app: February 2012

Saturday, February 25, 2012

Can't create a setup project for project referencing Microsoft.SqlServer.ManagedDTS

I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package.

I took a SQL Server 2000 dts package and using the SQL Server 3005 legacy tools migrated it so I could still use the package withing SQL 2005 until I can build one using BI/SSIS.

Anyway,I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands:

Dim oApp As New Application
Dim oPkg As New Package
oPkg = oApp.LoadPackage(g_DTSx_Directory & "AOC copy Generic1 CSV to AOC_verify_file_1.dtsx", Nothing)
Dim oResults As DTSExecResult
oResults = oPkg.Execute

Ok. That works fine. Executes without a hitch. So now I try and create a setup project for this and I use the setup wizard.

During the creation of the setup project I get a message that states:
The following files may have dependencies that cannot be determined automatically. Please confirm that all dependencies have been added to the project.
C:\windows\system32\msxml6.dll

OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have.

How do I find this out?

Has anyone else created a project like this and experenced the same?

I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.

Thank you.

I received the answer to the above problem here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1195465&SiteID=1

Can't create a setup project for project referencing Microsoft.SqlServer.ManagedDTS

I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package.

I took a SQL Server 2000 dts package and using the SQL Server 3005 legacy tools migrated it so I could still use the package withing SQL 2005 until I can build one using BI/SSIS.

Anyway,I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands:

Ok. That works fine. Executes without a hitch. So now I try and create a setup project for this and I use the setup wizard.

OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have.

How do I find this out?

Has anyone else created a project like this and experenced the same?

I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.

Thank you.

I received the answer to the above problem here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1195465&SiteID=1

Can't create a secondary index on a bit column

quote:

> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.

|||Amy
There are some (using your word 'bugs') with EM
It does work with QA so be happy.
Note: You are right that it's a bad idea to have index on 'bit' column.
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...

quote:

> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.

Can't create a secondary index on a bit column

I am trying to create a secondary index on mult. columns
and one of them is a bit (I know you should not but that
is what they want). Using Enterprise Manager this does not
work, I get the following error:
Primary key or index cannot be created on column '<0s>'
because its data type is 'bit'.
If I use Query Analyzer it works fine.
My question is: Is this a bug in Enterprise Manager that
it does not let me or a bug in Query Analyzer that it does
let me?
Thanks.Don't know where is wrong. But you can check with QA's execution plan if
you have adequate data set -- use a situation that the new index is suitable
and run a query to see whether ther index is used. Or you can also sysindex
and sysindexkeys to look at.
Quentin
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...
> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.|||Amy
There are some (using your word 'bugs') with EM
It does work with QA so be happy.
Note: You are right that it's a bad idea to have index on 'bit' column.
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...
> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.

Can't create a once existed database with its name

Hello, there,
Some time ago, there was a database with a name sts_servername_1.
God knows what I did, but it's now gone. I most probably have deleted it, or
maybe detached and never reatached. The fact is, I don't care about it and
its data anymore. I want it gone. There are no files of it anywhere. I just
want to create a new blank database with a same name.
And I can't. It says "database already exists". But running sp_databases
doesn't show it. Also if I try to "drop database" it says there's no such
database in the system catalogue. Could you please help me do something?
Thanks,
Mantvydas
hi Mantvydas,
Mantvydas wrote:
> Hello, there,
> Some time ago, there was a database with a name sts_servername_1.
> God knows what I did, but it's now gone. I most probably have deleted
> it, or maybe detached and never reatached. The fact is, I don't care
> about it and its data anymore. I want it gone. There are no files of
> it anywhere. I just want to create a new blank database with a same
> name.
> And I can't. It says "database already exists". But running
> sp_databases doesn't show it. Also if I try to "drop database" it
> says there's no such database in the system catalogue. Could you
> please help me do something?
> Thanks,
> Mantvydas
it sometimes happens that db names become '', thats' to say the relative
column [name] have been cleared... o not ask why.. I do not know...
try executing
SELECT * FROM master..sysdatabases
and see if you have an entry with [name] = ''
if this is the case, verify qhet the [filename] colum reports... that's the
physical data file to which the '' db points to..
rename the database accordingly and verify everithings ok
(http://msdn.microsoft.com/library/de..._dbcc_00gy.asp)
eventually drop the db
(http://msdn.microsoft.com/library/de...de-dz_82lh.asp)
if this is a viable solution...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Can't create a ODBC DSN to a remote MSDE instance

Hello,
I need to create an ODBC DNS that points to a remote MSDE instance, but I
can only get it to work if I turn Windows Firewall off. Both the local and
remote machines are WinXP SP2 and have SQL2000 and MSDE, same domain and I
can login as the same person on each machine.
On the remote machine I've enabled TCP/IP and the default port, and I've
added that port to the firewall, but when I try to add a DSN (on the local
machine) in the ODBC control panel it says
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
I've looked at the instance via Enterprise Manager and my domain login has
admin privs, and can connect a DSN to the SQL2000 server on that machine.
Stumped.
Suggestions?
Keith
hi Keith,
Keith wrote:
> Hello,
> I need to create an ODBC DNS that points to a remote MSDE instance,
> but I can only get it to work if I turn Windows Firewall off. Both
> the local and remote machines are WinXP SP2 and have SQL2000 and
> MSDE, same domain and I can login as the same person on each machine.
> On the remote machine I've enabled TCP/IP and the default port, and
> I've added that port to the firewall, but when I try to add a DSN (on
> the local machine) in the ODBC control panel it says
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
> exist or access denied.
> I've looked at the instance via Enterprise Manager and my domain
> login has admin privs, and can connect a DSN to the SQL2000 server on
> that machine.
did you define an exception on the server's firewall on the MSDE used TCP/IP
port or on the service itself?
http://support.microsoft.com/default.aspx?kbid=841249
http://support.microsoft.com/default.aspx?scid=kb;[LN];841251
http://support.microsoft.com/default.aspx?scid=kb;[LN];884012 (Win2003)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>
> did you define an exception on the server's firewall on the MSDE used
> TCP/IP port or on the service itself?
> http://support.microsoft.com/default.aspx?kbid=841249
> http://support.microsoft.com/default.aspx?scid=kb;[LN];841251
> http://support.microsoft.com/default.aspx?scid=kb;[LN];884012 (Win2003)
Well, after some reboots and a walk around the block it works now.
I finally got it to work either with firewall port exception, or a service
exception, but neither way seemed very consistent. Making changes to the
firewall while it is running just doesn't seem to work that well; perhaps I
wasn't waiting long enough for the changes to propagate or I just need to
stop/start the firewall after changing something.
And I suspect that some of the changes that don't "require" rebooting really
do.
Anyway, thanks for the quick response and support links.
Keith

Can't create a new record using Access?

Please forgive this basic question but I am just getting started. I am using
an Access project file connected to an SQL table. I used the Enterprise
Manager to create a simple table with only 3 fields. When I open the
database from the Tables section in Access it will not let me add a new
record. I tried looking at every possible option but I am stumped and none
of the books I have address this issue. Any suggestion would be greatly
appreciated.
Thanks,
Steve P.What does "not let me" mean? Do you get an error message? What is it?
"Steve P" <sprovoyeur@.cox.net> wrote in message
news:J3%Ib.17521$WQ3.11463@.lakeread05...

quote:

> Please forgive this basic question but I am just getting started. I am

using

quote:

> an Access project file connected to an SQL table. I used the Enterprise
> Manager to create a simple table with only 3 fields. When I open the
> database from the Tables section in Access it will not let me add a new
> record. I tried looking at every possible option but I am stumped and none
> of the books I have address this issue. Any suggestion would be greatly
> appreciated.
> Thanks,
> Steve P.
>
>

Can't create a new publication

Hi,
I'm trying to create a publication in a SQL Server 2005 server. I
right clicked on Replication tab in management studio and selected
'New Publication'. The management studio waits for a long time and
then nothing happens. It does not start any 'New Publication' wizard
nor does it report any error. Could you please let me know if there
are any server settings that need to be enabled before configuring
Publications.
Thanks and Regards,
Thyagu.D
Does anything appear in the sql server log, or the windows log? What happens
if you run sp_adddistributor and sp_adddistributiondb directly?
Cheers,
Paul Ibison
|||You probably have some locking going on from a previous replication process.
Issue calls to sp_who2 or select * from sysprocesses where blocked<>0 order
by waittime desc to see if you can see these processes and kill them.
You might also want to try to create they publication by using replication
stored procedures.
Try sp_replicationdboption 'MyDatabase', 'published', true for a start.
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
"Thyagu" <tdelli@.gmail.com> wrote in message
news:1184566869.270526.315190@.e16g2000pri.googlegr oups.com...
> Hi,
> I'm trying to create a publication in a SQL Server 2005 server. I
> right clicked on Replication tab in management studio and selected
> 'New Publication'. The management studio waits for a long time and
> then nothing happens. It does not start any 'New Publication' wizard
> nor does it report any error. Could you please let me know if there
> are any server settings that need to be enabled before configuring
> Publications.
> Thanks and Regards,
> Thyagu.D
>
|||On Jul 16, 2:14 pm, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
> Does anything appear in the sql server log, or the windows log? What happens
> if you run sp_adddistributor and sp_adddistributiondb directly?
> Cheers,
> Paul Ibison
This worked. Thanks!
Regards,
Thyagu.D

Can't create a new FT Catalog

Hi
We are having a problem creating a Full-Text catalog on our production
server and would appreciate any pointers to a solution.
The scenario is as follows:
1. Existing version of product running perfectly happily on production
server with Full Text indexing enabled and running. Lets call it Server 1
(Running SQL 2000 stndard sp3 on W2K Server )
2. New product developed on development server (Server 2). Full-text
indexing enabled and running.
3. Database transferred from Server 2 to Server 1 using DTS
4. Attempted to create FT catalog on new database on Server 1 using
Enterprise Manager. Failed, error given is:
An unknown full-text failure (80004005) occurred in function EnumCatalogs on
full-text catalog
Meanwhile it is still possible to create a new FT catalog on the existing db
(still on server 1)
We have done the usual web-searching to find a solution but anything turned
up appears to address the issue that it is impossible to create a FT catalog
on the server, this isn't our case, our problem is that we can't create the
FT index on the new db.
Any ideas?
Also if you need more info please ask.
Regards
Russell Wyatt
have you referred to this kb article yet?
http://support.microsoft.com/default...b;en-us;295772
"Russell Wyatt" <russellw_REMOVE_THIS_TO_REPLY_@.ihs.com.au> wrote in message
news:%23VcY80SxEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Hi
> We are having a problem creating a Full-Text catalog on our production
> server and would appreciate any pointers to a solution.
> The scenario is as follows:
> 1. Existing version of product running perfectly happily on production
> server with Full Text indexing enabled and running. Lets call it Server 1
> (Running SQL 2000 stndard sp3 on W2K Server )
> 2. New product developed on development server (Server 2). Full-text
> indexing enabled and running.
> 3. Database transferred from Server 2 to Server 1 using DTS
> 4. Attempted to create FT catalog on new database on Server 1 using
> Enterprise Manager. Failed, error given is:
> An unknown full-text failure (80004005) occurred in function EnumCatalogs
> on
> full-text catalog
> Meanwhile it is still possible to create a new FT catalog on the existing
> db
> (still on server 1)
> We have done the usual web-searching to find a solution but anything
> turned
> up appears to address the issue that it is impossible to create a FT
> catalog
> on the server, this isn't our case, our problem is that we can't create
> the
> FT index on the new db.
> Any ideas?
> Also if you need more info please ask.
> Regards
> Russell Wyatt
>
|||Thanks Hilary
Yes, we have referred to the article. (I say "we" because my colleague and
myself are trying to solve the problem). However, we can create a full-text
catalog on a different (already existing) database just not on the new one.
So catalog creation isn't completely broken, just for new databases. AFAIK
the kb article is useful if catalog creation doesn't work at all.
Russell
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eBqVoSTxEHA.2624@.TK2MSFTNGP11.phx.gbl...
> have you referred to this kb article yet?
> http://support.microsoft.com/default...b;en-us;295772
> "Russell Wyatt" <russellw_REMOVE_THIS_TO_REPLY_@.ihs.com.au> wrote in
message[vbcol=seagreen]
> news:%23VcY80SxEHA.2172@.TK2MSFTNGP14.phx.gbl...
1[vbcol=seagreen]
EnumCatalogs[vbcol=seagreen]
existing
>

Can't create a Maint Plan in 2005

I am trying to create a maint plan in 2005 and no matter what types of
things I tell it to do, when it goes to create the plan I get the following
error message:
Create maint plan failed.
Additional information:
create failed for jobstep 'Subplan'
An exception occurred while executing a Transact-SQL statement or
batch.
The specified '@.subsystem' is invalid (valid values are returned
by sp_enum_sqlagent_subsystems) (Microsoft SQL Server Error:14234)
It's on a clustered system.
TIA - Jeff.Do you have SQL Agent running and do you have SSIS installed and running?
Andrew J. Kelly SQL MVP
"UJ" <fred@.nowhere.com> wrote in message
news:epT9r3uQGHA.5808@.TK2MSFTNGP12.phx.gbl...
>I am trying to create a maint plan in 2005 and no matter what types of
>things I tell it to do, when it goes to create the plan I get the following
>error message:
> Create maint plan failed.
> Additional information:
> create failed for jobstep 'Subplan'
> An exception occurred while executing a Transact-SQL statement or
> batch.
> The specified '@.subsystem' is invalid (valid values are
> returned by sp_enum_sqlagent_subsystems) (Microsoft SQL Server
> Error:14234)
> It's on a clustered system.
> TIA - Jeff.
>

Can't create a Maint Plan in 2005

I am trying to create a maint plan in 2005 and no matter what types of
things I tell it to do, when it goes to create the plan I get the following
error message:
Create maint plan failed.
Additional information:
create failed for jobstep 'Subplan'
An exception occurred while executing a Transact-SQL statement or
batch.
The specified '@.subsystem' is invalid (valid values are returned
by sp_enum_sqlagent_subsystems) (Microsoft SQL Server Error:14234)
It's on a clustered system.
TIA - Jeff.
Do you have SQL Agent running and do you have SSIS installed and running?
Andrew J. Kelly SQL MVP
"UJ" <fred@.nowhere.com> wrote in message
news:epT9r3uQGHA.5808@.TK2MSFTNGP12.phx.gbl...
>I am trying to create a maint plan in 2005 and no matter what types of
>things I tell it to do, when it goes to create the plan I get the following
>error message:
> Create maint plan failed.
> Additional information:
> create failed for jobstep 'Subplan'
> An exception occurred while executing a Transact-SQL statement or
> batch.
> The specified '@.subsystem' is invalid (valid values are
> returned by sp_enum_sqlagent_subsystems) (Microsoft SQL Server
> Error:14234)
> It's on a clustered system.
> TIA - Jeff.
>

Can't create a Maint Plan in 2005

I am trying to create a maint plan in 2005 and no matter what types of
things I tell it to do, when it goes to create the plan I get the following
error message:
Create maint plan failed.
Additional information:
create failed for jobstep 'Subplan'
An exception occurred while executing a Transact-SQL statement or
batch.
The specified '@.subsystem' is invalid (valid values are returned
by sp_enum_sqlagent_subsystems) (Microsoft SQL Server Error:14234)
It's on a clustered system.
TIA - Jeff.Do you have SQL Agent running and do you have SSIS installed and running?
--
Andrew J. Kelly SQL MVP
"UJ" <fred@.nowhere.com> wrote in message
news:epT9r3uQGHA.5808@.TK2MSFTNGP12.phx.gbl...
>I am trying to create a maint plan in 2005 and no matter what types of
>things I tell it to do, when it goes to create the plan I get the following
>error message:
> Create maint plan failed.
> Additional information:
> create failed for jobstep 'Subplan'
> An exception occurred while executing a Transact-SQL statement or
> batch.
> The specified '@.subsystem' is invalid (valid values are
> returned by sp_enum_sqlagent_subsystems) (Microsoft SQL Server
> Error:14234)
> It's on a clustered system.
> TIA - Jeff.
>

Can't create a linked server when the servers are set up to replicate

I can't create a linked server when the same two servers are set up for repl
icatation. I get 'Error 15028: The server 'SERVERNAME' already exists.' whe
n I try to add a linked server. And it does exist as a Remote Server becaus
e as I mentioned, the two s
ervers are replicating data.
There has to be a way to add a linked server when two servers are already re
plicating. Or, there has to be a way to use the remote servers for data acc
ess.
Any suggestons?
Thanks,
SniperXYou may have to use sp_addlinkedsrvlogin . This was new for SQL 2000
remote servers. There's also a kb article documenting this issue.
274098 PRB: Adding a Linked Server Causes Error 15028
http://support.microsoft.com/?id=274098
From Books Online:
Setting Up the Local Server
In SQL Server 2000, create remote server connections for remote server
logins created by Windows Authentication by:
Setting up a local login mapping on a local server that defines what login
and password are used by an instance of SQL Server when it makes an RPC
connection to a remote server.
For logins created by Windows Authentication, you must create a mapping to
a login name and password. This login name and password must match the
incoming login and password expected by the remote server.
Using the sp_addlinkedsrvlogin stored procedure to create local login
mappings.
Note For logins created by SQL Server Authentication, it is not necessary
to create any local login mappings for executing a stored procedure against
a remote server.
Security Note When possible, use Windows Authentication.
274098 PRB: Adding a Linked Server Causes Error 15028
http://support.microsoft.com/?id=274098
When configuring new subscribers for replication, SQL Server adds the
subscriber as a remote server and stores its information in the
master..sysservers system table. Subsequent attempts to add a linked server
using the same name as an existing subscriber may cause error 15028 to
occur:
Server: Msg 15028, Level 16, State 1, Line 0
The server 'SQL1' already exists.
The article also references the use of sp_addlinkedsrvlogin
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Cant create a foreign key using non_primary key fields

Howdy all !
I'm just a bit on the frustrated side cause I want to create a foreign key but SQL Server won't let me. :(

I have table A with a primary key of main_id
I have table B with a primary key of another_id

Table A has a corresponding field called another_id.

I'm trying to create a foreign key between Table A & Table B on another_id but since it's not the Primary Key in Table A I get the following error:

There are no primary or candidate keys in the referenced table 'A' that match the referencing column list in the foreign key 'fk_classB_classA'.

Am I missing something totally obvious here? Why should I have to create a foreign key on a primary key?

What I find interesting is that I can create the relationship from enterprise manager but when I script it out is when I run into problems.

Here's the script I'm using:

Begin Code

alter table B add
constraint fk_classB_classA foreign key
(classB) references A (classB)
on delete no action
on update no action

End Code

Any help is greatly appreciated.

tamAdd a UNIQUE constraint and then SQL Server will oblige. It won't let you create an FK relationship to a column that could have more than one row with the "target" value, due to the chaos that can cause.

-PatP|||Hey Pat,
Many thanks for the quick response and I'll give that a try, but could you explain to me why it does it from enterprise manager? If I go into diagrams and drag and drop from one table to the other it comes up as a foreign key not a unique constraint.

Is it actually creating a unique constraint behind the curtain and labeling it as a Foreign key?

I'm more curious than anything. To me it just doesn't make much sense.

Again, thanks for the answer.
tam|||I don't think EM is actually creating FK relationship on the columns that you think it does. Right-mouse click on the relationship and select Properties, and see what fields participate in the relationship.|||The two ideas (UNIQUE CONSTRAINT and Foreign Key) are related, but they are decidedly not the same thing.

A unique constraint means that the column(s) that the constraint applies to are unique within the table, only one row can exist with a particular value. As an example, you might have an employee table that has an EmployeeID column which is the Primary Key, and an SSN column that is Unique. There can be only one row with any given value of EmployeeID, and NULL values are never allowed. There can be NULL values for SSN (which is good, since some employees may not have one), but the database engine won't allow two rows to have the same value.

Various tables in your schema would link to your employee table using the EmployeeID as a Foreign Key. You might get a table from the IRS that has employee data organized by SSN, and you could make that SSN a Foreign Key to the employee table too (because it has a Unique constraint). Not every employee may have this detail data, but the detail can apply to only one employee.

You might get data from a shoe manufacturer telling you about their schwell new work shoes. Even if they provide information by shoe size, you can't make the shoe size a foreign key to employee, because in the employee table the shoe size column couldn't have a unique constraint (at least not in most companies anyway!).

-PatP|||UNIQUE constraint on SSN? Then there may be only 1 employee without a valid unique SSN, because UNIQUE constraint will allow only 1 NULL-valued row for that field.|||Well I'll be horny-swoggled! I'd forgotten that they'd changed that. Good catch!

Ok, so much for my sterling example then... It used to hold water, long ago and far away!

-PatP

Can't create a BCP format file

Hi there,
does anyone know why I cannot create a bcp format file? I get
the following error. If I change the command to queryout instead of format,
it logs on OK and creates an output file. So why am I getting the error
below?
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'select convert(integer, p'. Login fails.
Waldy,
Please post your bcp command line. As it is, bcp apparently believes that
your login is named
'select convert(integer, p', which is surely not correct.
RLF
"Waldy" <someone@.microsoft.com> wrote in message
news:u1fydnTUIHA.5836@.TK2MSFTNGP04.phx.gbl...
> Hi there,
> does anyone know why I cannot create a bcp format file? I get
> the following error. If I change the command to queryout instead of
> format, it logs on OK and creates an output file. So why am I getting the
> error below?
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open
> database requested in login 'select convert(integer, p'. Login fails.
>
|||Waldy (someone@.microsoft.com) writes:
> does anyone know why I cannot create a bcp format file? I get the
> following error. If I change the command to queryout instead of format,
> it logs on OK and creates an output file. So why am I getting the error
> below?
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open
> database requested in login 'select convert(integer, p'. Login fails.
It seems to be a bug in BCP for SQL 2000. I got a similar error for this
command line:
bcp "SELECT CustomerID, COUNT(*) AS cnt FROM Northwind..Orders GROUP BY CustomerID" format xxx -f slask.fmt -T -n
with SQL 2000. With SQL 2005, the error message was more terse:
An error occurred while processing the command line.
The error in SQL 2000 actually makes sense. You are not saying QUERYOUT,
which means that what you have in the first argument is a table. But since
that database and table does not exist...
In any case, since the error message on SQL 2005 is complete crap, I
filed a bug report
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=321353
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Can't create a backup job in SQL2005

Maintenance plans need "SQL Integration Services" installed to work.
Peter Lawton
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>I spoke too soon, I'm getting exactly the same issue on a second server
>I've just upgraded from SQL 2000 to SQL 2005
> If I try to create a new maintenance job or migrate any of the legace
> maintenace jobs it fails with the error:-
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
> Error Number: 14234
> Severity: 16
> State: 1
> Procedure: sp_verify_subsystem
> Line Number: 21
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>|||Yes as I stated SQL Server Integration Services or "SSIS" for short must be
running. But by learning the hard way you won't have that problem again<g>.
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
> OK, I've found the problem

> Maintenance plans need "SQL Integration Services" installed to work.
> Peter Lawton
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>|||Yes, you're very right

I expect it's a mistake a lot of people will make though as it's not at all
obvious why an optional component is needed for basic tasks, and the error
message isn't at all helpful.
Peter Lawton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OuI9JcjBGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Yes as I stated SQL Server Integration Services or "SSIS" for short must
> be running. But by learning the hard way you won't have that problem
> again<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
>

Can't create a backup job in SQL2005

Maintenance plans need "SQL Integration Services" installed to work.
Peter Lawton
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>I spoke too soon, I'm getting exactly the same issue on a second server
>I've just upgraded from SQL 2000 to SQL 2005
> If I try to create a new maintenance job or migrate any of the legace
> maintenace jobs it fails with the error:-
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
> Error Number: 14234
> Severity: 16
> State: 1
> Procedure: sp_verify_subsystem
> Line Number: 21
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||Yes as I stated SQL Server Integration Services or "SSIS" for short must be
running. But by learning the hard way you won't have that problem again<g>.
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
> OK, I've found the problem

Can't create a backup job in SQL2005

I'm trying to create a simple backup job using the maintenance wizard in SQL
2005 and get the error below, anyone know what I'm doing wrong? (of course
the "For help click" link goes nowhere as usual)
Thanks
Maintenance Plan Wizard Progress
- Creating maintenance plan "MaintenancePlan" (Error)
Messages
Create maintenance plan failed.
--
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "MaintenancePlan" (Stopped)Make sure both SQL Agent and SSIS services are running.
--
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a simple backup job using the maintenance wizard in
> SQL 2005 and get the error below, anyone know what I'm doing wrong? (of
> course the "For help click" link goes nowhere as usual)
> Thanks
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "MaintenancePlan" (Error)
> Messages
> Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "MaintenancePlan" (Stopped)
>|||Very weird, everything looked OK, but I completely un-installed SQL2005 and
re-installed it and everything works now, no differences in the two installs
that I know of.
Anyway, problem fixed now - thanks for the help
Peter Lawton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
> Make sure both SQL Agent and SSIS services are running.
> --
> Andrew J. Kelly SQL MVP
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard in
>> SQL 2005 and get the error below, anyone know what I'm doing wrong? (of
>> course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>|||I spoke too soon, I'm getting exactly the same issue on a second server I've
just upgraded from SQL 2000 to SQL 2005
If I try to create a new maintenance job or migrate any of the legace
maintenace jobs it fails with the error:-
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
Error Number: 14234
Severity: 16
State: 1
Procedure: sp_verify_subsystem
Line Number: 21
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Very weird, everything looked OK, but I completely un-installed SQL2005
> and re-installed it and everything works now, no differences in the two
> installs that I know of.
> Anyway, problem fixed now - thanks for the help
> Peter Lawton
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>> Make sure both SQL Agent and SSIS services are running.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard in
>> SQL 2005 and get the error below, anyone know what I'm doing wrong? (of
>> course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>>
>|||OK, I've found the problem :)
Maintenance plans need "SQL Integration Services" installed to work.
Peter Lawton
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>I spoke too soon, I'm getting exactly the same issue on a second server
>I've just upgraded from SQL 2000 to SQL 2005
> If I try to create a new maintenance job or migrate any of the legace
> maintenace jobs it fails with the error:-
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
> Error Number: 14234
> Severity: 16
> State: 1
> Procedure: sp_verify_subsystem
> Line Number: 21
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Very weird, everything looked OK, but I completely un-installed SQL2005
>> and re-installed it and everything works now, no differences in the two
>> installs that I know of.
>> Anyway, problem fixed now - thanks for the help
>> Peter Lawton
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>> Make sure both SQL Agent and SSIS services are running.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard
>> in SQL 2005 and get the error below, anyone know what I'm doing wrong?
>> (of course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>>
>>
>|||Yes as I stated SQL Server Integration Services or "SSIS" for short must be
running. But by learning the hard way you won't have that problem again<g>.
--
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
> OK, I've found the problem :)
> Maintenance plans need "SQL Integration Services" installed to work.
> Peter Lawton
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>>I spoke too soon, I'm getting exactly the same issue on a second server
>>I've just upgraded from SQL 2000 to SQL 2005
>> If I try to create a new maintenance job or migrate any of the legace
>> maintenace jobs it fails with the error:-
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
>> Error Number: 14234
>> Severity: 16
>> State: 1
>> Procedure: sp_verify_subsystem
>> Line Number: 21
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Very weird, everything looked OK, but I completely un-installed SQL2005
>> and re-installed it and everything works now, no differences in the two
>> installs that I know of.
>> Anyway, problem fixed now - thanks for the help
>> Peter Lawton
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>> Make sure both SQL Agent and SSIS services are running.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard
>> in SQL 2005 and get the error below, anyone know what I'm doing wrong?
>> (of course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>>
>>
>>
>|||Yes, you're very right :)
I expect it's a mistake a lot of people will make though as it's not at all
obvious why an optional component is needed for basic tasks, and the error
message isn't at all helpful.
Peter Lawton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OuI9JcjBGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Yes as I stated SQL Server Integration Services or "SSIS" for short must
> be running. But by learning the hard way you won't have that problem
> again<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
>> OK, I've found the problem :)
>> Maintenance plans need "SQL Integration Services" installed to work.
>> Peter Lawton
>> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
>> news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>>I spoke too soon, I'm getting exactly the same issue on a second server
>>I've just upgraded from SQL 2000 to SQL 2005
>> If I try to create a new maintenance job or migrate any of the legace
>> maintenace jobs it fails with the error:-
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
>> Error Number: 14234
>> Severity: 16
>> State: 1
>> Procedure: sp_verify_subsystem
>> Line Number: 21
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Very weird, everything looked OK, but I completely un-installed SQL2005
>> and re-installed it and everything works now, no differences in the two
>> installs that I know of.
>> Anyway, problem fixed now - thanks for the help
>> Peter Lawton
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>> Make sure both SQL Agent and SSIS services are running.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard
>> in SQL 2005 and get the error below, anyone know what I'm doing
>> wrong? (of course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>>
>>
>>
>>
>

Can''t Creat Subscriptions

We're new to SQL Reporting Services and have a new SQL 2005 server.

I'm having a problem activating scubscriptions.

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error;

"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)"

Any suggestions?

Found the problem.

When configuring the Data Source the "Impersonate the authenticated user after a connection has been made to the data source" button should be off.

Thanks for the tip!|||Be careful! This might sound very fool but sometimes the problem is that the paramethers are bad passed to the report, or the report's paramethers has non-english characters, like '?'.

I've passed 'a?o' (year, in spanish) as a paramether, and nothing seems to work'till I changed that.

I hope this help all of you.

Saludos desde el sur del mundo.|||What screen are you configuring the data source? I can't find the button you are referring to.

Can't Creat Subscriptions

We're new to SQL Reporting Services and have a new SQL 2005 server.

I'm having a problem activating scubscriptions.

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error;

Any suggestions?

Found the problem.

When configuring the Data Source the "Impersonate the authenticated user after a connection has been made to the data source" button should be off.

I've passed 'a?o' (year, in spanish) as a paramether, and nothing seems to work'till I changed that.

I hope this help all of you.

Saludos desde el sur del mundo.|||

What screen are you configuring the data source? I can't find the button you are referring to.

Can't Creat Subscriptions

We're new to SQL Reporting Services and have a new SQL 2005 server.

I'm having a problem activating scubscriptions.

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error;

Any suggestions?

Found the problem.

When configuring the Data Source the "Impersonate the authenticated user after a connection has been made to the data source" button should be off.

I've passed 'a?o' (year, in spanish) as a paramether, and nothing seems to work'till I changed that.

I hope this help all of you.

Saludos desde el sur del mundo.|||

What screen are you configuring the data source? I can't find the button you are referring to.

Can't Creat Subscriptions

We're new to SQL Reporting Services and have a new SQL 2005 server.

I'm having a problem activating scubscriptions.

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error;

Any suggestions?

Found the problem.

When configuring the Data Source the "Impersonate the authenticated user after a connection has been made to the data source" button should be off.

I've passed 'a?o' (year, in spanish) as a paramether, and nothing seems to work'till I changed that.

I hope this help all of you.

Saludos desde el sur del mundo.|||

What screen are you configuring the data source? I can't find the button you are referring to.

Can't copy/export a database with identity NEWBIE

I have a database where some tables have the IDENTITY = YES.
If I copy or export from ServerA to ServerB, the IDENTITY gets set to No.
This has to be a simple problem.
How is it fixed?
If you are simply doing an export/import you need to create the table with
the desired format on the subscriber/destination/target side - i.e. with the
identity property on the column. Then before you do your import set
identity_insert on for that table, i.e.
SET IDENTITY_INSERT MyTableName ONIf you are using bcp use the -E parameter.
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
"Jeffrey Brandt" <jdbrandt@.verizon.net> wrote in message
news:lO7fi.268$XH5.206@.trndny02...
>I have a database where some tables have the IDENTITY = YES.
> If I copy or export from ServerA to ServerB, the IDENTITY gets set to No.
> This has to be a simple problem.
> How is it fixed?
>
|||You were SOO right.
I ended up using snapshot replication, and pushed it to the other machine.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBM0x%23YtHHA.1728@.TK2MSFTNGP06.phx.gbl...
> You can use queued updating subscribers or merge replication both with
> automatic identity range management.
> HTH,
> Paul Ibison
>

Can't copy databases across network with copy wizard

I am having trouble copying a database across a network using the copy database wizard.
I am receiving the following error:
'Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account to have the rights to copy files over the network.'
I have gone into services and set up the appropriate account to log unto the network servers with system admin priviledges but this does not solve the problem. Any step by step help would be appreciated.
Regards
You would need to change the destination server to run under
a domain account. Then you need to restart SQL service.
The configuration requirements for using the copy database
wizard can be found in the following article:
INF: Understanding and Troubleshooting the Copy Database
Wizard in SQL Server 2000
http://support.microsoft.com/?id=274463
-Sue
On Wed, 14 Apr 2004 11:26:05 -0700, "JackO"
<anonymous@.discussions.microsoft.com> wrote:

>I am having trouble copying a database across a network using the copy database wizard.
>I am receiving the following error:
>'Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account to have the rights to copy files over the network.'
>I have gone into services and set up the appropriate account to log unto the network servers with system admin priviledges but this does not solve the problem. Any step by step help would be appreciated.
>Regards
|||JackO
Sue answered you correctly. However you will have good results also if you are just willing to take the DB offline temporarily and copy over the files. Then you can attach the DB at the destination. This can be done without shutting down either server.
Jeff
|||Or...you can just backup and restore to the other server.
Nothing is offline, restarted, etc.
-Sue
On Wed, 14 Apr 2004 11:26:05 -0700, "JackO"
<anonymous@.discussions.microsoft.com> wrote:

>I am having trouble copying a database across a network using the copy database wizard.
>I am receiving the following error:
>'Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account to have the rights to copy files over the network.'
>I have gone into services and set up the appropriate account to log unto the network servers with system admin priviledges but this does not solve the problem. Any step by step help would be appreciated.
>Regards

Can't copy databases across network with copy wizard

I am having trouble copying a database across a network using the copy database wizard
I am receiving the following error
'Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account to have the rights to copy files over the network.
I have gone into services and set up the appropriate account to log unto the network servers with system admin priviledges but this does not solve the problem. Any step by step help would be appreciated
RegardsYou would need to change the destination server to run under
a domain account. Then you need to restart SQL service.
The configuration requirements for using the copy database
wizard can be found in the following article:
INF: Understanding and Troubleshooting the Copy Database
Wizard in SQL Server 2000
http://support.microsoft.com/?id=274463
-Sue
On Wed, 14 Apr 2004 11:26:05 -0700, "JackO"
<anonymous@.discussions.microsoft.com> wrote:
>I am having trouble copying a database across a network using the copy database wizard.
>I am receiving the following error:
>'Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account to have the rights to copy files over the network.'
>I have gone into services and set up the appropriate account to log unto the network servers with system admin priviledges but this does not solve the problem. Any step by step help would be appreciated.
>Regards|||Or...you can just backup and restore to the other server.
Nothing is offline, restarted, etc.
-Sue
On Wed, 14 Apr 2004 11:26:05 -0700, "JackO"
<anonymous@.discussions.microsoft.com> wrote:
>I am having trouble copying a database across a network using the copy database wizard.
>I am receiving the following error:
>'Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account to have the rights to copy files over the network.'
>I have gone into services and set up the appropriate account to log unto the network servers with system admin priviledges but this does not solve the problem. Any step by step help would be appreciated.
>Regards

Can't copy databases across network with copy wizard

I am having trouble copying a database across a network using the copy datab
ase wizard.
I am receiving the following error:
'Your SQL Server Service is running under the local system account. You need
to change your SQL Server Service account to have the rights to copy files
over the network.'
I have gone into services and set up the appropriate account to log unto the
network servers with system admin priviledges but this does not solve the p
roblem. Any step by step help would be appreciated.
RegardsYou would need to change the destination server to run under
a domain account. Then you need to restart SQL service.
The configuration requirements for using the copy database
wizard can be found in the following article:
INF: Understanding and Troubleshooting the Copy Database
Wizard in SQL Server 2000
http://support.microsoft.com/?id=274463
-Sue
On Wed, 14 Apr 2004 11:26:05 -0700, "JackO"
<anonymous@.discussions.microsoft.com> wrote:

>I am having trouble copying a database across a network using the copy data
base wizard.
>I am receiving the following error:
>'Your SQL Server Service is running under the local system account. You nee
d to change your SQL Server Service account to have the rights to copy files
over the network.'
>I have gone into services and set up the appropriate account to log unto th
e network servers with system admin priviledges but this does not solve the
problem. Any step by step help would be appreciated.
>Regards|||JackO
Sue answered you correctly. However you will have good results also if you
are just willing to take the DB offline temporarily and copy over the files.
Then you can attach the DB at the destination. This can be done without s
hutting down either server.
Jeff|||Or...you can just backup and restore to the other server.
Nothing is offline, restarted, etc.
-Sue
On Wed, 14 Apr 2004 11:26:05 -0700, "JackO"
<anonymous@.discussions.microsoft.com> wrote:

>I am having trouble copying a database across a network using the copy data
base wizard.
>I am receiving the following error:
>'Your SQL Server Service is running under the local system account. You nee
d to change your SQL Server Service account to have the rights to copy files
over the network.'
>I have gone into services and set up the appropriate account to log unto th
e network servers with system admin priviledges but this does not solve the
problem. Any step by step help would be appreciated.
>Regards

Cant copy database, strange error

I'm trying to copy a database from my web host to local Sql Server 2005 instance using the Copy Database wizard. I get this error:

An exception occurred while executing a Transact-SQL statement or batch.

Server user 'celestine' is not a valid user id database 'db2'.

However, 'db2' isn't the database I was trying to copy! I am trying to copy db1, but this error is complaining about db2, which is in fact somebody else's database that I don't have a login for. Why am I getting an error about db2 when I'm not trying to do anything with db2?

I can however export the tables from db1 just fine using Export. But then I have to go back and generate script to re-create the stored procs, views, etc., so it is rather tedious to do it that way.

This may be possible. For example if there is a view in db1 which references a table in db2, and the celestine login does not have access to db2, you may encounter this error.

So to avoid such issue, you can move/copy database by taking a full backup of the source database and then restore it on the destination server.

|||

Good idea but -- No, this is not the case. This is a shared hosting situation, the 2nd database is owned by someone else, I have absolutely no access to it at all. There is no relationship whatsoever between the databases.

I can't do a backup/restore either because I don't have access to the remote filesystem.

|||

Urgh...that's really a trouble. Then check whether your login has sufficient permissions to perform copy database task:

Required permissions

For the Copy Database Wizard to work, you must have the correct permissions, depending on how you are copying the database, as follows:

For the Detach and Attach Method, you must be a member of thesysadmin fixed server role on the both source and destination servers.

|||Right. But a) I do have correct permissions for both source/destination for the database I'm trying to copy, and b) The error is not pertaining to the database I want to copy. The error mentions a different database, that I have zero permissions for, that I will never have permissions for, that I don't want permissions for. I am trying to copy db1 and the error says "you don't have permissions for db2". Why is it even checking anything for db2?

Can't copy a database

I want to make a working copy of a database to a new name on the same server. I use the wizard giving the same name for source and destination, but giving the db copy a new name. I get an error that says "One or m ore arguments are invalid."

But no hint as to what is wrong.

Is it that I can't copy on the same machine?

You want to do a 'backup database' and 'restore database' to create a new database based on the old database.

Here is a script that you can use.

use master
go
declare @.db1 sysname,@.db2 sysname,
@.name1 sysname, @.name2 sysname,
@.file1 sysname,@.file2 sysname,
@.sql nvarchar(1000)

set @.db1='Northwind'
set @.db2='NewBD'

set @.sql='select @.name1=name,@.file1=filename from '+@.db1+'..sysfiles where fileid=1'
exec sp_executesql @.sql,N'@.name1 sysname out,@.file1 sysname out',@.name1 out,@.file1 out

set @.sql='select @.name2=name,@.file2=filename from '+@.db1+'..sysfiles where fileid=2'
exec sp_executesql @.sql,N'@.name2 sysname out,@.file2 sysname out',@.name2 out,@.file2 out

select @.name1=ltrim(rtrim(@.name1)),
@.name2=ltrim(rtrim(@.name2)),
@.file1=substring(@.file1,1,(len(@.file1)-charindex('\',reverse(rtrim(@.file1)))))+'\'+@.db2+'.mdf',
@.file2=substring(@.file2,1,(len(@.file2)-charindex('\',reverse(rtrim(@.file2)))))+'\'+@.db2+'.ldf'

set @.sql='backup database '+@.db1+' to disk=''c:\db1.tmp'' with init'
exec master..sp_executesql @.sql

set @.sql='restore database '+@.db2+' from disk=''c:\db1.tmp''
with replace,
move '+quotename(@.name1,char(39))+' to '+quotename(@.file1,char(39))+',
move '+quotename(@.name2,char(39))+' to '+quotename(@.file2,char(39))+',
recovery'

exec master..sp_executesql @.sql|||

I will give this a try. Thanks!

(But one wonders why simple stuff like this can't be handled by the GUI.)

Cant convert types

I ham trying to convert a 'System.Data.CommandType' to 'System.Web.UI.WebControls.SqlDataSourceCommandType'.

but I get

Error 4 Cannot implicitly convert type 'System.Data.CommandType' to 'System.Web.UI.WebControls.SqlDataSourceCommandType'. An explicit conversion exists (are you missing a cast?) D:\Inetpub\wwwroot\Help_Desk\App_Code\HelpDeskSql.cs 99 42 http://localhost/Help_Desk/

My problem is I cant seem to get a explicit conversion to work. if it cast it I get an.

Specified argument was out of the range of valid values.
Parameter name: value . when running the page. but no build errors, does anyone know how to cast this?

Show us your exact code or markup that you're talking about, which is giving you the error.|||

if (cmdUpdate !=null)

{

HDS.UpdateCommand = cmdUpdate.CommandText;

HDS.UpdateCommandType = (SqlDataSourceCommandType)cmdUpdate.CommandType; //errror line

for (int i = 0; i < cmdUpdate.Parameters.Count; i++)

{

HDS.UpdateParameters.Add(cmdUpdate.Parameters[i].ParameterName, cmdUpdate.Parameters[i].Value.ToString());

}

Cant convert record to integer

Hi All

I'm getting an error that says that this can't be converted to an integer.

Here is the line that gets the error.dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()

I have also tried. dt = (DataView)(EventDataSource1.Select(dssa);

I am programming in VB

here is teh rest of my code.

Dim EventDataSource1 As New SqlDataSource()

EventDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString

Dim dssa As New DataSourceSelectArguments()

Dim EventID As String = ""

Dim DataView = ""

Dim dt As New Data.DataTable

Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())

Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @.StartDate)", conn)

EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @.StartDate)")

conn.Open()

dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()

EventID = dt.Rows(0)(0).ToString()

EventDataSource1.SelectParameters.Add("@.StartDate",StartDate)

EventID = cmd.ExecuteScalar()

Seems like you overdoing it. If all you want is the first row, first column value...try something like this.

Dim EventIDAs String =""Dim connAs New Data.SqlClient.SqlConnection("Connection String")Dim cmdAs New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @.StartDate)", conn)'add parameters cmd.Parameters.Add(New SqlClient.SqlParameter("@.StartDate","01/01/2007"))'open connections cmd.Connection.Open()'get top row and column EventID = cmd.ExecuteScalar()'close connection cmd.Connection.Close()'clean up conn =Nothing cmd =Nothing

|||

That's all? Wow thanks!!

Just out of interest, what would I change if I didn't want the first column?

Thanks

|||here is a sample,

Dim EventIDAs String Dim drAs SqlDataReaderDim connAs New Data.SqlClient.SqlConnection("Connection String")Dim cmdAs New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @.StartDate)", conn)'add parameters cmd.Parameters.Add(New SqlClient.SqlParameter("@.StartDate","01/01/2007"))'open connections cmd.Connection.Open()'get entire dataset dr = cmd.ExecuteReaderWhile dr.Read()If IsDBNull(dr.Item("EventID")) =False Then EventID = (dr.Item("EventID"))End If'do whatever you want witht the dataEnd While'close dr dr.Close()'close connection cmd.Connection.Close()'clean up conn =Nothing cmd =Nothing

just remember to close the reader.|||Thanks again. Everything works great! YEEE HAAA!!!! Yes

cant convert char to varchar

I upgraded my datbase from 6.5 to 2000. Now I have primary key in one table datatype CHAR(5) and second table with a column having datatype VARCHAR(5).

I am trying to create a foreign key from second to first table but I am getting following error.

"Column 'dbo.dma.dma' is not the same data type as referencing column 'fone.dma' in foreign key 'FK_fone_dma'."

But I beleive CHAR to VARCHAR conversion is implicit.

COuld anyone please enlighten this problem.

Thanks in advance..The conversion is implicit, but the server won't do conversions for foreign keys. The two must be the same datatype.

You can choose to maintain relational integrity through the use of triggers, but you are better off making the field types uniform.

blindman|||If the table was created with ANSI_PADDING OFF, then you can drop the foreign key, alter table alter column <col_name> char(5) NULL + any defaults or check constraints. With ANSI_PADDING OFF the way data is stored in CHAR datatype is the same as for VARCHAR, providing that the column allows NULLs. After you altered the column re-create your foreign key and you're done.|||Cool. :cool:

blindman|||Doesn't appear so in RI

USE Northwind
GO

CREATE TABLE myTable99 (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
GO

CREATE TABLE myTablexx (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
ALTER TABLE myTablexx ADD CONSTRAINT myTablexx_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO

CREATE TABLE myTable00 (Col1 varchar(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO

DROP TABLE myTablexx
DROP TABLE myTable00
DROP TABLE myTable99
GO|||Uncool. :confused:
blindman|||Originally posted by rdjabarov
providing that the column allows NULLs.

Nulls...pk...hmmmmmmmmm

USE Northwind
GO

CREATE TABLE myTable99 (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
GO

CREATE TABLE myTablexx (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
ALTER TABLE myTablexx ADD CONSTRAINT myTablexx_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO

CREATE TABLE myTable00 (Col1 varchar(5) NOT NULL CONSTRAINT myTable00_PK PRIMARY KEY, Col2 int DEFAULT 0)
-- Will Fail
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
ALTER TABLE myTable00 ADD Col3 char(5) NULL
UPDATE myTable00 SET Col3 = Col1
ALTER TABLE myTable00 DROP CONSTRAINT myTable00_PK
ALTER TABLE myTable00 DROP COLUMN Col1
ALTER TABLE myTable00 ADD Col1 char(5) NULL
UPDATE myTable00 SET Col1 = Col3
ALTER TABLE myTable00 ALTER COLUMN Col1 char(5) NOT NULL
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_PK PRIMARY KEY (Col1)
-- Will NOT Fail
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO

DROP TABLE myTablexx
DROP TABLE myTable00
DROP TABLE myTable99
GO|||Originally posted by rdjabarov
If the table was created with ANSI_PADDING OFF, then you can drop the foreign key, alter table alter column <col_name> char(5) NULL + any defaults or check constraints. With ANSI_PADDING OFF the way data is stored in CHAR datatype is the same as for VARCHAR, providing that the column allows NULLs. After you altered the column re-create your foreign key and you're done.

Thanks.

But the same structure (PK CHAR / FK VARCHAR) is working fine in existing DB of 6.5.
How?|||Good old M$...

They forgot to forward engineer that "feature"...

Hey I still think that this is wrong...had lots of disagreements about it though...

USE Northwind
GO

DECLARE @.x int
CREATE TABLE myTable99(Col1 datetime)
SELECT @.x = 0
INSERT INTO myTable99(Col1) SELECT @.x
SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

In DB2, Oracle, ect, that breaks the rules...SQL Server loosley defines that 0 is a valid datatype for datetime...blew my mind when I found this out...

Oh, and btw, if it's a PK, you won't be able to do the ANSI thing (I don't thin)

If you can, post a sample...|||So I assume it worked the second time, Brett?

Cool? Uncool?|||If you mean the second alter, yes it works...cut and paste it in to QA and watch the test...

THE ANSI_PADDINGS thing I'll have to play with it...

But since a PK can't be NULL the point is probably moot...

You know what a faster way would be (but probably more resource intensive..)

CREATE TABLE myTable007 (Col1 char(5) NOT NULL CONSTRAINT myTable007_PK PRIMARY KEY, Col2 int DEFAULT 0)
INSERT INTO myTable007 (Col1, Col2) SELECT Col1, Col2 FROM myTable00
DROP TABLE myTable00
SELECT Col1, Col2 INTO myTable00 FROM myTable007
DROP TABLE myTable007
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO|||PK cannot allow NULLs, so Brett's experiment is not exactly demonstrating what I was talking about.

set ansi_padding off
go
create table parent (f1 char(5) not null primary key, f2 int null)
go
create table child (f1 char(5) null, f2 int null)
go
alter table child add constraint fk_child2parent foreign key (f1) references parent(f1)
go|||...And if you insert 'A' into PARENT and CHILD tables and select DATALENGTH(f1) from both, you'd see that the result on PARENT is 5 while on CHILD is 1. Thus, the behavior of VARCHAR datatype in CHILD table.|||Originally posted by nmajeed
I upgraded my datbase from 6.5 to 2000. Now I have primary key in one table datatype CHAR(5) and second table with a column having datatype VARCHAR(5).

But that was the original question...

PK cannot allow NULLs, so Brett's experiment is not exactly demonstrating what I was talking about.

But that's neat...

EDIT: But I rarely play with settings, because it can be dangerous...you have to remeber that you have a particular setting, and code for it...

If I do change a setting, it's always in the context of a transacxtion, and is set back at the conclusion...|||So what are your settings in the QA when you're about to create a table, for example? Or you're creating tables in EM?

Can't construct correct UPDATE script

I messed up an insert script somewhere and need to correct my data. The tabl
e which needs fixing is:
Table Mailings
AddressID int
PersonID int
NextMailDate datetime
Sample data:
AddressID PersonID NextMailDate
123 4 03/10/2005
123 4 08/30/2005
123 4 12/30/2005
123 12 07/05/2005
Data should be:
123 4 03/10/2005
123 4 NULL
123 4 NULL
123 12 07/05/2005
I need to set NextMailDate to NULL for each PersonID but the max(PersonID) h
aving a date > than the
system date.
I am lost, as you can see from my lame attempt. Thanks, Lars
update mailings
set nextmaildate = null
where addressid = (SELECT AddressID FROM Mailings m ) and
personid <> (SELECT max(PersonID) FROM Mailings WHERE AddressID = m.addressi
d) and
nextmaildate >= convert(nvarchar, getdate(), 112)larzeb wrote:
> I messed up an insert script somewhere and need to correct my data.
> The table which needs fixing is:
> Table Mailings
> AddressID int
> PersonID int
> NextMailDate datetime
> Sample data:
> AddressID PersonID NextMailDate
> 123 4 03/10/2005
> 123 4 08/30/2005
> 123 4 12/30/2005
> 123 12 07/05/2005
> Data should be:
> 123 4 03/10/2005
> 123 4 NULL
> 123 4 NULL
> 123 12 07/05/2005
> I need to set NextMailDate to NULL for each PersonID but the
> max(PersonID) having a date > than the system date.
>
What the PK on this table? I'm not sure I understand the requirement.
Are you saying you want to set the NextMailDate to NULL if a Person has
a NextMailDate > System Date? Are you showing us dates in European
format? I see a 03/10/2005, which in the USA is March 10th (presumably
less than the system date).
Based on the sample data, it looks as though you want a NULL
NextMailDate for all dates for a person that are not equal to the
MIN(NextMailDate).
Could you clarify?
David Gugick
Imceda Software
www.imceda.com|||David,
Sorry for the incomplete explanation. Yes, the dates are mm/dd/yyyy.
I did not include the PK. It is an identity column. The AddressID is a forei
gn key into the Address
table and the PersonID is a foreign key into the Person table.
For a single AddressID, I need to identify the MAX(PersonID) so that the com
bination of AddressID
and MAX(PersondID) is not affected.
For all other combinations of any single AddressID and PersonIDs, I need to
make the NextMailDate
NULL where the existing NextMailDate is equal to or greater than the system
date.
The data illustration was for a single AddressID.
Thanks, Lars
On Mon, 23 May 2005 14:52:24 -0400, "David Gugick" <davidg-nospam@.imceda.com
> wrote:

>larzeb wrote:
>What the PK on this table? I'm not sure I understand the requirement.
>Are you saying you want to set the NextMailDate to NULL if a Person has
>a NextMailDate > System Date? Are you showing us dates in European
>format? I see a 03/10/2005, which in the USA is March 10th (presumably
>less than the system date).
>Based on the sample data, it looks as though you want a NULL
>NextMailDate for all dates for a person that are not equal to the
>MIN(NextMailDate).
>Could you clarify?|||Firstly, don't depend on Identity columns as a guarantee of entry sequence.
You
should instead add a column called DateTime column that marks when the row w
as
added.

> For a single AddressID, I need to identify
> the MAX(PersonID) so that the combination of AddressID
> and MAX(PersondID) is not affected.
This logic does not appear to match your sample results. Given your
requirements, I would expect the following results
AddressId PersonId NextMailDate
123 4 2005-03-10
123 4 Null
123 4 Null
123 12 Null
If this is not the case, then why should the last entry for a given AddressI
d
and PersonId but a date greater than the system date not get reset?
Thomas
"larzeb" <larzeb@.community.nospam> wrote in message
news:627491d56eo3jrbu313505o318e1kn3bh3@.
4ax.com...
>I messed up an insert script somewhere and need to correct my data. The tab
le
>which needs fixing is:
> Table Mailings
> AddressID int
> PersonID int
> NextMailDate datetime
> Sample data:
> AddressID PersonID NextMailDate
> 123 4 03/10/2005
> 123 4 08/30/2005
> 123 4 12/30/2005
> 123 12 07/05/2005
> Data should be:
> 123 4 03/10/2005
> 123 4 NULL
> 123 4 NULL
> 123 12 07/05/2005
> I need to set NextMailDate to NULL for each PersonID but the max(PersonID)
> having a date > than the
> system date.
> I am lost, as you can see from my lame attempt. Thanks, Lars
>
> update mailings
> set nextmaildate = null
> where addressid = (SELECT AddressID FROM Mailings m ) and
> personid <> (SELECT max(PersonID) FROM Mailings WHERE AddressID =
> m.addressid) and
> nextmaildate >= convert(nvarchar, getdate(), 112)|||On Mon, 23 May 2005 12:44:51 -0700, larzeb wrote:
>David,
>Sorry for the incomplete explanation. Yes, the dates are mm/dd/yyyy.
>I did not include the PK. It is an identity column. The AddressID is a fore
ign key into the Address
>table and the PersonID is a foreign key into the Person table.
>For a single AddressID, I need to identify the MAX(PersonID) so that the co
mbination of AddressID
>and MAX(PersondID) is not affected.
>For all other combinations of any single AddressID and PersonIDs, I need to
make the NextMailDate
>NULL where the existing NextMailDate is equal to or greater than the system date.[/
color]
(snip)
Hi larzeb,
Not sure if I understand the requirements correctly, but maybe this is
what you want:
UPDATE Mailings
SET NextMailDate IS NULL
WHERE NextMailDate > CURRENT_TIMESTAMP
AND PersonID <> (SELECT MAX(PersonID)
FROM Mailings AS m2
WHERE m2.AddressID = Mailings.AddressID)
Try on test data first. Run inside a transaction, and check the results
before executing COMMIT (or ROLLBACK if the results are wrong).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Looking at the problem again, Hugo's response would be the solution you want
.
That said this is a pretty bizarre situation asking for the Max personId.
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:erNavS9XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Firstly, don't depend on Identity columns as a guarantee of entry sequence
.
> You should instead add a column called DateTime column that marks when the
row
> was added.
>
> This logic does not appear to match your sample results. Given your
> requirements, I would expect the following results
> AddressId PersonId NextMailDate
> 123 4 2005-03-10
> 123 4 Null
> 123 4 Null
> 123 12 Null
> If this is not the case, then why should the last entry for a given Addres
sId
> and PersonId but a date greater than the system date not get reset?
>
> Thomas
>
> "larzeb" <larzeb@.community.nospam> wrote in message
> news:627491d56eo3jrbu313505o318e1kn3bh3@.
4ax.com...
>|||On Mon, 23 May 2005 13:33:08 -0700, Thomas Coleman wrote:

>Looking at the problem again, Hugo's response would be the solution you wan
t.
>That said this is a pretty bizarre situation asking for the Max personId.
Hi Thomas,
My thoughts exactly - that's why I thought that I might have misread
something :)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Saturday, February 25, 2012

Can't create a setup project for project referencing Microsoft.SqlServer.ManagedDTS

Can't create a setup project for project referencing Microsoft.SqlServer.ManagedDTS

Can't create a secondary index on a bit column

Can't create a secondary index on a bit column

Can't create a once existed database with its name

Can't create a ODBC DSN to a remote MSDE instance

Can't create a new record using Access?

Can't create a new publication

Can't create a new FT Catalog

Can't create a Maint Plan in 2005

Can't create a Maint Plan in 2005

Can't create a Maint Plan in 2005

Can't create a linked server when the servers are set up to replicate

Cant create a foreign key using non_primary key fields

Can't create a BCP format file

Can't create a backup job in SQL2005

Can't create a backup job in SQL2005

Can't create a backup job in SQL2005

Can''t Creat Subscriptions

Can't Creat Subscriptions

Can't Creat Subscriptions

Can't Creat Subscriptions

Can't copy/export a database with identity NEWBIE

Can't copy databases across network with copy wizard

Can't copy databases across network with copy wizard

Can't copy databases across network with copy wizard

Cant copy database, strange error

Can't copy a database

Cant convert types

Cant convert record to integer

cant convert char to varchar

Can't construct correct UPDATE script

connect from ASP app

Blog Archive

About Me