I am recieving complete row in one single column and I have pipe delimeter in this row . I want to retrieve the data in individual columns...Currently row is in one column
Data
02|vinod sahu
Expected output
Col1 col2
02 VInod Sahu
I'm designing a sales report. The user would like to be able to look at individual customers against total sales in a report.
I have a parameter that will allow the user to select from a list of customers. Then I need a column that will show that customers sales and the next column will show total sales. If I filter the data set by Customer I will only get that customer's sales. Can I filter on individual columns?
I tried something like
=Sum(IIF(Fields!Name.Value=Parameters!VendorName.Label,Fields!Coil_Weight.Value/code.Expon(Parameters!Units.Value),0))in the expression but that doesn't seem to work.
I have data in SQLSever 2008 R2 in one column as following. I would like to run a sql statement and capture them into individual columns. Would that be possible? The column separator is |
MSH|^~&|HL7ADM|PYXIS|PAH|HL7PRX_PAH|201208131129 46||ZPM^ZPM|9442|P|2.3.1||||||| ZPM|C|console|N-HEART-ST|1|23|43025204|DOBUTAMINE 1000MCG/ML|U|1|1|1|CS3808|TEST, MONKEY|||1|||||0|1|0|20120813112839||||||||0||IV|| |||||||||||
Hi,
I have an example situation that seems like it should have a super easy solution, but my jobs keep failing.
Here we go. . .
I have a SQL Server 2005 table as my source in a data flow task.
This table contains raw data.
We'll call it FACT_Product_Raw - which contains a field called ProductType varchar(1)
Let's say that ProductType contains values of "A" or "B" or "C" - or for that matter, some null and garbage values
I have a lookup table, LOV_Product_Types
This table contains 3 fields that will transform my raw data table
We'll call these fields ProdTypeID smallint, ProdTypeRaw varchar(1) and ProdType smallint
It contains pairs such that A = 1, B = 2, and so on.
Here's what I want to do.
I want to ADD a field to FACT_Product_Raw that contains the "looked up" value from LOV_Product_Types.
Let's say that I want to add the ProdTypeID field to my _Raw table.
I have used the _Raw table as both my source and destination
It blows up every time.
Help.
Thanks,
David
Hai,
Iam new to SSRS please help me, my question as follows:
in my table fields are departmentid, studentexpected, student name, date attendance,sortorder
while grouping(drill down) the departmentid column , iam displaying student name, date attendance its working fine.
problem: while grouping(drill down) the studentexpected resultant column i need to display student name, date attendance fields.
in my database studentexpected column displaying studentlist like 28, 56,250, while grouping(drilldown) the individual lists like 28,56,250, i need to dispaly student name and date attendance fields.
my problem is while grouping(drill down) the departmentid column iam getting result for studentlist 28 as iam getting 28 student names and 28 attendancedates, this functionality is correct.
but while grouping(drilldown) the studentexpected resultant column iam not getting (assume for studentlist 28) 28 studentnames and 28 attendancedates.iam geting only 2 studentnames, 2 attendance dates, but i need respective list columns
in database iam taking sordorder as 7.
iam facing difficulty at the time filtering the studentname and attendancedates columns in table properties
which data i have to give in filter column for displying 28 student names and 28 date attendances fields at the time of grouping studentexpected column?
take below references:
database prview:
departmentid studentexpected student name date attendance sortorder
101 28 jack 12-31-2007 7
dapartmetid drilldown report preview:
departmentid studentexpected student name date attendance
+101 28 jack 12-31-2007
-- ---
-- --
-- ---
-- --
(assume 28 student names and 28 date attendance)
My Problem:
like above preview i have to dispaly respective studentexpected column results,
departmentid studentexpected student name date attendance
101 + 28 jack 12-31-2007
-- ---
--- ----
(here while drilldown the studentexpected resultant column like 28,56,250 ,i need to dispaly respective studentnames and respective date attendances)
which data i have to set in filter column in front end , how to solve above problem? help me
Thank You
Jacks V
I have a value in a column '01-08-087-0101W5'. I would like to break this value down into 5 different columns. The column will be broken down at the dashes , so the 5 columns will have values like
01
08
087
101
5
I would also like to trim any leading 0's in the above values. i have been trying the following
SUBSTRING(column1,patindex('%[^0]%',column1) ,2) AS FROM_TWP
hi,
i have labels for data stored in one cell
eg: item1; item22; item231;
and i want to convert it in following output
(probably using substring and charindex)
No_question| item_position | label
1|1| item1
1|2| item22
1|3| item231
any idea?
I have a table with 100 rows, 1 field (ID), and I would like to write a query to output it as 4 rows, and 25 columns.
Row data
ID
1
2
3
4
5
6
7
8
9
...
98
99
100
Output will be like
c1 c2 c3 c4 c5....
1 5 9 13
2 6 10 14
3 7 11 15
4 8 12 16
Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.
Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment](
[EQU] [VARCHAR](50) NOT NULL,
[Notes] [TEXT] NULL,
[Facility] [VARCHAR](50) NULL)
INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility])
SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union
SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION
select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA'
SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
I have a report with several matrix objects. The data contained in each matrix is simple. One matrix has one column of labels and a column of data. The other has 3 columns of data. Both matrix's only show about 25 rows of data. A variable amount of data is displayed above the matrix's so that sometime the render across a page boundary. Whenever this happens the matrix doesn't render rows down to the bottom of the page. A considerable amount of empty space is left on the page, the completed matrix is displayed on the next page. I'm wondering how to get the matrix to render in this empty space.
This problem only occurs when I view the report on screen, in the Visual Studio "Preview" window, or from the Report Server web site. Tiff, pdf and printed output doesn't contain the extra space.
I've tried adding my matrix to a List and a Rectangle to see of this would fix the problem but it didn't help.
I've check the dimensions and margins or the page and I don't think I have any sizing problems, everything should fit on the page.
--Mark
I have a link that inserts postid, catid, and postdate. I am trying to get count of catid(how many times its in the table) then display the number on a page. i have never tried this before. one more thing. Can you put a datalist inside a formview when the formview is databound already? Can someone help? Thank you.
I am trying to restore from a backup of an individual filegroup in SQL Server 7.0. I have backed up the individual filegroups, and I want to restore one of them (not the whole database). The process in EM is almost the same as restoring the entire database, only you choose the radio button 'Filegroups' and the files/filegroups you want to restore.
The restoration appears to complete sucessfully, but afterward, my database seems to be permanently loading. In other words, in EM the database is greyed out and you cannot use it anymore. It stays like this until you drop the database altogether. Is this a bug? Has anyone else had this problem or does anyone have experience restoring from filegroups sucessfully???
What is the best strategy for backing up individual tables in a database, not the whole database? Thank you for your help.
Best Regards,
Mark
Here's my two tables:
tblClients
--------------------------------------------------
ClientID (varchar(3)) | ClientName (varchar(100))
--------------------------------------------------
001 | First
002 | Second
tblProjects
---------------------------------------------------
ProjectID (varchar(6)) | ProjectName (varchar(100))
---------------------------------------------------
001001 | First Project1
001002 | First Project2
002001 | Second Project1
002002 | Second Project2
As you can see, the ClientID is the first 3 digits of the ProjectID, signifying that a certain project belongs to that client. I know it's a bad design, but I can't change the DB, I have to use it.
The problem is that I need to join the two tables based on the ClientID, but I don't know how to just retrieve the first 3 numbers in the ProjectID.
I need something like:
tblClients INNER JOIN tblProjects ON
tblClients.ClientID = tblProjects.First3DigitsOfTheProjectID
Hi,
In previous versions of SQL Server you were able to execute single steps within a DTS package. It doesn't seemt to be possible in visual studio. Does anybody know otherwise?
Thanks
Suppose my database has 3 table A though C and I need to get the number of records in each. I could do it as individual selects like
SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A
SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B
SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
or
SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A
UNION
SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B
UNION
SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
Is there any problem in unioning statements like these? In reality there were over a hundred tables and the TSQL was generated by looping through SYSOBJECTS. I eventually used the first construct as the XML was simpler to generate.
In Pablo Castro webcast, First Look at ADO.NET 2.0, he mentions the use of UpdateBatchSize which I think would be handy.However, I was not able to get it to work.
Dim t As New Global.System.Data.SqlClient.SqlDataAdapter("Select * from Table_1", Global.System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString)Dim d As New Global.System.Data.DataTablet.FillSchema(d, SchemaType.Source)Dim cb As New Global.System.Data.SqlClient.SqlCommandBuilder(t)For c As Int32 = 1 To 10000Dim r As Global.System.Data.DataRow = d.NewRowr("id") = Global.System.Guid.NewGuidr("val") = CType(Rnd(), Int32)d.Rows.Add(r)Nextt.UpdateBatchSize = 100t.Update(d)d.Dispose()t.Dispose()
What ends up showing in Sql Server Profiler (Sql Server 2005) is each Insert being executed in it's own statement:exec sp_executesql N'INSERT INTO [Table_1] ([id], [val]) VALUES (@p1, @p2)',N'@p1 uniqueidentifier,@p2 int',@p1='3793CB5E-3B7A-45E7-9A53-0BD528BB6B07',@p2=1
I think I made this example very simple and yet can't fathom why it won't batch the statements.
I'm somewhat aware of SqlBulkCopy and was very pleased with that speed, but don't think it would handle the Update,Delete,Insert that the SqlDataAdapter.Update would.
Originally started using xsd DataSets until I saw the data tutorials in the Learning section here in which case I copied out the autogenerated TableAdapter classes and fuddling with them to do what I want since batching was not something I saw in TableAdapters.
Does anyone see what I'm missing here?Nathan
Please can someone advise me on how I can create a statement that will query each individual row within a table.
As the data will vary month by month I'm unable to state the amount of rows that will need to be queried.
An Overview:
We have web-based application with a SQL back-end. When changes are made to a record a copy of that info is sent to another table (a quick snapshot, name, date, number, department etc.). At the end of the month I need to apply this data to an imported itemised phone bill so that I can make sure that if the mobile phone had been reallocated to another person I can change the cost accordingly.
As it stands the operator has to print a copy of the phone details before reallocating to another person. Then at the end of the month manually changes the data within the itemised bill by referring to the printouts. Ideally I would prefer to automate the whole process.
Please find below the script - its very much in its infancy!!
CREATE PROCEDURE sp_reallocate AS
select * from tbl_mobile_reallocate
order by allocation_change_date
update tbl_mobile_import
set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate
where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno
and tbl_mobile_import.invoice_date <= tbl_mobile_reallocate.change_date
update tbl_mobile_reallocate
set status = 'Closed'
GO
I think jthis is a bug. I have a table created and populated on its own filegroup. I backup the db(all filegroups) and the trans log then I drop the one table. When I try to restore from my backups, it insists that I back up the trans log again. I do, then do the restore of both the filegroup and the trans log. The restore finishes, but my table is still not there and I can never get it back.
Any one with comments please help
Brad kreuzburg
First of all, I`m a very new entrant to the world of SQL Server. So I`ll make some mistakes.
I need to know how to write a stored procedure that will insert or update INDIVIDUAL rows. All the documentation I`ve seen is pretty coy on how to actually do this.
Any actual examples, or any tips at all would be greatly appreciated.
Thank you very much.
I am writing a utility that browses all steps, connections, and tasks in a given DTS package. One of the functions is to rename each item using its description, ie, removing spaces, formatting it, and then appending it to the standard DTSTask_ActiveScriptTask_MyCustomNameDerivedFromTh eDescription. I am being sure to change everything to match this in both the Step and task for a given set. The behaviour that is occuring is this. The package appears to be named correctly, and even seems to run correctly, but none of the precedence constraints, it, OnFail, OnSuccess, OnCOmpletion seem to show up in the DTS Designer Environment. The package does seem to be running correctly, any idea what would cause this behaviour?
View 1 Replies View RelatedSQL Server's Books On-line seems to hint that this can be done. Does anyone have any info or practical application of this? Can someone confirm that this is possible please.
Aquafortis
In my database I have a table for Users, with an int primary key, and a table for Connections, with a combined primary key consisting of two UserID foreign keys. (the smallest first)
At the point I am stuck I have one UserID, lets call it current_user, and a column returned by a select statement consisting of UserIDs. Some of these IDs will likely be smaller than current_user, and some will likely be larger.
What i need to do is construct a view of two columns, combining each of the UserIDs in the column I have with current_user, with the smallest UserID of each pair residing in the first column, and the largest in the second column.
The point of this is to then SELECT the connections identified by the UserID pairs.
I suspect I could accomplish this if I could set individual fields in the a view, but I seem to have missed (or forgotten) that lecture. Anybody want to clue me in?
I have SQL Server running on our web server located at a managed hosting site at Rackspace. I've got some developers who are going to be editing various sites and will need access to specific databases. Is there something built in to MSSQL, or a third party app, which will allow for this kind of remote access? They will be doing everything from restructuring these DB's to just inputting data.
Thanks!
Chris Sanders
In SQL Server, I need to create a table from 3 different individual tables. I am new to the SQL Scene, so i want to know the best way to go about this. Each table has different fields, so that is making it difficult (at least for me). How about creating 1 table, that is composed of 3 other tables?
I know this is bad practice, but our District Manager wants to see production for his 'team' and whoever set this up back in the beginning gave each individual employee a different table, with custom fields (Why?!?!?!?!) so now I am trying to merge those 3 into 1, but want to get some insight on the best way to do this since there are different fields in each table and the merged (or master) table needs to have all of those fields.
Good day,
I have a Job that runs every half an hour, and has about 30 steps. If a step fails it moves on to the next step (the steps are not dependant on one another)... However I would like to receive notification if that occurs. I have SQL 2005, with database mail enabled and set up (works fine for other jobs)... The question is how do I get it to send me an email if a single step fails, however the job on a whole succeeds?
Do I need to set up a step between each job?
Step 1 An actual step, on failure: step 2 On success Step3
Step 2 A step that emails the failure of step 1, then goes to step 3
Step 3 An actual step
etc etc... Or is there a better way?
Hi,
Can any one tell me
how to find out the count of individual chars in a String
ie., my string is s='ganesh kumar'
my output must be as below
Character Occurance
--------------------
g 1
a 2
n 1
e 1
s 1
h 1
k 1
u 1
m 1
r 1
----------------------
Can any one do it,
This is interview question..
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
We have a pretty large SQL Server database, just shy of 400 GB. It is divided up into 24 different data files. The previous DBA here said it is faster to backup the backup individual data files to seperate files. So what he did was issue 6 backup statements like the one below as a step in an agent job.
BACKUP DATABASE MYDATABASE
FILE = 'Data',
FILE = 'data2',
FILE = 'data3',
FILE = 'data4'
TO Data1_4
WITH FORMAT
Next step:
BACKUP DATABASE MYDATABASE
FILE = 'data5',
FILE = 'data6',
FILE = 'data7',
FILE = 'data8'
TO Data5_8
WITH FORMAT
etc.. in sequence.
What I question is how this is faster than just issuing a backup for the whole database? Has anyone ever ran into this before?
- Eric
A login named UserLogin1 has db_dataReader and db_dataWriter roles, and three schemas which are db_owner, dbUser1, and dbUser2 in a database named Database1. I wanted to have some tables for read-only, so I right clicked on the table and select properties. In the Permissions tab, I added UserLogin1 and checked Alter, Delete, Insert, Take Ownership, and Update under Deny. It worked for schema dbo only. I did the same steps for the tables that have schema dbUser1 or dbUser2, but UserLogin1 I added didn't stay. Why? How can I make those tables read-only?
Thanks.
DanYeung
Hi,
My customers are having problems to restore their own db.
They use SQL Server Management Studio and when they try to restore their db from a device and add the backup file an error message comes up stating that the user does not have permission to C:Program FilesMicrosoft SQL ServerMSSQLBackup.
How can I set a different backup location for each user?
Kindest regards,
Tito Duarte
Hi !
i am developing feature like blogs where a user posts but what i am looking for a stored procedure which could count that how many post were made in a particular month like
jan2008 (3)
feb2008 (8)
so any one could provide me helping link....
Hi,
I have a package in which there are four tasks. I have to get the status of those four tasks and finally send a mail. I am unable to get the status for individual task.
Has anyone tried this? Please help.
Thanks in advance.
hello friends iam newlly joined in to company any one can help me how to bind individual controls to a single SqlDataSource Control..
my problem is "i have different text boxes and dropdown boxes and what i want to do is if i enter primary ker field(employeeno) in the text box automatically i need to get that particular record and i need to populate it to the Respective controls...
is there any chance of doing this by using a single datasource control..
pls help me its urgent
Regards
k.seshu