Split Number From Nvarchar Fiels Sql Server 2005
Hi
i want to split the numbers from the varchar field(accc0001).
i want 0001 only. at the same time select max of that number+1.
friends can u help me on this .
View Complete Forum Thread with Replies
Related Forum Messages:
Split Number Into New Row
I have the following: iddocidgroupidOption 48986111235 353973334 2314820112346 would like to see this result: iddocidgroupidOption 48986111 48986112 48986113 48986115 35397333 35397334 231482011 231482012 231482013 231482014 231482016 thanks you
View Replies !
Split Out Number Into Groups
hi, not sure if the title really expains what I want to achieve... I have a table which is like : name groups number bob 1 160 bob 51 160 bob 101 160 What I need to do is split the 160 into the correct groups.. so the first 50 goes into group 1, then next 50 in group 51, then the next 60 in 101.. so the result would look like : name groups number bob 1 50 bob 51 50 bob 101 60 thank you for any advice.
View Replies !
NTEXT Vs NVARCHAR For Large Number Of Columns
Hi all,I need to store data into about 104 columns. This is problematic with MSSQL, since it doesn't support rows over 8kb in total size.Most of the columns are of type NVARCHAR(255), which means we can't havemore than 8092/(255*2) = 15 columns of this type.With a row length of more than 8kb, SQL gives a warning that any rows overthat amount will be truncated.So far I'm seeing two possible solutions to this problem:1. Split data into multiple tables with the same ID column accross alltables, and then join them on SELECT statements.2. Use NTEXT instead of NVARCHAR. NTEXT's length is 16 bytes because itcontains a pointer to the actual value stored somewhere else. However, NTEXTdoesn't support regular indexing, only through a Full-Text Index catalog. Inthis case I'll need to user "WHERE CONTAINS(columnName, 'sometext')" toperform searches, which is bearable.I'm inclined toward #2. However I haven't used Full-Text indices before anddon't know their limitations. Will I run into problems with NTEXT? Is therea better solution?Thanks.-Oleg.
View Replies !
Convert Negative Number Stored As Nvarchar
I'm working with a horrible database! The field I'm having issues with is a negative number stored as a nvarchar, but it's not stored in a consistent format. I need to convert the field to a decimal (9,2) but I can't because of the negative sign. Example: Balance 00000000 0000-413 0000-913 00-10913 00009526 I don't even know where to start to convert this. Any help is appreciated! Thanks.
View Replies !
Data Type Nvarchar(MAX) In SQL Server 2005
nvarchar(max) I have a nvarchar(MAX) “Description” field in my SQL Server 2005 database. Somewhere I think I read that nvarchar(MAX) is the size of 4000. My question is in my Insert Stored Proc set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[syl_CodeScheduleTypeInsert] @ScheduleName nvarchar(50), @ScheduleDescription nvarchar(????????)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY INSERT I What number should replace the ???????? with? nvarchar(4000) is that correct? I tried nvarchar(MAX) and nvarchar but both versions only input one character into my database table. Please help a newbie out, cheers!
View Replies !
How Can I See The Data Of Some Fiels?
Hello, I'm new using ASP NET , after I created a local application Visual Web Developer that connect to the remote server to be able to see the data but only shows up User ID, and Email, what can i do if I want to see the rest of the fiels as name, address and phone number??What do you suggest I should do? and How encrypt the data to avoid other people read it?
View Replies !
Saving Carriage Return In A Nvarchar Field (SQL Server 2005)
Hi, As you can see, I'm totally new at Sql Server. I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?". What can I do to preserve the cr inside each field ? Thanks in advance.
View Replies !
Forwarding Variable Number Of Parameters From VB.2005 To Sql Server 2005 Stored Procedure
I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....
View Replies !
Split A Decimal Number Into The Integer Part And The Fraction Part
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
View Replies !
Number Of Databases In Sql Server 2005
Hello,If I have a database of 5 GB with only 20 tables in it, how many ofthose can we put in how many database servers on a high end machine(Dual Xeon 8 GB on windows 2003)?It is to maka a quotation and an estimation of the cost.TIAMario
View Replies !
Sql Server 2005 Auto Number Property
Hello I have a table with two columns. First column is type and second column is ID. ID column must be unique and I want to give values in ascending order to the new records. For example first record's ID is 1 ,second one's is 2 ... How can I do this in sql server 2005?
View Replies !
Finding Port Number In SQL Server 2005
I know the ..Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGErrorLog on the server contains the information. But that needs I to RDP to the server or open the file remotely. Is there any other way to find out the port from my SSMS connection? I remember in SQL Server 2000 you could find the information by choosing Properties of a connection in EM and click on the Network Configuration button. (I know it works when you are ON THE SERVER! But when I tried from my desktop, pressing the button didn't do anything) Canada DBA
View Replies !
How To Insert More Number Of Rows Into Sql Server 2005 Database At Once.
Hi, Good morning to all.My table: User_Group_Map(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER) Now, I want to write one stored procedure that can insert rows into the above table, but more number of rows at-once. Means, the program should allow multiple insertions without the need to call the stored procedure from front-end more number of times. Can anyone please help me on this... Thanks in advance...Ashok kumar.
View Replies !
SQL Server 2005 Slows Down After A Large Number Of Queries
Hi, We are running SQL Server 2005 Ent Edition with SP2 on a Windows 2003 Ent. Server SP2 with Intel E6600 Dual core CPU and 4GB of RAM. We have an C# application which perform a large number of calculation that run in a loop. The application first load transactions that needs to be updated and then goes to each one of the rows, query another table get some values and update the transaction. I have set a limit of 2GB of RAM for SQL server and when I run the application, it performs 5 records update (the process described above) per second. After roughly 10,000 records, the application slows down to about 1 record per second. I have tried to examine the activity monitor however I can't find anything that might indicate what's causing this. I have read that there are some known issues with Hyper-Threaded CPUs however since my CPU is Dual-core, I do not know if the issue applies to those CPUs too and I have no one to disable one core in the bios. The only thing that I have noticed is that if I change the Max Degree of Parallelism when the server slows down (I.e. From 0 to 1 and then back to 0), the server speeds up for another 10,000 records update and then slows down. Does anyone has an idea of what's causing it? What does the property change do that make the server speed up again? If there is no solution for this problem, does anyone know if there is a stored procedure or anything else than can be used programmatically to speed up the server when it slows down? (This is not the optimal solution however I will use it as a workaround) Any advice will be greatly appreciated. Thanks, Joe
View Replies !
How To Get The Week Number Of A Specific Date In SQL Server 2005
Hi, Can anybody tell me to How to get the week number of a specific Date in SQL Server 2005. Like : say Date "03/01/2008" belongs to the 1st week of the year 2008, same way "21/02/2008" is 8th week of the year 2008. So, the same way I need to know the week number of a given (variable) date. I need to write / use a Function in SQL server which will return the Number (INT) (i.e. 1 or 8 or 10 etc...) against a specific (Parameter) date. regards, Saibal Note : Please feel free to write me if my question is not clear.
View Replies !
SQL SERVER 2005 Database Mirroring For Large Number Of Databases
I am trying to enable database mirroring for 100 database. It goes error free till 59 databases (some times 60 databases) with the status (principal, synchronized) on principal. on the 60th or 61st database it gave the status (principal, disconnected). Also mirror starts acting abnormal. connection to mirror starts to give connection timeout and it is not enabling database mirroring on any more databases. I have SQL SERVER 2005 Enterprise with SP1 on the servers. witness is not included yet. this are my test servers... i have more than 500 databases on my production servers. principal and mirror both are using port 5022 for ENDPOINT communication.
View Replies !
SQL SERVER 2005 DATABASE MIRRORING For Large Number Of Databases
I am trying to enable database mirroring for 100 database. It goes error free till 59 databases (some times 60 databases) with the status (principal, synchronized) on principal. on the 60th or 61st database it gave the status (principal, disconnected). Also mirror starts acting abnormal. connection to mirror starts to give connection timeout and it is not enabling database mirroring on any more databases. I have SQL SERVER 2005 Enterprise with SP1 on the servers. witness is not included yet. these are my test servers... i have more than 500 databases on my production servers. principal and mirror both are using port 5022 for ENDPOINT communication. All of the databases are critical and all must be included in the Database Mirroring. so, after that I tried to implement database mirroring again...... System has 3 GB of RAM, SQL SERVER (Mirror) using 85 MB of RAM but still giving this error while trying to enable database mirroring for 37th Database..... "There is insufficient system Memory to run this query" WHY?
View Replies !
SQL Server 2005 Full Text Performance With Large Number Of Records
Hi We are using the SQL Server 2005 Full Text Service. The data is not huge, but the kind of data is that each record is small and there are a large number of records. There are 35 million records now with 11 GB of data and about 1.6 GB of FT catalog on the table. This is expected to grow to at least 10 times the size of this data. The issue is with FTS taking a long time to return results when the number of hits (rows) getting returned from FTS is large for some searches, it takes a very long time. With the same data & catalog, those full text queries for less common words return timely. The nature of the problem doesnt allow us to only have top results. We need all the results. So it’s not about the size of data but the number of results getting returned from FT. (As the catalog is inverted). The machine is dual processor with 4 GB RAM. I am considering splitting the table and hence the catalog and using multiple servers to do full text searches in smaller catalogs. Is there any other way this issue can be solved ? If splitting is the only way, can you give me an idea as to what is a statistical/standard limit to the number of search results/cataog size as which FTS gives good results Thanks in advance
View Replies !
SQL Mobile 2005 NVarChar Truncation Problem?
I have a field in an .sdf database which is an nvarchar(350). I'm accessing the database using .Net Compact Framework 2.0 and have setup the SqlCeCommand as follows: SqlCeCommand command = this.conn.CreateCommand(); command.CommandText = "INSERT INTO Items (FeedID, Title, Link, Description, PublishDate) "; command.CommandText += "VALUES (@feed, @title, @link, @desc, @date)"; command.Parameters.Add("@feed", SqlDbType.Int); command.Parameters.Add("@title", SqlDbType.NVarChar, 75); command.Parameters.Add("@link", SqlDbType.NVarChar, 300); command.Parameters.Add("@desc", SqlDbType.NVarChar, 350); command.Parameters.Add("@date", SqlDbType.DateTime); command.Parameters["@feed"].Value = article.Feed; command.Parameters["@title"].Value = article.Title; command.Parameters["@link"].Value = article.Link; command.Parameters["@desc"].Value = article.Description; command.Parameters["@date"].Value = article.Published; command.ExecuteNonQuery(); Now when the command executes, I get the following error message: InvalidOperationException@desc : String truncation: max=255, len=330, Given that my NVarChar is actually 350, why on earth is it saying max=255? Any help would be appreciated.
View Replies !
Adding New Data Fiels And Inserting Data
I have some website work lined up and it involves some simple modifications to a MS SQL 2000 server. What I'll need to do is add some new data fields and insert some data. I have some experience with databases - MS Access and MySQL, but I have never used or seen MS SQL 2000. My question is, is this a relatively simple thing to do for someone who hasn't used it before? I can do these things quite simply in Access or MySQL, so is MS SQL 2000 going to be any different? Also, does anyone know of any free tutorials online that would help me out? Thanks
View Replies !
Number Of Full Text Catalogs Sql Server 2005 Supports - Need Definitive Answer
I've searched the forum and saw that this question has been asked several times. However, none of the responses point to any documentation about this limit. We've got different groups bickering over this ("There is no limit!" "Yes there is!" "No there isn't!") and it would help to have a link on the MSDN or a response from Microsoft to this question to put this to rest once and for all. Also, if there is a limit, is that per Sql Server instance or per physical server? Secondly, we currently have over 400 catalogs. We've noticed it takes 4-5 minutes for new data to show up in search results. Does the number of catalogs affect performance, or is it the amount of data, irrespective of catalog count? Thanks
View Replies !
The Parameterized Query '(@contactdate Nvarchar(4000),@dnbnumber Nvarchar(4000),@prospect' Expects The Parameter '@futureopportunity', Which Was Not Supplied.
HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection) ' old method: Lots of INSERT statements Dim rowscopied As Integer = 0 ' first, create the insert command that we will call over and over: destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection) ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar) ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text) ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar) ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar) ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar) ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar) ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar) ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text) ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar) ' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15 ins.Parameters(i).Value = r(i) Next ins.ExecuteNonQuery() 'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then 'Console.WriteLine("-- copied {0} rows.", rowscopied) 'End If Next End Using destConnection.Close() End Sub
View Replies !
Compare Nvarchar(10) With Nvarchar(1000)
I had this question for quite a long time. It seems the latter one don't take any extra storage space than the previous one. As long as the real string length is less than 10. Is that mean the latter one not cost anything? I once heard the different is when they are in memory. But not sure of it. Can anyone explain it and provide some official reference on it? Thank.
View Replies !
SQL Svr 2005 Nvarchar(MAX) Interpreted As TEXT Fiields When Linked To MS Access 2003
Hi, We have moved our Access 2003 database to SQL Server 2005. A number of the original tables contained MEMO fields and these became nvarchar(MAX) fields in SS 2005. We continue to use the original Access application and therefore have linked the tables to the Access application via ODBC connection. However, the fields that were originally Memo in Access native tables are brought over as TEXT fields (255 char limit) by the linking process. Is there a way I can link the tables and have those big text fields be Memo fields in the linked tables?? Or is there some way to work around this? (Moving to Access 2007 is not an option at this time.) I would really appreciate any suggestions! Thanks.
View Replies !
Problem About Pass A Big String (over 8000 Characters) To A Variable Nvarchar(max) In Stored Procedure In SQL 2005!
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005! I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string. I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why? create procedure Hellocw_ImportBookmark @userId varchar(80), @FolderId varchar(80), @Insertcontent nvarchar(max) as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;
View Replies !
Problem About Pass A Big String (over 8000 Characters) To A Variable Nvarchar(max) In Stored Procedure In SQL 2005!
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005! I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string. I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why? ----------------------13------------------------------------- create procedure Hellocw_ImportBookmark @userId varchar(80), @FolderId varchar(80), @Insertcontent nvarchar(max) as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;
View Replies !
SQL 2005 Bug? Cannot Create Full Text Index For VARBINARY Column That's Populated With Converted NVARCHAR Values
Hi, I was wondering if any SQL Server gurus out there could help me...I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value - I've narrowed it down to this specifically, populating with non nvarchar text seems to work fine.To re-create the problem quickly...If I populate the column viaCONVERT(varbinary(max), 'test text')then there is no problem, I get results as expected.However if I populate the column viaCONVERT(varbinary(max), CAST('test text' as nvarchar(max)))no results are ever returned.Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.
View Replies !
SQL 2005 Bug? Cannot Create Full Text Index For Varbinary Column That's Populated From Converted Nvarchar Values
Hi, I was wondering if any SQL Server gurus out there could help me... I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value. To re-create the problem quickly... If I populate the column via CONVERT(varbinary(max), 'test text') then there is no problem, I get results as expected. However if I populate the column via CONVERT(varbinary(max), CAST('test text' as nvarchar(max))) no results are ever returned. Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary. I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out). The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it). Any pointers / suggestions would be greatly appreciated. Cheers, Gavin. UPDATE: Below is a T-SQL script you can run to demonstrate the effect I'm experiencing... Code Snippet -- Create test database CREATE DATABASE FullTextTest GO USE FullTextTest GO -- Create test data table CREATE TABLE TestTable ( pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY, varbinarycol VARBINARY(MAX), documentExtension VARCHAR(5), ) GO -- The below single entry WILL BE FOUND (the text source is being entered directly) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html') -- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html') INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html') GO -- Create the full text catalog sp_fulltext_database 'enable' GO CREATE FULLTEXT CATALOG TEST AS DEFAULT GO CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033) KEY INDEX tablePK GO -- NOTE: You might need to give the catalog a chance to build before running the script below. -- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')
View Replies !
Converting From Nvarchar To Int Into Sql Server
Columns from database have values like 1,5 etc and I'm getting the error: Syntax error converting the nvarchar value '1,5' to a column of data type int. when trying to convert from nvarchar to int: SELECT CONVERT(int, MyNVarChar_column) FROM MyTable What I'm doing wrong? Thanks a lot
View Replies !
How Sql Server Handles Nvarchar
Hi i'd like to know about varchar , when sql store data as nvarchar it store the actual length besides 2 bytes but the question is if there is another field does sql server store the next field besids nvarchar so there is no seprated bytes and is this exist and i update data of nvarchar does sql sqerver shifted the reamin data exist in the same track please send me the answering
View Replies !
Access 97 DB And SQL Server 7 - Converting Nvarchar To Int
I need help converting an nvarchar value to int. I receive a SQL error when running the following query... "SELECT DISTINCT [Time Cards].TimeCardID, [Time Cards].Status, [Time Cards].Verification, [Time Card Hours].[Date Worked], [Time Card Hours].[Billable Hours], [Time Card Hours].[Billing Rate], [Time Card Hours].[Overtime Hours], [Time Card Hours].[Overtime Rate], Employees.FirstName, Employees.LastName, [Function].[Function], [Time Card Hours].[Invoiced Hours], [Time Card Hours].[Invoiced Overtime], [Time Cards].[30 Day Grace] FROM [Function] INNER JOIN (Employees INNER JOIN ([Time Cards] INNER JOIN [Time Card Hours] ON [Time Cards].TimeCardID = [Time Card Hours].[Time Card ID]) ON Employees.EmployeeID = [Time Cards].EmployeeID) ON [Function].FunctionID = Employees.FunctionID WHERE ((([Time Cards].[30 Day Grace])=-1));" [Time Card Hours - nvarchar, Time Cards - int]
View Replies !
Problem With Varchar And Nvarchar Datatype In Linked Server
Hi, I am updating a remote table using linked server in sql server 2005. but in case of varchar and nvarchar i am getting an error : "OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Msg 16955, Level 16, State 2, Line 1 Could not create an acceptable cursor." thanks in advance. Thanks & Regards Pintu
View Replies !
Trying To Split My Columns Into Years (col1 Must Have Vals For 2005, Col2 Vals For 2006)
Hi, i'm reasonably new to reporting services and am looking for a way to split my reports' Years to compare the months in year 2005 to 2006 but i can't get my data nest to one another in a single line, it splits the years into different rows as an example this is what i want if you can decipher that 2005 2006 Growth 2005 Year to Date 2006 Year to Date Year to Date Growth turnover gross profit turnover gross profit turnover gross profit turnover gross profit Jan 250500 75300 280200 84100 11.85629 11.686587 250500 75300 Feb 205000 67950 190350 59900 -7.14634 -11.84695 455500 143250 take the month above and add the current months values Mar 217670 70540 234200 78000 7.594064 10.57556 673170 213790 Apr 270780 84000 290400 93000 7.245735 10.714286 943950 297790 May 265000 79260 289050 90200 9.075472 13.802675 1208950 377050 Jun 277300 81050 277900 82000 0.216372 1.172116 1486250 458100 Jul Aug Sep Oct Nov Dec Here is my Query: SELECT /*DT.[YEAR],*DT.[MONTH],*DT.MONTH_NAME,*/ DC.CLIENT_KEY, (select SUM(FT.Cost)where dt.[year] = 2005) AS COST , (select SUM(FT.Price)where dt.[year] = 2005)AS SALES,(select SUM(FT.Cost) where dt.[year] = 2006),(select SUM(FT.Price) where dt.[year] = 2006)--, SUM(FT.QTY) AS QUANTITY, SUM(FT.PRICE) - SUM(FT.COST) AS GP,(SUM(FT.PRICE) - SUM(FT.COST)) / SUM(FT.PRICE) * 100 AS GP_PERCENTAGEFROM FACT_TRANSACTION FT, DIM_TIME DT, DIM_CLIENT DC, DIM_INVOICE_TYPE DIT, DIM_PRODUCT DPWHERE FT.TIME_KEY = DT.TIME_KEYAND FT.PRODUCT_KEY = DP.PRODUCT_KEYAND FT.CLIENT_KEY = DC.CLIENT_KEYAND FT.TYPE_KEY = DIT.TYPE_KEY AND DIT.TYPE_KEY NOT IN (5,6,13,14,15,16,17)AND DC.CLIENT_SERIALNO = '86634'--AND DT.[YEAR] IN(2005,2006)AND DT.[MONTH] IN(1,2,3,4,5,6,7,8,9,10,11,12)AND DP.PRODUCT_KEY <> 1668684GROUP BY DT.[YEAR],DC.CLIENT_KEY--, DT.[MONTH]ORDER BY /*DT.[YEAR],*/DT.[MONTH] but it returns everything under one another 2005 1 January 2005 3 296092.3431 405263.62 12811 109171.2769 26.93 2005 2 February 2005 3 318597.658 432098.17 13220 113500.512 26.26 2005 3 March 2005 3 371327.721 506481.46 15283 135153.739 26.68 2005 4 April 2005 3 371647.994 504713.99 15491 133065.996 26.36 2005 5 May 2005 3 400870.6138 542759.57 16296 141888.9562 26.14 2005 6 June 2005 3 399673.0086 546110.59 16607 146437.5814 26.81 2005 7 July 2005 3 390477.7521 535531.40 16153 145053.6479 27.08 2005 8 August 2005 3 380628.57 520281.87 15800 139653.30 26.84 2005 9 September 2005 3 340949.8849 471861.17 14820 130911.2851 27.74 2005 10 October 2005 3 340240.804 470007.78 14444 129766.976 27.60 2005 11 November 2005 3 349156.1871 481193.61 14523 132037.4229 27.43 2005 12 December 2005 3 346038.5059 477011.72 14865 130973.2141 27.45 2006 1 January 2006 3 340062.1369 470010.08 14037 129947.9431 27.64 2006 2 February 2006 3 328463.9689 452404.79 13996 123940.8211 27.39 2006 3 March 2006 3 375264.977 517800.27 16065 142535.293 27.52 2006 4 April 2006 3 412708.965 567014.52 17550 154305.555 27.21 2006 5 May 2006 3 446973.4231 606476.26 18920 159502.8369 26.29 2006 6 June 2006 3 406072.4943 544634.77 17053 138562.2757 25.44 2006 7 July 2006 3 389104.6316 526091.14 16228 136986.5084 26.03 2006 8 August 2006 3 317810.4531 431530.58 13641 113720.1269 26.35 2006 10 October 2006 3 405230.7083 549310.72 17151 144080.0117 26.22 2006 11 November 2006 3 379788.6645 514554.14 15917 134765.4755 26.19 2006 12 December 2006 3 393235.0906 531582.69 16924 138347.5994 26.02 If i do get them split then it put every year's value on a different line 2005 1234123.34 32432432.43 NULL NULL 2006 NULL NULL 12312.212 15235453.21 Please Help,
View Replies !
Automatic Number Increment In Ms Sql 2005
will it be possible to increase number as below automatically000000010000000200000003....whenever the row is inserted, number will be increased like aboveformat.which data type should I select and do some other setting to recordlike that?thanks
View Replies !
Large Number Of Databases On 2005
For anyone with a larger number of databases (500+): How many do you have in a single instance. If you are using multiple instance on a single server, how many dbs per instance. This is why I'm asking We are experiencing 701 "out of system memory" and temporary (usually) system freezes when the error occurs. We have 32bit 2005 version 9.00.2153.00, 32GB of memory, AWE enabled, quad dual-core 3GHz hyperthreaded server. Nether the bPool or VAS show any pressure when the "out of system memory error" occurs. Since this error usually indicates a VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no difference. PSS has been working on the case for 3 weeks. They dont seem to be finding any evidince of memory pressure either. When I last spole to the escalation engineer yesterday it seemed that they are going to recommend reducing the number of databases on the server. I asked for clarification as to whether we are hitting a 32 bit barrior, an instance limitation, or both. I am awaiting the answer. How many databases do you have on your server? We had between 1700 and 1900 (the number varies) at times when the error occured. We are now at 1500, and have not had the error in the 2 days since reducing the number of databases...
View Replies !
Maximum Number Of Databases Used For Each Query In 2005?
In MS Sql Server 2000, if you run a stored procedure that query's morethan 8 databases, you get this error gets raised:Maximum number of databases used for each query has been exceeded. Themaximum allowed is 8.In 2005, does this maximum number still exist? if so, is it still 8, orhas the number been increased?Thanks,Mike
View Replies !
SQL 2005 Unattended Install TCP Port Number
Hi All, I am having an issue with SQL 2005 unattended install, I cannot see where in the template.ini file you can specify the TCP port number for SQL 2005 to listen to I want to change it to a specific port number, I know you can do this in an unattended install on SQL 2000. Also in an unattended install of SQL 2000 and 2005 is there any way you can specify the database and transaction logs location for new databases to be installed on another drive then the system databases. I know you can change this later in SQL server properties but I wanted this to be done during the install or is there a T-SQL script which can do this after? Any help on this will be great. Cheers Raxso
View Replies !
SQL Reporting Services 2005: Formatting A Number
I know this is very simple, and I've done it before, but now I can't remember how to do it and I keep failing to do it correctly. I want to format a 6 digit number like this: 00-00-00. The number is returned from the database as 6 digits, and I just want to add the dashes. I tried putting ##-##-## in the Format cell in the properties of the textbox (which I thought worked before) but it's not doing the trick. What am I doing wrong here?
View Replies !
How Can I Automate The Column Titled As 'ID NUmber' In My Database, In VS 2005?
I have a table with a primary key titled as 'ID NUmber' which needs to be created automatically, however every time i add a new record the ID is not added and i have to write it manually i.e. 1, 2, 3.., could you please advice me how i can format this; i know you can do this with microsoft Access but with VS 2005 + VB language this option is not available under data type *i am using VS 2005 and VB language
View Replies !
SSRS(2005) Print Review && Page Number Reset ?s
Hi, I'm having 2 issues on my first "real" SSRS report. 1. How to do a print preview via the preview tab. For print rendering, I have no print preview button on the preview toolbar. Is this normal? MS documention said that there is a Print Preview button. (BOL May 2007 - Designing & Creating reports -> working with report designer -> debugging and Publishing reports (Print Preview)) This report will always be rendered via the printer and it is a "pain" to have to always print it to see changes. The print layout option comes up all black with nothing viewable. 2. How do I reset page numbers back to 1 for report groups? I see no option for this. My report group requires a new page and I want page numbering to start at 1 when the grouping changes. Note: I'm running VS2005 via Vista (with VS2005 Vista fix installed). SQL2005 SP2 also installed. Any advice on this will be appreciated. I've spent too much time already where this should be obvious.
View Replies !
|
TRACKER
