Hi all,
running sql server 2000 sp4, viewing the sysxlogins table data, and am wondering about the three items listed with a <NULL> in the name column, all xstatus = 192, each has a unique srvid value of 0, 2 or 3.
Looking in the MS tables reference book did not help they don't cover sysxlogins, googled around but no luck, searched this forum also.
What are these entries?
Thx
It seens that a simple script:Update sysxlogins set name = 'AA001' + substring(name, 9, LEN(name)-8)where name like 'ILLINOIS%'Will replace the SQL2000 domain name correctly in sysxlogins:ILLINOISJonesP becomes AA001JonesPBut for some strange reason via ILLINOISJonesP can still logon viaQueryAnalyzer although he is no longer in the sysxlogins tableanymore? SQL has been stop/started, server even rebooted, yet BOTHthe new and old logins seem to both allow QA login, any thought howthe old one is getting thru SQL security?thanks in advance for any help...
View 2 Replies View RelatedHi All Gentleman,
I am writing a script to control rights and privileges regarding roles and login .
I have upgraded my server from sql server 2000 to 2005.I have a script which gives me all the logins that moved from sql server 2000 to sql server 2005 (i.e same instance)or on same server.I have multiple database say for example 5 database.Now my task is to grant all programmers login to programmer role and all logins belonging to this role should be granted only select permission on table on all the databases.
Can anyone help me with the script for my task?
I have observed that sysxlogins table exist in sql server 2000 however it does not exist in sql server 2005.
Can anyone tell me what is the equivalent of sysxlogins system table in sqlserver 2005?
Hi,
Is there any way of decrypting password value stored in sysxlogins table of SQL database?
Thx in Adv
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
Hi,
I have a table in which out of 20 columns, there will be data only in a few columns. So, I need to find those column names which have data in them. I was able to do it using the information_schema and using a cursor to loop through all the column names and find out which columns have data in them. But i need to know if there is a more efficient way of doing this without using the cursor. Can somebody please let me know how this can be done without using a cursor? Thank you.
I'm trying to do an update query that looks like this:
UPDATE
PAEMPLOYEE
SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL
FROM
PAEMPLOYEE A
JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE
It's erroring out on the Employee prefix B.EMPLOYEE saying:
..."does not match with a table name or alias name used in the query"
Is it wrong or will it cause problems to have a field name the same as the table name?
Hi,
I have tried to synchronize master..sysxlogins table from production server to
a testing server. All the databases in the testing server will be loaded from the production server. In order to save drop and recreate the database's users in every database restore, I have tried to find out a quick way to synchronize the sysxlogins table. However, when I load the sysxlogins from the production server onto the tempdb in the testing server, I get the following error :
Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Any comment and suggestion is highly appreciated.
Thanks
The table structures (output of sp_help) of the temporary table and master..sysxlogins are as follows :
1. sp_help testjoe4
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ---------------------------
testjoe4 dbo user table 2002-02-19 15:27:31.930
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
srvid smallint no 2 5 0 yes (n/a) (n/a) NULL
sid varbinary no 85 yes no no NULL
xstatus smallint no 2 5 0 no (n/a) (n/a) NULL
xdate1 datetime no 8 no (n/a) (n/a) NULL
xdate2 datetime no 8 no (n/a) (n/a) NULL
name sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS
password varbinary no 256 yes no no NULL
dbid smallint no 2 5 0 no (n/a) (n/a) NULL
language sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS
isrpcinmap smallint no 2 5 0 yes (n/a) (n/a) NULL
ishqoutmap smallint no 2 5 0 yes (n/a) (n/a) NULL
selfoutmap smallint no 2 5 0 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
2. sp_help master..sysxlogins
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ---------------------------
sysxlogins dbo system table 2000-08-06 01:29:12.500
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
srvid smallint no 2 5 0 yes (n/a) (n/a) NULL
sid varbinary no 85 yes no no NULL
xstatus smallint no 2 5 0 no (n/a) (n/a) NULL
xdate1 datetime no 8 no (n/a) (n/a) NULL
xdate2 datetime no 8 no (n/a) (n/a) NULL
name sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS
password varbinary no 256 yes no no NULL
dbid smallint no 2 5 0 no (n/a) (n/a) NULL
language sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS
isrpcinmap smallint yes 2 5 0 yes (n/a) (n/a) NULL
ishqoutmap smallint yes 2 5 0 yes (n/a) (n/a) NULL
selfoutmap smallint yes 2 5 0 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ncsysxlogins nonclustered located on PRIMARY srvid, sid
sysxlogins clustered, unique located on PRIMARY srvid, name, sid
No constraints have been defined for this object.
No foreign keys reference this table.
Table is referenced by views
--------------------------------------------------------------------------------------------------------------------------------
How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
[code]....
I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks
View 7 Replies View RelatedHello,
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.
I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.
how to solve that problem?
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
[code]....
Hello,
It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?
For example, a simple stored procedure is this:
CREATE PROCEDURE SelectTop(@tableName sysname)
AS
BEGIN
Execute('Select top 10 * from ' + @tableName + ';')
END
I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
Thx
Oracle provides for a table to have an alias name.
Does SQL Server 7 support alias names for tables?
Thanks!!
I like to print the table names that starts with 'table'.
I'm getting the same table name four times. Instead of four different table name.
create table table1(tableID int, tableName varchar (30))
insert table1
select 1, 'tableSam' union all
select 2, 'tableDaniel' union all
select 3, 'tableRon' union all
select 4, 'tableKen' union all
select 5, 'tableHonda'
create table table2(tableID int, tableName varchar (30))
insert table2
select 6, 'tableSam' union all
select 7, 'tableDaniel' union all
select 8, 'tableRon' union all
select 9, 'tableKen' union all
select 10, 'tableHonda'
create table table3(tableID int, tableName varchar (30))
insert table3
select 11, 'tableSam' union all
select 12, 'tableDaniel' union all
select 13, 'tableRon' union all
select 14, 'tableKen' union all
select 15, 'tableHonda'
create table table4 (tableID int, tableName varchar (30))
insert table4
select 16, 'tableSam' union all
select 17, 'tableDaniel' union all
select 18, 'tableRon' union all
select 19, 'tableKen' union all
select 10, 'tableHonda'
declare @string varchar(330)
declare @tableName varchar(30)
declare @count int
set @count = 0
while (@count < 5)
begin
select @tableName = table_name from information_schema.tables
where table_name like 'table%'
exec(@string)
print @tablename
set @count = @count + 1
end
In SQL Server Management Studio when browsing the tables they are all prefixed with dbo followed by the table name. Is there a way of turning this off.
Thanks
Is there a way i can create a query that gives me a list of all the table names in the database?
View 2 Replies View RelatedHi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists.
hi...
can someone pls tell me the sql statement to
1)Select all the tables in SQL server(only the ones created by me)
2)Select all the column names from a given table
tks a lot... :)
Please help,
I am inserting data into a newly created table with many rows similar to thoes below using a sp with a parameter @Extract
CREATE PROCEDURE mytest
@Extract int
AS
DECLARE @MyName varchar(25)--these keep changing for each total
DECLARE @Counter varchar(2)--this will increment from 2 to 40
SELECT @MyName ='tblG3>74Ex' +convert(varchar(3), @Extract)
SET @total1 =(SELECT COUNT(*)
FROM @MyName
WHERE (sSurgery_id ="S0" + @Counter) and (iExtract_nm = @Extract)
SELECT @MyName ='tblG335-74Ex' +convert(varchar(3), @Extract)
SET @total2 = (SELECT COUNT(*)
FROM @MyName
WHERE (sSurgery_id ="S0" + @Counter)and (iExtract_nm =@Extract)
insert into tblNew([35-74],[>74],etc,etc,Report4)
values (@total1,@total2,ect,ect,@report4)
it all works until I try to use the @MyName
Please put me out of my misery
Many thanks
David
While setting up a BCP in a DTS package, I've found a rather annoying 'feature' in DTS. When selecting the destination table, the display does not provide enough spaces to see the full table name. It is a drop down with a set width, and the database and owner are displayed with the table name. I hadn't noticed this problem in the past, but the database I'm working with now has several tables that start with the same letters, and when the drop down displays them, they all look the same.
Does anyone know of a work around for this so that I can see the full table names? Even being able to see them after the package is created would be nice, because then I could check to see if I guessed the correct table.
Thanks
Can I do something like the following with SQL Server?
DECLARE @Table_Name varchar(100)
SET @Table_Name = 'Accounts'
SELECT * FROM @Table_Name
Using VB5|6/ADO/MSSQL6.5. I want to get the names of tables in a query. I.e. if query is "Select Name, Title from Employee inner join job on employee.empid = job.empid" I want a way to get "Name, Title"
In VB 3 we used the VBSQL.VBX which had 2 functions for this: SQLTabCount and SQLTabName.
Thanks in advance
DP
Hi All,
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Thanks a bunch.
Val
Hi, I need do get a columns names from a table? How to do this in pure SQL? I thought about creating a stored procedure or user function with a result of a string ( col1name,col2name ....) I do not know how to count the number of columns in a specyfied table? Any help would be appreciated. Thanks in advance. magicxxxx
View 2 Replies View Relatedhere a quick rundown of what i want to do:
database
database1
database hold all my good information.
database 2 is the copy for breaking.
Im too a point that i need to get my 2nd DB to by like the first.
I want to truncate all the tables in database2, and then export, or select from insert into.
I just need the data, not triggers or anything like that.
So:
How can i find the name of my tables (not views) in that database
For each (select table_name from system where database="database") copy database.table_name database1.table_name;
i hope that makes sense.
I just need to restore my devel DB back to the original so i can mess it up again,
Thanks
I have a cursor which populates a variable with the name of each user table within my DB.
I'm trying to copy the tables one at a time by using a command like this:
SELECT * INTO @NewTable FROM @OrigTable
Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'.
This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this?
Are there any alternative ways of copying the table structure (preferable without the data)?
BTW both variables are of type char(100)
In a stored procedure returning several tables how can I assign a name to each table.
I wish to select the table in my app using DataSet.Tables("MyTable") rather than DataSet.Tables(0)
Create Procedure myProc As
Select * from table1
Select * from table2
Select * from table3
Go
Hi,
I have a database that have two handred tables.I want to get all the table name list.
Would you please help me if you know how to do this (query or build-in stored procedure).
Thanks
Mark
Hi all,
I have been attempting to construct some dynamic SQL using a table name as a parameter, and call it using sp_executesql, in SQL Server 2005.
According to all of the searches and articles I have read today, the following should work:
DECLARE @rownumINT
DECLARE @tabNamNVARCHAR(100)
DECLARE @SQLStringNVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
SET @rownum = 35
SET @tabNam = 'AdminScriptHistory'
SELECT @SQLString = N'SELECT * FROM @table where executionorder=@row'
SET @ParmDefinition = N'@table VARCHAR(100), @row INT'
EXEC sp_executesql @SQLString, @ParmDefinition, @tabNam,@rownum
However, it fails with:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@table".
Anybody know why?
Cheers