CREATE FUNCTION [dbo].[Compare]
(
@usrVal varchar(100),
@dbVal varchar(100)
)
RETURNS BIT
AS
BEGIN
DECLARE @flag BIT
DECLARE @userStrLen INT
DECLARE @dbStrlen INT
DECLARE @counter INT
--Default assignments
SET @counter=1
Set @userStrLen=len(@usrVal)
Set @dbStrlen=len(@dbVal)
IF (@userStrLen != @dbStrlen)
BEGIN
return 0
END
ELSE
BEGIN
WHILE @counter <= @userStrLen
BEGIN
IF (substring(@usrVal,@counter,1) != substring(@dbVal,@counter,1))
RETURN 0
SET @counter = @counter + 1
END
return 1
END
RETURN @flag
--SELECT dbo.Compare('sqlserver user defined functions','sqlserver is the best backend')
END
When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.
Create and populate table: create table maxtable ( tablename varchar (18) not null, [...] )
Try to insert into test3: insert into test3 (name, tbname) select i.name, o.name from dbo.sysindexes i, sysobjects o, maxtable m where i.indid > 0 and i.indid < 255 and i.id = o.id and i.indid = 1 and o.name = lower(m.tablename)
And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.
The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.
Hi I wanna put string in query "where" part.For example,$sql="select VEHICLEFROM databaseWHERE MECHANIC =BRIAN";like above, "mechanic" column is filled with strings. Then how can Iwrite "where" part?Above query does not work.Thanks.
Ok, here is what i'm trying to do and its driving me nuts.
ok,
1) I have a proc that runs and needs to validate the user prior to running - this proc is called from an hand held device
2) the id and password are being passed as "clear text" but the password is stored in the database table hashed.
Is there anything on the db side that can get the hash value from the password column of the aspnet_membership table and compare it to the password being passed in to this proc? I have suggested several options to the handheld developer but nothing. This has to be done on the database side.
so,
username and password are passed to proc from handheld.
proc needs to validate ther user in the aspnet_membership table
if user id and password are valid execute the stored procedure
is this possible? if so can ANYONE point me to some examples of this being done?
I have used a query statement with the following WHERE string to 'Fill' a dataset. "AND (A.ApptsDate > '" & strApptPreDate & "' OR (A.ApptsDate = '" & strApptPreDate & "' AND A.ApptsTime >= '" & strApptPreTime & "' ))" & _ and strApptPreTime is defined as:Dim strApptPreTime As String = SomaShared.strPadTime(CStr(dApptCalcNewDate.Hour) + ":" + CStr(dApptCalcNewDate.Minute))
Somehow, the dataset showed only the ApptsTime after 10 am. After more than 2 weeks of debugging, I still can see a dataset watch for > 10 amAppsTime only. Now I am guessting, the problem is 9 is different from 10 - 16, it all because 9 is single digit. Then I check the data type settings for these variables. Here are what I found: In SQL Server Agent job, the ApptsTime data was 'inserted' by @NewApptTimes, which is declared as char(5).In SQL Server database, the ApptsTime was defined as nvarchar(15). My question are: 1. The reason why there were no 9 am data for the dataset, is becasue 9 am of nvarchar(15) is not > 8:30 of strApptPreTime? 2. If the answer to the quation 1 is yes, how do I define AppsTime and/or strApptPreTime? TIA,Jeffrey
Hi All, I have a column in my table like so: 'D4B00 L2A00 L3A00 L6C00 P1C00 L2A28 P4B00 ' How do I check in SQL if any pieces have the first 3 character the same. In the above case, L2A is present twice. I need to do this because I need display disctinct items, therefore L2A needs to be displayed only once. Any help is appreciated. Thanks
Hi,I have a funny Error in our sql server 7 & 2000. When I compare twostring in Unicode format. I receive that it is the same when I add newnchar.I dont understand why?Could you help me ?declare @str nvarchar(128), @str2 nvarchar(128)Set @str =nchar(21121)+nchar(49352)+nchar(47811)+nchar(48256 )+nchar(4966)+nchar(25736)+nchar(1788)+nchar(51220)+nchar(17733)Set @str2 =nchar(21121)+nchar(49352)+nchar(47811)+nchar(48256 )+nchar(4966)+nchar(25736)+nchar(1788)+nchar(51220)+nchar(17733)+nchar(4 1273)select @strselect @str2SELECT DIFFERENCE(@str, @str2)if soundex(@str) = soundex(@str2)BEGINSELECT 'OK'ENDELSEBEGINSELECT 'KO'ENDif @str = @str2BEGINSELECT 'OK'ENDELSEBEGINSELECT 'KO'END-=zoltux=-*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int) RETURNS VARCHAR(100) AS BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') + CAST(quantity AS varchar(5)) FROM flexing_stock_transactions WHERE item = @item AND week = @week GROUP BY quantity ORDER BY quantity
RETURN @Output
END
how can I pass the variable @item correctly for the string comparison
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
We're converting to new student info system. Sometimes registrar entered the same school into the schools table but spelled it differently. Trying to find all student assigned transfer credits from the same school but the school name is different. My db shows a max of 9 different schools students have rec'd transfer credits. Spending too much time trying to figure out best way to do it w/o a ton of IF stmts. Looking at Soundex and Difference functions. Still looks like a lot of coding. how to compare up to 9 string variables in sqlserver 2008?
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers. OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error". OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954. http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
I have to supply a sqlserver connection string in the format:
ip address:port, how do i go about finding out what the port number is on the sqlserver server ??? This is for an R25 collegent interface connection, if that helps .
For nearly 6 wks I have been trying to workout with GoDaddy.com a connection string that will connect to the SQL Server 2000 from my ASP.Net web application. In my programming, I have placed the connection string in the web.config file and then call it from there within code behind using the SQLConnection object. My web.config entry has the following form: <appSettings> <add key="Conn" value="server=GoDaddyserver;integrated security=SSPI;database=databaseonGoDaddyserver;" /> </appSettings>I have also written the key supplying the UID and pwd. Nothing works and their support is of no help.Has anyone else had this difficulty either in general or with GoDaddy in particular?
I need to conect my software from a remote client to a server, mystandard connection:Provider=SQLOLEDB.1;Persist Security Info=False;UserID=sa;Password=;Initial Catalog=MyDb;DataSource=192.168.0.100;Network=DBMSSOCN;andProvider=SQLOLEDB.1;Persist Security Info=False;UserID=sa;Password=;Initial Catalog=MyDb;DataSource=192.168.0.100;Network=DBNMPNTW;make a error connection,i have tryed with ODBC connection and its OK only in Named Piped.Can any one help me write the right connection string?thanksrm
I have SQLServer 2005 runs well for months and stop working after install SqlServer2005 SP1. I try to reinstall the SQLServer 2005 but I have problem when install work station component on my and the error is "There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor". Please help me to fix this bug. I do not want to reformat my machine.
I found out that I could use SQLDMO to enumerate objects in a SQL server. However, I can't connect to my server, even though I have both "windows and SQL server" authentication on "(local) Windows NT".
Here is the code (it does not contain the validation in this snippet):
Imports System.Data.SqlClient Imports SQLDMO
Public Class ADO_2 Inherits System.Web.UI.Page Protected WithEvents txtQuery As System.Web.UI.WebControls.TextBox Protected WithEvents txtresults As System.Web.UI.WebControls.TextBox Protected WithEvents lstDB As System.Web.UI.WebControls.DropDownList Protected WithEvents lblResult As System.Web.UI.WebControls.Label Protected WithEvents cmdExecute As System.Web.UI.WebControls.Button Protected WithEvents cmdNext As System.Web.UI.WebControls.Button
Private m_objConn As New SqlConnection() Private m_objSQLServer As SQLDMO.SQLServer
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
EnumerateDatabases()
'TEMP lstDB.Items.Add("Northwind") lstDB.Items.Add("Pubs") 'END TEMP End Sub
Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Unload
m_objConn.Close() 'Just in case..... End Sub
Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click
Dim objCommand As SqlCommand Dim objDataReader As SqlDataReader Dim intField As Integer
objCommand = New SqlCommand(txtQuery.Text, m_objConn) objDataReader = objCommand.ExecuteReader
While objDataReader.Read txtresults.Text &= vbNewLine For intField = 0 To objDataReader.FieldCount - 1 txtresults.Text &= objDataReader(intField).ToString.PadRight(15) Next End While
m_objSQLServer = New SQLDMO.SQLServer() m_objSQLServer.Connect("radu")
'Iterate through all databases on the target server: For Each objSQLDatabase In m_objSQLServer.Databases lstDB.Items.Add(objSQLDatabase.Name) Next objSQLDatabase
EnumerateDatabasesExit: objSQLDatabase = Nothing m_objSQLServer.Close() m_objSQLServer = Nothing Exit Sub
EnumerateDatabasesError: lblResult.Text = Err.Number.ToString & ", " & Err.Description Resume EnumerateDatabasesExit End Sub End Class
Why can't I connect with m_objSQLServer.Connect("radu") ? I also tried m_objSQLServer.Connect("radu", "", ""). Otherwise, I can connect just fine with m_objConn.Open() (after I select a db from the dropdownlist, which is for now populated by hand)....
I just can't get it to connect to the database. Says something that remote connections are not allowed. I've done the Area thing and allowed all connection including remote connections.
I'm writing a java application that queries sqlserver 2005 (and 2000). In case the sql browser service is stopped I need to use the named pipe property or the port property, right? But how could the program know at runtime what is the port and what is the pipe name? And how should a connection string to sqlserver 2005 (and 2000) that includes named pipe, look like?
data source=RemoteHostName;initial catalog=myDb;password=sa;user id=sa; Max pool size = 200;
And now strange thing is happening ,, I am receiving error :
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
The SqlServer Activity Manager is telling that only 100 connections are pooled, and I guess that the Max pool size is 100, It is not being changed by my Connection string. As I am trying to change the default 100 pool size value to 200.
Huh , So stucked up , how to increase the Max pool size.. Is there any way .
I currently have SQL Server Management Studio Express 2005 and would like to upgrade my machine to SQL Developer 2005 as easily as possible. Keeping my databases and such.
Any recommendations on the best way to do this would be greatly appreciated.
Hello Im pretty new to development so..... be nice guys. I have VS2005 pro and SQL Server 2005 developer edition installed. When I open VS, in the server explorer i can connet to databases located on the SQL directory (SQL Server runs perfectly) But when i try to add an sql server to my project( add new item --> SQL database) it gives me an error saying "Connection to SQL server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component......" HELP! donno what VS wants!
Just wondering if there are any tools available for SQL Server 2005 which allow the comparison and scripting of data and schema between two databases. This is so that I can migrate between Dev, QA, and Live easily.