Convert Access to SQL

The following is my code for Access... can someone help me convert it to sql:

My Connectionstring is"server=(local);database=Database;trusted_connection=true"

<%

@.PageLanguage="VB" %>

<%

@.ImportNamespace="System.Data" %>

<%

@.ImportNamespace="System.Data.OleDb" %>

<

scriptlanguage="VB"runat="server">Sub btnLogin_OnClick(SrcAsObject, EAs EventArgs)Dim myConnectionAs OleDbConnectionDim myCommandAs OleDbCommandDim intUserCountAsIntegerDim strSQLAsString

strSQL =

"SELECT COUNT(*) FROM tblLoginInfo " _

&

"WHERE username='" & Replace(txtUsername.Text,"'","''") &"' " _

&

"AND password='" & Replace(txtPassword.Text,"'","''") &"';"

myConnection =

New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _

&

"Data Source=" & Server.MapPath("login.mdb") &";")

myCommand =

New OleDbCommand(strSQL, myConnection)

myConnection.Open()

intUserCount = myCommand.ExecuteScalar()

myConnection.Close()

If intUserCount > 0Then

lblInvalid.Text =

""

FormsAuthentication.SetAuthCookie(txtUsername.Text,

True)

Response.Redirect(

"login_db-protected.aspx")Else

lblInvalid.Text =

"Sorry... try again..."EndIfEndSub

</

script>

Reformatted code:

<%@. Page Language="VB" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">

Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim intUserCount As Integer
Dim strSQL As String

strSQL = "SELECT COUNT(*) FROM tblLoginInfo " _
& "WHERE username='" & Replace(txtUsername.Text, "'", "''") & "' " _
& "AND password='" & Replace(txtPassword.Text, "'", "''") & "';"

myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & Server.MapPath("login.mdb") & ";")

myCommand = New OleDbCommand(strSQL, myConnection)

myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()

If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub

</script>

|||

The query language used by SQL(Transact-SQL) is very similar to which used by Access. There is no need to modify the sql command in your case. Just modify the connection string to point to your sql server:

<%@. Import Namespace="System.Data.SqlClient" %>

...

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand

...
myConnection = New SqlConnection("Data Source=myServerName\SqlInstanceName; Integrated Security=SSPI; Database=mydb;")

You can visithttp://www.connectionstrings.com, or refer to MSDN:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

|||

Don't have to, but should:

<%@. Page Language="VB" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">

Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim intUserCount As Integer
Dim strSQL As String

strSQL = "SELECT COUNT(*) FROM tblLoginInfo WHEREusername=@.UserName AND password=@.Password"

myConnection = New SqlConnection({Your Sql Connection string})

myCommand = New SqlCommand(strSQL, myConnection)

myCommand.Parameters.add("@.UserName",sqldbtype.varchar).value=txtUserName.text

myCommand.Parameters.add("@.Password",sqldbtype.varchar).value=txtPassword.text

myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()

If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub

</script>

Convert ACCESS code to SQL Server

Hi,
I m working on the project which was using MS Access but now i want to use SQL Server 2005.
But there are some queries which are not understood by me, so please help me in converting those.
Following is the query:

SHAPE {select stlcode , SampleTypeCode,InNo,NoOfSamples,RecdDistrict,RecdT al,DtRecd , RecdFrom, RecdNameDesg, PrimaryInNo, MicroInNo, WaterInNo from SInward order by SampleTypeCode,InNo } AS ParentCMD APPEND
({select SampleType, InNo, SrNo, StlCode, AStlCode, SampleTakenDt, FarmerNm, FarAddVil, FarAddPost, FarAddDist, FarAddTal , FarAddPin, SurveyGrNo, ReprArea, LastSeason, LastSeasonCrop, NextSeason, NextSeasonCrop, NextSeasonCrop22, AgeOfTree, LandProfile, DepthFrom, DepthTo, WaterSource, CollectdBy, SampleAccepted, LabSampleNo, HCNumber, MicroLabSampleNo, WaterLabSampleNo from SDetails order by SrNo } AS ChildCMD RELATE SampleTypeCode TO SampleType, InNo to InNo, stlcode to stlcode) AS ChildCMD

This query was previously written in MS Access and now I want it to be in SQL Server 2005, So please help me in solving this.
Thanx

Quote:

Originally Posted by sachinkale123

Hi,
I m working on the project which was using MS Access but now i want to use SQL Server 2005.
But there are some queries which are not understood by me, so please help me in converting those.
Following is the query:

SHAPE {select stlcode , SampleTypeCode,InNo,NoOfSamples,RecdDistrict,RecdT al,DtRecd , RecdFrom, RecdNameDesg, PrimaryInNo, MicroInNo, WaterInNo from SInward order by SampleTypeCode,InNo } AS ParentCMD APPEND
({select SampleType, InNo, SrNo, StlCode, AStlCode, SampleTakenDt, FarmerNm, FarAddVil, FarAddPost, FarAddDist, FarAddTal , FarAddPin, SurveyGrNo, ReprArea, LastSeason, LastSeasonCrop, NextSeason, NextSeasonCrop, NextSeasonCrop22, AgeOfTree, LandProfile, DepthFrom, DepthTo, WaterSource, CollectdBy, SampleAccepted, LabSampleNo, HCNumber, MicroLabSampleNo, WaterLabSampleNo from SDetails order by SrNo } AS ChildCMD RELATE SampleTypeCode TO SampleType, InNo to InNo, stlcode to stlcode) AS ChildCMD

This query was previously written in MS Access and now I want it to be in SQL Server 2005, So please help me in solving this.
Thanx

That is not simple thing, most of the query you have to change.

1.Access Date in where clause will be # change to
2.Access IIF condition, change to CaseBlab blab.|||

Quote:

Originally Posted by hariharanmca

That is not simple thing, most of the query you have to change.

1.Access Date in where clause will be # change to
2.Access IIF condition, change to CaseBlab blab.

Thanxs for u r reply...
now i can think that way...sqlsql

convert / group by date

Hi,
I have a datetime column named dtDateTime.
its format is "Oct 27 2006 12:00:00 "
I want to group by only date part of it and count

my code is

$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))

FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId

WHERE (J1708Data.iPidId = 303) AND
(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescription = $VehicleID)

GROUP BY convert(varchar,J1708Data.dtDateTime,120)";

However, convert part, group by part doesnt' work at all.
(i couldn't check count part)

can you find where's the problem?
Thx.kirke wrote:

Quote:

Originally Posted by

Hi,
I have a datetime column named dtDateTime.
its format is "Oct 27 2006 12:00:00 "
I want to group by only date part of it and count
>
my code is
>
>
$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))
>
FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId
>
WHERE (J1708Data.iPidId = 303) AND
(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescription = $VehicleID)
>
GROUP BY convert(varchar,J1708Data.dtDateTime,120)";
>
>
However, convert part, group by part doesnt' work at all.
(i couldn't check count part)
>
can you find where's the problem?

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

I'd have done it like this (use VARCHAR(10) or CHAR(10) for the date
instead of an unspecified size):

SELECT CONVERT(VARCHAR(10),J.dtDateTime,120) As theDate,
COUNT(*) As theCount
FROM Vehicle As V INNER JOIN J1708Data As J
ON V.iID = J.iVehicleId
WHERE J.iPidId = 303
AND J.dtDateTime BETWEEN '2006-10-25' AND '2006-10-28 23:23:59'
AND V.sDescription = @.VehicleID
GROUP BY CONVERT(VARCHAR(10),J.dtDateTime,120)
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUrBwIechKqOuFEgEQKUTQCg1zGcAeAViDrJQWxENdcn2t xbhxYAoO4o
1Mks6W+FiXviMMrZi/lt4e3z
=vWR9
--END PGP SIGNATURE--|||On 2 Nov 2006 10:53:08 -0800, kirke wrote:

Quote:

Originally Posted by

>Hi,
>I have a datetime column named dtDateTime.
>its format is "Oct 27 2006 12:00:00 "
>I want to group by only date part of it and count
>
>my code is
>
>
>$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
>count(convert(varchar,J1708Data.dtDateTime,120))
>
>FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId
>
>WHERE (J1708Data.iPidId = 303) AND
>(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
>AND (Vehicle.sDescription = $VehicleID)
>
>GROUP BY convert(varchar,J1708Data.dtDateTime,120)";
>
>
>However, convert part, group by part doesnt' work at all.
>(i couldn't check count part)
>
>can you find where's the problem?
>Thx.

Hi kirke,

Have you tried to run the query? If so, what were the results? Were they
incoorrect, or did you get an error message. If the latter, then what
was that message?

I don't see any real problems with your data, thoough I would change a
few things:

* The date format. yyyy-mm-dd is not safe, becuase it can be interpreted
as yyyy-dd-mm for some country settings. Remve the dashes to get the
unambiguous yyyymmdd format.

* The use of BETWEEN means that rows with a startdate of 28th oct 2006
at exactly midnight will be included, but startdates on the same day
with a later time are excluded. The solution MGFoster proposes for this
(to include a time portion of 23:59:59) is not good enough - for
smalldatetime, this will be rounded up to the next minute, which is
midnight of the 29th of october; for datetime, you'll still miss rows
with a startdate in the last second of the day. You should replace
BETWEEN with a >= and a < condition:
AND J1708Date.dtDateTime >= '20061025'
AND J1708Date.dtDateTime < '20061029' -- Note the increased end day!
If you store all dates with the default time component of midnight, then
this is not necessary - but since it doesn't hurt either, I'd advice you
to accustom yourself to always using this techniques when comparing
datetimes.

The expression GROUP BY convert(varchar,J1708Data.dtDateTime,120) won't
group by daym, since the conversion doesn't chop off the time portion.
The result of select convert(varchar, current_timestamp, 120) for
instance is "2006-11-03 23:18:22", so you end up grouping by second.

Here's what I would try:

SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))
SELECT DATEADD(day, DATEDIFF(day, 0, d.DateTime), 0) AS TheDate,
COUNT(*) AS TheCount
FROM Vehicle AS v
INNER JOIN J1708Data AS d
ON v.VehicleID = d.VehicleId
WHERE d.PidId = 303
AND d.DateTime >= '20061025'
AND d.DateTime < '20061029'
AND v.Description = $VehicleID
GROUP BY DATEDIFF(day, 0, d.DateTime);

--
Hugo Kornelis, SQL Server MVPsqlsql

Convert

I am using the following snippet of code to help me convert the date and tim
e
in a query I am writing.
SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
dbo.Subjects.Subject, Convert
(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
The date converts fine to the format that I need. The time does not. It is
being displayed as military times and I want it to appear as a standard 12
format. i.e 9:00 AM
I tried all of the codes I found in BOL and none gave me what I wanted. Is
it possible to do what I want to do?
ThanksI think you can use the following:
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
HTH
Barry|||Thanks. But it still shows up as military time when I use 100.
"Barry" wrote:

> I think you can use the following:
> Convert
> (Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
> HTH
> Barry
>|||Umm not sure why - I have just checked the BOL and it confirms my
suggestion in the CAST and CONVERT section.
What are storing the Time as? Datetime?
Barry|||right(convert(varchar,dbo.TrainingSchedule.RequestedTime,100),7) as Time
Brennan wrote:

>I am using the following snippet of code to help me convert the date and ti
me
>in a query I am writing.
>SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
>dbo.Subjects.Subject, Convert
>(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
> Convert
>(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
>The date converts fine to the format that I need. The time does not. It i
s
>being displayed as military times and I want it to appear as a standard 12
>format. i.e 9:00 AM
>I tried all of the codes I found in BOL and none gave me what I wanted. Is
>it possible to do what I want to do?
>Thanks
>|||Usually, formatting is best left to the client since most client languages
have far better capabilities in this area. It isn't particularly clear what
datatypes you are using for the columns in question - the assumption is that
they are both datetime (or smalldatetime). If this assumption is not valid,
then you should clarify what the datatypes are and the expected formats of
the data (if applicable). One can question the wisdom of separating these
two intimately related bits of information into two separate columns -
especially given the dbms support.
If you must persist in this quest, you will most likely need to "generate"
the appropriate information in some convoluted and complex expression (and
possibly multiple queries). For the convert function, none of the available
formats has a space between the time and the AM/PM characters. If this can
be ignored, the 100 format is the closest - convert to this format and take
the last 7 characters (or all the characters from the last space to the end
of the string). You could also use the datepart functions to strip off and
convert the bits that are of interest. Experiment a bit - I think you will
understand the reason for the my first statement.|||Can you post a repro? Is the datatype really datetime? Also, I agree that fo
rmatting is best
performed in the client application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:753F4CC7-DC7F-496E-8834-51667F2BFD0B@.microsoft.com...
> Thanks. But it still shows up as military time when I use 100.
> "Barry" wrote:
>|||Thanks I agree with you about the client. I am using smalldatetime.
My problem is that my client is a DNN portal. I am using an add in module
that let's me dynamically display the results of an SQL statement in a grid
on any selected page.
Unfortunately, it does not give me the opportunity to adjust any formatting
which was why I was trying to approach it from a Convert perspective. And I
know nothing about asp so I can't approach the problem from the client side.
I'll try some of the solutions mentions here, but I think I'm going to end u
p
writing an RS report to provide this information to our end users.
Thanks
"Scott Morris" wrote:

> Usually, formatting is best left to the client since most client languages
> have far better capabilities in this area. It isn't particularly clear wh
at
> datatypes you are using for the columns in question - the assumption is th
at
> they are both datetime (or smalldatetime). If this assumption is not vali
d,
> then you should clarify what the datatypes are and the expected formats of
> the data (if applicable). One can question the wisdom of separating these
> two intimately related bits of information into two separate columns -
> especially given the dbms support.
> If you must persist in this quest, you will most likely need to "generate"
> the appropriate information in some convoluted and complex expression (and
> possibly multiple queries). For the convert function, none of the availab
le
> formats has a space between the time and the AM/PM characters. If this ca
n
> be ignored, the 100 format is the closest - convert to this format and tak
e
> the last 7 characters (or all the characters from the last space to the en
d
> of the string). You could also use the datepart functions to strip off an
d
> convert the bits that are of interest. Experiment a bit - I think you wil
l
> understand the reason for the my first statement.
>
>

Conversion of DTS Application to SSIS Application - SourceConnectionId and SourceObjectName

Hello All,

I am trying to convert an application created using DTS classes to SSIS object model. I have found following code in the application.

For x As Integer = 1 To mTask.Properties.Count

If mTask.Properties.Item(x).Name = "SourceConnectionID" Then

CnID = mTask.Properties.Item(x).Value

Exit For

End If

Next

For x As Integer = 1 To mPkg.DTSPackage.Connections.Count

If mPkg.DTSPackage.Connections.Item(x).ID = CnID Then

Return mPkg.DTSPackage.Connections.Item(x).Name

End If

Next

Return mTask.Properties.Item("SourceObjectName").Value

How can I retrieve or Set "SourceConnectinId", "SourceObjectName" of a data flow task in SSIS

Please help me to solve this problem.

Thanks in advance

Subin

Hello All,

How can I retrieve source and destination connection information of a data flow task through code (c#) . My dataflow task have one flat file source, script task and oledb destination. In SQL Server 2000 DTS, there is a SourceConnectionId property to get connection information. Which SSIS property is equivalent to SourceConnectionId of DTS?.

Please help me

Thanks in advance

Subin

|||Subin,
I've merged these two posts from you as they are the same topic. Please don't start a new thread for the same topic you've already posted about.

Thanks,
Phil Brammer|||

Subin wrote:

Hello All,

How can I retrieve source and destination connection information of a data flow task through code (c#) . My dataflow task have one flat file source, script task and oledb destination. In SQL Server 2000 DTS, there is a SourceConnectionId property to get connection information. Which SSIS property is equivalent to SourceConnectionId of DTS?.

Please help me

Thanks in advance

Subin

There is no apples-to-apples equivalent. DTS and SSIS have very different object models.

You will have to iterate over the SSIS object model exactly as you did in DTS. This post should help:

Building Packages Programatically

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

As Phil says - there is no need to post a thread more than once unless it has gone unanswered and therefore hidden many pages back.

-Jamie

|||Components reference connections through the RuntimeConnectionCollection collection, a property of the IDTSComponentMetaData90 class.

Conversion of code from oracle to sql server

hi all
I've already written the following code which works fine in oracle.
can somebody help me out for sql server as we have migrated to sql
server 2000.
select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
connect by prior EMP_CODE = EMP_REP_AUTH
thanxHello,
You can write the recursive queries from SQL 2005 onwards.
http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
Thanks
Hari
<jefftim@.gmail.com> wrote in message
news:1170159273.454099.318510@.p10g2000cwp.googlegroups.com...
> hi all
> I've already written the following code which works fine in oracle.
> can somebody help me out for sql server as we have migrated to sql
> server 2000.
> select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
> WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
> connect by prior EMP_CODE = EMP_REP_AUTH
>
> thanx
>

Conversion of code from oracle to sql server

hi all
I've already written the following code which works fine in oracle.
can somebody help me out for sql server as we have migrated to sql
server 2000.
select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
connect by prior EMP_CODE = EMP_REP_AUTH
thanx
Hello,
You can write the recursive queries from SQL 2005 onwards.
http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
Thanks
Hari
<jefftim@.gmail.com> wrote in message
news:1170159273.454099.318510@.p10g2000cwp.googlegr oups.com...
> hi all
> I've already written the following code which works fine in oracle.
> can somebody help me out for sql server as we have migrated to sql
> server 2000.
> select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
> WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
> connect by prior EMP_CODE = EMP_REP_AUTH
>
> thanx
>

Conversion of code from oracle to sql server

hi all
I've already written the following code which works fine in oracle.
can somebody help me out for sql server as we have migrated to sql
server 2000.
select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_D
EPARTMENT from EMPLOYEE
WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
connect by prior EMP_CODE = EMP_REP_AUTH
thanxHello,
You can write the recursive queries from SQL 2005 onwards.
http://www.sqlservercentral.com/col...00
5.asp
http://www.sqlservercentral.com/col...lserver2005.asp
Thanks
Hari
<jefftim@.gmail.com> wrote in message
news:1170159273.454099.318510@.p10g2000cwp.googlegroups.com...
> hi all
> I've already written the following code which works fine in oracle.
> can somebody help me out for sql server as we have migrated to sql
> server 2000.
> select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_D
EPARTMENT from EMPLOYEE
> WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
> connect by prior EMP_CODE = EMP_REP_AUTH
>
> thanx
>sqlsql

Conversion from char to Nchar

Hello,

I am trying to convert a single code page MS Server database into a unicode database, using the unicode data types,NCHAR, NVARCHAR, NTEXT. The problem is that in the original database, indexes and constraints have been defined on the tables whose configurations need to be changed. As a result, the ALTER TABLE command fails. Are there any other alternative solutions?
Also, data from the old database needs to be preserved. The objective is to create a unicode database which keeps the old data intact as well as accepts the new data in unicode.
It would be great if you could help!
Thanks,
Sheetal.If you have enough disk space, I strongly recommend:

1) Use SQL Enterprise Manager to script your old database
2) Edit the script to change CHAR to NCHAR
3) Edit the script to change VARCHAR to NVARCHAR
4) Edit the script to change TEXT to NTEXT
5) Create a new database
6) Play the script into the new database using SQL Query Analyzer
7) Copy the data from the old database to the new one

The down side is that this new database can take about 2.5 times as much disk space as your old database, so you have to have quite a bit of space free to make this happen.

There are other ways to do this conversion, but they are a lot more complicated. If you have the disk space, this is a much simpler way to do the conversion.

-PatP|||Hello there,

Thanks very much for your speedy reply!
Excuse me if i sound like a complete beginner, but I am not really experienced with MS Server, as a result, I'm not aware of whether my approach to scripting the database is correct or not. Is it, right click on the existing database -> All new tasks -> Generate SQL Script , and if so,then , General->Show All ;Options->All checkboxes selected??
When the script is ready, i try to execute it on the 'master' DB, after renaming the existing database.
But I get errors and it doesn't execute saying it that it doesn't recognise the user defined data types and roles (while giving their names)
How can I take care of this?
Lastly, after changing the concerned fields, i.e, char to Nchar, varchar to Nvarchar and text to Ntext, which tool is used to transfer the data from the old DB to the new empty one?

Thanks again!
Sheetal.|||That's Ok, everybody has to start somewhere!

First, create a new database. If you prefer working in a GUI environment, you can do this using SQL Enterprise Manager.

Next, open SQL Query Analyzer. Connect to the database, then open the edited script. Click on the "play" button in the toolbar, or just hit Ctrl-E to execute. The script should run in the new database with no error messages.

The simplest way to move the data is probably to use the DTS Wizard. You can get to it by right clicking the Data Transformation Service in SQL Enterprise Manager.

-PatP|||Hello Pat :-)

Many thanks again for your speedy (and warm) reply!
I tried using the SQL Enterprise Manager to create a new database, and then play into it the edited version of the old DB Script, but it gives me really wierd errors everytime, like a certain table or type doesn't exist(though it does exist in the original DB), and there's no way I can find out what's going on with the automated script generation. Any tips?
If not, I wrote a piece of code which works perfectly in converting Char to Nchar :
ALTER TABLE t_nm_reports
DROP CONSTRAINT UQ__t_nm_reports__5D60DB10

ALTER TABLE t_nm_reports
ALTER COLUMN nm_dw_name NCHAR(50) /*the column which needs to be changed*/

ALTER TABLE t_nm_reports
ADD CONSTRAINT UQ__t_nm_reports__5D60DB10
UNIQUE (nm_dw_name);

But, this is a very rough and basic way of solving the problem, done manually for each concerned table. I'm looking for a piece of code which can search for the concerned tables and perform the query, all in one program. Is that possible?
Thank you for your time!

Sheetal.|||I'm going to be really, really detailed about this. Please don't be offended, I'm trying to cover everything, not insult anyone!

1) Launch SQL Enterprise Manager
2) Navigate the tree to the server of interest
3) Double click the server to connect and open
4) Double click the Databases collection to open it
5) Right click the source database
6) Click on All Tasks | Generate SQL Script...
7) Click on the Show All button in the upper right corner
8) Click in the Script all objects checkbox
9) Click the Options tab
10) In the security section:
11) Click the checkbox for Script database users and database roles
12) Click the checkbox for Script object-level permissions
13) In the Table Scripting section:
14) Click all four checkboxes
15) Click the Ok button
16) Make the appropriate choices for saving the file

This should get you a script that includes everything, in the correct order to rebuild the schema from scratch. You should be able to edit this script to change CHAR to NCHAR and VARCHAR to NVARCHAR without any problems (or at least I can't think of any).

While you can hunt down all of the "problem child" columns and fix them as you did in your example, it is a lot more work and I'm not completely comfortable that you'll get what you really want, especially from a performance standpoint.

-PatP|||Hello there,
I'm sorry but it just doesn't seem to work :-(
Everytime I try to execute the edited script in the query analyzer(against the new empty database or the source database), I get errors like a particular table/sp doesn't exist (or incorrect syntax), even though it does exist before the execution, but seems to get dropped/deleted during the execution from the source database.
Thanks for ur time, any other suggestions would be highly appreciated!

Sheetal.|||If you have foreign key definitions (look for the keyword FOREIGN to find them), you will want to move them to the end of the script. This is due to the fact that the scripting engine doesn't always respect the "dependance sequence" of the tables, so the tables aren't always created in the same order that they were originally created.

-PatP|||You may also just be able to run your script twice, ignoring any errors that state that a particular object already exists.|||You may also just be able to run your script twice, ignoring any errors that state that a particular object already exists.As long as you skip the DROPs after the first run!

-PatP|||So, the scripting was completed successfully,with everything done as specified except that in the Options tab, the MS-DOS(OEM) format was selected, and not the Windows ANSI format.It does give some errors because of dependent objects, which when moved before the creation of the calling procedure, works fine. But it can be a hassle if they are many in number(as in my case). Any workarounds this problem??

Also, an important question for me is to know how to find and replace a certain string, eg changing char to nchar while using the query analyzer's "Replace", passes thru every string named 'varchar' or 'character' as well, so its very time consuming.I've tried using '(space)char', but its not foolproof either. Is there any way I can search for regular expressions automatically, as doin it manually in a huge database script is not very practical. Like for eg, creating a .bat file and using FINDSTR? if I'm on the right track, please guide me further!
Thanks!
Sheetal.|||Let's try a different approach...

Modify the directions for step #14 to exclude the last check box for Primary, Foreign, and Check constraints. Build the script that way (without the constraints).

Build a second script with only the constraints.

1) Launch SQL Enterprise Manager
2) Navigate the tree to the server of interest
3) Double click the server to connect and open
4) Double click the Databases collection to open it
5) Right click the source database
6) Click on All Tasks | Generate SQL Script...
7) Click on the Show All button in the upper right corner
8) Click in the Script all tables checkbox
9) Click the Formatting tab
10) Clear all of the check boxes
11) Click the Options tab
12) In the Table Scripting section:
13) Click only the PRIMARY keys, FOREIGN KEYS, and check constraints checkbox
14) Click the Ok button
15) Make the appropriate choices for saving the file

Now you should be able to play the first script, then play the second script without running into the dependancy problems you've been having.

In terms of better editing tools, I'd use an editor that recognizes regular expressions (Elvis is free, there are lots of others), or a tool like Perl that was made for those kinds of tasks.

-PatP

conversion from Access query to mssql query

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In AdvanceI'm sure some one will correct me if Im wrong but I dont think this query will ever work in SQL Server, it does look like something that might run in access though.

Ive had problems like this before, Access loves adding in brackets () that just get in the way and confuse things in SQL Server. It also seams to list all the tables then join them in the FROM statement which is not s SQL thing either.

I think your also going to have a problem with the WHERE clause, namely the part " & intJobSerial & " reefers to a variable in Access. Even if you have created the variable in SQL Server the syntax is still wrong

Your query doesnt look like a union query, but a standard select query gone a bit wrong in the FROM part. Your query should look something like

DECLARE @.intJobSerial VARCHAR(50) -- this creates the variable as a 50 character text field, don't need this bit if youve done it already

SET @.intJobSerial = 'XXX' -- this sets the variable to XXX, don't need this bit if youve done it already

SELECT DISTINCT [Sites Union Controls].[Description],[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].Type, Samples.SequenceNumber

FROM [Sites Union Controls]
INNER JOIN Samples
ON [Sites Union Controls].SiteSerial = Samples.SiteSerial
INNER JOIN Jobs
ON Jobs.JobSerial = Samples.JobSerial

WHERE Jobs.JobSerial = @.intJobSerial

ORDER BY Samples.SequenceNumber

The invalid object sites union controls in actually the table used the query, Im guessing its because the FROM Part was all messed up at it was the first thing it came across after it went wrong.

Hope this helps|||Hello,

Trying by used the next name Sites_Union_Controls because I'm not sure that you can have a name with blanc characters

Good luck

Sylvie

Quote:

Originally Posted by aakash

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In Advance

|||Hey, a couple of points...
SQL uses + for concatenation so you need to change your where statement to
((Jobs.JobSerial) = " + @.intJobSerial + ")

JohnK is right, if intJobSerial is a local variable then it needs to be declared and it must begin with an @..

There is nothing wrong with the FROM statement, it's a little unusual to delay the two ON clauses at the end but this gives a different result set because nulls in the 3rd table, in your query SAMPLES, can be handled differently this way. It's more effective if your mixing Left Outer and Inner Joins, however.

The biggest thing I see is that the permissions on the SQL table [Sites Union Controls] may be different than what you expect. You should probably be using a full reference to it as [database].[owner].[table] to at least eliminate the possibility that your error is a security setup problem.

Tom|||Sites Union Controls, was query in old ms access project which was connected to ms access database i am changing connectivity to ms sql 2000 ,none of the above solutions seem to be working ,
please help

Conversion failed when converting from a character string to uniqueidentifier.

Hi, i have a problem, i keep getting this Error.

I want to insert an uniqueidentifier using a textbox, i use the following code to insert.

SqlDataSource1.InsertParameters[

"RWID"] =newParameter("RWID",TypeCode.String, RWID);

SqlDataSource1.Insert();

The databasetype is an uniqueidentifier of that column.

Anyone who can help me with this problem?

Hi friend,

Have you tried TypeCode.Object

|||

I tried using TypeCode.Object, then I get another error:

Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.

|||

Hi friend,

I tried a sample to reproduce the error. But it its working fine for me. I created a table named t1 with one column c1 of datatype unique identifier.

SQL datasource code

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:iGoldWebConnectionString %>"

SelectCommand="SELECT * FROM [T1]"InsertCommand="insert into t1 values(@.g)" ></asp:SqlDataSource>Data Insert Code

SqlDataSource1.InsertParameters["g"] =newParameter("g",TypeCode.String,Guid.NewGuid().ToString());

SqlDataSource1.Insert();

Its working fine for me.

I hope the problem is with the guid which you get from textbox . Have you checked you receive only valid GUID.

Conversion failed when converting character string to smalldatetime data type.

Hello, I have problem with this code.(This programpresents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are 2 time categories in DropDownList - this day, this week.

Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.

Conversion failed when converting character string to smalldatetime data type.

Here is code:

<%

@.PageLanguage="C#" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e)

{

string datePatt =@."yyyymmdd";

// Get start and end of day

DateTime StartDate =DateTime.Today;

string @.StartDate1 = StartDate.ToString(datePatt);

string @.EndDate = StartDate.AddDays(1).ToString(datePatt);// Get start and end of weekstring @.startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);string @.startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);

switch (DropDownList1.SelectedValue)

{

case"1":

// day

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.StartDate1'" +" AND [DATE] < " +"'@.EndDate'";break;case"2"://week

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.startOfWeek'" +"AND [DATE] <" +"'@.startOfNextWeek'";break;

}

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>Untitled Page</title><styletype="text/css">body {font:1emVerdana;

}

</style>

</

head>

<

body><formid="form1"runat="server"><div>

<asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"Style="z-index: 100; left: 414px; position: absolute; top: 22px"><asp:ListItemSelected="True"Value="1">jeden den</asp:ListItem><asp:ListItemValue="2">jeden tyden</asp:ListItem></asp:DropDownList>

<asp:GridViewID="GridView1"runat="server"Style="z-index: 102; left: 228px; position: absolute;

top: 107px"

DataSourceID="SqlDataSource1"AutoGenerateColumns="True">

</asp:GridView> <br/><br/><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="Data Source=CR\SQLEXPRESS;

Initial Catalog=MyConn;Integrated Security=True"

ProviderName="System.Data.SqlClient"></asp:SqlDataSource></div></form>

</

body>

</

html>

string datePatt =@."yyyymmdd";

In your 'date' pattern, mm is minutes.

You want MM for month: yyyyMMdd

|||Thank you, for your reply, Icorrected this error. But the problem is still here.|||

I notice you are trying to use parameters but I don't see the code that adds parameters: Parameters.Add(...).

If you pass in the date as a DateTime, you will not have to format it to a string.

|||Thank you, I will try.

Conversion error

I am getting the error message Error converting data type varchar to float when running the following query:

Code Snippet

select top 175816
AV.intItemID,
AV.intAttrID,
-- AV.vchValue,
CAST(AV.vchValue AS float) AS Test,
0
from tblAttrVals AV
join tblAttributes AA
on AA.intAttributeID = AV.intAttrID
and AA.intDataTypeID in (2, 3)
and (1 = isnumeric (AV.vchValue))
order by AV.intItemID, AV.intAttrID

Here is what is strange. If I bump the top count down by one it succeeds. And even stranger, if I leave the top count the same and uncomment out the line in the select statement that shows the value being converted it succeeds.

Any ideas? This seems like a bug.

Chris:

Can you show us the specific data that is giving you trouble?

|||

I found the issue. It actually had nothing to do with the data that is being returned. It had to do with the data not being returned.

Here is the info from a post that helped me:

The problem is that SQL Server 2005 is more aggressive in terms of evaluating expressions in your query and moving them to different stages of the query plan. This might result in conversion error like in your case if the CAST gets computed before the WHERE clause checks. So there is no guarantee that the expressions in the WHERE clause will be computed first. This was true even in SQL Server 2000 except that you probably never hit it for your schema/data set. You can get the same error there also if the query plan changes.

To resolve the problem, you need to either correct your data model to represent the values correctly. Use float if your data is float - don't mix values from different domains. Or you will have to use CASE in the SELECT list to avoid the conversion problem. Note that using CASE expression is the only way to control order of execution of various expressions. See link below for more details (search for unsafe expressions):

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

To summarize you have two solutions:

1. Fix your data model / schema so you represent the values in their proper domain (not float values in varchar and mixing various values in string)
2. Or modify your SELECT in the 2nd view to:

SELECT cast(CASE WHEN dwpId LIKE '[0-9]%' THEN dwpId END as int) as dwpId, startDate, endDate
FROM View1

Note that even above check is not entirely correct because not all values that have just numeric digits can be successfully converted to int. You might get overflow errors for example. You could use ISNUMERIC but that checks for integer, numeric, and money conversions so it will let more data through. So it is best you correct your schema to avoid all these issues.

sqlsql

Conversation Timer problem : Timeout not effective

Hi,

I am using conversation Timer for delaying a message for a few seconds but I can see the message immediately in the queue.

Here is the code i am using. This is a part of a stored procedure I have used.

BEGIN CONVERSATION TIMER ( @.h ) TIMEOUT = @.DelayBySeconds;

SEND ON CONVERSATION @.h

MESSAGE TYPE [sendmsg]

(@.msg);

I am executing this stored procedure with following statements.

exec set_ssb_msg 'test3', 25;

exec set_ssb_msg 'test1', 1;

select * from q1

I was hoping to see just the 'Test1' and see test3 after 25 seconds. But I could see both the messages in a queue as soon as i run the stored proc.

If I execute a receive command on the queue, I am receiving 'test3' first and then 'test1'. This is exactly opposit of what i expected.

Can you please let me know if I am doing anything wrong or missing a step.

Any help is greatly appreciated.

Thanks,

Don.

Conversation timers have no relation whatsoever to sent messages, they affect the local endpoints only. You should expect a DialogTimer message in your sender's queue to show up after 25 and/or 1 seconds. The messages sent are unaffacted by timers. Also, although is not clear in your example, it seems that you're begining a new conversation for each message sent. The message order is only guaranteed within a conversation, and as such your expectations of a certain order on the target queue are not justified.

Conversation group id question

HI

I have an example ( see below ).

I expect to have all messages sent using this code to have the same group id but they are all different. what I am doing wrong?

Leonid.

DECLARE @.conversationHandle uniqueidentifier

DECLARE @.usergroup uniqueidentifier

select @.usergroup = uid from bvuser where userid = 1

select @.usergroup

Begin Transaction

BEGIN DIALOG @.conversationHandle

FROM SERVICE [BvMainResponseService]

TO SERVICE 'BvMainService'

ON CONTRACT [BvMainContract]

WITH RELATED_CONVERSATION_GROUP = @.usergroup;

-- Send a message on the dialog

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE [BvTaskMsg]

(N'Test')

commit

As far as i understand it you expand a conversation group by adding additional dialogs related to the first one:

For example:

DECLARE @.conversationHandle uniqueidentifier

Begin Transaction

BEGIN DIALOG @.conversationHandle

FROM SERVICE [BvMainResponseService]

TO SERVICE 'BvMainService'

ON CONTRACT [BvMainContract]

WITH RELATED_CONVERSATION_GROUP = @.conversationHandle;

-- Send a message on the dialog

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE [BvTaskMsg]

(N'Test')

commit

You keep using the conversation handle from the begin dialog to keep the same conversation, i could be mistaken as i have not really tried it , but i think that is the theory anyway.

Thanx

|||

this is from BOL

If related_conversation_group_id does not reference an existing conversation group, the service broker creates a new conversation group with the specified related_conversation_group_id and relates the new dialog to that conversation group.

so as I understand this - new conversation group id is created when BEGIN DIALOG is used for the first time with specified ID, and then ... here is BOL again

Specifies the existing conversation group that the new dialog is added to. When this clause is present, the new dialog will be added to the conversation group specified by related_conversation_group_id.

But obviously I am doing something wrong here becuase it doesn't work as I expect it.

Leonid.

|||

In the test you've shown the conversation should have the same conversation group id. How are you looking up the conversations?

Here is a test script that shows that the related_conversation_group creates conversation in the same group, and the first BEGIN CONVERSATION creates the group itself, just as you expect:

use [tempdb];

go

create queue [testQueue];

create service [testService] on queue [testQueue];

go

create queue [targetQueue];

create service [targetService] on queue [targetQueue] ([DEFAULT]);

go

declare @.cg uniqueidentifier;

declare @.h uniqueidentifier;

select @.cg = newid();

begin dialog conversation @.h

from service [testService]

to service N'targetService', N'current database'

with related_conversation_group = @.cg,

encryption = off;

send on conversation @.h;

begin dialog conversation @.h

from service [testService]

to service N'targetService', N'current database'

with related_conversation_group = @.cg,

encryption = off;

send on conversation @.h;

begin dialog conversation @.h

from service [testService]

to service N'targetService', N'current database'

with related_conversation_group = @.cg,

encryption = off;

send on conversation @.h;

select * from sys.conversation_endpoints where conversation_group_id = @.cg;

HTH,
~ Remus

Controlling Trigger actions based on user

Is it possible to control the actions of a trigger based on the user who
updates the record?
In pseudo code, I'm trying to do the following:
on update:
if (updating user = "User_A") and (Artist_Type = "DJ" or "CL") then
{ newrecord.PIC_FIELD = oldrecord.PIC_FIELD }
Could someone shead some light on if/how this could be done in "real" code?
Any help would be GREATLY appreciated.
Thanks,
_KThe trigger's code should look very similar to your pseudo code (not
tested):
IF SUSER_SNAME() = 'User_A'
BEGIN
UPDATE T1
SET PIC_FIELD = D.PIC_FIELD
FROM T1 JOIN deleted AS D
ON T1.key = D.key
WHERE T1.Artist_Type IN('DJ', 'CL')
END
BG, SQL Server MVP
www.SolidQualityLearning.com
"KBryan" <kbryan@.noyouwont.com> wrote in message
news:%237v6ttWNFHA.2716@.TK2MSFTNGP10.phx.gbl...
> Is it possible to control the actions of a trigger based on the user who
> updates the record?
> In pseudo code, I'm trying to do the following:
> on update:
> if (updating user = "User_A") and (Artist_Type = "DJ" or "CL") then
> { newrecord.PIC_FIELD = oldrecord.PIC_FIELD }
>
> Could someone shead some light on if/how this could be done in "real"
> code?
> Any help would be GREATLY appreciated.
> Thanks,
> _K
>|||Thanks VERY much.
Would it still be deleted if this is an update trigger?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23pug97WNFHA.3560@.TK2MSFTNGP14.phx.gbl...
> The trigger's code should look very similar to your pseudo code (not
> tested):
> IF SUSER_SNAME() = 'User_A'
> BEGIN
> UPDATE T1
> SET PIC_FIELD = D.PIC_FIELD
> FROM T1 JOIN deleted AS D
> ON T1.key = D.key
> WHERE T1.Artist_Type IN('DJ', 'CL')
> END
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "KBryan" <kbryan@.noyouwont.com> wrote in message
> news:%237v6ttWNFHA.2716@.TK2MSFTNGP10.phx.gbl...
>|||Yes; deleted holds the old image of the modified data.
BG, SQL Server MVP
www.SolidQualityLearning.com
"KBryan" <kbryan@.noyouwont.com> wrote in message
news:%23WFXaMXNFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Thanks VERY much.
> Would it still be deleted if this is an update trigger?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23pug97WNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>

Control jobs using SQL code?

Is there a way (system stored proc) to schedule/reschedule jobs through
SQL code (stored procedure) instead of GUI? We have a job set up on SQL
server and we are trying to control scheduling piece of it through
stored proc. Any help would be appreciated. Thanx!
*** Sent via Developersdex http://www.examnotes.net ***Have a look at sp_update_jobschedule in Books Online. This procedure is in t
he
msdb database, so it is used like
EXEC msdb.dbo.sp_update_jobschedule
"Test Test" wrote:

> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||have a look at following system stored procedures in BOL, there are few more
as well which are related to scheduling the job, you can have a look at them
in BOL.
sp_add_job
sp_add_jobstep
sp_add_jobschedule
sp_delete_job
sp_help_job
sp_help_jobstep
sp_update_job
"Test Test" wrote:

> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks, Mark. It really helps!
*** Sent via Developersdex http://www.examnotes.net ***

Continue on INSERT error.

Hi!

Imagine this SQL statement:

Code Snippet

INSERT INTO B SELECT * FROM A

If one of the insert fails ... don't continue, the statement fail. For example if any field in A violate a constraint in B, the statement fails.

I want that the statement continue if errors occurs, if i lost a number of rows don't matter ... but if i can save or log this row will be great too !!

Is posible? Any way to do it?

Regards.

Make two statements, by adding a WHERE clause, you can verify the CONSTRAINT and add rows ONLY if the CONSTRAINT passes. Then in the second statement, in the WHERE clause, get the rows that do not pass.

FOR illustration:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Name varchar(20) PRIMARY KEY
)

INSERT INTO @.MyTable VALUES ( 'Bill' )

DECLARE @.MyOtherTable table
( RowID int IDENTITY,
Name varchar(20)
)

DECLARE @.Failures table
( RowID int,
Name varchar(20)
)

INSERT INTO @.MyOtherTable VALUES ( 'Bill' )
INSERT INTO @.MyOtherTable VALUES ( 'Mary' )
INSERT INTO @.MyOtherTable VALUES ( 'Omar' )

-- First, isolate the CONSTRAINT Failures
INSERT INTO @.Failures
SELECT
t.RowID,
t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name = t.Name

-- Insert the rows that pass the CONSTRAINT test
INSERT INTO @.MyTable ( Name )
SELECT t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name <> t.Name

SELECT *
FROM @.MyTable

RowID Name
-- --
1 Bill
2 Mary
3 Omar

SELECT *
FROM @.Failures

RowID Name
-- --
1 Bill

|||

Thanks for your reply.

I will write my question in another way. What I want is if I can change the SQL/Server constraint behaviour when a error is thrown. I know that I can do the insert with a "WHERE" clause. But it some cases is useful to perform your own behaviour when the table has a lot of fields and a lot of rows and you are using a INSERT ... SELECT ... clause. There is some utility (NOTIFICATION, TRIGGERS) that help to do this in a speedy way?

Regards.

|||

A CONSTRAINT failure occurs BEFORE the data is inserted into the table -so a AFTER INSERT TRIGGER would not work.

You could create a BEFORE INSERT TRIGGER, but then you would STILL have to use the two step process I demonstrated in my earlier post. And there may be increased locking and blocking behavior as a result of using a TRIGGER.

Bottom line is that the CONSTRAINT prevents the data from getting into the table. Without the data getting to the table, there is little to offer in the form of Notifications, etc., and you are also, pardon the ironic pun, constrained in the ability to use a TRIGGER.

|||

OK! Thanks.

Regards.

Context Change and Cursor

I have T-SQL code that is used to run utilities against a particular list of databases that are stored in a table. I have a cursor that gets the list of DBs from the sysdatabases table based on entries in a table in a specific database. The list of databases will vary from server to server, so I need to have it use variables rather than code the db names into the script.

declare @.DatabaseId char(8)

declare DatabaseLoop cursor for
select name from master..sysdatabases where name in
(select <column name> from <db name..table> )

open DatabaseLoop
fetch next from DatabaseLoop into @.DatabaseId
while (@.@.fetch_status <> -1)
begin

...

So far, the context has not mattered, for example - backup @.DatabaseID works fine within the cursor since the context doesn't need to change. The problem is that I want to run particular scripts that require the context to be changed to the database. But, of course, USE @.DatabaseID does not work inside the cursor.

So, is there another way to be able to run my script against multiple variables other than using a cursor?

I am not necessarily looking for you to write my code, but if someone could point me in the right direction, I would appreciate it.

I would suggest that you use dynamic SQL for this and you can use a use in there.

declare @.query varchar(8000) -- I am assuming 2000 since you

--used sysdatabases

set @.query = 'use ' + @.database + ' select * from sysobjects'

exec (@.query)

I know this works in 2005, and am pretty sure it worked in 2000

Edit: Sorry, sent the message from my phone and it did a terrible job with the formatting

Consuming events in code

Hi,

Ive been taking a look at how to consume events from a package when executing programatically.

Ive got some code (copied below) that creates a package programatically, adds a sequence container then within that adds a script task , then executes it using the overloaded method of Package.Execute() that takes an IDtsEvents argument.

My class that implements IDtsEvents simply output a message to the console for each event type.

Weird thing is, when I execute, this is the only output I get:

Starting...
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Package
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Sequence
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.TaskHost
OnPostValidate:Microsoft.SqlServer.Dts.Runtime.TaskHost
OnQueryCancel
Package ran successfully

What I find weird is that I dont get information for loads of other event types. I would at least have expected to see some OnPostExecute events.

Anyone know why i dont see all of the events?

Thanks

Jamie

Heres the code:

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Tasks.ScriptTask;

namespace Package_API

{

class Program

{

static void Main(string[] args)

{

Console.WriteLine("Starting...");

Package p = new Package();

p.InteractiveMode = true;

p.OfflineMode = true;

// Add a Script Task to the package.

Sequence s = (Sequence)p.Executables.Add("STOCK:Sequence");

TaskHost taskH = (TaskHost)s.Executables.Add("STOCK:ScriptTask");

// Run the package.

DtsEvents events = new DtsEvents();

p.Execute(null,null,events,null,null);

//p.Execute();

if (p.ExecutionResult == DTSExecResult.Failure || p.ExecutionStatus == DTSExecStatus.Abend)

Console.WriteLine("Package failed or abended");

else

Console.WriteLine("Package ran successfully");

Console.ReadLine();

}

}

}

// Class that implements the IDTSEvents interface:

public sealed class DtsEvents : IDTSEvents

{

void IDTSEvents.OnPreExecute(Executable exec, ref bool fireAgain)

{

Console.WriteLine("OnPreExecute: " + exec.ToString());

}

void IDTSEvents.OnBreakpointHit(IDTSBreakpointSite breakpointSite, BreakpointTarget breakpointTarget)

{

Console.WriteLine("OnBreakpointHit");

}

void IDTSEvents.OnCustomEvent(TaskHost taskHost,string eventName,string eventText,ref Object[] arguments,string subComponent,ref bool fireAgain)

{

Console.WriteLine("CustomEvent");

}

void IDTSEvents.OnPreValidate(Executable exec, ref bool fireAgain)

{

Console.WriteLine("OnPreValidate: " + exec.ToString());

}

void IDTSEvents.OnPostValidate(Executable exec, ref bool fireAgain)

{

Console.WriteLine("OnPostValidate:" + exec.ToString());

}

void IDTSEvents.OnWarning(DtsObject source,int warningCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)

{

Console.WriteLine("OnWarning");

}

void IDTSEvents.OnInformation(DtsObject source,int informationCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError,ref bool fireAgain)

{

Console.WriteLine("OnInformation");

}

void IDTSEvents.OnPostExecute(Executable exec, ref bool fireAgain)

{

Console.WriteLine("OnPostExecute");

}

bool IDTSEvents.OnError(DtsObject source,int errorCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)

{

Console.WriteLine("OnError");

return true;

}

void IDTSEvents.OnTaskFailed(TaskHost taskHost)

{

Console.WriteLine("OnTaskFailed");

}

void IDTSEvents.OnProgress(TaskHost taskHost,string progressDescription,int percentComplete,int progressCountLow,int progressCountHigh,string subComponent,ref bool fireAgain)

{

Console.WriteLine("OnProgress");

}

bool IDTSEvents.OnQueryCancel()

{

Console.WriteLine("OnQueryCancel");

return true;

}

void IDTSEvents.OnExecutionStatusChanged(Executable exec,DTSExecStatus newStatus,ref bool fireAgain)

{

Console.WriteLine("OnExecutionStatusChanged");

}

void IDTSEvents.OnVariableValueChanged(DtsContainer DtsContainer,Variable variable,ref bool fireAgain)

{

Console.WriteLine("OnVariableValueChanged");

}

}

By returning true from OnQueryCancel, you are cancelling the package

Just add Console.WriteLine(p.ExecutionResult) - it should be Cancelled.

Return false from this method, or inherit from DefaultEvents and only override methods that you actually need.

|||

Michael Entin - MSFT wrote:

By returning true from OnQueryCancel, you are cancelling the package

Just add Console.WriteLine(p.ExecutionResult) - it should be Cancelled.

Return false from this method, or inherit from DefaultEvents and only override methods that you actually need.

DOH!!!

What a dumbass. I should have realised that!

Thanks Michael!

-Jamie

|||

How odd, Jamie. According my class I can follow each event -including post and pre executing...

Dim EventsSSIS As EventosSSIS
EventsSSIS = New EventosSSIS()
sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)

Public Class EventosSSIS
Implements IDTSEvents
Public proceso As Int16 = 0

Sub OnPostValidate(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPostValidate
End Sub
Sub OnProgress(ByVal taskHost As TaskHost, ByVal progressDescription As String, ByVal percentComplete As Integer, ByVal progressCountLow As Integer, ByVal progressCountHigh As Integer, ByVal subComponent As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnProgress
End Sub
Sub OnPreExecute(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPreExecute
End Sub
Sub OnPreValidate(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPreValidate
End Sub
Sub OnPostExecute(ByVal exec As Executable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnPostExecute
End Sub
Sub OnWarning(ByVal source As DtsObject, ByVal warningCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String) Implements IDTSEvents.OnWarning
End Sub
Sub OnInformation(ByVal [source] As DtsObject, ByVal informationCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnInformation
End Sub
Sub OnTaskFailed(ByVal taskHost As TaskHost) Implements IDTSEvents.OnTaskFailed
End Sub
Function OnError(ByVal source As DtsObject, ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, ByVal helpFile As String, ByVal helpContext As Integer, ByVal idofInterfaceWithError As String) As Boolean Implements IDTSEvents.OnError
End Function
Sub OnExecutionStatusChanged(ByVal exec As Executable, ByVal newStatus As DTSExecStatus, ByRef fireAgain As Boolean) Implements IDTSEvents.OnExecutionStatusChanged
End Sub
Sub OnCustomEvent(ByVal taskHost As TaskHost, ByVal eventName As String, ByVal eventText As String, ByRef arguments() As Object, ByVal subComponent As String, ByRef fireAgain As Boolean) Implements IDTSEvents.OnCustomEvent
End Sub
Sub OnBreakpointHit(ByVal breakpointSite As IDTSBreakpointSite, ByVal breakpointTarget As BreakpointTarget) Implements IDTSEvents.OnBreakpointHit
End Sub
Sub OnVariableValueChanged(ByVal dtsContainer As DtsContainer, ByVal variable As Variable, ByRef fireAgain As Boolean) Implements IDTSEvents.OnVariableValueChanged
End Sub
Public Overloads Function OnQueryCancel() As Boolean Implements IDTSEvents.OnQueryCancel
Dim cancelar As Int32 = 0
OnQueryCancel = False
Try
Using cn As New SqlConnection(sCadenadeConexion)
cn.Open()
Using cm As SqlCommand = cn.CreateCommand
cm.CommandType = Data.CommandType.Text
cm.CommandText = "SELECT cancelar FROM sis_controlthread where idproceso= " & proceso
cancelar = cm.ExecuteScalar
If cancelar Then
OnQueryCancel = True
Else
OnQueryCancel = False
End If
cm.Dispose()
End Using
cn.Close()
End Using
Catch ex As Exception
TratamientoErrores(0, 0, 11, ex.Message, "On Query Cancel")
End Try
End Function
End Class