Question regarding my StronglyTyped Dataset

If I created a DAL, can I just use ObjectDataSources instead of SqlDataSources and fill them with my methods from my DAL? I mean do I HAVE to use SqlDataSources? Cause I have to bind them at runtime right? I'd rather bind everything at design time and I can do so using ObjectDataSources.

Are there problems with using ObjectDataSources over SqlDataSources?

Thanks!

Only thing is you need to control select, delete etc function your own in Object data source while Sqldatasource does it by using query of SP

|||

ahh I see. I'm not extremely efficient in writing sprocs, but I guess nows the time :)

Thanks for the answer!

Question Regarding Job Schedules

Hi

My instance of SQL Server 2005 is installed on a Win Server 2003 box. I am trying to schedule an SSIS job. I have created a proxy using my NTLogin as Credential. When I run the job, it returns with the following error:

Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'XXXXXXXX', error code 0x54b. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
where XXXXXXXX is my Domain\NTLogin

I access the Server box and discovered that the Windows Task Scheduling Service is not running. Does SQL Server Scheduling depend on this?

I'm quite sure that SQL Server Agent has nothing to do with Windows Task Scheduler. However I have no idea about your error...|||

Hi

Thanks for the reply. Here is what I am doing to test this:

I have a Test SSIS package that has a singe Execute SQL Task which connects to a SQL 2000 db and makes an insert into a table. This works fine when I run it through the VS Studio IDE.

After this, I registered the Package with the Integration Services and when I execute the package by right clicking and selecting the Execute Package option it executes again.

I restarted the SQL Server Agent to run under my NT account. Now, the first piece of the puzzle is that when I try to create a new credential with my NT Account, it throws an error saying that :
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An error occurred during encryption. (Microsoft SQL Server, Error: 15467)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=09.00.1116&EvtSrc=MSSQLServer&EvtID=15467&LinkId=20476
I ran a Create Credential Script to insert the credential and created a proxy using that. Then I created a job with a new SSIS step that runs under the proxy. Now when I try to run the job it fails to execute with the following two entires in the log:

Message
[298] SQLServer Error: 3621, The statement has been terminated. [SQLSTATE 01000] (ConnIsLoginSysAdmin)

Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'CSFB\jgattani', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Can I provide you with any more information that might assist you in trying to figure out whats going on?

Regards
Jay

|||Hi

I had posted my question a couple of days back and did not get a response. Is the question vague or does it need more information. Please let me know if I could provide more details in understanding the problem I am facing.

We are stuck at this point and would like to get this resolved and use the SQL Server 2005 instead of good old SQL Server 2000.

Thanks
|||Hi,

just an idea... Changing the service login wasn't supported in older builds of SQL Server 2005. Perhaps there is still a problem with that... I would not change the service's credentials...

Encryption problem can also occur when you store "save" data (like database logins) within the package and encrypt the package with the user key. You should use a password for the package instead and use it when you run the package...

Again: just an idea...|||Hi

Thanks for the response. I think that the root cause of the issue is my inability to create the credential. If I have the SQL Server Agent running under my NT Acount and then the job Steps running under the "SQL Agent Service Account" and my nt account as the owner of the job, everything works perfect.

sql

Question on ON DELETE CASCADE

I have a master table and a child table. I have created a foreignkey
constraint on the child table with OUT the ON DELETE CASCADE option.
Now when I delete records from the Master table, can I specify "on
delete cascade" at that time.
Or I can do that only if I specify when the foreign key is created.
Thanks in advance.Hi
> Or I can do that only if I specify when the foreign key is created.
Yes
"SQL novice" <balacr@.gmail.com> wrote in message
news:1132299915.134325.257320@.g44g2000cwa.googlegroups.com...
>I have a master table and a child table. I have created a foreignkey
> constraint on the child table with OUT the ON DELETE CASCADE option.
> Now when I delete records from the Master table, can I specify "on
> delete cascade" at that time.
> Or I can do that only if I specify when the foreign key is created.
> Thanks in advance.
>

question on locks

somebody helps ?

1.What kind of lockes are created on what resources when following
querys executes ?
select * from JOBQUEUE where ID='XXX'
update JOBQUEUE set columnA='YYY' where ID='XXX'

2.Under what kind of situation, share lock on a row or on a page or on a
table will convert to exclusive lock?

Thanks a lot.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!stan cai (caichuang@.digitalchina.com) writes:
> somebody helps ?
> 1.What kind of lockes are created on what resources when following
> querys executes ?
> select * from JOBQUEUE where ID='XXX'
> update JOBQUEUE set columnA='YYY' where ID='XXX'

Depends. If there is an index on ID, you should get a shared row lock
and a exclusive row lock. If there is no index on ID, both statement
will need a shared table lock. The UPDATE still needs an exclusive row
lock.

> 2.Under what kind of situation, share lock on a row or on a page or on a
> table will convert to exclusive lock?

When you update the row.

If you want to learn more about locking, it could be an idea to check
out Kalen Delaney's E-book, "Hands-On SQL Server 2000 : Troubleshooting
Locking and Blocking". See www.netimpress.com or directly:
http://www.shareit.com/product.html...¤cies=USD

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question on backups

I have a backup of a database that has been running for quite a while,
successfully.
I went in to the Database Maintenance plan, and created a new plan to
optimize, check the integrity and do a complete backup (this created 3
new jobs out in the job section of SQL).
Here's the TSQL code from both.
Can anyone tell me if they're doing much the same thing with regards to
having a restore-able backup?
[sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT
, NOUNLOAD , NAME = N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
in the Tsql in the job.
the new one (the backup only) is:
.xp_sqlmaint N'-PlanID abcdefgh-ijkl-mnop-qrst-uvwxyz012345
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK
-BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
Thanks,
BCRun a Profiler trace and you will see the TSQL commands submitted from the maint wizard. I don't
know what [sfa] is supposed to symbolize, but the maint plan as posted does a database backup for
the database(s) selected in the plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:OIWww4mdFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I have a backup of a database that has been running for quite a while, successfully.
> I went in to the Database Maintenance plan, and created a new plan to optimize, check the
> integrity and do a complete backup (this created 3 new jobs out in the job section of SQL).
> Here's the TSQL code from both.
> Can anyone tell me if they're doing much the same thing with regards to having a restore-able
> backup?
> [sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT , NOUNLOAD , NAME => N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
> in the Tsql in the job.
> the new one (the backup only) is:
> .xp_sqlmaint N'-PlanID
> abcdefgh-ijkl-mnop-qrst-uvwxyz012345 -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
> Thanks,
> BC|||Tibor Karaszi wrote:
> Run a Profiler trace and you will see the TSQL commands submitted from
> the maint wizard. I don't know what [sfa] is supposed to symbolize, but
> the maint plan as posted does a database backup for the database(s)
> selected in the plan.
>
sfa in our case is the database name.|||I realized that after posting. I was confused by the missing BACKUP DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:elODCendFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>> Run a Profiler trace and you will see the TSQL commands submitted from the maint wizard. I don't
>> know what [sfa] is supposed to symbolize, but the maint plan as posted does a database backup for
>> the database(s) selected in the plan.
> sfa in our case is the database name.

Question on backups

I have a backup of a database that has been running for quite a while,
successfully.
I went in to the Database Maintenance plan, and created a new plan to
optimize, check the integrity and do a complete backup (this created 3
new jobs out in the job section of SQL).
Here's the TSQL code from both.
Can anyone tell me if they're doing much the same thing with regards to
having a restore-able backup?
[sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT
, NOUNLOAD , NAME = N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
in the Tsql in the job.
the new one (the backup only) is:
.xp_sqlmaint N'-PlanID abcdefgh-ijkl-mnop-qrst-uvwxyz012345
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK
-BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
Thanks,
BCRun a Profiler trace and you will see the TSQL commands submitted from the m
aint wizard. I don't
know what [sfa] is supposed to symbolize, but the maint plan as posted d
oes a database backup for
the database(s) selected in the plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:OIWww4mdFHA.2124@.TK2MSFTNGP14.ph
x.gbl...
>I have a backup of a database that has been running for quite a while, succ
essfully.
> I went in to the Database Maintenance plan, and created a new plan to opti
mize, check the
> integrity and do a complete backup (this created 3 new jobs out in the job
section of SQL).
> Here's the TSQL code from both.
> Can anyone tell me if they're doing much the same thing with regards to ha
ving a restore-able
> backup?
> [sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH IN
IT , NOUNLOAD , NAME =
> N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
> in the Tsql in the job.
> the new one (the backup only) is:
> .xp_sqlmaint N'-PlanID
> abcdefgh-ijkl-mnop-qrst-uvwxyz012345 -WriteHistory -VrfyBackup -BkUpOnlyI
fClean -CkDB -BkUpMedia
> DISK -BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
> Thanks,
> BC|||Tibor Karaszi wrote:
> Run a Profiler trace and you will see the TSQL commands submitted from
> the maint wizard. I don't know what [sfa] is supposed to symbolize, bu
t
> the maint plan as posted does a database backup for the database(s)
> selected in the plan.
>
sfa in our case is the database name.|||I realized that after posting. I was confused by the missing BACKUP DATABASE
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:elODCendFHA.2420@.TK2MSFTNGP12.ph
x.gbl...
> Tibor Karaszi wrote:
> sfa in our case is the database name.

Question on backups

I have a backup of a database that has been running for quite a while,
successfully.
I went in to the Database Maintenance plan, and created a new plan to
optimize, check the integrity and do a complete backup (this created 3
new jobs out in the job section of SQL).
Here's the TSQL code from both.
Can anyone tell me if they're doing much the same thing with regards to
having a restore-able backup?
[sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT
, NOUNLOAD , NAME = N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
in the Tsql in the job.
the new one (the backup only) is:
..xp_sqlmaint N'-PlanID abcdefgh-ijkl-mnop-qrst-uvwxyz012345
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK
-BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
Thanks,
BC
Run a Profiler trace and you will see the TSQL commands submitted from the maint wizard. I don't
know what [sfa] is supposed to symbolize, but the maint plan as posted does a database backup for
the database(s) selected in the plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:OIWww4mdFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I have a backup of a database that has been running for quite a while, successfully.
> I went in to the Database Maintenance plan, and created a new plan to optimize, check the
> integrity and do a complete backup (this created 3 new jobs out in the job section of SQL).
> Here's the TSQL code from both.
> Can anyone tell me if they're doing much the same thing with regards to having a restore-able
> backup?
> [sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT , NOUNLOAD , NAME =
> N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
> in the Tsql in the job.
> the new one (the backup only) is:
> .xp_sqlmaint N'-PlanID
> abcdefgh-ijkl-mnop-qrst-uvwxyz012345 -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
> Thanks,
> BC
|||Tibor Karaszi wrote:
> Run a Profiler trace and you will see the TSQL commands submitted from
> the maint wizard. I don't know what [sfa] is supposed to symbolize, but
> the maint plan as posted does a database backup for the database(s)
> selected in the plan.
>
sfa in our case is the database name.
|||I realized that after posting. I was confused by the missing BACKUP DATABASE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:elODCendFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> sfa in our case is the database name.

question on AqcuireConnection

Hi,

I created a function that supposedly checks a connection manager if a connection can be established.
When I was testing the code, I found out that the following code always returns "Successful" for flat files and SMTP connection even if the flat file does not even exist or there are no connection parameters for SMTP.

Can you tell me what to use to check if a connection can be established for all types of connection managers?

Here is the function that I created which does not seem to work as I expected:

Public Function connect(ByVal connMgr As Object) As String
Dim connected As String
Try
Dts.Connections(connMgr.name).AcquireConnection(Nothing)
Return "Successful"
Catch ex As Exception
Return "Failure"
Finally
Dts.Connections(connMgr.name).ReleaseConnection(Nothing)
End Try
End Function

Thanks.

AcquireConnection returns an object that represents the connection. You need to check this object. It will be different for each connection manager.

An ADO.NET connection manager will return an IDbConnection object.

The SMTP Connection manager simply returns the connection string of the connection manager.

There isn't a generic way to determine if a connection can be established for all types of connection managers.
|||

A flat file does not need to exist for AcquireConnection to be successful. It can point to a destination file to be created at the runtime.

Thanks.