I'm pretty new to SQL and I've got a bit of a sticky problem. I've looked around on the net for a solution but possibly I just don't know what I should be searching on.
I'm trying to join tables together where there is a one to many releationship - but I'm trying to put the results for each relationship on one row (which kind of results in dynamic columns).
Is this possible? There is no limit on how many rows in table 2 can be related to table 1.
Can anyone point me in the right direction for what I should be looking at?
The reason I'm trying this is for SQL reporting services if that makes a difference?
Thanks,
Kelvin.
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
Hi all, I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000. The application is in production and everytime I want to do some changes, i do the changes on my development machine then I copy the application dll on the server. The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements. These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.
How can I do modifications without affecting the production server and the users ??? thanks.
I just want to get the sum of a table's column into a variable, in a stored procedure. The best I can do is SET @TotalBalance = SELECT SUM(Balance) FROM AccountDetails Not good enough, of course.
I'm trying to put a date into a SQL Server table. The database field type is "smalldatetime". The variable dDate is type "date" and contains: 2/2/2006 (although I think Cdate actually converts it to: #2/2/2006#). When I run the following code the date in the database is always ends up being: 1/1/1900. Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _ "VALUES (" & dDate & ")", SqlConn)daAppts.InsertCommand = cmddaAppts.InsertCommand.Connection = SqlConndaAppts.InsertCommand.ExecuteNonQuery() Any other field types work fine, it's just dates that aren't working ???? Can someone please provide a code snippet showing me what I'm doing wrong??
set dateformat 'dmy' select count(c.id), e.name from call c left outer join employee e on c.req_id = e.id where c.posted between '01/01/2002' and '30/11/2002' group by e.name order by count(c.id) desc
set dateformat 'dmy' select count(ch.id), e.name from call_hist ch left outer join employee e on ch.req_id = e.id where ch.posted between '01/01/2002' and '30/11/2002' group by e.name order by count(ch.id) desc
the results:
42NULL 34Dirk Deloof 13Annick Leirman 11Ronny Loosen 9Geert Benoot 9Nicole Ferrari 8FLOCK 8Mosselmans Christoph 7Geert Pets 7Mireille Dutrieue 6johan 6Laurent De Schrijver 5Jeanette De SChrijve 5Marc De Vlieger 5minerva 5Pascal Saesen 5Rik Haghebaert 5Sonja Van Kerckhove 4Bcatron 4Luc Willems 3Brigit Brocken 3euroadmin 3Francine Kopp 3Luc Steyaert 3Marie-Rose Buysse 3Marnix Van Steirtege 3Mattias Denys 3Pieter Frooninckx 3Reserve 3Rik De Scheemaecker 3Thierry Linard 2Carlos Van Alboom 2Dorine Sierens 2Els Poelman 2Jean Claude Vermeir 2Katrien Colman 2Kim Impens 2Kris Lejeune 2MEDreserve01 2Roger De Wilde 1Agnes Lebon 1Carla Van Den Broeck 1Eric Vlaeminck
and
118NULL 58Marie-Rose Buysse 47Dirk Deloof 45Ronny Loosen 43Annick Leirman 41Geert Pets 38FLOCK 38Pascal Saesen 28Teamleiders afwerkin 24Kim Impens 22Ilse Soetens 22Rik Haghebaert 22Severine Balduck 21Teamleiders print 20Mosselmans Christoph 20Jeanette De SChrijve 19Geert Benoot 19Francine Kopp 18Geert Meuleman 17Rik De Scheemaecker 16johan 16Katrien Colman 15Gaby Eloot 14Kris Lejeune 14Gilbert Callebaut 14Laurent De Schrijver 13Els Poelman 13Luc Steyaert 11Marnix Van Steirtege 10Frans Hoogewijs 10Sonja Van Kerckhove 10Dorine Sierens 9Eric Vlaeminck 9Thierry Linard 7Frederic Denis 7Michel Poppe 6Carla Van Den Broeck 6Pieter Frooninckx 5Katlijn Poleyn 5MEDreserve01 5Mireille Dutrieue 5Agnes Lebon 4Guido Antoin 4Onderhoud 4minerva 4Jeanette Van Brussel 3Roger De Wilde 3Sofie Gabriels 3Verf2 3euroadmin 3Marc De Vlieger 2Luc Willems 2MEDRESERVE07 2Regina Decoster 2Monique Kohl 2MEDRESERVE04 2Portier 2Bcatron 2Pierre Hanet 2Tgabriels 2Isabelle Torrelle 2Nicole Ferrari 1Robert Zwaak 1Carlos Van Alboom 1testuser 1Brigit Brocken 1Reserve 1Opleiding 1Verf
How do I put these results in one? I need not two but one Query. Please help me. Thanks
Hi, I am trying to incorporate a cursor into a table function so that i can use the function to insert values inot a table. Everytime i add the INSERT INTO @MyTable syntax then the cursor seems to start an endless loop.
Does anyone have any ideas for me?
Code Block ALTER PROC csp_ICASTransaction_AskDoc @ClientID INT AS DECLARE @ClientID INT SET @ClientID = 1 DECLARE @cClientID INT DECLARE @cMonth VARCHAR(20) DECLARE @cOccurance INT DECLARE @Counter INT DECLARE @AskDoc CURSOR SET @AskDoc = Cursor FOR select @ClientID, Month_Year, SUM(AskDocs) FROM zzenrolled$ WHERE ICASClientID = @ClientID GROUP BY Month_Year OPEN @AskDoc FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance /* SELECT @@Cursor_Rows PRINT @cClientID PRINT @cMonth PRINT @cOccurance */ DECLARE @MyTable TABLE (ClientID INT, Date DATETIME, Occurance INT) WHILE (@@FETCH_Status = 0) BEGIN SET @Counter = 0 INSERT INTO @MyTable VALUES (@cClientID, CASE @cMonth WHEN 'Jan-03' THEN '20030101' WHEN 'Feb-03' THEN '20030201' WHEN 'Mar-03' THEN '20030301' WHEN 'Apr-03' THEN '20030401' WHEN 'May-03' THEN '20030501' WHEN 'Jun-03' THEN '20030601' WHEN 'Jul-03' THEN '20030701' WHEN 'Aug-03' THEN '20030801' WHEN 'Sep-03' THEN '20030901' WHEN 'Oct-03' THEN '20031001' WHEN 'Nov-03' THEN '20031101' WHEN 'Dec-03' THEN '20031201' WHEN 'Jan-04' THEN '20040101' WHEN 'Feb-04' THEN '20040201' WHEN 'Mar-04' THEN '20040301' WHEN 'Apr-04' THEN '20040401' WHEN 'May-04' THEN '20040501' WHEN 'Jun-04' THEN '20040601' WHEN 'Jul-04' THEN '20040701' WHEN 'Aug-04' THEN '20040801' WHEN 'Sep-04' THEN '20040901' WHEN 'Oct-04' THEN '20041001' WHEN 'Nov-04' THEN '20041101' WHEN 'Dec-04' THEN '20041201' WHEN 'Jan-05' THEN '20050101' WHEN 'Feb-05' THEN '20050201' WHEN 'Mar-05' THEN '20050301' WHEN 'Apr-05' THEN '20050401' WHEN 'May-05' THEN '20050501' WHEN 'Jun-05' THEN '20050601' WHEN 'Jul-05' THEN '20050701' WHEN 'Aug-05' THEN '20050801' WHEN 'Sep-05' THEN '20050901' WHEN 'Oct-05' THEN '20051001' WHEN 'Nov-05' THEN '20051101' WHEN 'Dec-05' THEN '20051201' WHEN 'Jan-06' THEN '20060101' WHEN 'Feb-06' THEN '20060201' WHEN 'Mar-06' THEN '20060301' WHEN 'Apr-06' THEN '20060401' WHEN 'May-06' THEN '20060501' WHEN 'Jun-06' THEN '20060601' WHEN 'Jul-06' THEN '20060701' WHEN 'Aug-06' THEN '20060801' WHEN 'Sep-06' THEN '20060901' WHEN 'Oct-06' THEN '20061001' WHEN 'Nov-06' THEN '20061101' WHEN 'Dec-06' THEN '20061201' WHEN 'Jan-07' THEN '20070101' WHEN 'Feb-07' THEN '20070201' WHEN 'Mar-07' THEN '20070301' WHEN 'Apr-07' THEN '20070401' WHEN 'May-07' THEN '20070501' WHEN 'Jun-07' THEN '20070601' WHEN 'Jul-07' THEN '20070701' WHEN 'Aug-07' THEN '20070801' WHEN 'Sep-07' THEN '20070901' WHEN 'Oct-07' THEN '20071001' WHEN 'Nov-07' THEN '20071101' WHEN 'Dec-07' THEN '20071201' END ,'3' )
SET @Counter = @Counter + 1 IF @cOccurance > @Counter FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance END CLOSE @AskDoc --INSERT INTO [Transactional$New] (ClientID, [ Date], [ Occurance]) SELECT * FROM @MyTable DEALLOCATE @AskDoc
I want the Cursor to insert the values into the table that i commented out, but it doesn't seem to insert the values, it just loops.
"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT 'Test' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTESTarchived est.pdf', SINGLE_BLOB) AS BLOB"
When I try to put in a variable as follws
"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT '" + @[User::MyFileValue] + "' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTestarchived est.pdf', SINGLE_BLOB) AS BLOB"
I keep getting errors Saying it contains an illegal escape sequence of w any ideas ?
I have the following sql string in my asp.net, its meant to retreive a value based on a text box value being "like" a value from my database, however when i place a word in the textbox nothing happens, can someone please take a look at the statement and see if its well formed, String sql = "SELECT fName FROM Customers WHERE fName LIKE " + "'" + fName.Text + "/%' OR PostCode= " + "'" + Postcode.Text + "'";
I am creating a stored Procedure and I am getting an error which relates to DATENAME. SELECT COUNT(*) AS calls, DATENAME(@varDate, CALLSTARTTIME) AS 'Total Calls' FROM CALL_LOG_MASTER WHERE (COMMERCIALS='1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <=@EndDatesql doesn't like: DATENAME( @varDate, CallStartTime)sql works fine if I change @varDate into 'yy', 'mm', 'dd', or 'wk'Since I do not want to make 5 unique Stored Proc just because of @varDate.....Is there any way to work around this problem?
hi guys, Could any one tell me whats the best way to put encypted data into a table.i have a textfield that a single integer is entered into it. It is then encrypted using DES encrption algorithim.It converts the value to byte(Convert2ByteArray method) I pass the data to a stored procedure which converts it to char before inserting it into the table. when i checked the database all it entered was System.Byte[]. Does anyone know where im goin wrong? Mairtin
We have a table that needs to be populated with a Foreign Key and an image file that matches it. The image file is named the the person's SSN which matches the FK.
Is there a way I can use the name of the file (123456789.jpg) to import it into the database or is there a better way?
I had an idea to put all my web design settings, css text and web content in the database..This way it would be easy for others to edit remotely. Do you guys think this would have an impact on performance if I do this?
I have a pivot transform that it believe is configured correctly but is not distributing the values accross the columns on the same row. for example.
input:
id seqno codevalue
1 A red
1 B red
2 C blue
2 A green
2 B violet
3 A green
desired output:
id Seq_A Seq_B Seq_C
1 red red null
2 green violet blue
3 green null null
what I am getting:
id Seq_A Seq_B Seq_C
1 red null null
1 null red null
2 green null null
2 null violet null
2 null null blue
3 green null null
I do have the pivot usage for the id column set to 1. I have the pivot usage for seqno column set to 2 and codevalue column set to 3. I have the source column for each of the output columns set to the lineageID of the apprpriate input columns. I have the pivotKey values set for each of the destination columns. A for column Seq_A, B for column Seq_B, C for column Seq_C. All four columns have sortkey positions set; 1 for id, 2 for Seq_A, 3 for Seq_B and 4 for column SEQ_C.
It seems like the id column's pivot usage is not set to 1 like it should but when I check it is 1.
I also have several other pivot transforms in the same data flow and they are working as expected.
I have a suspicion that there is some hidden meta data that is messed up that is over-ridding my settings (just my guess) I have deleted this transform and re-done it several times, checking each configuration value, but still getting the same result.
I have witten a lot of stored procedures in my project where I did not put dbo before the user tables.My colleague told me that I have to put dbo for all statements other there could be a problem.
Here's what I want: I have a DTSX package run as a SQL Server agent job, that each time it runs I want it to create a new log file with the date & time in the filename. e.g. MyJobLog.20080311_103613.txt
I have implemented this using the SSIS log provider for Text Files, and defining the Connection String as an expression as follows:-
Here's the problem:- When the job runs, each time it tries to write to the log file it re-evaluates the filename, and as the seconds pass by the name changes! So instead of one file I end up with several:- e.g. MyJobLog.20080311_103613.txt MyJobLog.20080311_103614.txt MyJobLog.20080311_103615.txt
So the one execution of the job is now divided across three files! This is not what want!
Here's one way I fixed it:- One of my Jobs is run from an MS-DOS batch file, so I was able to pass the constructed date/time sting into the DTSX file as a parameter. This works perfectly.
Here's my problem:- Three more of my jobs are run as scheduled job from SQL Server Agent, so I can't pass in the date/time as a parameter each time (unless you know otherwise). And the clients' security policy won't allow the job to call an MS-DOS batch file which runs the DTSX package. Or is there really some other way to stop the Log filename from re-evaluating on every write?
The dataset2 control doesn't seem to want to place the text in the right spot. I put it right after the 1st Dataset and the user comments wound up all the way down in the footer of the site. I want them to appear in the white area just underneath the article text. Here's a link to one of my articles so you can see this. http://www.link-exchangers.com/view_full_article.aspx?aid=50 Down at the bottom left corner you'll see white space below the article. I'd like to have all the comments lined up going down the page. Is the dataset the best control for this task?
How can I program BCP to output text items in double quotes (")? Here is an example (please try it) that trys to output some columns from a table to csv file. However, due to the existence of commas within the fields, the comma separation gets messed up.
------------------------------------ USE [MASTER]
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'mcg1') DROP TABLE mcg1 go
CREATE TABLE mcg1 (pkINT IDENTITY(1,1) ,Address_1VARCHAR(100) ,CityVARCHAR(100)) go
Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1" queryout "C:mcg1.csv" -c -t,"'
------------------------------------ The output I get is below. You can see how the use of commas in the text makes the comma separate list all confused 100 Road1, Suburb1,BigCity1 200 Road2, Suburb2,BigCity2
Thus what I want is "100 Road1, Suburb1","BigCity1" "200 Road2, Suburb2","BigCity2"
You can do this OK in DTS by specifying the text identifier to be double-quotes. I do NOT want to use DTS and want to be able to do via a T-SQL procedure. Note that the real table I will export from has numeric datatypes and I would prefer NOT to wrap them in double-quotes too.
Thus, how can I alter the Exec Master..xp_Cmdshell command, to wrap each text field in double quotes. I may have to use a format file in which case please provide the format file too.
I am retrieving the current month and setting a variable accordingly.
Code: DECLARE @currMonthNum INT SET @currMonthNum = DATEPART(MM, GETDATE())
My challenge is that at a later point I want to use the @currMonthNum value to 'build' a date in the style yyyymmdd and the variable is only one digit for the months January to September (e.g.: 1 to 9). For the months January to September I need to pad the variable with an extra zero (e.g.: 01 to 09).
Here is my attempt to do this...!
Code: IF @currMonthNum IN (1, 2, 3, 4, 5, 6, 7, 8, 9) SET @currMonthNum = '0' + CAST(@currMonthNum AS nvarchar)
Unfortunately this does not give the desired result of two digits, however ironically if I add a + '0' to the end I get 10 to 90! It is like the 0 at the front is simply being ignored.
I have also tried setting the @currMonthNum variable to an nvarchar type before performing the character addition as well attempting to use the CONCAT command (however I don't think that CONCAT can be used when setting a variable).
how to display a zero in front of the months that require it?
Need getting data into XML format as shown in the last code block.The datatypes and table structures are pretty much fixed, but I can re-hash data into another Temp Table, CTE, etc..This is a server running SQL Server 2012, but I'd guess any version that understands FOR XML PATH should be fine.
Source tables and data if object_id('Tempdb..#Element1') is not null drop table #Element1; create table #Element1 ( [Attr1] varchar(10) ,[Attr2] varchar(4)
is there a way to temporarily remove a record and then putting it back again? in my case I have lots of test patient with last)name ='test' and first_name='test'. I want to remove these test patient temporarily , do my calculation and them put them back.
The table is called "person" and all these test patients are in there including real patient under their last-name, first_name.
I have nearly 1000 table. I need all the table structure in word document. Is it possible to take table structure using Stored Procedure. If it is How to do that.
I would like to know how to put an attribute on the root node of thexml returned from a FOR XML PATH query. One thing I tried is this:selectm.msgid '@msgID',st.namelong 'set/@namelong',st.nameshort 'set/@nameshort',frommsgset minner join settable st on (st.setid = m.setid)where m.msgID = 195for xml path('set'), root('message')but it gives me:<message><set msgID="195"><set namelong="STUFF HERE" nameshort="STUFF" /></set><set msgID="195"><set namelong="MORE STUFF" nameshort="M STUFF" /></set><set msgID="195"><set namelong="TESTING 123" nameshort="TEST" /></set></message>here is what I want:<message msgID="195"><set namelong="STUFF HERE" nameshort="STUFF" /><set namelong="MORE STUFF" nameshort="M STUFF" /><set namelong="TESTING 123" nameshort="TEST" /></message>I can't get it. If I use: root(''), then it tells me: "Row tagomission (empty row tag name) cannot be used with attribute-centric FORXML serialization." I'm sure there is a trick to this-- anysuggestions?Many thanks.the chippster
