-OS: Windows 2000
-RS 2005
-SQL Server 2000
The installation completes successfuly in c:\Program Files\Microsoft SQL
Server\MSSQL.1\Reporting Services\
LogFiles in c:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting
Services\LogFiles do not have any errors..
But when i visit http://localhost/ReportServer I get a blank page that looks
like this:
localhost/ReportServer -
----
----
Microsoft SQL Server Reporting Services Version 9.00.1399.00
Any ideas'
I tried re-installing several times...There are two websites. One just shows folders and that website is
ReportServer. If you haven't deployed any reports you won't have any folders
created. The portal (Report Manager) is at the Reports website. Try
http://localhost/Reports
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:2D5F1522-EED5-4A81-B2D2-29BF7F5B873E@.microsoft.com...
> -OS: Windows 2000
> -RS 2005
> -SQL Server 2000
> The installation completes successfuly in c:\Program Files\Microsoft SQL
> Server\MSSQL.1\Reporting Services\
> LogFiles in c:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting
> Services\LogFiles do not have any errors..
> But when i visit http://localhost/ReportServer I get a blank page that
> looks
> like this:
>
> localhost/ReportServer - /
> ----
> ----
> Microsoft SQL Server Reporting Services Version 9.00.1399.00
>
> Any ideas'
> I tried re-installing several times...|||Oh wow thats it!! :)
Thanks...
Sorry for posting twice...I thought the my session expired and the first one
didnt make it. Thanks
"Bruce L-C [MVP]" wrote:
> There are two websites. One just shows folders and that website is
> ReportServer. If you haven't deployed any reports you won't have any folders
> created. The portal (Report Manager) is at the Reports website. Try
> http://localhost/Reports
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:2D5F1522-EED5-4A81-B2D2-29BF7F5B873E@.microsoft.com...
> > -OS: Windows 2000
> > -RS 2005
> > -SQL Server 2000
> > The installation completes successfuly in c:\Program Files\Microsoft SQL
> > Server\MSSQL.1\Reporting Services\
> >
> > LogFiles in c:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting
> > Services\LogFiles do not have any errors..
> > But when i visit http://localhost/ReportServer I get a blank page that
> > looks
> > like this:
> >
> >
> > localhost/ReportServer - /
> > ----
> >
> > ----
> > Microsoft SQL Server Reporting Services Version 9.00.1399.00
> >
> >
> > Any ideas'
> > I tried re-installing several times...
>
>
Showing posts with label files. Show all posts
Showing posts with label files. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Problems in attaching db
Does anyone know why trying to attach a db (.mdf and .ldf files) to a new db, the "Attach database" item in the menu is disabled, please? do I have to reinstall SQL Server?Originally posted by atorlone
Does anyone know why trying to attach a db (.mdf and .ldf files) to a new db, the "Attach database" item in the menu is disabled, please? do I have to reinstall SQL Server?
You could try to attach your db with a t-sql statement.
sp_attach_db @.dbname = 'db name',
@.filename1 = 'path_to/file.mdf' ,
@.filename2 = 'path_to/file.ldf'|||I entered the following in the Query Analyzer and got the result shown:
EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF',
@.filename2 = N'C:\MSSQL7\Data\AARETAIL_Log.LDF'
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\MSSQL7\Data\AARETAIL_Log.LDF' is not a valid database file header. The PageAudit property is incorrect.
I can find nothing about PageAudit property.
What is it and how do I need to modify the command?
Many thanks!|||Originally posted by atorlone
I entered the following in the Query Analyzer and got the result shown:
EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF',
@.filename2 = N'C:\MSSQL7\Data\AARETAIL_Log.LDF'
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\MSSQL7\Data\AARETAIL_Log.LDF' is not a valid database file header. The PageAudit property is incorrect.
I can find nothing about PageAudit property.
What is it and how do I need to modify the command?
Many thanks!
Do you run MsSql 7 or 2000 ?|||Originally posted by atorlone
I entered the following in the Query Analyzer and got the result shown:
EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF',
@.filename2 = N'C:\MSSQL7\Data\AARETAIL_Log.LDF'
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\MSSQL7\Data\AARETAIL_Log.LDF' is not a valid database file header. The PageAudit property is incorrect.
I can find nothing about PageAudit property.
What is it and how do I need to modify the command?
Many thanks!
try attaching the database without the logfile:
EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF'
Does anyone know why trying to attach a db (.mdf and .ldf files) to a new db, the "Attach database" item in the menu is disabled, please? do I have to reinstall SQL Server?
You could try to attach your db with a t-sql statement.
sp_attach_db @.dbname = 'db name',
@.filename1 = 'path_to/file.mdf' ,
@.filename2 = 'path_to/file.ldf'|||I entered the following in the Query Analyzer and got the result shown:
EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF',
@.filename2 = N'C:\MSSQL7\Data\AARETAIL_Log.LDF'
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\MSSQL7\Data\AARETAIL_Log.LDF' is not a valid database file header. The PageAudit property is incorrect.
I can find nothing about PageAudit property.
What is it and how do I need to modify the command?
Many thanks!|||Originally posted by atorlone
I entered the following in the Query Analyzer and got the result shown:
EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF',
@.filename2 = N'C:\MSSQL7\Data\AARETAIL_Log.LDF'
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\MSSQL7\Data\AARETAIL_Log.LDF' is not a valid database file header. The PageAudit property is incorrect.
I can find nothing about PageAudit property.
What is it and how do I need to modify the command?
Many thanks!
Do you run MsSql 7 or 2000 ?|||Originally posted by atorlone
I entered the following in the Query Analyzer and got the result shown:
EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF',
@.filename2 = N'C:\MSSQL7\Data\AARETAIL_Log.LDF'
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\MSSQL7\Data\AARETAIL_Log.LDF' is not a valid database file header. The PageAudit property is incorrect.
I can find nothing about PageAudit property.
What is it and how do I need to modify the command?
Many thanks!
try attaching the database without the logfile:
EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF'
Problems importing TXT files using DTS
Hello,
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JByou should create a transform data task and connect the text file to the destination though the task.
create the destination table in the xform task and map the data types to the new destinations.
ps while you are there change the column mappings from x individual column copies to 1 single thread it will increase your performance.|||Thanks for the response. I'm going to look into this today. If anyone has any other suggestions, I'd appreciate them too.|||Ruprect,
Come to find out that yesterday I was trying the exact thing you mentioned in your first post. I examined it a little more thorough this morning. The only thing different that I wasn't using 1 thread.
I have 94 columns worth of data. If I select the first 30 columns it will import the text and insert it into the table with appropriate column headings to perfection. The first 30 columns are all character fields.
At Column 31 the first integer field appears. It will stop and give me an error message. It says this, "... conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col031' (DBTYPE_STR), destination column 'MARKETVALUE' (DBTYPE_I4). There are all numbers as well, no letters or symbols in these columns.
I'm fairly certain I could convert the column to character and it would work fine. The problem is that I need the column to be an integer (as well as my other numeric columns) to perform various mathematical queries.
Thanks again,
JB|||Yeah, you've got bad data...
I would load the data to a table of a varchars...like you have now
Think of it as a staging table
Then audit the data...
For example
Col030 should be a date
Find out all the rows with bad dates
SELECT * FROM myTable99 WHERE ISDATE(Col030)=0
Will show you rows sql doesn't consider a date...
Same with numerics
ISNUMERIC(col1)=0
You won't be able to load those rows to your final destination table
either fix the input file, or ignore the rows...
in any case, go to who ever gave you the file and say...see here...this is garbage...fix it...
and wait a week while they try to figure out what to do...
Golf, Tennis, Long lunches...whatever you want...|||Brett,
You are correct it was bad data. That should have been the first place I looked.
I went into Query Analyzer and ran the query you posted on the columns of data that I wanted to turn into numeric data. 5 out of 32 potential columns had issues. It seems that some of the fields that I wanted to turn into numeric values had blank values. I guess you can't convert a blank character <NULL?> into a blank number.
Anyways, the problem is solved.
Thanks,
JB
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JByou should create a transform data task and connect the text file to the destination though the task.
create the destination table in the xform task and map the data types to the new destinations.
ps while you are there change the column mappings from x individual column copies to 1 single thread it will increase your performance.|||Thanks for the response. I'm going to look into this today. If anyone has any other suggestions, I'd appreciate them too.|||Ruprect,
Come to find out that yesterday I was trying the exact thing you mentioned in your first post. I examined it a little more thorough this morning. The only thing different that I wasn't using 1 thread.
I have 94 columns worth of data. If I select the first 30 columns it will import the text and insert it into the table with appropriate column headings to perfection. The first 30 columns are all character fields.
At Column 31 the first integer field appears. It will stop and give me an error message. It says this, "... conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col031' (DBTYPE_STR), destination column 'MARKETVALUE' (DBTYPE_I4). There are all numbers as well, no letters or symbols in these columns.
I'm fairly certain I could convert the column to character and it would work fine. The problem is that I need the column to be an integer (as well as my other numeric columns) to perform various mathematical queries.
Thanks again,
JB|||Yeah, you've got bad data...
I would load the data to a table of a varchars...like you have now
Think of it as a staging table
Then audit the data...
For example
Col030 should be a date
Find out all the rows with bad dates
SELECT * FROM myTable99 WHERE ISDATE(Col030)=0
Will show you rows sql doesn't consider a date...
Same with numerics
ISNUMERIC(col1)=0
You won't be able to load those rows to your final destination table
either fix the input file, or ignore the rows...
in any case, go to who ever gave you the file and say...see here...this is garbage...fix it...
and wait a week while they try to figure out what to do...
Golf, Tennis, Long lunches...whatever you want...|||Brett,
You are correct it was bad data. That should have been the first place I looked.
I went into Query Analyzer and ran the query you posted on the columns of data that I wanted to turn into numeric data. 5 out of 32 potential columns had issues. It seems that some of the fields that I wanted to turn into numeric values had blank values. I guess you can't convert a blank character <NULL?> into a blank number.
Anyways, the problem is solved.
Thanks,
JB
Problems importing text files with double-quotes as text qualifier
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.
Thanks
pcp
Import the data as a flat file... don't try to parse the columns apart yet. Save the data as one giant column per row. In your data flow, use a custom script to parse it apart. Here's a modified example of mine:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim unparsedSubString As String
Dim commaPos As Integer = 0
Dim firstQuotePos As Integer = 0
Dim nextQuotePos As Integer = 0
Dim colArray(0) As String
unparsedSubString = Row.RawColumns
commaPos = unparsedSubString.IndexOf(",", 0)
' while the comma position exists within the string, loop
While commaPos <> -1
' get the position of the first two quotes within the string
firstQuotePos = unparsedSubString.IndexOf("""", 0)
nextQuotePos = unparsedSubString.IndexOf("""", firstQuotePos + 1)
' if the comma position is between a set of quotes, grab everything within the quotes
If commaPos > firstQuotePos AndAlso commaPos < nextQuotePos Then
commaPos = unparsedSubString.IndexOf(",", nextQuotePos)
End If
' Resize the array to hold a new element.Element 0 may exist and be unfilled, which
' is required to make sure there are no null references on first byref call to Resize.
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
If commaPos >= 0 Then
colArray(colArray.Length - 1) = unparsedSubString.Substring(0, commaPos).Replace("""", "")
unparsedSubString = unparsedSubString.Substring(commaPos + 1, unparsedSubString.Length - (commaPos + 1))
Else
' This clause covers the last field being qouted with a comma
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
unparsedSubString = String.Empty
End If
' values for next iteration of loop
commaPos = unparsedSubString.IndexOf(",", 0)
End While
' Add whatever contents exist after the last comma
' This clause covers the last field being unquoted or quoted without a comma.
If unparsedSubString.Length > 0 Then
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
End If
If colArray.Length >= 2 AndAlso colArray(1) = "FIL" Then
Me.OutputBuffer.AddRow()
Me.OutputBuffer.Column1 = colArray(0)
Me.OutputBuffer.Column2 = colArray(1)
Me.OutputBuffer.Column3 = colArray(2)
Me.OutputBuffer.Column4 = colArray(3)
Me.OutputBuffer.Column5 = colArray(4)
Me.OutputBuffer.Column6 = colArray(5)
Me.OutputBuffer.Column7 = colArray(6)
Me.OutputBuffer.Column8 = colArray(7)
Me.OutputBuffer.Column9 = colArray(8)
Me.OutputBuffer.Column10 = colArray(9)
Me.OutputBuffer.Column11 = colArray(10)
End If
End Sub
End Class
I'm guessing you can figure the rest of the column mappings and such from here. Good luck.
|||Thanks Robert. This was very helpful. I just tried a variation with the Split function and it worked too. Any thoughts on the shortcomings of using the Split?|||Hi Robert,
I tried to use a transformation Script Component to do it, but all I am getting is
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
.....
I cannot get it to be created with Row as InputBuffer and use the RawColumns properties. InputBuffer class seems to be unrecognized somehow.
I am using SQL 2005 Developer edition with SP1. What am I missing here?
Thanks.
|||Another question :
In 2005 SSIS, Is there an easy way to import a flat file like
"ABC", "EDFG", "123", "10/1/2006" to a SQL database table, with the last two columns as int and date fields ?
Always errored out for data type mismatch. I used SQL 2000 DTS for same data file, no problem at all, but can't get the import work in SSIS.
Thanks.
|||I tried pasting the script, but I get the following errors:
type 'UserComponent' is not defined
type 'InputBuffer' is not defined
'OutputBuffer' is not a member of 'ScriptTask_7a67..........
The dtspipeline doesn't show up as an available reference in the editer.However, it does show up when I launch a regular instance of Visual Studio.I tried copying DTSPipeline.dll from the sql server directory to the .net framework directory, but it doesn't seem to register automatically.
Any ideas?
Thanks|||i seem to be having the same problems. let me know if you have a solution to this.
Problems importing text files with double-quotes as text qualifier
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.
Thanks
pcp
Import the data as a flat file... don't try to parse the columns apart yet. Save the data as one giant column per row. In your data flow, use a custom script to parse it apart. Here's a modified example of mine:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim unparsedSubString As String
Dim commaPos As Integer = 0
Dim firstQuotePos As Integer = 0
Dim nextQuotePos As Integer = 0
Dim colArray(0) As String
unparsedSubString = Row.RawColumns
commaPos = unparsedSubString.IndexOf(",", 0)
' while the comma position exists within the string, loop
While commaPos <> -1
' get the position of the first two quotes within the string
firstQuotePos = unparsedSubString.IndexOf("""", 0)
nextQuotePos = unparsedSubString.IndexOf("""", firstQuotePos + 1)
' if the comma position is between a set of quotes, grab everything within the quotes
If commaPos > firstQuotePos AndAlso commaPos < nextQuotePos Then
commaPos = unparsedSubString.IndexOf(",", nextQuotePos)
End If
' Resize the array to hold a new element.Element 0 may exist and be unfilled, which
' is required to make sure there are no null references on first byref call to Resize.
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
If commaPos >= 0 Then
colArray(colArray.Length - 1) = unparsedSubString.Substring(0, commaPos).Replace("""", "")
unparsedSubString = unparsedSubString.Substring(commaPos + 1, unparsedSubString.Length - (commaPos + 1))
Else
' This clause covers the last field being qouted with a comma
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
unparsedSubString = String.Empty
End If
' values for next iteration of loop
commaPos = unparsedSubString.IndexOf(",", 0)
End While
' Add whatever contents exist after the last comma
' This clause covers the last field being unquoted or quoted without a comma.
If unparsedSubString.Length > 0 Then
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
End If
If colArray.Length >= 2 AndAlso colArray(1) = "FIL" Then
Me.OutputBuffer.AddRow()
Me.OutputBuffer.Column1 = colArray(0)
Me.OutputBuffer.Column2 = colArray(1)
Me.OutputBuffer.Column3 = colArray(2)
Me.OutputBuffer.Column4 = colArray(3)
Me.OutputBuffer.Column5 = colArray(4)
Me.OutputBuffer.Column6 = colArray(5)
Me.OutputBuffer.Column7 = colArray(6)
Me.OutputBuffer.Column8 = colArray(7)
Me.OutputBuffer.Column9 = colArray(8)
Me.OutputBuffer.Column10 = colArray(9)
Me.OutputBuffer.Column11 = colArray(10)
End If
End Sub
End Class
I'm guessing you can figure the rest of the column mappings and such from here. Good luck.
|||Thanks Robert. This was very helpful. I just tried a variation with the Split function and it worked too. Any thoughts on the shortcomings of using the Split?|||Hi Robert,
I tried to use a transformation Script Component to do it, but all I am getting is
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
.....
I cannot get it to be created with Row as InputBuffer and use the RawColumns properties. InputBuffer class seems to be unrecognized somehow.
I am using SQL 2005 Developer edition with SP1. What am I missing here?
Thanks.
|||Another question :
In 2005 SSIS, Is there an easy way to import a flat file like
"ABC", "EDFG", "123", "10/1/2006" to a SQL database table, with the last two columns as int and date fields ?
Always errored out for data type mismatch. I used SQL 2000 DTS for same data file, no problem at all, but can't get the import work in SSIS.
Thanks.
|||I tried pasting the script, but I get the following errors:
type 'UserComponent' is not defined
type 'InputBuffer' is not defined
'OutputBuffer' is not a member of 'ScriptTask_7a67..........
The dtspipeline doesn't show up as an available reference in the editer.However, it does show up when I launch a regular instance of Visual Studio.I tried copying DTSPipeline.dll from the sql server directory to the .net framework directory, but it doesn't seem to register automatically.
Any ideas?
Thanks|||i seem to be having the same problems. let me know if you have a solution to this.
Problems importing text files with double-quotes as text qualifier
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.
Thanks
pcp
Import the data as a flat file... don't try to parse the columns apart yet. Save the data as one giant column per row. In your data flow, use a custom script to parse it apart. Here's a modified example of mine:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim unparsedSubString As String
Dim commaPos As Integer = 0
Dim firstQuotePos As Integer = 0
Dim nextQuotePos As Integer = 0
Dim colArray(0) As String
unparsedSubString = Row.RawColumns
commaPos = unparsedSubString.IndexOf(",", 0)
' while the comma position exists within the string, loop
While commaPos <> -1
' get the position of the first two quotes within the string
firstQuotePos = unparsedSubString.IndexOf("""", 0)
nextQuotePos = unparsedSubString.IndexOf("""", firstQuotePos + 1)
' if the comma position is between a set of quotes, grab everything within the quotes
If commaPos > firstQuotePos AndAlso commaPos < nextQuotePos Then
commaPos = unparsedSubString.IndexOf(",", nextQuotePos)
End If
' Resize the array to hold a new element.Element 0 may exist and be unfilled, which
' is required to make sure there are no null references on first byref call to Resize.
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
If commaPos >= 0 Then
colArray(colArray.Length - 1) = unparsedSubString.Substring(0, commaPos).Replace("""", "")
unparsedSubString = unparsedSubString.Substring(commaPos + 1, unparsedSubString.Length - (commaPos + 1))
Else
' This clause covers the last field being qouted with a comma
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
unparsedSubString = String.Empty
End If
' values for next iteration of loop
commaPos = unparsedSubString.IndexOf(",", 0)
End While
' Add whatever contents exist after the last comma
' This clause covers the last field being unquoted or quoted without a comma.
If unparsedSubString.Length > 0 Then
If colArray(0) <> Nothing Then
Array.Resize(colArray, colArray.Length + 1)
End If
colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")
End If
If colArray.Length >= 2 AndAlso colArray(1) = "FIL" Then
Me.OutputBuffer.AddRow()
Me.OutputBuffer.Column1 = colArray(0)
Me.OutputBuffer.Column2 = colArray(1)
Me.OutputBuffer.Column3 = colArray(2)
Me.OutputBuffer.Column4 = colArray(3)
Me.OutputBuffer.Column5 = colArray(4)
Me.OutputBuffer.Column6 = colArray(5)
Me.OutputBuffer.Column7 = colArray(6)
Me.OutputBuffer.Column8 = colArray(7)
Me.OutputBuffer.Column9 = colArray(8)
Me.OutputBuffer.Column10 = colArray(9)
Me.OutputBuffer.Column11 = colArray(10)
End If
End Sub
End Class
I'm guessing you can figure the rest of the column mappings and such from here. Good luck.
|||Thanks Robert. This was very helpful. I just tried a variation with the Split function and it worked too. Any thoughts on the shortcomings of using the Split?|||Hi Robert,
I tried to use a transformation Script Component to do it, but all I am getting is
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
.....
I cannot get it to be created with Row as InputBuffer and use the RawColumns properties. InputBuffer class seems to be unrecognized somehow.
I am using SQL 2005 Developer edition with SP1. What am I missing here?
Thanks.
|||Another question :
In 2005 SSIS, Is there an easy way to import a flat file like
"ABC", "EDFG", "123", "10/1/2006" to a SQL database table, with the last two columns as int and date fields ?
Always errored out for data type mismatch. I used SQL 2000 DTS for same data file, no problem at all, but can't get the import work in SSIS.
Thanks.
|||I tried pasting the script, but I get the following errors:
type 'UserComponent' is not defined
type 'InputBuffer' is not defined
'OutputBuffer' is not a member of 'ScriptTask_7a67..........
The dtspipeline doesn't show up as an available reference in the editer.However, it does show up when I launch a regular instance of Visual Studio.I tried copying DTSPipeline.dll from the sql server directory to the .net framework directory, but it doesn't seem to register automatically.
Any ideas?
Thanks|||i seem to be having the same problems. let me know if you have a solution to this.
Monday, March 26, 2012
Problems getting to the uninstall tool for SQl Server
Hi everyone,
I was so glad to get SQL Server Standard Edition that I forgot to remove the .Net Framework last. I almost removed all SQl Server files. I tried to remove the remaining files dealing with SQl Server(including add/remove programs) but I can't remove them all. I tried to go to the page to use the unstall tool for SQl Server 2005 but I get is the following:
You are not authorized to view this page
You might not have permission to view this directory or page using the credentials you supplied.
This is the URL that I was sent to: http://go.microsoft.com/fwlink/?LinkId=47598.
Is there any other way to get to this tool?
Thank you
I suspect that was a temporary glitch. Please repost if you still need help here...|||Hi Paul,
Thank you for your reply. I have sucessfully installed SQL 2005 on my machine.
Problems getting to the uninstall tool for SQl Server
Hi everyone,
I was so glad to get SQL Server Standard Edition that I forgot to remove the .Net Framework last. I almost removed all SQl Server files. I tried to remove the remaining files dealing with SQl Server(including add/remove programs) but I can't remove them all. I tried to go to the page to use the unstall tool for SQl Server 2005 but I get is the following:
You are not authorized to view this page
You might not have permission to view this directory or page using the credentials you supplied.
This is the URL that I was sent to: http://go.microsoft.com/fwlink/?LinkId=47598.
Is there any other way to get to this tool?
Thank you
I suspect that was a temporary glitch. Please repost if you still need help here...|||Hi Paul,
Thank you for your reply. I have sucessfully installed SQL 2005 on my machine.
sqlProblems deleting transaction log files older than x days
Hi,
I have a problem with maintainance plans an deleting
transaction log files older than x days. I have made one
maintainance plan for databases and that one works.
But the one made for the transaction log doesn't delete
the old files. They remain and aren't deleted
automatically.
brgds
PeterBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter Junell" <peter.junell@.silverhuset.se> wrote in message
news:9b0601c405c2$d5967c20$a101280a@.phx.gbl...
> Hi,
> I have a problem with maintainance plans an deleting
> transaction log files older than x days. I have made one
> maintainance plan for databases and that one works.
> But the one made for the transaction log doesn't delete
> the old files. They remain and aren't deleted
> automatically.
> brgds
> Peter
I have a problem with maintainance plans an deleting
transaction log files older than x days. I have made one
maintainance plan for databases and that one works.
But the one made for the transaction log doesn't delete
the old files. They remain and aren't deleted
automatically.
brgds
PeterBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter Junell" <peter.junell@.silverhuset.se> wrote in message
news:9b0601c405c2$d5967c20$a101280a@.phx.gbl...
> Hi,
> I have a problem with maintainance plans an deleting
> transaction log files older than x days. I have made one
> maintainance plan for databases and that one works.
> But the one made for the transaction log doesn't delete
> the old files. They remain and aren't deleted
> automatically.
> brgds
> Peter
Problems deleting SQL.LOG file
Hi,
I can not delete this file from C/Windows/Temp directory, all other files form that foder I can delete. The error message I get is that something else is using this file. The thing is I cant find what is using it, even though I uninstaled SQL Server 2005 completely. I have tried lots of things, nothing helps. At the end I entered windows in SAFE MODE, went to Computer Management and disabled almost every service I can, that might be using it, and still cant get rid of this SQL.LOG which is 26GB now. The only thing I could do is to compress the file and now it is 6.5GB.
Txs for the help..
This file is a result of ODBC tracing. Here's the article about how to delete it.
http://support.microsoft.com/kb/268591
Thanks,
Sam Lester (MSFT)
Friday, March 23, 2012
Problems creating format file.
sql2k sp4
Howdy all. I havent used format files inside BCP in several years and am having trouble creating one now.
declare @.exec varchar(1026)
set @.exec = 'bcp faa_ivr.dbo.primary_informant format -SboxName\instanceName -c -T -f\\destination\FAAIVR\primary_informant_format.txt '
exec master..xp_cmdshell @.exec
output
----------------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL
(5 row(s) affected)
I've tried brackets ([])around the box/ instance name. I've tried using the FQDN. I tried the SA account instead of WINNT authentication. All ideas are appreciated.it might be that the service account doesn't have permission to the location that the file is at. copy it to the server|||The file doesnt exist yet, Im trying to create it. I've also tried to create it locally, and that didn't work.|||I like to create my own
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_GenFormatCards]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_GenFormatCards]
GO
CREATE PROC isp_GenFormatCards
AS
DECLARE FormatCard CURSOR FOR
SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM (
/*
SELECT '--' + TABLE_NAME AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
*/
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ char(9)+'"\r\n"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping
DECLARE @.Card varchar(200), @.TABLE_NAME sysname, @.TABLE_SCHEMA sysname, @.cmd varchar(200), @.x char(2), @.Command_String varchar(8000)
, @.TABLE_NAME_OLD sysname, @.TABLE_SCHEMA_OLD sysname
SELECT @.x = '> ', @.TABLE_NAME_OLD = '', @.TABLE_SCHEMA_OLD = ''
OPEN FormatCard
FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.x = '>>'
IF @.TABLE_SCHEMA+@.TABLE_NAME <> @.TABLE_SCHEMA_OLD+@.TABLE_NAME_OLD
BEGIN
SELECT @.TABLE_SCHEMA_OLD = @.TABLE_SCHEMA
, @.TABLE_NAME_OLD = @.TABLE_NAME
, @.x = '> '
END
SET @.cmd = 'echo ' + @.Card + ' '+ @.x +' d:\Data\Tax\Format\'+@.TABLE_SCHEMA+'_'+@.TABLE_NAME +'.fmt'
SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''', NO_OUTPUT'
PRINT @.Command_String
Exec(@.Command_String)
FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA
END
CLOSE FormatCard
DEALLOCATE FormatCard
GO
--master..xp_cmdshell 'dir d:\Data\Tax\Format\*.*'|||Your a crazy mo fo.
Howdy all. I havent used format files inside BCP in several years and am having trouble creating one now.
declare @.exec varchar(1026)
set @.exec = 'bcp faa_ivr.dbo.primary_informant format -SboxName\instanceName -c -T -f\\destination\FAAIVR\primary_informant_format.txt '
exec master..xp_cmdshell @.exec
output
----------------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL
(5 row(s) affected)
I've tried brackets ([])around the box/ instance name. I've tried using the FQDN. I tried the SA account instead of WINNT authentication. All ideas are appreciated.it might be that the service account doesn't have permission to the location that the file is at. copy it to the server|||The file doesnt exist yet, Im trying to create it. I've also tried to create it locally, and that didn't work.|||I like to create my own
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_GenFormatCards]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_GenFormatCards]
GO
CREATE PROC isp_GenFormatCards
AS
DECLARE FormatCard CURSOR FOR
SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM (
/*
SELECT '--' + TABLE_NAME AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
*/
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ char(9)+'"\r\n"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping
DECLARE @.Card varchar(200), @.TABLE_NAME sysname, @.TABLE_SCHEMA sysname, @.cmd varchar(200), @.x char(2), @.Command_String varchar(8000)
, @.TABLE_NAME_OLD sysname, @.TABLE_SCHEMA_OLD sysname
SELECT @.x = '> ', @.TABLE_NAME_OLD = '', @.TABLE_SCHEMA_OLD = ''
OPEN FormatCard
FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.x = '>>'
IF @.TABLE_SCHEMA+@.TABLE_NAME <> @.TABLE_SCHEMA_OLD+@.TABLE_NAME_OLD
BEGIN
SELECT @.TABLE_SCHEMA_OLD = @.TABLE_SCHEMA
, @.TABLE_NAME_OLD = @.TABLE_NAME
, @.x = '> '
END
SET @.cmd = 'echo ' + @.Card + ' '+ @.x +' d:\Data\Tax\Format\'+@.TABLE_SCHEMA+'_'+@.TABLE_NAME +'.fmt'
SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''', NO_OUTPUT'
PRINT @.Command_String
Exec(@.Command_String)
FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA
END
CLOSE FormatCard
DEALLOCATE FormatCard
GO
--master..xp_cmdshell 'dir d:\Data\Tax\Format\*.*'|||Your a crazy mo fo.
Wednesday, March 21, 2012
Problems connecting to SQL 2005 DB on server
Hi - I have Web Developer, SQL Express 2005 running locally on my pc, and on my remote server.
Everything is fine locally, but when I copy my files over to my server (have setup a virtual dir, and ensured it is running .net2) I get the following message when trying to connect:
An attempt to attach an auto-named database for file C:\websites\sickness\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I have tried attaching and detaching the database from within SSME on the server - my connection string is:
<connectionStrings>
<add name="SicknessConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
I've tried it with and without the User Instance = True set
Has anyone got an actual fix for this?
Thanks, Mark
Hi - I'm sorry for bumping this, but I'm sure I can't be the only person with this problem - is there a more appropriate place I should look for an answer?Kind regards,
Mark|||
mtait wrote:
is there a more appropriate place I should look for an answer?
Yes, I've now moved this question from the Getting Started forum to this SQL Server forum.|||I am pretty sure the problem is that you are missing the Database attribute for your connection string. I always usehttp://www.connectionstrings.com/ as a connection string reference. Drill down under SQL Server 2005 and look at the 2 SQL Server Express connection string examples. I think this will help you.|||
Hi - thank you - I was using connectionstrings.com - but each one fails.
I've now reinstalled SQL Express, and allowed Mixed Mode.
From SSME I've set the sa password to 'Bob99' and use this connection string:
<connectionStrings>
<add name="SicknessConnectionString" connectionString="Server=.\SQLExpress;Database=sickdb;User ID=sa;Password=Bob99;Trusted_Connection=False"
providerName="System.Data.SqlClient" />
</connectionStrings>
...and now the error is:
System.Data.SqlClient.SqlException: Cannot open database "sickdb" requested by the login. The login failed.
Login failed for user 'sa'.
I've checked, and rechecked the sa password - again, thanks for any help on this.
Mark
|||
Try detaching the database. You've permanently attached it, or you already have a database named 'database' on the other server. I notice that now you are calling it 'sickdb'. You could try renaming the database.mdf to sickdb.mdf as well. Then go back to your original connection string (of course you will need to change the reference from database.mdf to sickdb.mdf).
Just an FYI - your last error message is saying the userid/password is fine, but that user has no access to the 'sickdb' database. You can of course at this point go forwards and assign sa rights to the database, or you can go back and fix the original problem and not worry about permissions at all.
|||Hi - thanks again for the help.
I think I've messed things up somewhere - I'm trying to change too many things to make this work, and I still end up with the same error messages.
If I uninstall SQL Express 2005, and do a clean install, with mixed mode authentication, is there a step by step guide anywhere which will advise how to:
a) setup a new user from within SSME
b) whether to attach the database file or not
c) which section of SSME to change to allow the user to connect to the local (database is on the same remote server as IIS) database - whether in Security/Logins or Database/Properties
d) advise a connection string which would work given the above steps#
Sorry - I know people are trying to help me, but I've just got myself completely mixed up here.
Thanks, Mark
sqlTuesday, March 20, 2012
Problems activating a second Report server using rsactivate (Web f
1. I cannot get rsactivate to activate a second RS node on a web farm.
rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config"
Failure starting the web service:
The local NT Service is not activated
2. rskeymgmt on the Original RS node 1 does not work either.
rskeymgmt -e -fc:\rsdbkey.txt -pxxxxx
Are you sure you want to extract the key from the report server (Y/N)? y
The local NT Service is disabled 0x80090005
Environment -
The two RS Servers are both Windows 2003 Servers.
SQL Server is on a third computer: SQL 2000 Server Enterprise Edition.
RS Node1: Works fine.
RS Node 2: Activation fails.
ReportServer service login account for both nodes: "NT
Authority\NetworkService"
SQL Server access - using a "sql admin" account.
IIS (W3Svc) on both RS nodes - "LocalSystemAccount".
Web Servers on both RS nodes are working fine and the ReportServer service
starts as well.To activate a web farm, you will need to run rsactivate -m <name of node 2>
from node 1.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"VChandy" <VChandy@.discussions.microsoft.com> wrote in message
news:539D88F5-35FE-44E7-B2A5-79AC74E363D9@.microsoft.com...
> 1. I cannot get rsactivate to activate a second RS node on a web farm.
> rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config"
> Failure starting the web service:
> The local NT Service is not activated
> 2. rskeymgmt on the Original RS node 1 does not work either.
> rskeymgmt -e -fc:\rsdbkey.txt -pxxxxx
> Are you sure you want to extract the key from the report server (Y/N)? y
> The local NT Service is disabled 0x80090005
> Environment -
> The two RS Servers are both Windows 2003 Servers.
> SQL Server is on a third computer: SQL 2000 Server Enterprise Edition.
> RS Node1: Works fine.
> RS Node 2: Activation fails.
> ReportServer service login account for both nodes: "NT
> Authority\NetworkService"
> SQL Server access - using a "sql admin" account.
> IIS (W3Svc) on both RS nodes - "LocalSystemAccount".
> Web Servers on both RS nodes are working fine and the ReportServer service
> starts as well.
>|||Figured it out from an earlier post on 8/17 from Brian Hartman [MSFT]
Did the following -
1. rskeymgmt -d on the orignal RS Node 1.
2. Stopped W3Svs and RS Service on Node 2. (** important)
3. From Node1 ran: rsactivate -mNode2 -c "C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"
4. Re-started W3Svc and RS Service on Node 2.
5. Deleted and re-deployed all my RDLs to RS Node 1 as the security
information for the Data sources & other stuff got clobbered by rskeymgmt -d
6. Both RS nodes 1 & 2 can access all reports.
7. SSL works fine as well - next need to upgrade both nodes to RS Service
pack 1 and work on an NLB cluster solution ...
"VChandy" wrote:
> 1. I cannot get rsactivate to activate a second RS node on a web farm.
> rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config"
> Failure starting the web service:
> The local NT Service is not activated
> 2. rskeymgmt on the Original RS node 1 does not work either.
> rskeymgmt -e -fc:\rsdbkey.txt -pxxxxx
> Are you sure you want to extract the key from the report server (Y/N)? y
> The local NT Service is disabled 0x80090005
> Environment -
> The two RS Servers are both Windows 2003 Servers.
> SQL Server is on a third computer: SQL 2000 Server Enterprise Edition.
> RS Node1: Works fine.
> RS Node 2: Activation fails.
> ReportServer service login account for both nodes: "NT
> Authority\NetworkService"
> SQL Server access - using a "sql admin" account.
> IIS (W3Svc) on both RS nodes - "LocalSystemAccount".
> Web Servers on both RS nodes are working fine and the ReportServer service
> starts as well.
>
rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config"
Failure starting the web service:
The local NT Service is not activated
2. rskeymgmt on the Original RS node 1 does not work either.
rskeymgmt -e -fc:\rsdbkey.txt -pxxxxx
Are you sure you want to extract the key from the report server (Y/N)? y
The local NT Service is disabled 0x80090005
Environment -
The two RS Servers are both Windows 2003 Servers.
SQL Server is on a third computer: SQL 2000 Server Enterprise Edition.
RS Node1: Works fine.
RS Node 2: Activation fails.
ReportServer service login account for both nodes: "NT
Authority\NetworkService"
SQL Server access - using a "sql admin" account.
IIS (W3Svc) on both RS nodes - "LocalSystemAccount".
Web Servers on both RS nodes are working fine and the ReportServer service
starts as well.To activate a web farm, you will need to run rsactivate -m <name of node 2>
from node 1.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"VChandy" <VChandy@.discussions.microsoft.com> wrote in message
news:539D88F5-35FE-44E7-B2A5-79AC74E363D9@.microsoft.com...
> 1. I cannot get rsactivate to activate a second RS node on a web farm.
> rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config"
> Failure starting the web service:
> The local NT Service is not activated
> 2. rskeymgmt on the Original RS node 1 does not work either.
> rskeymgmt -e -fc:\rsdbkey.txt -pxxxxx
> Are you sure you want to extract the key from the report server (Y/N)? y
> The local NT Service is disabled 0x80090005
> Environment -
> The two RS Servers are both Windows 2003 Servers.
> SQL Server is on a third computer: SQL 2000 Server Enterprise Edition.
> RS Node1: Works fine.
> RS Node 2: Activation fails.
> ReportServer service login account for both nodes: "NT
> Authority\NetworkService"
> SQL Server access - using a "sql admin" account.
> IIS (W3Svc) on both RS nodes - "LocalSystemAccount".
> Web Servers on both RS nodes are working fine and the ReportServer service
> starts as well.
>|||Figured it out from an earlier post on 8/17 from Brian Hartman [MSFT]
Did the following -
1. rskeymgmt -d on the orignal RS Node 1.
2. Stopped W3Svs and RS Service on Node 2. (** important)
3. From Node1 ran: rsactivate -mNode2 -c "C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"
4. Re-started W3Svc and RS Service on Node 2.
5. Deleted and re-deployed all my RDLs to RS Node 1 as the security
information for the Data sources & other stuff got clobbered by rskeymgmt -d
6. Both RS nodes 1 & 2 can access all reports.
7. SSL works fine as well - next need to upgrade both nodes to RS Service
pack 1 and work on an NLB cluster solution ...
"VChandy" wrote:
> 1. I cannot get rsactivate to activate a second RS node on a web farm.
> rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config"
> Failure starting the web service:
> The local NT Service is not activated
> 2. rskeymgmt on the Original RS node 1 does not work either.
> rskeymgmt -e -fc:\rsdbkey.txt -pxxxxx
> Are you sure you want to extract the key from the report server (Y/N)? y
> The local NT Service is disabled 0x80090005
> Environment -
> The two RS Servers are both Windows 2003 Servers.
> SQL Server is on a third computer: SQL 2000 Server Enterprise Edition.
> RS Node1: Works fine.
> RS Node 2: Activation fails.
> ReportServer service login account for both nodes: "NT
> Authority\NetworkService"
> SQL Server access - using a "sql admin" account.
> IIS (W3Svc) on both RS nodes - "LocalSystemAccount".
> Web Servers on both RS nodes are working fine and the ReportServer service
> starts as well.
>
Friday, March 9, 2012
Problem writing XML files.
Hi guys,
I have a stored procedure that I will subsequently post below this
message. What the stored procedure does is, it reads some specific
tables in a database and created XML off it.
The problem comes in when the data is bigger than a few hundred/
thousand characters. The data is truncated and the XML file is not
fully made.
Now i have been reading some posts by some people and tried using TEXT/
NTEXT/ IMAGE type to write the files but they give me errors which,
again, I am adding to this email.
STORED PROC:
****************************
CREATE PROCEDURE usrp_sp_makexmlfile
@.str varchar(50),
@.templatefile varchar(255),
@.ReturnValue as image OUT
AS
SET NOCOUNT ON
DECLARE @.strVar as varchar(8000)
/*DECLARE @.ReturnValue as varchar(255)*/
Set @.strVar = 'Select * from ' + @.str + ' FOR XML AUTO'
Set @.templatefile = 'c:\template.tpl'
EXEC (@.strVar)
SELECT @.ReturnValue
GO
THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
************************************************** *******
An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll
Additional information: Parameter 2: '@.ReturnValue' of type: Byte[],
the property Size has an invalid size: 0
Also, if i try to add an integer value to the image/text/ntext like
8000 or something less than that, it tells me that the result has to be
discarded because the current process has had some error.
Can someone give me a suggestion on how to resolve this or an example
that would illustrate the solution? Thank you all in advance.
Pi.Hi
You may want to check out http://sqlxml.org/faqs.aspx?faq=29 but there may
be issues with line breaks for sp_makewebtask or look at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp
John
"Pi" <3.something@.gmail.com> wrote in message
news:1126041751.328093.220490@.f14g2000cwb.googlegr oups.com...
> Hi guys,
> I have a stored procedure that I will subsequently post below this
> message. What the stored procedure does is, it reads some specific
> tables in a database and created XML off it.
> The problem comes in when the data is bigger than a few hundred/
> thousand characters. The data is truncated and the XML file is not
> fully made.
> Now i have been reading some posts by some people and tried using TEXT/
> NTEXT/ IMAGE type to write the files but they give me errors which,
> again, I am adding to this email.
> STORED PROC:
> ****************************
> CREATE PROCEDURE usrp_sp_makexmlfile
> @.str varchar(50),
> @.templatefile varchar(255),
> @.ReturnValue as image OUT
> AS
> SET NOCOUNT ON
> DECLARE @.strVar as varchar(8000)
> /*DECLARE @.ReturnValue as varchar(255)*/
> Set @.strVar = 'Select * from ' + @.str + ' FOR XML AUTO'
> Set @.templatefile = 'c:\template.tpl'
> EXEC (@.strVar)
> SELECT @.ReturnValue
> GO
>
>
> THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
> ************************************************** *******
> An unhandled exception of type 'System.InvalidOperationException'
> occurred in system.data.dll
> Additional information: Parameter 2: '@.ReturnValue' of type: Byte[],
> the property Size has an invalid size: 0
>
> Also, if i try to add an integer value to the image/text/ntext like
> 8000 or something less than that, it tells me that the result has to be
> discarded because the current process has had some error.
>
> Can someone give me a suggestion on how to resolve this or an example
> that would illustrate the solution? Thank you all in advance.
> Pi.|||According to the Sybase ASE manual at :
<http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/4429;pt=4429#X
<snip>
Restrictions on text and image columns
text and image columns cannot be used:
*As parameters to stored procedures or as values passed to
these parameters
*As local variables
</snip|||Hi
With Microsoft SQL Server text is a valid datatype for parameter.
John
"ZeldorBlat" <zeldorblat@.gmail.com> wrote in message
news:1126057842.315787.150600@.g14g2000cwa.googlegr oups.com...
> According to the Sybase ASE manual at :
> <http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/4429;pt=4429#X>
> <snip>
> Restrictions on text and image columns
> text and image columns cannot be used:
> *As parameters to stored procedures or as values passed to
> these parameters
> *As local variables
> </snip
Wednesday, March 7, 2012
Problem with WMI tasks : waiting for files in parallel in a SSIS package
Hi,
I have a problem with the task "event watcher".
I've made a query like the one in msdn (SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE Targetinstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"e:\\\\temp\""). I have 20 similar tasks for watching in different folders, but when there are too much tasks in parallel, it doesn't work anymore. I change the numbers of executables to 128 (in the general properties of the package (to test)) but it doesn't seems to work.
I don't understand why it works when there are only 1 or 2 (6 seems to be the maximum) tasks and not if there are more than 6.
Could you help me with this issue?
Configuration : Windows Server 2003, SQL Server 2005, SSIS, Sql Server Agent
Thanks a lot.
Julien.
I forgot to tell you that the package work when I execute it on debug, on the local machine but not on the server with a sql job.
Thanks.
|||I don't know anything about WMI, but it looks like you're waiting for files to be created. Have you looked at the File Watcher Task that's available for download from SQLIS.com?
http://www.sqlis.com/23.aspx
Hopefully someone will have an idea about the WMI problem, but this might get you around the problem in the interim...
|||Hi,
I don't really want to add tasks that are not supported by Microsoft, that's why I tried this WMI stuff.
Any idea?
Thanks.
Julien.
Subscribe to:
Posts (Atom)