I have a SQL database that has multiple tables I need to combine for an analytics presentation. The tables are PRODUCTS (contains product information and UPC), STORES (Store number, name and address), DATES (List of dates that data is published on), and DATA (quantity, by store, by UPC, by date). I am trying to combine these so that i have one table with a row for each UPC on each day at each store. Also, I would like to lookup values for year ago quantities at the same store for the same UPC. Is there a way to structure a query to provide all of this information? I know it will be a lot of rows, but I will filter by product prior to pulling the data.
I think the solution requires a subquery in the FROM portion of the statement, but I am not sure how to structure it. Here are the Fields:
Products.UPC, Data.UPC, Data.Store_Num, Stores.StoreNum, Data.StartDate, Dates.DateKey, and finally the reason we are here Data.Qty
--CREATE TEMP TABLE IF OBJECT_ID('tempdb..#TEMP_WK_STOCK') IS NOT NULL DROP TABLE #TEMP_WK_STOCK CREATE TABLE [#TEMP_WK_STOCK]( [WMNUMBER] [int] NOT NULL, [STOCKID] [int] NOT NULL)
[Code] ...
returns just 2 rows and misses the fact that the wmNumbers stop and start a few diffrent times. I cant see how to set up the query to bring back the 6 rows i would be expecting it this case, without going to a cursor which i really don't want to.
I deleted some records out of an entity, I'd like to keep the Codes as contiguous and incremental, meaning no breaks between the code numbers.I created a business rule and applied it but codes remain the same.
I used the "Default to a generated Value" action, then selected the Code attrib. --Saved.
Then back to the Entity, I applied business rules. But nothing seemed to have happened. As there was no change in codes.
Hi, I need to convert from mssqlto Postgres and I need to export all MS-SQL table data to a CSV or TXT file (one file per table)
Presumably, all data per row (of a table) must be in one line. Then when you copy to another database, a new line of data means a new row in the table.
However, MS SQL is exporting a large varchar text field as multiple lines. The data itself is many lines, so exporting it causes the data for one row to fall onto many lines.
My question: How do I escape new lines? When MS SQL exports the data, I want to replace all NEW LINES / carriage returns by /n or by <br> tag (since the data will be for web use).
(pls note I am not actually handling the ms sql database, so any response would be greatly appreciated as I advise the person in charge of the mssql db accordingly).
Does anyone know a wayto compress data between two database connections over "low bandwidth" lines in order to speedup datatransfer? Used connections are Oracle<->SQL2000 and SQL2000<->SQL2000.
Hi, I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below .
I have a Site 2509CRUZ2 and SB1931 that have Multiple Contract ID's assigned to them in the table. Which are in 12 font below. I need to create a script to take these duplicates and place them in their own view but not delete them from the table.
I'm trying to produce a chart that has both actual data values from a database and matching "line of best fit" plots on the one chart. I have 4 data series, 2 of the actual data values and 2 that represent the values for the "line of best fit". What I want to do is:
Plot the actual values just as data points (joining them with lines in meaningless) and Plot the "line of best fit" values as a line.
When I edit the data series on the scatter chart, I can see the "Plot data as line" option but it is "greyed" out.
Have I missed something really simple here or is this not possible. I'm using RS2005
Hi,How can I treat multiple, contiguous records as one record?i.e.,tblData(a varchar,t datetime)select * from tblDataorder by a,tthen, for row n, if a(n) = a(n+1) and t(n) <= t(n+1) (seconds) thentreat record n as the same as n+1, discard record n+1 and continue,else start again with the next row.Anyone got an idea? Just writing this I'm thinking I'm going to haveto venture into cursor territory (new for me).TIAChandy
On an SQL2000 Enterprise Edition with SP3 and 3.2 GB memory, I have restore around 700 databases from backup .bak files with no errors. Now, while in the process to restore (proc to restore with move statement) another 150 databases, following errors are encountered: ------------------------- There is insufficient system memory to run this query.
WARNING: failed to reserve contiguous memory of size = 196608.
Query Memory Manager: Grants=0 Waiting=0 Maximum=150384 Available=150384 "Buffer Distribution: stolen=6699 Free=280 Procedures=4821 Inram=0 Dirty=1221 Kept=0 I/O=0, Latched=1169, Other=194482" ------------------------- Is ther memory leaking and/or hardware related? the sql used only 1.8 gb mem and the whole system is using 2.4 gb mem. After reboot, same error appears in the middle of restoring 150 databases.
We are using a table that may give 1 to and unknown number of data elements (ie. years) . How can we break this to show only three years in each row. Since we don't know the number years we really won't know the number of rows needed. Years are stored in their own table by line.
car make year1 year2 year3 A volare 1995 1996 1997 a volare 1997 1998 1999 b toyat 1965 1966 1968
We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.
What tools has everyone used for cleaning name and address data(including identifying not-immediately-obvious duplicates) inconnection with a CRM project or the Customer dimension of a datawarehouse? What did you like/dislike about the tool you used? Howcustomizable was the tool you used?
Hello, Anybody has came to solution across this error? We are getting this error repeatedly. I appreciate if anybody give suitable solution for this. Thanks, Ravi
We see the following message in our error log. WARNING: Clearing procedure cache to free contiguous memory. It is accomonpanied by fairly intensive CPU activity. We get this roughly once per working day.
Anyone have any idea why, and what we can do to stop this?
Hi all, I'd like to link a SQL Server database / table to Microsoft Exchange Server E-Mail-Address data, for an Employee Directory project.
I've read a little about "Web Store", a little about ExOLEDB, but I've come across nothing along practical lines. I.e., I still don't know where to begin!
For example the "Web Store" should be created on the SQL server, but how do you create it? Any insights will be greatly appreciated.
A very basic MDX question. Using the following from AdventureWorks as an example:
Code Snippet WITH MEMBER [Measures].[Big Cities] AS ( [Geography].[City].&[New York]&[NY] , [Measures].[Internet Order Count] ) SELECT [Measures].[Big Cities] ON COLUMNS , [Customer].[Total Children].Children ON ROWS FROM [Adventure Works]
How would I re-write the [Measures].[Big Cities] calculated member so it included both [Geography].[City].&[New York]&[NY] and [Geography].[City].&[Los Angeles]&[CA]? Assuming there is no logical connection between these members, and that they are non-contiguous. Also, in my real example, I cannot move [Measures].[Internet Order Count] to the WHERE clause, it must remain within the calculated member.
Hey, take a look at the below. I need to sum the real balance of a line with the credit with the line above, or, n + 1. I don't wanna to have to walk across the lines to do the sum. Any idea of how to do that in a easier and better way? I tried using T-SQL, but I couldn't figure out how to do a reference to the next line on the SQL without moving the cursor.
Date Reference Description Debit Credit Real Balance Bank Balance
I'm using SQL mail to send an email from a stored procedure which works fine. However I need a line break in the middle of this email which I am struggling to achieve. I've tried adding char(13)+char(10) to the middle of the email body string but this does not seem to do anything. Any ideas?
I am using SQL Server 2005 Reporting Services and all my reports preview with many more pages, as the preview will show Page 1 of 20, but if I print the report I get say 12 pages. The reports are always correct, but the preview show less data per page? Is there a way to get the preview to be what is going to print out?
This occurs in MS Visual Studio 2005 also as well as from the web. On a drill down report I get on some reports only 10 line when the print out will have 30 lines perpage
I am generating hundreds of lines of sql with a tool and wish to execute is in runtime. The sql consists of table creation, procedure creation, aswell as inserts and updates. When i try with SqlDataAdapter I get an error. Does anyone have a solution on what I can do?
hi im making a page where i want a single line at a time to be pulled from my MS Database, basically at the moment i have a list of questions, but the page is displaying all the questions from my database, i only want it to pull out 1 and then if the user clicks the true button then it goes to another page and displays another question?
I take some data from a textbox and then dump it in to SQL.. and then try to show it on the page but the new lines in code dont show in HTML.I grab the data via a <%#Eval(xxx>%>so doing a replace isnt really an option there...any other ideas?
Assuming I have a line, is there a function I can call to create a parallel line at a given distance away.i.e - with the below I would want to draw a parallel line to the one output.
We have a very long and complicated SQL script which we run to upgradea version of our software from old to new. It works great in QueryAnalyzer, but when run through osql it takes errors on lines that arevery long and (I think) stops reading after a certain amount ofcharacters. I've searched the net but haven't found anyone mentioningthis before. I have tried the -w 5000 parm to no avail.Any suggestions?
I used to be able to enter new lines into the result pane cell for text(and varchar) data in Enterprise Manager, but now that I am using SQL2005 Management Studio, this feature is gone.Is there any way to do this?Also, copying to/from excel chops off part of the text in a cell and isvery infuriating.Any help would be appreciated.Dan
Hi, The chart is set to simple line I can not get my SSRS chart to show just the dots, instead it is joining the points together and show the points joining together and therefor appear as lines.. even tried setting the chart to simple scatter but then nothing appears on graph. Any thoughts please?
