Spaces In SQL 7 Database Names

Mar 7, 2000

Hey all,

Hope I can get some help here.

Have a view that has to reference a table in another database on the same server. This isn't the problem. I am just using Databasename..tablename in the from statement. Get the results required in testing.

The problem is that in production the table name has a space character. i.e. 'accounting info' would be the table name. I have not been able to figure out the proper syntax to capture this properly to reference in the from statement.

I have tried enclosing the name in ' ', " ", [], (), {} and just about every combination I can come up with.

Any help in this would be great, and changing the databse name isn't an option at this time.

Thanx,
Chris

View 3 Replies


ADVERTISEMENT

Spaces In Column Names

Apr 6, 2006

Hi all,

Is it a bad practice to create column names with spaces. like [Last Modified On]?

If yes, y?

View 9 Replies View Related

Truncate Tables With Spaces In Their Names Using STP

Dec 16, 2005

I am attempting to how to truncate list of tables using STP. That is decalre a cursor for a list table names and then to truncate the table names one by one.

The code below shows what I want to achieve. I want to truncate all the tables with names beginning with ZZ but this is failing. I have tried using both delete and truncate.

Is it possible and what do I need to do?

-- Code

SET QUOTED_IDENTIFIER Off
GO
SET ANSI_NULLS ON
GO

create PROCEDURE dbo.Empty_ZZ
AS

DECLARE @tablename sysname
DECLARE @localname varchar(50)

DECLARE ZZtablenames_cursor CURSOR FOR
select [name]
from sysobjects
WHERE [xtype] = 'U'
and name like 'ZZ %'

OPEN ZZtablenames_cursor

FETCH NEXT FROM ZZtablenames_cursor
into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
set @localname = '[' + @tablename + ']'
TRUNCATE + @localname
FETCH NEXT FROM ZZtablenames_cursor
END

CLOSE ZZtablenames_cursor
DEALLOCATE ZZtablenames_cursor

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 4 Replies View Related

Create Login Names Without Spaces

Dec 31, 2004

Afternoon,

I've got this security database that needs to create a login name using first initial and 7 characters of the lastname. No problem.

I wrote this:

select
CASE WHEN LastName like '%.%' then
LEFT(FirstName, 1)+left(replace(LastName,'.',''),7)
else
LEFT(FirstName, 1)+ LEFT(LastName,7)
end as UserID
from Security.dbo.tblUserInformation

The replace and case functions I used to get rid of the '.' Like the name St. Clair now I get "GSt Clai" I need it to also get rid of the space but am stuck. Any thoughts would be appreciated.

Thanks

Laura

View 2 Replies View Related

Dtutil And Spaces In Package Names

Nov 1, 2007

I'm having trouble running a statement like this:


dtutil /DTS MSDB/Budget/BUD_Production - dtsChart of Accounts Budget - Final /En FILE;BUD_Production - dtsChart of Accounts Budget - Final.dtsx;4

I've tried:



dtutil /DTS "MSDB/Budget/BUD_Production - dtsChart of Accounts Budget - Final" /En FILE;"BUD_Production - dtsChart of Accounts Budget - Final.dtsx";4



dtutil /DTS "MSDB/Budget/BUD_Production - dtsChart of Accounts Budget - Final" /En "FILE;BUD_Production - dtsChart of Accounts Budget - Final.dtsx;4"

Any suggestions? I'm no DOS pro, but I can normally get things going.

Thanks
Sam

View 4 Replies View Related

T-SQL (SS2K8) :: Embedded Spaces Ever Valid In Non-delimited Names?

Aug 29, 2014

I am trying to debug a procedure which is timing out, not in every case, but in some cases.

During my perusals, I note that there seems to be a stray space in the 3rd line of following fragment of an UPDATE statement in the procedure:

...
FROM dbo.LoadMedicationsGP LMGP
INNER JOIN dbo.Patient P ON LMGP.PatientId = P.PatientId
INNER JOIN dbo. PatientMonths PM ON P.PatientKey = PM.PatientKey
AND LMGP.DatePeriodKey = PM.DatePeriodKey
...

SSMS seems to parse the statement perfectly OK, but to me it looks as if it ought to be wrong.

I suspect that the space has no material effect, but I just wanted some confirmation one way or the other.

View 4 Replies View Related

How Can I Use The Use Statement With Database-name Contains Spaces In Sql2000

Jan 20, 2006

example :

i cann't write :

use [my db]

what can i do with this problem ?

View 1 Replies View Related

Inserting Text Into SQL 2005 Database Padded With Spaces

Jan 2, 2007

I have a Detailsview with Insert and Update options connected to a SQL 2005 table with templated textboxes for input. The textboxes have maxlength set to the number of characters in each respective field in the SQL 2005 table. When text is inserted it gets padded with spaces if all the field length is not used. When you try to edit the field the cursor does not move because of the padded spaces. The End key must be pressed to find the end of the string and the padded space removed before adding edited text. I am working in VB.net. If I check the field in SQL Studio Express is shows the text I typed plus blank space for the remainder of the field.
My question is how can I add text to the textboxes without the padded spaces being added when the maxlength of the field is not used?

View 3 Replies View Related

Sql Database Names?

Feb 2, 2006

just starting with sql server, thru the sql express edition, before i get to far wondering if it is ok to use the same database names, specifically the ASPNetDB.mdf in different web applications, or will this cause problems down the line,

ie will have different web site applications running on IIS and want to use the aspnet_regsl wizard,

also wondering if this has any implicaitons when publishing web application to web servers, are any of the local sql express server settings passed on to the web host provider, read about machine.config and if i understand i should put all the settings in the web.config file via the VS20005 ide.

also will it cause any problem inside the sql express magement console,

View 1 Replies View Related

How To Get All Table Names From A Database?

Apr 13, 2007

I have two questions:



1. Which system table (data dictionary) I can select to get all table names in one database?

2. What sql command can I use to get a definition about a table?



Thanks

View 9 Replies View Related

How To Get All Database Names Using A Sql Statment?

May 7, 2007

Hi,



Which view I can use to get all database names in a sql server 2005 instance?



Thanks

View 6 Replies View Related

Dynamically Pass Table Names And File Names To IS Package

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

Integration Services :: Chinese Names Export To CSV - Garbled Names

Aug 11, 2015

As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).

I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.

Here is the connection manager setup

There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?

View 4 Replies View Related

Getting Table Names From AdventureWorks Database

Apr 24, 2008

Hello,
I have ran into a problem when i'm getting table names in c#.
I have tried the sql command:
select name from sysobjects where type='U' order by name
So it returns the names without a problem. But when I try to access to some tables using their names (to retrieve the columns) I sometimes get the error "Invalid object name" following by the name of the table.
So I realized the if the table was not under dbo user then i would get this error.
for example: "select name from ContactCreditCard"
 gives me error because in database management tool it is shown like this: Sales.ContactCreditCard
How can i fix this problem? How to get that prefix of the tables?
 
Thank you,
 

View 8 Replies View Related

Returning The Database Column Names

Dec 24, 2003

Hello, { Merry xmas to all few hours early but hey !! }

I'm trynig to create an application that will connect to any database that i specify and return the colums names within any table that i specify.

I have done the code that establishes the connection but im a bit stumped on how to get the colum names from a tabel and was wondering if anybody had any sourse code for this in VB.

Thanks

Rob

ps: have a good new year !!

View 3 Replies View Related

Purging Deleted Database Names

Jul 1, 2002

When connecting to an SQL Server (v7 in this case) the log file shows that old and deleted databases are still being opened as part of the process. Further, these db names are now reserved and cannot be reused, even thought I've deleted them.

Is there any way I can purge all traces of these deleted db names?

You might have guessed, I'm a newbie, which is why there are a few dozen deleted DBs.

Thanks for any help
Max
atomax@gmx.net

View 2 Replies View Related

Returning Database Object Names

Jul 7, 2004

hey everyone, need some help

I have a query return all the names of the table in my database via this code:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],4))<>"MSYS"));

I need to further refine the search fitting in the critera LIKE 'qry_tbl%' I dont know how to fit this in .. i tried fitting it in to the end, but nothing comes up.. I also tried querying the results of this query with another query :-P but that didnt work either .. any ideas?

In essence i need all the table names in my DB that start with 'qry_tbl' to be in a list for me..

Thx
Doc


This gives me all the names

View 3 Replies View Related

User Can View All Database Names

Apr 17, 2008

I create a user then I create a database and assign my newly created user as owner

When I login as this new user everything works fine BUT my newly created user can still see other database names even though he is not allowed to use them.

How can I prevent him from seeing other database names.

P.S. I use the GUI

Thank you

View 2 Replies View Related

Database Tables And User Names

Jan 26, 2007

We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do.

For example, to query a table called Page we would nee to do this on the new box.

SELECT *
FROM webdev.page

unfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix?

Thank you for your help...sorry for the newbie type question.

View 3 Replies View Related

How To Get The Database Names From The Sql Server 2005

Apr 9, 2008



ho w do i get all the database names from my local machine which are present in the sql sevrer 2005
i also need how do i get the names of all the tables,views,stored procedure names
in the particular database

View 3 Replies View Related

Restore Database To Different File Names

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

Determine Table Names And Column Names At Runtime?

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

Where Paramerter Names(data) Will Be Stored In Database MS SQL

Jan 8, 2008

Hi,
 In Ms SQL server if we create a user defined stored procedure it will be stored in the SysObjects table.
 simillarly the paramerter are stored in other table which is that table.

View 4 Replies View Related

Error With Names Like Georgia's Way While Inserting Into A Database

May 14, 2008

Hello All,i am trying to insert some values into a table in sql database. i keep getting error saying incorrect syntax near 'S'. i fired up my debugger and found that one of the row contains name like Georgia's way. i am getting error at the "S" in georgia's way. how can i fix that. here is my code for inserting the values in to the table SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr1 in objDataSet.Tables[0].Rows) { String sqlinsert = String.Format("insert into Det values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},{8},'{9}','{10}','{11}','{12}')"
, dr1[0].ToString()
, dr1[1].ToString()
, dr1[2].ToString()
, dr1[3].ToString() -- this is the column where georgia's way is
, dr1[4].ToString()
, dr1[5].ToString()
, dr1[6].ToString()
, Convert.ToDecimal(dr1[7].ToString())
, Convert.ToDecimal(dr1[8].ToString())
, dr1[9].ToString()
, dr1[10].ToString()
, dr1[11].ToString()
, dr1[12].ToString());

new SqlCommand(sqlinsert, mysqlcon).ExecuteNonQuery(); LabelImport.Text = " Row Inserted"; } mysqlcon.Close();  can some please help me out.Thanks a lot 

View 16 Replies View Related

How Can I Find All Table Names Defined In A Database?

Mar 10, 2001

I have a database and I want to retrieve informations about all tables
defined in this database. In other words I need the table list defined in
database. Can you help me?

Thank you.

View 1 Replies View Related

Query To Understand The Names Of All Available Tables In Database

Aug 31, 2014

SQL query to understand the names of all the available tables in the database , number of records in these tables and size of these tables ?

View 1 Replies View Related

Getting English Column Names Into A German Database

Jul 23, 2005

I'm currently implementing a German ERP system and I'm having aterrible time writing reports/SQL statements. The database is MS SQLServer 2000. I want to set permanant aliases for the columns that Ican use in SQL statements. Is this even possible? All of the researchI've done implies that you can only alias column names in the output ofa SQL statement.

View 2 Replies View Related

Scheduled Report For Database Names And Sizes

Jul 20, 2005

Does anyone has a script which gives all databases names and thereallocated, used sizes in SQLserver2k. I want to schedule this to create adaily report.Thanks,Nasir

View 1 Replies View Related

How To Get The List Of Table Names In A SQL2005 Database

Aug 31, 2005

How to get the list of table names in a SQL2005 database?

View 6 Replies View Related

List Table Names In A Database Having A Particular Column.

Aug 3, 2007

Can anybody tell me how to find out whether a particular column exists in any of the tables of a database and if it does, display the table names?


Thanks

View 3 Replies View Related

SQL 2005 Database Design (Table Names)

Apr 14, 2006

In the past, we had table names such as:
PurchaseOrders
SalesOrders

Now with SQL 2005, you can use schema namespaces, so it could become:
Purchasing.Orders
Sales.Orders

So essentially you can use exactly the SAME table names, in different schemas.

So... would you recommend to change to the last model, or just stay with the old way?
What is best practice?



One of the reasons why I ask this question is that I looked at the AdventureWorks sample database included with SQL Server 2005, and Microsoft is using:

Sales.SalesOrderDetail
Purchasing.PurchaseOrderDetail
etc...

Why not:
Sales.OrderDetail
Purchasing.OrderDetail
etc...

????????

View 1 Replies View Related

Compare Column Names From Two Tables In Same Database

May 17, 2008

I'm not a full-time DBA, so excuse my style of expressing my question.I have a database which has 2 tables in SQL 2005. Both these tables have similar column names, EXCEPT for new extra columns in FY2007_DATA. I can visually see the difference in columns in Database Diagrams. My goal is to :- I want to compare FY2007 tbl column names to FY2006 column names and display only those columns as results that do not match.Tbl 1 :- FY2006_DATA Tbl 2:-



FY2007_DATA

With online reading and help I have managed to get this script to do exactly opposite of what i want. Below is the query

/* This query compares the column names from two tables and displays the ones that have an exact match. It does not care for case-sensitiveness */

Select a.Table_Name, a.Column_Name, (b.Table_Name), (b.column_name)
From [2006-2011].INFORMATION_SCHEMA.Columns AS a
Join [2006-2011].INFORMATION_SCHEMA.Columns AS b on a.Column_Name = b.Column_Name
Where a.TABLE_NAME = 'FY2006_DATA'
And b.TABLE_NAME = 'FY2007_DATA'
AND a.Column_Name IN
(Select Column_Name = LEFT(c.column_name, 20) FROM
[H1B_2006-2011].INFORMATION_SCHEMA.Columns AS c WHERE c.TABLE_NAME = 'FY2007_DATA' )


When I change "AND a.Column_Name IN.." to "AND a.Column_Name NOT IN.." so that the results will (should) display the extra columns in FY2007, in fact I do not see any results, but query executes perfect.

How can I achieve my goal.?

Thank you

View 1 Replies View Related

Create List Of Table Names And Size For A Database

May 5, 2004

Hi there,

I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB

E.g

Table1 1111KB
Table2 123300MB
Table3 120448KB

etc for all the tables in a particukar DB

I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.

can anyone help please!!

From

NewToSQL

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved