Long disappeared

I have just created a new database in the same computer that has a a one other databases. The other database has the "long" datatype for values but the new one doesn't allow them to be added. It's not on the list for datatypes. What setting am I missing?I am not aware of SQL Server ever allowing long as a datatype. Int and BIG_INT are allowed integer types, int is a 4 byte value, BIG_INT is 8 bytes, as I recall.|||I'm a big fat Mr. Dumbaz... Thanks. I was thinking C# not SQL.|||"long" is an Oracle datatype. It is functionally equivalent to "text" datatype in SQL Server.

logshipping mystery.

Hey All,
Hope this is the right place for this post.

I set up logshipping between two databases recently. everything looks fine. the backup, copy and restore jobs are all suceeding(from the job log). the problem is no Tlogs are being restored in secondary database. The restore job skips all the tlogs and says it did not find any tlog back up file to restore!! The jobs finishes with this:

007-07-17 14:40:30.59 Could not find a log backup file that could be applied to secondary database 'SaaSNet_dataStore'.
2007-07-17 14:40:30.59 The restore operation was successful. Secondary Database: 'SaaSNet_dataStore', Number of log backup files restored: 0
2007-07-17 14:40:30.59 Deleting old log backup files. Primary Database: 'SaaSNet_DataStore'
2007-07-17 14:40:30.59 The restore operation was successful. Secondary ID: '5808a414-2ada-41d2-a8a0-2cf84f85174a'

Appreciate your help

the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

|||
the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

logshipping mystery.

Hey All,
Hope this is the right place for this post.

I set up logshipping between two databases recently. everything looks fine. the backup, copy and restore jobs are all suceeding(from the job log). the problem is no Tlogs are being restored in secondary database. The restore job skips all the tlogs and says it did not find any tlog back up file to restore!! The jobs finishes with this:

007-07-17 14:40:30.59 Could not find a log backup file that could be applied to secondary database 'SaaSNet_dataStore'.
2007-07-17 14:40:30.59 The restore operation was successful. Secondary Database: 'SaaSNet_dataStore', Number of log backup files restored: 0
2007-07-17 14:40:30.59 Deleting old log backup files. Primary Database: 'SaaSNet_DataStore'
2007-07-17 14:40:30.59 The restore operation was successful. Secondary ID: '5808a414-2ada-41d2-a8a0-2cf84f85174a'

Appreciate your help

the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

|||
the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

logshipping mystery- SOLVED -

Hey All,
Hope this is the right place for this post.

I set up logshipping between two databases recently. everything looks fine. the backup, copy and restore jobs are all suceeding(from the job log). the problem is no Tlogs are being restored in secondary database. The restore job skips all the tlogs and says it did not find any tlog back up file to restore!! The jobs finishes with this:

007-07-17 14:40:30.59 Could not find a log backup file that could be applied to secondary database 'SaaSNet_dataStore'.
2007-07-17 14:40:30.59 The restore operation was successful. Secondary Database: 'SaaSNet_dataStore', Number of log backup files restored: 0
2007-07-17 14:40:30.59 Deleting old log backup files. Primary Database: 'SaaSNet_DataStore'
2007-07-17 14:40:30.59 The restore operation was successful. Secondary ID: '5808a414-2ada-41d2-a8a0-2cf84f85174a'

Appreciate your help

the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

|||
the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

Log-shipping failure

Hi All,
sql server 2000 enterprise edition, 8.00.760
Am trying to set logs-shipping between two databases on two different servers on the same domain. Am using same sql server agent account for both the servers.
After log-shipping is configured, am able to see the transaction logs being backed up in the primary server, then copied on to the secondary server folder. But when it is trying to restore the transaction logs on the secondary server, I get the following
error:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).
FYI, We are using compatibility level of 65 on the primary server, as this is how we want it. But I can change the compatibility level on secondary server to 80 (if its a must). Am wondering, if this is one of the reason for failure.
More information:
I have confugured it to restore in STANDBY mode and terminate any user connections. And the secondary server is used for monitoring log shipping activities.
Has anybody faced similar problem? Please advise
Thanks much
GYK
> Hi All,
> sql server 2000 enterprise edition, 8.00.760
> Am trying to set logs-shipping between two databases on two
> different servers on the same domain. Am using same sql server
> agent account for both the servers.
> After log-shipping is configured, am able to see the transaction
> logs being backed up in the primary server, then copied on to
> the secondary server folder. But when it is trying to restore the
> transaction logs on the secondary server, I get the following error:
> sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).
> FYI, We are using compatibility level of 65 on the primary server,
> as this is how we want it. But I can change the compatibility
> level on secondary server to 80 (if its a must). Am wondering,
> if this is one of the reason for failure.
> More information:
> I have confugured it to restore in STANDBY mode and terminate
> any user connections. And the secondary server is used for
> monitoring log shipping activities.
> Has anybody faced similar problem? Please advise
> Thanks much
> GYK
Check that the sql server agent account has enough permissions on the
secondary server.
Run the sqlmaint with the parameters from the command-line and see what
errors are returned.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Log-shipping failure

Hi All,
sql server 2000 enterprise edition, 8.00.760
Am trying to set logs-shipping between two databases on two different server
s on the same domain. Am using same sql server agent account for both the se
rvers.
After log-shipping is configured, am able to see the transaction logs being
backed up in the primary server, then copied on to the secondary server fold
er. But when it is trying to restore the transaction logs on the secondary s
erver, I get the following
error:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).
FYI, We are using compatibility level of 65 on the primary server, as this i
s how we want it. But I can change the compatibility level on secondary serv
er to 80 (if its a must). Am wondering, if this is one of the reason for fai
lure.
More information:
I have confugured it to restore in STANDBY mode and terminate any user conne
ctions. And the secondary server is used for monitoring log shipping activit
ies.
Has anybody faced similar problem? Please advise
Thanks much
GYK> Hi All,
> sql server 2000 enterprise edition, 8.00.760
> Am trying to set logs-shipping between two databases on two
> different servers on the same domain. Am using same sql server
> agent account for both the servers.
> After log-shipping is configured, am able to see the transaction
> logs being backed up in the primary server, then copied on to
> the secondary server folder. But when it is trying to restore the
> transaction logs on the secondary server, I get the following error:
> sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).
> FYI, We are using compatibility level of 65 on the primary server,
> as this is how we want it. But I can change the compatibility
> level on secondary server to 80 (if its a must). Am wondering,
> if this is one of the reason for failure.
> More information:
> I have confugured it to restore in STANDBY mode and terminate
> any user connections. And the secondary server is used for
> monitoring log shipping activities.
> Has anybody faced similar problem? Please advise
> Thanks much
> GYK
--
Check that the sql server agent account has enough permissions on the
secondary server.
Run the sqlmaint with the parameters from the command-line and see what
errors are returned.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Logs NOT truncating with Arcserve v9?

Anyone using Brightstor Arcserve v9 + the SQL agent to backup their SQL
databases?
We are using it to backup all our SQL2000 databases (15) and it works just
fine *except* even though it is configured to do a 'complete backup' of each
database it doesnt truncate the transaction log at the end...with the
consequence that the logs bloat out the many Gigabytes.
I understood that the default behaviour for a 'complete backup' was to
backup the database+logs then truncate?
The arcserve agent give the option to run a 'transaction log' backup...(ie
logs only) but that is not what we want.
Any ideas on how to get it to truncate those logs once the backup is
completed sucessfully (other than use another product ;)
NB the CA support site for Arcserve is *REALLY* crap - every query returns
hundreds of product releases and adverts for their bloody software but no
technical information. Every time they change it, it gets worse
Al Blake, Canberra, AustraliaAl,

> I understood that the default behaviour for a 'complete backup' was to
> backup the database+logs then truncate?
You would have to ask Arcserve about this. In SQL Server, BACKUP DATABASE
does not empty the log. You either do regular log backups or put the
database in simple recovery mode. A tip is to run Profiler while the
Arcserve job is executed to catch what is *really* going on.
Also, information about shrinking files etc (as I guess you might want to do
this) is found at below side, see the links in the text:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Al Blake" <al@.blakes.net> wrote in message
news:eBeoGjGEEHA.3804@.TK2MSFTNGP09.phx.gbl...
> Anyone using Brightstor Arcserve v9 + the SQL agent to backup their SQL
> databases?
> We are using it to backup all our SQL2000 databases (15) and it works just
> fine *except* even though it is configured to do a 'complete backup' of
each
> database it doesnt truncate the transaction log at the end...with the
> consequence that the logs bloat out the many Gigabytes.
> I understood that the default behaviour for a 'complete backup' was to
> backup the database+logs then truncate?
> The arcserve agent give the option to run a 'transaction log' backup...(ie
> logs only) but that is not what we want.
> Any ideas on how to get it to truncate those logs once the backup is
> completed sucessfully (other than use another product ;)
> NB the CA support site for Arcserve is *REALLY* crap - every query returns
> hundreds of product releases and adverts for their bloody software but no
> technical information. Every time they change it, it gets worse
> Al Blake, Canberra, Australia
>

Logs not truncating

Hi,
we have a problem on one of our databases.. mainly the log is not truncating
after a backup.. By now it has grown to 3Gb, with the data file at 156Mb.
Any Ideas?
Thanks
NicholasHave a look here :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
--
HTH
Ryan Waight, MCDBA, MCSE
"Nicholas Aquilina" <naquilina@.gfi.com> wrote in message
news:e$cXQaKnDHA.372@.TK2MSFTNGP11.phx.gbl...
> Hi,
> we have a problem on one of our databases.. mainly the log is not
truncating
> after a backup.. By now it has grown to 3Gb, with the data file at 156Mb.
> Any Ideas?
> Thanks
> Nicholas
>|||Hi
The article helped..I had to use DBCC SHRINKFILE(Database_log,6). I shrunk
the log to 700Mb first from 3Gb, and then the rest with a backup & log
truncate worked normally. Now could the shrinkfile possibly affect any
replication? We have a snapshot replication from this same database to other
servers
Thanks Aagain
Nicholas
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eWDTxdKnDHA.3024@.tk2msftngp13.phx.gbl...
> Have a look here :-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Nicholas Aquilina" <naquilina@.gfi.com> wrote in message
> news:e$cXQaKnDHA.372@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > we have a problem on one of our databases.. mainly the log is not
> truncating
> > after a backup.. By now it has grown to 3Gb, with the data file at
156Mb.
> >
> > Any Ideas?
> >
> > Thanks
> >
> > Nicholas
> >
> >
>

Logins from SQL7 to SQL2000

We are upgraded our vendor software (Jenzabar EX) from SQL7 to SQL2000. In the process, we are also moving the databases to a new server. Everything seemed to go fine until we tried to log on to one of Jenzabar EX modules. We have tried several differe
nt things, including the sp_change_users (which failed because of the @.action clause). We've tried running that huge script included in the Knowledge Base article. We also have tried using the transfer login wizard. Which, report success, but the login
s still do not work.
Could we have possibly missed a step somewhere? Is there something else we can try? I sure would greatly appreciate any advice or assistance.
Thanks!
Karma
What is the exact problem you are having when you login from this
application?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Logins from SQL7 to SQL2000

We are upgraded our vendor software (Jenzabar EX) from SQL7 to SQL2000. In
the process, we are also moving the databases to a new server. Everything s
eemed to go fine until we tried to log on to one of Jenzabar EX modules. We
have tried several differe
nt things, including the sp_change_users (which failed because of the @.actio
n clause). We've tried running that huge script included in the Knowledge B
ase article. We also have tried using the transfer login wizard. Which, re
port success, but the login
s still do not work.
Could we have possibly missed a step somewhere? Is there something else we
can try? I sure would greatly appreciate any advice or assistance.
Thanks!
KarmaWhat is the exact problem you are having when you login from this
application?
Rand
This posting is provided "as is" with no warranties and confers no rights.

login with diffrent password

Hi
Is it possible that two databases have same login name user but with
diffrent password'
thanksNo. But it is possible (not recommended) for two different logins to exist
each used by the same individual with different passwords. What problem are
you having?
HTH
Jerry
"perspolis" <rezarms@.hotmail.com> wrote in message
news:%23l9uW8z0FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible that two databases have same login name user but with
> diffrent password'
> thanks
>|||No. What would that accomplish?
What if one of them changed their password, and happened to choose the same
password as the other login? How would you differentiate them then?
A
"perspolis" <rezarms@.hotmail.com> wrote in message
news:%23l9uW8z0FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible that two databases have same login name user but with
> diffrent password'
> thanks
>|||In my application,user can create diffrent databases..
if the login name and password are the same,then a user can login to both of
databases...I don't want this..
I want every user can login his database..
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eUM2k#z0FHA.3336@.TK2MSFTNGP12.phx.gbl...
> No. But it is possible (not recommended) for two different logins to
exist
> each used by the same individual with different passwords. What problem
are
> you having?
> HTH
> Jerry
> "perspolis" <rezarms@.hotmail.com> wrote in message
> news:%23l9uW8z0FHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||Logins are not database specific; you can only have a login name appear once
per instance.
Perhaps you are talking about users, not logins. There is a difference.
Please see Books Online.
A
"perspolis" <rezarms@.hotmail.com> wrote in message
news:%233pn1p00FHA.3000@.TK2MSFTNGP12.phx.gbl...
> In my application,user can create diffrent databases..
> if the login name and password are the same,then a user can login to both
> of
> databases...I don't want this..
> I want every user can login his database..|||Well unfortunately that is the way SQL Server's security works. One login -
one password maps to one-to-many databases.
HTH
Jerry
"perspolis" <rezarms@.hotmail.com> wrote in message
news:%233pn1p00FHA.3000@.TK2MSFTNGP12.phx.gbl...
> In my application,user can create diffrent databases..
> if the login name and password are the same,then a user can login to both
> of
> databases...I don't want this..
> I want every user can login his database..
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eUM2k#z0FHA.3336@.TK2MSFTNGP12.phx.gbl...
> exist
> are
>|||I wrote this a few years back...this will help you undestand SQL Server's
security:
SQL Server 2000 Operations Guide - Security Administration
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:esKPUt00FHA.2428@.tk2msftngp13.phx.gbl...
> Well unfortunately that is the way SQL Server's security works. One
> login - one password maps to one-to-many databases.
> HTH
> Jerry
> "perspolis" <rezarms@.hotmail.com> wrote in message
> news:%233pn1p00FHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||The user could still login to multiple databases, even they did have
multiple logins with different passwords. It is possible using ALTER
DATABASE.. to configure a database to SINGLE_USER mode so that only one user
may login at a time. However, there is no feature to restrict a login to
only one database, unless you drop them as a user or deny permissions to all
databases but one. I would suggest that you steer away from a system design
that allows users to create their own databases, becuase the management
requirements of this design could quickly get out of hand.
"perspolis" <rezarms@.hotmail.com> wrote in message
news:%233pn1p00FHA.3000@.TK2MSFTNGP12.phx.gbl...
> In my application,user can create diffrent databases..
> if the login name and password are the same,then a user can login to both
> of
> databases...I don't want this..
> I want every user can login his database..
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eUM2k#z0FHA.3336@.TK2MSFTNGP12.phx.gbl...
> exist
> are
>|||I know what user does..the question is that you can login to many database
with one login name and password..
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSfXer00FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Logins are not database specific; you can only have a login name appear
once
> per instance.
> Perhaps you are talking about users, not logins. There is a difference.
> Please see Books Online.
> A
>
> "perspolis" <rezarms@.hotmail.com> wrote in message
> news:%233pn1p00FHA.3000@.TK2MSFTNGP12.phx.gbl...
both
>|||thanks all for their replying.
If I use application role,I think I can manage the problem..
what's your opinion'
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eUM2k#z0FHA.3336@.TK2MSFTNGP12.phx.gbl...
> No. But it is possible (not recommended) for two different logins to
exist
> each used by the same individual with different passwords. What problem
are
> you having?
> HTH
> Jerry
> "perspolis" <rezarms@.hotmail.com> wrote in message
> news:%23l9uW8z0FHA.3000@.TK2MSFTNGP12.phx.gbl...
>

Login to a SQL Server 2000 database keeps failing

Hello,
On my system (XP professional),I have no problem logging to all of my
databases (SQL Server 2000) using "sa" or any of the other logins that I had
created and gave them database access.
However, on this other system (XP professional and SQL Server 2000) I keep
getting the following error:
Java.sql.SQLException: []Login failed for user â'saâ'. Reason: Not associatied
with a trusted SQL Server connection.
I will get the same error if I create and use new logins that were given
access to the databases. I had never seen this particular error message.
Please help!
Thanks,
--
MitraPerhaps this SQL Server is configured to allow only Windows authentication.
To connect with SQL logins, you need to change the server's authentication
mode to SQL Server and Windows and restart SQL Server. You can do this from
the Security tab under Enterprise Manager server properties.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:42CDF6EC-8DCD-46BE-96A3-CEE02C498284@.microsoft.com...
> Hello,
> On my system (XP professional),I have no problem logging to all of my
> databases (SQL Server 2000) using "sa" or any of the other logins that I
> had
> created and gave them database access.
> However, on this other system (XP professional and SQL Server 2000) I keep
> getting the following error:
> Java.sql.SQLException: []Login failed for user 'sa'. Reason: Not
> associatied
> with a trusted SQL Server connection.
> I will get the same error if I create and use new logins that were given
> access to the databases. I had never seen this particular error message.
> Please help!
> Thanks,
> --
> Mitra|||Mitra,
How are you logging into your DBs -- it looks like from an application? Have
you tried creating a DSN entry using the same credentials? My guess is that
your application is trying to logon sa as a domain account not as a SQL
native account.
Tea C.
"mitra" wrote:
> Hello,
> On my system (XP professional),I have no problem logging to all of my
> databases (SQL Server 2000) using "sa" or any of the other logins that I had
> created and gave them database access.
> However, on this other system (XP professional and SQL Server 2000) I keep
> getting the following error:
> Java.sql.SQLException: []Login failed for user â'saâ'. Reason: Not associatied
> with a trusted SQL Server connection.
> I will get the same error if I create and use new logins that were given
> access to the databases. I had never seen this particular error message.
> Please help!
> Thanks,
> --
> Mitra

Login problems on Vista before and after SP2

Hi:

I have recently updated my Sony Vaio and installed Windows Vista. After installing I could not access my SQL databases because of the NT Authorisation / sysadmin problem.

I have since installed SQLServer SP2 (Nov CTP) but the problem persists. If I run SQL Management Studio under the administrative account I can login just fine. I have also made sure the 'NT AUTHORITY\SYSTEM' is marked for sysadmin access (which it is).

The eror I get is Error 18456.

What else can I do to gain access via Windows Authorisation?

TIA,

MartinH.

Hello Martin,

Are you are logging into the Vista under any domain credentials? If so please add the domain user as the local system administrator and try.

Thanks,

Prakash P [MSFT]

|||

Prakash:

Sorry to be so dense, but I'm not quite sure what you are asking.

I am using Vista on a portable that does not connect to any Domain Server. The locally created user belongs to the Administrators group.

Is this what you meant by 'please add the domain user as the local system administrator'?

Thanks for your reply.

Martin.

|||

Here is a workaround for resolving this issue:

1. Run SSMS “As an Administrator”, by right clicking the SSMS icon and clicking “Run As Administrator” option and select Windows Authentication and login to SQL Server.

2. In the Object Explorer node select the Security node and then select Logins node. Add a new login for your windows account and select Windows Authentication. Also go the Server Roles pane in the same window and select sysadmin role for it.

3. Close SSMS and start it normally and give your domain account and select Windows Authentication and you will not have any issues thereafter.

|||

Rajesh:

That did the trick!!

Thanks very much for your help.

Regards,

Martin.