Temporary Table Not Dropped After User Disconnected (SQL2005)
Oct 7, 2005Hello all,
View 12 Replies
ADVERTISEMENT
After Moving To SQL2005, Disconnected Recordsets Are Ready-only
Feb 16, 2006We're using ADO disconnected recordsets. On SQL 2000, we could update these on the client (without propagating the changes to the server) even if the underlying view or table was non-updatable.
When running our apps against SQL 2005 (using the same client-side environment), we can no longer change any attributes of those disconnected recordsets, that connect to a non-updatable database object (the rest of the app runs fine, we can update all updatable database objects through disconnected recordsets) . Does SQL 2005 respond to such calls differently from SQL 2000, so that ADO recordsets are built in a new way (which makes them read-only in our setting)?
Thanks for any suggestions.
Rene
User Being Dropped From Dbases
Nov 30, 2000Hello,
I have the following problem that I need to find a solution. I have created a generic user id and login in the model datbase on SQL 7. However the genericuser does not show in the explorer window for some databases, does not work on some databases. I have to go into TSQL and use dropuser to get rid off them and re-link them again.
Does any1 have a clue about why this happening? How it can be solved?
TIA Gurmi
Database User Cannot Be Dropped
Mar 2, 2005Hi..I just restored a databae from backup. After restoring, as usual, from the Enterprises manager, I want to delete the database user and readd again from the DB login name. But when I tried to delete the DB user, it prompts me the following.
"the selected user cannot be dropped because the user owns objects"
why? I never encountered this b4 ? I restored DB again but problem still the same. Any ideas?
Royal F-up, Dropped User Default Database
Jan 27, 2004I am by no means a db administrator, so I apologize for my ignorance. However I do know that I screwed up and am unable to find the solution...
The small company I work for has a development SQL Server where we do testing and development for web based applications. While using SQL Server Enteprise Manager I accidentally dropped the user's default default database.
I have always been able to access the enteprise manager via windows authentication, but now I am unable to. Is there any way to set the default database back to 'master'? Currently I cannot connect with SQL Query Analyzer for the same reasons, and do not have any other access except via windows authentication. Is there any hope? Any help is much appreciated.
Getting Data From Two Disconnected Table !
May 4, 2008
Hi,
We have two disconnected tables as shown below.
Table A Table B
Fields Class_NO Class_NameT State Class_No ColA ColB ColC State
Values 1 x S 3 xO e e S
2 XR S 4 UI re er S
9 YU w re S
8 OP we we S
We want to display the data from table A and table B as below.
Class_No Class_Name State Calss_NO Cola ColB ColC State
1 x S 3 xo e e S
2 XR S 4 UI re er S
9 YU w re S
8 OP we we S
Can it be done ? What should be sql query.
How To Setup The User Access Right To A BackupOperator User In SQL2005.. Thanks
Nov 21, 2007I would like the Backup SQL user A can backup and restore DATABASE_a, what access right I need to grant to him. Although he can backup the database_a to the c:Program Files.....Database_a.BAK but he cannot restore it with some message said ''not enough security privieges..
Please quote some example right assigment so that I can replicate to our environment
Many Thanks...
Dropped Table
Jan 25, 2001I am helping another division with a problem of a dropped table. They have no backup and after dropping the table, the server has been re-booted 3 times. Is there a way to recover the table with no backup?
Help anyone,
Thank you.
Who Has Dropped SQL Table?
Jan 16, 2007Somebody has dropped my SQL table in (multiuser environment),now I want
to know who has done that. Is there any way by which I can retrieve
that(user name or machine name)?
I am using SQL 2005.
The Dropped Table Does Not Have A Primary Key And Cannot Be Used
Feb 25, 2004I'm trying to drop a table onto the design view in Web Matrix and the following message appears: "dropped table does not have a primary key and cannot be used".
I'm using a SQL 2000 database that was previously an MSDE 2000 database. Is there anyway that I can define a column as a primary key?
Thanks.
Is It Possible To Recover A Dropped Table?
Jun 3, 2004Hi All,
Accidentally I have dropped a table and that is the main table for my application.
I dont have a backup of it. Is there any way to recover it.Please help me guys.
Thanks,
Kumar.
How To Recover A Dropped Table
Dec 9, 2011Unfortunately I dropped my table in sqlserver. How to I recover that table?
View 3 Replies View RelatedDropped Table But Have PHP Script
Mar 19, 2015After installing a Zen Cart plugin I was getting errors that there was a duplicate entry for the mysql table so I went in and dropped the table assuming that it would come back when I re - installed everything, anyways I have the PHP code that created the table but not sure how to recreate it or if that's even possible. Also my main way to get to change anything is phpMyAdmin, I tried importing the code there but that didn't work.
View 2 Replies View RelatedRestoring A Dropped Table - No Backup - HELP!!
Nov 24, 2000Hello,
I'm in trouble, I have just dropped a table from a sql dbase and now need to recreate it. I have no backup and I am panicking!!! I need to know if I can recrreate them .
Any and all help appreciated
DVNC
Dropped Table - Restorring With No Backup - VERY URGENT
Nov 24, 2000Hello,
I'm in trouble, I have just dropped a table from a sql dbase and now need to recreate it. I have no backup and I am panicking!!! I need to know if I can recrreate them .
If this can't be done let me know I have to bit the bullet
thanx
Release Table Space After Column Is Dropped
Apr 12, 2008I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.
thx
Temporary Tables In Multi User Environment
Feb 6, 2004Hi,
I am developing reporting application (access project) which will be used in multi user environment.
Here is what I have:
1 SQLServer database for many users
Each report will be based on:
stored procedure which creates a table filtered for specific dates predefined views will use the newly generated table to show results to the client. However, if more than 1 person runs reports results will not be accurate if each person specified different dates because they will look at the same table and results will match only for a user who called the stored procedure last.
what can you recommend - how to report in multi user environment?
Many thanks
User-defined Function And Temporary Tables
Mar 10, 2006Hiya,Is there really no way to use temporary tables from within a UDF? Ilike UDFs because I can use them like a table -- e.g., SELECT row1,row2 from MyFunc('abc')
View 1 Replies View RelatedSQL Server Admin 2014 :: Identify Who Dropped A Table
Jun 30, 2015I'm getting a replication error that an object (Table) was not found. Any script that can capture this information?
View 3 Replies View RelatedWhat Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure
Aug 29, 2007which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedPossible To Have Temporary Rows In A Non-temporary Table? Lifespan On Rows..
Mar 14, 2004I have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.
My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?
SQL Server Admin 2014 :: Schema Change History - Identify Who Dropped A Table?
Jun 30, 2015How can I easily identify who dropped a table?
View 8 Replies View RelatedTable Names In Stored Procedures As String Variables And Temporary Table Question
Apr 10, 2008How 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.
SQL2005 User Id Naming Issue?
Dec 7, 2006Hey all
Am using SSMS to add users to a DB and here is an issue I cant conquer.
User id's in this format "Aname" work as designed
User Id's in this format "John P User" will not be recognized by ssms as valid windows user names even though "John P User" is Administrator of the local computer or even Domain Administrator. I have tried a script to enter the name and still the multiname format errors out where the normal naming format works every time..
Im new to the forum and very green in this arena, I have scanned 43 pages looking for a similar issue and have found nothing even remotely similar in a post.
I would be thankful for any bones you wise people could throw my way.
Best: Phil
Creating A Common Table Expression--temporary Table--using TSQL???
Jul 23, 2005Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedIs It Possible To Insert Data Into A Table From A Temporary Table That Is Inner Join?
Mar 10, 2008Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789
How Can A User Change Password In SQL2005 Via TSQL?
Mar 30, 2007I have an application that controls user logins, passwords, etc. at the front end for a SQL database. I am in the stage of migrating to SQL2005 and cannot get the TSQL code to allow a user to change their own password. Here's the background;
The ADMIN of the app is a Sysadmin on the SQL server and can create logins, set roles, etc. Assume the Admin creates a user TOM with a password of xxxx. This works fine using the create login statement from a Connect.Execute statement from my app like so;
"Create Login 'TOM' With Password 'xxxx', Default_Database = 'myDB', Check_Policy = OFF"
TOM will be setup with db roles as well
When TOM logins into my app, he will have to change his password at some point. The TSQL code I am using (which fails) is executed by TOM who has a connection to the SQL db because he is logged into the app.
"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx', Check_Policy = OFF"
At this point I get an error:
RunTime error -2147217900 (80040e14)
ODBC SQL Server Driver][SQL Server] Cannot alter
the login 'TOM', becuase it does not exist or you do
not have permission.
Obviously it exists since TOM is currently logged into the SQL Server. So if it's permissions related, what permissions does a user need to change his/her password? Or is there another way to do it?
Thanks in advance for your help.
CH
Listing Size Of All User Databases In SQL2005
Aug 9, 2007Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?
Thanks,
Difference In Creating Temporary Table By #table And ##table
Nov 29, 2006Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
Temporary Table Vs. Table Valued Function
Jun 6, 2006I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...
Or using a table-valued function to return a temp table as the result.
I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
SQL2005: Running TSQL Commands In Single-user Mode
Dec 20, 2007I'm having trouble running TSQL commands when SQL2005 is in single-user mode.I've restarted SQL with -m, -c, -T3608 set in the startup options.I can get into Config manager OK, but as soon as I try to start a NewQuery it complains that the DB is in single-user mode and there'salready someone connected to the DB.Is there something I need to turn off? If not, how am I supposed torun queries in single-user mode? What I want to do is move the modeland msdb databases but I can't run the commands to do this until I getpast this problem.Thanks for any assistance you can give on this.Mike
View 6 Replies View RelatedSql2005 Express 'windows NT User Or Group Not Found. Check The Name Again.
Feb 26, 2006I have installed visual studio 2005 with sql express. My tutorial book asked me to do the following to be able to use sql.
sqlcmd -S mycomputernameSQLExpress -E
which worked and then I get
1>
where I am to put in
1> sp_grantlogin [mycomputernamemyusername]
2> go
after this i get the error.
Msg 15401, Level 11, State 1, Server mycomputernameSQLEXPRESS, Procedure sp_grantlogin, Line 49
Windows NT user or group 'mycomputernamemyusername' not found. Check the name again.
I used Local System for the user name as that is what I think I am seeing as the right user account.
Help?
Temporary Table
Feb 1, 2004Hi
I am using SQL 2000 I have the following code. When saving it, I am getting an error:
There is already an object named '##tbl' in the database
This is although #tbl is dropped.
Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.
declare @x int
set @x=1
IF @x=0
begin
create table #tbl (
abc int
)
drop table #tbl
end
IF @x=1
begin
create table #tbl (
abc int
)
drop table #tbl
end
Than you.