Code Table Maintenance
Mar 29, 2004
Hello,
I am working on the logical model for a database. I need to use a number of code tables (tables that keep typically name value pairs. I need maintain data like products, services etc).
I am wondering if I increase the abstraction and use one table to represent the name value pairs but use a category to identify each type. Is there is any value in doing this?
The advantage with this I think is consolidating the data and probably minimizing the administration
The disadvantages may be too many joins that need to be qualified by the category type. Also, I may end up having too many self-joins.
Any suggestions?????
View 1 Replies
ADVERTISEMENT
Mar 28, 2007
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
View 4 Replies
View Related
May 3, 2008
hello,
we have changed the name of MS SQL server 2005 from XYZ to ABC using
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error
Date 03.05.2008 16:00:00
Log Job History (ADM_AdminDB_TP_Backup.Subplan_1)
Step ID 0
Server ABC
Job Name ADM_AdminDB_TP_Backup.Subplan_1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. Unable to determine if the owner (XYZSQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'XYZSQLServer', error code 0x534. [SQLSTATE 42000] (Error 15404)).
please help us in this issue
View 13 Replies
View Related
Nov 28, 2000
I am running an overnight job for table Maintenance - this job execs a SP. It has been working okay for a while and is suddenly failing (last two weeks) with Error 515 cannot insert nulls into column. I realise that it is possible that a change has been made but cannot for the life of me ascertain what it is. I have checked server config, database properties and user options and it all appears to be the same as a server that is running this job okay. Please help!!
View 1 Replies
View Related
Jul 8, 2007
I have been asked to look at some performance isssues with an application that utilises a 800GB table. This table is huge and contains 4 int columns and 1 decimal column. The table has a clustered index that covers 4 of the int columns and is heavily fragmented and it has not been maintained for a long time. The system has limited free space to even attempt rebuilding the index. Does anyone have any experience of running a the Alter Index Reorganize command on such a large table? Any information on what storage would be required to attempt this, how long would this take?
View 4 Replies
View Related
Jul 23, 2005
sql server 2000I am currently maintaining a table that contains 30 Million+ records,30 columns, and 11 indexes and will double within the next six mouths.The application that accesses this table, mainly for read onlypurposes, runs without any problems. We have begun using Crystalreports and are now having problems. When we create reports thataccesses the large table our server has significant performance dip.The application begins to time out and the reports take a very longtime, even with simple selects on indexed field.I have began looking into partitioning the large table on its key fieldand creating a partition view. But from what I have read this willonly help if we key on the partitioned field. And all other searcheswill actually take a little longer.Archiving old data is not an option. All the data is being usedAny suggestions will be appreciated. Thanks in advance.Rick
View 1 Replies
View Related
Mar 5, 2008
Hi all!
I want to perform a DB cleanup automatically using Maintenance Plan
Basically I want to DROP all TABLES that starts with this : 'utt_'
Help please.
:beer:
View 2 Replies
View Related
Aug 17, 2007
Hello,
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
Thanks.
View 1 Replies
View Related
Oct 13, 2006
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View 2 Replies
View Related
Jul 2, 2014
I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
View 5 Replies
View Related
Feb 29, 2008
I am looking into SQL Code Pivot Tables. Does anyone know of any good sites to take a look at?
Let me know.
Thanks
Wendy
View 2 Replies
View Related
Aug 12, 2007
Hi
I developed a program. This program will use a SQL server database. How can I create the database using code. I think I need to use sql scipt like this:
create database customers
But where should I write the script and How can I make VB code implement the script.
thank
View 7 Replies
View Related
Feb 7, 2008
Ok, so im pretty much finished writing my forum web page. However to display things like how many replies each thread has and who replied last, i need to perform a query in the code file. Im guessing its simple enough but i cant get the syntax for actually performing any query. I already know the sql syntax like select * from all that stuff but how do i get do something like:
Dim x as integer = sqlQuery("Select count(*) FROM ...")
Currently i have it all working by creating a table and making it invisible and just pulling data from the table but thats sloppy and pretty ineffecient if i databind a table for every single topic name.
View 9 Replies
View Related
May 29, 2008
Hi i've to table where i have a product code and a area code.
I want to insert the values from table 1 into table 2 if the product code and area code do not already exists in the table in table 2.
Am using this code below but it does not seem to be working.
select distinct p_code1, area_code
from table1
where not exists
(select p_code, area_code
from table 2 )
View 2 Replies
View Related
Jan 1, 2008
I need to connect to a table in my database to get the contents there to put into a string variable in C# behind code. I’m having trouble with writing the part with the SqlConnection. I want to take the contents from a specific field in a specific record. I think the SELECT command might look like:SELECT formulation FROM Current WHERE id = 1formulation,id are fields, Current is the tableThe string variable is defined by: string test="000000";My connectionStrings in the web.config: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>
Thank you for your help.
-Larry
View 4 Replies
View Related
Jun 5, 2008
Hi to all,
I'm writting code that needs to pass data returned by a SqlDataSource object to another object (user control to graph data)
Query in SqlDataSource object is OK, I've seen data returned by it at a GridView Object but I need to pass that data to an user control that needs a reference to the table that has the data
... What would be the code that do that task ??
I'm new to develop web applications... using Visual Web Developer Express 2008
Help in advance
View 1 Replies
View Related
Mar 9, 2006
First of all let me say that ASP.NET a new programming environment for me so please forgive my ignorance.
Can someone please tell me how to write data to a SQL table that is a Binary data type? I have a stored procedure on the SQL server that I am calling to insert data into a table. I build a parameter list and set the values. It worked just fine before I added a binary field to the SQL table. My problem is that I don't know how to set the Binay data type to pass it to the stored procedure. Here is part of the code:
GetCMD = Myconnection.CreateCommand
GetCMD.CommandType = CommandType.StoredProcedure
GetCMD.CommandText = "SCHEMANAME.InsertLineItem"
GetCMD.Parameters.Add("HEADER_ID", SqlDbType.VarChar, 150)
GetCMD.Parameters("HEADER_ID").Value = "some value"
GetCMD.Parameters.Add("@OPTIONS", SqlDbType.Binary)
GetCMD.Parameters("@OPTIONS").Value = HOW DO I SET THIS VALUE????
rowsaffected = GetCMD.ExecuteNonQuery()
I assume serialization but have not figured out how. Anyone's help is greatly appreciated!!
View 1 Replies
View Related
Nov 18, 2005
I'm looking to create a table in SQL using data from the as/400. I need some code that extracts it from as/400 using odbc and I need it to loop and create that table every minute.
The file its retrieving is a live file but not very large.. I need to basically poll the data. Does anyone have code for something like this..
thanks
View 8 Replies
View Related
Feb 12, 2015
I have a table with code and description as below
create table isin_code
(
code varchar(5),
code_desc varchar(255)
)
go
insert into isin_code values ('aaa','aäsas')
go
insert into isin_code values ('aaa','asâ•šas')
go
insert into isin_code values ('aaa','aâsas')
go
insert into isin_code values ('aaa','asas')
go
I want to identify the list of alt codes available in the table.
View 6 Replies
View Related
Mar 22, 2007
I am trying to sort out the member "Adams" from the committee members, and sum up the total amounts that were donated while he was the committee member.
The Table:
CREATE TABLE contribution_list
(contrib_date DATE NOT NULL,
donor_name VARCHAR(30) NOT NULL,
amount NUMBER(8,2),
program VARCHAR2(30),
committee_member VARCHAR2(20) NOT NULL,
PRIMARY KEY (contrib_date,donor_name,committee_member));
The Code that is giving me errors:(not a group by expression):
CREATE or REPLACE VIEW adams_conrtibution_total as
select program, committee_member, sum(amount)
from contribution_list
where committee_member = 'Adams'
group by program;
View 1 Replies
View Related
Sep 28, 2007
I wish to call a custom code function from a table control that would return rows of data to be displayed in the table. Is this possible?
Specifically, I'd like to pass a large text string to the function, have the function break the string into smaller strings, and then have the smaller strings displayed in the table. The number of lines returned may vary, depending on the original string passed in.
View 5 Replies
View Related
Aug 9, 2013
I want to check if a table is empty using query code?
How to do this?
View 4 Replies
View Related
Dec 3, 2004
Hi,
I am trying to find some document or code that will load an excel spreadsheet into an sqlserver database.
Can anyone please point me in the right direction.
View 2 Replies
View Related
Dec 24, 2014
I am building a Visual Sudio process that will Truncate and load data to an Access database (2010) from SQL Server (2012). I have one "Execute SQL Task" for one table (LungCancer_HF_Patients):
"DELETE LungCancer_HF_Patients.* FROM LungCancer_HF_Patients;" that parses and works.
I have another table (Report1):
"DELETE Report1.* FROM Report1;"
that does not parse with a message "The query failed to parse. Incorrect syntax near '*'."
Why the first one parses correctly and the other one does not?
View 2 Replies
View Related
Nov 15, 2007
I use the following query to shred an xml and insert it into a table, but I want to have the whole html structure into the column to be able to present the formatted text in Cognos 8 BI.
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
INSERT INTO TMP_ATGFORSLAG (AtgForslagDesc)
SELECT
AtgForslagRad.value('(/my:AtgForslagRad/my:AtgForslagDesc)[1]', 'varchar(2000)') AS AtgForslagDesc
FROM dbo.DeklAtgForslagXml
The structure of the source in the xmlfile I want to import is:
<my:AtgForslagDesc>
<html xmlns="http://www.w3.org/1999/xhtml">
<ol>
<li>Text1...</li>
<li>Text2...</li>
<li>Text3...</li>
</ol>
</html>
</my:AtgForslagDesc>
How do I shred not only the text but the whole starting <html> to finishing </html> and insert it into a table I would be very happy.
View 1 Replies
View Related
Jan 17, 2006
Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much
View 3 Replies
View Related
Dec 12, 2007
Does anyone know how to query a field in a table where it contains anASCII code >= 128 - without looping through every field for everyrecord in table (using charindex)?Ex of char I would like to find: ü which is char(252)
View 1 Replies
View Related
Apr 5, 2008
help,a regular text file how to sql 2000 table code ?i have a text file as follow, line with ¡°|¡±and {LF},8|-000000186075919.|+000000000387820.|2008-03-31|20010423|9|-000000000003919.|-000000000123620.|2008-03-31|20010123|8|-000000018623419.|+000000000381230.|2008-05-30|20010423|i want to sign char(1)£¬year decimal(18,3) , month decimal(18,3), trandatesmalldatetime£¬update smalldatetime£¬after to sql table is as follow,sign year month trandate update8 -186075919.000 387820.000 3/31/2008 4/23/20019 -3919.000 -123620.000 3/31/2008 1/1/20018 -18623419.000 387820.000 5/30/2008 4/23/2001could you help me how write the sql code ?
View 1 Replies
View Related
Sep 11, 2015
below is some code I use to identify where a patient has attended the ED department whilst also admitted as an Inpatient. This report works fine. However while most of the results are recording issues to be corrected some of them are real and as so can be excluded from the report.
Is there a way I can build in an exclusion table which would include:
 Â
SELECT
IP_ADMISSION.HeyNo AS HEYNo
,IP_ADMISSION.NHSNo2 AS NHSNo
,IP_ADMISSION.Forename AS Forename
,IP_ADMISSION.Surname AS Surname
,IP_ADMISSION.SourceAdmNatCode AS SourceAdm
[code]....
View 4 Replies
View Related
Nov 29, 2007
I am not sure I understand the problem I am causing, but I am a beginner!
Here's the situation: I have a table located on MS SQL server database number 1. Said table, which we'll call WIDGET_PRICES, is accessed regularly by my existing source code and has no problems.
At some point, I decide to move operations to MS SQL database number 2 and do a very simple database copy of WIDGET_PRICES from database 1 to database 2 using the Microsoft SQL Server Management Studio.
The end result, inevitably, is that my source code can no longer access the very same table as it is located on the new database server. The code hasn't changed, it's still trying to access WIDGET_PRICES as always. And, from what I see on my screen through Management Studio, WIDGET_PRICES appears just fine.
An example error is the one I just got:
Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name 'YB_ITEMS'.
/yardbark/tampabay/header.asp, line 27
The only clue is that while my transferred tables often look named like "database1.WIDGET_PRICES on database 1, they wind up looking like database2.WIDGET_PRICES on dabase 2.
I include a little more detail and screenshots of the tables in questions at this web page.
Dave
View 3 Replies
View Related
Sep 8, 2006
I need to run stored procs based on a list in a code table. In other words, it reads the name of a stored proc from a table and runs it and does this for all the rows in the table. I think the ForEach loop container will do what I need and there is an ADO enumerator option but the documentation does not tell you how to use this. From what I can tell, you need to get a dataset into an SSIS variable first and then you plug the variable name into the ForEach ADO enumerator. Is that correct? If so, can someone tell me how to get a dataset into a variable?
Thanks
View 1 Replies
View Related
Jan 29, 2008
Hi,
Please do anyone know a VB 2005 code to export and import SQL server table to Oracle. Thanks.
View 3 Replies
View Related
Nov 20, 2001
I have converted a access database to sql server 2000 for some reason i keep getting this error base table not found.
I know the sql database is there and i also know the table is there. I'm not sure why i keep getting this error..
thanks
View 2 Replies
View Related