Hi,
I have a string like (Mike,David,John...). How can I change this string into a proper syntax of
('Mike','David','John'...) so that I can use it in an "IN" statement some where else.
Select * from TableName where Customer_Name In ('Mike','david','john',...)
Any function? Any Idea?
Thanks
We got data from a dns server that looks like:
some columns.... |121.12.250.2.somestring......|
The ip address should look like 2.250.12.121:
We could use charindex, substring and len to cut the 4 parts of the ip and rebuild it in the right format.
Starts with
SELECT
CHARINDEX('.',mycolumn)
,LEFT(mycolumn, CHARINDEX('.',mycolumn) - 1) as part_four_of_ip
,CHARINDEX('.', SUBSTRING(mycolumn,CHARINDEX('.',mycolumn) - 1),len(mycolumn)
as position_of_second_dot
........ and so on.....
This will cause an awful long code. How to shorten that code?
I have a SSIS Package which I developed on my own my database server.
But now the package is to deployed on 3-4 different computers pointing it to different databases.
I do not want to change the connection string for each and every instance because in future there may be more number of instances running.I tried using the Configuration file and add that file in to the configuration window but that also did not work.Please can anybody help me?
To change string '24.04.2013' (Apr. 24, 2013 format of dd-mm-yyyy) into a date in format of mm-dd-yyyy, what's the easiest way?
View 5 Replies View RelatedHi,
I am looking to dynamically change the connection string in my SSIS package, to avoid changing the connection string each time I want to run in different environments.
Thanks in advance
I've recently uploaded my website, http://www.bigredsongbus.com, to my host. Unfortunately, they don't support the use of SQL Server Express. So, I've purchased an addon through my host - discountasp.net. I've attached my database file to their server, now all that I need to do is to change the connection string in my .config file so that it points to the SQL Server. I don't know how to do this. Any help, please?Jeffrey Way
View 2 Replies View RelatedI know that connecttimeout and commandtimeout are separate entities. Is it possible to change the default command timeout value by changing the connection string?
I need to increase the command timeout and want to know if I can do it without changing my code and rebuilding my ASP.NET 1.1 web app.
Thanks in advance. -- ZLA
I have a query which runs exactly as I want it to. Let's say it returns this in a table:
IDNumber
123
212
312
I want to change (find and replace?) all the number 12s in the 'Number' column for a string. I want to change the 12s to the word: HelloWorld. I don't want to update the table, merely the table that is being returned in the query.
HiWhen we installed SqlServer2000 we left the default collation name(Sql_Latin1_General_CPI_CI_AS).The user defined databases we created afterwards were defined with adifferent collation name in order to be able to accept the character setwe use, Hebrew.We are looking into switching DTSs that we use to copy data from ourmain system , that uses an Ingres database, into OSQL scripts. Althoughthe DTSs successfully copy the Hebrew letters when I copy data with OSQLit comes over as jibberish.After looking into the matter I came to the conclusion that while DTSrefers to specific databases and uses the destination database'scollation name , OSQL refers to the remote server and destination serverand therefore uses the collation name of the server and not of thedatabase. In order for it to successfully copy the Hebrew I need tochange the default collation name of the installation.Is "rebuild master" the way to do such a thing ? (this is a productionserver so we are wary of doing a "rebuild master")Has anyone else run into similar problems when transfering data betweenservers using OSQL ?ThanksDavid Greenberg
View 3 Replies View Relateddid VARCHAR(20)
SELECT top 10 CAST(did AS INT) FROM table
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.
SELECT CONVERT(INT, did) FROM table
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.I found a SQL Function that removes all the alphabetic characters from the string and just leaves the integers.
This seems to be a rather old problem (http://www.themssforum.com/SQLServer/Does-empty/) but I couldn't find an answer yet.
The problem is: I have two tables t1 and t2 where t1 is a staging area of t2.
t1: (id int not null, phone varchar(30))
t2: (id int not null, phone varchar(30))
Data in t1: (1, '') <- empty string
Data in t2: (1, ' ') <- a blank
Comparing t1.phone with t2.phone results in equality which in my opinion isn't correct.
The question ist: How can I change the behaviour of SQL-Server to result in inequality so that the change in my staging table is detected correctly?
Thanks in advance
Fridtjof
Hello,
I have searched every post and tried every connection string there but can't figure out how to connect to my database on my remote server. I am using Visual Developer 2005 Express Edition in C# and uploading it to a Windows server running asp.net 2.0. The domain has not resolved yet.
I am so new to this and have followed many tutorials step by step but none of them address this issue. They just show how to upload it to the server.
Do I need to use the SQL server provided by my host or can the database stay in the App_Data folder?
My local connection string works locally:
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|add_newSQL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
When I uploaded to my server I changed SQLEXPRESS to (local) as advised in the forum.
<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|add_newSQL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
When I debug a page I get the <customErrors mode="Off"/>
error message even thought I have already set it in my remote web.config file which may be causing problems itself:
<configuration>
<appSettings/>
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|add_newSQL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<customErrors mode="Off" />
<compilation debug="false" />
<authentication mode="Windows" />
</system.web>
</configuration>
Thanks for any help you can offer. I wish I could find someone to hire to do this part for me or teach me how over the phone. Any suggestions?
Hi Folks ...Question for everyone that I have not been able to figure out. I have an application that is spread across tiers:SQL Connection defined in Web.Config file that connects to SQLServer database.DAL layer created references SQL Connection in Web.Config file - have noticed this copies the connection string information to the local properties for each TableAdapter that is defined in the DAL layer.BLL Layer that references Table Adapters declared in the DAL layer.When the web site is called, the link will provide an encoded id.Sample call to website: http://www.mysamplesite.com/default.aspx?company=AE2837FG28F7B327Based on the encoded id that is passed to the site, I need to switch the connection string to use different databases on the backend.Sample connection string: Data Source=localhost;Initial Catalog=dbSystem_LiveCorp1;User ID=client;Password=live2006 I would need to change Initial Catablog to the following:Data Source=localhost;Initial Catalog=dbSystem_LiveCorp196;User ID=client;Password=live2006How do I do this and have the connection string reflected in all of the Table Adapters that I have created in the DAL layer - currently 100+ Table Adapters have been defined.As we gain new clients, I need to be able to have each clients information located in a different database within SQL Server. Mandated - I have no choice in this requirement. Being as I don't want to have to recreate the DAL for several dozen clients and maintain that whenever I make a change to the DAL to then replicate across multiple copies. There has to be a way to dynamically alter the SQLConnection and have it recognized across all DAL TableAdapters.I'm developing with MS-Visual Studio 2005 - VB. Any help would be greatly appreciated. Thanks ...David Any day above ground is a good day ...
View 1 Replies View RelatedDo I need to change connection string if I upgrage SQL server from 2000 to 2005 in ASP.NET 2.0?
View 1 Replies View Relatedi am trying to take a field that has part of a date in it, so I have to parse it out as follows:
SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
CAST((SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)) as datetime)
I also tried CONVERT(datetime, (SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)), and got the same message.
how I can parse that field, then convert it to a datetime format for running a DATEDIFF statement?
I have my SSIS package that reads elements from a Sharepoint list to a SQL table. The data type of my source is string and the destination is integer. The source column can sometimes be an empty string "" (not a required column)
Which expression to use and "where" or "what" SSIS component can I add this expression to? Like I know that I can use the "Conditional Split" to filter the data so for my expression which component can I use?
Hello,
I am trying to extract from some strings like the following strings the number and order them by that number:
Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)
The problem I am having is that I already extracted the number using
Substring([field],[starting position],[lenght])
but the output seems to be in a string format, so the order is not in an ascending order.
Thanks for any suggestions.
One of my excel 2013 power pivot report was migrated from old server to new server after migration i changed the excel power pivot connection string to connect with new server but the workbook connections is still taking the old connection string of old server and there is no option of changing workbook connection string .
I am able to edit the powerpivot connection but workbook connections are not getting updated they are still taking old server connection string.
Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies View RelatedWe ran into weird/interesting issue with below details.
Version:Â Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)Â Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies View RelatedI have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset
at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
Can anyone please tell me what's wrong?
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
End Sub
End Class
I have a string 'ACDIPFJZ'
In my table one of the column has data like
PFAG
ABCDEFHJMPUYZ
KML
JC
RPF
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
My output should be:
PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
Hello All,
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Thanks a bunch in advance,
Chutikorn
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
I am trying to find a way to find a certian character in a string and then select everything after that character.
for example i would look for the position of the underscore and then need to return everthing after it so in this case
yes_no
i would return no
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View 2 Replies View RelatedI have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID Â in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.