Problems using SQL: 2012

Friday, March 30, 2012

problems with VB6 and CR10

i'm using visual basic 6, and crystal reports 10
i made some application that prints some reports..
when i made the distribution package... everithing was ok
the problem came when i tried to install the software

i got following errors

registrywrapper.dll unable to register
or registrywrapper.dll file not found (i've added it to installation)

and the same problem with
cxlibw-1-6.dll
localcon.cll
commonobjectmodel.dll
objectfactory.dll
and crqe.dll

all from crystal reports...Try to register those dlls

Problems with values display as currency

Hi NG,
has anybody got a report with values formatted as currency and could export
it to pdf without loosing the currency-formatting?
My currency-values are in Euro. Do i have to put some language-parametes in
the URL?
regards
Frank
www.xax.deAdobe 5.00 and 5.05 do not render symbols (e.g. Euro symbol) correctly when
the Arial font is used. Which version are you using? Did you try other fonts
(e.g. Tahoma)?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Matthiesen" <fm@.xax.de> wrote in message
news:34mtrsF4avhu6U1@.individual.net...
> Hi NG,
> has anybody got a report with values formatted as currency and could
export
> it to pdf without loosing the currency-formatting?
> My currency-values are in Euro. Do i have to put some language-parametes
in
> the URL?
> regards
> Frank
> www.xax.de
>
>|||"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> schrieb im
Newsbeitrag news:eEvOCqZ%23EHA.3592@.TK2MSFTNGP09.phx.gbl...
> Adobe 5.00 and 5.05 do not render symbols (e.g. Euro symbol) correctly
> when
> the Arial font is used. Which version are you using? Did you try other
> fonts
> (e.g. Tahoma)?
Got the wrong acrobat-version (5.0)
After an update everything wirks fine.
thx
frank
www.xax.desql

Problems with using Variables in place of Nested Queires

Ok, I had a problem yesterday where the code was erroring out before running. Now it looks like I am presented with some logic errors that may be because of how I have implemented the variables.

Right now the below code runs, but it does not affect any codes "(0 row(s) affected)". I have been able to pull up over 500 rows that the code should affect. I think their may be a fundimental problem with the code below because if I substitute a specific number for the values of the variables, then I still produce the same results (no rows affected).

Ack please help!

DECLARE @.q4TotRebate int, @.sumRebate int, @.q4TotRed int, @.sumRedeemed int

Update tblTransactionSummary

Set TotalRebate =
CASE
WHEN ((@.q4TotRebate + @.sumRebate)-(@.q4TotRed + @.sumRedeemed)) < 0
THEN (TotalRebate - ((@.q4TotRebate + @.sumRebate)-(@.q4TotRed + @.sumRedeemed)))
WHEN ((@.q4TotRebate + @.sumRebate)-(@.q4TotRed + @.sumRedeemed)) > 0
THEN TotalRebate
END

From tblTransactionSummary as t
WHERE

@.q4TotRebate = ISNULL((Select n.TotalRebateAmount
FROM tblSummary as n
WHERE t.AccountNumber = n.AccountNumber
AND Quarter = '2006/Q4'),0)
AND
@.sumRebate = ISNULL(((SELECT SUM(b.TransactionAmount)
FROM tblTransactionDetail as b
WHERE t.AccountNumber = b.AccountNumber
AND b.TransactionType = 'P'
and b.TransactionDate > '12/31/06'
AND b.TransactionDate < '01/06/07') * .01), 0)
AND
@.q4TotRed =ISNULL((Select SUM(g.QtrRebateRedeemed)
FROM tblSummary as g
WHERE g.AccountNumber = t.AccountNumber
And Quarter < '2007/Q1'),0)
AND
@.sumRedeemed = ISNULL((SELECT SUM(m.TransactionAmount)
FROM tblTransactionDetail as m
WHERE t.AccountNumber = m.AccountNumber
AND m.TransactionType = 'R'
AND m.TransactionDate > '12/31/06'
AND m.TransactionDate < '01/06/07'),0)http://www.msdner.com/forum/thread591536.html

problems with useing ms2000 sql

I am new at this and I am getting though a book that is teaching me asp.net
I have experance with vb but I am having truble with the connection to my sql server

<%@. Page Language="VB" debug="true" %>
<script runat="server"
Function MyQueryMethod() As System.Data.SqlClient.SqlDataReader
Dim connectionString As String = "server='68.250.171.129'; trusted_connection=true; Database='working'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [personaldata].* FROM [personaldata]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

sqlConnection.Open
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function' Insert page code here
Sub Page_load(sender as object, e as EventArgs)
dgCustomers.DataSource = MyQueryMethod()
dgCustomers.DataBind()
End Sub

</script>
<html>
<head>
</head>
<body>
<form runat="server">
<asp:DataGrid id="dgCustomers" runat="server"></asp:DataGrid>

</form>
</body>
</html
This page works fine when I use the web matrix project, web server. I cannot get it to work when I use my win2000 servers iis server.

this is the error I am getting

Server Error in '/aspnet_client' Application.
------------------------

Invalid object name 'personaldata'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'personaldata'.

Source Error:

Line 10:
Line 11: sqlConnection.Open
Line 12: Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Line 13:
Line 14: Return dataReader

Source File: D:\server\WEB\aspnet_client\SimpleDataGrid.aspx Line: 12

Stack Trace:

[SqlException: Invalid object name 'personaldata'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +45
ASP.SimpleDataGrid_aspx.MyQueryMethod() in D:\server\WEB\aspnet_client\SimpleDataGrid.aspx:12
ASP.SimpleDataGrid_aspx.Page_load(Object sender, EventArgs e) in D:\server\WEB\aspnet_client\SimpleDataGrid.aspx:17
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731

Please can anyone helpYou must make the MACHINENAME\ASPNET Windows user a user in SQL Server. When running in Web Matrix, you are running in your user context, whereas when running in IIS, you are running in the securoty contest of, by default, the ASPNET user.

Note that if SQL is on another box, it might be easier to actually use SQL Server security and send a username and password.|||Thank you I did have that user set up, but it was not connected the that data base

problems with URL

Hello

When you click on a name in my report you can send a email with the link of the report with the reports parameters for that person

but i have a problem with that
the report parameter consist of several words with spaces between and because of this the link isn't shown correct

anyone have a solution for this, i already tried a lot possibilities

here is the code of the jump to url expression

="mailto:"+Fields!Email.Value + "?subject=Action(s) not completed for "+Fields!ToUsersName.Value + "&body=You have unfinished task(s) %0A%0A Copy the link below into your browser to see your task(s) %0A%0A http%3A//fs-bru-etl01/ReportServer/Pages/ReportViewer.aspx%3F%252freport+1+KD%252fReport3%26rs%3ACommand%3DRender%26Users%3D"+Fields!ToUsersName.Value

So the problem is with Fields!ToUsersName.Value

http://server/ReportServer/Pages/ReportViewer.aspx?%2freport+1+KD%2fReport3&rs:Command=Render&Users=ALAINLA: alain Lannoo

like you see the link stops because of the spaces in the username

Hello Kenny,

See if this works for you.

="mailto:"+Fields!Email.Value + "?subject=Action(s) not completed for "+ Replace(Fields!ToUsersName.Value, " ", "%20") + "&body=You have unfinished task(s) %0A%0A Copy the link below into your browser to see your task(s) %0A%0A http%3A//fs-bru-etl01/ReportServer/Pages/ReportViewer.aspx%3F%252freport+1+KD%252fReport3%26rs%3ACommand%3DRender%26Users%3D"+Fields!ToUsersName.Value

Hope this helps.

Jarret

|||

Sorry, this one...

="mailto:"+Fields!Email.Value + "?subject=Action(s) not completed for "+ Fields!ToUsersName.Value + "&body=You have unfinished task(s) %0A%0A Copy the link below into your browser to see your task(s) %0A%0A http%3A//fs-bru-etl01/ReportServer/Pages/ReportViewer.aspx%3F%252freport+1+KD%252fReport3%26rs%3ACommand%3DRender%26Users%3D"+Replace(Fields!ToUsersName.Value, " ", "%20")

Hope this helps.

Jarret

|||

thanks for the reply
but it doesn't work

does anyone else have an other suggestion?

|||

It may not work if you have a sequence of spaces, I mean more than one space (continuously) in your field value. You can use this expression to achieve just 1 space:

IIf(InStr(Replace(<yourfield>, Space(2), "&"), "& ") > 0, Replace(Replace(<yourfield>, "&", ""), Space(1), "%20"), Replace(<yourfield>, Space(1), "%20"))

Hope this helps.

Shyam

|||

I just tried it with parameters with more than one space and more than one consecutive space, and it worked fine for me.

I think the issue may be with your link. Can you try this in an IE window:

http://fs-bru-etl01/ReportServer/Pages/ReportViewer.aspx?%2freport+1+KD%2fReport3&rs:Command=Render&Users=ALAINLA:%20alain%20Lannoo

Does this work? If so, try this just to get the link. This is how I have it in my successful test, the only difference is your servername, report path, and report name.

="http://fs-bru-etl01/ReportServer/Pages/ReportViewer.aspx?%2freport+1+KD%2fReport3&rs:Command=Render&Users="+Replace(Fields!ToUsersName.Value, " ", "%20")

Once you get this working, go back and add your mailto code. If this doesn't work, can you post an example of a link that is generated?

Jarret

|||

Kenny,

In Internet Explorer, go to Tools -> Internet Options -> Advanced Tab and check if "Always send URLs as UTF-8" is checked?

Shyam

problems with updating datasets in visual studio .net

Hi,
I have a strange problem since yesterday.
I'm trying to change datasets in my project and I save changes. when I close
the Report and open it again I see that the changes where not saved. Also
the visual studio keeps getting stuck and I have to close it and open it
again.
I have noticed that if I change the query string from the Dataset Properties
window, it does save it.
Since yesterday everything worked properlly, I didn't installed any thing or
make any changes. I tried to do the same thing in another computer in a new
project, and I have the same problem there. Also when I make changes in a web
application in the visual studio, there are no problems.
I'm really desprate here,
can you help me?
Thanks,
LimorI don't know if this is what your problem is, but I noticed that when I
change a dataset from the Data tab, I click on the Layout tab (which shows
the * next to the name to show that it's changed) and then click Save. Since
I've done that I haven't seen a problem with dataset changes not being saved.
Also, if you click Run from the Data tab, leave the result set displayed and
go to another application for awhile, eventually Visual Studio pops up the
same window that Enterprise Manager does having to do with the result set
(can't remember the exact message). Sometimes a separate window pops up from
Visual Studio with the message, sometimes Visual Studio just hangs. That's
probably what you're seeing.
Hope that helps!
"Limor Bellison" wrote:
> Hi,
> I have a strange problem since yesterday.
> I'm trying to change datasets in my project and I save changes. when I close
> the Report and open it again I see that the changes where not saved. Also
> the visual studio keeps getting stuck and I have to close it and open it
> again.
> I have noticed that if I change the query string from the Dataset Properties
> window, it does save it.
> Since yesterday everything worked properlly, I didn't installed any thing or
> make any changes. I tried to do the same thing in another computer in a new
> project, and I have the same problem there. Also when I make changes in a web
> application in the visual studio, there are no problems.
> I'm really desprate here,
> can you help me?
> Thanks,
> Limor|||This is definitely the route to go. Sometimes when you change a dataset the
designer does not recognize that a change was made so it does not get saved.
Going to layout and clicking the save button resolves this problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"daw" <daw@.discussions.microsoft.com> wrote in message
news:48C835F1-5BB3-4CEB-A969-73995D0F4F22@.microsoft.com...
>I don't know if this is what your problem is, but I noticed that when I
> change a dataset from the Data tab, I click on the Layout tab (which shows
> the * next to the name to show that it's changed) and then click Save.
> Since
> I've done that I haven't seen a problem with dataset changes not being
> saved.
> Also, if you click Run from the Data tab, leave the result set displayed
> and
> go to another application for awhile, eventually Visual Studio pops up the
> same window that Enterprise Manager does having to do with the result set
> (can't remember the exact message). Sometimes a separate window pops up
> from
> Visual Studio with the message, sometimes Visual Studio just hangs.
> That's
> probably what you're seeing.
> Hope that helps!
> "Limor Bellison" wrote:
>> Hi,
>> I have a strange problem since yesterday.
>> I'm trying to change datasets in my project and I save changes. when I
>> close
>> the Report and open it again I see that the changes where not saved.
>> Also
>> the visual studio keeps getting stuck and I have to close it and open it
>> again.
>> I have noticed that if I change the query string from the Dataset
>> Properties
>> window, it does save it.
>> Since yesterday everything worked properlly, I didn't installed any thing
>> or
>> make any changes. I tried to do the same thing in another computer in a
>> new
>> project, and I have the same problem there. Also when I make changes in a
>> web
>> application in the visual studio, there are no problems.
>> I'm really desprate here,
>> can you help me?
>> Thanks,
>> Limor|||Hi,
I mannaged to overcome the saving problem by changing the dataset from the
"edit selected dataset" window. but I still have problems with visual studio
getting stuck, it happens unexpetedly after I change the dataset.
Thanx,
Limor
"Bruce L-C [MVP]" wrote:
> This is definitely the route to go. Sometimes when you change a dataset the
> designer does not recognize that a change was made so it does not get saved.
> Going to layout and clicking the save button resolves this problem.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "daw" <daw@.discussions.microsoft.com> wrote in message
> news:48C835F1-5BB3-4CEB-A969-73995D0F4F22@.microsoft.com...
> >I don't know if this is what your problem is, but I noticed that when I
> > change a dataset from the Data tab, I click on the Layout tab (which shows
> > the * next to the name to show that it's changed) and then click Save.
> > Since
> > I've done that I haven't seen a problem with dataset changes not being
> > saved.
> > Also, if you click Run from the Data tab, leave the result set displayed
> > and
> > go to another application for awhile, eventually Visual Studio pops up the
> > same window that Enterprise Manager does having to do with the result set
> > (can't remember the exact message). Sometimes a separate window pops up
> > from
> > Visual Studio with the message, sometimes Visual Studio just hangs.
> > That's
> > probably what you're seeing.
> > Hope that helps!
> >
> > "Limor Bellison" wrote:
> >
> >> Hi,
> >> I have a strange problem since yesterday.
> >> I'm trying to change datasets in my project and I save changes. when I
> >> close
> >> the Report and open it again I see that the changes where not saved.
> >> Also
> >> the visual studio keeps getting stuck and I have to close it and open it
> >> again.
> >> I have noticed that if I change the query string from the Dataset
> >> Properties
> >> window, it does save it.
> >> Since yesterday everything worked properlly, I didn't installed any thing
> >> or
> >> make any changes. I tried to do the same thing in another computer in a
> >> new
> >> project, and I have the same problem there. Also when I make changes in a
> >> web
> >> application in the visual studio, there are no problems.
> >>
> >> I'm really desprate here,
> >> can you help me?
> >>
> >> Thanks,
> >> Limor
>
>|||Where are you doing this from? Is it from the report wizard?
Once you have the report designed and you want to modify the dataset
definition then you just go to the data tab. There is either a graphical
design tool or you can switch to generic (2 pane versus the 4 pane). To
switch to generic designer you click on a button that is to the right of the
...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Limor Bellison" <LimorBellison@.discussions.microsoft.com> wrote in message
news:491F9403-8334-46BD-B5DB-0AD8268EFB95@.microsoft.com...
> Hi,
> I mannaged to overcome the saving problem by changing the dataset from the
> "edit selected dataset" window. but I still have problems with visual
> studio
> getting stuck, it happens unexpetedly after I change the dataset.
> Thanx,
> Limor
> "Bruce L-C [MVP]" wrote:
>> This is definitely the route to go. Sometimes when you change a dataset
>> the
>> designer does not recognize that a change was made so it does not get
>> saved.
>> Going to layout and clicking the save button resolves this problem.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "daw" <daw@.discussions.microsoft.com> wrote in message
>> news:48C835F1-5BB3-4CEB-A969-73995D0F4F22@.microsoft.com...
>> >I don't know if this is what your problem is, but I noticed that when I
>> > change a dataset from the Data tab, I click on the Layout tab (which
>> > shows
>> > the * next to the name to show that it's changed) and then click Save.
>> > Since
>> > I've done that I haven't seen a problem with dataset changes not being
>> > saved.
>> > Also, if you click Run from the Data tab, leave the result set
>> > displayed
>> > and
>> > go to another application for awhile, eventually Visual Studio pops up
>> > the
>> > same window that Enterprise Manager does having to do with the result
>> > set
>> > (can't remember the exact message). Sometimes a separate window pops
>> > up
>> > from
>> > Visual Studio with the message, sometimes Visual Studio just hangs.
>> > That's
>> > probably what you're seeing.
>> > Hope that helps!
>> >
>> > "Limor Bellison" wrote:
>> >
>> >> Hi,
>> >> I have a strange problem since yesterday.
>> >> I'm trying to change datasets in my project and I save changes. when I
>> >> close
>> >> the Report and open it again I see that the changes where not saved.
>> >> Also
>> >> the visual studio keeps getting stuck and I have to close it and open
>> >> it
>> >> again.
>> >> I have noticed that if I change the query string from the Dataset
>> >> Properties
>> >> window, it does save it.
>> >> Since yesterday everything worked properlly, I didn't installed any
>> >> thing
>> >> or
>> >> make any changes. I tried to do the same thing in another computer in
>> >> a
>> >> new
>> >> project, and I have the same problem there. Also when I make changes
>> >> in a
>> >> web
>> >> application in the visual studio, there are no problems.
>> >>
>> >> I'm really desprate here,
>> >> can you help me?
>> >>
>> >> Thanks,
>> >> Limor
>>|||Hi,
My rport is allready designed and I modify the dataset from the data tab by
the generic designer.
Limor
"Bruce L-C [MVP]" wrote:
> Where are you doing this from? Is it from the report wizard?
> Once you have the report designed and you want to modify the dataset
> definition then you just go to the data tab. There is either a graphical
> design tool or you can switch to generic (2 pane versus the 4 pane). To
> switch to generic designer you click on a button that is to the right of the
> ...
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Limor Bellison" <LimorBellison@.discussions.microsoft.com> wrote in message
> news:491F9403-8334-46BD-B5DB-0AD8268EFB95@.microsoft.com...
> > Hi,
> > I mannaged to overcome the saving problem by changing the dataset from the
> > "edit selected dataset" window. but I still have problems with visual
> > studio
> > getting stuck, it happens unexpetedly after I change the dataset.
> >
> > Thanx,
> > Limor
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> This is definitely the route to go. Sometimes when you change a dataset
> >> the
> >> designer does not recognize that a change was made so it does not get
> >> saved.
> >> Going to layout and clicking the save button resolves this problem.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "daw" <daw@.discussions.microsoft.com> wrote in message
> >> news:48C835F1-5BB3-4CEB-A969-73995D0F4F22@.microsoft.com...
> >> >I don't know if this is what your problem is, but I noticed that when I
> >> > change a dataset from the Data tab, I click on the Layout tab (which
> >> > shows
> >> > the * next to the name to show that it's changed) and then click Save.
> >> > Since
> >> > I've done that I haven't seen a problem with dataset changes not being
> >> > saved.
> >> > Also, if you click Run from the Data tab, leave the result set
> >> > displayed
> >> > and
> >> > go to another application for awhile, eventually Visual Studio pops up
> >> > the
> >> > same window that Enterprise Manager does having to do with the result
> >> > set
> >> > (can't remember the exact message). Sometimes a separate window pops
> >> > up
> >> > from
> >> > Visual Studio with the message, sometimes Visual Studio just hangs.
> >> > That's
> >> > probably what you're seeing.
> >> > Hope that helps!
> >> >
> >> > "Limor Bellison" wrote:
> >> >
> >> >> Hi,
> >> >> I have a strange problem since yesterday.
> >> >> I'm trying to change datasets in my project and I save changes. when I
> >> >> close
> >> >> the Report and open it again I see that the changes where not saved.
> >> >> Also
> >> >> the visual studio keeps getting stuck and I have to close it and open
> >> >> it
> >> >> again.
> >> >> I have noticed that if I change the query string from the Dataset
> >> >> Properties
> >> >> window, it does save it.
> >> >> Since yesterday everything worked properlly, I didn't installed any
> >> >> thing
> >> >> or
> >> >> make any changes. I tried to do the same thing in another computer in
> >> >> a
> >> >> new
> >> >> project, and I have the same problem there. Also when I make changes
> >> >> in a
> >> >> web
> >> >> application in the visual studio, there are no problems.
> >> >>
> >> >> I'm really desprate here,
> >> >> can you help me?
> >> >>
> >> >> Thanks,
> >> >> Limor
> >>
> >>
> >>
>
>|||Is this going against a stored procedure or are you inputting the query
directly. Is there any pattern? Are these very large or complicated queries,
lots of parameters?
One of the things that happens when you modify the query is RS tries to get
the new field list. If you have a query that this happens with routinely
then you can directly modify in the RDL. Make a copy first before you do
this. Then you do a view, code (I think, I am at home and don't remember the
menu to view the RDL).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Limor Bellison" <LimorBellison@.discussions.microsoft.com> wrote in message
news:3953B976-D223-44A9-AB2A-7113027A4122@.microsoft.com...
> Hi,
> My rport is allready designed and I modify the dataset from the data tab
> by
> the generic designer.
> Limor
> "Bruce L-C [MVP]" wrote:
>> Where are you doing this from? Is it from the report wizard?
>> Once you have the report designed and you want to modify the dataset
>> definition then you just go to the data tab. There is either a graphical
>> design tool or you can switch to generic (2 pane versus the 4 pane). To
>> switch to generic designer you click on a button that is to the right of
>> the
>> ...
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Limor Bellison" <LimorBellison@.discussions.microsoft.com> wrote in
>> message
>> news:491F9403-8334-46BD-B5DB-0AD8268EFB95@.microsoft.com...
>> > Hi,
>> > I mannaged to overcome the saving problem by changing the dataset from
>> > the
>> > "edit selected dataset" window. but I still have problems with visual
>> > studio
>> > getting stuck, it happens unexpetedly after I change the dataset.
>> >
>> > Thanx,
>> > Limor
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> This is definitely the route to go. Sometimes when you change a
>> >> dataset
>> >> the
>> >> designer does not recognize that a change was made so it does not get
>> >> saved.
>> >> Going to layout and clicking the save button resolves this problem.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "daw" <daw@.discussions.microsoft.com> wrote in message
>> >> news:48C835F1-5BB3-4CEB-A969-73995D0F4F22@.microsoft.com...
>> >> >I don't know if this is what your problem is, but I noticed that when
>> >> >I
>> >> > change a dataset from the Data tab, I click on the Layout tab (which
>> >> > shows
>> >> > the * next to the name to show that it's changed) and then click
>> >> > Save.
>> >> > Since
>> >> > I've done that I haven't seen a problem with dataset changes not
>> >> > being
>> >> > saved.
>> >> > Also, if you click Run from the Data tab, leave the result set
>> >> > displayed
>> >> > and
>> >> > go to another application for awhile, eventually Visual Studio pops
>> >> > up
>> >> > the
>> >> > same window that Enterprise Manager does having to do with the
>> >> > result
>> >> > set
>> >> > (can't remember the exact message). Sometimes a separate window
>> >> > pops
>> >> > up
>> >> > from
>> >> > Visual Studio with the message, sometimes Visual Studio just hangs.
>> >> > That's
>> >> > probably what you're seeing.
>> >> > Hope that helps!
>> >> >
>> >> > "Limor Bellison" wrote:
>> >> >
>> >> >> Hi,
>> >> >> I have a strange problem since yesterday.
>> >> >> I'm trying to change datasets in my project and I save changes.
>> >> >> when I
>> >> >> close
>> >> >> the Report and open it again I see that the changes where not
>> >> >> saved.
>> >> >> Also
>> >> >> the visual studio keeps getting stuck and I have to close it and
>> >> >> open
>> >> >> it
>> >> >> again.
>> >> >> I have noticed that if I change the query string from the Dataset
>> >> >> Properties
>> >> >> window, it does save it.
>> >> >> Since yesterday everything worked properlly, I didn't installed any
>> >> >> thing
>> >> >> or
>> >> >> make any changes. I tried to do the same thing in another computer
>> >> >> in
>> >> >> a
>> >> >> new
>> >> >> project, and I have the same problem there. Also when I make
>> >> >> changes
>> >> >> in a
>> >> >> web
>> >> >> application in the visual studio, there are no problems.
>> >> >>
>> >> >> I'm really desprate here,
>> >> >> can you help me?
>> >> >>
>> >> >> Thanks,
>> >> >> Limor
>> >>
>> >>
>> >>
>>

Problems with UNION-query

I have a sql statement with 2 SELECT statements combined with the keyword UNION. Something like this

SELECT a, b
FROM ... WHERE ...
UNION
SELECT c, d
FROM ... WHERE ...

If i execute this in the Query Analizer everything works fine and i get the results. But if i execute this through Visual Basic (with database.OpenRecordSet(sql) ) i get following error message:

The Microsoft Jet database engine cannot find the input table or query ... Make sure it exists and that its name is spelled correctly.

If i execute the first part of this combined query in VB (e.g. SELECT a, b FROM ... WHERE ...), no errors occur. The same with the second part.

I find it really strange and any hints and tips are welcome!
thanks in advance.Make sure you specify dbname..table. A different database may be your default via odbc or whatever you are using to connect through VB.|||Thx for the suggestion, but i already tried that one and unfortunately it doesn't solve the problem

Someone other hints, tips, tricks,...?sql

problems with two insert queries in a page

Hello

I have a question about something odd happening with an insert query.

I am working on a password protected site and I want to moniter general student use. I have an on page load even which picks up their user id and the url and sends it to the database

Because my site is a beta site, I also want the users to be able to add their comments as and when they want, so I built a small feedback form on each page.

The quick feedback form has a panel to hide it until a user clicks on a radio button and this reveals a text box and a submit button.

However, since I have added this second sql connection with an insert, I now get an error message I was getting a connection string error when I tried to point at the connection string in the web.config file, so I put the full path in to the database and now I get an error message that says the conTrackUser has not been declared, but as you can see from the code it certainly has. So I'm beginning to thing that the error is something entierly different, but that's probably me being thick.

I've pasted the two insert blocks below, I can't see anything wrong with them, but then I'm mostly groping about in the dark with asp.net.

Is it not possible to have two insert commands on a page? That would be daft.

Here is the code.

Track and initilize the radio button and panel for the feedback:


Sub Page_Load(Sender As Object, E As EventArgs)
Dim UserID as String = session("UserID")
pnlQuickFeedback.Visible = False
If Not Page.IsPostback Then
Dim conTrackUser As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommandconTrackUser = New SqlConnection("server='server'; user id='me'; password='nuts'; database='database'")
cmdInsert = New SqlCommand("TrackUser5530", conTrackUser)
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Parameters.Add("@.UserIdentity", session("UserID") )
cmdInsert.Parameters.Add("@.URL", Request.Url.AbsoluteUri )
conTrackUser.Open()
cmdInsert.ExecuteNonQuery()
conTrackUser.Close()
End If
End Sub

The sub for the feedback:


Sub SubmitFbbtn_Click(Sender As Object, E As EventArgs)
Dim UserID as String = session("UserID")'If Not Page.IsPostback Then
Dim conQuickFeedback As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
conQuickFeedback = New SqlConnection("server='server'; user id='me'; password='nuts'; database='database'")
cmdInsert = New SqlCommand("QuickComments5530", conQuickFeedback)
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Parameters.Add("@.UserIdentity", session("UserID") )
cmdInsert.Parameters.Add("@.Comments", txtComments.text)
cmdInsert.Parameters.Add("@.URL", Request.Url.AbsoluteUri )
conQuickFeedback.Open()
cmdInsert.ExecuteNonQuery()
conQuickFeedback.Close()
pnlQuickFeedback.Visible = False
feedbackFormbtn.Visible = True
feedbackFormbtn.Checked = False
End Sub

thanksTry using

data source='server'; user id='me'; password='nuts'; initial catalog='database'

Problems with Ttransaction Replication

The problems I have encountered with SQL 2000 transaction replication are as
follows:
1. There a lot of tables with identity columns.
2. There are about tables that don't have a primary key, thus they
can't be replicated.
3. Replication only monitors Insert, Replace and Delete commands in
the transaction logs.
I'm not too sure what the question is here - or is it just a moan

?
Transactional replication monitors data changes to a table with a primary
key. In SQL 2005 there is also the possibility of replicating most schema
changes automatically. That is the starting point. It might be that merge
replication would be more appropriate for your requirements if it is
impossible to add PKs.
HTH,
Paul Ibison

Problems with T-SQL in SQL Job

I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.You should be able to use the convert function directly:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<stephan@.pathcom.com> wrote in message
news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.|||Thanks for the advice Tom but I still receive the same error when it
tries to convert the data. Like I said, this works well in Analyzer,
but not when it's set up as a step in a Job.
SR
Tom Moreau wrote:
> You should be able to use the convert function directly:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> <stephan@.pathcom.com> wrote in message
> news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> I have a strange problem. I have some T-SQL that functions properly
in
> Analyzer but fails when I write the same code into a SQL Job:
> DECLARE @.DateTime datetime
> DECLARE @.DateNoTime varchar(10)
> SET @.DateTime = (GetDate())
> SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
'/'
> + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> DECLARE @.Result datetime
> SET @.Result = CONVERT(datetime, @.DateNoTime)
> The last line, the CONVERT, fails in the SQL Job. I've tried using
> CAST to see if that made a difference but I received the same error.
> Here is the error SQL Agent generates:
> The conversion of a char data type to a datetime data type resulted
in
> an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> step failed.
> Does anyone have any thoughts? I'm running SQL 2000.|||Tom's advice should've worked. Your convert statement
is faulty.
What version of SQL Server are you running?
<stephan@.pathcom.com> wrote in message
news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> Thanks for the advice Tom but I still receive the same error when it
> tries to convert the data. Like I said, this works well in Analyzer,
> but not when it's set up as a step in a Job.
> SR
> Tom Moreau wrote:
> > You should be able to use the convert function directly:
> >
> > SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > <stephan@.pathcom.com> wrote in message
> > news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> > I have a strange problem. I have some T-SQL that functions properly
> in
> > Analyzer but fails when I write the same code into a SQL Job:
> >
> > DECLARE @.DateTime datetime
> > DECLARE @.DateNoTime varchar(10)
> >
> > SET @.DateTime = (GetDate())
> > SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
> '/'
> > + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> > rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> >
> > DECLARE @.Result datetime
> > SET @.Result = CONVERT(datetime, @.DateNoTime)
> >
> > The last line, the CONVERT, fails in the SQL Job. I've tried using
> > CAST to see if that made a difference but I received the same error.
> > Here is the error SQL Agent generates:
> >
> > The conversion of a char data type to a datetime data type resulted
> in
> > an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> > Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> > step failed.
> >
> > Does anyone have any thoughts? I'm running SQL 2000.
>|||Stephan,
Try replacing the final statement with
SET @.Result = CONVERT(datetime, @.DateNoTime,103)
For whatever reason, the context in which this runs as a job step
has different language or dateformat settings than your Query Analyzer,
and you are applying CONVERT without specifying the format of
the date string, hence leaving the interpretation dependent on the context.
By the way, you can remove the time from a date without anything
depending on string formats for datetime:
SET @.Result = DATEADD(d,DATEDIFF(d,0,@.DateTime),0)
Steve Kass
Drew University
stephan@.pathcom.com wrote:
>I have a strange problem. I have some T-SQL that functions properly in
>Analyzer but fails when I write the same code into a SQL Job:
>DECLARE @.DateTime datetime
>DECLARE @.DateNoTime varchar(10)
>SET @.DateTime = (GetDate())
>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>DECLARE @.Result datetime
>SET @.Result = CONVERT(datetime, @.DateNoTime)
>The last line, the CONVERT, fails in the SQL Job. I've tried using
>CAST to see if that made a difference but I received the same error.
>Here is the error SQL Agent generates:
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>step failed.
>Does anyone have any thoughts? I'm running SQL 2000.
>
>|||Armando,
Tom's statement didn't work because it did not specify a format for
the conversion back to datetime. Tom's statement would have worked this
way:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
SK
Armando Prato wrote:
>Tom's advice should've worked. Your convert statement
>is faulty.
>What version of SQL Server are you running?
><stephan@.pathcom.com> wrote in message
>news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
>
>>Thanks for the advice Tom but I still receive the same error when it
>>tries to convert the data. Like I said, this works well in Analyzer,
>>but not when it's set up as a step in a Job.
>>SR
>>Tom Moreau wrote:
>>
>>You should be able to use the convert function directly:
>>SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
>>--
>>Tom
>>---
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>><stephan@.pathcom.com> wrote in message
>>news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
>>I have a strange problem. I have some T-SQL that functions properly
>>
>>in
>>
>>Analyzer but fails when I write the same code into a SQL Job:
>>DECLARE @.DateTime datetime
>>DECLARE @.DateNoTime varchar(10)
>>SET @.DateTime = (GetDate())
>>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
>>
>>'/'
>>
>>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>>DECLARE @.Result datetime
>>SET @.Result = CONVERT(datetime, @.DateNoTime)
>>The last line, the CONVERT, fails in the SQL Job. I've tried using
>>CAST to see if that made a difference but I received the same error.
>>Here is the error SQL Agent generates:
>>The conversion of a char data type to a datetime data type resulted
>>
>>in
>>
>>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>>step failed.
>>Does anyone have any thoughts? I'm running SQL 2000.
>>
>
>|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Armando,
> Tom's statement didn't work because it did not specify a format for
> the conversion back to datetime. Tom's statement would have worked this
> way:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
> SK
>
> Armando Prato wrote:
> >Tom's advice should've worked. Your convert statement
> >is faulty.
> >
> >What version of SQL Server are you running?
> >
> ><stephan@.pathcom.com> wrote in message
> >news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> >
> >
> >>Thanks for the advice Tom but I still receive the same error when it
> >>tries to convert the data. Like I said, this works well in Analyzer,
> >>but not when it's set up as a step in a Job.
> >>
> >>SR
> >>
> >>Tom Moreau wrote:
> >>
> >>
> >>You should be able to use the convert function directly:
> >>
> >>SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> >>
> >>--
> >>Tom
> >>
> >>---
> >>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >>SQL Server MVP
> >>Columnist, SQL Server Professional
> >>Toronto, ON Canada
> >>www.pinnaclepublishing.com
> >>
> >>
> >><stephan@.pathcom.com> wrote in message
> >>news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> >>I have a strange problem. I have some T-SQL that functions properly
> >>
> >>
> >>in
> >>
> >>
> >>Analyzer but fails when I write the same code into a SQL Job:
> >>
> >>DECLARE @.DateTime datetime
> >>DECLARE @.DateNoTime varchar(10)
> >>
> >>SET @.DateTime = (GetDate())
> >>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
> >>
> >>
> >>'/'
> >>
> >>
> >>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> >>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> >>
> >>DECLARE @.Result datetime
> >>SET @.Result = CONVERT(datetime, @.DateNoTime)
> >>
> >>The last line, the CONVERT, fails in the SQL Job. I've tried using
> >>CAST to see if that made a difference but I received the same error.
> >>Here is the error SQL Agent generates:
> >>
> >>The conversion of a char data type to a datetime data type resulted
> >>
> >>
> >>in
> >>
> >>
> >>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> >>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> >>step failed.
> >>
> >>Does anyone have any thoughts? I'm running SQL 2000.
> >>
> >>
> >
> >
> >
> >|||Thanks guys. That worked like a charm.
Thanks Steve for the explanation why it didn't work. Strange that the
Agent and the Analyzer use a different engine...
SR|||Stephan,
you may find that the owner of the sql agent job(and/or the sqlserver agent
account) uses a different account to the one you have used to access QA and
as a result possibly has a different default language setting which has
resulted in a different date format.
HTH
<stephan@.pathcom.com> wrote in message
news:1104433299.670932.135410@.c13g2000cwb.googlegroups.com...
> Thanks guys. That worked like a charm.
> Thanks Steve for the explanation why it didn't work. Strange that the
> Agent and the Analyzer use a different engine...
> SR
>

Problems with T-SQL in SQL Job

Problems with T-SQL Debugger and Query Analyzer

I'm receiving this error message on the majority of machines, from my
developers, trying to debug T-SQL Stored Procedures (including my machine)...
Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on DESEN (Error = 0x800706ba). Ensure that client-side components, such as
SQLDBREG.EXE, are installed and registered on CONSOFT-08. Debugging disabled
for connection 89.
Strangely, one of the machines works fine, it's running Windows 2000...
The other machines runs Win2k or XP... None of them work...
The server and the client runs the same SQL version (SP3)...
I saw an article in BOL that didn't help to solve the problem (Troubleshoot
T-SQL Debugger)...
Does anybody knows something about that?
If you need any more information that could help you to determine the
problem, plz contact me... I'm very thank!!
Hi Rafa,
Is this article helpful then?
INF: Transact-SQL Debugger Limitations and Troubleshooting Tips for SQL
Server 2000
http://support.microsoft.com/default...b;en-us;280101
Yih-Yoon Lee
Rafa? wrote:
> I'm receiving this error message on the majority of machines, from my
> developers, trying to debug T-SQL Stored Procedures (including my machine)...
> Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on DESEN (Error = 0x800706ba). Ensure that client-side components, such as
> SQLDBREG.EXE, are installed and registered on CONSOFT-08. Debugging disabled
> for connection 89.
> Strangely, one of the machines works fine, it's running Windows 2000...
> The other machines runs Win2k or XP... None of them work...
> The server and the client runs the same SQL version (SP3)...
> I saw an article in BOL that didn't help to solve the problem (Troubleshoot
> T-SQL Debugger)...
> Does anybody knows something about that?
> If you need any more information that could help you to determine the
> problem, plz contact me... I'm very thank!!

Problems with TRIGGER

I still have problems with my TRIGGER (T-SQL) to update a table. I will read some information from INSERTD table and create a String to execute EXEC(@.sSQL), I’m creating a dynamic SQL because the name of then table I’ll update is in the INSERTED table, the problem I have is that : I read a date from the INSERTED table (31/08/2007 12:15:30) and write this date into then update table, but the date change to this : 31/08/2007 12:16:00 above is part of the trigger that do this :

SELECT @.TipoConta = Tipo, @.Inscricao = Inscricao, @.MesAno = Mes_Ref, @.DataPagam = Data_Quitacao, @.ValorPago = Valor_Pago, @.LiberMult = Libera_Multa FROM INSERTED

SET @.YearTable = Right(@.MesAno, 4)

SET @.MyTable = 'LEITURAS_VALORES_' + @.YearTable

set @.sSQL =

'UPDATE ' +

@.MyTable +

' SET ' +

'VALOR_PAGO = ' + str(@.ValorPago, 12, 2) +

', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

', SITUACAO = '''+ @.Situacao + '''' +

', LIB_MULTA = ' + str(@.LiberMult, 2) +

' WHERE ' +

'INSCRICAO = ' + str(@.Inscricao, 8) +

'AND MES_ANO = ''' + @.AuxVar + ''''

exec (@.sSQL)

@.DataPagam is the date I’m reading from INSERTED

The problem is in the code : ', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

Date format (dd/mm/yyyy hh:mm Tongue Tied s) we use here in Brazil

Any one can help me,

Thanks, very much

Try something like this:

', DATA_SITUACAO = CAST(''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + ''' AS DATETIME) '
|||

It could be that your installation is set to us_english which has a dateformat of MDY.

In your trigger, try setting SET DATEFORMAT DMY at the start.

HTH!

|||

I put ''' AS DATETIME) '

Error Source :

.NetSqlCliente Data Provider

Error Message :

Incorrect syntax near then keyword 'AS'

|||

I have already done it : SET DATEFORMAT DMY in my TRIGGER

Thanks

Problems with TRIGGER

SELECT @.TipoConta = Tipo, @.Inscricao = Inscricao, @.MesAno = Mes_Ref, @.DataPagam = Data_Quitacao, @.ValorPago = Valor_Pago, @.LiberMult = Libera_Multa FROM INSERTED

SET @.YearTable = Right(@.MesAno, 4)

SET @.MyTable = 'LEITURAS_VALORES_' + @.YearTable

set @.sSQL =

'UPDATE ' +

@.MyTable +

' SET ' +

'VALOR_PAGO = ' + str(@.ValorPago, 12, 2) +

', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

', SITUACAO = '''+ @.Situacao + '''' +

', LIB_MULTA = ' + str(@.LiberMult, 2) +

' WHERE ' +

'INSCRICAO = ' + str(@.Inscricao, 8) +

'AND MES_ANO = ''' + @.AuxVar + ''''

exec (@.sSQL)

@.DataPagam is the date I’m reading from INSERTED

The problem is in the code : ', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

Date format (dd/mm/yyyy hh:mm Tongue Tied s) we use here in Brazil

Any one can help me,

Thanks, very much

Try something like this:

', DATA_SITUACAO = CAST(''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + ''' AS DATETIME) '
|||

It could be that your installation is set to us_english which has a dateformat of MDY.

In your trigger, try setting SET DATEFORMAT DMY at the start.

HTH!

|||

I put ''' AS DATETIME) '

Error Source :

.NetSqlCliente Data Provider

Error Message :

Incorrect syntax near then keyword 'AS'

|||

I have already done it : SET DATEFORMAT DMY in my TRIGGER

Thanks

Problems with transactional replication.

Hi, I have confired some transactional replication with puss subscrition
between servers running sql server 2000 sp3. The replication works fine,
but yesterday after the publication/distribution was restared the log
reader agent of each publication reports this error:
The process could not execute 'sp_msadd_repl_commands27hp'
on publishername
Do somebody know how could I resolve the problem.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Please try increasing theLogReader's QueryTimeout parameter.
Also it might be interesting to check the view msdistribution_status to see
how many commands are in the pipeline.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Problems with transactional replication without making a snapshot

I try to configure replication between 2 SQL Server 2000 servers with another
SQL Server 2000 machine configured as the distributor. (All with SP3)
I initialize the replication with 'subscribers already has the data schema
en the data'
and I use pull subscription.
When I have finished the replication setup the next error occurred on the
Distribution agent:
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__0169315C'. Cannot
insert duplicate key in object '#00750D23'.
I've disabled the whole replication and reboot the servers but the error
still appears.
What could be the reason of the problem I’ve defined?
have a look at this kb article:
http://support.microsoft.com/default...b;en-us;813494
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Marijn" <Marijn@.discussions.microsoft.com> wrote in message
news:AE46BE44-3E53-4CD2-ADF4-9D01EE86BDF3@.microsoft.com...
>I try to configure replication between 2 SQL Server 2000 servers with
>another
> SQL Server 2000 machine configured as the distributor. (All with SP3)
> I initialize the replication with 'subscribers already has the data schema
> en the data'
> and I use pull subscription.
> When I have finished the replication setup the next error occurred on the
> Distribution agent:
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__0169315C'.
> Cannot
> insert duplicate key in object '#00750D23'.
> I've disabled the whole replication and reboot the servers but the error
> still appears.
> What could be the reason of the problem I've defined?
>
>

Problems with transactional replication

Hello All!
Today I've found one strange problem ... I have tables like
something_another_2004_9 (so name_YEAR_MONTH) ... funny thing happened when
I got to the month 10,
I see that the insert/update/delete stored procedures are missing the last
digit in their name.
So, for the 2004_9 month I get sp_MSins_something_another_2004_9,
sp_MSupd_something_another_2004_9, etc .
But for the 2004_10 month I get sp_MSins_something_another_2004_1,
sp_MSupd_something_another_2004_1 ... where is the last 0!? Same happened
for 2004_11 table.
What could be the problem? Are the table names too long or what ?!
Thanks four your input!
Kind regards,
Dejan
can you post the exact names of the tables which are causing the problems
with stored procedure generation?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dejan Markic" <dejan@.akton.is> wrote in message
news:hy2fd.4744$F6.1274490@.news.siol.net...
> Hello All!
> Today I've found one strange problem ... I have tables like
> something_another_2004_9 (so name_YEAR_MONTH) ... funny thing happened
when
> I got to the month 10,
> I see that the insert/update/delete stored procedures are missing the last
> digit in their name.
> So, for the 2004_9 month I get sp_MSins_something_another_2004_9,
> sp_MSupd_something_another_2004_9, etc .
> But for the 2004_10 month I get sp_MSins_something_another_2004_1,
> sp_MSupd_something_another_2004_1 ... where is the last 0!? Same happened
> for 2004_11 table.
> What could be the problem? Are the table names too long or what ?!
> Thanks four your input!
> Kind regards,
> Dejan
>
>
|||Hello!
Sorry for the late response ...
Full table names are:
billing_record_2004_9, billing_record_2004_10 ...
Paul,
I see normal procs name (without missin last number) ... I don't know if
this makes any difference, but before I ran that I already 'repaired' this
by hand (renaming and creating new procs for this new tables). I'm just
afraid that this will happen again next month (12 = two letters

Problems with transactional replication

I got transactional replication between two servers , two identical database
(one publusher and one subscriber, distributor is on publisher server),
everything works clear however a few days ago i got on
Replication Monitor --> Publishers --> MyServerName failed icon snapshot
agent, now on path above i have two publication one that i buil transactional
with 3 agents, and one snapshot i don't know how it created.
Also when i trying to remove it i got the following message description
couldn't retrive information about publication, ... .... ... publication
name not found in the TransPublication collection.
Any ideas ? TNX
Message posted via http://www.droptable.com
You have inconsistent meta data in your distribution database. The best way
to fix this is unfortunately to start carefully editing the metadata tables.
Sometimes it is better to live with these phantom messages.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"akej via droptable.com" <forum@.droptable.com> wrote in message
news:507682708C7A4@.droptable.com...
> I got transactional replication between two servers , two identical
database
> (one publusher and one subscriber, distributor is on publisher server),
> everything works clear however a few days ago i got on
> Replication Monitor --> Publishers --> MyServerName failed icon
snapshot
> agent, now on path above i have two publication one that i buil
transactional
> with 3 agents, and one snapshot i don't know how it created.
> Also when i trying to remove it i got the following message description
> couldn't retrive information about publication, ... .... ... publication
> name not found in the TransPublication collection.
>
> Any ideas ? TNX
> --
> Message posted via http://www.droptable.com
|||>carefully editing the metadata tables
I don't really understande what does it mean, can u explain me plz?
Message posted via http://www.droptable.com
|||somebody can help me with problem that i experiance
Message posted via http://www.droptable.com

Problems with transaction scope time out

Hi, i'm trying to run wrap a transaction around a block of code to make sure every statement is carried out. Here's what i have:

using (TransactionScope ts =newTransactionScope())

{
...
ts.Complete();
}

The code executed in the ... part is a set of sql commands within my dal. If this above expression takes less than 30 seconds everything is carried out correctly, however if it takes longer than 30 seconds i receive an sql timeout error. I tried modify the above code to:

using (TransactionScope ts =newTransactionScope(TransactionScopeOption.Required,TimeSpan.FromMinutes(5)))

{
...
ts.Complete();
}

But it did not seem to make a difference. I suppose the easiest option would be to increase the timeout for every sql command in my dal but i feel that the 30 second timeout is sufficient for a single command.

Appreciate if someone could tell me how this should be done correctly. Thanks

Try to run the same proc/T-SQL from query analyzer within a transaction and see how long it takes. Try to tune your queries to run under 1-2 secs.

|||Hi, i made the sql as efficient as possible but still no joy so i figured that it wasn't a good idea to wrap a transaction around what i was doing. I simply logged whether the process had been completed and all is well.

Problems with TLog Backup

To All:
TLog backups have been going smoothly for many months. Now, the following
messages are generated (always together) with increasing frequency.
3041 :
BACKUP failed to complete the command backup log "Apollo" to
VIRTUAL_DEVICE='VNBU0-2416-2316' with blocksize = 512, maxtransfersize = 65536, buffercount = 1
18210 :
BackupVirtualDeviceSet::Initialize: CoCreateInstance failure on
backup device 'VNBU0-2416-2316'. Operating system error -2147467262(No such
interface supported).
The fellow in charge of backups wants me to reboot the server daily. This
isn't the answer to this problem. Any suggestions are appreciated.
Regards,
Michael CliffordIs the backup device local ?
If not can you backup locally ?
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Michael Clifford" <mjclifford_nospam@.mindspring.com> wrote in message
news:ufZJblQiDHA.2164@.TK2MSFTNGP09.phx.gbl...
> To All:
> TLog backups have been going smoothly for many months. Now, the following
> messages are generated (always together) with increasing frequency.
> 3041 :
> BACKUP failed to complete the command backup log "Apollo" to
> VIRTUAL_DEVICE='VNBU0-2416-2316' with blocksize = 512, maxtransfersize => 65536, buffercount = 1
> 18210 :
> BackupVirtualDeviceSet::Initialize: CoCreateInstance failure on
> backup device 'VNBU0-2416-2316'. Operating system error -2147467262(No
such
> interface supported).
>
> The fellow in charge of backups wants me to reboot the server daily. This
> isn't the answer to this problem. Any suggestions are appreciated.
>
> Regards,
> Michael Clifford
>
>
>|||Allan,
Backups are done with an enterprise level Veritas SQL Agent on the local box
and a tape backup server remotely.
Michael
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uEA1oASiDHA.1508@.TK2MSFTNGP10.phx.gbl...
> Is the backup device local ?
> If not can you backup locally ?
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Michael Clifford" <mjclifford_nospam@.mindspring.com> wrote in message
> news:ufZJblQiDHA.2164@.TK2MSFTNGP09.phx.gbl...
> > To All:
> >
> > TLog backups have been going smoothly for many months. Now, the
following
> > messages are generated (always together) with increasing frequency.
> >
> > 3041 :
> >
> > BACKUP failed to complete the command backup log "Apollo" to
> > VIRTUAL_DEVICE='VNBU0-2416-2316' with blocksize = 512, maxtransfersize => > 65536, buffercount = 1
> >
> > 18210 :
> >
> > BackupVirtualDeviceSet::Initialize: CoCreateInstance failure on
> > backup device 'VNBU0-2416-2316'. Operating system error -2147467262(No
> such
> > interface supported).
> >
> >
> >
> > The fellow in charge of backups wants me to reboot the server daily.
This
> > isn't the answer to this problem. Any suggestions are appreciated.
> >
> >
> >
> > Regards,
> >
> > Michael Clifford
> >
> >
> >
> >
> >
> >
>|||OK so these are those "Suck the guts" backups from an "Agent" ?
Can you issue a simple BACKUP DATABASE <<name>> TO DISK = '<<local disk
file>>'
Have you seen this
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q323/6/02.asp&NoWebContent=1
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Michael Clifford" <mjclifford_nospam@.mindspring.com> wrote in message
news:uzrz%23bZiDHA.1624@.tk2msftngp13.phx.gbl...
> Allan,
> Backups are done with an enterprise level Veritas SQL Agent on the local
box
> and a tape backup server remotely.
> Michael
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:uEA1oASiDHA.1508@.TK2MSFTNGP10.phx.gbl...
> > Is the backup device local ?
> > If not can you backup locally ?
> >
> > --
> > --
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Michael Clifford" <mjclifford_nospam@.mindspring.com> wrote in message
> > news:ufZJblQiDHA.2164@.TK2MSFTNGP09.phx.gbl...
> > > To All:
> > >
> > > TLog backups have been going smoothly for many months. Now, the
> following
> > > messages are generated (always together) with increasing frequency.
> > >
> > > 3041 :
> > >
> > > BACKUP failed to complete the command backup log "Apollo" to
> > > VIRTUAL_DEVICE='VNBU0-2416-2316' with blocksize = 512, maxtransfersize
=> > > 65536, buffercount = 1
> > >
> > > 18210 :
> > >
> > > BackupVirtualDeviceSet::Initialize: CoCreateInstance failure
on
> > > backup device 'VNBU0-2416-2316'. Operating system error -2147467262(No
> > such
> > > interface supported).
> > >
> > >
> > >
> > > The fellow in charge of backups wants me to reboot the server daily.
> This
> > > isn't the answer to this problem. Any suggestions are appreciated.
> > >
> > >
> > >
> > > Regards,
> > >
> > > Michael Clifford
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>sql

Problems with Timeout expired

I have a SQL Server database which has a number of tables and views. Using SQL Server Management Studio Express I can open most of the views without a problem. However, some of the views cause an SQL Connection Error but not every time I open them.

Error Source: Net SqlClient Data Provider

Error Message: Timeout expired. The timeout period expired ....

I have looked at the help given for this error but have come to the conclusion that none of the suggested causes are relevant as most views open without a problem. It would appear that I need to increase the timeout value but I can't find where I do that.

It must be simple..

I have just checked that the Execution Time-out in the SSMSE options is set to 0.

Friday, March 30, 2012

problems with VB6 and CR10

Problems with values display as currency

Problems with using Variables in place of Nested Queires

problems with useing ms2000 sql

problems with URL

problems with updating datasets in visual studio .net

Problems with UNION-query

problems with two insert queries in a page

Problems with Ttransaction Replication

Problems with T-SQL in SQL Job

Problems with T-SQL in SQL Job

Problems with T-SQL in SQL Job

Problems with T-SQL Debugger and Query Analyzer

Problems with TRIGGER

Problems with TRIGGER

Problems with transactional replication.

Problems with transactional replication without making a snapshot

Problems with transactional replication

Problems with transactional replication

Problems with transaction scope time out

Problems with TLog Backup

Problems with Timeout expired

Problems using SQL

Blog Archive

About Me