DTS 2005 And Quoted Table Names
Jul 23, 2005
We're trying to use DTS from SQL Server 2005 beta 2 to query data
from an Informix IDS server via OLEDB.
Unfortunately DTS is building queries of the form:
select * from "database":"owner"."tabname"
and the quoted table name is being rejected by the Informix server as a
syntax error.
Is there a way to keep DTS from quoting the table name in queries? Or is
this happening within the OLEDB provider?
Thanks.
--
John Hardin
Development and Technology group (Seattle)
CRS Retail Systems, Inc.
View 1 Replies
ADVERTISEMENT
May 27, 2015
I have existing table which is having Set Quoted Identifier Off and Set Ansi Null
Now I want to change those setting so Is there any alter statement for the same?
Also Let's say At my database level If those settings are off and If I convert it to ON then It is not taking effect on existing tables SP which are already build.
View 4 Replies
View Related
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
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
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
May 14, 2003
During Index Rebuild, I get the following error:
"DBCC failed because the following set options have incorrect settings: 'Quoted_Identifier'"
Any ideas what might be causing this?
Thanks,
Ken Nicholson
View 8 Replies
View Related
Sep 26, 2006
There have been some posts on the difficulties of handling csv with quoted identifers.
I heard there were some custom components that aid in dealing with these as input files.
IYO, what do you consider to be the best way to deal with csv with quoted identifiers as an input to an ETL operation?
Also, if you know of any bloggers making quality (hopefully free) custom components?
Thanks!!
View 1 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
Oct 2, 2006
We have several sp's created to support a 3rd pary app that uses crystal inside the app with an odbc connection. We got sql row errors on any Citrix server where Use ANSI quoted identifiers was not checked. If we had them checked it works. Does this mean the sp's were created with the identifiers on and this is causing the issue?
View 1 Replies
View Related
Nov 2, 2005
I'm dumping data from a table via BCP and when BCPing them back in to another table, it errors out on numeric and date fields. I'd like to place quote marks on the text fields. How do I do this using BCP?
View 4 Replies
View Related
Aug 8, 2005
Hello everyone,SQL Server 2000. I have a database in which there are several objectswhich have ansi nuls and quoted identifier turned ON. Is there a way Ican generate a script which:(1) Can identify all objects within the database that have those twoproperties turned ON and(2) Change the properties for these objects and turn the ansi nulls andquoted identifier OFF for those objects.I am trying to avoid going throuh gazillions of objects and manuallydoing this.Thanks for any help.Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View 17 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
Jan 16, 2003
Hi,
If you run the following code on 7.0 server, it will send a test file in csv format.
--*********************************
create table ##temp1
(tmp varchar(15))
insert into ##temp1
select 'Erin, Brockowich'
declare @cmd varchar(255)
select @cmd = 'select * from ##temp1'
EXEC master..xp_sendmail
@recipients = '<email>', --change to your email
@query = @cmd,
@subject = 'test-test',
@attach_results = 'true',
@attachments = 'test.csv',
@width = 800
drop table ##temp1
--**********************************
When you receive an email and double click on te attachment, it will launch excel automatically and put 'Erin' and 'Brockowich' in seperate columns. which is good and that is the way I want it.
But if I run this code on sql 2000 server, it will generate a file with 'csv' extension unrecognizable by excel. If you open this 2000 attachment in notepad, you can see that the data looks like
"Erin, Brockowich" (vs Erin, Brockowich without quotes on 7.0 server ), no wonder it is unrecognizable by excel.
I have set quoted_identifier off while compiling all the user sps on 2000 server. But while sending emails from within the procedure, all the attachments still generated with quotes.
How can I get rid of the quotes? We have at least 45 routines running generating coma seperated files as a result of the query and sending emails to the clients for years. Now all of a sudden all the routines got messed up with 2000 upgrade.
Can you suggest any work around?
Thanks,
Shaili
:confused:
View 1 Replies
View Related
Jul 27, 2006
Hello,
I am trying to insert quoted strings into a database table, however, I cannot remember how to do so. For instance, I am trying to insert the following values into a SQL table:
My Friend's
"Happy Birthday"
exactly as they are listed. How can I do that in a SQL insert statement?
Thanks,
Crystal
View 1 Replies
View Related
Aug 16, 2007
I have a stored procedure which returns a count of products and a limited number of rows from a query.
I am using SQL Server 2005 and calling the procedure in asp.net
The procedure is as follows
Code Snippet
GO
ALTER PROCEDURE [dbo].[GetProductsByCategoryId]
@Category VARCHAR(255),
@Range INT,
@PageIndex INT,
@NumRows INT,
@CategoryName nvarchar(255) OUTPUT,
@CategoryProductCount INT OUTPUT
AS
BEGIN
/*
Get product count
*/
SELECT @CategoryProductCount=(SELECT COUNT(*) FROM Products LEFT JOIN tblVar on Products.ProductID = tblVar.prodidvar WHERE Products.Category=@Category AND Products.Range=@Range)
/*set row variables*/
Declare @startRowIndex INT;
set @startRowIndex =(@PageIndex * @NumRows) + 1;
/* get full list of products */
With ProductEntries as (
SELECT ROW_NUMBER() OVER (ORDER BY Products.ProductID, tblVar.idvar ASC) as Row, field1, field2
FROM Products LEFT JOIN tblVar on Products.ProductID=tblVar.prodidvar
WHERE Range=@Range
AND Category = @Category
)
/*get only needed rows */
SELECT field1, field2
FROM ProductEntries
WHERE Row Between
@startRowIndex and @startRowIndex+@NumRows-1
END
The problem seems to be with the line
AND Category = @ Category
in the query to make the ProductEntries
If I take this query and run it in an SQL pane I need to enclose the argument for @Category in single quotes.
If I try to do this in the procedure it simply searchs for @Category as a string rather than the value of @Category.
The query returns and displays results with no problems without this line, and also if it is returning a result set that has no values in tblVar to join to.
Also if I run the query on just the Products table removing the left join it will return results with no problems.
Thanks to anyone who can help!
And I apologise if it is something simple but asp and SQL Server is not my usual coding platform.
View 7 Replies
View Related
Sep 9, 2007
Hi,
My replication is failing to apply the initial snapshot because of the issue with QUOTED identifier. The snapshot files for the stored procedure are being generated using the 'SET QUOTED IDENTIFIER ON' where as my store procedure code is using the double quotes for string comparision e.g if @val = "test".
It is not possible for me to change the sps code as there are 1000+ sps exists. Is there any way to generate the snapshot files with 'SET QUOTED IDENTIFIER OFF'
View 1 Replies
View Related
Nov 8, 2006
I am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field.
So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?
View 1 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
Oct 26, 2007
I inherited a system that was started in Access and moved to SQL 2000. The business has grown and we are trying to replace our older systems with ASP.NET and Server 2005. Currently, we are trying to make a new asp.net page for searching the database for records with matching dates or date ranges. There are several types of dates to search, so they are all optional. Set to default as null in the proc. For each date there is an operator field, such as equal or greater, etc. The proc only looks at the date if the operator is set to EQ" or "IN" and ignores the date if operator set to "NO"
The proc works fine when running under Management Studio, but fails coming through a SQLDataSource to a gridview. It works with integer and string filters, but fails when entering the same date ('07/20/2007') that works in the testing tool. All dates are actually stored as datetime, and they are set as DateTime Control Parameters in the SQLDataSource.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:someConnectionString %>"
SelectCommand="spTESTSearch" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="M" Name="TypeCode" Type="String" />
<asp:Parameter DefaultValue="EQ" Name="FirstPubOp" Type="String" />
<asp:ControlParameter ControlID="FirstPubDateTextBox" DefaultValue="" Name="FirstPubDate"
PropertyName="Text" Type="DateTime" />
<asp:Parameter DefaultValue="C" Name="UserName" Type="String" />
<asp:Parameter DefaultValue="NO" Name="SearchTextOp" Type="String" />
<asp:Parameter Name="SearchText" Type="String" />
</SelectParameters></asp:SqlDataSource>
The dates are selected properly in the testing tool, with code such as :
DateDiff(day, FirstPubDate, @FirstPubDate) = 0
I think my problem is based on option settings for the databases themselves. The old database was set to Ansii Nulls and Quoted Identiers to OFF, and the new ones were defaulted to them being ON. I noticed that the tool also, sets those options on when creating new stored procedures.
Would this difference be causing the dates to be quoted and viewed as objects rather than strings? What are the dangers in changing those options on the database that still gets uploads from the old SQL 2000 database and some Mac-based systems?
I welcome any suggestions on how to get my new stuff running while not breaking my old production systems.
Thanks for the assist!
View 1 Replies
View Related
Jan 18, 2008
Hello all,
From what I've read, SQL Server is supposed to do a phrase match when you do a full text search that contains quoted literals. So, for example, if I did a full text search on the phrase "time out" and I put it in quotes, it's supposed to search for the full phrase "time out" and not just look for rows that contain the words "time" or "out." However, this isn't working for me.
Here is the query that I'm using :
SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC
What's it's doing is this : it's returning a bunch of rows that have the words "time" or "out" in the column called hed. It's also returning rows that have the full phrase "time out", but it's giving those rows the same rank as rows that only contain the word "time." In this case, that rank is 180.
Is there anything else I should be doing in my query, or is there some configuration option I should have turned on?
Thanks.
View 1 Replies
View Related
Nov 18, 2014
I need to extract specific text elements from a varchar column. There are three keywords in any given string: "wfTask," "wfStatus" and "displayReportFromWorkflow." "wfTask" and "wfStatus" can appear multiple times, but always as a pair and will each be followed by by "==" (with or without surrounding spaces). "displayReportFromWorkflow" is always followed by "(" and there can be spaces on either side. The text elements will be between a pair of double quotes, and following one of keywords. For each row, I need to return the task, status and report name.
declare @t table (rowID int, textValue varchar(1024))
insert @t
(rowID, textValue)
values
[Code] ....
Output:
rowID, Task, Status, ReportName
----- --------- ------- ------------------------
1, Issuance, Issued, General Permit
2, Issuance, Issued, Capacity Letter Type III
2, Review, Denied, Capacity Letter Type III
I started with a string splitter using the double quote character, referencing elements "i" and "i+1" where the text like '%wfTask%' or '%wfStatus%' or '%displayReportFromWorkflow%', but the case of multiple task/status in a row has confounded me so far.
Unfortunately, CLR is not an option.
View 1 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
