Determine Table Load Order
Nov 23, 2004Does anyone have a script that analyzes primary and foreign key relationships of tables and produces a suggested load order based upon dependancies?
Thanks,
Fred.
ADVERTISEMENT
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
Jan 7, 2007Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
Thank you, Mark
Determine Next Available Order ID
Sep 21, 2006I am trying to determine the next available order id using the method below. It works provided the table has a record in it. If it doesn't I get the error "Input string was not in a correct format." I am certain that it is because the query is returning a value of NULL. How can get around that or check for the NULL value?' Establish data connection...Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))'Determine order id number...Dim order_id As IntegerDim strSQL As StringstrSQL = "Select MAX(order_id) from mkt_order"Dim sqlCmd As New SqlCommand(strSQL, sqlConn)Dim sqlDA As New SqlDataAdapter(sqlCmd)Dim sqlDS As New DataSetsqlDA.Fill(sqlDS, "item")If sqlDS.Tables(0).Rows.Count <> 0 Thenorder_id = Convert.ToInt32(sqlDS.Tables(0).Rows(0)(0).ToString()) + 1Elseorder_id = 1End If
View 1 Replies View RelatedDetermine Next Order Id
May 17, 2007I'm working on a sproc that determines the next order id for a specified customer. The table has
custid int,
ordernum varchar(10)
Data is:
1000, 1000-001
1000, 1000-002
1001, 1001-001
1000, 1000-003
I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet).
I honestly don't know where to begin.
Can someone please help?
Using An Array To Determine Query Order??
Oct 17, 2006Hi.
I have a VB.NET function that returns an array of Integers.
Say, FunArray = [2, 3, 5, 8, 6, 23, 1, 10, 20 , 4, 54]
One characteristic of the array is that no two numbers
repeat - it reflects the IDs of my Users table. And, that
it is not ascending or descending.
What I would like to know is how do I sort my query in
the order of the integers in this array? Ideally, I
would like to use ORDER BY for this query.
thanks in advance.
Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number
Aug 21, 2007I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
How To Load A Unicode File Into The Database In The Same Order As The File Order
Jul 23, 2005The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View 15 Replies View RelatedOrder By Is Slowing Page Load
Mar 14, 2007hi guys,
the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
thanks
M
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim oRS,oConn,myOrder,strSQL
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
'next, a couple of test lines to prevent timeout (seems to have no effect)
oConn.CommandTimeout = 0
Server.ScriptTimeout = 0
Set strOrder = Request.QueryString("Order")
oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATA BASE=name"
oConn.Open
%>
</head>
<body>
<%
strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
make = oRS("make")
model = oRS("model")
price = oRS("price")
%>
<%=make%> <%=model%> <%=price%><BR>
<%
oRS.MoveNext
loop
oRS.close
set oRS= nothing
oConn.close
set oConn=nothing
%>
</body>
</html>
SQL 2012 :: Load Files In Date Order In SSIS
Apr 10, 2014I have files which has date in file name and I want to load all files in sequential order.
Here I am giving you an example of my file name.
"NetworkActivity_869_403722_01-01-2014.log"
"NetworkActivity_869_403722_01-02-2014.log"
"NetworkActivity_869_403722_01-03-2014.log"
"NetworkActivity_869_403722_01-04-2014.log"
"NetworkActivity_869_403722_01-05-2014.log"
These are my files in that I want to load files sequentially means jan 1st, 2nd like this way.
Order Of Data Load And Index Creation / Move Indexes To Separate Filegroup?
Apr 15, 2015We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).
1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.
2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO
I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)
Placing Indexes on Filegroups:
[URL]
By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:
• Create nonclustered indexes on a filegroup other than the filegroup of the base table.
Move an Existing Index to a Different Filegroup:
[URL]
Limitations and Restrictions
• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?
May 19, 2015I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
Default Sort Order - Open Table - Select Without Order By
Mar 27, 2008Hi!
I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.
The table returns the data in the same order in SQL Manager "Open Table"
So I started to wonder what deterimins the sort order when there is no order by clause ?
I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.
Peace.
/P
Determine If Autoincrement Is Set In A Table, Form Asp.net
Nov 15, 2006I want to access sqlserver table properties from asp.net .How do i know that a table defined in sqlserver has autoincrement field or not.Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.
I am not being able identify this property from codes.Please help.thank you
Determine Last Access Of A Database Or Table
Dec 18, 2007Does anybody know of a way to determine the last date/time a table has been accessed (query/update)? I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table. I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience. Any info will help greatly! Thanks!
View 2 Replies View RelatedQuery To Determine Table Size
Mar 7, 2001Hi all,
I am looking for a query to determine the exact size of a large table.
Thanks
JJ
Determine Row Of Data In A Table Through SSIS
Jun 13, 2013I have a requirement based on 2 parts to determine a row of data in a table through SSIS :
a. Work out the LEG_SEQ_NBR of the VESSEL, VOYAGE and LEG already stored in table1.
To do this join table1 and join table2 through BL_ID column.
b. Work out the 2ndVESSEL, 2ndVOYAGE and 2ndLEG.
Once we have identified the LEG_SEQ_NBR of the VESSEL,VOYAGE, LEG already stored in table1 we need to add '1' to this value and then find that LEQ_SEQ_NBR in table2.
The DDL of table1 and table2 along with the test data are as below:
CREATE TABLE [dbo].[table1](
[BL_ID] [decimal](10, 0) NOT NULL,
[VESSEL] [nvarchar](10) NULL,
[VOYAGE] [nvarchar](12) NULL,
[LEG] [nchar](3) NULL,
[Code] ....
For Example:
BLNO = 21 and BL_ID = 1
Current Vessel/Voyage/Leg in table1 = CEUR 032E E
LEG_SEQ_NBR of this Vessel/Voyage/Leg in table1 = '1'
Therefore if we add '1' to this value we'd be looking for LEG_SEQ_NBR '2' as the 2ndVESSEL,2ndVOYAGE,2ndLEG.
In this case that would = MARB 794S S
How this can be worked out through an SSIS job ?
Determine That ID Is Unique In Customer Table
Oct 12, 2014I would need to rewrite SQL code to determine that id is unique in the Customer table.
My two tables are:
CREATE TABLE CUSTOMER(
[CUSTNO] varchar(5) NOT NULL,
[ID] CHAR(9) NOT NULL,
[NAME] VARCHAR(128) NOT NULL,
[ADDRESS] VARCHAR(128) NOT NULL,
[DATEOFBIRTH] DATE NOT NULL,
[Code] ....
How To Determine Length Of Table Row In Sql 2005
Sep 14, 2006How, other than by doing the addition on my own, can I detemrine the length of a row in a table?
TIA,
barkingdog
Determine Current Table Of Trigger
May 6, 2008Good Morning
Is There anyway to determine the table you are referencing in a trigger's inserted / deleted tables?
i.e would this be possible
print @Table_Name + " was Updated"
Where @Table_Name is the table on which the trigger is created
(bad example I know but you get the idea)
Any input will be appreciated.
How To: Determine If A Table Exists Using SQL Or TSQL.
Feb 4, 2008I need a SQL or TSQL command (not a stored procedure) that will determine if a table exists (TBL_PARAMETERS). The command needs to return a 1 if the table exists or 0 if it dose not exist.
Thanks!
Sean
SQL 2012 :: Determine If A Certain Table Has Been Accessed In The Past?
Nov 4, 2015code that I can use to determine if a certain table has been accessed in the past?
View 6 Replies View RelatedHow To Determine Table And Column Names In A Trigger?
May 5, 2008Hi all,
I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already.
I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.
Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?
Thanks,
-Mike
SP To Determine Layout, Population And Sample Data In Table(s)
Nov 1, 2007Hi! I am new to SQL Server... looking for some veteran assistance.
"Data Integrity Report"
I need a Stored Procedure that takes a table name as a parameter and returns a cursor suitable as a data source for a pre-built Report Services report (I guess Report Services would call the SP?).
The cursor/report needs to have the following columns:
Ordinal_Position (I.E. Column number)
Column_Name
Number Of Blank Rows (how many missing values for this column in this table)
Difference (Between total rowcount and population of this column)
Data_Type
Column_Length (either Character_Maximum_Length or the numeric widths rolled up with COALESCE?)
Sample Data (The contents of the "first" row in the table, based on a TOP(1) and ORDER BY xxx)
The report should look like this (for a table with 100 rows):
Col Num Col Name # Blanks Difference Data Type Col Length Sample Data
1 Name 12 88 varchar 30 Sally Smith
2 Address 34 66 varchar 45 123 Main St Apt 45
3 Acct_ID 0 100 varchar 4 AB12345
Using the "Information_Schema.Columns" I can get everything I need except for #3 (blanks count) and #7 (Sample data).
Is it possible to do this as 1 query, with a CTE or APPLY or something, or do I need to do a table variable based on the Information_Schema and then use dynamic SQL and row-by-row COUNT(*) for each column? And the same for the Sample Data.
Sorry for the long post, and thanks in advance!
John
Transact SQL :: Retrieve Currently Created Date From Master Table To Load Into Parent And Child Table
May 12, 2015In Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
Integration Services :: Load Incremental Data Into Fact Table When Source Table Not Have Timestamp And Unique Key
Sep 24, 2015I have a transaction table having about 40 crore rows in source. It don't have timestamp and unique key columns. It have only Bill_month and Bill_Year columns. Actually for loading this table into staging I have added a new datetime column by adding default bill_date as 01. Then
* First we delete last 3 month data from staging tables.
* Get last 3 months data from source table.
* Load that 3 months data from source to staging table.
We do this because we only get update for last three months data. Now I have to include this transaction table as Fact table in DW. What will be the best practice for loading the fact table by picking data form staging table. Also we have to look up with dimensions for Foreign Keys.
* Should I implement the same method of deleting last 3 months records and loading them again.
SQL Server 2012 :: Determine Read / Write Frequency On Database Table
Oct 6, 2015How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.
View 2 Replies View RelatedAdd Calculated Field In Order Table Based On Price Column In Product Table
Nov 18, 2014I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
Table Load
Mar 14, 2006After working on this with no luck for a couple of days I am sick of pounding my head against a wall! I need help!! I am trying to load 3 flat files into one table and I am not sure how to do it using SSIS. I just need some guidence as to where to go. I can load 1 flat file into the table but 3 is the issue and combining the data so it is loaded in sequential rows. I have had it load, but it has been in three pieces one after the other. I am just not sure where to go and I really need some help! Thank you for any assistance or advice!
View 5 Replies View RelatedHow To Load A Table Into Memory ?
Nov 7, 2006How can I load a table into memory in SQL Server 2000 or SQL Server 2005 ?
How can I keep the table always in memory in SQL Server 2005 ?
with all my thanks
Load A Table From A File
Feb 15, 2000I want to load a table from a file.
My file has a fixed length(fixed block) and have the same
fields of the table.
I need the right sintaxis, because The next with errors:
"load from file1 insert into table1"
ANY ADVICE will be greatly apreciatted because I'm not an
expert in databases. Thank you veru much.
Nauj
Single Table Load
Mar 15, 1999I thought I had posted this question already, but didn't see it in
the list. I apologize if this is a repost.
I am running SQL Server 6.5 SP 4.
I am attempting to load a single table from backup, but continually get
the error about schemas not matching. Interestingly, it comes back with
a status 4, and not the status 3 indicating a mismatch on Ansi_Padding.
The statement I am using is
load table demhist from internal_tape with file=5,nounload
I have tried creating the table from scratch and using select * into...
I have tried both above with both settings of Ansi Padding. The table
I am trying to load contaings char columns which allow nulls. I have
experimented with loading 2 other tables. One of the other tables loads
and one doesn't. The one which does not load also has char columns which
allow nulls, while the one that does load does not have char columns
which allow nulls.
Is it a known problem or limitation on the table load that it cannot reload
tables that contain char columns which allow nulls?
Table Load Problem
Feb 7, 2008I am trying to load a table via SSIS from a text file (becdldep), which I have defined in the package as fixed width. The fourth column in my table is defined as decimal (6, 4) and the input is the six digits 246128. I get the following error message:
Error: 0xC020901C at becdldep to Securities, Securities [1006]:
There was an error with input column "Column 4" (1313) on input
"OLE DB Destination Input" (1019). The column status returned was:
"Conversion failed because the data value overflowed the specified type.".
When I define the column as float the value is accepted without an error but it is seen as 246128 rather than 24.6128. I could update the column with a divide by 1,000 but is there an easier solution?
Auto Create && Load In SQL Table
Apr 19, 2006Hi does anyone know how to create a sql table and then import a list by just clicking on a button to call a procedure?CREATE TABLE clients(ClientID VARCHAR(5), ClientName VARCHAR(30), PRIMARY KEY (ClientID));LOAD DATA LOCAL INFILE 'C:/client.csv' INTO TABLE clientsLINES TERMINATED BY '
';