temptable - Page 2 - Developer IT

Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

- by Jay

Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder DESC), Address2 = (SELECT TOP 1 [Address] FROM (SELECT TOP 2 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder DESC) FROM User u In this scenario, users have multiple address definitions, with an integer field specifying the preferred order. "Address2" (the second preferred address) attempts to take the top two preferred addresses, order them descending, then take the top one from the result. You might say, just use a subquery which does a SELECT for the record with "2" in the Order field, but the Order values are not contiguous. How can this be rewritten to conform to SQL 2000's limitations? Very much appreciated.

Read the article

SMO missing dll on a clients

- by Dale

I've created an app that connects remotely to SQL Server 2008. SQL connections work and all traditional oCommand.ExecuteNonQuery(), work great! But my SMO class using server.ConnectionContext.ExecuteNonQuery(scriptfile); ERROR: missing batch parsing.dll . I can't install these independent utils on a client machines, and then take them all off when done: as suggested by: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=228de03f-3b5a-428a-923f-58a033d316e1 Since my bulk-inserts are large memory hogs containing complete tables, I wrote the tables to a temptable.sql files and used SQLCMD Util and later switch too SMO and I have the same problem. Neither of these can be leveraged on the client's PCs . Any suggestion? thanks :-)

Read the article

SQL Temp Tables & Replication

- by Refracted Paladin

I have had an issue with our replication process and would like to salvage some data. I have a process in place where I will connect to each subscriber before flagging them for reinitialization and I will run the below to pull any data they may have entered in during the "dark time". I am pretty sure this will work in a vanilla palace. What I am unsure of is whether the Global Temporary Table will persist through DB Replication. To be clear, I am not trying to Replicate the TempTable, I just want to make sure it will still exist at the local DB after the Replication so I may run the INSERT from it. Thoughts? USE MemberCenteredPlan -- Select Data from tblPLan SELECT * INTO ##MyPlan FROM tblPlan WHERE PlanID = 407869 --------------------------- -- Run Replication Process --------------------------- -- Insert Plan back into DB INSERT INTO tblPlan SELECT * FROM ##MyPlan WHERE PlanID = 407869 -- Drop Global Temp Table DROP TABLE ##MyPlan --------------------------- -- Run Replication Process ---------------------------

Read the article

Table Variables in SSIS

- by aceinthehole

In one SQL Task can I create a table variable DELCARE @TableVar TABLE (...) Then in another SQL Task or DataSource destination and select or insert into the table variable? The other option I have considered is using a Temp Table. CREATE TABLE #TempTable (...) I would prefer to use Table Variable so that it remains in memory. But can use temp table if it is not possible to use table variable. Also I cannot use the record set destination as I need to preform straight SQL tasks on it later on.

Read the article

TextColor not showing when row backgroundcolor set

- by RuAware

if my row is colored red the text wont set to green and bold italic. When debugging I can see it telling the TextView to set each textViews setting. Thanks TableRow row = new TableRow(getContext()); row.setLayoutParams(new TableRow.LayoutParams(LayoutParams.MATCH_PARENT, LayoutParams.WRAP_CONTENT)); String[] items = list.get(l).split(":"); for(int i=0; i < items.length; i++){ //see if i need to colour row if(items[i].startsWith("colorme_") == true) { if (items[i].substring(8).equals("red") == true) { row.setBackgroundColor(Color.RED); } } else { //create a temp textview then add to row TextView tempTV = new TextView(getContext()); tempTV.setText(items[i].toString()); //test against correct answers and colour text view green if correct if (correctAnswers != null && correctAnswers.size() > i) { if (correctAnswers.get(i).equals(items[i].toString()) == true) { tempTV.setTextColor(Color.GREEN); tempTV.setTypeface(null, Typeface.BOLD_ITALIC); } } row.addView(tempTV,lpTextView); } } //add the row tempTable.addView(row);

Developer IT