Re: SQL Search within a table - Database Discussion Boards

Hello All,

I need to convert this value of datetime
2012-09-16T20:08:30Z to this format "mmddyy".

I tried
select convert(char(10),'2012-09-16T20:08:30Z',101) - but this did not work.

Is there any other method to try before trying to write a whole bunch of casts, replaces!

vanikanc wrote:
I need to convert this value of datetime

2012-09-16T20:08:30Z to this format "mmddyy".

Doesn't seem a valid datetime format to me

As a string-function use:

SQL

SELECT LEFT('2012-09-16T20:08:30Z', 10) ...

With values in datetime-format:

SQL

SELECT CONVERT(DATE,@datetime)

------------------------------
Author of Primary ROleplaying SysTem

How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.

I Would like to ask about sql server commands and materials,could you provide me some topics related to sql server

Thanks,

MSDN SQL Server Developer Training[^] is a good place to start.

But you didn't Google[^] that, did you?

Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.

Start from here:
http://www.w3schools.com/sql/default.asp[^]
http://www.microsoft.com/en-in/SQLserver/default.aspx[^]
http://learnsqlserver.in/[^]

Start to learn about:
- Various query types - CREATE, UPDATE, DELETE, ALTER, SELECT, INSERT, GROUP BY , HAVING etc
- Various query functions - COUNT, LENGTH, LTRIM, RTRIM, SUBSTRING, etc
- Various query statements - Distinct, Union, Union all, Intersect, Order by, etc
- Various query grouping and execution - Store procedures, Trigger, Cursor, Views, etc

Try!

Sandeep Mewara
Microsoft ASP.NET MVP

[My latest Article]: Server side Delimiters in ASP.NET[^]

Good day,

I have some problem with my SQL Commands in access, really hope that you can help me out.

Category

CatID    Category Name       Sub- Category   Description

1        Beverages                            123
2        Condiments                           456
3        Juice               1                help
4        Coffee              1                pls

I wanted to display the main category of the Juice which is beverage. No matter how hard i tried, i can't display the Main category in access.

Really hope if any kind soul could help me with this

Regards
Veon

Could you please show your SQL.

Sorry its kinda messy, its from access.. the bold part is the 1 have issue. once againg sorry

SQL

SELECT Product.[Electronic Product Code], Product.[Item Name], IIf([parent - category] Is Null,[name],[Category].[name]=[category].[Category ID].[name]) AS Category , IIf([Parent - Category] Is Not Null,[name],&quot;&quot;) AS SubCategory, Location.Country, Location.Building, Location.[Street Address], [Tracking Record].Timestamp, [RFID Reader].[Reader ID]
FROM Location RIGHT JOIN ((([RFID Reader] RIGHT JOIN (Product LEFT JOIN [Tracking Record] ON Product.[Electronic Product Code] = [Tracking Record].[Electronic Product Code]) ON [RFID Reader].[Reader ID] = [Tracking Record].[RFID Reader ID]) LEFT JOIN Has ON Product.[Electronic Product Code] = Has.[Electronic Product Code]) LEFT JOIN Category ON Has.[Category ID] = Category.[Category ID]) ON (Location.Latitude = [Tracking Record].Latitude) AND (Location.Longtitude = [Tracking Record].Longtitude)
WHERE (((Product.[Electronic Product Code]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxEPC] &amp; &quot;*&quot;) AND ((Product.[Item Name]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxProductName] &amp; &quot;*&quot;) AND ((Category.[Category ID])=[forms]![Search History]![cmbCat])) OR (((Product.[Electronic Product Code]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxEPC] &amp; &quot;*&quot;) AND ((Product.[Item Name]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxProductName] &amp; &quot;*&quot;) AND (([forms]![Search History]![cmbCat]) Is Null)) OR (((Product.[Electronic Product Code]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxEPC] &amp; &quot;*&quot;) AND ((Product.[Item Name]) Like &quot;*&quot; &amp; [forms]![Search History]![tbxProductName] &amp; &quot;*&quot;) AND (([forms]![Search History]![cmbCat]) Is Not Null) AND ((Category.[Parent - Category])=[forms]![Search History]![cmbCat]));</pre>

veon cheng wrote:
I wanted to display the main category of the Juice which is beverage. No matter how hard i tried, i can't display the Main category in access.

SELECT a.CategoryName
FROM Category a INNER JOIN Category b On a.CatId = b.SubId
WHERE b.SubCategory = 'Juice';

------------------------------
Author of Primary ROleplaying SysTem

How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.

Hi all,

I am trying to connect db file from a pen drive. it is fine when using on my local machine. But giving following error when trying it on my client machine. i am using vs2008 and mssql express 2008. Mssql express 2008 also installed on my client's machine.
Type of application: Window application.

Error:Failed to generate a user instance of sql server due to a failure in startng the process for the user instance. the connection will be closed.

i have deleted mssqlexpress folder form the location:
C:\Documents and Settings\username\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\

and restarted machine, but still getting the same error.

Connection string: " Data Source='" & (SqlServerName) & "';AttachDbFilename=" & driveName & "dbfolder\dbname2.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

if i remove the "User Instance=True" from the above connection string it gives me following error:
"Create database permission denied in dataase 'master' an attempt to attach an auto named database for file failed. A database with the same name exists, or specified file cannot be opened, or it is located on usc share."

Kindly help me.

Thanks

rmshah
Developer

r_mohd wrote:
if i remove the "User Instance=True" from the above connection string it gives me following error:

Do you need a user-instance, or not?

Microsoft SQL Server 2005 Express Edition (SQL Server Express) supports a new feature, the user instance, which is only available when using the .NET Framework Data Provider for SQL Server (SqlClient). A user instance is a separate instance of the SQL Server Express Database Engine that is generated by a parent instance. User instances allow users who are not administrators on their local computers to attach and connect to SQL Server Express databases. Each instance runs under the security context of the individual user, on a one-instance-per-user basis.

I assume that Sql Express or Sql Server is installed on the machine? Can you post the stacktrace of the exception?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

r_mohd wrote:
I am trying to connect db file from a pen drive.

That needs more explanation.

You have at least the following parts
1. MS SQL Server
2. The location of the MS SQL Server files.
3. The Visual Studio application.

Is the above list a COMPLETE list of everything that you are attempting to run?
From the above list what EXACTLY is on the pen drive?
From the above list what EXACTLY is NOT on the pen drive?

r_mohd wrote:
i have deleted mssqlexpress folder form the location:

As stated that is just flat out wrong. You can't just delete that directory.

If SQL Server can't see the database then nothing you do in Visual Studio is going to fix that.

r_mohd wrote:
Connection string: " Data Source='" & (SqlServerName) & "';AttachDbFilename=" & driveName & "dbfolder\dbname2.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Certainly looks like a back slash is missing to me.

Thanks jschell,

MS SQL Server is installed on client's machine.

The location of the MS SQL Server files. it on client's machine(C drive) and data file and log file lying on pen drive.

The Visual Studio application is lying on client's machine(c drive).

The connection string is fine as it works on my machine.
i am using a pen drive for data file and log file.

Thanks again.

rmshah
Developer

r_mohd wrote:
and data file and log file lying on pen drive.

And you can go into SQL Server Management Studio and create a database using that drive? On your box? Notice that has nothing to do with your application. It is just a test to insure that SQL server accepts that.

Hi friends. I have a small software that uses an access database. I use it at home but i wonder if i can use it from anywhere by reaching the db over internet. Should i change any code to do so. By the way if Access doesnt support this function, i can replace my db with sql server.

Bad idea; Microsoft Access is not meant to be used as a server, and it ain't as secure as Sql Server. I'd even recommend against using Sql Server over the internet, would be safer to have IIS on the same server and provide access to the DB using webservices.

I can imagine you don't want to "waste time" with writing a useless layer. If my memory serves me correct, you'd open port 1433 on the firewall. Updating from Access to Sql Server should be easy; if you open the DB in Access, you'll have a button in that lint that says "upgrade to Sql Server" - that launches a wizard that recreates all your tables in Sql Server.

Hope this helps Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

leone wrote:
I use it at home but i wonder if i can use it from anywhere by reaching the db over internet.

As stated no.

MS Access is a file based server. Thus if you have software that uses MS Access then the file must be visible in file system of the client machine.

Thus if you want to run the software on machine A then on A you MUST be able to open explorer and see the database file.

That means that you would need to enable file sharing over the internet. You might be able to do that depending on the machines at either end and how they are connected.

leone wrote:
i can replace my db with sql server.

MS SQL Server is accessible via the internet as long as the server machine is accessible. That is not guaranteed (machine is visible) but that is a different discussion.

It is possible to secure MS SQL Server so it can be exposed on the internet however either you must presume that your data is not secure and not important or you are going to need to learn a lot more about internet security and SQL Server before using it. It probably isn't worth learning that since it isn't a skill that businesses want.

Thank you friends for your answers. I decided to hire a server machine and put everyhing on it.

Don't understand why this was downvoted, (over)compensating.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Access can only be used remotely on MS Sharepoint websites, but you could uses Access as a data store behind a hosted web application (though I would suggest the use of SQL server).

Alternatively, you can use Access as the front end to a hosted SQL server database, so your user interface is local, but links to the remote database.

Many web service providers offer MSSQL and/or MySQL databases

====================================
Transvestites - Roberts in Disguise!
====================================

Hey.. I am developing an windows form application using ms sql server 2008. and i want to connect my sql server to cloud at internet to use it as backup. if there is any changes made bu application in local server then it should be reflected on cloud also. and if there is any loss of data at local server then it should get backup automatically from cloud.

Can someone help me in this please.. ??

You seem to be describing replication. See if your cloud provider will support that type of configuration. Sounds like overkill to me.

You can build a very robust database server configuration locally and maybe use the cloud to store your database backups. Before shipping the databackups to the cloud you might want to investigate data encryption. I don't know the type of data your are working with, but I wouldn't want my personal information stored in the cloud without being encrypted.

KUNWAR999 wrote:
Can someone help me in this please.. ??

You already received help in the C# forum. Pete posted the best answer, as far as I can judge.

Did you try his answer, what where the results, and why are you repeating the question? It'd be appreciated if you at least included a link to the prior question, otherwise I'll simply start copy/pasting the old answers to your question. Same question -> same answer.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Hi All,

I'm trying to send an email using a SQL procedure within SQL Anywhere 11. I have the following SQL statements to do this:

ALTER PROCEDURE "DBName"."WebFormNotify"()
begin
call xp_startsmtp('*A*@***.org', 'SMTP_Relay_server_name');
call xp_sendmail(recipient = '*B*@***.org',subject = 'SMTP SYBASE TEST',"message" = 'SMTP SYBASE TEST');
call xp_stopsmtp()
end

This is being ran from the server that has SMTP Relay setup on it. I am able to use telnet and send an email successfully. The procedure runs with no errors but the email is never recieved by the recipient. I have also replaced the second parameter passed into xp_startsmtp with the name of the actual SMTP server itself, but still no email was recieved by the recipient.

Any help on this would be really appreciated. Pleas elet me know if additional information is needed.

Thank you,
Mel

I'd start by capturing the return code from the calls. The MAPI errors will likely tell you what is failing. At a guess, it is possible that some form of authentication may be necessary to get this to work. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]