Sp_help Can't See My Tables
I'm trying to use sp_help to get information on my tables. I can use
sp_help alone to get a list of objects (including user tables), but
when I pass a table name as an argument I get the following error
message:
exec sp_help
exec sp_help parcel
Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
The object 'DGM_HILLSHADE2' does not exist in database 'raster'.
The table clearly exists, but sp_help fails to find it and return
info. This is the case in a couple of my databases. I'm connected as
'sa' so it seems to me it shouldn't be a permissions problem, right?
D Bull
View Complete Forum Thread with Replies
Related Forum Messages:
Determining What Are &"system&" Objects In Sp_help Or System Tables
Hi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
View Replies !
Sp_help
We use sql server 6.5. When I do an sp_help <table_name>, there is a last column called "FixedLenNullInSource". What is the purpose of the column? How does it get set to "Yes" or "No"? I am doing a select * into <new_table> from <old_table> in my code. Then I do a insert into that table. It works fine most of the time. When it works fine and I do a sp_help on the <new_table> and all columns have either "FixedLenNullinSource" as n/a or No. Suddenly when it starts working improperly (it pads the varbinary column with zeroes)I checked the table with the sp_help <New_table> command it shows the varbinary column with "FixedLenNullinSource" as Yes. Is there a way to manually make it 'No'? Any help will be appreciated
View Replies !
SP_Help
I need to write dynamic SQL statment that will delete a code from a table but before you can delete that code...I need to find all the foreign key constraints (all the tables that references this table to either update or delete from them and then delete from this table last) I know how to get this information from SP_Help but i dont know how I can do this dynamically. Please help. Emadkb
View Replies !
Problem With Sp_Help Proc
Hi All, I am using Sp_Help <TableName> command to get details of table. I found out one strange this, can anybody tell me is this problem with sp_Help store Proc. I have created table AsseType With AssetID DataType: Int AssetDesc DataType: nvarchar(30) If I go to Enterprise Manager right click on it table and Click on Design, I can see size is 30. But if I go to Query Analyzer and type Sp_Help AssetType Then I get length as 60. One thing which I notice is apart from nvarchar datatype for all other datatypes, it returns same field length as seen in Design mode. I found out this on SQL7 with SP3 and SQL2K with SP1. Any help......... Many thanks in Advance. Regards, Santosh
View Replies !
Problem With Sp_Help Proc
Hi All, I am using Sp_Help <TableName> command to get details of table. I found out one strange this, can anybody tell me is this problem with sp_Help store Proc. I have created table AsseType With AssetID DataType: Int AssetDesc DataType: nvarchar(30) If I go to Enterprise Manager right click on it table and Click on Design, I can see size is 30. But if I go to Query Analyzer and type Sp_Help AssetType Then I get length as 60. One thing which I notice is apart from nvarchar datatype for all other datatypes, it returns same field length as seen in Design mode. I found out this on SQL7 with SP3 and SQL2K with SP1. Any help......... Many thanks in Advance. Regards, Santosh
View Replies !
DB Suspect Not Show In Sp_help
Hi! I am having a problem with one of my local data bases, it shown the status of (suspect). When I try to apply solutions that I found on internet for example run: sp_resetstatus 'dbImpOfertas' it shows the message that : The databse 'dbImpOfertas' does not exist. Use sp_help to show available databases. This is the log information: 2007-11-28 00:00:03.68 spid53 Starting up database 'bdImpOfertas'. 2007-11-28 00:00:03.76 spid53 Error: 602, Severity: 21, State: 50 2007-11-28 00:00:03.76 spid53 Could not find row in sysindexes for database ID 15, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.. 2007-11-28 00:00:05.21 spid53 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_instance_regread'. 2007-11-28 00:00:05.21 spid54 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_instance_regread'.
View Replies !
HELP: Sp_help And Object Browser Report View Column Sizes Differently
Hi,I've run into a curious problem with MS SQL Server 8.0. Using sp_help andSQL Query Analyzer's object browser to view the columns returned by a view,I find that sp_help is reporting stale information.In a recent schema change, for example, someone lengthened a varchar columnfrom 15 to 50 characters. If we use sp_help to find out about a view thatdepends upon this column, it still shows up as VARCHAR(15), whereas theobject browser correctly reports it as VARCHAR(50).Dropping and recreating the view fixes the problem, but we have quite a fewviews, and dropping and re-creating all of them any time a schema change ismade is something we want to avoid. I tried using DBCC CHECKDB in hopes thatit would 'refresh' SQL Server's information, but no luck.(if you're curious as to why I don't just use the object browser instead,read boring technical details below)Has anyone seen this before? Is there some other way (other thanre-creating every view) to tell SQL Server to "refresh" it's information?Thanks!-Scott----------------------Boring Technical Information:The reason this is an issue for us (i.e., I can't just use the objectbrowser instead) is that our object model classes are built using standardmetadata query methods in Java that seem to be returning the same staleinformation that sp_help is returning. These methods are a part of thestandard JDK, so we can't easily fiddle with them. Anyway, as a result, ourobject model (at least with respect to views) may not match our currentschema!
View Replies !
Why Would Tables Pulled In From ODBC In Access Be Different Than Tables In SQL 2005 Tables?
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms. I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data! So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking? Thanks, Keith
View Replies !
Track The Changes To Normalised Tables And Update The Denormalised Tables Depending On The Changes To Normalised Tables
We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View Replies !
Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database
From Newbie to Newbie, Add reference to: 'Microsoft ActiveX Data Objects 2.8 Library 'Microsoft ADO Ext.2.8 for DDL and Security 'Microsoft Jet and Replication Objects 2.6 Library -------------------------------------------------------- Imports System.IO Imports System.IO.File Code Snippet 'BACKUP DATABASE Public Shared Sub Restart() End Sub 'You have to have a BackUps folder included into your release! Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb" 'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & "" Try File.Copy(z, f) Catch ex As System.Exception System.Windows.Forms.MessageBox.Show(ex.Message) End Try MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub Code Snippet 'RESTORE DATABASE Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName 'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With 'Notify user MsgBox("Data restored successfully") Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub Code Snippet 'CREATE NEW DATABASE Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5") Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table 'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet OLEDB:Engine Type=5") 'Open the Catalog Cat.ActiveConnection = Cn 'Create the table (you can name it anyway you want) Tablename.Name = "Table1" 'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID") 'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8) 'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename) 'User notification ) MsgBox("A new empty database was created successfully") 'clean up objects Tablename = Nothing Cat = Nothing Cn.Close() Cn = Nothing 'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub Code Snippet 'COMPACT DATABASE Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine 'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5") 'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") 'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb") 'User notification MsgBox("The database was compacted successfully") End Sub End Class
View Replies !
Can I Export Tables So That Existing Tables In Destination Database Will Be Modified?
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
View Replies !
Saving Tables That Are Generated By Queries As HTML File Or Sub-tables
I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows. The table has columns like Commodity, Unit, Quantity, Value, Month, Country A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx" The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4. It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage. I wanted to know if there is an alternate way to pull the data from server ? I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ?? OR Can I write a script that creates a html files for each table for all input combinations save them ? OR Is there exists any other solution ?
View Replies !
Dynamic Tables Names And Temporary Tables Options
Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
View Replies !
Insert Records From Foxpro Tables To SQL Server Tables
Hi, Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables: 1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables. I only know the following ways to import Foxpro data into SQL Server: #1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables I'm thinking whether the following choices will be better than the current way: 1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2 Thank you for any suggestion.
View Replies !
Updating Tables In SQL Server 7 Based On Access 97 Tables
Let me start by saying I'm very new to SQL Server... I've upsized my Access Database to SQL Server successfully, but need to keep updating my SQL Server Database with data in my Access 97 database. For example, a table in my Access Database is updated on a regular basis and at certain times, we want to upload that information to our SQL Server database. How can I easily overwrite data in a SQL Server table with data imported from Access 97? I keep getting error messages about the primary/foreign keys... Any help would be greatly appreciated. Glenn
View Replies !
Temp. Tables / Variables / Process Keyed Tables ?
I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side? [Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)
View Replies !
How To Create Multiple Tables On The Fly So That Every User Each Has His/her Own Set Of Tables?
Hello all, Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients. Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result. What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1 Please help :-D Greetingz, DJ Roelfsema
View Replies !
Smalller Tables From Larger Tables
hi. I am very new to databases. At the moment I have one big database that has column titles such as author, book title, rating, isbn, publisher, publish date, copyright date, graphical image review, availability, genre. I want to break this large table down into say four smaller relational tables. Partly because it is so large and partly to make it easier to bind the data to the web site. I also want to keep the large table. How can i do this? I use sql server management express. The database is on my hosts database. I also use VWD 2005 express edition. thanks for your time nick
View Replies !
Database Tables And Lookup Up Tables?
Ok say I would like to build a table for of the following questions(say 6 questions for the sake of argument): Do I just stored the index of the radiobuttonlist. What are some resources that I could look at. Should I make a look up table. 5) If money were no object, I would live . . . Prefer not to say On a tropical island In a New York penthouse In an English castle On a Texas ranch In a Malibu beach house In a mountain retreat (Selected) On the moon None of the above 1 ---> 2 ---> 3 ---> 4 ---> 5) --->6 This is the question we are looking at. 6 ---> How should I create the database table for the above example.
View Replies !
Multiple Datasets And Tables With In Tables
I have a report that is sorted by User that shows the selected months details. eg: User1 Details Summary for User1 User2 Details Summary for User2 ... This works fine. I am trying to add for each users summaries for the previous month. I was thinking to call a second dataset using the same query, but for the previous month. I would then create a table with the results I want and imbed it into the first table. eg: User1 Details Summary for User1 Previous months summary for User1 User 2 Details Summary for User2 Previous months summary for User2 Problem: The second instance of the query requires the User to be passed. I do not know how to do that. Also, is this even possible? Thanx
View Replies !
Joining Tables - But Data Not Always In All Tables
I have created 3 views, which I then want to join to produce an overall result. The first view returns customer details, along with payment information. The next two views return values only when the customer has purchased extras outside our standard product i.e. if there is no purchase of an extra, then nothing is written to the extra's table. When I join the views together they only return values where data has been matched in all 3 views i.e. extra's have been purchased. Any data that did not match in all 3 view (i.e. no extra's purchased) is either ignored or dropped from the results. So I need my script to return all values even if no data exists in the two extra views. My scripts are as follows: Main View SELECT CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, CUSTOMER_POLICY_DETAILS.POLICYNUMBER, --TotalPayable IsNull(SUM(CASE LIST_TRAN_BREAKDOWN_TYPE.IncludeInTotal WHEN 1 THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalPayable, --NetPremium IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'NET' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS NetPremium, --IPT IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Premium_Section_ID, 1, 3) = 'TAX' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS IPT, --Fee IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'FEE' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS Fee, --TotalCommission IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID, 4, 4) = 'COMM' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalCommission FROM ACCOUNTS_CLIENT_TRAN_LINK INNER JOIN ACCOUNTS_TRANSACTION ON ACCOUNTS_CLIENT_TRAN_LINK.TRANSACTION_ID = ACCOUNTS_TRANSACTION.TRANSACTION_ID INNER JOIN ACCOUNTS_TRAN_BREAKDOWN ON ACCOUNTS_TRANSACTION.TRANSACTION_ID = ACCOUNTS_TRAN_BREAKDOWN.TRANSACTION_ID INNER JOIN LIST_TRAN_BREAKDOWN_TYPE ON ACCOUNTS_TRAN_BREAKDOWN.TRAN_BREAKDOWN_TYPE_ID = LIST_TRAN_BREAKDOWN_TYPE.TRAN_BREAKDOWN_TYPE_ID INNER JOIN CUSTOMER_POLICY_DETAILS ON CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_ID AND CUSTOMER_POLICY_DETAILS.HISTORY_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_HISTORY_ID INNER JOIN SYSTEM_INSURER ON CUSTOMER_POLICY_DETAILS.INSURER_ID = SYSTEM_INSURER.INSURER_ID INNER JOIN SYSTEM_SCHEME_NAME ON CUSTOMER_POLICY_DETAILS.SCHEMETABLE_ID = SYSTEM_SCHEME_NAME.SCHEMETABLE_ID INNER JOIN CUSTOMER_INSURED_PARTY ON ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_HISTORY_ID = CUSTOMER_INSURED_PARTY.HISTORY_ID AND ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_ID = CUSTOMER_INSURED_PARTY.INSURED_PARTY_ID WHERE CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE = '2007-08-17' AND ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID <> 'PAY' GROUP BY CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, ACCOUNTS_TRANSACTION.Transaction_ID, CUSTOMER_POLICY_DETAILS.POLICYNUMBER Add on View 1 CREATE VIEW TOPCARDPA AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'TRPCAE01' Add on View 2 CREATE VIEW TOPCARDRESC AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'HICRESC01' Join Result Script SELECT TOPCARD.AUTHORISATIONUSER, TOPCARD.AUTHORISATIONDATE, TOPCARD.TRANSACTION_CODE_ID, TOPCARD.SURNAME, TOPCARD.INSURER_DEBUG, TOPCARD.SCHEMENAME, TOPCARD.POLICYNUMBER, TOPCARD.TotalPayable, TOPCARD.NetPremium, TOPCARD.IPT, TOPCARD.Fee, TOPCARD.TotalCommission, TOPCARDPA.SELECTED, TOPCARDRESC.SELECTED FROM dbo.TOPCARD TOPCARD INNER JOIN dbo.TOPCARDPA TOPCARDPA ON TOPCARD.POLICY_DETAILS_ID = TOPCARDPA.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDPA.HISTORY_ID INNER JOIN dbo.TOPCARDRESC TOPCARDRESC ON TOPCARD.POLICY_DETAILS_ID = TOPCARDRESC.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDRESC.HISTORY_ID I have included all the scripts I have used, as others may find them useful, in addition to anyone that is able to provide me with some assistance. Thanks in advance for for the help.
View Replies !
Reference Tables Or Lookup Tables
I have facing a design problem and unable to justify which design to choose for my data model. Usually, what we have is like data tables and reference tables to store data in those data tables. My database has tables with 20-30 columns in them. And most of them (though not all of them), stores data from some reference tables. Meaning each column has an associated reference table where it stores possible list of values for that particular column. Sort of data domain for that column. FYI, My database is related to medical field. For example, A table that has a varchar(40) column called "Differentiation". It can only store values from following list: - Undifferentiated - Moderate - Poor - Poor - Moderate - Moderate - well Now, to implement this, simple solution would be to have a reference table where I can store all these possible values...And then have just a reference of each data item into my "Differentiation" column in the table. This is simplest and probably the best solution for such thing and i can also have referential integrity implemented for this. But now if we look at the bigger picture, my database is growing and I have about 80 tables which I need to create where most of the columns will have different reference tables like I mentioned above. Approximate number of reference tables is 300 tables. All the reference table will have same structure, with different values for different columns. Now, what seems to me is, because the table structure is same for every column, rather than having 300 different tables, I can only have 2 tables, where I can put all these reference values into these 2 tables. Like, Table 1 : This table can have name of the reference table like "differentiationlist" etc. Table 2: It has reference to the reference table list in Table 1 discussed above and all the values that are part of that reference table can go in this table with its reference. But problem with this is, because all the reference tables are in these two tables, I don't know how to implement referential integrity in this design. Does anyone have any idea or solution for situation like this? Thanks, Ujjaval
View Replies !
How To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View Replies !
SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are: 1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup. 1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types. 1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately. I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that. Thanks in advance for any help/suggestions, Bob
View Replies !
Revoke Perms On System Tables, Merge System Tables
I have recently set up Merge Replication and it puts alot of extra system tables in the database. When I connect through an ODBC using Access 97 I don't want non-admin users to see these tables (i.e., developers). If I revoke select on say INFORMATION_SCHEMA tables, dbo.sys... tables and dbo.Merge... tables from public, will replication be affected?
View Replies !
Remote Tables Are Not Updatable. Updatable Keyset-driven Cursors On Remote Tables Require A Transaction With The REPEATABLE_READ
Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed. Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance. DT
View Replies !
When I Want To Get A Value By 3 Tables, How To Join 3 Tables?
I'm doing DTS and need to write a SQL Query for what data have to move and what data have to deleted I have many tables need to difference query. this one is one of tables Table-Account0 column EAIndex �BillerCode�ChainCode�PostDate�PostAmount�BalanceDate�BillBegDate�BillEndDate�BillCount�ChAmount Table-Biller CompanyCode�BilerCode�BillerName�UniformNo�BusCode�AcctNo�BillerInfoList Table-DataBackup column CompanyCode�Keepmonth when I'm doing SQL DTS, in Table Account0 , I need to select the data which BalanceDate is < today's Date-6 --------------------------------------------------------------------------------------------------------------------- my sql query is below . but it didn't works , I must did somehing wrong with this query pleae point me to correct it SELECT ZT_EAccount0.*, ZT_DataBackup.KeepMonthFROM ZT_EAccount0 INNER JOIN ZT_Biller ON ZT_EAccount0.BillerCode = ZT_Biller.BillerCode CROSS JOIN ZT_DataBackup on ZT_EAccount0.BalanceDate < getdate()-6 ----------------------------------------------------------------------------------------------------------------------- thank you very much
View Replies !
T-SQL: Tables Vs. Temp Tables
I was wondering if there is any performance difference between creating an actual table in a DB and inserting records in a Stored Procedure vs. creating a temp table in the Stored Procedure (i.e. CREATE TABLE #MyTempTable .....) and inserting records.I need to create one of those massive "everything in the database, hundreds of pages ", overview reports, which consists of about 40 elements of caclulated data per product. It will be three levels deep:Generic Example:1. Calculated data elements for Cars.2. Calculated data elements for all of a Car's Parts.3. Calculated data elements for all of a Car's Part's Components.Example Report Format:Product Curr 12 Months Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD PrevYtd %Change--------------------------------------------------------------------------------------------------------------------------------------------------Car1 Element1 Element2 Element3Car Part1 Element1 Element2Car Part2 Element1 Element2Car Part3 Element1 Element2Car PartN Element1 Element2 Car Part Element1 Car Part Element2 Car Part Element3 Car Part ElementN------------------------------------------Car2 Element1 Element2 Element3... etc As of now, I completed part 1 above, and the SP takes about 10 minutes to run. It is using a T-SQL created temp table (#MyTempTable). I was wondering, before I recode anything, if anyone knows if my SP will run faster if I actually create the table in the DB instead? I figure that once I complete parts 2 and 3 above, the SP could take 45 minutes to run.
View Replies !
Temp Tables Vs Tables
Hi, Are there any significant performance benefit if we avoid temp tables? Are the temp tables bad for good performance of queries? Is it better to create table ,then use it after that drop it ,rather then using temp tables? Any comments would be greatly appreciated.
View Replies !
Getting Value In Tables And Insert To Other Tables
I want to compare two tables. if the data is existed to the other table i will insert it to other table if not exist it will load also to diffrent table. Example: if my table was Tbl_StudentID and Tbl_FacultyID i want to insert in tbl_Grade if the data in Tbl_StudentID is exist or present in Tbl_FacultyID . And if the Tbl_StudentId is not exist in Tbl_FacultyID the data will move to Tbl_SupervisorID. Please Help..
View Replies !
Rollback Will Drop Created Tables And Drop Created Tables In Transaction..?
Hi folks. Here i have small problem in transactions.I don't know how it is happaning. Up to my knowldge if you start a transaction in side the transaction if you have DML statements Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5 It is rolling back all the commands including DDL witch it shouldn't please let me know on that If any one can help this please tell me ...........Please............ For Example begin transaction t1 create table t1 drop table t2 then execute bellow statements select * from t1 this query gives you table with out data select * from t2 you will recieve an error that there is no object but if you rollback T1 willn't be there in the database droped table t2 will come back please explain how it can happand..................... Email Address: myself@ramkistuff.8m.com
View Replies !
Does My DB Has Tables?
hello how could i determine (Programmatically) if a data base contains tables or not? how could i (programmatically) get the details of tables in a database? SQL server 2005 ghassan
View Replies !
Many DB's Or Many Tables
We are designing an application where we will be providing a service to multiple users (customers) where we will have to manage data for the service in a database. The question is what is the best for performance (and maybe security) in the long run. A. Create a new set of tables for each new user all within the same database. Ex: DB 1: User1_Table1, User1_Table2, etc. and User2_Table1, User2_Table2, etc. or B. Create a new database on the fly for each new user. Ex: DB 1: Table1, Table 2 and DB2: Table 1, Table 2. Thanks in advance
View Replies !
I Can Not See My Tables !
Hello, I am using " SqlDataSource " and I can not see Talbles of memberships ! I can not only see View or see manule tables !!! for this when I am using any tool I can not use edit or add fetures :( Where is problem ?
View Replies !
Many Db's / Few Tables Vs. Few Db's / Many Tables
Our infrastructure group had a consultant come in for an eval. Currently, one development group who support many different groups create a database per group (sales, etc). The consultant said that this was inefficient, and that we should combine all of the databases into one database. The Infrastructure group now wants us to use one db for everything. Does anyone have any thoughts on the advantages / disadvantages of these approaches.
View Replies !
Two Tables, Same Name
After attaching a database i've got two tables named "dtproperties". Both tables are system tables, one of them having 'dbo' as owner, the other one having the user 'xxx' as owner. I need to delete the user 'xxx' as a user from the database, but i can't do that as long as it owns the 'dtproperties' table. I have tried the running sp_configure to set the "allow updates" var to 1, but that didn't help. I noticed a weird thing, when i try to change any settings under the properties for the database, all the settings are reverted when i click OK, no error dialog. I'm using the sa login so permissions should be ok. Any advice?
View Replies !
Sql 2 Tables Ask
I have 2 tables (names A B)i need (In SQL stetments) to search from table A if rows in table Bwith this conditions:A.col1 = B.col1 AND A.col2 * (0.5) > B.col2 AND A.col3 + (0.5) >B.col3Best Regards,Yuval
View Replies !
SQL Ask 2 Tables
I have 2 tables (names A B)i need (In SQL stetments) to search from table A if rows in table Bwith this conditions:A.col1 containe 4 char.B.col1 containe 4 or more charneed all the records that A.col1 IN B.col1Best Regards,Yuval
View Replies !
SQL 2 Tables Ask
I need an IF statment at SQLI have 2 tables (names A B)i need (In SQL stetments) to search from table A if rows in table Bwith this conditions:if B.col1=1 or B.col1=6 then search all row (1=1)otherwise select A.col1=B.col1this select is part of a big select from A,B tablesBest RegardsYuval
View Replies !
Using Many To Many Tables
I'm having a problem writing the correct sql to bind togeather threetables. Here's the problem: I have a table for detail information onbooks that are in our catalog, and a table for authors. Unfortunatly, itis not a one to one relationship. Some books are written by severalauthors, hence, I've created an intermidiate table called book_to_authorwhere I list the isbn's for the books next to the author id's, so I maylist an isbn three times with three different author id's next to it, ifthree authors have contributed to it.Now I have to write a sql statement that will pull up the book detailinfo, such as the title, and the three authors associated with it.Assuming I have a table called authors, with an author name in it, onecalled book_to_authors, with the isbn and author_id, and a third calledbooks, with the title in it, how would I write the sql to join all theinfo where the isbn='0530725318?Thanks a million if you can help. I've been trying to write this allmorning, and can't get it to work.Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
SQL Tables
Hi AllI was wondering if there is a way to be able to tell when a tables data hasbeen changed (Record inserted updated or deleted), as i wish to be able totell this from an sql stored procedure if possibleThanks
View Replies !
Sys Tables
Dear Members What are basically sys tables and what they do basically and why they are by-default created when any database is created? Regards Praveen Kumar Pandey CoVisible Solutions [I] Pvt. Ltd. CRM and Knowledge Managemet Channel (http://www.covisible.com)
View Replies !
Most Used Tables?
Hi, Is there any way using Profiler, Perfmon, or a script to find how often individual tables and indexes are used? I want to break up a database with heavy read/write activity into filegroups, but I'm not familliar with the application yet and I don't know which tables are the most active. Thanks Charlie
View Replies !
2 Tables With The Same Name... Should I?
Hi all, I am debating if I should go ahead with my instincts which are telling me that I shouldn't name 2 tables with the same name... or if I should go ahead and name more then one table the same name as long as they are in different schema... something like (making stuff up here for sake of showing an example...) car.Reservations hotel.Reservations flights.Reservations or audit.TransactionTypes inventory.TransactionTypes sales.TransactionTypes etc... should I go this way or use something more traditional like dbo.SALES_TransactionTypes or dbo.SalesTransactionTypes?
View Replies !
Help With 2 Tables
I'm trying to post something up but it's timing out on me when I hit preview post...what is wrong? I included the DDL etc in code tags...still no joy. Please help!
View Replies !
Sys Tables
Hello, Ia am using SQL 6.5 as my SMS database. Using SQL Probe utility I have found that these following tables have a very low Reorganisation Status. syscolumns = 18% syscomments = 50% sysindexes = 21% sysobjects = 15% sysprocedures = 55% sysprotects = 20% workstation status = 23% All other tables in my Database are running at 95% or better. Question: Would using Clustered Indexes on the SYS Tables Help? If not what would help me? TIA, Terry
View Replies !
SQL Tables
I am trying to copy tables from SQL into a Kornshell environment. Can anyone tell me how to suppress duplicate values?
View Replies !
|
TRACKER
