Dear All,
i've a string to pass as a parametre to a procedure.
like
create preocedure myproc(@EMPID VARCHAR(50),'abc,def,ghi,jkl')
...
end
i need the output like this
1 abc
2 def
3 ghi
4 jkl
.....
how can i do that?
Vinod
Even you learn 1%, Learn it with 100% confidence.
I have a parameter value as shown below and this is dynamic and can grow
Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL --- each state to be followed with comma and the next state
correct query that will fetch only state names and not the numbers.
Hi I'm pretty new to using Microsoft Visual C# .NET and I want to upload a comma delimited text file from my local machine into a table in an sql server database through a web app. How would I go about programming this and what controls do I need? Any help would be much appreciated. Thanks in advance.
View 4 Replies View RelatedI have a text where it is mix up of all alphanumeric values... For example like this
ACETAMINOPHEN 250 MG ASPIRIN65 MG CAFFEINE
ACER NEGUNDO POLLEN0.0021 G/ML ACER RUBRUM POLLEN0.0021 G/ML ACER SACCHARINUM POLLEN0.0021 G/ML ACER SACCHARUM POLLEN
So my requirement is i need to add comma (,) before every number value in this text.The text is different from each other.But main one what ever the number is there need to add comma before that one (decimal,numeric etc)
ACETAMINOPHEN,250 MG ASPIRIN,65 MG CAFFEINE
ACER NEGUNDO POLLEN,0.0021 G/ML ACER RUBRUM POLLEN,0.0021 G/ML ACER SACCHARINUM POLLEN,0.0021 G/ML ACER SACCHARUM POLLEN
Hi,On SQLServer 2000, I have a table with a following structure:MYTABLEcol1 char,col2 date,col3 numberMy Objective:------------Externally (from a command line), to select all columns and write theoutput into a file delimited by a comma.My method:---------1. Probably will use OSQL or BCP to do this.2. Use the following syntax:select RTRIM(col1) +','+ RTRIM(col2) +','+ RTRIM(col3)from MYTABLE;My 3 Problems:-------------1) If there is a NULL column, the result of concatenating any value withNULL, is NULL. How can I work around this? I still want to record thiscolumn as null. Something like say from the example above, if col2 isnull, would result to: APPLE,,52) The time format when querying the database is: 2003-06-24 15:10:20.However, on the file, the data becomes: 24 JUN 2003 3:10PM. How can Ipreserve the YYYY-MM-DD HH:MM:SS format? Notice that I also lost theSS.3) Which utility is better? BCP or OSQL?For OSQL, it has a "-s" flag which gives me the option of putting acolumn separator. But the result is:"APPLE ,14 JUN 2003 , 5"I don't need the extra space.While for BCP, there is no column separator flag.You will notice from my inquiry above that my background in SQLServer isnot very good.Thanks in Advance!!RegardsRicky*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related
hi ,
I have 2 sql tables. 1 is the header table and another is the detail table. How can I have the header record being appended in the text file and then have the detail records being appended to a same text file again with comma delimited ?
I want to join differnet tables and import the data into comma delimited text file. There will be lot of checks like if then else to manipulate data. I want to use stored procedure but don't know how to output to text file. Is there any utility which can be used in stored procedure. In future this will be run as an automated job.
Thanks in advance.
Hello, i need to load some data from a long comma delimited text file, How can a i do that, using t-sql?, thanks for your help!!!!!
View 5 Replies View RelatedHi All
I am working on a query to get all the datetime values in a column in a table into a comma separated text.
eg.
ColumnDate --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000
I wanted to get something like
2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16
Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks
Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's.
In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this.
How can I do this?
I have one sp which has param name as cordinatorname varchar(max)
In where condition of my sp i passed as
coordinator=(coordinatorname in (select ltrim(rtrim(value)) from dbo.fnSPLIT(@coordinatorname,',')))
But now my promblm is for @coordinatorname i have values as 'coorcinator1', 'coordinato2,inc'
So when my ssrs report taking these values as multiselect, comma seperated coordinator2,inc also has comma already.
Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN,
it was never a problem in DTS 2000.
any work around.
Thanks,
I have a file which contains comma separated columns. One of columns contains names of companies. Sometimes the names of the companies have a comma as part of the name. For those, the value is surrounded by double-quotes.
But it seems that SSIS ignores the double quotes and ONLY looks for the column separator. This causes my value to be split in half.
Traditionally, I thought parsers that deal with this type of import do not automatically take the first comma following the double-quote as the column separator but instead look for the first comma following the ending quote. (i.e. Look at how Excel performs imports...)
I cannot set the column separator of the column to double-quote comma since only those values that HAVE a comma in them are qualified.
Any ideas?
Here is sample fie content to see what I mean:
342123, Jason, 12345
21, Kim,4567
32.43, John Paul, 1245
23, "Mr. T", 98764
12, "Peter, Paul, Mary", 09643
The last entry should be imported as 12 in the first column, "Peter, Paul, Mary" in the second column and 09643 in the third but instead ends up as 12 in the first, "Peter in second column and Paul, Mary", 09643 in the last.
(Oddly enough, if I remove the first column of numbers the import works like it is supposed.)
Hi All,
I have got MSSQL 2000 set up on a machine in my rack at my local telehouse, and a web server set up at home on an ADSL line.
Both servers can see (ping) eachother fine , so you can rule out any kind of connectivity issues straight away, but when i try to get my forum to connect to the mssql database using the correct credentials it just fails saying that the credentials are incorrect ot the server does not exist.
I also installed an SQL database tool on my web server (Shusheng SQL Tool) and attempted to connect to my SQL server using that tool, and got the following message: '[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.'
The server is currently using mixed mode authentication (SQL/Windows) and has both TCP/IP and Named pipes enabled.
Is there some kind of 'Enable remote connections' option in SQL? I need to be able to allow connections to my SQL server from any system, anywhere...
Any ideas?
Hi,
Why we allocate .mdf and .ldf on seperate drives?
Please tell me a proper logical reason behind it.
MERRY CHRISTMAS EVERYONE :)
I need to update a table on our Test Server which is GCSQLTEST, with another table thats on our live server GCSQL. How would I go about doing that in a stored procedure??
CREATE PROCEDURE [InsertRevised_MainTable]
AS
INSERT INTO dbo.RevisedMainTable
([IR Number], [Date], [I/RDocument], [Violation Type])
SELECT [Incident Report No], [Date], [I/RDocument], TypeOfIncident
FROM dbo.RevisedMainTable
WHERE NOT EXISTS (SELECT * FROM dbo.RevisedMainTable
WHERE [IR Number] = [IR Number])
Hi. I have been talking with some developers who have built a hosted application supporting multiple customers. Their database approach is to create a new, dedicated database (same schema each time) for every customer that signs-up.
This approach is contrary to typical hosted DB designs that I have delt with -- that is, a single database holding multiple customer information rather than a unique database for each customer.
Does the improved security of a dedicated database out-weigh the additional maintenance requirements?
If anyone has some objective thoughts on this topic, I'd love to hear them.
Thanks,
Bill
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
View 5 Replies View Related
Hello ALL,
I want to seperate a FULNAME into First name, last name and middle name using SSIS. Provided "," Seperator
u can take example
John first name,mathew middle name,
smith last name
Please give me the command how to do it.
Thank YOu
I have twon names in one row and in the same column.
Column Name
John Baker, Sarah Lynn
I want to seperate them using Derived Column like. First part before COMMA,
SECOND PART after Comma. into two diffrent directions. and later Union.
What is the command to extract the first name and second name.
Please le t me know
I have a field that contains values such as 8ft , 7ft, 18ft
I have a report in reporting services that shows this:
Before Restock After Restock
Date 1/12/2008 8ft 9ft
1/13/2008 10ft 7ft
1/14/2008 5ft 4ft
I want to create a subquery that grabs the before restock and figures out if it sheds the "ft" part of the value, and then put in a where before restock > (greater than) After Restock.
Is there a an easy way to do this?
I have 4 tables with the respective amount of records
1) 6755
2) 2021
3) 2021
4) 355
They all have the same columns. However, they need to be seperate, or at least when I query them. I'll be accessing this database via the web. i was first afraid that a large database would cause major slow down when accessing the db. So I broke it up into 4 tables. If I combined all 4 tables into one large table and just had a column that differentiated the 4, how significant would be the change in speed when accessing the table? It's not a big deal to keep them seperate, its just that when I have to add or remove a column from one table I have to remove it from all the tables. Furthermore, I'm using a module from DEVEXPRESS, don't know if anyone has heard of it, but when you use a gridview, it loads up the entire table even though your paging (which I think is retarded), so for that reason I was afraid it would slow up my access to the db. Any thoughts?
Hi all -
Quick question... I want to move all my non-clustered indexes to their own seperate drive array. How would I accomplish this?
Do I just add the filename at the end of the statement like you do in Oracle? (EXAMPLE: CREATE INDEX IDX_Cls_Code on dbo.Class(Code) on secondary.ndf)
If this isn't correct, can someone please post the correct syntax?
Thanks!
Rich
I have been asked to move the indexes on our membership database tables to seperate partitions on the server. This is a new concept to me and thought I could use some advice on how to go about doing it.
Thanks in advance.
Brad Keck
Hi,
I'm struggling with this. I'd like to perform a joined query
from two or more tables and take the same field from several
rows of one table into seperate fields of a single row in a new table.
Like this:
table 1
uidunameuloc
1mehere
1methere
2youhere
2 youthere
table 2
uidulocuparam#uparamval
1here1a
1here2b
1here3c
1there1d
1there2e
1there3f
2here1g
2here2h
2here3i
2there1j
2there2k
2there3l
result in table 3
uidunameuloc uparval1 uparval2 uparval3
1mehere a b c
1methere d e f
2youhere g h i
2 youthere j k l
uparam# field in table 2 always the same sequence
Any ideas????
Thanks...
Hey all, Here is what I would like to do.. if any can give some advice or direction with this i would appreciate it.
We have a "master database" (MS SQL) at our main office. Then a smaller office with only a dial-up connection. Can I have my db application write all the data input to a local DB. Then write something to connect with the master DB, through a Terminal server, etc., and update it with the data from the other smaller db?
We are looking at expanding our web application (C# and Sql Server). The module we are going to be adding will be far more processor intense (plotting and modeling) than any of the current code.
I need to know what the best design for this add on would be.
Would it be better to
a) Keep the new module as part of the core db (entire application on 1 database) and use loading balncing between 2 (or more) servers to handle the huge increase in demand?
b) Create the new module as a seperate database that runs on a seperate server?
If B is the best option (I am thinking it is not) is there a way to write easy and efficient queries and views of the 2 databases as 1.
Thanks
Justin
Justin Bezanson
www.justinbezanson.com
www.geekdaily.net - web development and technology blog and news
www.offsidegames.com - free online flash games
www.thrufare.com - free proxy website
I've got a customer who thinks their data should be sepeated and reside on two seperate databases in case one is compromised. Now I've never heard of anyone doing this and would like comments from the users here as to why this should/(shoult not) be done.
I'm assuming that they want to keep their general info e.g. name and address on one database and keep other (medical info) on the other db.
None of our db's are outside of the firewalls so to me this makes no sense but I would like feedback.
thanks.
Does anyone have any idea how to syncronise two SQL identical tables acrossa network, between servers?We want to run our website from a SQL tables on the same server, but set upso that if the main SQL tables across the network change, or the tables onthe web server change, the two seperate servers willsyncronise/update/trigger updates to the tables on each other?We are running SQL server 2000.AHAJohn
View 2 Replies View RelatedRecently I was stumped on a problem where I was granting permissions to a user, from within a script that was creating a stored procedure. Then I would check the permissions on the procedure, but the permissions were empty. It turned out that I was missing a "go" statement to seperate the create procedure statement from the grant statement. So that got me to thinking about what other kids of statements must be seperated into seperate batches ? I would thik that anything that is being created must be seperated from any statements that grant or alter permissions, because the items would need to exist first.
comments ?
We have a table with an indesx that is varchar(7). It contains up to 5 numbers and 2 characters to denote batches. IE. 100a
105zz
1c
2
10001w
In an Access gui I need to be able to sort this field numericaly so each number shows up in its correct position. IE.
1c
2
100a
105zz
10001w
In order to accomplish this, I created a seperate field on the table named Num as varchar(5). I would like to create a trigger that updates this field any time a new batch number is entered. This way I can have my queries order by Num when returning the recordsets to the GUI.
Below is the code that I thought would work, but it is giving me an error near the keyword BEGIN after the IF(ISNUMERIC) statement. I can't seem to find an error with this code, but I thought maybe a thousand fresh sets of eyes could. Any help would be greatly appreciated. Also if you know of a better way to accomplish this task, please feel free to post here.
TIA,
Aaron
CREATE TRIGGER [InsertNum] ON [dbo].[IP_Batch_Table_Temp]
FOR INSERT, UPDATE
AS
DECLARE @Num varchar(7)
DECLARE @Num1 CHAR(7)
DECLARE @x INT
SET @Num = (SELECT IP_Batch_Number FROM inserted)
SET @x = 1
WHILE (@x < len(@Num))
BEGIN
IF (ISNUMERIC( SUBSTRING(@Num, @x, 1) ) )
BEGIN
SET @Num1 = @Num1 + SUBSTRING(@Num, @x, 1)
END
SET @x = @x + 1
END
UPDATE IP_BATCH_TABLE_TEMP SET NUMBER = ltrim(rtrim(@Num1)) WHERE IP_BATCH_NUMBER = @Num
I have a project were I will have it so that users can sign in and change information on an SQL server. The catch is that this site will be from a different domain name and from a different hosting company then where the SQL database is located. Sorry if this is a dumb question but how can I utilize asp.net to change and view an SQL database that is located else-where.
For example: a user logs into www.something.com and he/she can view and edit SQL tables from www.somethingelse.com's database.
Thanks in advance.
Hi All.
I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query.
This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have the table below:-
Column Name Data Type
OrderID intOrderNo intInvoiceNo varchar(50)PurchaseDate smalldatetimeCost moneyInsure money
I wish to have a dropdownlist on an aspx page display the OrderNo and the Cost e.g. 12345 £3.50. I have tried the SQL Statement below but that just calculates the addition of both.
SELECT OrderNo + ' ' + Cost AS Expr1
FROM [Order]
Do i have to convert them both to string/varchar first? If so, how?
Thanks for your replies.