I'm having some problems when trying to import an external table into my SQL Server 7.0 DB. When date fields are being created the datatype is being set to smalldatetime which is causing errors because some of the data in the source table is using dates proir to 1900.
Is there a way I can set the datatype to be set as datetime instead of smalldatetime?
I'm importing a multi tab spreadsheet using Import wizard, which I understand to use the same internals as SSIS. The total number of columns in the spread sheet will be over 500. The import wizard defaults everything to varchar 255. I understand there is an XML file I can manipulate to change this and they are located
C:Program FilesMicrosoft SQL Server100DTSMappingFiles
Assuming one of these will control Excel defaults, which one is it? None of the names lend themselves to the Excel as a source. SqlClientToMSSql10?
How do i know whats the default values on which table and which column and whats the default value
It should be like this
Table columnconstraint_name value
empemp_idDF_emp_emp_no 1
empjoin_dateDF_emp_cur_date getdate()
How can i get these results on all the tables in my DB.
Thanks.
I have a 64-bit box with SQL 2000 and 2005 installed on it. At the command prompt, typing BCP -v gives me the 8.0 version, not 9.0. I really want the 9.0 version as the default. Obviously typing
"C:Program FilesMicrosoft SQL Server90 oolsinncp" gives me the 9.0 version.
My PATH variable looks like this:
Path=C:WINDOWSsystem32;C:WINDOWS;C:WINDOWSSystem32Wbem;C:Program FilesAT
I TechnologiesATI Control Panel;C:Program FilesIntelDMIX;C:Program Files (
x86)Microsoft SQL Server80ToolsBINN;C:Program Files (x86)Microsoft SQL Ser
ver80ToolsBinn;C:Program FilesMicrosoft SQL Server90DTSBinn;C:Program
FilesMicrosoft SQL Server90Toolsinn;C:Program Files (x86)Microsoft SQL
Server90Toolsinn;C:Program Files (x86)Microsoft SQL Server90DTSBinn;C
:Program Files (x86)Microsoft SQL Server90ToolsBinnVSShellCommon7IDE;C:
Program Files (x86)Microsoft Visual Studio 8Common7IDEPrivateAssemblies
Do I dare change the order so it looks in the 9.0 directories first? Or is this by design?
thx
Hi!
I´m experiencing problems with datepart(weekday,...) function. In particular with week 54 of 2000... (by the way, don´t make a bet about that, because Jan 1st. was saturday (last day of week) and year 2000 was a leap year!!!, so Dec 31 became the one and only case of 54 weeks... in our calendar).
Is there a way I can fix sunday as first day of week, instead using SET DATEFIRST in every procedure? (because "somebody" puts MONDAY as a default in spanish)
Thanx in advance,
César.
I have run the following script to create a default on a table:
alter table TableXYY
add default 0 for ColumnX
Now, how do I delete this default (without re-creating the table)??
Thanks
I have a stored procedure that has @BeginDate and @EndDate as parameters. I created a report with a default for both. They run just fine. After I deployed, I created Linked Reports and wanted to override the defaults. In the defaults, I tried to put in GetDate() for @BeginDate and GetDate()+10 for the @EndDate so this can be passed in the where statement of the stored procedure. I get 'Syntax error converting datetime from character string.'
What I assume is that if I override the default, the stored procedure will process what I put in the @BeginDate and @EndDate parameters.
The where statement looks like:
and (m.BeginDate >= @BeginDate) and (m.EndDate <= @EndDate)
I'm using Reporting Services 2005 and SQL Server 2005.
Thanks, Iris
Does anyone have a way of finding all parameters with default values, for any stored procedure in a database?
aTdHvAaNnKcSe!
I have a column which uses a DEFAULT as GETDATE in one of my tables. When I execute a DTS package to insert data into it, the column values are all the same, but if I use SSIS, the dates differ slightly (by a few ticks after several rows, but not a consistent amount of rows).
Is there an explanation for this difference, and how can I correct this problem?
I have a table, tbl1:create table tbl1 ([field1] [char] (16) NULL DEFAULT (' '),[field2] [char] (6) NULL DEFAULT (' ')).When I do a select * into tbl2 from tbl1, tbl2 does not have defaults.Is there any settings I have to keep on when I do a select * into?Any help will be appreciated.
View 2 Replies View Related
Thanks in advance for fielding my question!
I have parameters (type=datetime) on a report whose values are populated from a query. This query just pulls a list of all the dates (all Sunday dates) in a table. I have the parameter set up to have the date as the value and then a string representation of the value as the label (ie 2007-11-25) to make it easier for the user.
I want to set up a default for this parameter that essentially takes today's date and calculates the previous Sunday's date so the parameter drop down defaults to last Sunday's date.
My expression works fine just to display it on the report in a text box. It calculates and displays last Sunday's date perfectly. BUT, it doesn't work when I use it in the expression for the parameter default - Im' assuming because the latter cares about data type?
here's my statement:
=Iif(Now.DayofWeek=1,dateadd("d",-1,Now),(Iif(Now.DayofWeek=2,dateadd("d",-2,Now),(Iif(Now.DayofWeek=3,dateadd("d",-3,Now),(Iif(Now.DayofWeek=4,dateadd("d",-4,Now),(Iif(Now.DayofWeek=5,dateadd("d",-5,Now),(Iif(Now.DayofWeek=6,dateadd("d",-6,Now),(Iif(Now.DayofWeek=7,Now,0)))))))))))))
In short all those if statements calculate the day of last Sunday's date based on Now()...so if it's Monday, subtract 1, if it's Tuesday, subtract 2...etc.... BUT this returns the time also - argh! Formats!
How can I format this so it will equate to my oracle date populating in my parameter list? Do I need to match the output of the above statement to the LABEL (ie, string) or the actual VALUE (ie, date). I've tried both. I've hacked at this thing for an hour and I'm sure it's so obvious!
Thanks!
J
Hi guys,
I have a problem when configuring SharePoint and RS on two boxes. (If install everything in one box is fine)
I have SQL SP2 + RS in one server and SharePoint on the other server. I've followed every step in the SQL 2005 Online Book to configure the RS and SharePoint including:
How to: Install the Windows SharePoint Services Object Model on a Report Server Computer. (Done)
How to: Configure Service Accounts (Reporting Services Configuration). (Done)
However, I got an error when click "Set server defaults" in SharePoint Central Admin:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.
In ReadMe of SQL SP2, see below: http://download.microsoft.com/download/f/d/c/fdcb3a53-0cb1-4e67-a1b6-45b89b3c59cf/readme_rsaddin.htm#_rsshare_known_problems_7
---
6.4 Service account requirements for Reporting Services
Restrictions on using built-in accounts apply to some deployment topologies of Reporting Services that include a report server running in SharePoint integrated mode. The following combination of factors will result in service account requirements:
The report server is integrated with a SharePoint farm comprising more than one computer.
The report server and SharePoint Central Administration Web site run on separate computers.
In this scenario, if either the Report Server Web service or Windows service runs under a built-in account such as NetworkService, the Grant database access option in SharePoint Central Administration will not work correctly. Consequently, accessing any Reporting Services feature through a SharePoint site will result in the following error:
"An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. --> Client found response content type of 'text/html; charset=utf-8', but expected 'text.xml'."
To avoid this error, choose one of the following approaches:
On the computer that hosts the report server, continue to run the Report Server Web service as NetworkService and add the built-in account, such as NT_AUTHORITYNetworkService to the WSS_WPG Windows group.
Configure the service accounts to run under a domain user account as follows:
Start the Reporting Services Configuration tool and connect to the report server.
Click Windows Service Identity, click Windows Account, type a domain user account, and click Apply.
Click Web Service Identity, for Report Server, click New, type an application pool name, click Windows Account, type a domain user account, and click Apply.
Reset IIS.
Restart the Windows service.
---
I did try it out but still get the error when clicking "Set server defaults" link in SharePoint Central Admin.
Is there something wrong with the two boxes installation???
Hope the MS guys can help.
Paul
We've got an app that is in final testing so we can't go around forcing SqlDbType.VarChar. Is there any way to make SqlParameter default to varchar if you don't explicitly set a type for a string value? It's bad for our db index usage because they're all varchar and nvarchar forces a convert_implicit messing up performance.
View 1 Replies View RelatedHi,
I Just wanted know that where is the Default and Constraint values are stored in SQL server or DB2 sytem Tables?
I am doing a little research on Google about this topic and I ran intothis thread:http://groups.google.com/group/micr...dc13d4ee6758966I read SQL Server MVP Louis Davidson's post saying:"Actually they are more likely to drop the concept of bound defaults.Constraints are the standard way to do this, and really should be the wayyou create defaults anyhow."Even I read in the Microsoft SQL Server Introduction (SQL 7 bookpage 244, however we're using SQL Server 2000):"Constraints define rules regarding the values allowed in columns and arethe standard mechanism for enforcing integrity, preferred over triggers,rules, and defaults. They are also used by the query optimizer to improveperformance in selectivity estimation, cost calculations, and queryrewriting."Why constraint defaults are better? The second sentence about constraintshaving better optimization, I am guessing they don't mean this aboutDefault Constraints, rather the other type of constraints?Because I don't see how a Default Constraint have anything to do withperformance? Isn't default only to do with new records being created?At work we are setting all tables' columns to have constraint defaultsof 0 or ' ' (space character) in order not to have any column with theNULL value. Therefore we have dozens of files containing statements like:alter table TABLE1 add constraint TABLE1_ID_DFDEFAULT(' ') FOR IDgoalter table TABLE1 add constraint TABLE1_QUANTITY_DFDEFAULT(0) FOR QUANTITYgoFirst I was thinking to create 3 SQL Defaults called:DefaultZeroDefaultSpaceDefaultDateand then bind these defaults to all the columns of all tables excludingprimary keys. After creating the tables I would enumerate throughall the columns and bind one of these three Defaults based on theirdatatype:number = DefaultZerotext type = DefaultSpacedate type = DefaultDateAnd then unbind the ones that we specifically need to specify otherdefault values.So my question is should I do this by using sp_binddefault or stickwith using Default Constraints inside a table/columns loop code?Thank you
View 10 Replies View RelatedTo make certain SSIS features work there are many properties that need to be set over and over for most containers in the package. For example with CheckpointRestart, you need to set (in most cases) all of the tasks' FailPackageOnFailure to True. If you miss one, the package may not restart properly and you might never know. There are other situations where as a development team we want certain properties to be usually set the same but differently from the SSIS default.
Is there a way to control the defaults that the SSIS IDE uses?
I remember back in classic VB that if you wanted to change the defaults of a bare form you could create a template form adjusted the way you like and put it in a templates folder. Then new forms added to a project would be based on the template form.
I created the following Default
CREATE DEFAULT HelloWorld AS 'HelloWorld'
Now, tell me how can I get the information for this default? I can find it in sys.objects and it says its a DEFAULT CONSTRAINT, but sys.default_constraints does not show anything. Anyone?
Thanks
Hi Everyone,
Can anyone help me with this little problem that im stuck with.
As part of the initial snapshot using merge replication, i am creating the initial schema on the subscriber database however it seems as though it does not like UDDTs or defaults and hence does not create them on the subscribers.
Its looking likely that I may have to create a script which executes on the subscriber which creates the initial schema then use merge replication to transfer data.
Does Merge Replication support UDTs/Defaults? If so how does it work?
Ive search many places though have come up with nothing.
Please help. = (
Does it allow you it create rules ? a right click does not present an option, as if you cannot. Going to try T-SQL to see if that works.
John
I want to set defaults for my multi-valued report parameter MONTH so that when the report starts, it automatically selects all the months prior to the current month (effectively creates a YTD report). However, using RS2005, I can't seem to figure out how to do this. I can create an IIF expression in 12 different value entries in the report parameters that returns the month based on the system date, but the first time I pass blanks, null or anything except a valid parameter, it clears the entire parameter list when the report displays.
Does anyone have any suggestions for auto-populating multiple values in a parameter at runtime where one or more of the parameter values may be empty? Checking "Allow Null" or "Allow Blank" doesn't fix this problem.
I tried to pass all the values in a single value entry on the report parameters page, but can't find the syntax that will allow this. I'm not sure if it will let you do that anyway...
Trent
It seems that my report runs immediately now that I've set defaults on all parameters. Is there a way to allow defaults on all params but still require that the user click "view report" before it runs?
View 3 Replies View RelatedHi,
Ever since working with RS I had noticed that deploying reports felt inconsistent and didn't always seem to work, requiring sometimes for me to delete and re-deploy the reports before seeing changes applied. This has the knock on effect of generating new report ID's (GUID's) for the reports and any data in the ExecutionLog table for your reports becomes useless inhibiting effective usage reporting.
I never quite put my finger on exactly what the issue was until recently discovered that "by design" report paramerter defaults are not updated when deploying reports from BIDS. This is because MS envisaged that DBA's or Report Adminstrators would be responsible for changing parameter defaults and prompts once the reports are deployed.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=100960&SiteID=1
I disagree and having discussed this with the product team here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2181673&SiteID=1 it seems a sensible option would be to add a deployment option to the reporting services project definition similar to the OverwriteDataSources option.
I have posted a product feedback report on the Connect site https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299372
Please vote for this to be added to Katmai (RS 2008).
I have various ways of getting the parameters of a stored procedure:
I have a procedure that has all defaults 4 are null and 2 are 0.
The following shows most of what I need but no defaults
SELECT PARAMETER_NAME ,
ORDINAL_POSITION ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
CHARACTER_OCTET_LENGTH ,
NUMERIC_PRECISION ,
[Code] ...
This one has two values:
PARAMETER_HASDEFAULT (always 0) and PARAMETER_DEFAULT (always 0)
sp_procedure_params_rowset proc procedure
Is there something else that would tell me if there is a default on a parameter and what the default is if there is one.
Hi,When I copy tables in a database from one server to another usingenterprise manager, everything copies ok, except for field defaults.Has anyone seen this, and what is the solution?--http://www.dbForumz.com/ This article was posted by author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbForumz.com/General-Dis...pict174830.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=586973
View 5 Replies View RelatedHi there
View 5 Replies View RelatedHey All,
I installed Reporting Services Add-in with no problems on a Virtual server that already hosts MOSS 2007 and configured everything that was stated in documentation. The Report server Url works fine..like http://myserver1:802/ReportServer and I can see the contents when I type this url.
However, when I go to Central Administration and click on "Set Server Defaults"...I am getting a "401.1 Unauthorized..." error.I setup the Manage Integration to TrustedAccount and the Grant DatabaseAccess to the account that is the Server.Farm and local administrator.
In the Reporting Services Configuration, I used SSRSWebAccount for WebService and SSRSWindowsAccount for WindowsService and the RSAppPool which is the ApplicationPool for ReportingServices Web Site uses the SSRSWindowsAccount only.
Anybody..please help.
Thanks,
Vedala
I'm not quite sure where to post this question, so I'll try here. We've been struggling getting SQL2005 reporting services integrated with Share Point Services 3.0 for several days now.
We've installed SQL Reporting Services with SP2 and run the SharePoint Reporting Services add-in. We've been able to go through the SQL Reporting Services Configuration and now were at the last step in the SharePoint Application Management Reporting Services configuration.
The first two steps of Manage Integration Settings and Grant Database Access was sucessful. The final step of "Set Server Defaults" gives us a 404 error message. There is nothing in the Event Log to suggest that there was a problem, and the SQL2005 Server is in SharePoint Integration mode. I've searched but can't find a solution. Can anyone steer me in the right direction please?
I have a report with a subscription enabled and the default values that are selected for the report frequently change. Â I have our report server locked down so that the users can't change the defaults, but I now want to empower them to maintain this on their own. Â Here is my dilemma. Â When you have the available parameters set up to pull from a query, the defaults on the report server have to be keyed in manually, which is not an option. Â The only way to get a check box there, is to explicitly specify the available values.I need my available values to be database driven and I need to be able to select my defaults on the report server using check boxes.
View 5 Replies View RelatedHi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies View RelatedHey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
Hi,
I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date
'31/08/2006 23:28:59'
I get the error "...datetime data type resulted in an out-of-range datetime value"
I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?
cheers,
Ernest