ANSI PADDING (pt.2)
Jun 13, 2000
ok people, this is getting seriously frustrating! Please help!
As mentioned in a previous post, one of my batch jobs is printing fields with padding added, even when
the table column is defined as varchar.
I've been to the knowledge base, read the article on ansi padding, ran the test scripts.
But when I ran the select to display the columns,
THE OUTPUT FOR BOTH TABLES WAS IDENTICAL!!!
Apparently the SET ANSI_PADDING ON/OFF option had no effect!
What am i doing wrong? What is missing? Do i have to run the Set Ansi Padding option in the Master DB
context? Have I unknowingly overridden the option somewhere else? Must I brush up on my COBOL
for a career change?
HELP!
fjw
View 2 Replies
ADVERTISEMENT
Apr 3, 2006
Hi,
This might sound obvious, or a newbie question, but how are trailing blanks treated by SQL2005 on varchar columns?
I have a column where two rows only differ by a trailing blank. If write a select and a where clause on the column, anly trailing blanks seem to be trimmed. I tried the ansi padding setting but it doesn't change anything. Is it a question of collation? I have default collation on the server set to SQL_Latin1_General_CP1_CI_AS...
The problem also seems to arise when I try to create a unique index on the column, where both values are considered equivalent...
I give here a sample based on the BOL for set ansi_padding. I was expecting each of the select statements below to retrun only one row...
Cany somebody please explain why they all return two rows?
PRINT 'Testing with ANSI_PADDING ON'
SET ANSI_PADDING ON;
GO
CREATE TABLE t1 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank ';
GO
PRINT 'Testing with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
GO
CREATE TABLE t2 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank ';
GO
DROP TABLE t1
DROP TABLE t2
View 1 Replies
View Related
Aug 3, 2012
I have a question relating to the ANSI_PADDING setting on some existing tables in a SQL Server 2008 R2 database I am working with. When I generated the tables originally I basically programmatically created them by building CREATE scripts within my code. Since I did not explicitly set ANSI_PADDING to ON all these tables they seem to have been created with ANSI_PADDING as OFF. Some of these tables, which I now need to add columns to, contain varchar(n) and varbinary(n) columns.
When I try to alter the tables through Management Studio, SQL Server gives me a warning: "One or more tables have ANSI_PADDING 'off' and will be recreated with ANSI_PADDING 'on'" - this seems to be generated by the ALTER statement which by default sets ANSI_PADDING to ON. Another iteration of the same warning - "Columns have different ANSI_PADDING settings. New columns will be created with ANSI_PADDING 'on'".
From what I read regarding ANSI_PADDING it seems ON is definitely the way to go. I just need to know if changing the value may result in any of the existing data in the table to be changed or may have any other unintended side effect, as this may cause problems for me.
View 8 Replies
View Related
Jul 9, 2015
I am attempting to create a snapshot replication publication.
When the snapshot is generated, any table that consists of all numeric columns creates a script with SET ANSI PADDING OFF
I googled around a bit and found that if a DDL trigger is in place on the database, it will cause this to happen.
I dropped the triggers, re-created the publication and subscription, same thing.
View 5 Replies
View Related
Oct 25, 2007
A question for everyone:
With the introduction of SQL 2005, we now have to use ANSI-92 T-SQL Syntax and I was wondering if anyone had written a tool to convert queries from old ANSI SQL to the new syntax.
We have some code that has to change for the outer joins, but we also have a lot of code that should change for the inner joins. It doesn't seem that difficult to write something that parses an old piece of code and at least suggests a new version. Especially if the conversion code wasn't SQL code.
Thanks, in advance,
Brian
View 7 Replies
View Related
Oct 1, 2007
I've been using this syntax for years on SQL Server and now comes the time to convert to SQL 2005 (90 compatibility). This syntax returns four rows. Basically it returns one row for each servername/component/context/property/value even when there does not exist a property of 'fff' since it's a left join:
Code Block
select t1.* from tblconfiguration t1
,tblconfiguration t2
where t1.component = 'AdjProcessUtility'
and t1.servername *= t2.servername
and t1.component *= t2.component
and t1.context *= t2.context
and t1.property = 'proc'
and t2.property = 'fff'
Result:
SQLEDEV1 AdjProcessUtility DuplicatesReport Proc Adjustment.dbo.prcDuplicatesReport
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport Proc Adjustment.dbo.prcValidationReport
When the converted (using SQL enterprise Mgr) runs it returns no rows:
Code Block
SELECT t1.*
FROM dbo.tblConfiguration t1 LEFT OUTER JOIN
dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context
WHERE (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff')
I don't really see how to change this query to make it work. I've searched the web and I really don't see any examples of left joins which use more than one column.
Here's the table definition:
Code Block
CREATE TABLE dbo.tblConfiguration
(
ServerName VARCHAR(30) NOT NULL,
Component VARCHAR(255) NOT NULL,
Context VARCHAR(255) NOT NULL,
Property VARCHAR(255) NOT NULL,
CONSTRAINT PK_tblConfiguration PRIMARY KEY NONCLUSTERED( ServerName, Component, Context, Property ),
Value VARCHAR(255) NOT NULL
)
I use this table to define reports and there attribues. The rows repeat themselves except for the Property and Value columns
Here is some of the data:
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ExtractAdjFile RunTime 13:25
SQLEDEV1 AdjProcessUtility ExtractAdjFile Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ExtractAdjFile FixedRecLength 71
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteFileHeader Y
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteTempTable Y
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport ReportClass ReportCSV
SQLEDEV1 AdjProcessUtility ValidationReport Ids Validation
SQLEDEV1 AdjProcessUtility ValidationReport RunTime 15:06
SQLEDEV1 AdjProcessUtility ValidationReport Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ValidationReport DefaultFileName Adj_ValidationReport_MMDDYYHHMM.csv
etc.
Any help is greatly appreciated,
Sid
View 16 Replies
View Related
Oct 5, 2006
Hi everyone.. can anyone help me on how to solve my problem regarding on Select.. im using PB6.5 and running on MSSLQ2005 database.. i attached an image for your reference.. thnks!
View 5 Replies
View Related
Jul 31, 2000
Hi,
I'm using SQL Server 7.0. I am importing data from an EXCEL spreadsheet. I need to pad my agent_id column from the spreadsheet with zero's. Here are the values from the spreadsheet:
agent_id
--------
123
4567
112233
9
This is what I need to see in my database column:
agent_id (char(6))
--------
000123
004567
112233
000009
Is there a setting on the column in SQL Server 7.0 that I can set to pad the column with zero's?
Thanks in advance,
Darrin
View 1 Replies
View Related
Feb 7, 2008
I have a search box to look up using a number as an identifier. Currently my query is where x = @enteredNumber, but some place where a user might get the number it is padded with zeros. We can have them just see the number and retype it.
EG: 000000123 would be entered as 123.
But, I would like to let them copy and paste, but then strip the zeros (left padding only).
EG:
@num = stripPadding(@enteredNumber)
IE:
@num = stripPadding(000000123)
@num = 123
View 2 Replies
View Related
Jun 12, 2000
upgraded to 7.0 and all was fine until...
we run a job that pull selected fields from a table and writes them to a dos text file.
(tables get loaded via bcp)
Pre-7.0, all varchar data was correctly output without any padding.
NOW, all varchar fields are padded with spaces on output.
tried toggling the Set ansi_padding switch before creating table def, didn't work.
HELP! (please?)
fw
View 1 Replies
View Related
Apr 20, 2007
Hi All,
Is there equivalent of LPAD in SQL 2000 similar to that in Oracle?
I am trying to work out for below piece of query
TO_CAHR(LPAD(columnname,20,'0'))
vishu
Bangalore
View 6 Replies
View Related
Jun 28, 2006
Hi everyone,Please excuse me if this has been asked before or sounds a bit dim.This is a question asked on another forum but the solutions beingoffered are focussing on programming rather than letting the DB serverdo the work, which I'm not sure is the most efficient solution.However, my confession is I dont use SQL server so can't help themdirectly with the syntax. Hopefully you can help me help them and learna little about SQL Server in the process.Trying to right pad a first name field so the padded string is a totalof 30 chars. It will be output concatenated with the last name field,and each field separated with a "|". So that when output it readssomething like:fname | mylastnameSyntax given was:select id,substring((last_name+','+rtrim(' '+isnull(level,'))+''+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as student_namefrom studentIssue: It appears this is padding correctly but the spaces are notrendering in the browser. (I have no way to check this as I don't usesqlserver. However, I can understand that multiple spaces are not goingto render in the client browser, if indeed the query is padding withspaces.Question: Instead of using space(), can replicate() be used and aunicode space representation rather than an actual space be used? Or,is there a better way that will ensurethe padding shows in browser?I guess a fixed width font would also need to be used otherwise the30-char blocks could wind up being different widths, which would defeatthe purpose.If there is something I've missed, or you have any suggestions, I'mkeen to learn.TYhanks in advance,Lossed
View 13 Replies
View Related
Apr 25, 2007
I need a table similar to Fig-1 on my report. The subtotal and total lines need top padding and solid border at top and bottom. I€™m currently using bottom padding of 20pt on the sub total row and setting the border style property of the sub total and total rows to solid at top and bottom. But the problem is that the sub total rows are being padded first and then the border at the bottom is being set to solid. As a result, my table looks something similar to figure 2, which is not acceptable to my customer. I also tried by setting the top padding of the lines below subtotals, but it doesn't give me what I need. Any way I can set the border before padding? Appreciate your help.
Figure-1
A
10
B
20
C
30
Sub Total
60
D
20
E
10
F
50
Sub Total
80
TOTAL
140
Figure-2
A
10
B
20
C
30
Sub Total
60
D
20
E
10
F
50
Sub Total
80
TOTAL
140
View 5 Replies
View Related
Jan 18, 2008
Hi,
I need a simpler way of doing this:
I've a number column which I need to show as a 3 digit field in a string:
Example:
number =1; string = 010
number =10; string =100
is there any function that can do this string manipulation?
Currently I'm using a CASE statement to check the length of this number column,
if its single digit I use:
SUBSTRING(('0' + CONVERT(VARCHAR,CONVERT(INT,ISNULL(D.Number,E.Number)))+'0'),1,3)
else I use
CONVERT(VARCHAR,CONVERT(INT,ISNULL(D.Number,E.Number)))+'0')
Is there a better way of doing this?
Thanks,
Subha
View 5 Replies
View Related
Jan 22, 2004
I've created an asp.net page that takes the content of text boxes and writes them to a sql table.
The problem is that when I examine the resulting data in the SQL database, I find that the fields written to have had padding added (up to the maximum size of the fields).
I was under the impression that fields of type NVARCHAR did not store padding (only the no of characters being stored).
I've checked it's not the text boxes on the aspx page by explicitly posting values instead of the boxes content and the same thing happens.
Help
example of function i'm using to post data:
Function AddNews(ByVal Category As String, ByVal ApplicRole As String, ByVal NewsType As String, ByVal Description As String, ByVal News As String, ByVal Hyperlink As String, ByVal Email As String, ByVal BirthDate As Date, ByVal KillDate As Date, ByVal Parent As String) As Integer
Dim connectionString As String = ConfigurationSettings.AppSettings("AuthentConnection")
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [News]([Category],[ApplicRole],[NewsType],[Description],[News],[Hyperlink],[Email],[BirthDate],[KillDate],[Parent]) VALUES (@Category,@ApplicRole,@NewsType,@Description,@News,@Hyperlink,@Email,@BirthDate,@KillDate,@Parent)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_Category As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Category.ParameterName = "@Category"
dbParam_Category.Value = Category
dbParam_Category.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Category)
Dim dbParam_ApplicRole As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_ApplicRole.ParameterName = "@ApplicRole"
dbParam_ApplicRole.Value = ApplicRole
dbParam_ApplicRole.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_ApplicRole)
Dim dbParam_NewsType As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_NewsType.ParameterName = "@NewsType"
dbParam_NewsType.Value = NewsType
dbParam_NewsType.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_NewsType)
Dim dbParam_Description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Description.ParameterName = "@Description"
dbParam_Description.Value = Description
dbParam_Description.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Description)
Dim dbParam_News As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_News.ParameterName = "@News"
dbParam_News.Value = News
dbParam_News.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_News)
Dim dbParam_Hyperlink As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Hyperlink.ParameterName = "@Hyperlink"
dbParam_Hyperlink.Value = Hyperlink
dbParam_Hyperlink.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Hyperlink)
Dim dbParam_Email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Email.ParameterName = "@Email"
dbParam_Email.Value = Email
dbParam_Email.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Email)
Dim dbParam_BirthDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_BirthDate.ParameterName = "@BirthDate"
dbParam_BirthDate.Value = BirthDate
dbParam_BirthDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_BirthDate)
Dim dbParam_KillDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_KillDate.ParameterName = "@KillDate"
dbParam_KillDate.Value = KillDate
dbParam_KillDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_KillDate)
Dim dbParam_Parent As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Parent.ParameterName = "@Parent"
dbParam_Parent.Value = Parent
dbParam_Parent.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Parent)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
View 4 Replies
View Related
Dec 6, 2001
Hi all-
I'm inserting 4-digit codes into my db table where the column is a smallint type. Some of these codes begin with 0's, like "0003". How can I format the val in this column to always have the extra 0's in front?
Thanks!
View 3 Replies
View Related
Oct 24, 2001
Is there a function in transact sql to pad a column with leading zeros?
Similar to the LPAD function in Oracle.
View 1 Replies
View Related
Mar 7, 2008
HI Guys, I have a question. I have to output 2 implied decimal amount fields (AJ_Dollar_Amount & AJ_Amount). I have defined they as decimal (13, 0) and decimal (17, 0) respectively. I was told to use the following func to pad the field, but it is not working. How should I define field(s) or rewrite the Cast statement to get it to work?
Right('0000000000000' + Cast(AJ_Dollar_Amount as varchar (13)), 13),
Thank you,
View 11 Replies
View Related
Jul 20, 2005
Hi,I've got a field that stores numeric values, representing a tracking number.I've also got a stored procedure that will extract this field and return itto a client. However, I would like to return it slightly differently to theway in which it is stored. Basically, I want to return it as TRK000nnn -Where TRK is the string "TRK", 000 is zero-padding up to 4 zeros, and nnn isthe number itself - results would look something like this:Tracking Number Formatted Value1 TRK0000124 TRK0002443321 TRK43321At the moment, a typical query could look something like this.SELECT ("TRK" + CAST(trackNo AS varchar(10)))FROM TB_QueueBut I'm not sure how to go about the zero padding. This would be easiest todo on the client, but it is impractical (there are many client programs thatwill use this stored procedure, and the format that it is returned as mayneed to be altered in the future).Many thanks,Rowland.
View 6 Replies
View Related
Jan 12, 2006
When using the MS OLE DB Provider for DB2 for retrieving data from a DB2 database on an AS/400, all string are padded with white spaces in the end. For instance, if you have a text field with a length of 10 and the field contains the text 'xyz', then when you transfer the data using SSIS you will get the text 'xyz ' ('xyz' followed by 7 times white space). Is there any way to get around this - other than using the RTRIM function in the source query?
View 4 Replies
View Related
Oct 4, 2006
I am trying to create a field in SQL query builder that takes the input number 12345 and formats it 0000012345. In MSAccess there is a great Format() function which allowed this to be done easily, but I cant find an equivalent in SQLServer. Can anyone provide me with a new function and possibly a sample? Thanks in Advance,
Michael.
View 6 Replies
View Related
May 2, 2006
Anyone notice that the padding property doesn't seem to work as nice as RS 2000? When I select more than one cell or row, and try to set the padding to 1,1,1,1, by typing the values in the property field (as opposed to expanding it and entering it in each side), it sometimes it gives me the following error:
'RptPaddingConverter' is unable to convert 'Microsoft.ReportDesigner.Drawing.ReportPadding' to 'System.ComponentModel.Design.Serialization.InstanceDescriptor'.
Other times it works. This has always worked in RS 2000. It is more of a time-consuming annoyance than anything because then I have to go into each cell individually and change the padding, one by one.
View 6 Replies
View Related
Feb 8, 1999
1) What is the restriction on the max no. of tables that can be used in any one query in v7.0? In 6.5 it was a max of 16 :(
2) Are non-ANSI-style joins permitted in v7? Just that we're thinking of upgrading but 50% of our stored procs/views have the old *= syntax
Thanks,
Brad Carr
View 1 Replies
View Related
Aug 7, 2001
My question here is whether or not MS SQL is processing joins according to the ANSI SQL standard. The situation is this, a friend of mine has the following query (The definition for the tables and the data in them are at the end of the message):
SELECT t.*, v.*
FROM Table3 T LEFT OUTER JOIN
TableView V ON T.tx = V.tx
WHERE V.tx IS NULL and V.part IN (1,2)
My friend is implying that the OUTER join takes precedence over the WHERE clause and should return ALL rows from table3 that don't exist in the view. The view, as you will see below, is a partitioned view. My contention is that the proper join processing is to join the tables first and then apply the WHERE clause to that result set (regardless of the join type). In my friend's case, he expects this query to return four rows. My expectation is that it will return zero rows, because in the result set after the join is performed there are no rows that have a NULL in the v.tx column AND 1 or 2 in the v.part column. The actual ouput agrees with my premise and my friend is sure that this is not proper ANSI SQL join processing. Is this proper ANSI join processing or is MS SQL deviating from the standard?
/************************************************** **********************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO
CREATE TABLE [dbo].[Table1] (
[Part] [tinyint] NOT NULL ,
[id] [int] NOT NULL ,
[tx] [INT] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Part],
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD
CONSTRAINT [CK_Table1] CHECK ([Part] = 1)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO
CREATE TABLE [dbo].[Table2] (
[Part] [tinyint] NOT NULL ,
[id] [int] NOT NULL ,
[tx] [INT] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Part],
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD
CONSTRAINT [CK_Table2] CHECK ([Part] = 2)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table3]
GO
CREATE TABLE [dbo].[Table3] (
[tx] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table3] WITH CHECK ADD
CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED
(
[tx]
) ON [PRIMARY]
GO
--DROP VIEW TableView
CREATE VIEW TableView AS
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
GO
INSERT TableView (Part,Id,Tx) VALUES (1,1,1)
INSERT TableView (Part,Id,Tx) VALUES (1,2,2)
INSERT TableView (Part,Id,Tx) VALUES (1,3,NULL)
INSERT TableView (Part,Id,Tx) VALUES (2,4,4)
INSERT TableView (Part,Id,Tx) VALUES (2,5,5)
INSERT TableView (Part,Id,Tx) VALUES (2,6,NULL)
INSERT Table3 (tx) VALUES (1)
INSERT Table3 (tx) VALUES (2)
INSERT Table3 (tx) VALUES (3)
INSERT Table3 (tx) VALUES (4)
INSERT Table3 (tx) VALUES (5)
INSERT Table3 (tx) VALUES (6)
INSERT Table3 (tx) VALUES (7)
INSERT Table3 (tx) VALUES (8)
GO
View 1 Replies
View Related
Jul 13, 2004
Took a test and ran into a question. Have a look, am I even close?
View 4 Replies
View Related
Dec 30, 2004
I've got a weird question --
Have any of you ever come across software that can convert MS Jet SQL to ANSI standard SQL or other SQL flavors?
Could be a huge help in my next project. Please let me know if you know of anything like this!!
:)
View 2 Replies
View Related
Oct 27, 2005
I have a 6.5 and 2000 db that allows over flow of data to be entered into the
fields, but I have another 2000 db that does not allow over flow of data to
be entered. How can I check the ansi_defaults in the db's? I went through
the book ol but it didn't explain how to see those values. Only how to set
it using the SET option. Which was SET ANSI_DEFAULTS off go. Does it set it for the whole db or just my session? I need to
change it for all logins to be consistant. Thanks
View 1 Replies
View Related
Jul 23, 2005
Help....I have a DB I'm working with that I know doesn't work with theANSI-92 JOIN SYNTAX....I'm not sure how much this limits my ability todeal with the following situation, so I'm soliciting the help of aguru....I apologize for the lack of scripted table structure, but thisdatabase is embedded in an application that I have no true schema for.I have a crude diagram of the tables and some of the relationships, butI've managed to have manually mapped some of the fields in the tablesI'm working with.What I have is a table(A) that I need to join with 10 othertables.....I'm joining on an identifier in the (A) that may exist manytimes in any of the other 10 tables...and may not be in ANY of thetables.When I run this query:SELECTSAMPLES.PK_SampleUID,UDFSAMPLEDATA02.AlphaData,UDF SAMPLEDATA01.AlphaData,UDFSAMPLEDATA03.AlphaData,UDFSAMPLEDATA05.AlphaData, UDFSAMPLEDATA06.AlphaData,UDFSAMPLEDATA07.AlphaData, UDFSAMPLEDATA08.AlphaData,UDFSAMPLEDATA09.AlphaData,UDFSAMPLEDATA10.AlphaDat aFROM SAMPLES, UDFSAMPLEDATA01,UDFSAMPLEDATA02,UDFSAMPLEDATA03,UDFSAMPLEDATA05,U DFSAMPLEDATA06,UDFSAMPLEDATA07 ,UDFSAMPLEDATA08, UDFSAMPLEDATA09, UDFSAMPLEDATA10WHERE UDFSAMPLEDATA02.AlphaData<>' ' ANDUDFSAMPLEDATA01.FK_SampleUID=SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA02.FK_SampleUID=SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA03.FK_SampleUID= SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA05.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA06.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA07.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA08.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA09.FK_SampleUID=SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA10.FK_SampleUID = SAMPLES.PK_SampleUIDI return what appears to be the gazillion COMBINATIONS of all thefields in all the tables....they query doesn't even finish before theODBC driver I'm working with crashes my VBscript....Is there some way to take the multiple returned rows from a join andwork them all into ONE row per identifier?Any help I can garner would just make my week!TIA!J
View 3 Replies
View Related
May 2, 2006
I've mentioned this before, but now it's got me stuck. I have atable-valued function that was created using QA in SQL 2000. I can'tedit it using 2005 Mgt Studio, because the property AnsiNullsStatusisn't set (apparently SQL 2000 forgot to set it when creating thefunction). Now I find I really want to edit it in SQL 2005 MS withoutgoing to SQL 2000 QA on my laptop. So, using SQL 2005 MS, I droppedand re-created the function, having obtained the code from SQL 2000 QA.That should fix this, right? When SQL 2005 re-creates the function, itwill specify whatever it needs. But no, I still can't edit it! Theproperty is still not set!Can anyone help? Please?Thanks,Jim Geissman
View 2 Replies
View Related
Feb 20, 2008
I have upsized some tables from ms access (using the wizard) which has created many nvarchar fields. I know the system doesn't need unicode characters so I straight away changed them to varchar and the system is all working but am now deciding on which ones to change to char as I understand you get a performance gain. I am trying to do this without changing much ms access front end code so don't want to deal with rtrim I am thinking change the ones I can guarantee the length of. These happen to be the primary keys.
My questions are is this the correct way of doing things, am I correct in assuming I would have to rtrim alot in program code if I changed all to char.
Finally and. most importantly what about a single character field which could be null can I make this a char(1) and it will still return null if empty as it would as a varchar(1). I suppose this question is how does the padding work is it the character followed by a number of nulls (ascii 0 I think).
Sorry for the multiple questions but I think they are all related
View 2 Replies
View Related
Dec 2, 2005
Hi AllCan you please help me with a few queries on adding a header line andpadding rows out.I apologise profusely for not providing the DDL for this, but I don't haveit. All I have is stored procedure that I'm trying to edit to make thefront-end app display the right data.The relevant part of the stored procedure that I'm working on is as follow:Declare StockHelpCursor Scroll Cursor ForSelect s.StockID,ISNULL(sd.ShortDescription, s.StockID) +space(30-len(ISNULL(sd.ShortDescription,s.StockID))) +pl.name +space(10-len(str(pl.name,10,3))) +sp.currencyid + str(sp.sellingprice,10,3) +space(10-len(str(sp.sellingprice,10,3))) +str(sq.quantityinstock)From Stock s, StockDescriptions sd, StockQuantities sq,StockPrices sp, PriceLevels plWhere (s.StockID Like @theID) And(ISNULL(sd.ShortDescription, sd.StockID) Like @theName) And(s.StockID=sd.StockID) And(s.StockID=sq.StockID) And(s.StockID=sp.StockID) And(sp.PriceLevelID=pl.PriceLevelID) And(sd.LanguageID=@theLanguageID) And(sp.CurrencyID=@theCurrencyID)Order By s.StockIDOpen StockHelpCursorPLEASE NOTE: this query works fine apart from the following problems:1) Creating a header line - I need to insert a header line to this cursorfor the field headers, as the app is stripping off this header. I wasthinking of creating a var, sticking it in-between the Declare and theSelect part and inserting these field headers, but I don't know how to dothis. Any ideas?2) Padding the results - As you can see from the script, I have tried to padout the above fields as the app's output window is basically a textbox, butthey just don't line up.NOTE: I'm using the 3 in the str(xxx,10,3) bit to make my numbers show at 3decimal places.Could you please give me some pointers on how I can pad these out correctly.ThanksRobbie
View 7 Replies
View Related
Sep 25, 2005
I need to write a stored procedure where I need to joing 3 tables A, B and C having approximately 200K, 500K, 800K rows respectively.
Query:
1) If I use ansi-syntax (inner join) as against non-ansi syntax (A.col1 = B.col1), I get a better performance.
Any idea why?
2) If I write a query (shown below), it tries to join table A and B returning large number of rows.
Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'
Why does it try to join the table B with A though there is no join specified.
View 12 Replies
View Related
Jan 17, 2007
Hi folks,
How can I re-write the following code in ANSI SQL code:
select cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+ cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar), event_instance_id, max(time_stamp)
from usmuser.usm_sli_event_data
where event_instance_id=10019
group by cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+
cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar),
event_instance_id
order by event_instance_id
Thanks for your help!
-Parul
View 5 Replies
View Related
