Little help with returning both the name and the current identity for all tables? If a table doesn't have any rows, instead of the current identity, I'd like to return 0. The database will be in single user mode during the query if that helps.
The following query is close but it still returns 1 for tables that don't have any rows. Somehow the CASE expression isn't right.
Thanks in advance.
WHEN COUNT(TABLE_NAME) = 0 THEN
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
TABLE_TYPE = 'BASE TABLE'
SELECT CASE <br />
WHEN COUNT(TABLE_NAME) = 0 THEN 0<br />
WHEN COUNT(TABLE_NAME) > 0 THEN IDENT_CURRENT(TABLE_NAME)<br />
FROM INFORMATION_SCHEMA.TABLES<br />
WHERE TABLE_TYPE = 'BASE TABLE'<br />
GROUP BY<br />
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
Maybe I'm missing something here. How can that ever return 0?
If I have understood it correctly, this is what the query will do:
Select the rows from TABLES with type "BASE TABLE"
Group them by TABLE_NAME
Counts the number of rows for each TABLE_NAME
If the count is 0, return 0
If the count is not 0, return the identity of the table
But, surely the only way COUNT(TABLE_NAME) can be 0 is if there are no rows in TABLES with this TABLE_NAME and type "BASE TABLE". In which case, this query would never pick up that table name, so it would never appear in the result set. So for any TABLE_NAME that this query picks up, the count will always be greater than 0.
I have aproblem with my websit veiwing arabic data, when I importing data from csv file into web host sqlserver database the data in tables appears like ??????? and squares also in web pages, for notes the arabic field data type is nvarchar max and collation is arabic language .
so If any body have the answer please help me fast
But how are you importing the data. What encoding are you using when you read the data in? Just because the database is storing these values doesn't mean that the problem is at the database end - it's entirely possible that the problem is at the data population end.
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Hi, am designing a database that expedite collection of data from the insurance companies by insurance body. members are required to upload daily transaction in xml format.the insurance policy no is the primary key but other information can change. the data will be query by users. should i store the data as xml datatype or in split the column as use cdc to audit the canges or plit the column with history table . like below
I'd also add to the above comment to create a RealProperty view that queries on only those columns that deal with RealProperty and like wise for the HomeOwner data as well. Just make sure that each view uses the index fields defined at the table level and everything should work fine.
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
I would not count on that. Besides, as a developer, you would be better off learning to handle the more general situation sooner rather than later.
I would take a slightly different tack and either have the RealProperty table refer to the HomeOwner table (as it's more common for one owner to own multiple properties) or (more likely) allow many-to-many relationships via a third table. But that's just me.
I would not use a trigger. In the DAL, I would have a method to add a property and a method to add an owner -- and another method to add a relationship. In the API I would have a method that calls both (all three) and handles transactioning.
I would also use SQL Server .
Edit: Fixed relationship .
Edit 2: Oh, yeah, you had it that way...
I do believe your data structure is incorrect. Your constraint of 1:1 between owner and property fails when someone own more that 1 property or a property has more than 1 owner or a property is sold (I assume you wish to retain history of the property).
As Piebald suggested a many:many link table is what you need to implement.
What happens if you have a company own the property, now you are in the position of needing an address table so you need a many:many link between owner and address...
Designing a data structure is not a trivial excercise and needs time and effort to get it right. It is guaranteed that you will not get it right the first time. Be prepared to admit an error and redesign ASAP, allowing an incorrect design to persist only deepens the cost of fixing the problem.
Never underestimate the power of human stupidity
I agree with the statement that I think your reasoning is flawed.
More often then not you are going to see multiple owners to a home (or any other entity) simply because of things like marriage, partnerships, trusts, etc... Also, keeping history now requires that you set up 2 tables that contain the same data linked to a building. Keeping it all in one table and adding an attribute to show current vs. past ownership I think is the better option.
In fact, you are probably really going to want to separate out the table that tracks the data about the building, the tables that track the address data of all the possible owners and then use a join table to crate the linking between the two. That I think is the best way to achieve a clean design. When you consider what else you can store in this third reference table (IE: the data about the sales agent(s) involved, the inspectors used durring the sales, etc...) you start to see how it works cleaner.
For an example, using Sql Server - let's say I have three tables:
Each record in Data can be linked back to each Client record through the File Record. I want the DataField value to be unique only per client - so two values of "42" in the DataField is ok as long as they link to different clients.
I could put the ClientID in the Data table, but that would then mean I would need to be sure the ClientID in the related File record always matches.
Can an index be set on the Data table using the DataField field and the Client ID field?
If so, how would you do this? If not, is there an alternative?
I think you can add two unique constaint in File..ClientID and Data..FileID to achieve your goal. By your data design, if you want the DataField value to be unique only per client, the infomation in [File] should also unique match one [Client] record.
We have a linked server on SQL 2005 to database on AS400. the linked server is made using Microsoft DB2OLEDB provider.
when we are using the linked server, we are getting the following error:
OLE DB provider "DB2OLEDB" for linked server "AS400PROD" returned message "".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "AS400PROD".
When we restart the SQL server machine, the linked server works fine for about 5 minutes then we receive the same error as described before,
The script of the linked server is :
EXEC master.dbo.sp_addlinkedserver @server = N'AS400PROD', @srvproduct=N'AS400', @provider=N'DB2OLEDB', @datasrc=N'S6595D7F', @provstr=N'Provider=DB2OLEDB;Password=ISA108;Persist Security Info=True;User ID=ASI;Initial Catalog=S6595D7F;Data Source=AS400;Network Address=184.108.40.206;Package Collection=KFILFNB;DBMS Platform=DB2/AS400;Process Binary as Character=True'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400PROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'LOGINRPORT',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'use remote collation', @optvalue=N'true'
What iam sure of is that it is not an issue from SQL server. i have tried to ping the AS400 server and the reply is <1 ms then i wrote a query to check the linked server and it is giving an ok reply b4 it stops suddenly. i have seen soo many possible issues and read articles abt the same error Msg 7303 and i will be trying to update the server with hotfixes. Are there any fixes that came to your mind?
MKG Ex Du A918731765L
TVN Ex Du A919721354L
NCL Ex Du A901713191L
Bin Ex Du A948606458L
--- -- -- ----------
--- -- -- ----------
etc etc etc(table is having hundreds of data like this and it is a expanding table.So i have to split all of them not only the mentioned data.So a common way to split this column is required.)
I am writing a stored procedure to get this columns as
SK1 SK2 SK3 SK4 SK5
MKG EX Du A91873176 5L
TVN Ex Du A91972135 4L
The values will be always in the above format.
Thanks in advance
-- Modified Monday, October 18, 2010 12:20 AM
Last Visit: 31-Dec-99 19:00 Last Update: 10-Dec-23 3:15