|
Hi,
i'm having a very strange problem on my code, i'm on VS2008 with .net 3.5 and with SQLServer 2005, i have the 'correct' data tracked with the profiler tool;
SELECT x.columnName, x.columnOrdinal, x.columnTypeID, x.columnMaxLength, x.columnPrecision, x.columnScale, x.columnAllowNull, x.columnIsIdentity, x.columnFKTable, Case When y.Name Is Null Then 'No' Else 'Yes' End As columnPrimaryKey
FROM ( select sc.name columnName, sc.colid columnOrdinal, sc.xusertype columnTypeID, sc.length columnMaxLength, sc.xprec columnPrecision, sc.xscale columnScale, sc.isnullable columnAllowNull, (sc.status & 0x80)/0x80 columnIsIdentity, sor.name as columnFKTable, sc.colorder
FROM [teste].[SYS].[syscolumns] sc
INNER JOIN [teste].[SYS].[sysobjects] so ON sc.id=so.id
LEFT OUTER JOIN [teste].[SYS].[sysforeignkeys] sfk ON so.id=sfk.fkeyid AND sc.colid=sfk.fkey
LEFT OUTER JOIN [teste].[SYS].[sysobjects] sor ON sor.id=sfk.rkeyid
WHERE SO.Name='Centro' ) AS x
LEFT JOIN
( SELECT c.name, c.colid
FROM sysindexes i
INNER JOIN [teste].[SYS].[sysobjects] t ON i.id = t.id
INNER JOIN [teste].[SYS].[sysindexkeys] k ON i.indid = k.indid AND i.id = k.ID
INNER JOIN [teste].[SYS].[syscolumns] c ON c.id = t.id AND c.colid = k.colid
WHERE i.id = t.id AND i.indid BETWEEN 1 And 254 AND (i.status & 2048) = 2048 AND t.id = OBJECT_ID('Centro')) As y
ON x.columnName = y.Name
ORDER BY x.colorder
the result will have this row of data:
centroID 1 56 4 10 0 0 1 NULL Yes
the problem it's on the last column, here is a 'Yes' and on VS, while debugging right after the dataadapter.fill i get a 'No', obviously with the same query.
Here is the code i used to get the data from the DB:
public void GetColumnsForTableFromDatabase(string tabela)
{
string queryForColumns = "SELECT x.columnName, x.columnOrdinal, x.columnTypeID, x.columnMaxLength, x.columnPrecision, x.columnScale, x.columnAllowNull, x.columnIsIdentity, x.columnFKTable, Case When y.Name Is Null Then 'No' Else 'Yes' End As columnPrimaryKey \r\n";
queryForColumns += "FROM ( select sc.name columnName, sc.colid columnOrdinal, sc.xusertype columnTypeID, sc.length columnMaxLength, sc.xprec columnPrecision, sc.xscale columnScale, sc.isnullable columnAllowNull, (sc.status & 0x80)/0x80 columnIsIdentity, sor.name as columnFKTable, sc.colorder \r\n";
queryForColumns += "FROM [" + cmbBoxDatabases.Text + "].[SYS].[syscolumns] sc \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysobjects] so ON sc.id=so.id \r\n";
queryForColumns += "LEFT OUTER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysforeignkeys] sfk ON so.id=sfk.fkeyid AND sc.colid=sfk.fkey \r\n";
queryForColumns += "LEFT OUTER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysobjects] sor ON sor.id=sfk.rkeyid \r\n";
queryForColumns += "WHERE SO.Name='" + tabela + "' ) AS x \r\n";
queryForColumns += "LEFT OUTER JOIN \r\n";
queryForColumns += "( SELECT c.name, c.colid \r\n";
queryForColumns += "FROM sysindexes i \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysobjects] t ON i.id = t.id \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysindexkeys] k ON i.indid = k.indid AND i.id = k.ID \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[syscolumns] c ON c.id = t.id AND c.colid = k.colid \r\n";
queryForColumns += "WHERE i.id = t.id AND i.indid BETWEEN 1 And 254 AND (i.status & 2048) = 2048 AND t.id = OBJECT_ID('" + tabela + "')) As y \r\n";
queryForColumns += "ON x.columnName = y.Name \r\n";
queryForColumns += "ORDER BY x.colorder \r\n";
sqlConn.ConnectionString = getConnectionString();
SqlCommand sqlCom = new SqlCommand(queryForColumns, sqlConn);
SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCom.CommandText, sqlConn);
if (DS.Tables.Contains(tabela))
DS.Tables[tabela].Clear();
sqlConn.Open();
sqlDA.Fill(DS, tabela);
sqlConn.Close();
}
I'm lost here, but on my tests i got the correct result if replace:
queryForColumns += "( SELECT c.name, c.colid \r\n";
queryForColumns += "FROM sysindexes i \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysobjects] t ON i.id = t.id \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[sysindexkeys] k ON i.indid = k.indid AND i.id = k.ID \r\n";
queryForColumns += "INNER JOIN [" + cmbBoxDatabases.Text + "].[SYS].[syscolumns] c ON c.id = t.id AND c.colid = k.colid \r\n";
queryForColumns += "WHERE i.id = t.id AND i.indid BETWEEN 1 And 254 AND (i.status & 2048) = 2048 AND t.id = OBJECT_ID('" + tabela + "')) As y \r\n";
with this:
queryForColumns += " ( SELECT 'centroID' as name, 1 as columnID) As y ";
i know this is strange, but it works, it leads me to think that this has something to do with the 'LEFT JOIN'.
Thanks for the help in advance,
Chapas
|
|
|
|
|
Have you grabbed the exec from Profiler and run it in QA/MS.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
thanks for your reply,
yes i've taken the result obtained from the profiler, during the the debug of my program, and paste it to QA/MS and result was the expected, in the debugger was not the same doe.
|
|
|
|
|
Perhaps something odd is happening caused by the way you are building up the query string (not particularly good practice...). Try building up the query in a StringBuilder using the Append method, and pass it to the SqlCommand Object using StringBuilder.ToString() , and omit the "\r\n" business (SQL doesn't need them, if you want them to ease debugging, then use Environment.Newline constant instead). Also consider trimming the variable strings before using them in the query, just to insure there are no stray leading or trailing spaces.
|
|
|
|
|
Rob Graham wrote: Environment.Newline
I love this place, you learn something new every day, I have the control \r\n characters coming from a VB background, this looks like the old VBNewLine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks a lot,
this seems to solve my issue, using the StringBuilder and the Append method.
Don't know how to explain this issue, but the data from the DB got correctly to my app.
|
|
|
|
|
please, I want simple table in SQL to training and query, and thank you.
|
|
|
|
|
CREATE TABLE [dbo].[Customer](<br />
[CustomerID] [nvarchar](20) NOT NULL,<br />
[Description] [nvarchar](90) NULL,<br />
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED <br />
(<br />
[CustomerID] ASC<br />
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]<br />
) ON [PRIMARY]<br />
<br />
CREATE TABLE [dbo].[Users](<br />
[User_Code] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,<br />
[First_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
[Last_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED <br />
(<br />
[User_Code] ASC<br />
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]<br />
) ON [PRIMARY]<br />
<br />
CREATE TABLE [dbo].[QuoteHistory](<br />
[ID] [int] IDENTITY(1,1) NOT NULL,<br />
[Qdate] [smalldatetime] NULL,<br />
[RequestorID] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
[CustomerID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
[ProductCode] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br />
CONSTRAINT [PK_QuoteHistory] PRIMARY KEY CLUSTERED <br />
(<br />
[ID] ASC<br />
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]<br />
) ON [PRIMARY]
|
|
|
|
|
Huh? What is it you are trying to do? Learn how to make a simple table? If so, there's plenty of good resources ( google, books, etc )...
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
If you need a sample database, you can use for example Northwind database provided by Microsoft. Found here: Sample Databases[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
As we know that "Set NoCount ON;" increases the performance in SQL server queries by avoiding any extramessages being sent to client...
We are using this statement invarialbly in all procedures and I have seen it being part of SQL Server default procedure template...
What I couldnt figure out is if we need to use this with all the statements why is it OFF by default???
What are the cases where we shouldn't be using it???
~AHAGeek
|
|
|
|
|
A _sp that returns a result set and you want the count (often for queries and reporting purposes).
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
In SP if we want count..we can still use @@RowCount with this statement on???
~AHAGeek
|
|
|
|
|
Typically I would use @@ROWCOUNT in a stored procedure where there are several transactions that are going to modify records. You can then be certain that every update, delete, insert occurs as expected; esp. in stored procedures where it's all or nothing. If 1 table doesn't get modified as expected, you can then rollback.
IE: You have a SP (with NOCOUNT = ON) that has several table updates for updating general ledger entries for A/R and then write the changes into a history table. If one update fails (by checking @@ROWCOUNT), the entire transaction gets rolled.
Returning a rowcount (meaning NOCOUNT = OFF) is good for a very simple sps that return a result set and the row count can be displayed for information purposes and ensure the query didn't error off.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
I suggest the default is bc it is useful when coding a proc, it is only when you go to production that you want to turn it off.
It would be nice to be able to turn it off as a database/server option.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, I searched the boards and found how to do this, but I have come up against a problem that I cannot seem to resolve.
This is the code to create the stored procedure: (It was create first but I have been playing with it)
ALTER PROCEDURE [dbo].[sproc_CreateMarriageTable]
-- Add the parameters for the stored procedure here
@Surname varchar(50)
AS
BEGIN
DECLARE @SQLStatement varchar(1500)
SET @SQLStatement = 'CREATE TABLE [' + @Surname + ']
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname +'_FName] DEFAULT (''''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_TName] DEFAULT (''''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_' + @Surname + '_MF] DEFAULT (''M''),
[Year] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_PerMar] DEFAULT (''''),
[BirthDate] [smalldatetime] NULL,
[Volume] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_Vol] DEFAULT (''''),
[PageNo] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_PNo] DEFAULT (''''),
[RegDistrictCode] [int] NOT NULL CONSTRAINT [DF_' + @Surname + '_RegDistCode] DEFAULT ((0)), CONSTRAINT [PK_' +@Surname + ']
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY])
ON [PRIMARY]'
EXEC @SQLStatement
END
This parses and compiles without any errors. Then I tried to execute the procedure:
USE [BMD2005]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[sproc_CreateMarriageTable]
@Surname = N'M_VAISEY'
SELECT 'Return Value' = @return_value
GO
It fails with the message:
Msg 203, Level 16, State 2, Procedure sproc_CreateMarriageTable, Line 29
The name 'CREATE TABLE [M_VAISEY]
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_FName] DEFAULT (''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_SName] DEFAULT (''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_TName] DEFAULT (''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_M_VAISEY_MF] DEFAULT ('M'),
[Year] [smallint] NULL CONSTRAINT [DF_M_VAISEY_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_M_VAISEY_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_PerMar] DEFAULT (''),
[BirthDate] [smalldatetime] ' is not a valid identifier.
The declaration of the statement variable is plenty long enough (The text is about 1200 characters) and the place where it stops is about 687 characters.
Is there a problem with NULL? Do I need to enclose that in something?
If anyone can see what the problem is I would be most grateful. 
David
UPDATE: I removed a couple of the filds from the end and got this message when I executed
Msg 203, Level 16, State 2, Procedure sproc_CreateMarriageTableTest, Line 25
The name 'CREATE TABLE [M_VAUGHAN]
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_FName] DEFAULT (''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_SName] DEFAULT (''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_TName] DEFAULT (''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_M_VAUGHAN_MF] DEFAULT ('M'),
[Year] [smallint] NULL CONSTRAINT [DF_M_VAUGHAN_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_M_VAUGHAN_Qtr] DEFAULT ((1)),
[BirthDate] [smalldatetime] NULL
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTI' is not a valid identifier.
It would seem there is some lenght of string problem. Any ideas?
modified on Friday, October 17, 2008 11:07 AM
|
|
|
|
|
DECLARE @SQLStatement varchar(1024)
DECLARE @SQLStatement2 varchar(1024)
SET @SQLStatement = 'CREATE TABLE [' + @Surname + ']
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname +'_FName] DEFAULT (''''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_TName] DEFAULT (''''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_' + @Surname + '_MF] DEFAULT (''M''),
[Year] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_PerMar] DEFAULT (''''), '
SET @SQLStatement2 = '[BirthDate] [smalldatetime] NULL,
[Volume] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_Vol] DEFAULT (''''),
[PageNo] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_PNo] DEFAULT (''''),
[RegDistrictCode] [int] NOT NULL CONSTRAINT [DF_' + @Surname + '_RegDistCode] DEFAULT ((0)), CONSTRAINT [PK_' +@Surname + ']
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY])
ON [PRIMARY]'
EXEC (@SQLStatement + @SQLStatement2)
|
|
|
|
|
Hi,
It looks like the length is the issue. I guess you might have some tab characters in the code for formatting perhaps, not sure. Anyway, I just fixed a similar problem in a Sybase IQ procedure and just removed all the white space.
So for example...
DECLARE @SQLStatement varchar(1500)
SET @SQLStatement = 'CREATE TABLE [' + @Surname
+ '] ([AutoID] [int] IDENTITY(1,1) NOT NULL,'
+ '[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_'
+ @Surname
+ '_FName] DEFAULT (''''), '
+ '[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname
+ '_SName] DEFAULT (''''), '
+ '[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname
... etc
... etc
... etc
+ ' STATISTICS_NORECOMPUTE = OFF, '
+ ' IGNORE_DUP_KEY = OFF, '
+ ' ALLOW_ROW_LOCKS = ON, '
+ ' ALLOW_PAGE_LOCKS = ON)'
+ ' ON [PRIMARY]) '
+ ' ON [PRIMARY]'
EXEC @SQLStatement
I hope that helps.
Good luck  ,
Kevin
|
|
|
|
|
Took me a second or two to work out what you were saying. *david scratches head* Ah! The light dawned - concatenation
Yes of course the wrapping or rather formatting may be adding spurious characters.
Strange thing is that it worked when I split the line into two. Perhaps I just hit the right spot by luck rather than by judgement! 
I will give it a try.
Thanks
David
|
|
|
|
|
Worked a treat. Thanks again.
|
|
|
|
|
I'm missing the 'Open Table' right click menu item in SSMS 2008. I am running SQL Compact 3.5 sp1. Any ideas on how to get the menu option back.
It's getting extremely annoying having to view, modify, insert/delete by using queries.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
I was hoping someone had a fix for this. So annoying. Only with compact DBs.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
Hi all.., How to find refresh percentage of a database through QUERY?
D.Willington
|
|
|
|
|
What do you mean exactly? Your question makes no sense.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i have an application to refresh a database. I have to select database, backup file path and all.., it is working well.., while the database in refresh i have to display this much percentage has fininshed and so on.
so i want to get the completed percentage of refresh database.
D.Willington
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
