I have been working on a pretty ugly stored procedure recently, while debugging I added a char(10) to the end of each line of the SQL query so I could copy it to query analyzer(QA) and debug the SQL syntax output from of the stored procedure.
It had no effect on the stored procedure working, but when I copied the query to QA it got the error below, so I removed them all and added them in one line at a time to find the problem.
--Server: Msg 170, Level 15, State 1, Line 3
--Line 3: Incorrect syntax near ','.
Below are the 2 querys, the only difference is the Char(10) between Amt6 and Amt7!
On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far. SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3], [column4], [column5] FROM [table] WHERE [column4] = 'condition4' AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate ", Conn) ; cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text)); cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text)); cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text)); I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help.
I’m okay with simple queries but as I’m no expert and have failed to find perhaps the correct wording to describe this method, if at all possible to do, so I have come to ask here.
What I would like to do is take a column from a query and then break down that column into separate results.
So the full query results: 36,18/09/2007 10:00:00,NULL,000102000304,NULL
The column I would like to brake down is (Unique Reference Number): 000102000304
And I would like to break it down to get the last 2 parts (0003 and 04): 0001 | 02 | 0003 | 04
Is this possible to do? If so where should I be looking or what should I be looking at?
Need query to expand the count from a month to the entire year in 2013 .Below is my query where I got the values for a month in 2013 ,How do I expand on this query so that it generates for the entire 2013
Query SELECT RD.RPTDESC,Count(SR.RPTDESC) AS ReportCount,sum(SR.Hrs) as ProdHours,Sum(SR.Mins) as ProdMins, (sum(SR.Hrs)*60+ Sum(SR.Mins)) as TotalProdTime, (sum(SR.Hrs)*60+ Sum(SR.Mins)/Count(SR.RPTDESC)) as AverageProdTime,sum(SR.TriageHrs) as TriageHours,Sum(SR.TriageMins) as TriageMins, (sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)) as TotalTriageTime,(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)/Count(SR.RPTDESC)) as AverageTriageTime
Also when I am running for months individually there are certain months where for certain reportids there is no data returned,I would like to populate all zeroes for that row for example report id 306 in February and how to get decimal values in average fields.
Hi GuysI don't want to keep asking for your help all the time on each individualquery, so could you please help me to break the myths on the following:1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.ACCOUNTS includes all of the usual details for customers and suppliers, egthe account code (as ACCOUNTID), the account name (as NAME), etc.ACCOUNTBUDGET basically holds a transaction line for each month and it'scorresponding year to store the turnover for that month, eg one row containsACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.Now a lot of the SQL 6.5 tables that I deal with are in this vein and theusual query is that I want to list all of the ACCOUNTIDs and NAMEs thatexist in the ACCOUNTS table and then show for example what their TURNOVER isfor a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGETtable.Now if I do a quick query using MS Query all I get are rows that haverelated values in both the ACCOUNTS and ACCOUNTSBUDGET table when I havespecified say a certain PERIOD and YEAR.The main point of my current reporting problem is that I want to show allthe ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for aparticular PERIOD and YEAR.I'm positive that I have to create a 2 step query/join, but I don't know howto do it. What is the method? People in this NG, can rattle one up inseconds, but I just don't see the logic. Can you help me with this queryand let me know how you manage to fathom it.2) Are there any good web sites that explain in kiddie form how to do thissort of thing?I really appreciate your help on this.RegardsLaphan
I have a text field in a table that contains number along with chars.Is there a way i can write a query to show all the fields that containsjust Numbers or Char in a field??TBALE ExampleCOL1 : COL2(nvarchar)---------------------------100 345G01200 123456789300 GQ9220
I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.
I am not using CAST in the design grid. Is this even possible?
I am modifying the underlying SQL code. Here is the line that is giving me trouble:
CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequenceâ€
( @CompanyID NVARCHAR(36), @DivisionID NVARCHAR(36), @DepartmentID NVARCHAR(36), @ItemID NVARCHAR(36), @OrderNo NVARCHAR(36), @LineNo NVARCHAR(36), @TAllotedQty Numeric, @EmployeeID NVARCHAR(36), @Trndate datetime ) AS BEGIN By default iam passing 12 char itemid as parameter...
Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID END
Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same) IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID END
Does anyone know why this statement would fail? I have created my own assembly which is on the server and when i run the mdx query call myassembly.mystoredproc() it returns data, but now when I use call myassembly.mystoredproc() it returns an error.
using an mdx query this works fine call AsmTest.Asm.Analysis.TestClass.NameMe()
When using openquery i get an error select * from openquery(NV, 'call AsmTest.Asm.Analysis.TestClass.NameMe()' )
OLE DB provider "MSOLAP.3" for linked server "NV" returned message "Prepare is not safe during execution of the NameMe stored procedure.". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "call AsmTest.Asm.Analysis.TestClass.NameMe()" for execution against OLE DB provider "MSOLAP.3" for linked server "NV".
linked server definition
USE master GO
/* Add new linked server */ EXEC sp_addlinkedserver @server='NV', -- local SQL name given to the linked server @srvproduct='', -- not used @provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version) @datasrc='nvsifwfp', -- analysis server name (machine name) @catalog='ARTSDW' -- default catalog/database
I've got a Table that has over 500,000 row in it. Now I need to convert the whole thing into Excel to import into another application. So I need to break the table into 10 different tables. How can I do that?
I hope I can get this across clearly.I have a table that needs to be broken into 3 tables.Col1 Col2 Col3 Col4 Col5 Col6 Col7Col1 and Col2 need to go into LookupTable1Col3 and Col4 into LookupTable2If Col5 is twice the width.... haha just kidding...so Col5 and Col6 go into LookupTable3There is a 4th table which is made up of foreign keys which are the PK ofLookupTable1,2,3My questions is, how to get the data from the columns of each row and add itto its respective lookuptableand sequentially step throw the table to repeat the above step until I'veprocessed each rowthanks folksT.B
Is it possible by any kind of workaround to break the 8kb limit on user-defines datatypes?
My datatype can contain an arbitrary number of double-precision points meaning that I in best case only can store 512 points (2 x 8 x 512). there's a few extra bytes used for something else, but this is roughly the maximum, which is far from what I in many cases need. I serialize the object myself to ensure that I only store what I really need.
I have a column that has text delimited by a percent sign that I wishto turn into rows.Example:A column contains ROBERT%CAMARDA, I want to turn that into two rows,one row with ROBERT and antoher row with CAMARDA.I will have source rows that have zero, one, or many percent signdelimiters that will correspond to that many rows (One percent signwill create 2 rows, 2 percent signs will create 3 rows and so forth).Any thoughts?TIARob
Hi, I have a report that is frustrating me. I've built this report, and it is not yet used in production. What it does is page break in places that I don't understand.
The FIRST area it would break after a line that had wrapped text to the line below. Even though there was plenty of room to fit it, and the entire rest of the group on the page.
The second area I honestly have NO idea why it breaks.
Does anyone have a routine that takes a row of data from database, duplicates/triplicates it, appends some information to it and writes it out as 2/3 CSV rows.
I want to show a third-level overview of how many to-do items are in various statuses (pending declined and completed) for the third level individuals.
In this example there's 2 regional leaders (level3), each with local leaders (level2) and individual team members (level1)
Status "1" is a new or pending to-do item Status "2" is a declined item Status "3" is a completed item
Here's what I want the output to look like (I may need to add columns for the final report but it should look as follows):
LEVEL3 | Status 1 | Status 1 | Status 2 | Status 3 | LEADER | TOTAL | 2012 | TOTAL | TOTAL | -------|----------|----------|----------|----------| paul | 3 | 1 | 1 | 0 | roger | 1 | 1 | 1 | 0 |
Here's some sample data similar to my live DB:
CREATE TABLE #items (id int, datestamp datetime, userid int, status int) INSERT INTO #items (id,datestamp,userid,status) VALUES (1,'12/26/2011',1,1) INSERT INTO #items (id,datestamp,userid,status) VALUES (2,'12/26/2011',2,1) INSERT INTO #items (id,datestamp,userid,status) VALUES (3,'1/2/2012',4,1) INSERT INTO #items (id,datestamp,userid,status) VALUES (4,'1/3/2012',1,2)
[Code] ....
I have a view called hierarchy that shows me the hierarchy for each level 1 user. Here's the source for the view:
SELECT a.id 'level1', b.id 'level2', c.id 'level3' FROM #users a INNER JOIN #users b ON a.reportsto = b.id INNER JOIN #users c ON b.reportsto = c.id
I've run into table/view limits when using a straight query and timeouts when trying to create a cursor that looks through all level3 users and populates a temporary table with an INSERT and multiple UPDATE statements to get the individual counts for each column for each user.
(Just a note on the actual database in case it's relevant: It has about 1000 LEVEL1 users, 50 Level2, and 15 Level 3 users. It will generate about 1400 to-do items per week.)
I'm having a problem and I cannot seem to find how to fix this anywhere on the web.
I have a stacked bar chart with my data labels showing down the x axis. My client wants me to put a bit of white space between the label and the actual chart. So I tried adding spaces to my data label but of course RS thinks they don't exist since they are not non breaking spaces.
How can I do the following in my chart label
=Fields!Myfield.Value & " "
As you can see I want to add a couple of spaces to my data label. I can't use , all the ampersands get confused. I tried & char(32) but RS doesn't like char(n). I tried ChrW(160) [I saw this in the web somewhere] but this didn't work either.
I add some records to two tables after createing operation completed.
Then i use dbcc page command to oversee the structures of data page in two tables. I found some interest things: The rows in two tabes take up same space:9 bytes
You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE or calculate from the offset array
I'm wondering how most people manage very very large backups. What is the best approach to breaking up the backup files if you're restricted to a drive size (450gig in my case). I unix, you can pipe the backup to gzip and split, I'm not sure how the same thing could be accomplished in windows.
Col 9 is char (2) and Col10 is char (34). It is this column that needs to be broken up into several columns depending on the value in Col 9.
Col1 to Col3 is the key to the record.
So say if record 1 has Col 9 value 'AA' then Col10 ( 34 bytes) is to be spilt into 10+10+10+4 (four columns).The value 'AA' can repeat for several records and the value in Col 10 can change for the same value 'AA'.
Now say record 27 has Col 9 value 'BB' then Col 10 is to be split as 5+25+4 (3 columns).
There are 15 such unique values of Col 9. I have the file layouts for Col 10 for each distinct value of Col 9. So using the file layouts and Table A which exists in my database how do I proceed.
Need I make 15 tables ( one each for the 15 unique Col 9 values). These structure Col1..Col2..Col3...Col9 (the key fields and Col 9) will be common to every table. Plus the file layouts will serve as additional columns specific to each of these tables.
In my database there is a text field type that is used to enter streetaddress. This address could be a few lines long, each line with acarriage return at the end.Is there a way to search for these carriage returns and break out whatis in each line seperately?Thanks.Mike
We run a multiple database environment, with two of the databases receiving most of the user activity. (both write and read). These databases are roughly 25gb each and receive roughly the same amount of activity. Currently both of the .mdf files sit on the same drive shelf. Their log files are located on a separate drive shelf.
Debate: We have an extra fiber channel shelf available for us to use. We are not having too many problems related to performance, but we are always seeking for different ways to increase application/server performance. The debate centers on what to do with the extra shelf. There are two different suggestions on how best to use the shelf. They are:
1)Separate the .mdf files for two most utilized databases. This would separate the databases and the I/O associated with each across two different shelves
2)Break off the indexes for all 5 databases on to the extra shelf. This would leave all the .mdf files on the same shelf, but it would move the I/O associated with the indexes to a different shelf.
Can anyone provide the pros and cons of either suggestion? I would like to see arguements for either side.
We are running SQL Server 2012 on Windows Server 2008. Just wondering what type of actions would break the backup-chain or backup sequence? For instance, if you have tlog backups being taken every 10 minutes and you stop the SQL Server Service for say 30 minutes. Would this action break the backup chain? Or would everything return to normal once the SQL Server Service is restarted?
Created a SP that uses system function XP_SENDMAIL. I wantto be able to send a HYPERLINK in the email. TheHYPERLINK is created dynamically and generally long in length (exceedsdefault width of 80 characters) and when rendered in the email is splitacross 2 lines. The problem is that when you click on the link itdisregards the part of the link that has been split onto the linebelow.Does anyone know a solution to this - how to extend the width of theemail to wider that 80 characters so that the link is not split over 2lines? I know that you can use the @width parameter when placing themessage in an attatchment, however I want the link to be placed in thebody of the email and not in an attachment.Any help is much appreciated..
Whenever I make a breaking change to a custom SSIS component/tasks and update the Assembly Version, it seems to break my packages beyond repair, telling me it can't load the task:
Error loading Package1.dtsx: Error loading a task. The contact information for the task is "". This happens when loading a task fails.
All of the properties of said task now show:
Could not get value for property 'c-155-designer-name'. Specified cast is not valid.
Typically, a "breaking" change when it comes to code just means that you need to update your components to adhere to the new contract of the updated signatures. But with SSIS, it seems the only solution to this is to completely remove the component, and re-add the new version, and re-enter all of the property values/expressions. If I have a package containing 10 instances of a task that only had one property removed, for example, this results in a very time-consuming process of fixing my package.
So my questions:
1) Am I doing something wrong in my versioning/deployment that is causing my packages to unnecessarily break?
2) If this is just "by design" and the way it's meant to behave, what is the best practice for making breaking changes to custom tasks/components used by many packages? Should I just never change the assembly version, even when it is a breaking change (this seems to be less disastrous)?
3) As a last resort, if I'm stuck with having to fix the broken tasks, is there a better way to fix them rather than having to completely remove them, re-add them, and re-set all of their properties/expressions?
I'm performing SQL Server native backups and Comvault Backups are breaking the backup chain?I can work around them if they only perfume 1 backup per day.We have a 3rd party hosting our servers so I do not have much control as to what they do.I have concerns about being able to get my databases Comvault being restored.
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
