Hi everyone
I have a problem about sysusers table in sql2005.
if you run the query in SQL2000
select * from sysusers where status = 2
and you can see the users which are created as sqlauthentication.
But in sql2005 status column equals 0 for sql authenticated users.
Is this a bug or the sturucture of the sysusers table is changed???
And how can i query the sql authenticated users in sql2005.
thanks
Dear All
Within the sysusers table there is a field named status... All the users has this field set to 2. However there is a particular user and the status for this user is set to 14. Does anyone knows what this means... i.e. why the status field is not set to 2 as it is for the other users?
Regards
Sandro
sri writes "Suppose I have a table to enter the name,number,address of employees in different coloumns.I want the names to be as hyperlink.how can i get that?
should we do that at the time of creating database?or do we need to write the code?
If we have to write code please let me know the code also."
I am working SQL Server 2005 and One Table Which contain only one column without primary keyNow I want to remove all duplicate value from that table with only single query
View 2 Replies View RelatedHas anyone else ever restored a database and noticed that any users added into the sysusers table do not show up on the main database GUI under the general tab (i.e., the number of users for that database will equal zero)?
However,a quick look into the sysusers table will show that they are in fact there.
It's very strange.
Any ideas?
Does any one know the meaning of "status" column in the sysusers table?
I found out in one of my database the status has three different values: 0, 2, 14, what this value means? Thanks.
Yu
Hi,
In SQL Server, if I active the application role, can all the public user still read the sysusers table even though the application role does not select the sysusers as read permission?
Thanks.
I want to change one column 's name which have had data in it?
Can I?
When I enter a data in a table, SQL Server automatically completes the data with blanks up to length of column.
this happens in a web form also in Management Studiıo,
Whan am I doing wrong ?
does Collation have anything with it ?
SQL Server 2005 / Developer Edition
Sql 7.0
Is there Tsql to list the roles of all the users(both logged and unlogged)of sql server.
Any help will be appreciated
what is the status column value spcify in sysusers table.
because in our job selecting a row whose status = 2.I am new DBA to this project, and this code were written by previous DBA.Thanks in Advance
Hiya,
I'm trying to run reports, some of which have UserEntered as a criteria. So I'm filling up a list with (SELECT * FROM SysUsers WHERE IsLogin = 1), which is also pulling up INFORMATION_SCHEMA as a login. I'm not really sure why this would be an islogin = 1 or issqluser = 1. Is this a server default login,and if yes,why, and what can I do about it?
Thanks,
Sarah
Is it possible to have a two way replication of the Logins and Users between two servers? What we have is two SQL 7.0 servers, for an CRM package. They use the CRM replication program to move data back and forth. However, we need to keep the logins and passwords between the two machines ther same. Any thoughts on how to do this?
View 1 Replies View RelatedHello:
We are running an application on mssql 6.5, sp 4.
As the data for the application is growing by leaps and bounds, we were shortly going to run out of disk space on one and decided to move production to a new, larger box.
I took a full backup of the current production database nad loaded it onto the new server with no problems except one.
When users tried to log in, there were problems. Apparently the passwords for users were switched with other users or somehow the sysusers data got reversed. THe logins, or the data from msyslogins, looked fine.
I don't understand how a full backup and then a restore would have any effect on the sysusers data.
Has any one experienced this before? Other than bcp out the data from sysusers and bcp'ing the data after the restore, is there any thing else I can do?
Does any one have a script tp offload sysusers data?
Any help which can be provided would be appreciated. THanks.
David Spaisman
I have >200 tables and I want to create a table that lists the name ofeach table, the number of records, and the number of locations withinthe table.I've created a cursor to do this but it doesn't like it. I get thefollowing error.Invalid column name '<tablename>'.Here's my scriptDECLARE @tbl varchar(100)DECLARE @sql varchar(1000)-- Insert statements for procedure heredeclare c_table cursor forselect table_name from INFORMATION_SCHEMA.TABLES where table_type ='base table' order by table_nameopen c_tablefetch next from c_table into @tblwhile (@@fetch_status = 0)beginset @SQL = 'INSERT INTO [zzTable_Status]SELECT ('+ @tbl +') as tblname, count(distinct station__no),count(station__no)FROM [bronze_views].'+@tbl+''exec (@SQL)Print @tbl + ' Updated'fetch next from c_table into @tblendclose c_tabledeallocate c_tableAny help is appreciated...
View 13 Replies View RelatedSID statusid listindex listsize sent
1 12 25 25 1
2 12 25 50 0
3 12 75 150 1
4 14 25 25 1
I have a table like above where cid is unique but status is not for all the status id i need put 1 as they sent out .but till now i used max listindex because they used to send files sequentially but now list index is random so how to update sent to 1 for all the status ids.
I have 2 tables (master and masterbackup), trying to update and replace the status in one with the other based on master.number match
UPDATE master m
set m.status = (SELECT status from masterbackup where number = m.number )
FROM mastermasterbackup
Hi All,
I want to know the status of an index whether it is corrupted or it needs to be reindexed etc ?
I used the sp_MShelpindex tablename stored procedure to retrieve the status also.
In the status column it is showing as 0,2 and 2000 values . what are these values ?
Thanks
Murali
In my SSIS program I have a main package that calls bunch of packages step by step. I would like to find out how can I capture the status of each task once its done and insert the value whether its "success" or "failure" into a sql server table.
any ideas how should I go about doing this?
we have a table in an oracle database containing security information that is updated several times a day. the network connection into that oracle database is not always reliable. we would like to keep a copy of that table in a sql2005 database. the table will need to be refreshed 1/hr. it is a small table so performance won't be an issue.
would ssis be ideal for setting something like this up? i have never created an ssis package before (i'm an oracle dba crossing over). any pointers on how best to set this up?
thanks!
chris
I want to produce a summary table of amounts per status per user.
I have 2 tables:
Invoices:
Code:
user_id, amount, status
1, £10, S
2, £20, P
3, £30, P
3, £40, E
Users:
Code:
user_id, name
1, user A
2, user B
3, user C
And I want to produce a summary table like this:
Code:
S P E Total
user A £10 £10
user B £20 £20
user C £30 £40 £70
What I have is:
Code:
SELECT Users.name,
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'S')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'P')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'E')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices .user_id AND (t1.status IN ('S','P','E')))
FROM Invoices
LEFT JOIN Users ON Users.user_id = Invoices .user_id
GROUP BY Invoices.user_id, Users.name
ORDER BY Users.name
This does give me what I want, however the real situation has lots of status codes, many more fields in the Invoices table, hundreds of users and hundred of thousands of records in the Invoice table and I have run out of system memory.
Hello,
I have a table which i use to queue messages in. in this table there is a status column along with all the other message colums (ID, body, phone, time, etc). I use tinyint to express the diffrent status values (my frist question is, should i continue using tinyint, or is there some sort of enum, or another suggestion?). the value 1 means its a new message. An external process polls this table and pics up messages that are new.
Some messages fail, and so their status is changed to two. An external post process comes along and does what it needs to with messages that have status' which it handles, for example message failures, it might erase it, or change its status back to 1.
I expect to handle thousands of messages per hour. Should I create another table, which has only id and status as columns. And when a message fails I will place it in here instead of placing it in my original table. then the post process only polls this table and joins with my main table. instead of having both processes poll the single table. Would this be more efficient for sql internal seraching? since it has a smaller table to search in? or does it not make a diffrence.
Thank you.
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
Hi,
I have an aplication (VS2005 and Sql2005 Express), where I need to lock a table when opening one form, and then close it when completing another form.
Is there a way to do this, maybe using a Stored Procedure.? I'd prefer to be still able to access the table in read only mode if possible.
I've looked at pessimistic concurrency, but the particular part of my application is not using a dataset, being based upon SqlCommand.ExecuteScalar(), so I'm fairly sure this is not an option.
Thanks
In my SSIS package I used the Web Service task to populate a SSIS variable with data from an XML feed. Then in the Data Flow I used a XML Source to import that data into a table on my SQL Server. When I looked at the data i noticed that the some of the fields contained CRLF pair. These CRLF pairs are being only populated by xml nodes that contain no data and are self-terminated. Below is a sample of the XML feed that is causing the problems. the DR_M_Initial and DR_Type1 nodes are populating CRLF's to the table.
Can anybody give me some insight on this problem. I am performing a workaround using the SQL REPLACE function in an Update statement to strip out ch(10) and ch(13), but this just seems a little cluegy to me.
Sample XML
---------------------------------------------------------
<?xml
version="1.0" encoding="utf-16" ?>
- <ROOT xmlns="">
<ROWRESULT>
<DR_NUMBER>1</DR_NUMBER>
<DR_L_NAME>SMITH</DR_L_NAME>
<DR_F_NAME>JOE</DR_F_NAME>
<DR_M_INITIAL />
<DR_TYPE1 />
</ROWRESULT>
</ROOT>
Thanks,
Tim Frakes
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
Is there a possibility to import a table from sql 2000 into sql 2005 express?
I have a table exportet from sql2000 to csv, adjusted "FALSE" and "TRUE" into 0 and 1 and saved it to csv. Now I want to replace a table in the sql2005 DB by the csv. (The first row contains the header, each row contains a record.
I'm a n00b, as you can see ;)
Thanks for your help ;)
Peter
How to get the list of table names in a SQL2005 database?
View 6 Replies View RelatedI am trying to import an Excel Spreadsheet into SQL2005. There is a column in the spreadsheet that has character values, and numbers. I have formatted the numbers as text on the spreadsheet. I have declared the column on the table as char/varchar/nchar, but whatever I do, the numbers don't get imported into the table, but show up as nulls. Any idea why?
Thanks
Mangala
Hello all,
View 12 Replies View RelatedI just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies View RelatedHi
We have Sql2005 x64 bit standard edition server installed in windows 2003 64 bit editio server,
currently due to buisness requirements we need to have sql2005 x64 bit enterprise edition, please let me know how do i do the upgrade or change.
is it possible to retain all our custom settings in the standard edition after changing to enterprise edition.
This has to be done for our production and very critical, please help
Thanks
Samuel I
Im from Russia, sorry if my english is not very good.
Here's the case:
1)-------------------------------
I made a DTS-package in sql2000 that transfers the [sql table] into [dbf file] via jet4.
First i create (in delphi) the empty dbf with the same name and columns same as in sql table.
Second, I run my DTS with variables - source and destination table names
In DTS there is source, destination and transformation . After I send the Variables(table names)
, the transformation "arrow" needs to be "refreshed" to make column names in both tables correspond each other. For that in transformation I chose ActiveXScript Mode and wright VB Script:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
dim i
For i = 1 To DTSSource.Count
DTSDestination(i) = DTSSource(i)
next
Main = DTSTransformStat_OK
End Function
And it works
2)------------------------------
I want to do same thing in sql2005 SSIS but don't figure out how...
I managed to make a package that recieves (in variables) table names and runs correctly.
But after I change those variable names into any other it crashes -
Description: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Of cource this happens 'cause I didn't "refresh" the transformatoin (and maybe also source and dest), but I don't know how.
Anyone can help ?!