Restore Messed Up Table Names
Sep 2, 2006
Using SQL Server 2000 and moving to a new computer. We did a full backup of
the existing database to tape, brought up the new computer with a clean
install using the same server name and IP address, and did a full restore.
Not only were some permissions messed up, but Crystal Reports 10 and some
Access Data Projects refused to run. I finally discovered while running an
SP_WHO that the individual database names that we'd created (meaning not
'master' and the other standard tables) had several dozen blanks appended
onto the end of them. Looking at dbnames in the SP_WHO made it clear that
this had happened, and once I knew what I was looking for it was apparent in
Enterprise Manager as well when I'd select a database name in the left pane.
Interestingly, VB6 applications have no trouble connecting to these tables
without modification of the connection string. Every single CR10 report so
far has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.
1: How could something like this happen?
2: How is it best fixed?
Thanks!
David
View 3 Replies
ADVERTISEMENT
Mar 1, 2015
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.
View 1 Replies
View Related
Jan 22, 2004
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.
View 5 Replies
View Related
May 9, 2008
Hi SQL server experts,
I'm trying to restore from a backup file.
I need to restore the datafile and logfile to different names.
I modified the options page, changed the 'Restored As' file name.
I clicked on the General page and Clicked on the Options page again, the old values came back.
How do I handle this?
Thanks in advance.
Pingx
View 3 Replies
View Related
Feb 20, 2007
Hi,I am planning to automate a nighty restore of a DB on another servercan someone point me in the right direction with the SQL script tomodify the logical file names to the correct path and not the onescarried over with the DB??i.e the database is to be renamed on the new serverany help much appreciatedMany thanks in advance
View 14 Replies
View Related
Jan 21, 2004
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?
View 5 Replies
View Related
Jul 20, 2005
Hi,Consider this (light example):SELECT [template].[id], [template].[navn], [tvalues].[id]FROM template LEFT JOIN Tvalues ON [tvalues].[templateid]=[template].[id]This returns ok. But I have a clause on the Tvalues like this:WHERE [tvalues].[nr]=1;Now I dont get what I want anymore? What I mean here is:"return all records from Template and the corresponding values in TValues.If no corresponding values can be found or those where TValues.nr<>1 thenreturn NULL".Erhm...`Hope u get my meaning.../Pip
View 3 Replies
View Related
May 13, 2008
There I was merrily working away on my SSIS package, when one of our lovely network people completely wiped the drive my SQL Server database was sitting on. I was a bit miffed.
I was even more miffed when I found out after restoring the database that the package I was editing would no longer connect to it. I've removed and recreated all my datasources and connection managers and no luck.
I get a number of errors -
When trying to execute a simple task to take populate a table in my SQL Server database from a remote source I get:
[ODD - PMP10 in ORBIT STAGING [652]] Error: SSIS Error Code
Code SnippetDTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ORBIT STAGING" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
I can however, connect to the database in design mode, using the same connection parameters - I can create tables too, so access rights look ok.
When trying to run a for each container that uses an ADO Connection, I get:
Code SnippetError 1 Error loading Orbit Extract.dtsx: The connection "ORBIT_STAGING - ADO.NET" is not found. This error is thrown by Connections collection when the specific connection element is not found. D:DevORBITExtractOrbit Extract.dtsx
Well, the connection does exist, so it must be a corrupt internal pointer or something...
If I double click on the errors, It takes me to the top of the XML file, and I'm loath to mess with this directly, as I don't know what is what here...
Anyone know of a way to sort this out?
View 3 Replies
View Related
May 16, 2008
Hi,
I did something bad (I don't recall what it was) and I can no longer log into Reporting Services. I wish I could list everything I've tried but I've been at it so long I don't remember. And I've been through the ringer of error messages.
Is there a way I can completely reset and/or restart all the settings from the original install - without doing a new install? I am afraid I am going to mess up SSAS, SSMS, SSIS and something else.
Thank you for the help.
-Gumbatman
View 1 Replies
View Related
Nov 1, 2006
When I try to view my report manager from the computer that SSRS is installed on, I cannot do any administration on it. How can I get this back?
Here's some more info: I browse to http://localhost/reports on the machine and it only shows links for "Home", "My Subscriptions" and "Help". there are no options to add a new folder, set up security on the root folder or any admin functions.
I looked into the HELP and it wasn't any...
View 7 Replies
View Related
Aug 31, 2007
Hello,
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?
View 5 Replies
View Related
Apr 10, 2008
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.
View 6 Replies
View Related
Sep 14, 2015
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]....
View 9 Replies
View Related
Feb 3, 2007
I have this #@!% installed on my machine and when it failed I made the
major error of following some telephone instructions from their
"help-less" support desk - - - well their $#@% products now runs again
(oh whoopie) -but- nothing else related to sql server will work.
This includes:
Visual Developer 2005 Express - Database Explorer
SQL Server Configuration Manager, Surface Area Configuration, etc., etc. (not found)
I tried to re-install SQL Server -but- the install fails, fails, fails...
The failures are always with a corrupt msi file that has just been downloaded / expanded - yet fails???
I have registered and sent error reports -but- I still don't have a functional sql server version running and available.
I do have "their" version of SQL Server running again - this time it is named my-machine-nameGOFIGURE
Like an idiot I deleted folders and un-installed sql server (as per 'their' instructions)
NEVER again - if I ever get things back to "normal"
WHAT should I do - I would 'like' to continue to run their software -but- NOT at the expense of everything else.
If I have to uninstall anything - please list - thanks :)
Thanks,
Paul
View 2 Replies
View Related
May 25, 2008
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)?
View 11 Replies
View Related
Mar 20, 2007
i changed a float data type to a decimal and have lost accurate details, is there a way of rolling back to what it was before
View 1 Replies
View Related
Nov 17, 2006
Hello
I need to import a unicode text file with a DTS. The textfile needs to be imported width fixed column width settings as there are no field delimeters.
The data in the file is messed up (some columns are concatenated) when opened in Notepad.
The data looks fine when opened in Wordpad and also all fields are nicely delimeted but then I end up with unicode characters which are not supported in Wordpad.
Is there anyway around this?
Many thanks in advance!
Best regards,
Worf
View 1 Replies
View Related
Jan 17, 2000
Hello:
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
View 1 Replies
View Related
Mar 4, 2006
I installed the evaluation version of SQL Server 2005 and now my DSL connection on Windows XP is all fouled up; I'm using Server 2003 at the moment. Will uninstalling it get rid of the problem? First Oracle eats my DSL connection, now SQL wants a piece! :eek:
View 4 Replies
View Related
Jun 13, 2008
Recently moved a couple of dbs from SQL 2000 -> SQL 2005 (different machine). Just restored the dbs on the new server from a full backup of the old one. Everything is fine but...
Some queries are returning out-of-order results (for records added since the migration). I tracked this down to the indexes. I completely rebuilt and reorganized the indexes (online) through the manager interface. This helped provide correct ordering for existing records, but new records added since the re index are still out-of-order.
Looking for a way to permanently fix the indexes so I don't have to constantly rebuild them.
Thanks.
View 7 Replies
View Related
Jul 23, 2005
I am using SQL7 Query Analyzer. A simple select * from myMLSview andthen I save results as a .csv file has the commas messed up in quite afew places. The data is messed up before I save it to the .csv file. Sothere are blank spaces being added here and there causing them to readas 'add comma' when saving to .csv format. Even the column data ismessed up... example: ,photo_mod_time,photo_mo,d_date, should read,photo_mod_time,photo_mod_date,I have a view on a remote MS-SQL7 Server, the view has about 200columns and the data returned from the my querys can be from 10 to 25MBin size. Connection is via Roadrunner/cable. Should I use anothermethod, maybe command line? Any suggestions?
View 3 Replies
View Related
Feb 1, 2007
I have (had!) SQL Server 2005 Express and Visual Studio Web Express
2005 (still do) installed but need to write some Reporting Services
reports. I have now bought a Sql Server 2005 Developer license (£40 version)
and have installed it. However I couldn't find any new installation in
my program files so i uninstalled it and the express
version and tried to reinstall the Developer edition but it
kept stopping with all the options greyed out saying there's nothing
to install. So i managed to install as an instance, can't install the 2nd disc though ?? Am i doing something wrong here ? how can i satrt again ??
hopefully
steve
View 3 Replies
View Related
Nov 5, 2004
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
View 12 Replies
View Related
Sep 10, 2007
Hi,
I am using expressions for the textboxes in the Table control Header, because the header names should be displayed in both English as well as in Japanese based on the language selection.The report works fine and all the render formatts except CSV are working fine.when i export this report to CSV, the header names are not coming in the first row of CSV , but some other textbox names (eg textbox 34..) are being displayed on the first row of CSV.From second row onwards, i am getting the header names seperated by comma and the data is being displayed.This header names are being repeated for all the rows in the CSV along with the data.Please give me a solution regarding this.
I tried by setting Data Element as "NO" from "Auto".I could stop the header names being repeated from second row in CSV, but i couldnot get the names in the first row of CSV.
I need to have all the header names as first row in csv and from the second row, i need data.
View 2 Replies
View Related
Aug 1, 2000
Oracle provides for a table to have an alias name.
Does SQL Server 7 support alias names for tables?
Thanks!!
View 2 Replies
View Related
May 21, 2007
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
View 12 Replies
View Related
Mar 26, 2008
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
View 3 Replies
View Related
Jun 29, 2006
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 Related
May 30, 2008
Hi 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.
View 2 Replies
View Related
Feb 16, 2004
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... :)
View 4 Replies
View Related
Sep 11, 2001
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
View 1 Replies
View Related
Apr 17, 2000
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
View 1 Replies
View Related
Jan 29, 2001
Can I do something like the following with SQL Server?
DECLARE @Table_Name varchar(100)
SET @Table_Name = 'Accounts'
SELECT * FROM @Table_Name
View 2 Replies
View Related
