Query On Syslogins Table
Aug 17, 2004Pls help me out with this query as
select * from master..syslogins WHERE hasaccess = 1
as it is both giving me record where hasaccess = 0 and 1
brgds
ADVERTISEMENT
BCP IN SYSLOGINS - SQL 7
Jul 25, 2000In a recent Disaster Recovery TEST, I successfully recovered some SQL Server 6.5 databases by re-installing SQL Server, applying the SP5a, restoring the application database, and then BCPing IN the syslogins. Everything went normal, as I have done this many times.
I had one SQL 7 to recover. I used the same procedures, however, the BCP in did not work. I ended up getting a Dr. Watson message (not sure if it was related to the BCP or not). Luckily I had the Logins scripted and was able to apply them as follows:
USE master
go
EXEC sp_addlogin 'user1',
0x21312144332c534025532f3a442d5e4c,
@defdb = 'DBNAME',
@deflanguage = 'us_english',
@sid = 0xf8ddfa809a05d411a56600508ba705cb,
@encryptopt = 'skip_encryption'
go
This seemed to work, and luckily there weren't many users in this database.
How do you get the syslogins in for SQL 7?
Thanks,
Carl
Migrating Syslogins
Apr 6, 2001We are looking to migrate an entire server with several databases which collectively contain every user in the company. Obviously, I would prefer to directly migrate the logins without having to make everyone reset their own passwords. Anyone know of a way to do this?
I tried dts'ing the syslogins table but it fails due to password collumn being read only.
Syslogins In 6.5 - Urgent Question
Jul 18, 2001hi,
I created a new 6.5 sql server. I brought all the logins from the old
server. I bcp out the syslogins from the old server and bcp into the new
server. There were no errors from the bcp in. Somehow, I still can't
login to the new server using the same password from the old server.
Why?
how can I fix it?
thanks
Bcp Syslogins SQL65->SQL7
Mar 23, 2000my problem is : I want to export syslogins from database SQL6.5 to syslogins SQL7.0.
I use BCP (command : mssqlBCP master..syslogins out d:empsyslogins.dat -c -Sservername -Usa -P -ed:emplogins.err)
It runs well.
AFTER to export to SQL7 database :
(command : mssql7BCP master..syslogins in d:empsyslogins.dat -c -SNEWservername -Usa -P -ed:emplogins.err)
I always have DR WATSON.
If someone has encountered this problem and solved it.
Your help will be strongly appreciate.
Patrick
Replicate Syslogins & Sysusers
Oct 22, 1999Is 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 RelatedSQL Server 20000 Syslogins
Aug 24, 2000Just installed SQL Server 2000 Beta 2. I have a procedure that selects the suid from the syslogins table from a SQL 7.0 version that is now saying that column is not valid.
All the documentation I can find still refers to that being a valid column.
Any suggestions?
Logins In Master..syslogins
Jun 21, 2001How can you manually update the syslogins table in SQL 2000? It keeps giving me the following error:
Server: Msg 4406, Level 16, State 2, Line 1
Update or insert of view or function 'master..syslogins' failed because it contains a derived or constant field.
I used to be able to do it in 6.5.
Syslogins, Database Logins Script.
Dec 15, 2004Does anyone have a script already which pulls all the logins and what databases they have public/dbo access to? I'm looking for something more than just pulling syslogins/users and their default dbname, I need a list of all the databases they have dbo access to.
Thanks, Jim
Setting Permissions On Master.dbo.syslogins
Jul 23, 2005I've got a tool that accesses syslogins to pick up some information.When I run the tool, I get the error message that sasys that my logindoes not have sufficient permissions to read syslogins. If I run undermy admin ID, everything is fine. HOWEVER, the intention is that thistool will be used by non-DBA staff members so we have a generic idcreated to run this tool specifically (non-DBA account).OK, so I go to Enterprise Manager and open up the master database, goto syslogins and add my non-DBA id to the permissions list as havingSELECT access, and click on EXECUTE.BOOM! No sign of my permissions being set.Am I missing something? If so, what do I need to do to set permissionsagainst this view?
View 1 Replies View RelatedSQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table
Jun 26, 2015how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?
Jun 25, 2007Hi, all experts here,
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
With best regards,
Yours sincerely,
Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?
Jul 19, 2012I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
Simple example:
declare @tTable(col1 int)
insert into @tTable(col1) values (1)
select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
How To Create A Make-Table Query With A Union Query
Oct 30, 2006I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?
View 2 Replies View RelatedQuery To Give All Table Sizes On A Database (was Query Help)
Mar 9, 2006Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks
Naming Query Column Name Of Resulting Table Name From Query
Oct 23, 2014I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..
Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
Hint: Use the sys.tables catalog view.
I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example..
Transact SQL :: Query Plan Shows Table Not Even In Query?
Jul 22, 2015I am trying to optimize a stored procedure in SQL 2008. When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? I can't just change the query script because the table in question isn't there.
Query To Only Display Information From One Table Where The Foreign Key Doesnt Exist In The Other Table.
Nov 28, 2006I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.
View 1 Replies View RelatedNeed Help W/ Postback To 'Customers' Table On Form Using Select Query From 'Parameters' Table
Dec 20, 2007I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.
Append Query From Access Table To Linked SQL Server Table Failing
Jun 18, 2004Strange one here - I am posting this in both SQL Server and Access forums
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
Brad
Query Based Off Primary Key Of Parent Table - Adding Child Table
Jan 28, 2012I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
Query To Return Records Where One Table Is Void Of Linked Data In Another Table
Feb 13, 2008I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Thanks in advance,
Mark
Transact SQL :: Create Hierarchies Table Or Query From Multi Parent Table?
May 21, 2015convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent
Using An Exec Query To Insert Pdf, .doc File Into Table From A Dir Path Which Is A Field In Another Table
Aug 5, 2007I have the following query in sql 2005:
PROCEDURE [dbo].[uspInsert_Blob] (
@fName varchar(60),
@fType char(5),
@fID numeric(18, 0),
@bID char(3),
@fPath nvarchar(60)
)
as
DECLARE @QUERY VARCHAR(2000)
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
Any ideas how to do this?
Trying To Save Editted Textbox Value In Table But Original Value Saves Instead - Trouble With Table Update Query
Jan 9, 2008This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
View 1 Replies View RelatedAppend Query To Insert 80000 From One Table To Empty Table
Jul 22, 2012All, Using access 2003 frontend and sql server 2008 backend. I have an append query to insert 80000 from one table to an empty table. I get an error:
"Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and didn't add 36000 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations."
I know this error normally comes if there are dups in a field that doesnt allow.
Query To Move Certian Data From Sql Table To Access Table
Dec 14, 2007hi all,
is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.
thanks
Multiple Table Query - How To Get List Of All Values For Each Table
Jun 7, 2012I have a database that has 370 tables that match %_REF_% naming. All of these tables have a column call ds_name.
I need to get a list of all values of ds_name for each table.
SQL Server 2012 :: Update Table Using CTE Or Using Inner Query On Same Table
Jul 29, 2015To avoid locking/blocking, or in transaction scope, we are trying make a common practice of writing coide for update commands in our all SPs based on primary key columns in where clause. I have a following scenario...
UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerIssuanceId IN (SELECT LockerIssuanceId
[Code] ...
What is the better approach and should be followed to avoid locks and gain performance or best approach.
Naming A New Table From A Make Table Query
Mar 25, 2004I want to use a Make Table Query to generate a new read-only Table. This I can do but my problem is that I want to be able to name the new Table containing only the records from a calendar year; e.y. Rents2001 and the next year another new table would be created and called Rents2002 and next year Rents2003 ...............
I require the Table to be generated yearly. I know I could do this in other ways but I really require the Table as once I have it I will be doing other things with it to give the final report.
Any suggestions how I can generate the Table with the YEAR being in the Table Name as part of running the Make Table Query? Thanks
Query A Table Whos Name I Pull From Other Table
Feb 4, 2008I have recently moved jobs and have come across an unusual database setup that is not ideal for the resolution of my task at hand.
I need to query table A, which will return 1 and only 1 tablename as a result. there are various tablenames on table A but the query will always return a unique result. (from Table B-G for example)
My problem is now, how do i run my next query on the table i have returned from my first query. I need to be able to reference the value.
Making sense?
Ideal scenario would be if tables B-G were on a single table and had a unique identifier, but this architecture cannot be changed for numerous reasons and a workaround needs to be established. your help is much appreciated,
How Do I Query A Table For Matches With Keywords From Another Table?
Aug 24, 2007This is what I wan to do: If I, for example, have a table containing articles, and I want to allow users to return any article that matches a filter, which is defined by any number of keywords, located in another table.
My tables:
Article table: [ArticleID, Headline, Content]
Filter table: [FilterID, Name]
Keyword table: [KeywordID, Word]
FiltersKeywords table: [FilterID, KeywordID]
(The FiltersKeywords table serves as a many-to-many relation table between the Keyword table and the Filter table)
In other words, a filter, which is used to selected articles with, can be made up of any number of keywords. The users would thus be able to build on the filters, adding new keywords as time goes, removing old none relevant etc. I suspect some filters could end having anywhere from 100 to 1000 keywords associated with them.
Using SQL 2005, is there anyway to query for articles in an efficient manner? How would the code look?
Thanks, Egil.
Query Oracle Table In SQL && Put In Temp Table
Nov 6, 2006Hi all,
I am querying a table in oracle, the server connection to the Oracle database is determined by a criteria. Though how can I put the results from the oracle query into a temp table ?
This is the code i'm using for the query:
DECLARE @cmd VARCHAR(500)
declare @Year varchar(25)
set @Year = '2006'
DECLARE @Link VARCHAR(100)
DECLARE @Table VARCHAR(100)
select @Link = Server from tbl_Conn where Area='Floor'
select @Table = Target_Table from tbl_Conn where Area='Floor'
SET @cmd =
'
select * from OPENQUERY
(
' + @Link + ',
''
UPDATE '+ @Table +'
SET TARGET_VALUE = '+@Value+'
WHERE Date = '+@Year'
''
)
'
EXEC (@cmd)
How do I put the executed results into a TEMP table ?
Rgds,