sql login permissions: format

Showing posts with label format. Show all posts

Friday, March 30, 2012

Long text

Hi,
I have written a UDF that calls itself and nests its operation. The output
is a varchar value in XML format. But sometimes the XML is more than 8000
characters and varchar is small for that. It is not possible to use text
data type. What can I do?
Any help would be greatly appreciated,
LeilaCan you form the XML using FOR XML instead of your own recursive UDF?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Leila" <leilas@.hotpop.com> wrote in message
news:%23XJncCx5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have written a UDF that calls itself and nests its operation. The output
> is a varchar value in XML format. But sometimes the XML is more than 8000
> characters and varchar is small for that. It is not possible to use text
> data type. What can I do?
> Any help would be greatly appreciated,
> Leila
>|||Actually I tried, maybe it's because lack of my skills in writing queries
that I couldn't use FOR XML, hence used UDF. Could you please have a look to
my post with subject: Retrieving Data in XML Format
Thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:us1MORx5EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Can you form the XML using FOR XML instead of your own recursive UDF?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23XJncCx5EHA.2624@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> I have written a UDF that calls itself and nests its operation. The
>> output
>> is a varchar value in XML format. But sometimes the XML is more than 8000
>> characters and varchar is small for that. It is not possible to use text
>> data type. What can I do?
>> Any help would be greatly appreciated,
>> Leila
>>
>|||Start reading:
www.sqlxml.org
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Leila" <leilas@.hotpop.com> wrote in message
news:uNJZHFy5EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Actually I tried, maybe it's because lack of my skills in writing queries
> that I couldn't use FOR XML, hence used UDF. Could you please have a look
to
> my post with subject: Retrieving Data in XML Format
> Thanks
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:us1MORx5EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > Can you form the XML using FOR XML instead of your own recursive UDF?
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:%23XJncCx5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> >> Hi,
> >> I have written a UDF that calls itself and nests its operation. The
> >> output
> >> is a varchar value in XML format. But sometimes the XML is more than
8000
> >> characters and varchar is small for that. It is not possible to use
text
> >> data type. What can I do?
> >> Any help would be greatly appreciated,
> >> Leila
> >>
> >>
> >
> >
>

Long text

Friday, March 23, 2012

Logshipping problem with naming transaction log backups

Hey, my logshipping is working just fine but the name of the backup file is of a concern to me. It's using the name in the format of DbName_20061011200002.trn. I know 20061011 is the date. That's fine. 2000 is supposed to be the time but it's giving the wrong time. It's supposed to be 1500 because the time right now is 3pm. And I dont know why the last 2 digits before the .trn are for. Is this something new in SQL 2005 logshipping? SQL 2000 didn't have it. Thank you.

Log shipping uses UTC time when naming the files. This ensures that global deployments of log shipping work consistently.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||Is there any possibility for me to change the naming convention. I wanted to go back to SQL 2000 naming convention. Please let me know. Thank you for your help.|||

You can use VBScript to rename your transaction log files to your requirements. Here's a sample script I use:

'============================
'Script to change a filename using timestamps
strMonth = DatePart("m", Now())
strDay = DatePart("d",Now())
if Len(strMonth)=1 then
strMonth = "0" & strMonth
else
strMonth = strMonth
end if

if Len(strDay)=1 then
strDay = "0" & strDay
else
strDay = strDay
end if

strFileName = "LOG_" & DatePart("yyyy",Now()) & strMonth & strDay & FormatDateTime(Now(), vbShortTime) & ".TRN"
strFileName = Replace(strFileName,":","")
Set objFSO = CreateObject("Scripting.FileSystemObject")
'===============================================
'Change the drive letter, folder and filename if necessary
objFSO.MoveFile "D:\SQL_Backups\LOG.TRN" , "D:\SQL_Backups\" & strFileName

You can include this in your Log Shipping Jobs on both the source and the destination servers. One thing to note is that the names should be the same as what you created when the transaction logs are restored on the destination. Your Database maintenance plan won't work on this. Might as well use a customized log shipping plan.

Friday, February 24, 2012

Login problems when rendering reports

This is a multi-part message in MIME format.
--=_NextPart_000_008D_01C4C8B6.1764EDB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I am continually getting the following error when attempting to render = my reports from http://hostname/ReportServer...
An error has occurred during report processing. (rsProcessingAborted) = Get Online Help a.. Cannot create a connection to data source 'DataCollection'. = (rsErrorOpeningConnection) Get Online Help a.. Login failed for user '(null)'. Reason: Not associated with a = trusted SQL Server connection.
I have tried EVERY possible security setting for the DataSource = (integrated, NO Credentials, SQL authentican, etc.), and it still = doesn't work.
Here is how it's set up:
1. Reporting Services metadata location on Server A:
2. Datasource (from which I am pulling data to render reports) is on = Server B:
3. Created a user called ReportUser on BOTH the Reporting Services = server as well as the Datasource. I made sure the user has read access = in the dbs on both servers (including adding to the the RSExecRole).
4. I have designed my reports using ReportUser as the DataSource = connection.
Books online is not helping me troubleshoot this error.
Any ideas?
Thanks,
Dan
--=_NextPart_000_008D_01C4C8B6.1764EDB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I am continually getting the following = error when attempting to render my reports from
http://hostname/ReportServer...= .

An error has occurred during report = processing. (rsProcessingAborted) Get Online = Help
Cannot create a connection to data = source 'DataCollection'. (rsErrorOpeningConnection) Get = Online Help
Login failed for user '(null)'. = Reason: Not associated with a trusted SQL Server connection. =

I have tried EVERY possible security = setting for the DataSource (integrated, NO Credentials, SQL authentican, etc.), and = it still doesn't work.
Here is how it's set up:

1. Reporting Services metadata location = on Server A:
2. Datasource (from which I am pulling = data to render reports) is on Server B:
3. Created a user called ReportUser on BOTH the Reporting Services server as = well as the Datasource. I made sure the user has read access in the dbs on = both servers (including adding to the the RSExecRole).
4. I have designed my reports using = ReportUser as the DataSource connection.

Books online is not helping me = troubleshoot this error.

Any ideas?

Thanks,
Dan

--=_NextPart_000_008D_01C4C8B6.1764EDB0--This is a multi-part message in MIME format.
--=_NextPart_000_00AD_01C4C8BB.E4981C30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Okay: I finally got it to work by configuring my datasource to use ="Shared DataSource".
And then: Configuring my datasource (in ReportDesigner) to use a SQL =Authenticated login...
The security model in ReportServices is a bit tough to navigate in my =opinion.
"Dan Carollo (hotmail)" <dcarollo@.hotmail.com> wrote in message =news:eMhumkPyEHA.2764@.TK2MSFTNGP10.phx.gbl...
I am continually getting the following error when attempting to render =my reports from http://hostname/ReportServer...
An error has occurred during report processing. (rsProcessingAborted) =Get Online Help a.. Cannot create a connection to data source 'DataCollection'. =(rsErrorOpeningConnection) Get Online Help a.. Login failed for user '(null)'. Reason: Not associated with a =trusted SQL Server connection.
I have tried EVERY possible security setting for the DataSource =(integrated, NO Credentials, SQL authentican, etc.), and it still =doesn't work.
Here is how it's set up:
1. Reporting Services metadata location on Server A:
2. Datasource (from which I am pulling data to render reports) is on =Server B:
3. Created a user called ReportUser on BOTH the Reporting Services =server as well as the Datasource. I made sure the user has read access =in the dbs on both servers (including adding to the the RSExecRole).
4. I have designed my reports using ReportUser as the DataSource =connection.
Books online is not helping me troubleshoot this error.
Any ideas?
Thanks,
Dan
--=_NextPart_000_00AD_01C4C8BB.E4981C30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Okay: I finally got it to work by =configuring my datasource to use "Shared DataSource".
And then: Configuring my =datasource (in ReportDesigner) to use a SQL Authenticated login...
The security model in ReportServices is =a bit tough to navigate in my opinion.
"Dan Carollo (hotmail)" =wrote in message news:eMhumkPyEHA.2764=@.TK2MSFTNGP10.phx.gbl...
I am continually getting the =following error when attempting to render my reports from
http://hostname/ReportServer">http://hostname/ReportServer...=.

An error has occurred during report =processing. (rsProcessingAborted) Get =Online Help
Cannot create a connection to data =source 'DataCollection'. (rsErrorOpeningConnection) Get =Online Help
Login failed for user '(null)'. =Reason: Not associated with a trusted SQL Server connection. =
I have tried EVERY possible security =setting for the DataSource (integrated, NO Credentials, SQL authentican, etc.), =and it still doesn't work.
Here is how it's set up:

1. Reporting Services metadata =location on Server A:
2. Datasource (from which I am =pulling data to render reports) is on Server B:
3. Created a user called ReportUser on BOTH the Reporting Services server as =well as the Datasource. I made sure the user has read access in the dbs =on both servers (including adding to the the RSExecRole).
4. I have designed my reports using =ReportUser as the DataSource connection.

Books online is not helping me =troubleshoot this error.

Any ideas?

Thanks,
Dan

--=_NextPart_000_00AD_01C4C8BB.E4981C30--|||This is a multi-part message in MIME format.
--=_NextPart_000_002C_01C4CB2B.5BA97140
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Well: This is odd...
I came back into the office on Monday morning -- attempted to render =the same reports and I'm back to the same error again:
Cannot create a connection to data source 'DataCollection'. =(rsErrorOpeningConnection) Get Online Help a.. Login failed for user '(null)'. Reason: Not associated with a =trusted SQL Server connection.
"Dan Carollo (hotmail)" <dcarollo@.hotmail.com> wrote in message =news:#Ba5z7PyEHA.3844@.TK2MSFTNGP12.phx.gbl...
Okay: I finally got it to work by configuring my datasource to use ="Shared DataSource".
And then: Configuring my datasource (in ReportDesigner) to use a SQL =Authenticated login...
The security model in ReportServices is a bit tough to navigate in my =opinion.
"Dan Carollo (hotmail)" <dcarollo@.hotmail.com> wrote in message =news:eMhumkPyEHA.2764@.TK2MSFTNGP10.phx.gbl...
I am continually getting the following error when attempting to =render my reports from http://hostname/ReportServer...
An error has occurred during report processing. =(rsProcessingAborted) Get Online Help a.. Cannot create a connection to data source 'DataCollection'. =(rsErrorOpeningConnection) Get Online Help a.. Login failed for user '(null)'. Reason: Not associated with =a trusted SQL Server connection.
I have tried EVERY possible security setting for the DataSource =(integrated, NO Credentials, SQL authentican, etc.), and it still =doesn't work.
Here is how it's set up:
1. Reporting Services metadata location on Server A:
2. Datasource (from which I am pulling data to render reports) is on =Server B:
3. Created a user called ReportUser on BOTH the Reporting Services =server as well as the Datasource. I made sure the user has read access =in the dbs on both servers (including adding to the the RSExecRole).
4. I have designed my reports using ReportUser as the DataSource =connection.
Books online is not helping me troubleshoot this error.
Any ideas?
Thanks,
Dan
--=_NextPart_000_002C_01C4CB2B.5BA97140
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Well: This is =odd...
I came back into the office on Monday =morning -- attempted to render the same reports and I'm back to the same error again:
Cannot =create a connection to data source 'DataCollection'. (rsErrorOpeningConnection) =Get Online =Help
Login failed for user '(null)'. =Reason: Not associated with a trusted SQL Server connection.
"Dan Carollo (hotmail)" =wrote in message news:#Ba5z7PyEHA.3844=@.TK2MSFTNGP12.phx.gbl...
Okay: I finally got it to work =by configuring my datasource to use "Shared DataSource".
And then: Configuring my =datasource (in ReportDesigner) to use a SQL Authenticated login...

The security model in ReportServices =is a bit tough to navigate in my opinion.

"Dan Carollo (hotmail)" =wrote in message news:eMhumkPyEHA.2764=@.TK2MSFTNGP10.phx.gbl...
I am continually getting the =following error when attempting to render my reports from
http://hostname/ReportServer">http://hostname/ReportServer...=.

An error has occurred during =report processing. (rsProcessingAborted) Get =Online Help
Cannot create a connection to =data source 'DataCollection'. (rsErrorOpeningConnection) Get =Online Help
Login failed for user ='(null)'. Reason: Not associated with a trusted SQL Server connection.

I have tried EVERY possible =security setting for the DataSource (integrated, NO Credentials, SQL authentican, =etc.), and it still doesn't work.
Here is how it's set =up:

1. Reporting Services metadata =location on Server A:
2. Datasource (from which I am =pulling data to render reports) is on Server B:
3. Created a user called ReportUser on BOTH the Reporting Services server as =well as the Datasource. I made sure the user has read access in the =dbs on both servers (including adding to the the RSExecRole).
4. I have designed my reports using =ReportUser as the DataSource connection.

Books online is not helping me =troubleshoot this error.

Any ideas?

Thanks,
Dan

--=_NextPart_000_002C_01C4CB2B.5BA97140--

Friday, March 30, 2012

Long text

Long text

Friday, March 23, 2012

Logshipping problem with naming transaction log backups

Friday, February 24, 2012

Login problems when rendering reports

sql login permissions

Blog Archive

About Me