|
i am geting value from datbase with this code CONVERT(varchar(11), TH_DT_HolidayDate) AS Expr1 and output is jan 15 2009 . but i want output like jan 15,2009 .how can i do this .thnx in advance.
|
|
|
|
|
You could try something like:
SELECT ...
datename(month, TH_DT_HolidayDate)
+ datename(day, TH_DT_HolidayDate)
+ ', '
+ datename(year, TH_DT_HolidayDate) AS Result
...
|
|
|
|
|
thnx friend its working......
|
|
|
|
|
You're welcome
|
|
|
|
|
i want to know how to generate regular expressions for sql query
|
|
|
|
|
If you mean that you want to use regular expressions in the query, you can create CLR based user defined function to handle regular expressions.
But if you mean that you want to parse SQL using regular expressions, I think it's not the right way to go (if you really want to fully parse a statement). Typically parsing is done using tools like YACC[^] and lex[^].
|
|
|
|
|
To do what? If you just want to validate a query you can run with noexec on, it just parses it then.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
|
Hi,
Tina P wrote: SQL server 2005 express edition (120) day trial
If you installed Express Edition, it's actually not a trial and won't expire after 120 days. It's free to use.
Tina P wrote: its not showing me the database that comes with the SQL server basis install (such as Northwind and Punlishers etc)
First thing is that os the database runniong. Check your services (SQLExpress...) to make sure it's started. After that, you have to create a connection:
- open server explorer
- select Data Connections , right click and select Add connection...
- in the dialog define the server name for example in format machinename\instancename. You can check the instance name from your Windows services (the instance name is in parenthesis).
After that you should have a connection.
Then another thing is that there are no sample databases shipped along the server anymore. You can download and instal AdventureWorks (which is a newer sample database) from: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=18407[^]
Hope this helps,
Mika
|
|
|
|
|
Here is the problem:
I have a .net application (a button runs a stored proc, which in turn runs the SSIS package). I need to add a dialog so that each time someone wants to change or use a different flat file source, no problem they just browse to the location of the file and the flat file name and location are added to my flat file connection via the .NET application ,which all the files are in the same formatting. I am not sure where to start with the variables. >.NET dialog passed to Stored proc > passed to variable in SSIS package. I am not even sure how to do the stored proc to SSIS package in a flat file connection. Any help would be appreciated.
|
|
|
|
|
I am sending data from SQL 2005 database to AS400. What I want is, to delete all the records present in the AS400 table prior to the insertion. Is there a way to accomplish this? Something like deciding sequence for tasks.
May be a real beginner's question.
The word "politics" describes the process so well: "Poli" in Latin meaning "many" and "tics" meaning "bloodsucking creatures."
जय हिंद
|
|
|
|
|
Drop Execute Sql task and connect it to the task that actually writes task in AS400. You can execute any command you wish with execute sql task.
|
|
|
|
|
Add SQL task to the project as the first step and execute a "DELETE TableName" in that task against the AS400 connection.
|
|
|
|
|
Hello,
I'm creating a windows service that listens to a sql server database table each second when any insertion occurs this windows service make a lot of work, to accomplish this task I firstly think in the notification services but it creates an auto generated windows service which I can not edit to do my logic and in the same time it works(the notification services) just like my windows service in listening to the table every Generator Quanta.
The question is which is the best implementing my one windows service or using the notification services to send notifications to filesystemwatcher then my service check this file every second?
Thanks
Dad
|
|
|
|
|
|
Can SqlDependency detect when a row is inserted?
|
|
|
|
|
Yes, since if the result set based on the select statement is changed, the OnChange event is triggered. This actually means that all modifications (updates, inserts and deletes) are notified as long as they change the result.
Or at least this is my understanding
There's a bit old but quite good documentation about this: Query Notifications in ADO.NET 2.0
[^]
Mika
|
|
|
|
|
Hi!
I have a (almost) finished program using a database on an Sql Server 2005 Express. I don't have access to the program code but to the database. I need to track changes made (insert, upgrade, delete) in that database in order to compare them to changes made in my mobile version of that program. I am considering an easy solution for the changes on the device side, storing these changes in an XML file. How can I track these changes to the Express database? Is it possible via C# code or stored procedures or anything?
Thanks / Jacob
|
|
|
|
|
Is the mobile version using the same database as the other version of your program? Or is the idea that you have several databases and you want to compare their changes (I guess I didn't understand where the changes came from in the mobile version, from the program itself or the database).
If all the programs use one database, I think you could use triggers on the database side to gather (interesting) changes to the data.
Also one question is that do you want to compare the changes (and what would be the 'baseline') or do you want to compare current situations in different places (this sounds like you're trying to create a replication like logic).
|
|
|
|
|
Replication yes. I have an Sql Server Express DB and a compact .sdf file. Since the device is working offline I need to try and reinvent merge replication I think. I have never used triggers, is that the way to do it? Where are they implemented?
And yes, what I want to do is to track these changes, e.g. store them in a table seems an easy way pf tracking these events.
After googling a little I must point out that this DB has 165 tables. Do triggers have to be created for every single one?
Thanks for your help /Jacob
modified on Thursday, February 19, 2009 3:33 AM
|
|
|
|
|
Jacob Flarup wrote: I have never used triggers, is that the way to do it? Where are they implemented?
Triggers are created upon a table. Have a look at: CREATE TRIGGER[^]
You could for example take information from inserted and deleted virtual tables inside the trigger and sotre it in some other table. If you like, you could store it in xml.
Jacob Flarup wrote: Do triggers have to be created for every single one?
Yes they do, but if the logic is always the same, you could for example generate the trigger code for each table.
|
|
|
|
|
I believe what you want to do is create a set of triggers on the tables that you want to track changes and write values to a "changes" table. You would then need to write reconciliation logic to compare the values from your mobile device to your database. One way of handling this would be to compare timestamps of the two systems and have the latest timestamp win.
|
|
|
|
|
Ive done this so many times, I just cant seem to get my head around what's wrong!
I have a table with data which looks like this
Columns (in order): siteNo, cardSchemeCode, currencyCode, saleAmount, saleTotal, refundAmount, refundTotal
0000001 DEL GBP 12 240.05 0 0.00
0000001 JCB GBP 1 20.00 0 0.00
CU1000000001 DEL GBP 10 166.51 0 0.00
I am trying to get Xml that looks like:
<Root>
<Site siteNo="0000001">
<Currency currencyCode="GBP">
</Site>
<Site siteNo="CU1000000001">
<Currency currencyCode="GBP">
</Site>
</Root>
Instead I get:
<Root>
<Site siteNo="0000001" />
<Site siteNo="CU1000000001">
<Currency currencyCode="GBP" />
<Currency currencyCode="GBP" />
</Site>
</Root>
Here is my SQL:
SELECT 1 TAG,
NULL Parent,
Site.siteNo 'Site!1!siteNo',
NULL 'Currency!2!currencyCode'
FROM records [Site]
GROUP BY siteNo
UNION
SELECT 2 TAG,
1 Parent,
Site.siteNo,
Currency.currencyCode
FROM records [Currency]
INNER JOIN records [Site]
ON Currency.siteNo = Site.siteNo
GROUP BY Site.siteNo, Currency.currencyCode
FOR XML EXPLICIT, ROOT('Root')
Any help?
|
|
|
|
|
Hi,
Your query is otherwise correct, but you must add the ORDER BY clause since the FOR XML processes the row (almost) without no logic in the order they are in the result set. So the query would be:
...
ORDER BY 'Site!1!siteNo',
'Currency!2!currencyCode'
FOR XML EXPLICIT, ROOT('Root')
Mika
|
|
|
|
|
Hi all,
I am using SQL server 2000, I have a table with four columns (ID, Password, Name, contact).
This table is being used by a web application which is having a registration page, the registration credentials are inserted to above table in server.
Here I want to know how many users can simultaneously register. I mean to what particular count does database insert the records into the same table simultaneously.
Please address the scenario in detail if possible with example.
Regards,
ashok
|
|
|
|